PostgreSQL Regular vs Unlogged vs Temporary Tables: Which Should You Use?

PostgreSQL gives you three table durability choices that look similar in SQL but behave very differently in production: regular tables, unlogged tables, and temporary tables. The wrong choice can turn a harmless restart into data loss, make a failover return incomplete results, or leave a connection-pooled application unable to find its own staging data.

Regular tables are the safe default because their changes participate in write-ahead logging, crash recovery, backups, and physical replication. Unlogged tables trade those guarantees for lower write-ahead log volume. Temporary tables go further by making both the data and the table definition session-scoped. This guide explains where each option fits, what fails during a crash or failover, and how to test the choice before using it in a managed PostgreSQL workload.

Regular, unlogged, and temporary tables at a glance

The fastest way to choose is to start with the lifetime and durability of the data. If the application cannot recreate the rows, use a regular table. If many sessions need a shared scratch area and every row can be rebuilt, an unlogged table may fit. If only one database session needs the data, a temporary table is usually the clearer boundary.

This is a durability comparison, not a benchmark. PostgreSQL documentation says unlogged tables avoid WAL writes and can therefore be considerably faster than ordinary tables, but the actual benefit depends on the workload, storage, indexes, checkpoints, and whether the job is limited by WAL in the first place. Measure the complete workflow rather than assuming the table keyword will remove the bottleneck.

Why regular tables should remain the default

A regular PostgreSQL table is durable because changes are represented in the write-ahead log before the database reports the relevant commit as successful. WAL supports crash recovery and is also the stream used by physical standby servers. That makes ordinary tables the correct choice whenever a committed row must still exist after a process crash, host restart, failover, or restore.

Use a regular table for source-of-truth application data even when the rows feel temporary from a product perspective. A job queue may retain items for only minutes, but losing an acknowledged payment job after a server crash is still data loss. A cache table may be derivable, but a cold rebuild could overload an upstream service or leave the product unavailable for hours. Durability is about the recovery promise, not the planned retention period.

Regular tables also fit connection pools naturally. Any authorized session can see them, and PgBouncer can move transactions between server connections without changing whether the relation exists. If a workflow needs shared staging data across workers, a regular table with a run identifier and an explicit retention policy is often simpler than relying on session state.

What unlogged tables actually trade away

An unlogged table is a persistent database object, but its data changes are not written to WAL. PostgreSQL therefore cannot recover its contents after a crash in the same way it recovers a normal table. The official CREATE TABLE documentation is explicit: an unlogged table is automatically truncated after a crash or unclean shutdown. Its contents are not replicated to standby servers, and its indexes are unlogged as well.

That behavior can be useful for a shared, rebuildable work area. Consider a reporting service that periodically expands durable source rows into a large denormalized staging table. Several workers need to read the result, so a session-local temporary table is not enough. If the service already knows how to rebuild the entire staging set and can tolerate an empty table after failover, unlogged storage can reduce WAL pressure.

The important word is rebuildable. Do not use unlogged tables for jobs that have been accepted but not completed, user uploads that have not reached durable storage, idempotency keys, migration checkpoints, or any data whose absence would be interpreted as a valid empty state. After a crash, the schema remains while the rows disappear. An application that does not distinguish “rebuild in progress” from “there is no data” can serve incorrect results without raising an error.

Unlogged tables also do not solve every bulk-load problem. If the destination must eventually become durable, you still need a controlled copy into a regular table and a clear commit boundary. For one-time loads, it may be better to load directly into a regular staging table, tune the import path, validate row counts, and remove the staging data afterward.

Temporary tables are scoped to a database session

A temporary table belongs to the session that creates it. Other sessions cannot use it, and PostgreSQL can drop it automatically at the end of the session or earlier through an ON COMMIT rule. Temporary tables are well suited to multi-step transformations where every step runs through the same connection.

That connection requirement is easy to miss in web applications. With PgBouncer transaction pooling, one application transaction can use a different PostgreSQL server connection from the next. Creating a temporary table in one transaction and querying it in a later transaction is therefore unsafe unless the application deliberately pins the workflow to one server session. Session pooling preserves the connection boundary, but it reduces the multiplexing benefit that often motivated PgBouncer in the first place.

Temporary tables also need planner statistics for non-trivial queries. PostgreSQL's routine vacuuming documentation notes that autovacuum cannot access temporary tables. After loading a meaningful amount of data, the creating session may need to run ANALYZE explicitly so the planner sees realistic row counts and value distribution.

