SQL UNLOGGED

What is an UNLOGGED table in PostgreSQL?

Marks a table or materialized view so that its data is not written to the write ahead log, giving faster writes but no crash-recovery or replication safety.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL UNLOGGED: Supported: PostgreSQL 9.1+ for tables, PostgreSQL 14+ for materialized views. Not supported: MySQL, SQL Server, Oracle, SQLite, Snowflake.

SQL UNLOGGED Full Explanation

In PostgreSQL, the UNLOGGED keyword can be placed before TABLE or MATERIALIZED VIEW in a CREATE statement. Pages modified in an UNLOGGED relation are not written to the write ahead log (WAL). Skipping WAL greatly reduces disk I/O and can improve insert and update throughput, especially for bulk-load or transient data.However, because WAL is what allows PostgreSQL to recover after a crash and to stream changes to replicas, UNLOGGED objects come with two critical caveats:1. Crash survival - After an unexpected server crash or immediate shutdown, the contents of every UNLOGGED relation are automatically truncated to zero rows.2. Replication - UNLOGGED relations are not shipped to physical or logical standbys, so they only exist on the primary server.UNLOGGED is different from TEMPORARY. A TEMPORARY table is scoped to a session, whereas an UNLOGGED table is permanent in the catalog but simply lacks WAL logging. An UNLOGGED table can be converted back to a normal (LOGGED) table with ALTER TABLE SET LOGGED, after which existing data is rewritten and future changes are WAL-logged.Performance benefits are workload-dependent. Write-heavy tables that do not need durability or replication (such as staging, ETL, or caching layers) often see double-digit percentage gains. Read performance is largely unchanged because data still resides on disk buffers.

SQL UNLOGGED Syntax

-- Create an unlogged table
CREATE UNLOGGED TABLE table_name (
    column_definitions
);

-- Convert an existing table to unlogged
ALTER TABLE table_name SET UNLOGGED;

-- Revert to normal logging
ALTER TABLE table_name SET LOGGED;

-- Unlogged materialized view (PostgreSQL 14+)
CREATE UNLOGGED MATERIALIZED VIEW view_name AS
SELECT ...;

SQL UNLOGGED Parameters

Example Queries Using SQL UNLOGGED

-- 1. Staging table for daily imports
CREATE UNLOGGED TABLE staging_events (
    event_id   bigint,
    user_id    int,
    payload    jsonb,
    received_at timestamptz
);

-- 2. Bulk load without WAL pressure
COPY staging_events FROM '/data/events.csv' CSV;

-- 3. Convert back to logged after cleaning
ALTER TABLE staging_events SET LOGGED;

-- 4. Unlogged materialized view for fast refreshes
CREATE UNLOGGED MATERIALIZED VIEW mv_fast AS
SELECT user_id, count(*) AS hits
FROM staging_events
GROUP BY user_id;

Expected Output Using SQL UNLOGGED

PostgreSQL creates a persistent table whose future writes are not stored in WAL.
COPY runs faster because it bypasses WAL.
ALTER TABLE rewrites the data and turns WAL back on.
Materialized view is created but will be truncated after a crash and not replicated.

Use Cases with SQL UNLOGGED

ETL staging tables that are repopulated daily
Caching or scratch tables whose loss is acceptable
High-velocity logging where durability can be traded for speed
Intermediate results used inside the same server session but across multiple transactions

Common Mistakes with SQL UNLOGGED

Assuming UNLOGGED tables survive crashes - they do not.
Expecting data to appear on replicas - it never will.
Mixing UNLOGGED with logical replication publications - they are silently skipped.
Forgetting to switch back to LOGGED before relying on durability.

First Introduced In

PostgreSQL 9.1

Frequently Asked Questions

Does an UNLOGGED table survive a database crash?

No. After a crash or immediate shutdown, PostgreSQL automatically truncates the table because no WAL was written.

Can UNLOGGED tables be replicated to standbys?

No. Because changes are not in WAL, physical and logical replicas never see UNLOGGED data.

Are UNLOGGED tables the same as TEMPORARY tables?

No. TEMPORARY tables are session-scoped but crash-safe, whereas UNLOGGED tables are permanent catalog objects that lose data on crash.

How can I make an existing table UNLOGGED?

Issue ALTER TABLE table_name SET UNLOGGED; PostgreSQL rewrites the table so future modifications bypass WAL.