PostgreSQL Query Optimization for SaaS Applications: Indexes, EXPLAIN, and Partitioning
How to diagnose and fix slow PostgreSQL queries in a growing SaaS application — from reading EXPLAIN ANALYZE output to choosing the right index type, and when to reach for table partitioning.
By POINTNEXIS Team
PostgreSQL performance problems follow a predictable pattern in SaaS products: everything is fine at 10,000 rows, slow at 100,000, and broken at 1,000,000. The fixes are rarely exotic — they are almost always about missing indexes, sequential scans on large tables, or N+1 query patterns in the ORM layer.
This guide covers the diagnostic approach and the fixes, in order of impact.
Reading EXPLAIN ANALYZE Output
Prefix any slow query with `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` to see the actual execution plan with timing and buffer hit counts. Look for: `Seq Scan` on large tables (index needed), high `rows` estimates vs actual (stale statistics — run `ANALYZE`), and `Hash Join` with large hash batches (memory pressure).
Use `pg_stat_statements` extension to identify the slowest queries by total execution time across your application — not just the queries that feel slow in development. The queries costing the most cumulative time are the highest-priority optimization targets.
Choosing the Right Index Type
B-tree indexes (the default) cover equality and range queries on sortable columns. GIN indexes are for JSONB, array containment, and full-text search. BRIN indexes work well for naturally-ordered append-only tables (timestamps, sequential IDs) at very low storage cost.
Partial indexes are underused: `CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL` indexes only active users. Queries that filter by the same condition get a tiny index scan instead of a full table scan with a filter. Composite indexes follow column order — put the highest-cardinality equality column first, range columns last.
Avoiding N+1 Queries with ORMs
N+1 queries — one query to load a list, then one per item for related data — are the most common SaaS performance mistake. In Prisma, use `include` to eager-load relations. In SQLAlchemy, use `joinedload` or `selectinload`. In TypeORM, use `relations` in the `find` options.
Enable query logging in development to spot N+1 patterns early. Tools like `prisma-query-inspector` or Django Debug Toolbar (for Django ORMs) make N+1 visible without reading query logs manually.
Table Partitioning for Scale
Declarative table partitioning (PostgreSQL 10+) splits a logical table into physical partitions by range (date ranges), list (tenant IDs), or hash. Queries that filter by the partition key skip partitions entirely — turning a million-row scan into a ten-thousand-row scan.
For multi-tenant SaaS, hash partitioning by `tenant_id` distributes data evenly and scopes queries to a single partition automatically. For time-series data (events, logs, audit trails), monthly range partitions with automated creation via `pg_partman` handle retention and query performance simultaneously.