4

As far as I can understand temp_buffers setting serve for storing temp tables data.

But as I have created a temp table immediately files gets created for it inside PGDATA along with the main database. So I would appreciate help on several questions.

  1. What happens with
    a. temp table data if temp_buffers is enough to store temp table data?

    b. temp table data if temp_buffers is not enough to store temp table data?

  2. Is there any case when not enough temp_buffers setting ends up spilling to pgsql_tmp?

1 Answer 1

6

Entries in pg_class, pg_attributes (and other related data in pg_catalog) along with empty related files are always created during every create temp table. I cannot refer to one place in the postgresql source code where this behavior would be clearly visible. File creation is very far from create temp table. It's possible to start reading from here

The data written to these temporary tables will be stored in temp_buffers, a special memory structure in the backend private memory. Works like shared_buffers, but much simpler, because we don't care about concurrent access and recovery of such temporary data after a crash.

If temp_buffers is not enough for data - old pages within temp_buffers area will be evicted and written to corresponding relation files.

Is there any case when not enough temp_buffers setting ends up spilling to pgsql_tmp?

No, never. Explicit temporary tables are stored in the regular database directory with the visible t<backendOID>_ prefix in filenames (like $PGDATA/base/13709/t3_226351).


Please do not create or drop temporary tables frequently in postgresql (dozens of times per second). This causes bloat on the system catalog and thus slows down any queries. Temporary tables are not "free" in postgresql.

1
  • thank you for making this situation clear for me. As for frequent temp table if you mean pg_class bloat there is special module 'fasttruncate' developed specifically for '1C:Enterprise' app which I am using
    – George K
    Commented Dec 22, 2021 at 8:10

Your Answer

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

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