1

I'm using a large number of TEMP TABLES and want to make sure I'm not leaking storage.

I create and use the table in a transaction with CREATE TEMP TABLE mytable ... ON COMMIT DROP.

I'm having trouble understanding the documentation for CREATE TEMPORARY TABLE. It states:

The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands.

Will ON COMMIT DROP reclaim space or do I need to actually run VACUUM mytable?

If I do need to run VACUUM then I'm not sure how, as the temp table isn't available after the transaction ends.

Improve this question

1 Answer 1

Reset to default
3

Temporary tables are dropped completely at the end of your session in any case. (Or at the end of the current transaction already with ON COMMIT DROP.) The autovacuum daemon (or manual VACUUM, for that matter) is not involved in this.

Since temp tables are registered in the system catalogs like regular tables, dropping them leaves some (very small!) dead tuples behind, which are cleaned up by autovacuum, eventually. If you want to count that as effect on storage.

Related:

Improve this answer
1
  • Excellent, thank you so much! I did read that answer first, but it left some room for doubt.
    – Joe
    Aug 9 at 6:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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