The Rise of Open Source Time Series Databases

The key is the covering index, you don't want to hit the actual table at all. You should re-organize the index periodically as well in order to ensure optimal query times.

This is suboptimal in many ways as the records are tiny yet there is per row overhead. However, it works fine to low billions of rows in general. I think it would work fine in SQLite as well. Redis is a very different animal however so a different strategy would be needed.

Migrating the same schema to ClickHouse when you need it (I have no affiliation with them), will give you 50X lower data footprint, increase your ingest rates by 10X and your query times by about 2X. However, if you don't really need that, don't bother adding another component in your infrastructure (imo). Also, ClickHouse is much more of a purpose built Ferrari. I.e. you can't expect it to do all kinds of "off schema" type queries (i.e. give me all the stock values on Jan 10, @ 10:32:02.232am). In a relational DB you can add indexes and get results reasonably well. In ClickHouse schema == query pattern essentially. Naturally, you should know what you are doing with both models but in ClickHouse, don't treat it like a "magic data box" that will can handle anything you throw at it (beyond a certain scale at least).

My guess is that constraining to numbers greatly simplifies the implementation, especially so for the query language and aggregation functions.

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.

"In mathematics, a time series is a series of data points indexed (or listed or graphed) in time order."

A a numeric datapoint is by far the largest use case, timeseries databases optimize for that. Most allow you to have several labels ofcourse, i.e. you could store country border crossings as

{from: US,to:Canada} 1726377148, 2123 {from: US,to:Mexico} 1726377148, 34567