Earlier quoted context omitted.
The naive relational time series table looks like: propertyid, timestamp, value Then add a covering index so your reads only ever hit the index. This works completely fine for low billions of rows. After that suggest using clickhouse. It is less general but at large enough scale you need to make some tradeoffs. Completely fine to start with a relational DB in many cases though.
Thanks for this. Can we keep going? The article mentioned stock prices, so let's use your schema: LLY.NYSE, 1726377148, 924.38 SHOP.NYSE, 1726377216, 72.45 SHOP.NYSE, 1726377245, 72.41 LLY.NYSE, 1726377284, 924.39 LLY.NYSE, 1726377310, 924.36 Okay, so you're appending values that capture a moment in time for a given index, with the understanding that you're almost never going to revise a tuple. So far as we're concer…
For enormous amounts of data, you want your data to be sequential (a few batch reads are generally faster than lots of random ones) and sorted. Databases like Postgres don't store rows in index order, and their indexes are geared towards small fetches as opposed to big ranges of sequential data. (Postgres does have BRIN indexes, but they're not that great for this.) Other databases do support clustered tables, but they're still page-based underneath and still not suboptimal for other reasons. It's a good learning exercise to compare traditional databases with something like ClickHouse, which was s designed from the ground up to store data in this optimized way. (It's a general-purpose OLAP database, so columns can be any kind of data, not just numbers, and data doesn't need to be time-based; so it's useful for much more than "pure" TSDBs.)
As for numbers: For metric data you're storing numeric measurements. The whole point is applying aggregations to the data, like calculating an average or a percentile over a certain time period. Storing numbers also has the advantage of allowing downsampling. For many purposes you don't need 1s granularity for all your data. So as you "roll" data over into old time periods, you can downsample by storing the data points aggregated as mean/min/max/etc. New data can be exact down to the second, while old data can be exact down to the hour, for example.