5

I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again. I'm wondering what the speed difference is if instead of drop and create, if I just truncate.

Francis Lewis
  • 150
  • 1
  • 1
  • 3

3 Answers3

6

DROP & CREATE is slightly more expensive, as it actually deletes rows from some system tables (pg_class, pg_attribute, ...) in addition to removing the physical table files - and later has to parse etc. the new CREATE TABLE command, while TRUNCATE only removes the physical files for the table and starts new ones, keeping the catalog entries. But the difference is negligible for simple tables, especially for temp tables. And it gets smaller, yet if you factor in an additional ANALYZE that might be needed after TRUNCATE. Then again, you might need that in any case. See:

By "filling the tables" you mean COPY, I suppose? A much more costly difference would be to CREATE or TRUNCATE a plain table in a separate transaction before writing to it, as in this case you accrue the additional (substantial) cost of writing WAL (Write Ahead Log) entries. The manual:

COPY is fastest when used within the same transaction as an earlier CREATE TABLE or TRUNCATE command. In such cases no WAL needs to be written, because in case of an error, the files containing the newly loaded data will be removed anyway. However, this consideration only applies when wal_level is minimal for non-partitioned tables as all commands must write WAL otherwise.

Bold emphasis mine. minimal used to be the default for wal_level until Postgres 9.6. Since version 10, the default replica. Does not affect temp tables, which do not write WAL at all.

You might be interested in CREATE TEMP TABLE ...ON COMMIT DELETE ROWS.
The manual:

All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic TRUNCATE is done at each commit. When used on a partitioned table, this is not cascaded to its partitions.

Should be fastest. But the difference still typically small.

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
0

DROP TABLE -- remove/deletes a table

TRUNCATE -- empty a table or set of tables, but leaves its structure for future data.

If you do not intend on using the table again, you can DROP the table.

If you intend to use the table again, you would TRUNCATE a table

Speed difference is insignificant compared to doing what is functionally correct for your situation. The only case where the speed might make a difference is if you are performing the operation many hundred times over.

So lets consider that scenario: If you no longer need these several hundred tables, then you might as well DROP them, else they will remain there taking up resources.

If you are going to reuse the tables, then TRUNCATE them, else you are faced with having to recreate them. I would guess that TRUNCATE is faster versus the two operations of DELETE+CREATE.

Check this links for more info:

PostgreSQL Documentation: DROP

PostgreSQL Documentation: TRUNCATE

Community
  • 1
CR241
  • 1,565
  • 3
  • 18
  • 32
0

You should not compare DROP and TRUNCATE because they are different types of commands and it doesn't make much sense to compare them - although of course you can finally compare the speed of their execution, then as written above, TRUNCATE is definitely faster.

However, if you are considering using TRUNCATE then you should also compare TRUNCATE vs DELETE - in this case TRUNCATE is certainly faster as well, although using both of these commands depends on different factors, you should know them, making sure that in this case you can certainly use one of the these commands.

Pawel W
  • 27
  • 4