How to duplicate huge postgres table?

Question

I have huge postgres table (10GB of data - 160M records). Table is static and there are no write operations on it performed. I want to duplicate it, perform writes, reindex it and then with single fast transaction delete the old one and rename the new one to original name.

What is the fastest way to duplicate such huge table?

Answer 1

Generally the fastest way to duplicate a table is simply:

CREATE TABLE table2 AS SELECT * FROM table1;

Parallel INSERTs may be faster, but only with a very fast disk subsystem (when data is interleaved on many drives). Otherwise this will be slower.

Once you're done with modifying table2, it can take the new name with:

BEGIN;
DROP TABLE table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;

The DROP TABLE command needs an exclusive lock, which affects concurrent readers in a way you may want to anticipate:

• DROP will wait for any pending read on the table from other transactions to finish.
• Any new transaction attempting to read that table in the meantime will be put it in wait, and then fail since the original table1 no longer exists. The error would look like "could not open relation with OID oid"

To avoid the second issue, you may rename table1 to old_table1 instead of dropping it, and then drop it only later outside of the transaction, when these readers are done with it. So the sequence above would become:

BEGIN;
ALTER TABLE table1 RENAME TO old_table1;
ALTER TABLE table2 RENAME TO table1;
COMMIT;
...
DROP TABLE old_table1;

Answer 2

You could you this open source deduplicator — uses JDBC: https://github.com/bmiller1009/deduper