Hacker News Reader

Comment by willvarfar | Hacker News Reader

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.

View on HN

AFAIK is an extension to pg and not a “drop in” replacement

Would TimescaleDB be much faster for analytical queries that aren't necessarily segmented or filtered by time?

My uninformed assumption is if I do a group by over all rows in a table that they may not perform better.

I'll look into their continuous aggregates -- that could be one way to get around the cost of aggregating everything if it's done incrementally.

I’d guess any roughly sequentially keyed table ought get good insert performance. Think how common auto-increment is.

And being HTAP, timescale ought do better than a classic pure column store on upserts and non-appending inserts too.

Of course if your table is big and the keys are unordered you still might get excellent performance if your access pattern is read heavy.

Of course you can still mix in classic Postgres row-based tables etc. Timescale just gives you a column store choice for each table.

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:

https://www.citusdata.com/blog/2021/03/05/citus-10-release-o...

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.)

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.

> 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.

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?

Reply on news.ycombinator.com