A sequence can be created with a CACHE setting (for example CACHE 1 versus CACHE 1000). Explain what that setting does, and how you would choose it for a high-throughput insert workload — including what happens on a crash and across multiple database instances.
answer
- persist a high-water mark, issue from memory
- cache ↑ ⇒ contention ↓, gaps ↑
- crash discards unissued cached values
- per-instance blocks ⇒ unique but unordered
- allocator hot spot ≠ index right-edge hot spot
basics
~20 sCACHE n preallocates n values into memory so most allocations avoid touching and persisting shared sequence state. Bigger cache means less contention and fewer writes, but larger gaps after a crash and, across instances, values issued out of global order.
solid answer
~60 sEvery allocation must not lose values across a crash, so the generator persists a high-water mark. `CACHE n` lets a session or instance claim `n` values at once by persisting the mark just once, then hand them out from memory. With `CACHE 1` every allocation touches durable shared state — safest, slowest, the classic hot spot on a high-insert table. With `CACHE 1000` you pay that cost once per thousand rows. Costs of a large cache: - **Crash gaps.** Unissued cached values are lost on an unclean shutdown — up to `n` per holder. - **Restart jumps** that look alarming in the data but are harmless. - **Interleaving.** Where the cache is per session or per instance, two holders issue from different blocks, so values are unique but not globally ordered. Never infer chronology from them. I choose by write rate: leave it small for low-volume tables where a tidy series aids humans, raise it for hot ingest tables, and be aware that in a multi-instance cluster ordering is a per-node property, so ordered generation there needs a much larger cache or a different key scheme entirely.
code
sql · 5 lines-- low-volume, human-readable, tight series
CREATE SEQUENCE ticket_no_seq CACHE 1 NO CYCLE;
-- hot ingest table: fewer durable writes per row, bigger gaps
CREATE SEQUENCE event_id_seq CACHE 1000 NO CYCLE;go deeper
Know that CACHE preallocates values so inserts are faster, and that the cached values are lost if the server stops unexpectedly.
Explain the durable high-water mark, the gap-versus-contention trade, and that cached values are per session or instance.
Choose a value from the write rate and id width, and distinguish allocator contention from index right-edge contention when diagnosing slow inserts.
Treat it as identifier-generation architecture: per-node blocks versus a coordinated ordered generator versus externally generated ids, and state plainly which ordering guarantees the system will and will not offer.
## What the cache is protecting against Allocation has two hard requirements: no value is ever issued twice, even across a crash; and allocation is cheap under heavy concurrency. Those pull in opposite directions. Durability wants a persistent write per allocation; concurrency wants no shared, durable state on the hot path. The compromise is block allocation. The generator persists a **high-water mark** — "values up to X are considered used" — and then hands out values below X from memory. With `CACHE n`, one durable update covers `n` allocations. After a crash the engine resumes at the persisted mark, so nothing already issued can be reissued; the price is that everything cached but unissued is skipped. ## Reading the trade-off **Small cache (1 or a few).** - Tightest series, smallest crash gaps. - Every allocation contends on the same sequence state and forces durable work. On an ingest table doing tens of thousands of inserts per second this shows up as the classic sequence hot spot: waits concentrated on one object. - Reasonable for low-volume tables, and where humans read the ids and a tidy series has real support value. **Large cache (hundreds to thousands).** - Allocation becomes an in-memory increment for almost every row; contention and durable writes drop by the cache factor. - Crash or restart discards up to `n` values per holder. If ids are `int` rather than `bigint`, aggressive caching plus frequent restarts consumes range faster than the row count suggests. - Values from different holders interleave: holder A issues 1000–1999 while holder B issues 2000–2999, so B's row can have a lower id than a later A row. Uniqueness holds; ordering does not. ## Multiple instances In clustered or multi-writer deployments each instance typically caches its own block, so what looks like one counter is really several ranges being consumed in parallel. Consequences: - Ids are unique but **not globally ordered**. Any code that treats id order as event order is broken by design there, and it will pass single-node testing. - A too-small cache turns the sequence into a cross-instance coordination point — the worst possible outcome, since every allocation becomes a distributed round trip. Cluster deployments therefore push caches *up*, not down. - Some engines offer an ordered mode that forces cross-instance coordination for global ordering. It is correct and slow; use it only if ordering is a genuine requirement, and prefer fixing the requirement. An alternative shape used to avoid a shared counter entirely: give each instance a disjoint range by increment offset (increment 4, starting at 1, 2, 3, 4). Cheap, sharded, and the ordering guarantee is gone by construction — which is honest. ## Interactions worth naming - **Index insertion point.** A single monotonic key means all inserts land at the right edge of the primary-key index, concentrating page contention. Bigger caches do not fix that; changing the key shape does. Mentioning that you know the difference between *allocator* contention and *index* contention is a strong signal. - **Exhaustion.** Cache loss and gaps burn range. A 32-bit key with heavy caching can exhaust far sooner than expected; prefer 64-bit keys and treat `CYCLE` as unsafe for primary keys, since wrapping reissues values that already exist. - **Application-side blocks.** ORMs and id services often take a block of values and hand them out in the application, which is the same trade one level up: fewer round trips, bigger gaps, no ordering. ## How to answer Define the mechanism (durable high-water mark plus in-memory block), state the trade in one line (contention and durable writes down, gaps and disorder up), give a concrete choice rule keyed to write rate and id width, and name the multi-instance consequence. Finish by refusing to treat ids as an ordering signal.
- Raising CACHE removed sequence waits but inserts are still slow, with contention on the primary-key index. What is happening?Two different hot spots. The cache fixed allocator contention on the sequence object. What remains is index-level contention: every monotonically increasing key targets the same right-edge leaf page, so concurrent inserts serialise on that page and its buffer latch. Fixes live in key or table design — a less monotonic key, hash or range partitioning, or a table structure that does not order rows by that key — not in the sequence settings.
- In a multi-writer cluster, why is a small sequence cache actively harmful?Each instance must obtain a fresh block from shared, durable state whenever its cache empties. A small cache makes that happen constantly, turning every few allocations into cross-instance coordination with the latency of a distributed write. Large per-instance caches keep allocation local; the cost you accept is that ids interleave across nodes and carry no global ordering.
A ticket seller taking a roll of a thousand tickets from the safe instead of walking to the safe for every customer. Far fewer trips; if the shop closes suddenly, the rest of the roll is thrown away.
saying these in an interview costs you the question
- Thinking CACHE makes ids gap-free or safer
- Setting CACHE 1 in a cluster to "keep ids in order"
- Assuming cached values survive an unclean shutdown
- Confusing sequence-object contention with right-edge index contention
- Using CYCLE on a primary-key sequence to avoid exhaustion