How a Timezone-Safe date_trunc() Filter Silently Disabled Partition Pruning and Pushed Checkout to 4.1s p99

09:04 UTC. PagerDuty: checkout-api p99 > 4000ms. Four minutes earlier, checkout had been running its usual 180-220ms. Nothing deployed to checkout-api that morning. The only thing that had kicked off at 09:00 sharp was the daily merchant recap job, the same job that had been running quietly every morning for the past week.

the scramble

First theory: something shipped overnight. A quick check of the deploy log turns up exactly one change in the last twelve hours, and it's not to checkout-api at all. It's a one-line config bump to the recap job's worker pool, from 6 concurrent workers to 40, merged the previous evening so the recap emails for all 4,100 active merchants would finish before the 09:30 UTC send cutoff instead of trickling out past 10:00.

That's an easy, satisfying story: more workers, more load, checkout got crowded out. Someone starts drafting a revert. But a revert only explains "more of the same thing," and it doesn't explain why six workers running the exact same query every morning for the past week never once touched checkout's latency. Whatever this query is doing, the concurrency bump didn't create the problem, it just finally had enough parallel copies of it running at once to notice.

  pid  | state  | query_start | wait_event      | left(query, 60)
-------+--------+-------------+-----------------+---------------------------------------------
 48221 | active | 09:04:51    | DataFileRead     | SELECT merchant_id, count(*), sum(total_cents)...
 48233 | active | 09:04:49    | DataFileRead     | SELECT merchant_id, count(*), sum(total_cents)...
 48240 | active | 09:04:53    | DataFileRead     | SELECT merchant_id, count(*), sum(total_cents)...
 (37 more rows, all the same query, all 30-48 seconds into execution)

Forty connections, all running the identical recap query with a different merchant_id parameter, all still active after 30+ seconds. A query that computes "how many orders did one merchant get today" should not take 30 seconds against any reasonably indexed table. Something about this specific query was the real problem, not how many copies of it happened to be running at once.

the hunt

orders is range-partitioned by created_at, one partition per month, 36 partitions covering three years, 284 million rows total. A query scoped to "today" should touch exactly one partition, maybe two at a month boundary, and a few thousand rows. Running EXPLAIN (ANALYZE, BUFFERS) on the recap query by hand settled it immediately:

Append  (actual time=0.038..31842.902 rows=4 loops=1)
  Subplans Removed: 0
  ->  Seq Scan on orders_2023_11 (actual time=14.221..882.103 rows=0 loops=1)
        Filter: ((date_trunc('day', created_at AT TIME ZONE m.tz)) = CURRENT_DATE)
        Rows Removed by Filter: 7,841,220
  ->  Seq Scan on orders_2023_12 (actual time=0.019..901.447 rows=0 loops=1)
        Rows Removed by Filter: 7,902,114
  ... (34 more child plans, same shape)
  ->  Seq Scan on orders_2026_11 (actual time=0.015..94.822 rows=4 loops=1)
        Rows Removed by Filter: 612,004
Planning Time: 1.112 ms
Execution Time: 31844.510 ms

Subplans Removed: 0. Every one of the 36 monthly partitions got scanned in full, start to finish, to find four rows that lived in the current month's partition alone. The query filtered on date_trunc('day', created_at AT TIME ZONE m.tz), not on created_at directly, and Postgres's partition pruning only works when the partition key column appears bare in the predicate. Wrap it in any expression, even a correct and necessary one, and the planner can no longer prove which partitions could possibly match, so it has to check all of them.

Git blame on that line pointed at a six-day-old PR, not the previous night's config change. Merchants in UTC+8 through UTC+13 had been seeing their recap reset at the wrong local hour, since the original query compared created_at = CURRENT_DATE in server time (UTC) instead of the merchant's own timezone. The fix wrapped created_at in a timezone conversion before truncating to a day, which made every number in the email correct. It also made every partition boundary invisible to the planner, for every recap query, from the moment it shipped.

the find

The recap query had been doing a full 36-partition scan since the localization fix shipped six days earlier, not just this morning. At six concurrent workers, 36 sequential scans per merchant were slow (30-40 seconds each) but spread thin enough across the morning that the shared buffer cache absorbed most of it and nobody's dashboard crossed an alert threshold. Bumping the worker pool to 40 the night before this incident didn't introduce a new bug. It just ran 40 copies of an existing 36-partition scan at once, long enough to pin the primary's CPU and starve checkout's queries of the buffer cache and CPU time they needed to stay under 200ms.

the fix

The actual fix had nothing to do with the worker pool. It was computing the merchant's local day boundaries once, in application code, and passing them as plain timestamptz bounds, so the predicate compares the raw partition key directly instead of a derived expression:

// before: correct result, unprunable plan
const { rows } = await db.query(`
  SELECT merchant_id, count(*), sum(total_cents)
  FROM orders
  WHERE merchant_id = $1
    AND date_trunc('day', created_at AT TIME ZONE $2) = CURRENT_DATE
`, [merchantId, merchantTz]);

// after: same result, partition key compared directly
const dayStart = zonedTimeToUtc(startOfDay(new Date(), merchantTz), merchantTz);
const dayEnd = addDays(dayStart, 1);

const { rows } = await db.query(`
  SELECT merchant_id, count(*), sum(total_cents)
  FROM orders
  WHERE merchant_id = $1
    AND created_at >= $2
    AND created_at <  $3
`, [merchantId, dayStart, dayEnd]);

Same timezone correctness, same four rows back for a merchant whose day hadn't rolled over in UTC yet. The EXPLAIN after the change showed Subplans Removed: 35, pruned to the single partition that could possibly hold today's rows. We also added auto_explain with log_min_duration = 5000 on the reporting role specifically, so a query that silently regresses from milliseconds to tens of seconds gets logged the first morning it happens instead of the sixth.

the aftermath

Checkout's p99 stayed above 4 seconds for 23 minutes before the on-call team killed the recap job's active connections to buy time, which brought checkout back to normal immediately and confirmed the recap job as the cause before the real fix even shipped. Roughly 860 checkout attempts failed with a gateway timeout in that window; most customers who retried succeeded on the second attempt once the recap job was paused. The full digest run for all 4,100 merchants dropped from 46 minutes to under 3.

The recap feature had been live for six days before anyone looked at its query plan, and the thing that finally forced that look wasn't the bug itself. It was a worker pool config that had nothing to do with partitioning at all.