How do you plan PostgreSQL capacity for disk, WAL and sequences?

Treat PostgreSQL capacity as four separate runways, not one: disk, WAL, sequences and transaction IDs (plus MultiXact). Each has a different ceiling, a different query to measure it, and a different lead time. Sample each one on a schedule, fit a slope to the history, and convert the slope into "days until the ceiling" per runway.

Key facts

| Fact | Value | |---|---| | serial / integer sequence ceiling | 2,147,483,647 (2^31 - 1); bigserial is 9,223,372,036,854,775,807 (PostgreSQL docs: serial types) | | XID wraparound cliff | 2^31 (about 2.1 billion) transactions of age; PostgreSQL refuses new transactions when fewer than 3 million remain (routine vacuuming) | | autovacuum_freeze_max_age default | 200,000,000; hard-capped at 2 billion (autovacuum settings) | | autovacuum_multixact_freeze_max_age default | 400,000,000 (MultiXact wraparound) | | max_slot_wal_keep_size default | -1 (unlimited); available since PostgreSQL 13 (replication settings) | | max_wal_size default | 1 GB, a soft limit checkpoints aim for, not a cap on pg_wal (WAL settings) | | temp_file_limit default | -1 (no limit per session) (resource settings) | | Fleet data (MyDBA seo_aggregate_health_stats) | Across 32 monitored instances, 3.1% have a sequence past the exhaustion warning; across 47, 40.4% write temp files above threshold; across 50, 0% currently fail the XID, inactive-slot or archive-health checks |

Why this happens

Disk is the runway that ends an instance most abruptly, but the other three ceilings are invisible on a disk graph. A table can stop accepting inserts on a half-empty volume because its serial column hit 2^31. A database can go read-only with terabytes free because vacuum could not freeze old rows in time. pg_wal can fill a disk while every table stays the same size. Each of these has its own mechanism.

Disk. Data files grow with inserts and with bloat, which is dead-tuple space that vacuum has marked reusable but not returned to the operating system. Sort and hash operations that exceed work_mem spill to temporary files under base/pgsql_tmp; with temp_file_limit at its default of -1 there is no per-session cap (resource settings), so one query plan change can consume tens of gigabytes in minutes. pg_stat_database.temp_bytes counts this spill cumulatively (pg_stat_database). Steady data growth is forecastable; bloat and temp spill are steps, not slopes, and need a ceiling check.

WAL. max_wal_size is the amount of WAL that checkpoints aim to keep between them; it is a soft target, and the docs are explicit that pg_wal can exceed it under heavy load, a failing archive_command, or a high wal_keep_size (WAL configuration). The two ways WAL fills a disk on its own are a replication slot whose consumer has stopped reading, and an archiver that keeps failing. A slot pins every WAL segment after its restart_lsn until the consumer confirms it; with max_slot_wal_keep_size at -1 (the default) there is no bound (max_slot_wal_keep_size). A failing archiver has the same effect: segments cannot be recycled until archived, and pg_stat_archiver records failed_count and last_failed_wal (pg_stat_archiver). Either way the tables do not grow, the disk does.

Sequences. A serial column is an integer backed by a sequence, and an integer tops out at 2,147,483,647 (serial types). A GENERATED ... AS IDENTITY column on an integer has the same ceiling (CREATE TABLE). When the sequence runs out, nextval raises "reached maximum value of sequence" and every insert fails until the column type changes. Sequences also advance on rolled-back inserts and on INSERT ... ON CONFLICT paths that write nothing, so last_value runs ahead of the row count. pg_sequences exposes last_value and max_value per sequence (pg_sequences).

Transaction IDs. XIDs are a 32-bit counter compared modulo 2^32, so any row must be frozen before it is more than about 2 billion transactions old or it becomes invisible. PostgreSQL protects itself: autovacuum forces a freeze on any table older than autovacuum_freeze_max_age (default 200 million), and if the oldest XID in the cluster gets within 3 million of the limit, the server refuses new transactions until a superuser runs VACUUM (preventing wraparound failures). The cliff is not "age reaches 2^31"; it is "vacuum is prevented from freezing". Long-running transactions, idle-in-transaction sessions, prepared transactions and stale replication slots all hold back the freeze horizon. The companion piece on XID wraparound prevention covers the blockers in depth. MultiXact IDs are a second, separate 32-bit counter used when several transactions lock the same row (foreign-key checks, SELECT ... FOR SHARE); they wrap independently and are governed by autovacuum_multixact_freeze_max_age (default 400 million) (MultiXact wraparound).

