Postgres indexes for multi-tenant SaaS: the short list

It's Tuesday, 23:14. The slow query log on the staging replica has eighty rows on a single SELECT against the events table. The query is fine. The plan is a sequential scan of 4.6 million rows because someone shipped a partial index six weeks ago that the planner will not use. Your tenants notice. They send Slack messages with the word "again" in them.

This is the cheatsheet I wish someone had handed me the first time we ran a multi-tenant Postgres database at real scale. By "real scale" I mean the boring middle: 50 to 500 tenants, 100k to 500k rows per tenant on the main tables, mixed OLTP plus a few reporting queries that need to come back in under two seconds. Single database, single schema, tenant_id on every row. The version most of you actually run.

The workhorse: composite B-tree starting with tenant_id

If you remember one thing from this post, remember this. In a multi-tenant database with tenant_id on every row, the first column of every functional index should be tenant_id. Not "should usually be". Should.

The planner has no way to know that tenant 42 only owns 1% of the rows in your invoices table. The statistics it keeps are global. A B-tree on (status, created_at) looks reasonable in EXPLAIN until you run it for a real tenant in production and Postgres decides a bitmap scan plus a filter is cheaper than the index walk. It is not.

CREATE INDEX invoices_tenant_status_created_idx
  ON invoices (tenant_id, status, created_at DESC);

This one index serves three query shapes at once: list a tenant's invoices, list a tenant's invoices by status, and list a tenant's most recent invoices. Postgres can use a prefix of a B-tree, so all three plans come out clean.

Partial indexes for the hot 5%

Most SaaS tables have a tiny hot subset (active jobs, pending tasks, unread notifications) and a large cold tail. A partial index is the cheapest, fastest way to make that hot subset fly.

CREATE INDEX jobs_active_idx
  ON jobs (tenant_id, scheduled_for)
  WHERE status = 'active';

The trade-off is that the predicate has to match the query exactly. WHERE status = 'active' on the index will not be used if your query is WHERE status IN ('active', 'queued'). The planner is conservative about partial-index matching and will not "almost match". Get the predicate wrong and you have a pointless index that nobody uses, but Postgres still has to keep it up to date on every write.

Covering indexes with INCLUDE for read-heavy paths

Postgres 11 added INCLUDE, which lets you tack non-key columns onto a B-tree so the planner can do an index-only scan. For multi-tenant SaaS this matters most on the table that gets queried by the dashboard on every page load.

CREATE INDEX projects_tenant_idx
  ON projects (tenant_id, archived_at)
  INCLUDE (name, owner_id, updated_at);

Now the dashboard query (list project name, owner, last update for one tenant) does not touch the heap. On a 200k-row-per-tenant projects table we have seen this take a 28ms query down to 4ms. The cost is index size: that INCLUDE clause makes the index roughly twice as big. Worth it for the read path. Not worth it for tables you write to constantly.

BRIN for append-only timelines

BRIN (Block Range INdex) is the index type most SaaS teams underuse. If you have a table that only ever grows by append (events, audit_logs, webhook_deliveries) and you query it almost entirely by time range plus tenant, a BRIN is a tenth the size of a B-tree and just as fast for the access pattern you actually have.

CREATE INDEX events_brin_idx
  ON events USING BRIN (tenant_id, occurred_at)
  WITH (pages_per_range = 32);

The catch: BRIN only helps when the physical order of rows on disk matches the indexed columns. For an append-only table that is naturally true (rows arrive in time order). If you delete and re-insert, or if you have a heavy backfill, BRIN performance falls apart. Use CLUSTER once after the backfill, then let it stay append-only. The Postgres docs spell this out clearly if you want the full picture.

GIN for jsonb and search

Every SaaS database ends up with a jsonb column somewhere: custom fields, webhook payloads, feature flags. The moment you start querying inside that jsonb (WHERE payload ->> 'event_type' = 'paid'), the sequential scan returns. GIN is the answer.

CREATE INDEX events_payload_gin_idx
  ON events USING GIN (payload jsonb_path_ops);

Use jsonb_path_ops unless you genuinely need the full operator class. It is two or three times smaller and faster for the containment queries (@>) that 90% of jsonb queries actually use. The jsonb indexing docs have the operator class table if you need to look it up.

Same index type for full-text search, but build a generated tsvector column first. Indexing an expression at query time defeats the point.

Three index types we do not reach for

Hash indexes

Postgres hash indexes have been crash-safe since version 10 and people keep writing blog posts about how they are "now production-ready". They are. They are also still slower than a B-tree on equality lookups for any column you would actually index. Hash gives you exact-match only: no range, no ORDER BY, no prefix. A B-tree on the same column does equality lookups in roughly the same time and gives you the other operations for free. We have never measured a query where hash won.

Bloom indexes

The bloom extension looks magical on paper: index N columns at once, query by any subset. In practice the false-positive rate eats the win, the index is large, and the planner does not estimate it well. The use case (wide tables with arbitrary multi-column filters and no obvious leading column) is real but rare. If you think you need a Bloom index, you probably need to look at your query patterns instead and discover that 3 of the 12 columns account for 95% of the WHERE clauses.

SP-GiST for the things B-tree already does

SP-GiST is genuinely useful for spatial data and some text patterns. It is not useful for the kinds of equality and range lookups multi-tenant SaaS workloads spend their time on. If you are not indexing geometry or doing phone-prefix matching, B-tree wins on every dimension that matters.

The five-minute audit

Before you create a single new index, run this on your production replica. It will tell you which indexes are doing nothing and costing you writes.

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT conindid FROM pg_constraint WHERE conindid > 0
  )
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 25;

The output is the punch list. Anything older than two weeks with zero scans is a candidate for DROP INDEX CONCURRENTLY. Anything large with low idx_scan relative to the table's read traffic is a candidate for re-examination.

When we built the reporting backend for a SaaS client whose largest tenant had 1.4M invoices, the thing we kept hitting was that every "obvious" index someone had added in the first year was being skipped by the planner because tenant_id was not the leading column. We rebuilt the index set from scratch in one migration (dropped 11, added 6, p95 dashboard query from 1.9s to 180ms), which is most of what our backend and database rescue engagements turn out to be.

Open one slow query from last week. Run EXPLAIN (ANALYZE, BUFFERS) on it. Look at the first sequential scan. If the table has tenant_id and the query has a tenant_id predicate, you already know the index that is missing.