Indexing for Multi-Tenant Queries

Tenant First or the Index Is Mostly Decorative

Multi-tenant schemas punish generic indexing advice. In Fieldwork, every important table is scoped by tenant_id, and many hot queries also include workspace_id, project_id, or membership role. The first indexing mistake we rejected was designing indexes around entity-local access patterns while treating tenant_id as a filter you can tack on later. In a shared-table system, tenant_id is not incidental context. It is the primary boundary that keeps scans narrow and predictable. If it is absent or buried late in the index, Postgres has to do more work before it can even get to the rows your request is allowed to see.

The alternative sounded reasonable: index by the obvious business key first, such as project_id for task lookups or user_id for memberships, because those are the values the handler already has. But that design leaks a single-tenant assumption into a multi-tenant system. In Fieldwork, project IDs may be globally unique, but the bulk of list and search queries still begin by narrowing to one tenant and often one workspace. Leading with tenant_id gave the planner an index that matches the real access path instead of a theoretical entity lookup divorced from authorization scope.

Designing Composite Indexes From Real Queries

The next step was to design indexes from the actual repository methods instead of from table schemas alone. Fieldwork's task list endpoint does not ask for "all tasks by project_id." It asks for tasks inside one tenant, one workspace, one project, ordered by created_at descending with pagination. That means the useful index is shaped by equality filters first and sort order next. We rejected a one-column-per-problem mindset because it creates many narrow indexes that each almost fit, while the planner still needs to sort or recheck a lot of rows.

This is where repository code helped. Because the SQL was explicit, we could read the exact predicates and ordering clauses the application produced. That made composite index design much less mystical. Equality columns first, then the range or sort column, then included columns only when they removed expensive heap visits for a proven hot path. The decision was driven by the query shape we owned, not by generic advice about indexing everything that appears in a WHERE clause.

CREATE INDEX CONCURRENTLY idx_tasks_tenant_workspace_project_created_at
    ON tasks (tenant_id, workspace_id, project_id, created_at DESC);

CREATE INDEX CONCURRENTLY idx_memberships_tenant_workspace_user_role
    ON memberships (tenant_id, workspace_id, user_id, role);

CREATE INDEX CONCURRENTLY idx_projects_tenant_workspace_archived_created_at
    ON projects (tenant_id, workspace_id, archived_at, created_at DESC)
    WHERE archived_at IS NULL;

Read the Plan, Not Your Intentions

The most expensive indexing mistake is assuming the database agrees with your idea. We forced ourselves to verify with EXPLAIN ANALYZE because a beautifully named index can still be ignored. Sometimes the planner prefers a sequential scan because the table is small. Sometimes the statistics suggest the filter is not selective enough. Sometimes the ORDER BY or LIMIT means another index is cheaper. The only honest way to know is to read the plan.

Fieldwork's rule became simple: no new index is "done" until we run the real query with realistic tenant-scoped parameters and confirm the chosen plan matches the reason we created the index. If it does not, we either change the index, change the query shape, or accept that the index was not worth the write amplification it imposes. That verification step prevented the common trap of accumulating indexes that feel right but mostly slow down inserts and updates.

EXPLAIN ANALYZE
SELECT id, title, status, assignee_user_id, created_at
FROM tasks
WHERE tenant_id = 'tenant_42'
  AND workspace_id = 'ws_ops'
  AND project_id = 'proj_outage'
ORDER BY created_at DESC
LIMIT 50;

Limit
  ->  Index Scan using idx_tasks_tenant_workspace_project_created_at on tasks
        Index Cond: ((tenant_id = 'tenant_42') AND (workspace_id = 'ws_ops') AND (project_id = 'proj_outage'))

What We Indexed in Fieldwork

By the time the schema stabilized, the pattern was consistent. Tables that are always accessed under tenant scope lead with tenant_id. The next columns reflect the query's equality filters in order of common use, followed by sort columns where list endpoints need ordered pagination. Partial indexes appear only when a real predicate like archived_at IS NULL meaningfully shrinks the hot working set. We did not index every foreign key combination just because we could. Every index had to justify its read benefit against write cost.

That is the decision to keep: tenant-first composite indexes, designed from actual repository queries, validated with EXPLAIN, and pruned when they do not pay their way. In a multi-tenant backend, indexing is less about memorizing clever DDL and more about respecting the access path the application actually forces every request to take.

• Lead composite indexes with tenant_id when the table is shared across tenants and requests are tenant-scoped.
• Design from real WHERE and ORDER BY clauses, not from abstract table diagrams.
• Prefer one well-shaped composite index over several narrow indexes that each only half-match the query.
• Use partial indexes when a stable predicate meaningfully trims the hot row set.
• Verify index utility with EXPLAIN ANALYZE before accepting the extra write overhead.