DEV Community

SoftwareDevs mvpfactory.io
SoftwareDevs mvpfactory.io

Posted on • Originally published at mvpfactory.io

PostgreSQL Partial Indexes and Predicate Lock Escalation: The Multi-Tenant Query Optimization Most Developers Skip

#webdev #programming 
---
title: "PostgreSQL Partial Indexes: Kill Seq Scans on 100M+ Row Multi-Tenant Tables"
published: true
description: "Partial indexes scoped to tenant predicates cut index storage by 60–80% and eliminate the predicate lock escalation that silently kills throughput under SERIALIZABLE isolation."
tags: postgresql, performance, architecture, cloud
canonical_url: https://blog.mvpfactory.co/postgresql-partial-indexes-multi-tenant
---
Enter fullscreen mode Exit fullscreen mode

What You Will Learn

Let me show you a pattern I use in every multi-tenant SaaS project at scale. By the end of this tutorial, you will know how to replace bloated composite indexes with partial indexes scoped to tenant predicates — dropping index storage by 60–80% and eliminating the predicate lock escalation that silently kills throughput under SERIALIZABLE isolation.

We are working with a realistic scenario: an events table with 100 million rows across 2,000 tenants.

Prerequisites

  • PostgreSQL 12+
  • A multi-tenant schema with a tenant_id column
  • Basic familiarity with EXPLAIN ANALYZE

The Problem With Composite Indexes at Scale

Most teams reach for composite indexes first. That is the wrong instinct.

A standard B-tree composite index on (tenant_id, created_at, status) over 100 million rows at 8 bytes per key runs 2–4 GB. Every VACUUM cycle touches it. Every autovacuum worker, every checkpoint — they all pay that cost.

-- What most teams add — and later regret
CREATE INDEX idx_events_tenant_created
  ON events (tenant_id, created_at DESC);
Enter fullscreen mode Exit fullscreen mode

If you are running SERIALIZABLE isolation for full consistency guarantees, you have a second problem. PostgreSQL's Serializable Snapshot Isolation (SSI) acquires SIREAD predicate locks at tuple, page, and relation granularity across every index page your scan touches. That is the escalation trap.

Here Is the Minimal Setup to Get This Working

A partial index bakes a WHERE predicate into the index definition. Only matching rows are indexed.

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

For a table where 15% of rows are active, this index is 6.7x smaller. Fewer pages, fewer I/O, and under SERIALIZABLE isolation — far fewer predicate locks acquired per scan.

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

Relation-level SIREAD locks kill concurrency. Transactions that should run in parallel start conflicting. You see retry storms and pg_stat_activity filling with lock-wait sessions.

Three WHERE Clause Patterns That Work in Production

Tenant + bounded status

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

Soft-delete elimination

CREATE INDEX idx_users_active
  ON users (tenant_id, email)
  WHERE deleted_at IS NULL;
Enter fullscreen mode Exit fullscreen mode

On a system with 5% deleted records, this eliminates 5% of index bloat immediately.

Time-bounded hot data

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

Validate the Planner Uses Your Index

Always verify:

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;
Enter fullscreen mode Exit fullscreen mode

Look for Index Only Scan using idx_events_active_tenant. Seeing Seq Scan or Bitmap Heap Scan with high Buffers: shared hit means your query's filter is not a logical subset of the index predicate.

Gotchas

NOW() is volatile — you cannot use it in a partial index predicate. The docs do not emphasize this clearly enough.

-- This will NOT work
CREATE INDEX ... WHERE created_at > NOW() - INTERVAL '90 days';

-- Use an immutable timestamp literal instead
CREATE INDEX ... WHERE created_at > '2026-04-25'::timestamptz;
-- Or materialize the condition as a GENERATED boolean column and index on that
Enter fullscreen mode Exit fullscreen mode

REPEATABLE READ does not give you SSI. Predicate lock escalation is specific to SERIALIZABLE isolation. If you are on REPEATABLE READ believing you have full serializability, you do not. Upgrade to SERIALIZABLE and apply partial indexes immediately to keep the predicate lock surface manageable.

The planner must imply your predicate. Treat partial index predicates as schema contracts in your query layer. If the application's WHERE clause does not logically imply the index predicate, the planner ignores the index entirely. Document these contracts alongside your migrations.

Three Actions to Take Today

  1. Run pg_relation_size across your indexes. If your largest index exceeds 30% of table size on a filtered dataset, a partial index is almost certainly warranted.

  2. Align your application's WHERE clauses with index predicates and document them as part of your schema contract.

  3. Query pg_locks where locktype = 'relation' during peak read traffic. Relation-level SIREAD locks are your canary. If you see them, your scan surface is too wide and a partial index will directly reduce escalation frequency.

Here is the gotcha that will save you hours: composite indexes are obvious and well-documented. Partial indexes are neither — but on multi-tenant tables at scale, they are the higher-leverage move every time.

Top comments (0)