Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 90 ยท Schema Design and Constraints

Temporary and Unlogged Tables

Learn PostgreSQL TEMP and UNLOGGED tables: lifetime, ON COMMIT, crash behavior, replication limits, name shadowing and when each is the right tool.

Not every table has to live forever. Some data is a scratch pad for one report, some is a staging area for a nightly import, and some is a cache that can be rebuilt in seconds. Treating all of it like precious business data costs you write-ahead log traffic, vacuum work and disk space. PostgreSQL gives you two ways to relax the rules: temporary tables, which are private and disappear, and unlogged tables, which are shared but not crash-safe.

They are easy to confuse, because both skip some of the durability machinery. This lesson explains exactly what each one skips, how they behave on crash and on replicas, and how to choose. The PostgreSQL behavior needs a real server, so those blocks are read-only. The live examples use SQLite's TEMP tables, which share the same name-shadowing and staging ideas.

Two ways to skip durability

TEMP Private to one session. Gone when it ends.
UNLOGGED Shared by everyone. Emptied after a crash.
Neither writes WAL Less I/O, but no replica copy.

Choose by who must see the data, and how long.

1 / 2

Temporary tables

A temporary table exists only for the session that created it. Other sessions cannot see it, even if they run as the same user, and PostgreSQL drops it automatically when the session ends.

SQL
-- PostgreSQL (read-only)
CREATE TEMP TABLE recent_orders (
  order_id bigint,
  total    numeric(10,2)
);

-- Or create and fill it in one step
CREATE TEMP TABLE big_spenders AS
SELECT customer_id, sum(total) AS spent
FROM orders
WHERE placed_at >= now() - interval '30 days'
GROUP BY customer_id
HAVING sum(total) > 1000;

Use TEMPORARY or TEMP. The table lives in a special per-session schema (named like pg_temp_3), and you can refer to it as pg_temp.big_spenders.

How long does it last?

By default a temporary table lasts until the end of the session, which can be a long time on a pooled connection. Control the lifetime with ON COMMIT:

SQL
-- PostgreSQL (read-only)
CREATE TEMP TABLE batch (id bigint) ON COMMIT DELETE ROWS;   -- keep the table, empty it at each commit
CREATE TEMP TABLE once  (id bigint) ON COMMIT DROP;          -- drop it at the end of the transaction
CREATE TEMP TABLE keep  (id bigint) ON COMMIT PRESERVE ROWS; -- the default

ON COMMIT DROP is the tidy choice for work inside a single transaction, such as a function or an import step. It also avoids a problem with connection pools: a session that is reused by a different client would otherwise still own the old temp table.

What temp tables skip

  • No write-ahead logging. Changes are not written to the WAL, so inserting into a temp table is cheaper than inserting into a normal table. There is nothing to recover after a crash, because the table disappears anyway.
  • Local buffers. Data is cached in the session's own memory (temp_buffers, 8 MB by default per session) and spills to disk files beyond that. Files can be placed on a different disk with temp_tablespaces (see Tablespaces and Physical Storage).
  • No replication. A temporary table exists only on the primary, in one session. Streaming replicas never see it.
  • No autovacuum or autoanalyze. Background workers cannot see other sessions' temp tables. If you fill a temp table with thousands of rows and then join it, run ANALYZE big_spenders; yourself so the planner knows its size. Without statistics, plans can be poor.
  • No parallel query access. Parallel workers cannot read temp tables, so a query that touches one will not use parallelism for that part.

Indexes and constraints work as usual on temp tables, and you can create an index after loading, as described in Bulk Loading.

Name shadowing: the classic trap

A temporary table lives in its own schema, which PostgreSQL searches first unless you place pg_temp somewhere else in search_path. A temp table with the same name as a permanent table therefore hides it:

SQL
-- PostgreSQL (read-only)
CREATE TABLE settings (key text, value text);             -- permanent
CREATE TEMP TABLE settings (key text, value text);        -- same name, in pg_temp

SELECT * FROM settings;            -- reads the TEMP table
SELECT * FROM public.settings;     -- reads the permanent table

This is useful for testing, and dangerous in production. A forgotten temp table can make a query silently read the wrong data, and a malicious user with the right to create temp tables could try to shadow an unqualified table name used inside a function. Qualify names in functions (see Roles and Privileges) and revoke TEMPORARY on databases where roles should not create them: REVOKE TEMPORARY ON DATABASE shop FROM PUBLIC;.

SQLite has the same rule: its temp tables live in the temp schema, which is searched before main. Run the example.

SQL A temp table shadows a permanent one

Press Run (or Ctrl + Enter) to see the result here.

The unqualified query returns only the single temp row, even though the permanent table has two. This is exactly the surprise to avoid in real code.

A temp table as a working area

