Great Stack to Doesn't Work #1

PostgreSQL: "I Added an Index and It Got Slower"

A survival guide for when everything goes wrong in production.

You've been there. The query is slow. You check the table — no index on the WHERE clause column. You add one. You deploy. You check again.

It's slower.

Not a little slower. Ten times slower. Your index, the thing that was supposed to fix everything, just made it worse. And now your PM is asking why the dashboard takes 14 seconds to load.

Welcome to PostgreSQL's query planner. It has opinions, and it doesn't care about yours.

The Query Planner Has a Plan (And It's Not Yours)

PostgreSQL doesn't just "use your index." It makes a decision. Every single time a query runs, the planner evaluates dozens of possible execution strategies and picks the one it thinks will be cheapest. Key word: thinks.

The planner uses statistics — pg_statistic stores data distribution info for every column. How many distinct values? What's the most common value? What does the histogram look like? These stats feed into cost estimates, and cost estimates determine whether your index gets used or ignored.

Here's the first thing most engineers miss: EXPLAIN shows you the plan. EXPLAIN ANALYZE shows you the plan and what actually happened. The gap between estimated rows and actual rows is where bugs live.

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

If the planner estimates 100 rows but gets back 2 million, it chose the wrong strategy. It probably did an index scan when a sequential scan would have been faster. Or worse — it did a sequential scan because the stats said "pending" is rare, but your last batch job just created 2 million pending orders and the stats haven't caught up.

Run ANALYZE orders; and try again. If the plan changes, your stats were stale. If it doesn't change, keep reading.

The Index Trap: When More Is Less

B-Tree indexes are the default, and for single-column equality or range queries, they're excellent. But engineers treat them like magic. They're not. They're data structures with rules.

Composite index column order matters more than you think. An index on (status, created_at) is not the same as (created_at, status). PostgreSQL reads composite indexes left to right. If your query filters on created_at and status, but your index leads with status, the planner might skip your index entirely because it can't efficiently seek to the right created_at range without first knowing the status.

The rule: put the highest-selectivity column first. If status has 5 distinct values and created_at has millions, lead with created_at. Unless you're always filtering by status first — then lead with status. Context wins.

Partial indexes are underused. If 95% of your queries only care about active records:

CREATE INDEX idx_orders_active ON orders (created_at) WHERE status = 'active';

This index is smaller, faster to scan, and faster to maintain. It doesn't cover queries about archived orders, and that's the point.

Expression indexes solve computed filters. If you keep writing WHERE LOWER(email) = 'john@example.com':

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Without this, PostgreSQL can't use a regular index on email because the function transforms the value. It has to scan every row and compute LOWER() each time.

Covering indexes eliminate table lookups. If your query only needs id and status, and your index includes both:

CREATE INDEX idx_orders_status_covering ON orders (status) INCLUDE (id);

PostgreSQL can answer the query from the index alone (an index-only scan) without touching the table. On large tables, this is the difference between 2ms and 200ms.

The 5 Reasons Your Query Is Still Slow After Adding an Index

I've debugged hundreds of slow queries. These five causes cover 90% of cases.

1. Wrong index type. You used B-Tree for a full-text search or a JSONB containment query. B-Tree handles equality and range. For text search, use GIN. For geometric or range operations, use GiST. For large sequential datasets with natural ordering (timestamps on append-only tables), consider BRIN — it's 1000x smaller than B-Tree and surprisingly fast for the right workload.

2. Stale statistics. Autovacuum updates stats, but it can lag behind bulk operations. After a large INSERT, UPDATE, or DELETE, run ANALYZE manually. One team I worked with disabled autovacuum on their largest table "for performance" and spent 3 days wondering why every query was doing sequential scans. Don't be that team.

3. Low selectivity. An index on a boolean column with 50% TRUE and 50% FALSE is almost useless. The planner correctly decides that scanning half the table via index is more expensive than just reading the whole thing sequentially. Indexes work best when they eliminate most of the rows.

4. Too many indexes. Every index slows down writes. INSERT, UPDATE, DELETE — all of them now have to maintain every index. I've seen tables with 23 indexes where 17 were never used. Check pg_stat_user_indexes and look for idx_scan = 0. If an index hasn't been scanned since the last stats reset, it's dead weight.

5. The planner is right, you're wrong. Sometimes a sequential scan IS faster. On a small table (under 10K rows), the overhead of index traversal can exceed the cost of just reading everything. On a table that fits entirely in memory with high cache hit rates, sequential access patterns beat random index lookups. Trust the planner until you have hard evidence it's wrong.

Partitioning: When One Table Isn't Enough

Partitioning splits a logical table into physical chunks. PostgreSQL supports range, list, and hash partitioning.

Range partitioning is the most common. A table with 500 million rows partitioned by month means each partition holds roughly 40 million rows. Queries that filter by date only scan relevant partitions. The planner prunes everything else.

CREATE TABLE orders (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    status TEXT,
    total NUMERIC
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_01 PARTITION OF orders
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

But partitioning is not free. Cross-partition queries can be slower than a single well-indexed table. Partition maintenance (creating new partitions, archiving old ones) is operational overhead. And if your queries don't filter on the partition key, partitioning doesn't help — it might hurt because the planner now has to plan across multiple partitions.

The rule: partition when your table exceeds your maintenance capacity, not at a specific row count. If vacuum runs are taking hours, if index rebuilds are blocking production, if your backup window is too tight — that's when you partition.

Vacuum: The Janitor You Can't Fire

PostgreSQL's MVCC (Multi-Version Concurrency Control) means every UPDATE creates a new row version and marks the old one as dead. DELETE marks rows as dead. These dead tuples pile up, bloating your table and indexes.

Vacuum cleans them up. Autovacuum does it automatically, but it's conservative by default.

The critical parameters:

• autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor: determines when vacuum kicks in. Default is 50 rows + 20% of the table. For a 100 million row table, that means 20 million dead tuples before vacuum runs. That's too many.
• autovacuum_vacuum_cost_delay: how aggressively vacuum works. Default is gentle. On high-write tables, crank it down.

For large tables, set per-table autovacuum parameters:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 10000,
    autovacuum_vacuum_cost_delay = 2
);

This tells autovacuum: run after 1% dead tuples or 10K dead rows, whichever comes first, and work more aggressively.

The biggest vacuum disaster I've seen: A team disabled autovacuum on their main transaction table because "it was causing latency spikes." For three days, everything seemed fine. Then queries started taking 30 seconds. Then 2 minutes. Then the table was so bloated that vacuum itself would take 6 hours to complete, and they couldn't afford 6 hours of elevated I/O. They ended up doing pg_repack during a maintenance window at 3 AM on a Sunday.

Don't turn off the janitor.

Connection Pooling: PgBouncer Is Not Optional

PostgreSQL forks a new process for every connection. Each backend process costs about 5-10 MB of memory. At 500 connections, that's 5 GB just for connection overhead. At 2,000, your database server is spending more resources managing connections than running queries.

PgBouncer sits between your application and PostgreSQL, multiplexing hundreds of application connections into a handful of database connections.

Two modes matter:

• Transaction mode: a database connection is assigned for the duration of a transaction, then returned to the pool. This is what you want 95% of the time.
• Session mode: a database connection is held for the entire application session. Use this only if your app relies on session-level state (prepared statements, temp tables, SET commands).

The catch with transaction mode: prepared statements break. The application prepares a statement on connection A, but the next query runs on connection B, which doesn't know about that prepared statement. Solutions: use pgbouncer's prepared_statement mode (if available), or disable prepared statements in your ORM, or use session mode for specific services that need them.

The 47 TB Story

I'll keep this brief because the details matter more than the drama.

Financial database. 47 TB. 12 billion rows across 40 tables. The main transaction table had 8 billion rows and 23 indexes. Average query time: 4.2 seconds. Target: under 500ms.

What we did:

1. Killed 14 unused indexes. Checked pg_stat_user_indexes, confirmed zero scans, dropped them. Write throughput improved 35% overnight.
2. Reordered 3 composite indexes. The most-queried filter was (account_id, transaction_date) but the index was (transaction_date, account_id). Swapping the order turned a 3-second query into a 12ms query.
3. Added BRIN indexes for time-series scans. The audit queries that scanned months of data didn't need precise row-level indexing. BRIN indexes were 500x smaller and fast enough.
4. Partitioned by month. Each partition: ~600 million rows. Vacuum now ran per-partition, completing in minutes instead of hours.
5. Tuned autovacuum per table. High-write tables got aggressive settings. Read-heavy reference tables kept the defaults.
6. Deployed PgBouncer in transaction mode. Connection count dropped from 1,800 to 60 actual database connections.

Result: average query time dropped from 4.2 seconds to 180ms. P99 dropped from 12 seconds to 900ms. No schema changes. No application rewrites. Just understanding how PostgreSQL actually works.

Key Takeaways

The query planner is smart, but it's working with the information you give it. Stale stats, wrong index types, and missing vacuum runs are not PostgreSQL bugs — they're operator errors.

Before adding an index, run EXPLAIN ANALYZE. After adding it, run EXPLAIN ANALYZE again. If the plan didn't change, the index isn't being used, and you need to understand why before adding more.

Every fast database started as a slow one that someone took the time to understand.

Over to You

What's the worst index-related performance disaster you've encountered? Did you ever add an index that made things slower? Drop your war story below.

If you enjoyed this, I write about production engineering, AI systems, and the messy reality of building software at scale.

Follow me:

• LinkedIn — Mehmet TURAÇ
• X/Twitter — @TuracTheThinker

This is part of the **Great Stack to Doesn't Work* series — a survival guide for when everything goes wrong in production. Follow the series to catch every episode.*