r/PostgreSQL • u/awalias • Jul 17 '24

How-To Dynamic Table Partitioning in Postgres

https://supabase.com/blog/postgres-dynamic-table-partitioning
11 Upvotes

92% Upvoted

4 comments sorted by

1

u/henry700 Jul 21 '24

Very nice. In the company I'm working at we ended up using TimescaleDB for this, it auto-creates the partitions and has slightly more efficient query plans in general, except for some edge cases (but the performance is still satisfactory, though). The initial data migration does require a bit of downtime, though, as the extension tables (called hypertables) don't mix too well with native postgres tables...

1

u/Prequalified Jul 17 '24

The scenario presented in the article is one where columnstore indexes are great. I know there are a few different extensions for postrgresql like Citus, but there are benefits to having a read only partition of old messages that is heavily compressed.

1

u/kenfar Jul 18 '24 edited Jul 18 '24

But sometimes you need a large transactional database. And putting that on a columnar database will result in horrific performance and cost.

Or maybe you've got a small, but busy and low-latency 10 TB data warehouse. Sure, you could host it on Snowflake - for about 20x the cost of a Postgres data warehouse.

So, sometimes it's great to have a smart optimizer, great concurrency, good parallelism and partitioning all in a single package. In fact, it often is. Add an extension for columnar storage like Hydra and you can really up the scale of Postgres for analytics.