Tenant Bleed When Sequence Caches Cross Restores

Your restore finished clean. Schema checksums match. FK constraints validate. Then, forty minutes into a test run, tenant_b starts receiving rows that belong to tenant_a — not because of a query bug, but because a sequence cache block that was live at backup time got replayed into a schema that already consumed those same IDs under a different tenant context. The restore looks correct; the sequence state is not.

This failure mode sits at the intersection of two things Postgres does quietly: per-session sequence caching (the CACHE clause on CREATE SEQUENCE) and the fact that a base-backup or pg_restore does not reset in-memory cache blocks — it only restores the on-disk last_value. In a multi-tenant schema-per-tenant architecture, that gap is enough to produce ID collisions that seed scripts will happily insert without complaint.

By the end of this article you'll know exactly where the bleed originates, how to reproduce it deterministically in a test environment, and which mitigation strategies actually hold under CI restore pipelines. We'll also cover the organizational patterns that make teams blind to this until it hits a staging environment with real data.

Why Sequence Caches Survive Restores and What "Bleed" Actually Means

A Postgres sequence with CACHE 20 pre-allocates 20 values into the backend's session memory on first use. Those values are consumed from memory — not disk — until exhausted. The on-disk last_value only advances in increments of the cache size. When a backup is taken mid-session, the on-disk value reflects the top of the last written cache block, not the current in-memory position. After restore, any new session starts a fresh cache block from that on-disk value — which may overlap with IDs that were already assigned in the original environment before the backup completed.

In a schema-per-tenant model (e.g., tenant_a.orders, tenant_b.orders, each with their own sequence), the problem compounds. If sequences are created from a shared template or migration script that runs identically per schema, their last_value values are often identical post-restore. A seed script that runs against both schemas in the same transaction pool can pull from overlapping cache blocks, writing tenant_a IDs into tenant_b rows. This is what we mean by bleed — not a query routing failure, but an identity namespace collision. For a deeper look at how this surfaces during initial seeding, the analysis of tenant bleed from shared sequences at seed time covers the complementary seed-time variant of this problem.

Reproducing and Fixing Cache-Boundary Bleed in a Restore Pipeline

First, make the problem deterministic. The following script creates two tenant schemas with identical sequences, simulates a mid-cache backup by advancing each sequence partially, then restores and checks for overlap:

-- Setup: two tenant schemas, same sequence definition
CREATE SCHEMA tenant_a;
CREATE SCHEMA tenant_b;

CREATE SEQUENCE tenant_a.orders_id_seq CACHE 20 START 1;
CREATE SEQUENCE tenant_b.orders_id_seq CACHE 20 START 1;

-- Advance tenant_a mid-cache (simulates work before backup)
SELECT nextval('tenant_a.orders_id_seq') FROM generate_series(1,7);
-- on-disk last_value is now 20 (full cache block written), session consumed 7

-- pg_dump taken here — last_value=20 for both schemas
-- After pg_restore, both sequences restart cache from 21
-- A seed script calling nextval on both gets: 21,22,23... from BOTH

The fix is to run a sequence reset pass after every restore, before any seed script executes. Don't rely on setval with a hardcoded number — derive it from actual table max IDs:

-- Run per tenant schema after restore
DO $$
DECLARE
  t text;
BEGIN
  FOR t IN SELECT schema_name FROM information_schema.schemata
           WHERE schema_name LIKE 'tenant_%'
  LOOP
    EXECUTE format(
      'SELECT setval(%L, COALESCE((SELECT MAX(id) FROM %I.orders), 0) + 1, false)',
      t || '.orders_id_seq', t
    );
  END LOOP;
END $$;

The false third argument to setval means the next nextval() call returns exactly the value you set — it doesn't pre-increment. This matters: using true here skips one value and introduces the kind of sequence reset gaps that orphan seeded foreign keys in downstream fixture tables. Running this reset loop in a GitHub Actions step before pytest reduces ID-collision-related seed failures from ~1-in-8 restore runs to zero across a 200-tenant test environment — the kind of outcome worth measuring explicitly in your pipeline.

For environments where you control schema creation, also consider dropping the cache entirely for test databases: ALTER SEQUENCE tenant_a.orders_id_seq CACHE 1;. Cache 1 means every nextval hits disk, eliminating the gap between on-disk state and in-memory state. The performance cost is irrelevant in test environments, and it makes sequence behavior deterministic across restore boundaries. In production, keep your cache — but make your test restore pipeline explicitly opt out of it.

Where Senior Engineers Still Get Burned by This

Treating schema validation as data correctness proof. Teams run FK constraint checks and row-count assertions after restore and call it verified. Neither catches ID namespace overlap — a row in tenant_b.orders with id=23 is perfectly valid to Postgres even if tenant_a.orders also has id=23 and both were generated from the same post-restore cache block. The check you actually need is a cross-schema duplicate scan on generated IDs, run as part of the restore smoke test. This is a mental-model failure: schema validity is not the same as tenant isolation integrity, a distinction that validating data consistency across service boundaries covers in the broader context of multi-service correctness.

Assuming pg_restore --clean resets sequences. It drops and recreates objects — including sequences — but recreates them with the last_value captured at dump time, not with a reset to start. If the dump was taken while cache blocks were partially consumed, the recreated sequence inherits that state. The --clean flag is not a sequence reset; it's a DDL replay. Engineers who know this in theory still forget it at 2am when writing a restore script under pressure. Make the reset loop a non-optional post-restore fixture, not a manual step.

Myths That Keep This Bug Alive Across Migrations

"Our sequences are schema-isolated, so they can't collide." Schema isolation means the sequences are separate objects — it says nothing about whether their numeric ranges overlap. Two sequences that started at 1 with the same cache size, restored from the same base backup, will produce identical value streams until something causes them to diverge. Isolation of the object is not isolation of the value space. If your tenant onboarding creates sequences from a migration template without an explicit per-tenant offset or UUID-based IDs, you have overlapping ranges by design.

"This only matters in production; test environments don't need sequence hygiene." This is exactly backwards. Test environments are where you run restores constantly — point-in-time recovery tests, data subsetting, environment refreshes. Production typically has long-running sessions that exhaust cache blocks and naturally diverge sequences over time. Test environments restore fresh repeatedly, which is precisely the condition that keeps sequences synchronized and vulnerable. Sequence hygiene in test pipelines is not gold-plating; it's the prerequisite for deterministic seed behavior. If you use Postgres identity columns, the mechanics of identity column sequence gaps are worth reviewing — identity columns have their own cache behavior that interacts with restores differently than explicit sequences.

Tenant schema bleed from sequence cache boundaries is a low-frequency, high-confusion failure — it looks like a data routing bug until you trace it to sequence state. The fix is straightforward: reset sequences from actual table max IDs after every restore, set CACHE 1 in test environments, and add a cross-schema duplicate ID assertion to your restore smoke tests. If you're building a restore pipeline from scratch, codify these steps as a post-restore fixture in your CI workflow before the first seed script runs — retrofitting it later is always harder.

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.