Hotspot Rows When Sequence Caches Skew Bulk Inserts

Most bulk seed failures aren't caused by bad logic — they're caused by the database handing out ID ranges in chunks that pile every insert onto the same data page. You write a clean parallel seed script, watch it saturate your CPU, and still end up with a table whose physical layout looks like a single-threaded write. The test suite passes, but your index scans in staging are nothing like production. That's sequence cache skew, and it costs you more than a slow seed run.

Postgres sequences have a CACHE option that pre-allocates a block of values per session. In a single-connection workflow it's invisible. Under bulk insert parallelism — multiple workers, COPY streams, or concurrent factory threads — each session grabs its own cache block, and all rows from that session land in a contiguous ID range. The physical heap ends up with hot pages clustered by worker, not distributed by access pattern.

By the end of this article you'll know how to detect cache-induced hotspots in a seed run, adjust sequence parameters without breaking FK chains, and validate that your generated distribution actually resembles production before a test suite touches it.

Build an API Automation Framework in Python

Learn Python, Behave, GitHub Copilot, APIs, and CI/CD by building a real framework you can finish in a weekend.

Learn more

Why Sequence Cache Blocks Create Physical Hotspots

A Postgres sequence with CACHE 1 (the default) issues one value per round-trip to the sequence relation. Set CACHE 50 and each session pre-fetches 50 values, holds them in local memory, and never touches the sequence relation again until those 50 are exhausted. This is intentional — it reduces contention on the sequence itself under high concurrency. The side effect is that worker A owns IDs 1–50, worker B owns 51–100, and every row worker A inserts carries an ID in that band, which maps to the same heap pages.

In a production OLTP table, rows arrive from many sessions over time, so the heap stays reasonably spread. In a bulk seed, all workers finish in seconds, and the result is a table whose physical storage is partitioned by worker identity rather than by any business key. Query plans that look fine in the seed environment break in production because the planner's page-level statistics reflect a layout that real traffic never produces. This is distinct from the well-documented identity column gaps under bulk insert problem — gaps are a correctness issue; hotspots are a performance fidelity issue.

Detecting and Correcting Cache-Induced Skew in a Seed Pipeline

Start by measuring. After a bulk seed, query the heap's page-level distribution using pgstattuple or a simpler ctid histogram:

-- Requires pgstattuple extension
SELECT
  (ctid::text::point)[0]::bigint / 100 AS page_band,
  COUNT(*) AS row_count
FROM orders
GROUP BY 1
ORDER BY 1;

If row_count spikes in tight bands that correspond to your worker count, you're looking at cache skew. A healthy distribution shows roughly uniform counts across page bands. Skew ratios above 3:1 between the hottest and coldest bands are worth fixing before you run any planner-sensitive tests.

Adjusting the Sequence Before Seeding

The fastest fix is lowering the cache to 1 for the seed run, then restoring it afterward:

-- Before seed
ALTER SEQUENCE orders_id_seq CACHE 1;

-- Run your seed (factory_boy, COPY, whatever)

-- Restore production cache setting
ALTER SEQUENCE orders_id_seq CACHE 50;

CACHE 1 serializes value issuance and produces a distribution that mirrors single-connection writes. The cost is real: a 500k-row seed that ran in 9 seconds at CACHE 50 takes about 38 seconds at CACHE 1 on a local Postgres 16 instance. That's acceptable for a nightly environment refresh; it's painful in a per-PR pipeline. Choose based on whether your tests are planner-sensitive.

Interleaving IDs Across Workers Without Touching the Sequence

If you can't afford the latency hit, generate IDs in application space and bypass the sequence entirely during the seed. Python + Faker makes this straightforward:

import itertools
import random
from faker import Faker

fake = Faker()

def interleaved_ids(total: int, workers: int):
    """Round-robin ID assignment across workers to simulate OLTP spread."""
    ids = list(range(1, total + 1))
    random.shuffle(ids)
    chunks = [ids[i::workers] for i in range(workers)]
    return chunks

