PostgreSQL Temporary and Unlogged Tables Cheat Sheet

CREATE TEMP TABLE work_items (
  item_id bigint PRIMARY KEY,
  customer_id bigint NOT NULL,
  payload jsonb NOT NULL
) ON COMMIT DELETE ROWS;

BEGIN;
INSERT INTO work_items (item_id, customer_id, payload)
VALUES (1001, 42, '{"op":"reprice"}');
COMMIT;
-- The definition remains in this session; its rows were cleared at COMMIT.

BEGIN;
CREATE TEMP TABLE recent_orders
  ON COMMIT DROP
AS
SELECT order_id, customer_id, total
FROM app.orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

ALTER TABLE recent_orders ADD PRIMARY KEY (order_id);
COMMIT;

CREATE TEMP TABLE staged_orders (
  LIKE app.orders
    INCLUDING DEFAULTS
    INCLUDING GENERATED
    INCLUDING CONSTRAINTS
) ON COMMIT DROP;

SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_schema = pg_my_temp_schema()::regnamespace::text
  AND table_name = 'staged_orders'
ORDER BY ordinal_position;

CREATE INDEX work_items_customer_idx ON work_items (customer_id);
ANALYZE work_items;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT w.item_id, c.segment
FROM work_items AS w
JOIN app.customers AS c USING (customer_id)
WHERE c.active;

REVOKE TEMPORARY ON DATABASE appdb FROM PUBLIC;
GRANT TEMPORARY ON DATABASE appdb TO batch_worker;

-- This remains the permanent table even if the session creates pg_temp.orders.
SELECT order_id, total
FROM app.orders
WHERE status = 'pending';

SELECT current_schemas(true), pg_my_temp_schema();

SET temp_buffers = '64MB';

BEGIN;
CREATE TEMP TABLE request_keys (
  key bigint PRIMARY KEY
) ON COMMIT DROP;
INSERT INTO request_keys (key) VALUES (101), (205), (309);
SELECT a.* FROM app.accounts AS a JOIN request_keys AS r ON r.key = a.account_id;
COMMIT;

CREATE UNLOGGED TABLE app.ingest_buffer (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_id text NOT NULL,
  received_at timestamptz NOT NULL DEFAULT now(),
  payload jsonb NOT NULL,
  UNIQUE (source_id)
);

COMMENT ON TABLE app.ingest_buffer IS
  'Rebuildable staging data; empty after crash recovery and absent from standbys';

BEGIN;
SET LOCAL lock_timeout = '5s';
LOCK TABLE app.ingest_buffer IN ACCESS EXCLUSIVE MODE;
ALTER TABLE app.ingest_buffer SET LOGGED;
COMMIT;

SELECT c.relpersistence
FROM pg_class AS c
WHERE c.oid = 'app.ingest_buffer'::regclass;

SELECT n.nspname AS schema_name, c.relname,
       CASE c.relpersistence
         WHEN 'p' THEN 'permanent'
         WHEN 'u' THEN 'unlogged'
         WHEN 't' THEN 'temporary'
       END AS persistence,
       pg_total_relation_size(c.oid) AS total_bytes
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND c.relpersistence <> 'p'
ORDER BY total_bytes DESC;

# Default: include unlogged table and sequence data.
pg_dump --format=custom --file=appdb.dump appdb

# Schema remains, but omit rebuildable unlogged data.
pg_dump --format=custom --no-unlogged-table-data --file=appdb-without-stage.dump appdb