30

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?

2 Answers 2

57

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;
3
  • 2
    Thanks man. This is exactly the kind of explanation I was searching for. Thanks again! Commented Dec 27, 2013 at 14:12
  • If there are indexes defined on table2, will they still work once the table is renamed?
    – BamaPookie
    Commented Oct 19, 2016 at 16:25
  • 1
    @BamaPookie check out this for full schema including indexes, constraints and defaults wiki.postgresql.org/wiki/Clone_schema Commented Nov 2, 2016 at 19:39
-1

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

1
  • 1
    Afaik the question is about duplication, not dedup.
    – peterh
    Commented Feb 26, 2020 at 3:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.