PostgreSQL Temporary Tables: Session Scope

Tip: Create Temporary Table

CREATE TEMP TABLE temp_results AS
SELECT user_id, sum(amount) as total FROM orders GROUP BY user_id;

Only visible in the current session.

Gotcha: Auto-Dropped on Disconnect

Temporary tables are automatically dropped when the session ends.

Tip: ON COMMIT Options

CREATE TEMP TABLE temp_data (...) ON COMMIT DELETE ROWS;

• PRESERVE ROWS (default) — keep data after commit
• DELETE ROWS — truncate after each commit
• DROP — drop the table after commit

Gotcha: Temp Tables Shadow Permanent Tables

If a temp table has the same name as a permanent table, the temp table takes precedence.

Tip: Index Temp Tables

CREATE INDEX idx_temp_results_total ON temp_results (total);

Speeds up queries on large temporary datasets.

Gotcha: Temp Tables and Connection Pooling

With PgBouncer in transaction mode, temp tables are lost between transactions. Use session mode.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Temporary tables in PostgreSQL are session-isolated and automatically dropped when the session ends. I use them for complex ETL operations that need intermediate staging. An important nuance: temporary tables are not subject to autovacuum — if you modify them heavily within a session, they can bloat. For repeated use within a session, truncate or drop and recreate the temporary table rather than modifying it incrementally.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)