A good temporary-table workflow keeps the whole operation inside one checked-out database connection:

BEGIN;
CREATE TEMP TABLE candidate_accounts (
  account_id bigint PRIMARY KEY
) ON COMMIT DROP;

INSERT INTO candidate_accounts
SELECT id
FROM accounts
WHERE last_seen_at < now() - interval '180 days';

ANALYZE candidate_accounts;

UPDATE accounts
SET review_required = true
FROM candidate_accounts
WHERE accounts.id = candidate_accounts.account_id;
COMMIT;

The table is an implementation detail of this unit of work. If the process disconnects, the temporary relation disappears instead of leaving shared cleanup work behind.

How replication and failover change the decision

A high-availability design makes the difference between logged and unlogged data concrete. Physical standbys reproduce changes carried in WAL. Because unlogged-table contents are not WAL-logged, a promoted standby does not have those rows. Temporary tables are session-local and are not failover state either.

Suppose an application writes durable source events into a regular table and maintains an unlogged search-preparation table. After failover, the source events remain available on the promoted standby, while the derived table needs a rebuild. That can be a sound design if the application detects the empty or stale derived state, starts a bounded rebuild, and degrades safely while the rebuild runs.

The same design is unsafe when the unlogged table contains the only copy of accepted work. A failover might appear successful at the database layer while the product silently loses pending tasks. Before adopting unlogged storage, write down the exact recovery source, expected rebuild time, concurrency control, and user-visible behavior during rebuild.

Logical replication has a similar boundary. PostgreSQL publications accept persistent base tables, not temporary or unlogged tables. If a future migration, change-data-capture pipeline, or analytics subscriber may need the data, choosing an unlogged table today creates extra migration work tomorrow.

A practical decision checklist

Start by asking whether every row can be recreated from a durable source. If not, choose a regular table. If it can, ask whether the data must be shared between sessions. Session-local data points toward a temporary table; shared derived data may justify an unlogged table. Then test the operational failure, not only the happy path.

For an unlogged candidate, load representative data, terminate PostgreSQL uncleanly in an isolated environment, restart it, and confirm the application detects and rebuilds the empty table. Repeat the exercise through a standby promotion if production uses failover. Measure rebuild time and upstream load. A recovery path that works on ten thousand rows may become an incident on a hundred million.

For a temporary-table candidate, test with the same driver and PgBouncer mode used in production. Confirm that all statements run on one checked-out connection, that cleanup happens after success and failure, and that ANALYZE is called when the temporary dataset affects meaningful joins. Also test retries: a transaction retry may start on a new server session where the temporary table does not exist.

Common mistakes to avoid

The first mistake is treating unlogged as “less important but still persistent.” Its definition is stricter: the table object persists, but its contents can be cleared after an unclean shutdown and are absent from standbys. Build the application around that fact.

The second is using temporary tables across request boundaries. Connection pools make session ownership invisible unless the application explicitly controls it. If multiple requests or workers need the same rows, use a shared table with a run identifier instead.

The third is choosing based on a synthetic insert benchmark. Lower WAL volume can help, but a real workflow includes source reads, index maintenance, validation, transformation, cleanup, and sometimes a final durable copy. Benchmark the end-to-end job and include recovery cost.

The fourth is forgetting operational tooling. Monitoring, backup validation, replica reads, CDC, and incident queries may all assume a regular table. A local performance win is not useful if it makes recovery or observability ambiguous.

Takeaway

Use regular tables unless the data lifetime gives you a precise reason not to. Choose an unlogged table only for shared data that is fully rebuildable and safe to lose during crash or failover. Choose a temporary table for session-local intermediate work, and keep the entire workflow on one database connection.

The best design documents the failure behavior in the same place as the schema. If the table can disappear, state what rebuilds it, how long that takes, and what users see meanwhile. If those answers are unclear, WAL-backed regular storage is the safer and usually cheaper operational choice.

For managed deployments, pair this decision with a tested restore and failover plan. ArmorDB's backup documentation is a useful starting point for defining what must survive and how recovery is verified.

Sources and further reading

• PostgreSQL documentation: CREATE TABLE
• PostgreSQL documentation: Write Ahead Log
• PostgreSQL documentation: Routine Vacuuming
• PostgreSQL documentation: CREATE PUBLICATION
• PostgreSQL glossary: Unlogged