Geek Answers Handbook

DROP and CREATE vs DELETE and INSERT in PostgreSQL

I have to duplicate values ​​from one table to another (identical table schemas). Which is better (performance):

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

Update: I did a little test on the table with almost 3k lines. Drop and create give about 60 ms against deleting and pasting - about 30 ms.

+4
source share
3 answers

I see four useful ways to replace the contents of a table. None of them are “clearly wrong”, but it depends on your requirements.

  • (In one transaction) DELETE FROM foo; INSERT INTO foo SELECT ... DELETE FROM foo; INSERT INTO foo SELECT ...

    Pro: Best concurrency: does not block other transactions accessing the table as it uses Postgres MVCC.

    Con: Probably the slowest if you only measure insertion speed. Forces autovacuum to clear dead rows, thereby creating a higher I / O load.

  • TRUNCATE foo; INSERT INTO foo SELECT ...

    Pro: The fastest for small tables. Causes less I / O than # 1

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

  • TRUNCATE foo , DROP of all indexes in the table, INSERT INTO foo SELECT ... , recreate all indexes.

    Pro: The fastest for large tables, because creating indexes with CREATE INDEX is faster than updating them step by step.

    Con: Same as # 2

  • Switch. 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 functions, if this is done in a transaction, renaming is done without notifying other transactions. You can also combine this with C # 3 and the drop / create index.

    Pro: Fewer I / O operations, such as # 2, and without locking other readers (locks performed only during rename).

    Con: The hardest part. In addition, you cannot have foreign keys or views pointing to a table, as they will point to the wrong table after it is renamed.

+14
source

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

Like DROP, TRUNCATE also needs to lock the table.

+2
source

If you talk about doing INSERT manually, one by one, then DROP / CREATE will be much faster. In addition, when using CREATE TABLE AS it only copies column definitions. Indexes and other restrictions will not be copied. This will speed up the copy process. But you will have to remember to re-create them on a new copy as soon as you are done.

The same applies to SELECT INTO . They are functionally identical. They just have different names.

Anyway. When copying large tables, always turn off triggers, indexes, and restrictions to improve performance.

+1
source

More articles:


All Articles