Pg_analytics: Transforming Postgres into a Fast Analytical Database
docs.paradedb.com
Pg_analytics: Transforming Postgres into a Fast Analytical Database
1–4 of 4 posts
Re: Pg_analytics: Transforming Postgres into a Fast Analytical Database
#2Is there any sort of hit to insertion timing for using a column-based backend?
Re: Pg_analytics: Transforming Postgres into a Fast Analytical Database
#3Is 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.
Re: Pg_analytics: Transforming Postgres into a Fast Analytical Database
#4Is 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 tabl…
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.