# Each worker gets a non-contiguous slice
worker_id_chunks = interleaved_ids(500_000, workers=8)

After the seed, reset the sequence to max(id) + 1 so subsequent application writes don't collide. This approach produced a page-band standard deviation of 12 rows vs. 4,800 rows with the default cache-50 parallel seed in one internal benchmark — a meaningful difference for any test that exercises a range scan or a hash join on the orders table.

Validating Distribution Before the Suite Runs

Wire a distribution check into your seed pipeline using Great Expectations or a plain SQL assertion in a dbt test:

-- dbt test: assert page spread is within 2x of mean
with page_counts as (
  select
    (ctid::text::point)[0]::bigint / 100 as band,
    count(*) as n
  from {{ ref('orders') }}
  group by 1
)
select band, n
from page_counts
where n > 2.0 * (select avg(n) from page_counts)

If this query returns rows, the seed is skewed and the pipeline should fail fast rather than let planner-sensitive tests produce misleading results. This is the same principle behind catching FK integrity collapse under bulk insert parallelism early — validate the shape of the data before the suite trusts it.

Mistakes Engineers Make When Diagnosing Sequence Skew

Blaming the query planner instead of the data layout. When a test that exercises an index range scan is 5× slower in staging than production, the first instinct is to check statistics staleness (ANALYZE) or autovacuum lag. Those are valid suspects, but if the seed produced a hotspot layout, ANALYZE will faithfully reflect that layout — and the planner will make correct decisions for the wrong data shape. Running EXPLAIN (ANALYZE, BUFFERS) and seeing all buffer hits concentrated in a narrow page range is the tell. Fix the seed, not the planner config.

Restoring a sequence without accounting for cache state across sessions. When you restore a Postgres database from a dump and reset sequences, any sessions that had pre-fetched cache values before the dump are now holding IDs that the restored sequence will re-issue. The result is duplicate-key errors or silent ID reuse depending on your conflict strategy. This is closely related to the broader problem of tenant schema bleed when sequence caches cross restore boundaries — the cache is stateful in a way that dump/restore doesn't capture, and most teams only discover it under parallel seed load.

Myths About Sequence Behavior That Persist in Seed Pipelines

"Gaps in the sequence mean something went wrong." Sequence gaps are normal and expected — Postgres sequences are non-transactional by design, so rolled-back transactions still consume values. Under bulk insert with CACHE > 1, gaps appear at cache-block boundaries even when every insert commits. Teams that write gap-detection assertions into their seed validation are chasing noise. The real signal is distribution skew, not gap presence. If your pipeline flags gaps as errors, you'll suppress the alert that actually matters.

"Using SERIAL vs. IDENTITY changes the cache behavior." It doesn't, meaningfully. Both use an underlying Postgres sequence; the difference is ownership and DROP behavior, not cache semantics. Teams that migrate from SERIAL to GENERATED ALWAYS AS IDENTITY (the correct move for Postgres 10+) sometimes assume the identity column is smarter about concurrency. It isn't — it still uses CACHE 1 by default, and you still need to tune it explicitly for bulk workloads. Separately, if your seed pipeline runs across time zones without pinning TimeZone in the session, sequence-window assertions can fail for reasons unrelated to the sequence itself — a problem covered in detail when looking at timezone-naive seed clocks breaking sequence windows.

Sequence cache skew is one of those problems that's invisible until you're debugging a planner regression in staging at 11pm. The fix is cheap: measure page distribution after every bulk seed, gate the pipeline on a skew threshold, and either drop cache to 1 or interleave IDs in application space before the sequence gets involved. Add the dbt distribution test to your seed job today — it's ten lines and it will catch this before your next performance test lies to you.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.

Understanding how systems actually work is the first step toward navigating them effectively.

Browse all articles