A new Postgres-based darling is TimescaleDB. It’s a drop-in for Postgres.
It is a hybrid row column store with excellent compression and performance.
It would be interesting to see how it compares if narrator would try it out. Benchmarks would be cool.
One very neat feature I am enamored by is “continuous aggregates”. These are materialized views that auto-update as you change the fact table.
Continuous aggregates are a great idea. InfluxDB had “continuous queries” (but the implementation of influx generally is not so neat), and firebolt has “aggregate indexes” which are much the same thing.
I think all olap dbs will eventually have them as staple, and that they will trickle down into oltp too.
The reason why TimescaleDB is so fast relies really a simple concept; keep indexes small enough to keep in memory. If you have a small enough index, you only need to do 2 reads from disk; one for the index to locate the data, and then one for the actual data.
Fetching data becomes much, much slower once your index is too large to fit in main memory. TimescaleDB segments the indexes into chunks (the “hypertables”) and makes sure these chunks are all “small enough”.
This alleviated further by having the data sequential by time; inserting new data does not need to alter older index chunks, which is what makes inserts fast.
I can imagine that if that’s not the case and your inserts are altering “older” chunks so data needs to move between lots of chunks could make the database prohibitively slow.
I would have to dig more into specifics of your use case but my gut reaction is yes, it would be better.
I do not have specific experience with TimescaleDB, but I have some experience scaling PostgreSQL directly and with Citus (which is similar, but not the same). But depending on the nuances of your use case, I can envision a number of scaling strategies in vanilla Postgres to handle your use case. A lot of what Timescale and Citus does is abstract some of those strategies and extend them. Which is just a vague way of me saying: I think I could probably come up with a scheme in vanilla Postgres to support your use case, and since Timescale/Citus makes those strategies even easier/better I am fairly confident they would also handle that use case.
As an example I currently have a table in my current Citus schema that is sharded by column hash (e.g. "type" enumerator) and further partitioned by time. The first part (hash based sharding) seems possibly sufficient for your use case.
Beyond the most simple applications in that domain though, there are more exotic options available to both Timescale and Citus that could come into play. For example, I know Citus recently incorporated some of their work on the cstore_fdw into Citus "natively" to allow columnar storage tables directly:
> if I do a group by over all rows in a table that they may not perform better
Computing aggregates against contiguous values in memory or disk for a single column will always be faster than reading records with differing value offsets/alignments. These operations can benefit from SIMD and other hardware optimizations you don't get with row-aligned data.
Much faster, if you can leverage its strengths. It helps if you have a time column or monotonic increasing value like a serial column. But just using the table compression and columnar format can give a order of magnitude speedup on analytics queries (because you load and scan less bytes.)
This recommendation reads backward. You made a suggestion of a substitute that's "comparable" yet without any data and then go to nudge the author to try it. How do we know you didn't just pull up something just to have the author test it for you?
It is a hybrid row column store with excellent compression and performance.
It would be interesting to see how it compares if narrator would try it out. Benchmarks would be cool.
One very neat feature I am enamored by is “continuous aggregates”. These are materialized views that auto-update as you change the fact table.
Continuous aggregates are a great idea. InfluxDB had “continuous queries” (but the implementation of influx generally is not so neat), and firebolt has “aggregate indexes” which are much the same thing.
I think all olap dbs will eventually have them as staple, and that they will trickle down into oltp too.