Inspect PostgreSQL Sequences with the pg_sequences View

calendar Jun 20, 2026 / · 6 min read · postgresql database administration sql queries sequences pg_sequences catalogs postgres dba  ·
Share on: linkedin copy

Inspect PostgreSQL Sequences with the pg_sequences View

pg_sequences is the catalog-backed view that exposes every sequence in the current database in one readable row each — start_value, min_value, max_value, increment_by, cache_size, cycle, and the all-important last_value. It turns a scattered set of per-sequence currval() lookups into a single audit query.

Purpose and Overview

Sequences are easy to create and easy to forget. Every SERIAL or GENERATED AS IDENTITY column quietly relies on a sequence object to hand out the next number, and most of the time nobody looks at one until an INSERT fails with a "nextval: reached maximum value" error. By then the table is blocked for new rows and the fix has to happen under pressure.

The pg_sequences view solves the visibility problem. It reports the live state of every sequence the current role can see, with the current position (last_value) sitting next to the ceiling (max_value) and the step (increment_by). That adjacency is what makes capacity planning possible: a sequence on an integer column maxes out at 2,147,483,647, and a high-volume table can travel a surprising fraction of that range in a year. Seeing last_value as a percentage of max_value across the whole database is the difference between a scheduled migration to bigint and an outage.

The view also surfaces configuration that rarely gets a second look after CREATE SEQUENCE: whether a sequence will cycle back to its minimum when it hits the ceiling, how large its cache_size is, and what data type bounds it. Each of those settings has correctness implications, and each is invisible from the table definition alone.

Sample Code

 1SELECT
 2    schemaname,
 3    sequencename,
 4    data_type,
 5    last_value,
 6    max_value,
 7    increment_by,
 8    cycle,
 9    round(100.0 * COALESCE(last_value, 0) / max_value, 2) AS pct_used
10FROM
11    pg_sequences
12ORDER BY
13    pct_used DESC NULLS LAST;

Notes: Runs on PostgreSQL 10 and later, where pg_sequences was introduced. last_value is NULL until a sequence has been advanced at least once, so the COALESCE keeps the percentage calculation from collapsing to NULL. The view reports only sequences visible to the current role; run as a superuser or a sufficiently privileged role for a complete inventory.

Code Breakdown

The query is a single scan of pg_sequences with one derived column. The work is in the columns selected and the ordering.

The pct_used Expression

1round(100.0 * COALESCE(last_value, 0) / max_value, 2) AS pct_used

This is the diagnostic core. 100.0 forces floating-point division so the result is a real percentage rather than an integer truncated to 0 or 100. COALESCE(last_value, 0) treats a never-used sequence as sitting at zero rather than dropping the row to NULL. Dividing by max_value and rounding to two decimals gives a number a human can scan: a sequence at 92.4 deserves attention; one at 0.01 does not.

Ordering by Risk

1ORDER BY pct_used DESC NULLS LAST

Sorting the most-consumed sequences to the top means the rows that matter appear first regardless of how many sequences the database holds. NULLS LAST pushes any sequence whose max_value is somehow unreadable to the bottom instead of letting it masquerade as the highest-risk row.

Why last_value Can Be NULL

last_value reflects on-disk sequence state, and PostgreSQL does not materialize that state until the first nextval(). A freshly created sequence therefore reports NULL here. This is expected, not an error, and is the reason the COALESCE exists.

Key Sequence Columns

last_value and the Cache Caveat

last_value is the last value written to the sequence's on-disk record, not necessarily the last value a session actually consumed. When cache_size is greater than one, each backend reserves a block of values in memory, so last_value can run ahead of what has been committed to any table. For capacity monitoring this is the conservative, correct number to watch — it never under-reports how far the sequence has advanced.

max_value and data_type

max_value is the hard ceiling. For a sequence backing an integer identity column it defaults to 2,147,483,647; for bigint it is effectively unreachable at roughly 9.2 quintillion. data_type tells you which world a sequence lives in, and a mismatch — a bigint sequence feeding an integer column, or the reverse — is a latent bug worth catching during an audit.

increment_by and cycle

increment_by is usually 1, but custom sharding or interleaving schemes set larger steps, which changes how fast max_value arrives. cycle determines what happens at the ceiling: when true, the sequence wraps to min_value and keeps issuing numbers; when false (the default), it raises an error. A cycling sequence on a primary key is a duplicate-key incident waiting to happen, so a cycle = true row on an identity column is a red flag.

Practical Applications

Pre-Migration Capacity Check

Before a release that adds write volume, run the audit query and look at pct_used. Any integer-backed sequence above roughly 70% is a candidate for a planned bigint migration during a maintenance window, on your schedule rather than the database's.

Catching Misconfigured Cycles

Filter the view to WHERE cycle and review the results against what each sequence feeds. Cycling is legitimate for ring-buffer-style numbering but dangerous for identity columns. The audit makes the setting visible instead of buried in a long-forgotten CREATE SEQUENCE statement.

Detecting Orphaned or Stalled Sequences

A sequence whose last_value is NULL or has not moved across repeated checks may be unused — a leftover from a dropped column or a feature that never shipped. Cross-referencing low-activity sequences against pg_depend confirms whether they still back a live column or can be dropped.

Type-Mismatch Discovery

Joining the data type of the sequence against the data type of its owning column finds the integer-sequence-on-bigint-column class of mistake, which silently caps a table that the schema implies is unbounded.

Version Compatibility

The pg_sequences view was added in PostgreSQL 10, alongside the move to store sequence metadata in the pg_sequence system catalog rather than inside each sequence relation. On PostgreSQL 9.6 and earlier the same information requires querying each sequence relation directly with SELECT * FROM sequence_name, one statement per sequence — far less convenient for a database-wide audit.

The underlying pg_sequence catalog holds the configuration columns (seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle), while pg_sequences joins that catalog to pg_class and pg_namespace to add human-readable schema and sequence names and the live last_value. For most audit work the view is the right entry point; drop to the catalog only when you need to join sequence configuration into a larger query against other system catalogs. Column behavior has been stable across PostgreSQL 10 through 18.

Best Practices

  • Watch pct_used, not raw counts — a sequence at 1.8 billion sounds alarming but is fine on bigint; the percentage of max_value is the number that signals real risk.
  • Schedule integer-to-bigint migrations early — converting an identity column's type is a heavy operation on large tables; start it at 70% used, not at 99%.
  • Audit cycle on identity columns — a cycling sequence behind a primary key will eventually collide; confirm cycle = false for anything that must stay unique.
  • Grant carefully for complete inventories — pg_sequences only shows sequences the current role can access; run the audit as a sufficiently privileged role or you will miss schemas.
  • Re-run after bulk loads — large COPY or backfill jobs can move last_value dramatically; re-check the audit after any major data import.

References

Posts in this series