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.
CREATE is slightly more expensive, as it actually deletes rows from some system tables (
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
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:
COPYis fastest when used within the same transaction as an earlier
TRUNCATEcommand. 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
minimalfor 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
TRUNCATEis 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.
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
Check this links for more info: