DROP and CREATE vs DELETE and INSERT in PostgreSQL

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.

Use TRUNCATE instead of DROP TABLE or DELETE when you have to get rid of all records in a table. With TRUNCATE you can still use triggers in PostgreSQL and permissions are easier to set and maintain.

Like a DROP, TRUNCATE also needs a table lock.