Rather than plain SERIALIZABLE, maybe consider a SERIALIZABLE READ ONLY DEFERRABLE transaction to read the data. This is the only way in PostgreSQL to read fro

Rather than plain SERIALIZABLE, maybe consider a SERIALIZABLE READ ONLY DEFERRABLE transaction to read the data. This is the only way in PostgreSQL to read from a table both without holding any locks, and also without suffering the potential of failed serialization (which would mean restarting the entire copy). [1] 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#XACT-SERIALIZABLE https://www.postgresql.org/docs/current/transaction-iso.html... [2] https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-SNAPSHOT-SYNCHRONIZATION https://www.postgresql.org/docs/current/functions-admin.html... [3] https://www.postgresql.org/docs/current/sql-set-transaction.html https://www.postgresql.org/docs/current/sql-set-transaction....