PostgreSQL – Improving Temp Table Performance with Copy/Rename

performancepostgresqltemporary-tables

An application I am working with contains this sequence of queries at the end of constructing a large temp table; I believe it is an attempt to improve performance by pruning dead tuples –

create temp table tmp_foo_new as select * from tmp_foo;
drop table tmp_foo;
alter table tmp_foo_new rename to tmp_foo;

Some rudimentary benchmarks do indicate this sequence does improve performance substantially, so it is worthwhile. It feels somewhat hacky to me though. Is there a more canonical way to do this?

Best Answer

If the table has an index, you may want to consider the cluster command instead of re-creating it or using vacuum full. This will:

  • Have the same effect on dead tuples - it physically re-writes the whole table
  • Retains any existing indexes
  • Might improve performance more than just removing dead tuples, depending on whether you will benefit from the clustering too (though of course you could achieve the same with an order by clause when re-creating)
Related Question