Warning Table Health

Unlogged Table

A table is created as UNLOGGED, meaning data is not written to WAL and will be lost on crash or unclean shutdown.

What is this issue?

Unlogged tables skip WAL (Write-Ahead Log) writes, making them faster for
writes but with significant trade-offs:

- Data is truncated after a crash or unclean shutdown
- Data is not replicated to standby servers
- Point-in-time recovery cannot restore unlogged tables

Valid uses for unlogged tables:
- Session/cache data that can be regenerated
- ETL staging tables
- Temporary materialized views

Why it matters

Data Loss

All data is lost on crash, power failure, or pg_ctl stop -m immediate

No Replication

Standby servers have empty copies of unlogged tables

Failover Impact

After failover, unlogged tables are empty

Backup Limitations

Unlogged table contents aren't in WAL-based backups

How PG Pilot detects it

```sql
SELECT
  schemaname,
  relname,
  pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS size
FROM pg_stat_user_tables
JOIN pg_class ON relname = pg_class.relname
WHERE relpersistence = 'u'
ORDER BY pg_relation_size(schemaname || '.' || relname) DESC;
```

How to fix it

1

Evaluate if unlogged is appropriate

Ask these questions:
- Can this data be regenerated if lost?
- Is this table used for caching or staging?
- Do we need this data on replicas?

If any answer requires durability, convert to logged.

2

Convert to logged table

Change the table to logged (requires exclusive lock):

ALTER TABLE your_table SET LOGGED;

This rewrites the table and generates WAL entries.

3

Document if intentionally unlogged

If the table should remain unlogged, document why:

COMMENT ON TABLE your_table IS
  'Unlogged: Used for session cache, data regenerated on startup';

Prevention

  • Only use UNLOGGED for truly ephemeral data
  • Document why each unlogged table exists
  • Test failover scenarios with unlogged tables
  • Consider using TEMPORARY tables instead for session data

Related Issues

Table Bloat

Related Features

Table Monitoring

PostgreSQL Documentation