Add a partial index on `shipment.status_changed_at` #482

Open priya-n wants to apply 1 migration to production-eu from priya/2026-09-partial-index-shipment-status-changed-at

Migration summary from priya-n

priya-n opened this on 11 Sep, 14:22 UTC Author

The dispatch planner reads shipments whose status changed in the last fifteen minutes. It scans 41.2 million rows to find about nine hundred, and the p95 on that query has been over four seconds since the July backfill.

This replaces the unused index on status with a partial index on status_changed_at covering the three statuses the planner asks for. The index build is concurrent, so writes keep flowing while it runs.

#476 finished populating the column in July, so the NOT NULL in the same file is now safe to set.

ledgerhawk linked the migrations this one supersedes #476 Backfill shipment.status_changed_at Merged

#479 Add index on shipment.status Closed never applied, superseded here

db/migrate/20260911142207_index_shipment_status_changed_at.sql +8 −2

Unified diff of the migration file, 8 added lines and 2 removed lines
Hunk		@@ -4,6 +4,12 @@ shipment
4		-- +migrate Up
5		SET lock_timeout = '3s';
6
7	−	CREATE INDEX index_shipment_on_status
8	−	ON shipment (status);
9	+	CREATE INDEX CONCURRENTLY index_shipment_status_changed_at
10	+	ON shipment (status_changed_at DESC)
11	+	WHERE status IN ('picked', 'staged', 'in_transit');
12	+
13	+	ALTER TABLE shipment
14	+	ALTER COLUMN status_changed_at SET NOT NULL;
15	+
16	+	DROP INDEX CONCURRENTLY index_shipment_on_status;
17
18		-- +migrate Down

ledgerhawk replayed the migration against a 41.2 million row clone and attached the lock analysis nine minutes later

Lock analysis

Budget 30 s

Every statement in the file, in order, with the lock it takes on `shipment` and how long it holds it. Estimates come from the replay clone, not from production.
Statement	Lock mode	Blocks	Estimated hold
CREATE INDEX CONCURRENTLY index_shipment_status_changed_at	SHARE UPDATE EXCLUSIVE	Nothing. Reads and writes continue	6 m 40 s
ALTER TABLE shipment ALTER COLUMN status_changed_at SET NOT NULL	ACCESS EXCLUSIVE	Every read and every write, for the whole scan	4 m 12 s
DROP INDEX CONCURRENTLY index_shipment_on_status	ACCESS EXCLUSIVE	Every read and every write, briefly	0.04 s
One statement holds `ACCESS EXCLUSIVE` for 4 m 12 s. The budget is 30 s. Line 5 sets `lock_timeout` to 3 s, which cancels the statement while it waits for a lock, and does nothing once it holds one.

Review from mo-adeyemi

mo-adeyemi requested changes on 11 Sep, 15:04 UTC Database reviewer

The index is right and the concurrent build is right. Line 14 is the problem: setting NOT NULL on an existing column makes Postgres verify all 41.2 million rows while holding ACCESS EXCLUSIVE, so the planner, the dispatch queue and every write to shipment queue behind it for four minutes.

Split it into three. Add a NOT VALID check constraint here. Validate it in a follow-up, which runs under SHARE UPDATE EXCLUSIVE. Then set NOT NULL in a third migration, where Postgres proves the column from the validated constraint and skips the scan. Same end state, longest lock measured in milliseconds.

Happy with the rest of the file. Ask me again and I will approve the split version tonight.

Reply from priya-n

priya-n replied on 11 Sep, 15:31 UTC Author

Three migrations is three deploys, and the third one lands after the freeze lifts on Monday. Until then the planner stays at four seconds a call, through the peak week.

The window on Saturday at 02:00 is the quietest two hours we get. I would rather take the four minutes there, once, and have the whole thing done. What would change your mind?

Reply from mo-adeyemi

mo-adeyemi replied on 11 Sep, 15:52 UTC Database reviewer

02:00 UTC is quiet for us and not for the carriers. Saturday morning we still take around 180 callbacks a minute off the west coast, and two of those carriers give up after three minutes and mark the shipment undeliverable. Four minutes of ACCESS EXCLUSIVE queues every one of them.

The planner does not have to wait for any of this. Line 9 builds concurrently and takes no lock worth the name, so ship lines 9 to 11 tonight and the four seconds go away tonight. The constraint is one more deploy this week and the NOT NULL can land on Monday with nothing riding on it.

Checks

2 passed, 1 warning, 1 failing

Lock budget: failing. One statement holds ACCESS EXCLUSIVE for 4 m 12 s. The budget is 30 s.

14:31

Change window: warning. This window overlaps the Friday freeze on the EU fleet.

14:24

Replay on the clone: passed. Applied and rolled back cleanly, 7 m 02 s end to end.

14:31

Reversibility: passed. The down migration restores the index it drops.

14:24

Two things block applying

Lock budget is failing. Line 14 holds every reader and writer of shipment for 4 m 12 s.
This migration needs one approval from a database reviewer. mo-adeyemi requested changes at 15:04 UTC.
The replay passed on a clone of production-eu.

Applying opens when the lock budget passes and one database reviewer approves.

Add your review

Design notes

Shape of the page

A side rail holding the week's releases, a bar that stays put, and one migration filling the work area.

What it leads with

The conversation. The review is the spine of the page, and the changes, the lock table and the checks sit under the sentences that refer to them.

Spacing

Tight, on GitHub's compact setting, with every control growing to a comfortable target on a touch screen.

The one bold decision

State is never carried by colour alone. Open, merged and closed each get an icon, a word and a shape of their own, and the rail repeats all three six times.

The three state marks: Open is a filled pill, merged a filled bar, closed an outline with nothing in it. Printed in grey, the open and merged colours are the same tone, in every theme GitHub ships, so no change of colour could have told them apart. The shape, the icon and the word do it instead.
The rail: Six migrations in one release week, one of them this page. The other five are rows rather than links, and it is the state vocabulary that makes the rail readable at a glance.
Where the colour comes from: Every colour, size and spacing step is GitHub's own published Primer set, taken from the system rather than sampled. The three state pills take the colours Primer reserves for exactly that job, because those are the ones it keeps separate in its colour-blind themes as well.
Readability, measured: The small print under a comment: 5.74:1 light, 5.94:1 dark. The label on a state pill: 4.52:1 light, 4.63:1 dark. That second pair is tight against the floor of 4.5, so the label is set a size larger and slightly heavier.
The second state: The migration blocked, with both failing conditions named and the main action switched off beside the reason. Overriding it means typing the name of the thing being changed.
Line numbers: The rows of a code diff are too close together for a line number to be a safe target on a touch screen, so a number here identifies its line and is not something to tap. A link to the page still lands on the correct line and still highlights it, and the links that reach a line sit in the sentences discussing it.
What we changed: Nothing in the palette. Two readability standards disagree about those two pairs at these sizes, and the values are GitHub's own; rewriting them here would break the thirteen other themes Primer ships.