DROP and CREATE vs DELETE and INSERT in PostgreSQL

Question

I've to duplicate values from one table to another (identical table schemes). What is better (performance):

• Drop table1 and create as select * from table2
• Delete all rows from table1 and insert all rows from table2

Update: I've made a small test on table with almost 3k rows. Drop and create gives about 60ms vs Delete and insert - about 30ms.

Answer 1

I see four useful ways to replace the contents of the table. None of them is "obviously right", but it depends on your requirements.

1. (In a single transaction) DELETE FROM foo; INSERT INTO foo SELECT ...

   Pro: Best concurrency: doesn't lock out other transactions accessing the table, as it leverages Postgres's MVCC.

   Con: Probably the slowest if you measure the insert-speed alone. Causes autovacuum to clean up dead rows, thus creating a higher I/O load.

2. TRUNCATE foo; INSERT INTO foo SELECT ...

   Pro: Fastest for smaller tables. Causes less write I/O than #1

   Con: Excludes all other readers -- other transactions reading from the table will have to wait.

3. TRUNCATE foo, DROP all indexes on table, INSERT INTO foo SELECT ..., re-create all indexes.

   Pro: Fastest for large tables, because creating indexes with CREATE INDEX is faster than updating them incrementally.

   Con: Same as #2

4. The switcheroo. Create two identical tables foo and foo_tmp

   TRUNCATE foo_tmp;
   INSERT INTO foo_tmp SELECT ...;
   ALTER TABLE foo RENAME TO foo_tmp1;
   ALTER TABLE foo_tmp RENAME TO foo;
   ALTER TABLE foo_tmp1 RENAME TO foo_tmp;
   

   Thanks to PostgreSQL's transactional DDL capabilities, if this is done in a transaction, the rename is performed without other transactions noticing. You can also combine this with #3 and drop/create indexes.

   Pro: Less I/O performed, like #2, and without locking out other readers (locks taken only during the rename part).

   Con: The most complicated. Also you cannot have foreign keys or views pointing to the table, as they would point to the wrong table after renaming it.