Non blocking and zero downtime ALTER statements in PostgreSQL with pg-osc

Unfortunately this would mean you'd have to round-trip the data being copied through the client into a concurrent non-read-only transaction to write into the shadow table. However I think you could avoid the client round-trip by exporting the snapshot from the read-only transaction using pg_export_snapshot() [2] and then, on the concurrent connection, copy in batches by alternating between a read-only transaction opened on this snapshot using SET TRANSACTION SNAPSHOT in which you grab a WITH HOLD cursor on a LIMIT query (which copies the rows into an in-memory region), and a non-read-only transaction which actually writes to the shadow table. (The latter can be simply READ COMMITTED to avoid the possibility of serialization failure.)

And also of course you can't issue the DELETE on the audit table if the transaction is READ ONLY -- but instead of doing that, you could just read out the contents of the audit table (or the max serial), and skip those entries when applying the audit table later.

[1] https://www.postgresql.org/docs/current/transaction-iso.html...

[2] https://www.postgresql.org/docs/current/functions-admin.html...

[3] https://www.postgresql.org/docs/current/sql-set-transaction....

My use case is somewhat different. I have ~400M row tables which are not updated live, but I rebuild them from new source data, because it is faster that way (lots of columns, indices and FKs). There are also materialized views based on these tables, similarly with multiple indices.

I wrote some sql scripts using information_schema, which prepare new tables for data import, rebuild indices, FKs and then swap tables. After that scripts recreate materialized views from definitions and swap them. All happens without ACCESS EXCLUSIVE lock, so it can be still used by the backend. It sucks, though. I wouldn't mind if there was a way to have views use table names, so I could just refresh them after swapping tables.

1. begin a transaction;

2. rename your table (or the entire schema) to some temporary/anonymous name;

3. perform the DDL operation;

4. rename your table/schema back;

5. commit the transaction.

I have no idea why this works, but it does!

MySQL and MariaDB both support native online DDL, which makes alter statements non-blocking and zero downtime in most cases, in even in-place (no whole table data copy) in some cases.

pt-online-schema-change is still useful when you want control on when the tables are swapped over.

People that have never used the Oracle RDBMS give it grief because of Larry, the rep of the company, etc, which is a shame because the DB is great. This feature is more than 20 years old.

I’m pleased that Postgres is the best of the open source databases & it leading as far as functionality goes.