pglogical

A bit of the history as I've been told by 2nd Quadrant/EDB people (my teammates): BDR1 [0] came first and was, and is, open source. pgactive is based on BDR1. BDR2 was a closed-source rewrite of BDR1 that was later abandoned. pglogical v1 and v2 (PGL1, PGL2) were, and are, open-source [1]. pglogical v1, after heavy modification, was eventually merged into Postgres 10. Based on learnings from this logical replication in Postgres 10, 2nd Quadrant started pglogical v2. pgEdge is based on pglogical v2. Then later 2nd Quadrant started pglogical v3 (closed source) and BDR v3 (closed source). They were merged into just BDR v4. At some point the BDR product was renamed to Postgres Distributed (PGD) [2]. 2ndQuadrant was acquired by EDB. We (EDB) just released PGD v6. [0] https://github.com/2ndQuadrant/bdr/tree/bdr-plugin/REL1_0_ST... [1] https://g

Here's how we did it at OneGraph (RIP), where we not only upgraded versions without downtime, but we also moved hosting providers from GCP to Aurora without downtime. 1. Set up logical replication to a new database server. We used https://github.com/2ndQuadrant/pglogical, but maybe you don't need that any more with newer versions of postgres? 2. Flip a feature flag that pauses all database queries and wait for the queue of queries to complete. 3. Wait for the query queue to drain and for replication to catch up. 4. Flip a feature flag that switches the connection from the old db to the new db. 5. Flip the flag to resume queries. It helped that we were written in OCaml. We had to write our own connection pooling, which meant that we had full control over the query queue. Not sure how you would do it with e.g. Java's Hikari, where the query

Partitioning has been supported for quite a while https://www.postgresql.org/docs/current/ddl-partitioning.htm... Logical replication... https://www.postgresql.org/docs/current/logical-replication.... https://github.com/2ndQuadrant/pglogical?tab=readme-ov-file#... 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-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-de... Partitioned data and batches to spread out updates also appear to be current best practices https://www.dragonflydb.io/

You could use pglogical[1][2] and sync to an updatable view - it ought to be faster and it should be easier to maintain (the updatable view would act as an abstraction layer). [1] https://www.2ndquadrant.com/en/resources/pglogical/ [2] https://github.com/2ndQuadrant/pglogical