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?
Best Answer
Generally the fastest way to duplicate a table is simply:
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: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.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
toold_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: