When an application's database table grows from 5,000 rows in development to 5,000,000 rows in production, unoptimized queries that previously finished in 4 milliseconds begin to take 12 seconds. CPU utilization spikes to 100%, database connection pools exhaust, and users experience application timeouts.

The naive fix is often scaling up compute instances or adding read replicas. However, in over 85% of production bottlenecks, the real issue is missing indexes or index scans that degrade into full sequential table scans.

Here is how to design and tune PostgreSQL indexes for high-throughput SaaS workloads.

Deconstructing EXPLAIN (ANALYZE, BUFFERS)

Before creating an index, you must inspect the PostgreSQL query planner's execution path:

sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE tenant_id = 'acme_corp'
  AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 20;

Critical Red Flags in Query Plans:

  • Seq Scan on orders: The engine read every single data block from disk into memory.
  • Buffers: shared read=18492: Indicates data was not in RAM cache and had to be fetched via physical disk I/O.
  • Sort Method: external merge Disk: The query planner did not have enough work_mem to sort rows in memory and spilled temporary data to the SSD.

    • The Three Essential Indexing Strategies

    1. Composite B-Tree Indexes & The Leftmost Prefix Rule

    When a query filters across multiple columns, creating separate indexes on each column is ineffective. PostgreSQL must merge bitmaps in memory. Instead, create a composite multi-column index:

    sql
    CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

    Column Ordering Rule:

  • Equality Columns First: Place columns tested with = first (tenant_id, status).
  • Range / Sort Columns Last: Place columns tested with >, <, or ORDER BY last (created_at).

    • 2. Partial (Filtered) Indexes

    In multi-tenant SaaS systems, most rows represent historical, inactive data (e.g., status = 'COMPLETED'), while 95% of operational queries look for active rows (e.g., status = 'PENDING').

    Indexing millions of completed rows wastes disk space and slows down write operations:

    sql
    -- Creates an index that ONLY contains pending orders
CREATE INDEX CONCURRENTLY idx_orders_pending_active
ON orders (tenant_id, created_at DESC)
WHERE status = 'PENDING';

    This partial index is 90% smaller than a full-table index, fits entirely in RAM, and makes queries for pending orders nearly instantaneous.

    3. Generalized Inverted Indexes (GIN) for JSONB

    PostgreSQL is often used as a high-performance document store via the jsonb column type. Standard B-Tree indexes cannot index nested keys inside arbitrary JSON payloads.

    sql
    -- Table storing dynamic device telemetry payloads
CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);

-- Create a GIN index on the jsonb column using jsonb_path_ops
CREATE INDEX CONCURRENTLY idx_audit_logs_payload_gin
ON audit_logs USING GIN (payload jsonb_path_ops);

    Now, queries checking for specific nested attributes execute via lightning-fast index lookups:

    sql
    SELECT * FROM audit_logs
WHERE payload @> '{"event": "AUTH_FAILURE", "country": "PH"}';

    The Cost of Indexing: Writing Safely in Production

    Every index added to a table incurs a write penalty on every INSERT, UPDATE, and DELETE statement.

  • Always Use `CONCURRENTLY`: In production, running CREATE INDEX takes an ACCESS EXCLUSIVE lock that blocks reads and writes until complete. Always execute CREATE INDEX CONCURRENTLY, which builds the index in the background without locking application traffic.
  • Audit Unused Indexes: Use pg_stat_user_indexes monthly to detect and drop indexes that consume disk space without ever being selected by the query planner.