3 ms·

Partitioning has been supported for quite a while https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-par

Partitioning has been supported for quite a while https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm... Logical replication... https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication.... https://github.com/2ndQuadrant/pglogical?tab=readme-ov-file#pglogical-2 https://github.com/2ndQuadrant/pglogical?tab=readme-ov-file#... https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.postgresql-rds-postgresql-full-load-pglogical.html https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatab... In 'recent years' (in database support terms), PostgreSQL has gained autovacuum support. https://www.enterprisedb.com/blog/postgresql-vacuum-and-analyze-best-practice-tips https://www.enterprisedb.com/blog/postgresql-vacuum-and-anal... This stack overflow question was insightful, in that most of the slowness many experience may be related to foreign key check lookups on unindexed columns that point to external keys. https://dba.stackexchange.com/questions/328884/why-is-the-delete-operation-on-a-postgresql-database-table-unusually-very-slow https://dba.stackexchange.com/questions/328884/why-is-the-de... Partitioned data and batches to spread out updates also appear to be current best practices https://www.dragonflydb.io/faq/postgres-delete-performance https://www.dragonflydb.io/faq/postgres-delete-performance

mathnode 2y ago
Logical replication does exist in pgsql, which is great. What it still lacks however (and I am sure they will very quickly catch up on) is the user facing process of being able to fix or sync a broken node without a rebuild. I'm also pretty sure pgsql logical replication is single threaded? Things like pg_rewind are layered on fixes that other database users don't have to depend on or learn. Except Oracle (because it's a mess).
evanelias 2y ago
> Partitioning has been supported for quite a while My comment (which you're replying to) didn't mention partitioning at all. > Logical replication... My comment specifically said that logical replication of DDL statements is not supported out-of-the-box in FOSS Postgres, which is absolutely accurate. See the very first thing mentioned on https://www.postgresql.org/docs/current/logical-replication-restrictions.html https://www.postgresql.org/docs/current/logical-replication-... You can pay EnterpriseDB for a solution, among other vendors. That situation is far from ideal. > PostgreSQL has gained autovacuum support. That doesn't even remotely solve the inherent problems of Postgres's MVCC implementation. See https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postg...
jitl 2y ago
The fact that vacuum runs automatically does not stop stuck/slow vacuum and transaction ID wrap-around issues from being a death sentence for a high-write-volume Postgres instance. There are many stories on the internet about this, the Notion one (my employer) is here: https://www.notion.so/blog/sharding-postgres-at-notion https://www.notion.so/blog/sharding-postgres-at-notion

mathnode 2y ago

Logical replication does exist in pgsql, which is great. What it still lacks however (and I am sure they will very quickly catch up on) is the user facing process of being able to fix or sync a broken node without a rebuild. I'm also pretty sure pgsql logical replication is single threaded? Things like pg_rewind are layered on fixes that other database users don't have to depend on or learn. Except Oracle (because it's a mess).

evanelias 2y ago

> Partitioning has been supported for quite a while My comment (which you're replying to) didn't mention partitioning at all. > Logical replication... My comment specifically said that logical replication of DDL statements is not supported out-of-the-box in FOSS Postgres, which is absolutely accurate. See the very first thing mentioned on https://www.postgresql.org/docs/current/logical-replication-restrictions.html https://www.postgresql.org/docs/current/logical-replication-... You can pay EnterpriseDB for a solution, among other vendors. That situation is far from ideal. > PostgreSQL has gained autovacuum support. That doesn't even remotely solve the inherent problems of Postgres's MVCC implementation. See https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postg...

jitl 2y ago

The fact that vacuum runs automatically does not stop stuck/slow vacuum and transaction ID wrap-around issues from being a death sentence for a high-write-volume Postgres instance. There are many stories on the internet about this, the Notion one (my employer) is here: https://www.notion.so/blog/sharding-postgres-at-notion https://www.notion.so/blog/sharding-postgres-at-notion