skip to content

In PostgreSQL 18, why is CREATE UNLOGGED TABLE clicks (...) PARTITION BY RANGE (clicked_at) rejected, and where do UNLOGGED and storage settings live instead?

level: middleimportance: nice to knowfreq 22%

answer

  1. no storage, nothing to log
  2. 17 accepted it silently
  3. leaf partitions are ordinary tables
  4. storage parameters per leaf

basics

~10 s

Since PostgreSQL 18 a partitioned table cannot be unlogged: it has no storage, and the flag never reached its partitions anyway. Persistence, like fillfactor and autovacuum settings, is set on each leaf partition.

solid answer

~40 s

PostgreSQL 18 rejects it with `ERROR: partitioned tables cannot be unlogged` (SQLSTATE `0A000`), and `ALTER TABLE clicks SET LOGGED` or `SET UNLOGGED` on the parent is refused as unsupported for partitioned tables. Before 18 both were accepted and did nothing: `SET [UN]LOGGED` on the parent was a no-op, and an unlogged partitioned table did not make its partitions unlogged, so people believed they were skipping WAL when they were not. The parent has no data files, so persistence belongs to the **leaf partitions**, which are ordinary tables: `CREATE UNLOGGED TABLE clicks_2026_10 PARTITION OF clicks ...` is accepted. Storage parameters such as `fillfactor` and `autovacuum_*` follow the same rule. 18's `pg_dump` drops `UNLOGGED` from partitioned tables, and pg_partman sets it per child through its template table.

code

sql · 14 lines
sql
CREATE UNLOGGED TABLE clicks (click_id bigint NOT NULL, clicked_at timestamptz NOT NULL)
    PARTITION BY RANGE (clicked_at);
-- ERROR:  partitioned tables cannot be unlogged

CREATE TABLE clicks (click_id bigint NOT NULL, clicked_at timestamptz NOT NULL)
    PARTITION BY RANGE (clicked_at);

CREATE UNLOGGED TABLE clicks_2026_10 PARTITION OF clicks
    FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00');

ALTER TABLE clicks_2026_10 SET (fillfactor = 100);  -- leaf partition: accepted

ALTER TABLE clicks SET (fillfactor = 100);
-- ERROR:  cannot specify storage parameters for a partitioned table

go deeper

for a junior

Know that in PostgreSQL 18 a partitioned parent cannot be UNLOGGED, and that UNLOGGED and storage settings go on individual partitions.

for a middle

Explain why: the parent has no storage, the pre-18 flag was a silent no-op, and leaf partitions are ordinary tables with their own persistence and storage parameters.

for a senior

Audit upgraded schemas for parents that were believed to be unlogged, weigh per-partition crash and standby behaviour, and set autovacuum parameters on the partitions that need them.

for a principal

Decide which partitions, if any, may trade durability for write speed, and make that a documented property of the partitioning policy rather than an accident of DDL.

