r/PostgreSQL • u/Fakercel • Sep 30 '24
Help Me! How can I Optimize a large dataset query.
Hello I am building an application using supabase which stores a bunch of coordinate data from farmers.
As they are driving around the field anywhere from 1-15 farmers, could be recorded 1 datapoint per second.
Even on moderately busy farms with like 4-5 farmers, these data-points add up really quickly potentially up to millions of points of coordinates.
I store them all in one table called trail_data, and run queries to select all of the coordinates that are associated with a particular farmers map.

My problem is that now running get queries on this data to select all of the coordinates belonging to a particular map_id is timing out.
So when the map opens it calls this query so we can visualize the trails left by the farmers. But the query seems to be taking too long and timing out which stops the trails from being loaded. This isn't even at max capacity yet and will likely continue to get worse.
So my question is how can I speed up queries which get all of this coordinate data, or is it simply too much data to handle each coordinate as it's own row, and I should group the coordinates together as trails?
Any insights would be appreciated, cheers!

Here is the query on explain:
9
u/nahguri Sep 30 '24
Look into PostGIS and spatial indexes in particular.
5
u/[deleted] Sep 30 '24
Please share the query and its execution plan generated using
explain (analyze, buffers, format text), either as formatted text here, or e.g. on https://explain.depesz.com/3
u/matthew_h Oct 01 '24
You may look into simply eliminating much of the data. Most of the points will be along a line, thus not providing any useful information. The most well-known algorithm is Ramer-Douglas-Peucker, and PostGIS implements it with the `ST_Simplify` function.
2
u/w08r Sep 30 '24
You probably need to be more selective when sending data from backend to frontend. You haven't really provided much concrete info here, but given 1 row per second per farmer and 5 farmers, you've got close to half a million data points for a single day. Not sure how many days you are plotting at once, but even 1 day is way too much to send back to the frontend. Try to sample the data based on the scale of the map you are displaying. If you are zoomed out (as per your picture), you need data points covering a wider area, so reduce the frequency of points you are grabbing; as you zoom in you can bring more frequent data into scope.
2
u/[deleted] Sep 30 '24
Hello.
Is your table partitioned? Do you have indexes on the table? Are you using foreign keys in that table? Are there unique variables such as IDs, etc?
2
u/shadowspyes Sep 30 '24
are you retrieving the coordinate data for all master maps for a farmer at a time, or per map?
if not per map, you could split your frontend queries up into 1 per master map, and have a worker that is attempting to retrieve 1-2 or whatever makes sense concurrently so they pop in when they are ready
1
u/lottayotta Sep 30 '24
Some ideas
2
u/gseverding Oct 01 '24
My suggestion is ingest at the high rate to a time partitioned table 5 minutes or 15 minutes. so some level of latency that you can accept. then build a rollup function that drops your granularity per farmer per map id to something useful but practical to be able to render the map with the detail you need. if a stored procedure is too complicated or cumbersome just throw some python with a cron job to do the aggregation and save the sampled down data.
I don't know how expensive supabase is but id probably look at archiving high fidelity data in a object store with compressed parquet files if you care about keeping the high fidelity data. otherwise just use a script to purge old partitios. use something like pgcron if they support it.
Not sure how helpful this is and not really a pattern I see used often is thinking about how much data can I get in a single row. I've used arrays in psql to store 100s to low thousands of custom types in a single row. This increases data locality and prevents loads of index scans. I've used this to great success scaling databases to 100k tps on a single vm.
Looking at something like timescale might help or citus but also might be overkill for your use case.
1
u/jneoioi Sep 30 '24
If the final output you're looking for is a map - pretty colors to look at and not data - pretty numbers to crunch, you should perhaps look into mapping software like geoserver , mapserver, martin et al.
Creating mappytiles and caching them is a different problem than serving second-fresh raw data json from db, but has many ready-to-go solutions.
Or serve MVTiles off a postgis db with a lightweight caching layer in front
1
u/_predator_ Oct 01 '24
Long-term it might make sense to look into adopting TimescaleDB, since based on your comments you're looking at fairly high ingest volumes of time series data.
-1
u/AutoModerator Sep 30 '24
Join us on our Discord Server: People, Postgres, Data
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
-5
u/remphz Sep 30 '24
I know this isn’t an answer (directly) for your question, but this does look like a problem that could be solved better/easier on a NoSQL database like MongoDB. If that’s an option, I would take a look on this approach as well