Stage 7 · Master
Advanced PostgreSQL
Indexing for Multi-Tenant Queries
PostgreSQL is still a planned dependency in meridian. This chapter therefore specifies the index strategy for Meridian's shared-schema design: tenant_id leads the access path, and every index must justify itself against a real service query pattern.
Shared-Schema Starts the Problem
Meridian's tenancy model is already fixed even before Postgres is wired: every tenant-scoped table will live in a shared schema and carry a tenant_id column. That immediately determines the indexing discipline. Query selectivity must begin with tenant isolation, because almost every identity-service, community-service, and billing-service lookup will filter on tenant_id before it filters on membership, resident, unit, invoice, or payment identifiers.
No migration files, schema definitions, or live queries exist in meridian yet. The SQL in this lesson is a specification for the persistence phase, grounded in the documented service responsibilities and shared-schema tenancy model.
tenant_id-First Indexes
| Index shape | When it helps | When it fails |
|---|---|---|
| (tenant_id, created_at DESC) | Listing tenant-scoped rows ordered by recency | Weak for lookups that also filter on unit_id or resident_id |
| (tenant_id, unit_id, due_date DESC) | Listing invoices for one unit inside one tenant | Useful only if that exact lookup exists often enough to justify maintenance cost |
| (unit_id, due_date DESC) | Rarely appropriate in Meridian | Cross-tenant scans become possible before isolation is applied |
The shared rule is simple: if a table is tenant-scoped, tenant_id should usually be the leading column in the index or be enforced by a partial design that still preserves tenant isolation. Putting a business identifier first only makes sense if that identifier is already globally unique and the access path truly never begins with tenant scoping. For most HOA data, that will not be the case.
Service Query Patterns
apps/identity-service/migrations/001_memberships.sqlcreateResponsibility: Create memberships tables and tenant-first indexes for tenant/user/role lookups.
Why now: identity-service will frequently answer membership and role queries scoped to one tenant.
Connects to: Supports the gateway-owned tenant resolution and future authorization checks.
apps/community-service/migrations/001_residents_units.sqlcreateResponsibility: Create residents, units, and building indexes that keep resident and unit listings tenant-local.
Why now: community-service owns the highest volume of directory-style lookups inside one HOA.
Connects to: Will back resident rosters, unit occupancy views, and complaint ownership checks.
apps/billing-service/migrations/001_invoices_payments.sqlcreateResponsibility: Create invoice and payment indexes for per-tenant, per-unit, and per-resident financial queries.
Why now: billing-service must list and aggregate financial data without scanning unrelated tenants.
Connects to: Supports invoice listing, overdue balance checks, and payment history retrieval.
CREATE INDEX CONCURRENTLY idx_invoices_tenant_unit_due_date
ON invoices (tenant_id, unit_id, due_date DESC);
CREATE INDEX CONCURRENTLY idx_payments_tenant_invoice_created_at
ON payments (tenant_id, invoice_id, created_at DESC);The leading tenant_id column preserves shared-schema isolation at the access-path level before unit_id or invoice_id narrows the search further.
Validation with EXPLAIN
Index design does not stop at migration authoring. Each planned index must be validated against the concrete repository query that motivated it. The acceptance check is not merely 'the migration succeeded'; it is 'EXPLAIN shows the intended tenant-first access path for the actual WHERE and ORDER BY clauses.' Composite indexes that are never used only add write overhead and longer vacuum work.
EXPLAIN ANALYZE
SELECT invoice_id, unit_id, due_date, amount_due
FROM invoices
WHERE tenant_id = $1 AND unit_id = $2
ORDER BY due_date DESC
LIMIT 50;The query pattern is expressed in Meridian's real domain nouns: tenant, unit, invoice. That is the level at which the index must earn its keep.
The Decision
Meridian's future Postgres indexing strategy follows directly from its documented tenancy model: shared schema, tenant_id on every tenant-scoped table, and tenant-first composite indexes designed from real service queries. Indexes are created online where possible and kept only if EXPLAIN validates the intended access path.
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.