A PostgreSQL checkout service set CACHE 100 on its order_id identity; why do ids now arrive out of order and jump by hundreds?
answer
- the cache lives in each backend
- blocks reserved per session
- unused blocks die with the session
- ON CONFLICT still calls nextval()
- values logged to WAL ahead
basics
~20 sPostgreSQL's sequence CACHE is per session: each backend reserves 100 values and serves them from private memory. Sessions hand out interleaved blocks, and unused values vanish when a session ends, so ids stay distinct but are neither ordered nor dense.
solid answer
~50 s`ALTER TABLE orders ALTER COLUMN order_id SET CACHE 100` changes the identity's sequence so that each **session**, meaning each backend process, reserves 100 numbers on its first `nextval()` and serves the next 99 from its own memory. Session A may hold 1-100 while session B holds 101-200, so B's early orders carry higher ids than A's later ones: values are distinct, not sequential. Whatever a session has not used when it disconnects is lost, so a pool that recycles connections, or short-lived workers, leaves gaps of up to 99 per session. Other PostgreSQL burners add to it: `INSERT ... ON CONFLICT` evaluates `nextval()` before detecting the conflict, rolled-back inserts keep their numbers, and after a crash the sequence resumes past values it had pre-logged to WAL. With the default `CACHE 1`, ids follow the order of `nextval()` calls, which is still not commit order; sort by a `timestamptz`.
code
sql · 13 linesALTER TABLE orders ALTER COLUMN order_id SET CACHE 100;
-- session A: first nextval() reserves 1..100, returns 1
-- session B: first nextval() reserves 101..200, returns 101
-- session A: returns 2 from its private block
-- session B disconnects: 102..200 are never used
SELECT cache_size, last_value
FROM pg_sequences
WHERE sequencename = 'orders_order_id_seq';
-- back to the default once the experiment is over
ALTER TABLE orders ALTER COLUMN order_id SET CACHE 1;go deeper
Recall that PostgreSQL sequences never reuse a value, so gaps are normal, and that the CACHE setting defaults to 1.
Explain that CACHE reserves blocks per session in backend memory, making ids distinct but not ordered, and name rollbacks and ON CONFLICT as other value burners.
Diagnose the jump pattern from connection lifetimes and failovers, read pg_sequences, and argue for CACHE 1 plus a timestamptz column instead of trusting id order.
Decide what identifiers may promise across services: uniqueness only, with event order from timestamps and any gap-free numbering isolated in a deliberately serialized counter.
## Where a PostgreSQL sequence keeps its cache A **sequence** in PostgreSQL is a special one-row relation. `nextval()` takes an exclusive lock on that relation's single page in shared buffers, advances `last_value`, and returns a number. The `CACHE` option, **1 by default**, changes how many numbers one visit reserves: - On the first `nextval()` in a session, the backend reserves `CACHE` consecutive values and moves the shared `last_value` to the end of that block. - The next `CACHE - 1` calls in **that session** are answered from the backend's private memory without touching the sequence page. - Other sessions never see that block; they reserve their own. For an identity column the option is set through the table, `ALTER TABLE orders ALTER COLUMN order_id SET CACHE 100`, or at creation with `GENERATED ALWAYS AS IDENTITY (CACHE 100)`. ## Why ids arrive out of order The manual's own example, scaled to 100: session A reserves 1-100 and inserts order 1; session B reserves 101-200 and inserts order 101; A then inserts order 2. Sorted by `order_id`, B's order sits before A's later one. With a cache above one, PostgreSQL promises only that values are **distinct**, not that they were generated in sequence. With `CACHE 1`, values come out in the order `nextval()` was called, which is still not commit order, since a transaction that called `nextval()` first may commit last. ## Why ids jump | Cause | What is lost | PostgreSQL detail | |---|---|---| | Session ends with part of its block unused | up to `CACHE - 1` values per session | cached values live only in that backend | | Transaction rolls back | the values it took | `nextval()` and `setval()` are never rolled back | | `INSERT ... ON CONFLICT` hits a conflict | one value per conflicting row | the row, including its `nextval()` default, is computed before the conflict is detected | | Crash restart | values pre-logged but never handed out | the sequence is WAL-logged as it will look 32 fetches ahead (`SEQ_LOG_VALS`), plus the cache block | | Physical standby promoted | likewise | the standby replayed that same pre-logged state | The checkout service's jumps of hundreds usually come from the first row: application pools that close connections on a maximum-lifetime timer, autoscaled workers, or scripts that connect, insert a few orders and exit, each discarding most of a 100-value block. Gap-free numbering is a different feature, and the manual states that sequences cannot provide it. ## Telling the causes apart - Gaps close to the `CACHE` size that appear per worker restart or connection recycle point at discarded cache blocks. - Single missing ids that line up with duplicate sign-ups or failed checkouts point at `ON CONFLICT` or at rolled-back inserts; the application's error log confirms which. - One larger jump right after a crash restart or a promotion is the WAL pre-logging, bounded by 32 plus the cache size. ## Inspecting the state - `SELECT cache_size, last_value FROM pg_sequences WHERE sequencename = 'orders_order_id_seq';` shows the setting and the position. With caching, `last_value` can be **ahead of** the last number actually handed out, because it includes blocks reserved by live sessions. - `pg_get_serial_sequence('orders', 'order_id')` names the identity's implicit sequence if you do not know it. - A `setval()` is not noticed by other sessions until they exhaust the block they already hold. ## What CACHE actually buys in PostgreSQL PostgreSQL already avoids a WAL record per call: it logs the sequence 32 values ahead and writes a new record only when that allowance is used up or the first call after a checkpoint arrives. So `CACHE` mainly saves visits to the sequence's shared page and its exclusive buffer lock, which matters only on very hot sequences with many concurrent inserting sessions. For most tables the price, disorder and larger gaps, is not worth it. ## What to do 1. Keep `CACHE 1` unless measurement shows contention on the sequence; if you raise it, size the block near the number of inserts a session makes before it disconnects. 2. Never use `order_id` order as event order. Store `placed_at timestamptz` and sort by it. 3. Never promise customers consecutive order numbers from a sequence. If the business needs gap-free invoice numbers, assign them from a counter row updated under a row lock inside the transaction, slower by design. 4. Treat a jump after a crash or failover as the expected effect of pre-logging, not as lost orders.
- Why do order ids sometimes jump after a PostgreSQL primary fails over to a standby?`nextval()` does not write WAL for every value: it logs the sequence as it will look after up to 32 more fetches, plus any cache block, and serves values without new records until that allowance runs out. The standby replays the logged state, so after promotion it continues from the pre-logged position, skipping numbers the old primary never handed out. A crash restart does the same; only numbers are lost.
- Does setting CACHE back to 1 repair the gaps and the out-of-order ids already stored?No. `ALTER TABLE ... ALTER COLUMN order_id SET CACHE 1` only changes how future `nextval()` calls reserve values; ids already in the table stay exactly as they are. Gaps are permanent by design, and renumbering a primary key that other tables, logs and customers reference is almost never worth it. Fix consumers that assumed density or order instead.
Each cashier takes a strip of 100 numbered tickets from the dispenser at the start of a shift and hands them out at their own till. No number is ever given twice, but customers at different tills get interleaved numbers, and a cashier who goes home early throws the rest of the strip away.
- CACHE 100 keeps a shared pool of values in shared memory for all sessions
- With CACHE above 1, ids still increase in the order rows are inserted
- A rolled-back INSERT gives its id back to the sequence
- ON CONFLICT DO NOTHING never consumes a sequence value when it skips the row
- PostgreSQL's default sequence CACHE is 32