Pg_analytics: Transforming Postgres into a Fast Analytical Database

docs.paradedb.com

10 pointsalamb3 comments

Is there any sort of hit to insertion timing for using a column-based backend?

On Clickbench, ParadeDB load times are 1.85x faster than Postgres. Typically, you would expect slower inserts if you were using an index to accelerate queries since inserting into the index takes time. But pg_analytics is not an index. It's introducing a new kind of table that uses column-oriented storage (Arrow + Parquet) instead of row-oriented, and allows the user to choose what kind of storage they want on a table by table basis.

The ideal case would be if you did something similar to what Timescale does with their compression/data tiering and kept "recent" data in a "regular" table so that transactional performance (including updates) is maximized but then "archive" it to columnar storage.

But also in general, how does `pg_analytics` compare to Timescale's compression or their tiered storage? My understanding is that compression is on disk and tiered storage is parquet files in S3.

Reply on news.ycombinator.com