Infrastructure at your Service

Daniel Westermann

Can I do it with PostgreSQL? – 9 – Temporary tables

It has been quite a while since the last posts in this series, so here is what we looked at until now:

In this post we’ll look at temporary tables. Temporary tables hold data for the scope of a session or a transaction and the data is lost afterwards, so what are they good for then? Usually you will use them to store intermediate results that you need for further processing without the need to permanently store these. A typical use case is a business report that requires some intermediate aggregations. Can we do this in PostgreSQL? Yes, of course. Lets go.

When we look at the create table syntax the keywords TEMP or TEMPORARY are there:

([email protected][local]:5439) [postgres] > \h create table
Command:     CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

Then we should be able to create a temporay table, shouldn’t we?

([email protected][local]:5439) [postgres] > create temporary table tmp1 ( a int, b varchar(10));
CREATE TABLE
([email protected][local]:5439) [postgres] > \d tmp1
           Table "pg_temp_2.tmp1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 

Easy. Have you looked at the schema it got created in? What is pg_temp_2? I do not have schema that is named pg_temp_2:

([email protected][local]:5439) [postgres] > \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

Temporay tables get created in a special schema. You can see it if you query pg_namespace directly:

([email protected][local]:5439) [postgres] > select nspname from pg_namespace where nspname = 'pg_temp_2';
  nspname  
-----------
 pg_temp_2
(1 row)

Important to know: When you exit from your session the temporary table is gone:

([email protected][local]:5439) [postgres] > \d tmp1
           Table "pg_temp_2.tmp1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 

([email protected][local]:5439) [postgres] > \q
[email protected]:/home/postgres/ [PG961] psql postgres
psql (9.6.1 dbi services build)
Type "help" for help.

([email protected][local]:5439) [postgres] > \d tmp1
Did not find any relation named "tmp1".

What about the visibiliy of the data in a temporary table? It depends on how you create the table. When you want the data
to be visible for the entire lifetime of the session you do it like above:

([email protected][local]:5439) [postgres] > create temporary table tmp1 ( a int, b varchar(10));
CREATE TABLE
([email protected][local]:5439) [postgres] > insert into tmp1 values (1,'1');
INSERT 0 1
([email protected][local]:5439) [postgres] > select * from tmp1;
 a | b 
---+---
 1 | 1
(1 row)

When you want the data to be visible only for the duration of the current transaction you do it like this:

([email protected][local]:5439) [postgres] > create temporary table tmp2 ( a int, b varchar(10)) on commit delete rows;
CREATE TABLE
([email protected][local]:5439) [postgres] > begin;
BEGIN
([email protected][local]:5439) [postgres] > insert into tmp2 values (1,'1');
INSERT 0 1
([email protected][local]:5439) [postgres] > end;
COMMIT
([email protected][local]:5439) [postgres] > select count(*) from tmp2;
 count 
-------
     0
(1 row)

Another important point to know: When you create a temporary table with the same name as an existing table you will not see
the existing table anymore unless you prefix the normal table with the schema name:

([email protected][local]:5439) [postgres] > create table tmp3 ( a int );
CREATE TABLE
([email protected][local]:5439) [postgres] > create temporary table tmp3 ( a int, b varchar(10));
CREATE TABLE
([email protected][local]:5439) [postgres] > \d tmp3
           Table "pg_temp_2.tmp3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(10) | 

([email protected][local]:5439) [postgres] > \d public.tmp3
     Table "public.tmp3"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

Be careful with this. What about performance? Is a temporary table faster to insert than a normal table?

([email protected][local]:5439) [postgres] > create table tmp1 ( a int, b varchar(10) );
CREATE TABLE
([email protected][local]:5439) [postgres] > create temporary table tmp2 ( a int, b varchar(10) );
CREATE TABLE

This is the script:

([email protected][local]:5439) [postgres] > \! cat a.sql
truncate tmp1;
truncate tmp2;
with aa as
( select generate_series ( 1, 1000000 ) a  )
insert into tmp1 (a,b)
       select a, a
         from aa;

with aa as
( select generate_series ( 1, 1000000 ) a  )
insert into tmp2 (a,b)
       select a, a
         from aa;

This is the result:

([email protected][local]:5439) [postgres] > \i a.sql
TRUNCATE TABLE
Time: 19.626 ms
TRUNCATE TABLE
Time: 35.665 ms
INSERT 0 1000000
Time: 7777.034 ms
INSERT 0 1000000
Time: 3327.984 ms
([email protected][local]:5439) [postgres] > \i a.sql
TRUNCATE TABLE
Time: 46.221 ms
TRUNCATE TABLE
Time: 33.286 ms
INSERT 0 1000000
Time: 7425.957 ms
INSERT 0 1000000
Time: 3241.140 ms
([email protected][local]:5439) [postgres] > \i a.sql
TRUNCATE TABLE
Time: 44.365 ms
TRUNCATE TABLE
Time: 35.992 ms
INSERT 0 1000000
Time: 8732.566 ms
INSERT 0 1000000
Time: 3888.795 ms

The temporary table is almost double as fast to write to than the normal table. Of course you can create indexes on temporary tables as well:

([email protected][local]:5439) [postgres] > create temporary table tmp4 ( a int, b varchar );
CREATE TABLE
([email protected][local]:5439) [postgres] > create index tmpi1 on tmp4(a);
CREATE INDEX

.. and of course the index is gone as well once you end your session.

Another point to remember: Temporary tables are not visible to the vacuum deamon. You might think this is not a problem as they disappear anyway but remember that autovacuum is responsible for gathering the statistics (kicking off analyze) as well.
Depending on what you want to do with the data you loaded into the temporary table it might be wise to issue a manual analyze on it:

([email protected][local]:5439) [postgres] > analyze verbose tmp4;

I hope I do not need to say that tempoary tables are not crash safe 🙂 Have fun …

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure