Postgres indexes: the only guide I wish I had

Feb 28, 2024 updated May 6, 2024 #postgres #databases

B-tree, partial, covering, and GIN indexes with the queries that motivated each one.

What surprised me most was how small the final change was. Most of the work was understanding the problem precisely enough that the fix became obvious, and that understanding took far longer than the fix itself.

The problem

The first version worked, which is exactly why it was hard to see how wrong it was. Everything looked fine in the demo, and everything looked fine until traffic arrived on a Tuesday afternoon and the graph started doing something I'd never seen before.

Tools matter less than the feedback loop they enable. A fast, boring loop beat every sophisticated setup I tried, and I've kept it ever since, even as the rest of the stack changed twice over.

First attempt

Tools matter less than the feedback loop they enable. A fast, boring loop beat every sophisticated setup I tried, and I've kept it ever since, even as the rest of the stack changed twice over.

A note for context that helps the reader.

fn retry<T>(mut f: impl FnMut() -> Result<T>, times: u32) -> Result<T> {
    let mut last = None;
    for _ in 0..times {
        match f() {
            Ok(v) => return Ok(v),
            Err(e) => last = Some(e),
        }
    }
    Err(last.unwrap())
}

A better model

Tools matter less than the feedback loop they enable. A fast, boring loop beat every sophisticated setup I tried, and I've kept it ever since, even as the rest of the stack changed twice over.

I kept a log of every decision during this project, and reading it back a month later was humbling: the choices that aged well were the boring ones, and the clever ones all came with a footnote I hadn't written yet.

The best engineering advice I've received is unglamorous: make it measurable, make it reversible, and only then make it fast.

Results

The numbers below are from my own machine and my own workload. Treat them as a method, not a benchmark: the point is how the measurement was taken, not the exact figures it produced.

I want to be honest about the trade-offs here. The approach I settled on solves the problem I had, not every problem of this shape, and there is a clear point where you should reach for something more heavy-duty.

Approach	Best for	Watch out for
Baseline	Quick comparisons	No tail visibility
Instrumented	Finding the real hotspot	Overhead of the probes
Production	Truthful numbers	Harder to reproduce

Trade-offs

Tools matter less than the feedback loop they enable. A fast, boring loop beat every sophisticated setup I tried, and I've kept it ever since, even as the rest of the stack changed twice over.

A tip for a practical recommendation.

The problem

const debounce = (fn, ms = 200) => {
  let t;
  return (...args) => {
    clearTimeout(t);
    t = setTimeout(() => fn(...args), ms);
  };
};

First attempt

By the end of this post you should be able to reproduce the whole thing yourself in under an hour, including the parts I got wrong the first time — those are marked so you can skip them if you're in a hurry.

Latency at p50, because most users live there
Latency at p99, because someone always lives there
Error rate, because a fast failure is still a failure
Saturation, because averages hide the cliff

A better model

Wrap-up

If you remember one thing, make it this: b-tree, partial, covering, and gin indexes with the queries that motivated each one compounds. The cost of getting it right early is a afternoon; the cost of getting it late is a migration.