Lead time differs per runway. Adding disk or dropping a slot takes minutes. Widening an integer primary key to bigint rewrites the table and every index that references it, under an ACCESS EXCLUSIVE lock (ALTER TABLE), which on a large table is a scheduled maintenance window. A forced anti-wraparound vacuum on a multi-terabyte table can run for days. So "days remaining" only means something next to "days to fix".

How to detect it

Run these four queries on a schedule and keep the results: one reading says where you are, the trend says when you arrive.

1. Disk: database size, bloat proxy and temp spill. Pair this with a filesystem reading from the host (df), because PostgreSQL does not know the mount size.

SELECT d.datname,
       pg_size_pretty(pg_database_size(d.datname)) AS size,
       pg_size_pretty(s.temp_bytes)                AS temp_written_total,
       s.temp_files,
       s.stats_reset
FROM pg_database d
JOIN pg_stat_database s ON s.datname = d.datname
WHERE NOT d.datistemplate
ORDER BY pg_database_size(d.datname) DESC;

Sample pg_database_size daily and fit a slope. temp_bytes is cumulative since stats_reset, so subtract consecutive readings to get a rate. The companion piece on checking total database size covers the size functions in detail.

2. WAL: what is on disk, what a slot is retaining, and whether the archiver is stuck.

-- pg_wal on disk
SELECT count(*) AS segments, pg_size_pretty(sum(size)) AS pg_wal_size
FROM pg_ls_waldir();

-- retained WAL per slot (PG13+ for wal_status and safe_wal_size)
SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained,
       pg_size_pretty(safe_wal_size) AS headroom_before_invalidation
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

-- archiver
SELECT archived_count, failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

wal_status is reserved when the slot is healthy, extended when it is already past max_wal_size, unreserved when the next checkpoint may remove needed segments, and lost when it already has (pg_replication_slots). safe_wal_size is NULL when max_slot_wal_keep_size is -1, which means the only ceiling is the mount. A non-zero failed_count with a recent last_failed_time means segments are piling up regardless of slots. The replication slot guide walks through what an inactive slot looks like over time.

3. Sequences: percent used, with the type of the backing column.

SELECT schemaname, sequencename, data_type,
       last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY pct_used DESC
LIMIT 20;

Anything with data_type = integer and pct_used above 50 deserves a migration plan now, because the fix is the slow one. bigint sequences at any realistic rate are effectively unbounded: at one million increments per second, 2^63 takes about 292,000 years.

4. Transaction IDs and MultiXacts: age against the freeze thresholds.

SELECT datname,
       age(datfrozenxid)                              AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 1) AS pct_of_wraparound,
       mxid_age(datminmxid)                           AS multixact_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

xid_age rising past autovacuum_freeze_max_age is normal and expected to be caught by autovacuum. xid_age still rising after it has crossed that threshold is the actual alarm, because it means a freeze is blocked. Check pg_stat_activity for the oldest backend_xmin, pg_prepared_xacts, and pg_replication_slots.xmin to find what is holding it. mxid_age(datminmxid) reads the same way against the 400 million MultiXact default.

How MyDBA shows this

MyDBA's Insights page has a "Capacity & Runway" domain that keeps one card per bounded resource: a disk card per mount on each instance, an XID card and a MultiXact card per database, a sequence card for any sequence already past 50% used, and a slot card for any replication slot with a finite max_slot_wal_keep_size (a slot on unlimited retention is left to the disk card, since the disk is then the real ceiling, and a slot reporting unreserved or lost is marked critical outright). Each card fits a least-squares slope to the last two weeks of the collector's readings and shows the ETA as "N days to limit", "< 1 day to limit" or "> 1 year to limit", with severity set by lead time: critical inside 3 days, warning inside 14, informational inside 90, or immediately when the resource is already past its hard floor (disk at 95%, sequence at 95% used) regardless of trend. The analyzer re-runs every 6 hours and a card disappears once the trend resolves, so it is a current forecast rather than an incident record; the storage and vacuum health checks read the same verdicts, so the dashboard grade and the card ETA cannot disagree. Across the monitored fleet today, 0% of 50 instances fail the XID, inactive-slot or archive-health checks, while 3.1% of 32 instances carry a sequence near exhaustion and 40.4% of 47 are spilling temp files above threshold: the quiet runways are the ones that catch people. The free PostgreSQL health check scores all four runways on one instance with no card required.

How to fix it

Order matters: the fix with the longest lead time is the one to start first, even if its ETA is furthest out.

