A pull request moved a list’s sort key to a new column. Code review flagged that nothing indexed it and proposed a fix. Over the next few days that one review comment turned into four separate index decisions on the same hot tables, and every one of them went the opposite way from the obvious answer once measured. This is the four, with the numbers.
the index code review asked for could not have worked
The query sorts last_message_at DESC NULLS LAST. The proposed index, written as a schema attribute in the ORM, was (company_id, last_message_at DESC).
A Postgres DESC B-tree index defaults to NULLS FIRST. An ORDER BY ... DESC NULLS LAST cannot use it. The ORM in use had no way to express null ordering on an index at all, so the correct index needed a raw SQL migration. The proposed fix would have created an index the planner never touched, and it would have read as a fix that was applied.
Then the question of whether the sort was even the cost. The largest tenant held 1,059 rows in the list. The p99 tenant held 74. Sorting a thousand rows is sub-millisecond. The column was also null on every row at the time, so the entire population sorted through the fallback key anyway. The previous sort key had also been unindexed, which matters: the PR was not a regression and should not be treated as one.
Rule: do not index a sort that costs sub-millisecond time. Write down the threshold at which it would matter (roughly five figures of rows in one partition, here) and move on.
the database was invisible
Before choosing any index, I checked what share of the list query’s 600 ms p99 was the database. Nobody could say. The service emitted HTTP, GraphQL, and middleware spans and no database spans at all. Every proposal in the review thread was a guess about where the time went.
So the first item in the recommendation was not an index. It was instrumenting the ORM so database time shows up in tracing. Everything else was ranked by evidence per effort behind it, and the fix that review asked for landed fourth.
the sort-key index would have zeroed hot updates
Once the column started being written, the ordering index came back for a second look, this time with a write-side measurement.
Two facts killed it. The planner never chose it for the shape the application runs: every caller scopes the list to the viewer’s own memberships, and the plan keeps driving off the existing tenant index and top-N sorts the caller’s own rows, at most a couple hundred. Forcing the join to drive off the membership side instead measured 3.3 ms against 1.0 ms. The new index would have served a query nothing performs.
The write cost was not free either. Postgres can do a heap-only tuple update (HOT) when an update touches no indexed column, which avoids writing a new index entry per index on the table. The column in question is written on every message and every lifecycle event. Indexing it took HOT updates on that table from 85% to 0%, measured over 5,000 updates each way on a seed of about a million messages. The whole feature’s write path would pay, per message, for a read nothing performs.
What shipped instead were two covering indexes on the paths that actually run, which took one badge query from 217 buffers to 64, a batched unread check from 172 to 56, and a filter from a 4.87 ms sequential scan to a 1.41 ms index-only scan.
the include column that cost more than it saved
One of those covering indexes originally carried a read-watermark column in its INCLUDE payload so an unread-count query could run index-only. Measured on a staging-shaped seed: carrying it bought 9 buffers on the badge query and 9 on the unread filter, with timings inside noise. HOT updates on that table fell from 16% to 0% over 3,000 updates each way, with a VACUUM FULL and a warm-up pass between runs so page free space was not the variable.
That column is written on every read-mark and on every send, through the author’s own watermark advancing. The write path would pay 18 buffers per message for 9 buffers on a read. Removed. A different column stayed in the payload because nothing writes it.
Rule, generalized from both cases: before putting a column in an index key or an INCLUDE payload, check whether anything on a per-request write path updates it.
the partial index the planner silently skips
The unread check runs an EXISTS over messages filtered on kind <> 'SYSTEM'. Review proposed a partial index with that predicate. The predicate value is bound as a parameter through a raw query.
Under a custom plan, Postgres knows the parameter value and proves the partial index applies: index-only scan. Under a generic plan, which it switches to after a few executions, it cannot prove a bound parameter satisfies the predicate, so it silently falls back to the older, wider index. Measured on a 400,000-row seed, both behaviors, same query. A hand-written EXPLAIN would only ever have shown the good one.
The INCLUDE (kind) form is used under both plan types. It is a strict superset of the existing index and smaller (26 MB against 31 MB), because INCLUDE columns live only in leaf pages. The old index stays until the statistics view shows the superset serving production traffic, then drops in a follow-up, which is the safe order for replacing an index on a hot write table.
Two mechanics worth knowing from the same change. CREATE INDEX CONCURRENTLY gets one migration file each, because the ORM sends a whole file as one query string, Postgres runs a multi-statement string in an implicit transaction, and CONCURRENTLY is illegal inside one. The correctly ordered NULLS LAST index, when it is eventually earned, is raw SQL for the reason in the first section.
what the four have in common
Every proposal was reasonable on paper, and every one was decided by a measurement the proposal did not include. An index is a bet that a read is worth a write, and the write side is the half that review comments never mention.