The most common use of a temp table is to break a big job into steps: collect the rows once, then use them several times. This avoids repeating an expensive query and lets you inspect the intermediate result. The example below computes a cleaned set of orders into a temp table, then reports on it twice.

SQL Temp table as an intermediate result

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL a CTE (WITH ... AS) or a subquery often does the same job without creating a table. Prefer them for a one-off use. Reach for a temp table when you need to use the result several times, add an index to it, or analyze it.

Unlogged tables

An unlogged table is a normal, permanent-looking table that skips the write-ahead log:

SQL
-- PostgreSQL (read-only)
CREATE UNLOGGED TABLE page_cache (
  url        text PRIMARY KEY,
  body       text,
  fetched_at timestamptz DEFAULT now()
);

Unlike a temp table, an unlogged table is visible to every session, keeps its data when sessions end, and survives a normal shutdown and restart. It is only emptied by a crash (or an unclean shutdown), because without WAL, PostgreSQL cannot repair a half-written table. On recovery it truncates the table and its indexes back to empty, using a small _init file as the template.

The behavior in one list:

  • Faster writes. No WAL means less disk I/O, so loads and updates are often several times faster than on a logged table. They are not free: the data still goes through shared buffers and checkpoints.
  • Truncated after a crash. Never keep the only copy of anything here. Rebuild the contents from the real source after a restart.
  • Not replicated. There is no WAL to ship, so streaming replicas hold an empty copy that cannot be queried, and logical replication cannot publish it. After a failover to a replica the table is empty.
  • Not in point-in-time recovery. Restoring from a base backup plus WAL does not bring back its data.
  • References. A permanent table cannot have a foreign key to an unlogged table, since the parent could vanish. (The other direction is allowed.)
  • Indexes follow the table. Indexes on an unlogged table are unlogged too. Sequences owned by an unlogged table can be unlogged as well in recent versions.

You can switch a table later:

SQL
-- PostgreSQL (read-only)
ALTER TABLE page_cache SET LOGGED;     -- rewrites the table and writes it to the WAL
ALTER TABLE page_cache SET UNLOGGED;   -- rewrites the table, stops WAL for it

Both forms rewrite the whole table and take an ACCESS EXCLUSIVE lock, so treat the choice as a design decision made up front, not a tuning knob you flip casually.

Which one should I use?

Question TEMP UNLOGGED Normal
Who can see it? Only the creating session Everyone with privileges Everyone with privileges
Survives a clean restart? No, dropped at session end Yes Yes
Survives a crash? Not applicable No, truncated Yes
On replicas? No Empty and unreadable Yes
Written to WAL? No No Yes
Autovacuum? No, run ANALYZE yourself Yes Yes
Best for Scratch data inside one job Staging data and rebuildable caches Everything you cannot lose

Practical guidance:

  • Import staging in a single script? Use a temp table, or ON COMMIT DROP inside a transaction.
  • A multi-step import with several sessions, or loads that take hours? Use an unlogged staging table, then copy validated rows into the real table. See Bulk Loading for the full flow.
  • A cache, rate-limit counters or throw-away session data? An unlogged table is a good fit if you can regenerate it. If the data matters, use a normal table or a dedicated store.
  • Behind a transaction-pooling proxy? Temp tables are tied to a server connection, which a different client may get next. Use ON COMMIT DROP, or avoid temp tables (see Connection Pooling).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which table type is visible to every session but loses its rows after a crash?

    An unlogged table is shared and survives a clean restart, but PostgreSQL truncates it during crash recovery because it has no WAL.
  2. 2. What does ON COMMIT DROP do for a temporary table?

    ON COMMIT DROP removes the table when the transaction ends. ON COMMIT DELETE ROWS keeps the table and empties it.
  3. 3. You create TEMP TABLE users that has the same name as a permanent users table, and run SELECT * FROM users. What do you get?

    The temporary schema is searched before the others unless search_path says otherwise. Use public.users to reach the permanent table.
  4. 4. Why should you run ANALYZE on a large temporary table before joining it?

    Background autovacuum workers skip temp tables, so the planner has no statistics unless you gather them yourself.
  5. 5. What happens to an unlogged table on a streaming replica?

    No WAL is produced for the table, so there is nothing for the replica to replay.

Summary

  • A temp table is private to one session, never WAL-logged, dropped automatically (or at commit with ON COMMIT DROP), and needs manual ANALYZE.
  • A temp table can hide a permanent table of the same name because pg_temp is searched first.
  • An unlogged table is shared and faster to write, survives clean restarts, but is truncated by a crash and is not replicated.
  • SET LOGGED and SET UNLOGGED rewrite the table under an exclusive lock.
  • Use temp tables for scratch work in one job, unlogged tables for rebuildable staging and caches, and normal tables for everything else.

For sequences that give your tables their keys, read Sequences in Depth.