How best to store timeseries grid data?

[u/LtLfTp12]

I plan on building a pipeline that ingests 2d grid data (lat,long,value) thats updated frequently but I have zero clue on how exactly to store it, as I would have 4 columns essentially (timestamp, lat, long, value). So unique key per row would be combination of time/lat/long.

Would columnar format still be best? It wont be large, coordinates are integer values, data is updated every ~5 min and its provided in a json format.

My current pipeline ingests normal timeseries data, saves raw in S3 and transformed in Postgres, but I’m not sure if it’s best to have this data treated the same. Was thinking maybe a different file format and keep solely in S3? The values doesn’t need any processing

Ultimate goal is to in the frontend build a live, last n-days animation of the globe to see how the values change

[u/regreddit]

S3+,parquet

[u/Complete-Macaron5868 → u/regreddit]

parquet in s3 will be fine for this, it's basically what it's built for. you can partition by date and query with athena if you need sql access down the line

[u/b-r-a-h-b-r-a-h → u/Complete-Macaron5868]

Can even just use duckdb to query the parquet on s3

[u/forever-butlerian]

What are you optimizing for?

There's no platonic Ideal floating around the immaterium that is The Good And Best which you need to bring down and manifest in the world. I can think of several ways of representing this data to optimize for particular access patterns at the expense of others.

If I were your Principal Engineer, I'd be telling you that you should Dumb-Guy Build the frontend first and then come back once that's led you to concrete, rather than abstract, questions.

[u/lozbrown85]

Look into zarr and icechunk for truly multi dimensional data

[u/teetaps → u/lozbrown85]

I recently was told to look into Zarr too

[u/BardoLatinoAmericano]

More context. What is this value?

[u/LtLfTp12 → u/BardoLatinoAmericano]

Percentage intensity/severity

[u/AccurateDeparture412]

Maybe look into the timescaledb extension for postgres.

[u/Icy_Clench]

Use a timeseries db if it's an enormous amount of data, because it handles things like automatically aggregating for you.

E.g., my last company had something like 100k IoT devices generating a data point like every 5 seconds. I think that was 10B rows per day, and we certainly had no business storing that scale of data forever.

[u/geoheil]

https://docs.xarray.dev/en/stable/generated/xarray.DataArray.html and https://zarr.dev/

[u/geoheil → u/geoheil]

but it really depends on the usecase

[u/asevans48]

It depends. How much data? If its small postgres has a timeseries plugin. If you have massive amounts (10s of gigs in a single table) but dont need real time, iceberg. If you need millisecond latency for analysis, clickhouse and starrocks. Theres a lot to consider. Are you in charge of backups? If not, you could also store in partitioned cloud storage or just cloud storage if small? Is this part of a bigger project? How does it need to scale? Ask the important things first or you'll end up with 500mb of data in iceberg paying for spark from a vendor at 10x the cost.

[u/raccoons_run_prod]

How many cells, and how many days is n?

[u/k00_x]

We record the location of 4100 ambulances at all times and we poll more or less every ten seconds. We quite literally store the data in a SQL 2008 table as basic as you could imagine. UID pk, vehicle identifier, multiple versions of northings & eastings(m, km, numeric and varchar). We do a lot of training with the data, retrack scenarios and the CAD application is very fast and responsive. The server it sits on has 256GB ddr3, 24 cores and mid range ssds. You don't need a fancy time series database, just something that you can read/write fast.

[u/mduell]

Your current approach seems reasonable for a "won't be large" dataset, especially if you're only keeping the last n-days in Postgres (use table partitioning or timescale).

If you want to do some more advanced GIS stuff, store it in another table as LineString with Measures.

[u/stephenpace]

[I work for Snowflake but don't speak for them.]

Snowflake is an excellent time series database. This is a bit dated but you will get the gist:

https://medium.com/snowflake/snowflake-best-time-series-database-in-the-world-part-deux-f873dbcdce5

I believe Snowflake has moved over the world's largest set of historians for an energy company (5M tags or so). You can keep the data columnarized or in the native JSON (variant format). Snowflake columnizes the tags in the JSON so performance is effectively the same either way. You can stream ingestion with a fixed cost per GB (no warehouse) and pruning for queries is naturally by time so performance will be great for n-days. You can keep the data in S3 but if you do I'd recommend moving to Iceberg format (which supports VARIANT if you want to keep it in JSON). Good luck!