0

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.

1

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 is the default for wal_level.) 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.