Hans-Juergen Schoenig: PostgreSQL: Sophisticating temporary tables from Planet PostgreSQL

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:

tmp=# CREATE TEMPORARY TABLE x (id int);
CREATE TABLE
tmp=# d
        List of relations
  Schema   | Name | Type  | Owner 
-----------+------+-------+-------
 pg_temp_3 | x    | table | hs
(1 row)

tmp=# q
iMac:~ hs$ psql tmp
psql (12.3)
Type "help" for help.

tmp=# d
Did not find any relations.

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:

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

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:

tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DELETE ROWS AS 
	SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# SELECT * FROM x;
 y 
---
 1
 2
 3
 4
 5
(5 rows)

tmp=# COMMIT;
COMMIT
tmp=# SELECT * FROM x;
 y 
---
(0 rows)

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 us drop the table for now:

tmp=# DROP TABLE x;
DROP TABLE

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

tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DROP AS 
	SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# COMMIT;
COMMIT
tmp=# SELECT * FROM x;
ERROR:  relation "x" does not exist
LINE 1: SELECT * FROM x;

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:

tmp=# BEGIN;
BEGIN
tmp=# CREATE TEMP TABLE x ON COMMIT DROP AS SELECT * FROM generate_series(1, 5) AS y;
SELECT 5
tmp=# DECLARE mycur CURSOR WITH HOLD FOR SELECT * FROM x;
DECLARE CURSOR
tmp=# COMMIT;
COMMIT
tmp=# FETCH ALL FROM mycur;
 y 
---
 1
 2
 3
 4
 5
(5 rows)

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:

tmp=# SHOW temp_buffers;
 temp_buffers 
--------------
 8MB
(1 row)

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.

The post PostgreSQL: Sophisticating temporary tables appeared first on Cybertec.