## What PostgreSQL 18 changed - `CREATE UNLOGGED TABLE clicks (...) PARTITION BY RANGE (clicked_at)` fails with `ERROR: partitioned tables cannot be unlogged`, SQLSTATE `0A000` (`feature_not_supported`). - `ALTER TABLE clicks SET LOGGED` and `ALTER TABLE clicks SET UNLOGGED` on the partitioned table fail with a DETAIL saying the operation is not supported for partitioned tables. - **Before 18** both were accepted and neither did anything useful: `ALTER TABLE ... SET [UN]LOGGED` on the parent did nothing, and creating an unlogged partitioned table did not cause its partitions to be unlogged. The release turned a silent no-op into an error. Anyone who wrote `UNLOGGED` on the parent of a high-volume clickstream believed it was skipping WAL; its partitions were being WAL-logged all along. ## Why the parent cannot carry the flag **UNLOGGED** is a property of a table's storage. An unlogged table's data is not written to the write-ahead log, which makes writes faster; in exchange it is truncated after a crash or unclean shutdown and its contents are not replicated to standbys. A partitioned table has **no data files**, so there is nothing to log or not log. Persistence lives on each **leaf partition**, which is an ordinary table. ## Where properties live in a partition tree | Property | On the partitioned parent | On a leaf partition | |---|---|---| | `UNLOGGED` / `LOGGED` | rejected since 18 | yes, per partition | | storage parameters such as `fillfactor`, `autovacuum_*` | rejected: `cannot specify storage parameters for a partitioned table` | yes, per partition | | indexes, constraints, row-level triggers | yes; cloned onto existing and future partitions | extra ones allowed | | `CHECK` and `NOT NULL` | yes; always inherited, `NO INHERIT` not allowed | additional ones allowed | | column `DEFAULT` | yes; used for rows inserted through the parent | allowed, but used only for inserts aimed at the partition directly | | columns | yes | no extra columns | ## Doing it in practice 1. Create the parent as a normal partitioned table. 2. Make the partitions that should skip WAL unlogged individually, either at creation with `CREATE UNLOGGED TABLE clicks_2026_10 PARTITION OF clicks FOR VALUES FROM (...) TO (...)` or later with `ALTER TABLE clicks_2026_10 SET UNLOGGED`. 3. Accept the cost per partition: after a crash that partition comes back **empty** while the others keep their data, and on a streaming standby a query that has to read it fails with `cannot access temporary or unlogged relations during recovery`. 4. Set storage parameters the same way, partition by partition, for example a lower `autovacuum_vacuum_scale_factor` on the current month only. Unlogged partitions suit data you can rebuild, such as a derived rollup; for raw clicks that must survive a crash they are rarely the right choice. Using unlogged tables as a bulk-load staging area is a separate loading technique. ## Auditing what you actually have The catalog tells the truth regardless of what the DDL scripts intended. In `pg_class`, `relkind` is `p` for a partitioned table and `r` for an ordinary table such as a leaf partition, and `relpersistence` is `p` for permanent and `u` for unlogged. A query over `pg_partition_tree('clicks')` joined to `pg_class` lists every member with both columns, which is the quickest way to confirm, after an upgrade or a template change, which months really skip WAL. ## Upgrades and tooling - **pg_dump** in 18 omits the `UNLOGGED` keyword when it dumps a partitioned table, so a schema from 17 whose parent was marked unlogged restores cleanly; leaf partitions keep their own persistence. - **pg_partman** manages the unlogged state of new children through its **template table**, because core no longer lets the parent hold it; its documentation notes that on 18 the flag is removed from parent tables but can still be set on the lowest-level children. Template properties apply to children created after you change them, not retroactively.

  • What happens to an unlogged leaf partition of clicks after a crash, and on a standby?
    Crash recovery truncates it, so that partition comes back empty while logged partitions keep their rows. Its contents are never streamed to standbys, and a query on a standby that has to read it fails with `cannot access temporary or unlogged relations during recovery`.
  • A 17 schema marked its partitioned parent UNLOGGED. What happens when you move it to 18?
    PostgreSQL 18's pg_dump leaves the `UNLOGGED` keyword out for partitioned tables, so the parent is recreated as an ordinary partitioned table and the restore succeeds. Each leaf partition keeps whatever persistence it actually had, which is usually logged, because the parent's flag never reached them in 17.

  • Marking the parent UNLOGGED in PostgreSQL 17 made every partition unlogged.
  • PostgreSQL 18 forbids unlogged partitions at every level of the tree.
  • Setting fillfactor on the partitioned parent cascades to all partitions.
  • A DEFAULT declared on a partition applies to rows inserted through the parent.
  • ALTER TABLE SET UNLOGGED on the parent is the quick way to stop WAL for a whole partition tree.

Assumes PostgreSQL 18, which disallowed unlogged partitioned tables; in 17 and earlier the same statements were accepted but had no effect on the partitions.