1. Sequence near 2^31. Widen the column, not just the sequence. ALTER SEQUENCE ... AS bigint on its own only defers the failure to the integer column that receives the value. Run ALTER TABLE t ALTER COLUMN id TYPE bigint; and the same on every foreign-key column that references it. This rewrites the table and its indexes under ACCESS EXCLUSIVE (ALTER TABLE); for large tables, add a new bigint column, backfill in batches, swap the primary key in a short window. If the sequence has genuinely run out and you need minutes of breathing room, ALTER SEQUENCE ... CYCLE or a MINVALUE of -2147483648 will reuse the negative range, at the cost of application assumptions about ordering. 2. XID or MultiXact age climbing past the freeze threshold. Find and end the blocker first: terminate the idle-in-transaction session, commit or roll back the prepared transaction, drop the abandoned slot. Then let autovacuum finish, or run VACUUM (FREEZE, VERBOSE) on the oldest tables ordered by age(relfrozenxid). vacuum_failsafe_age (default 1.6 billion) is the point at which vacuum abandons index cleanup and cost delays to finish faster (vacuum_failsafe_age); if you are near it, do not wait for the next autovacuum cycle. 3. WAL retained by a slot. Confirm the consumer is really gone, then SELECT pg_drop_replication_slot('slot_name');. Segments are recycled at the next checkpoint. If the consumer is merely slow, fix the consumer; dropping the slot forces a full re-sync of that replica or subscriber. 4. WAL retained by a failing archiver. Fix archive_command (permissions, disk on the archive target, credentials), then watch pg_stat_archiver.archived_count advance. Never delete files from pg_wal by hand. 5. Disk from temp spill. Find the query via pg_stat_statements.temp_blks_written, then either fix its plan or raise work_mem for that role or session. Set temp_file_limit so a single runaway sort cannot take the instance down. 6. Disk from bloat. VACUUM reclaims space for reuse but does not shrink the file; VACUUM FULL does, under an exclusive lock, and pg_repack does the same online (routine vacuuming). 7. Disk from data growth. Extend the volume. This is the cheap fix, and the one done in an emergency for problems that were not disk problems.

How to prevent it

• Set max_slot_wal_keep_size to a value the mount can absorb (for example, 20 GB on a 200 GB volume). A slot that exceeds it is invalidated rather than filling the disk, and wal_status tells you it happened (max_slot_wal_keep_size). Alert on any slot with active = false for more than an hour.
• Alert on pg_stat_archiver.failed_count increasing, not just on its value.
• Set temp_file_limit per role to a number below free disk, and alert on the delta of pg_stat_database.temp_bytes per hour.
• Use bigint identity columns for new tables by default. The extra four bytes are cheaper than the migration.
• Alert on age(datfrozenxid) at 80% and 90% of 2^31, and on xid_age still rising for more than an hour after crossing autovacuum_freeze_max_age. That second condition is the real signal.
• Sample all four runways at least daily and keep at least 30 days of history. A slope over a week is noisy; over a month it is usable. The 12 metrics that predict an outage piece places these four alongside the other signals worth keeping. Which tools collect them is covered in the monitoring tools comparison.

FAQ

Can pg_wal fill the disk even if max_wal_size is small?

Yes. max_wal_size is a checkpoint target, not a cap. Anything that prevents a segment from being recycled (a replication slot that has not confirmed it, an archive_command that keeps failing, or wal_keep_size) keeps that segment on disk indefinitely (WAL configuration). The fix is to unblock recycling, not to lower max_wal_size.

Is an integer primary key at 60% used urgent?

It depends on the rate, which is why the reading alone is not enough. At 60% used you have about 860 million values left. At 10,000 inserts per second (including rolled-back inserts, which also consume values) that is roughly one day; at 100 per second it is about 100 days. Because widening the column rewrites the table, start planning at 50% regardless of the rate, and treat the ETA as the deadline for finishing, not for starting.

Why is my XID age above autovacuum_freeze_max_age but nothing is wrong?

Crossing autovacuum_freeze_max_age (default 200 million) is what triggers the anti-wraparound autovacuum in the first place, so age hovering just above it on a busy database is normal. The problem is age that keeps rising after crossing it, which means the freeze is being blocked by a long transaction, an idle-in-transaction session, a prepared transaction or a stale slot. The XID wraparound prevention guide covers how to find and clear each blocker.

Does MultiXact wraparound need separate monitoring from XID wraparound?

Yes. MultiXact IDs are a second 32-bit counter with its own freeze threshold (autovacuum_multixact_freeze_max_age, default 400 million) and its own wrap (MultiXact wraparound). Workloads heavy on foreign keys or SELECT ... FOR SHARE can consume MultiXacts much faster than plain XIDs, so a database can be healthy on age(datfrozenxid) and close to the wall on mxid_age(datminmxid).

Part of the PostgreSQL Monitoring Tools Compared (2026) guide. Last verified against PostgreSQL 18, 2026-09-16.