PostgreSQL partial indexes: kill seq scans on 100M+ row tables

TL;DR

Partial indexes scoped to tenant predicates eliminate full-table seq scans, reduce index storage by 60–80%, and — critically — shrink the predicate lock surface under SERIALIZABLE isolation, preventing lock escalation that silently kills throughput in high-concurrency multi-tenant SaaS systems. Most teams reach for composite indexes first. That’s the wrong instinct.

What most teams get wrong about multi-tenant indexes

You’ve got an events table. It holds 100 million rows across 2,000 tenants. You add a composite index on (tenant_id, created_at, status) and call it done. Query plans look fine in staging. Production tells a different story.

A standard B-tree composite index on a 100M-row table at 8 bytes per key easily runs 2–4 GB. Every VACUUM cycle, every autovacuum worker, every checkpoint — they all touch that index. And if you’re running SERIALIZABLE isolation for full consistency guarantees, PostgreSQL’s predicate locking is quietly acquiring locks on every index page your scan touches.

That’s the escalation trap.

Partial indexes: the architecture

A partial index is a B-tree (or GiST, GIN, etc.) built over a filtered subset of rows using a WHERE predicate baked into the index definition. For multi-tenant systems with predictable access patterns, nothing moves the needle faster.

-- Standard composite index: indexes ALL rows
CREATE INDEX idx_events_tenant_created
  ON events (tenant_id, created_at DESC);

-- Partial index: indexes only ACTIVE rows per tenant
CREATE INDEX idx_events_active_tenant
  ON events (tenant_id, created_at DESC)
  WHERE status = 'active';

For a table where 15% of rows are active, the second index is 6.7x smaller. Fewer pages. Fewer I/O operations. And in high-concurrency reads under SERIALIZABLE isolation — fewer predicate locks.

Predicate lock escalation: the silent killer

PostgreSQL’s Serializable Snapshot Isolation (SSI) uses predicate locks — SIREAD locks — at three granularities: tuple-level, page-level, and relation-level. This mechanism is specific to SERIALIZABLE isolation; REPEATABLE READ doesn’t acquire predicate locks and doesn’t provide SSI guarantees. If your application uses REPEATABLE READ for consistency, understand that you’re not getting full serializability — and if you need it, upgrading to SERIALIZABLE plus shrinking your predicate lock surface with partial indexes is the correct path.

Here’s what happens under SERIALIZABLE at scale:

Scenario	Index Pages Scanned	Predicate Locks Acquired	Escalation Risk
Full composite index, 100M rows	~12,000	High	Relation-level escalation
Partial index, 15M active rows	~1,800	Moderate	Page-level (manageable)
Partial index + tight predicate	~200	Low	Tuple-level (no escalation)

When predicate locks escalate from tuple → page → relation, you lose concurrency. Transactions that should proceed in parallel start conflicting. You see increased serialization failure rates, retry storms in your application layer, and pg_stat_activity fills with sessions waiting on lock conflicts.

The WHERE clause patterns that work

Three predicates show up reliably in production multi-tenant systems and consistently yield index-only scans:

Tenant + bounded status

CREATE INDEX idx_orders_pending_tenant
  ON orders (tenant_id, created_at DESC)
  WHERE status IN ('pending', 'processing');

Works when the predicate cardinality is stable. Avoid this if status values are unbounded.

Soft-delete elimination

CREATE INDEX idx_users_active
  ON users (tenant_id, email)
  WHERE deleted_at IS NULL;

Classic. On a system with 5% deleted records, this eliminates 5% of index bloat and keeps scans tight. The column should be nullable with NULL representing the live state — no additional constraint is needed beyond the partial index predicate itself.

Time-bounded hot data

CREATE INDEX idx_events_recent_tenant
  ON events (tenant_id, created_at DESC)
  WHERE created_at > '2026-04-25'::timestamptz;

Note: NOW() is a volatile function — partial index predicates must use immutable expressions. Materialize the cutoff with a generated column (e.g., is_recent boolean GENERATED ALWAYS AS (created_at > '2026-04-25'::timestamptz) STORED) and index on that, or use a bind parameter in your query that matches the stored immutable predicate exactly.

Validating the plan

Always verify the planner uses your partial index:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, payload
FROM events
WHERE tenant_id = 'acme-corp'
  AND status = 'active'
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

Look for Index Only Scan using idx_events_active_tenant. If you see Seq Scan or Bitmap Heap Scan with high Buffers: shared hit, your predicate isn’t matching — check that your query’s WHERE clause is a logical subset of the index predicate.

Three things to do now

Run pg_relation_size across your indexes. If your largest index is more than 30% of your table size on a filtered dataset, a partial index is almost certainly warranted.
Align your application’s WHERE clauses with index predicates. The planner will only use a partial index if the query’s filter implies the index predicate. Document these contracts in your query layer — treat partial index predicates as part of your schema contract.
Profile predicate lock escalation under load if you run SERIALIZABLE. Query pg_locks where locktype = 'relation' during peak read traffic. Relation-level SIREAD locks are the canary — if you see them, your index scan surface is too wide and a partial index will directly reduce escalation frequency. If you’re on REPEATABLE READ and need true serializability, this is also the moment to reconsider your isolation level.

#backend #architecture #postgresql #database