Portfolio Ledgerhawk
priya-n

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

What the replay found

Longest lock on shipment

Replay of 41.2 million rows, 7 m 02 s end to end

4 m 12 s

Over budget. More than 8 times the limit of 30 s.

Blocking apply

3

Review, checks and one open conversation

Apply window

Sat 12 Sep

02:00 to 04:00 UTC, in 9 h 55 m

Migration summary from priya-n

priya-n opened this 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 needs no backfill first.

priya-n added 1 commit a3f9c1e Add partial index on shipment.status_changed_at
ledgerhawk linked #476 Backfill shipment.status_changed_at Merged which this one builds on
ledgerhawk closed #479 Add index on shipment.status Closed never applied, superseded here
ledgerhawk Bot replayed a3f9c1e on a clone of production-eu

Lock analysis

One statement holds ACCESS EXCLUSIVE on shipment for 4 m 12 s against a budget of 30 s. Estimates come from this replay of 41.2 million rows, not from production.

Locks held on shipment, statement by statement. Time runs left to right, in minutes and seconds into the replay.
  1. CREATE INDEX CONCURRENTLY index_shipment_status_changed_at2 m 50 s

    SHARE UPDATE EXCLUSIVE Nothing waits. Reads and writes continue.

  2. ALTER TABLE shipment ALTER COLUMN status_changed_at SET NOT NULL4 m 12 s

    ACCESS EXCLUSIVE Every read and every write waits for the whole scan.

    3 m 42 s over the 30 s budget

  3. DROP INDEX CONCURRENTLY index_shipment_on_status0.04 s

    ACCESS EXCLUSIVE Every read and every write, briefly.

Line 5 sets lock_timeout to 3 s, which cancels a statement while it waits for a lock and does nothing once it holds one.

mo-adeyemi requested changes Database reviewer

Review from mo-adeyemi

The index is right and the concurrent build is right. Line 12 is the problem, and the fix is on it. Ask me again and I will approve the split version tonight.

Lines 9 to 12 of the new file, where mo-adeyemi left the comment
9+ WHERE status IN ('picked', 'staged', 'in_transit');
10+
11+ALTER TABLE shipment
12+ ALTER COLUMN status_changed_at SET NOT NULL;
mo-adeyemi commented on lines +11 to +12

Setting NOT NULL on an existing column makes Postgres verify all 41.2 million rows while it holds ACCESS EXCLUSIVE. The planner, the dispatch queue and every write to shipment wait behind it for four minutes.

Add the constraint NOT VALID here and 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.

Suggested change
Replace 2 lines with 3 lines
−ALTER TABLE shipment
− ALTER COLUMN status_changed_at SET NOT NULL;
+ALTER TABLE shipment
+ ADD CONSTRAINT shipment_status_changed_at_nn
+ CHECK (status_changed_at IS NOT NULL) NOT VALID;
1 comment
priya-n replied Author

Reply from priya-n

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?

mo-adeyemi replied Database reviewer

Reply from mo-adeyemi

02:00 UTC is quiet for us and not for the carriers. On 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 7 builds concurrently and takes no lock worth the name, so ship lines 7 to 9 tonight and the four-minute lock goes away. The constraint costs one more release this week, and the NOT NULL can land on Monday with nothing riding on it.

Three things block applying

Review required

mo-adeyemi requested changes at 15:04 UTC. The PR needs one approval from a database reviewer.

Checks are failing

1 failing, 1 warning and 2 passing

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

  • Change window: warning. Sat 12 Sep, 02:00 to 04:00 UTC sits inside the EU fleet freeze, which lifts on Monday.

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

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

1 conversation still needs resolving

mo-adeyemi's comment on line 12 is still open.

Go to conversation

Applying opens when the lock budget passes, someone resolves the conversation and one database reviewer approves.

Add a comment

Write in Markdown. Reference a line with #L12. Press Ctrl Enter to post.

