CYBERTEC PostgreSQL Logo

PostgreSQL: Sophisticated temporary tables

10.2020 / Category: / Tags: |

Temporary tables have been around forever and are widely used by application developers. However, there is more to temporary tables than meets the eye. PostgreSQL allows you to configure the lifespan of a temporary table in a nice way and helps to avoid some common pitfalls.

CREATE TEMPORARY TABLE …

By default, a temporary table will live as long as your database connection. It will be dropped as soon as you disconnect. In many cases this is the behavior people want:

Once we have reconnected, the table is gone for good. Also, keep in mind that the temporary table is only visible within your session. Other connections are not going to see the table (which is, of course, the desired behavior). This also implies that many sessions can create a temporary table having the same name.

However, a temporary table can do more. The most important thing is the ability to control what happens on commit:

3 Options available

As you can see, there are three options. "PRESERVE ROWS" is the behavior you have just witnessed. Sometimes you don't want that. It is therefore also possible to empty a temporary table on commit:

In this case, PostgreSQL simply leaves us with an empty table as soon as the transaction ends. The table itself is still around and can be used.

Let's drop the table for now:

Sometimes you want the entire table to be gone at the end of the transaction: "ON COMMIT DROP" can be used to achieve exactly that:

PostgreSQL will throw an error because the table is already gone. What is noteworthy here is that you can still use WITH HOLD cursors as shown in the next example:

The table itself is still gone, but the WITH HOLD cursors will ensure that the "content" of the cursor will survive the end of the transaction. Many people don't expect this kind of behavior, but it makes sense and can come in pretty handy.

Controlling memory usage …

If you are using temporary tables, it makes sense to keep them relatively small. In some cases, however, a temporary table might be quite large for whatever reason. To ensure that performance stays good, you can tell PostgreSQL to keep more of a temporary table in RAM. temp_buffers is the parameter in postgresql.conf you should be looking at in this case:

The default value is 8 MB. If your temporary tables are large, increasing this value certainly makes sense.

Finally...

If you want to find out more about PostgreSQL database performance in general, consider checking out my post about three ways to detect and fix slow queries.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram