UNLOGGED Tables in Postgres

PostgreSQL’s UNLOGGED tables offer significant performance advantages by bypassing WAL (Write-Ahead Logging). However, they come with…

UNLOGGED Tables in Postgres

PostgreSQL’s UNLOGGED tables offer significant performance advantages by bypassing WAL (Write-Ahead Logging). However, they come with trade-offs that can impact replication, backup, and durability. This article explores the internals, use cases, limitations, and best practices of UNLOGGED tables. You’ll learn when to use them and how to mitigate their downsides with real-world examples.

An UNLOGGED table is a special kind of table that does not write its changes to the WAL. This reduces disk I/O and boosts performance but at a cost. These tables are not crash-safe, not replicated, and not included in physical backups.

In this guide, we’ll delve into:

The behavior and internals of UNLOGGED tables
Their limitations in physical and logical replication
Backup implications
Performance comparisons
Use cases and anti-patterns
Converting UNLOGGED tables to LOGGED ones

What Are UNLOGGED Tables?

You can create an UNLOGGED table using:

CREATE UNLOGGED TABLE cache_table (
    id SERIAL PRIMARY KEY,
    data TEXT,
    created_at TIMESTAMP DEFAULT now()
);

Key Properties:

No WAL Logging: Changes are not recorded in the WAL.
Crash Volatility: Data is lost after an unclean shutdown.
No Replication: Not streamed to replicas.
No Physical Backup: Skipped by pg_basebackup and similar tools.

How PostgreSQL Handles Them Internally

PostgreSQL maintains a separate fork for UNLOGGED tables, which gets truncated automatically during server startup after a crash. Since these tables don’t produce WAL records, any operations on them are invisible to streaming replicas and WAL-based backup tools.

This design results in errors like the following on replicas:

ERROR:  cannot access temporary or unlogged relations during recovery

This is expected behavior — the replica doesn’t know the table even exists.

Replication Limitations

Streaming Replication

Since streaming replication is WAL-based, UNLOGGED tables are simply not visible on replicas. You can’t even SELECT from them.

Logical Replication

You might think logical replication is a workaround, but PostgreSQL does not allow UNLOGGED tables to be part of a publication. So this is also a dead end.

Bottom Line: There is no replication strategy — physical or logical — that supports UNLOGGED tables in PostgreSQL.

Backup Limitations

Physical Backup

UNLOGGED tables are excluded from WAL and thus from physical backups using tools like:

pg_basebackup
barman
pgBackRest

Logical Backup

To back up UNLOGGED tables, use logical tools:

pg_dump --create --clean --if-exists --format=d --jobs=4 --verbose --file=21042025******.dump postgres://postgres:dummy@**.***.**.***:port/dbname

This ensures at least one form of persistence, which is critical if the data is non-trivial to regenerate.

Performance Benefits

Because WAL logging is skipped, performance improves noticeably for write-heavy operations. Here’s a basic comparison (results will vary):


Operation         LOGGED Table       UNLOGGED Table
INSERT 1M rows    1.6 sec            0.75 sec
TRUNCATE          0.3 sec            0.1 sec

These improvements are especially valuable in temporary or caching scenarios.

Ideal Use Cases

Use UNLOGGED Tables For               Avoid For 
----------------------                ---------
Cache tables                          Financial transactions 
ETL staging or intermediate data      User data or audit trails 
Transient analytics workloads         Critical replicated environments 
Frequently truncated tables           Anything needing crash recovery

Converting UNLOGGED to LOGGED

You can switch an UNLOGGED table to a regular table:

ALTER TABLE cache_table SET LOGGED;

This causes PostgreSQL to log all existing rows and future changes to WAL, so expect a performance hit during the conversion.

Best Practices

Never use UNLOGGED tables for critical or sensitive data.
Always use pg_dump to back up important UNLOGGED data.
Document your reasons for using UNLOGGED tables.
Consider converting to LOGGED if needs change.

Conclusion

UNLOGGED tables offer a high-performance option for specific, non-critical workloads. However, they sacrifice durability, replication, and backup safety. Use them where appropriate such as temporary caches or ETL intermediates but never for data you can’t afford to lose. When performance matters and you understand the risks, UNLOGGED tables can be a powerful tool in your PostgreSQL toolbox. Just use them wisely.