Between 10K and 1M rows, specific PostgreSQL query patterns — N+1s, bloated joins, missing partial indexes — silently destroy SaaS performance. Here's how to find and fix them.

How to Optimise PostgreSQL Queries That Slow Down at Scale

Your app flies in staging. You deploy to production, onboard a few thousand users, and suddenly a dashboard that loaded in 200ms is taking four seconds. Nobody touched the queries. Nothing changed — except the data.

This is the most common PostgreSQL performance trap SaaS teams fall into. The problem is rarely the database engine itself. It is almost always a handful of query patterns that look harmless at low row counts but compound badly between 10K and 1M rows. Here is how to identify and fix them.

Why the 10K–1M Range Is the Danger Zone

Below 10K rows, PostgreSQL's sequential scans are fast enough to mask bad queries. Above 1M rows, most teams have already felt the pain and reacted. The middle range is where the damage happens quietly — slow enough to frustrate users, fast enough that no single query looks like the obvious culprit.

The key insight: query cost in PostgreSQL is not linear. A missing index or a poorly structured join does not just add a fixed overhead — it causes the planner to switch strategies entirely, often from an index scan to a sequential scan, multiplying execution time by orders of magnitude.

Pattern 1: The N+1 Query

N+1 is so well-known it has become a cliché — which is exactly why teams stop looking for it.

In a SaaS context, it surfaces in API endpoints that loop over a result set and fire a child query per row. An ORM makes this invisible in code:

-- This runs once per user in the outer loop (N+1 in disguise)
SELECT * FROM subscriptions WHERE user_id = $1 AND status = 'active';

If your endpoint returns 200 users and fires this query 200 times, you have 200 round trips — each with its own parse, plan, and execution cycle. At 10K users with pagination, it still feels acceptable. At 100K, your connection pool saturates.

The fix: Rewrite as a single join or use WHERE user_id = ANY($1::int[]) to batch the lookup. Then verify with pg_stat_statements that query count per request drops proportionally.

Pattern 2: Bloated Joins Across Unfiltered Tables

Joins are not inherently slow. Joins across large tables before filtering are.

Consider a reporting query that joins orders, users, and products to compute monthly revenue. If the join happens before the WHERE created_at > now() - interval '30 days' clause is pushed down, PostgreSQL may hash-join millions of rows before discarding 95% of them.

Run EXPLAIN ANALYZE on the query and look at the rows estimate versus actual rows:

Hash Join  (cost=4821.00..98234.56 rows=182400 width=72)
           (actual time=312.451..4821.003 rows=1840 loops=1)

A massive gap between estimated and actual rows tells you the planner is working with stale statistics. Run ANALYZE orders; and check pg_statistic freshness. If autovacuum is not keeping up with write volume, statistics drift — and the planner makes increasingly wrong choices.

The fix: Ensure filter predicates are pushed as early as possible, use CTEs sparingly (materialised CTEs in PostgreSQL 12 and below are optimisation fences), and keep autovacuum_analyze_scale_factor low on high-write tables.

Pattern 3: Missing Partial Indexes

A full index on status across a 500K-row orders table sounds useful. But if 92% of orders have status = 'completed', and every live query filters on status = 'pending', you are forcing PostgreSQL to index-scan through half a million rows to find eight thousand.

A partial index solves this precisely:

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

Now queries filtered on pending orders scan a tiny, focused structure. Index size drops by 92%, maintenance overhead drops with it, and query time on the hot path falls dramatically.

Partial indexes are underused because ORMs do not generate them. You have to write them deliberately, based on knowledge of your actual data distribution — which is why pg_stats column histograms are worth reading before you reach for a generic index.

Pattern 4: Sort Operations Without Supporting Indexes

ORDER BY on an unindexed column forces a full sort of the result set. At low row counts, this is sub-millisecond. At scale, it triggers disk-spill when the sort exceeds work_mem.

Look for this in EXPLAIN ANALYZE:

Sort  (cost=45821.34..46071.34 rows=100000 width=48)
      (actual time=1823.21..2104.55 rows=100000 loops=1)
  Sort Method: external merge  Disk: 7832kB

external merge Disk is the red flag. The planner ran out of memory and spilled to disk.

Options: Increase work_mem for the session (not globally — it multiplies per sort node per connection), or create a compound index that covers both the filter column and the sort column, allowing PostgreSQL to avoid the sort entirely with an index scan in order.

Reading EXPLAIN ANALYZE Without Getting Lost

Three numbers to focus on first:

Actual time on the outermost node — total wall time
Rows removed by filter — signals a missing or unused index
loops — a high loop count on a nested join node is an N+1 at the database level

Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to also see buffer hits versus disk reads. A high Buffers: shared read relative to hit means your working set is not fitting in shared_buffers — a separate tuning concern.

Putting It Together: A Diagnosis Workflow

Enable pg_stat_statements and sort by total_exec_time descending — find your top five offenders.
Run EXPLAIN (ANALYZE, BUFFERS) on each. Note estimated vs. actual rows, sort methods, and loop counts.
Check pg_stats for column histograms on filter columns. Stale or missing statistics are often the root cause.
Apply targeted fixes: partial indexes, query restructuring, or ANALYZE on specific tables.
Re-measure. Do not trust intuition — trust the numbers.

Why This Matters for Your Project

If you are building a SaaS product on PostgreSQL, the performance headroom you engineer between 10K and 1M rows determines whether you can scale without an emergency infrastructure spend. The fixes above are not theoretical — they are the difference between a team that rewrites their database layer at Series A and one that ships confidently into growth. Query optimisation is a product decision as much as an engineering one.