db/migrate/20260911142207_index_shipment_status_changed_at.sql

+9 −3
Unified diff of the migration file, 9 added lines and 3 removed lines
Old lineNew lineChangeSource
Hunk@@ -4,7 +4,13 @@
44 -- +migrate Up
5None−SET lock_timeout = '1s';
5+SET lock_timeout = '3s';
66
7None−-- Write the change here.
8None−SELECT 1;
7+CREATE INDEX CONCURRENTLY index_shipment_status_changed_at
8+ ON shipment (status_changed_at DESC)
9+ WHERE status IN ('picked', 'staged', 'in_transit');
10+
11+ALTER TABLE shipment
12+ ALTER COLUMN status_changed_at SET NOT NULL;
mo-adeyemi Unresolved Setting NOT NULL on an existing column makes Postgres verify all 41.2 million rows while it holds ACCESS EXCLUSIVE. Go to conversation
13+
14+DROP INDEX CONCURRENTLY index_shipment_on_status;
915
1016 -- +migrate Down

Design notes

Shape of the page

A shell: the release week in a rail, a bar that stays put, and one migration filling the well. The first screen is a verdict, and the conversation that earned it follows.

What it leads with

A figure, 4 m 12 s, set against a budget of 30 s. The review reads as sentences, with the chart, the diff and the checks under the lines that cite them.

Spacing

GitHub's compact setting: 14 px text and 32 px controls. On a touch screen the controls keep their size and the tap area grows around them.

The one bold decision

Colour never carries state alone. Open, merged and closed each get an icon, a word and a silhouette, and the lock chart says what it means in length and in words.

5.74:1light5.94:1dark

Small print on the muted surface: timestamps, captions and the line under each check.

4.52:1light4.63:1dark

The label on a state pill. It is the tightest pair GitHub ships, so the label takes 14 px and medium weight.

Try the review
Apply mo-adeyemi's suggestion and the file, the replay, the chart, the checks and the apply box all answer. Resolve the conversation and ask for a new review, and mo-adeyemi approves the split version. Applying then opens, asks when it runs and what it costs, and queues the migration for the window, with a way to take it back.
The three state marks
Open is a filled pill, merged a filled bar, closed an outline with nothing in it. In grey print, open and merged are the same tone in every theme GitHub ships, so the shape, the icon and the word do the telling.
The lock chart
Each bar is a statement holding its lock, drawn to one clock, so the long one is long before it is read. The rule on the second row is 30 s after that statement starts, and the red begins where the budget ends.
Labels
Each label takes its text and its fill from one themed pair, so contrast is GitHub's measured figure and not a guess from a colour. The lowest of the three is 4.93:1 in light and 6.27:1 in dark.
Dark
Dark is its own theme with its own measured pairs, not the light page inverted. The diff tints, the label pairs and the chart bars are each read again on that ground.
Keyboard
Press ? for the list. J and K walk the conversation, R re-runs the checks, A applies the suggestion, and / filters the release week.
Line numbers
The rows of a diff are too close together for a line number to be a safe tap target. A number only identifies its line. A link to the page still lands on it and highlights it, and the links that reach a line sit in the sentences discussing it.
The palette
Nothing in the palette. Two readability standards disagree about those two pairs at these sizes. The values are GitHub's own, and rewriting them here would break the other themes Primer ships.

Override the lock budget?

Line 12 holds ACCESS EXCLUSIVE on shipment for an estimated 4 m 12 s. For that time the dispatch planner, the carrier webhooks and every write to the table wait. Nothing cancels the scan once it begins.

Ledgerhawk records the override against priya-n in the change log. Review and conversation still block applying.

Apply 1 migration to production-eu?

Until the window opens you can remove it from the queue.

Keyboard shortcuts

On this page

?
Show this list
Esc
Close a dialog

In the conversation

J K
Next and previous item
R
Re-run every check
A
Apply the suggested change