Skip to content

fix(db): carry entity_id in statements_prop_value_idx so ref lookups are index-only

Thomas Kavanagh requested to merge fix/statements-covering-ref-idx into main

Why

/v1/committees/totals returned HTTP 500 for understory's two large campaign sets after pdxcivic-postgres moved to the Hetzner data volume on 2026-10-13:

Campaign Committees Before stopgap
multnomah-d2 38 500 at 61 s
washington-chair 62 500 at 61 s
multnomah-chair 3 200 in 8 s

EXPLAIN ANALYZE of the exact d2 SQL on prod took 73 s against the pdxc_orestar_api role's 60 s statement_timeout; it measured 32.6 s on 2026-09-01. About 35 s of that is the refs step: 185,471 matches and 223,775 heap pages read, because statements_prop_value_idx stops at (dataset, prop, value) and every match needs a table read for its entity_id. Random reads are the network volume's weak point, the 4 GB host can't cache a 6.7 GB table, and the non-shared parallel hash join repeats the scan in each of three workers.

What

One migration, 20261014000001_ftm_statements_prop_value_covering.sql:

  1. CREATE INDEX CONCURRENTLY the same keys and partial filter with INCLUDE (entity_id), under a temporary name.
  2. DROP INDEX CONCURRENTLY the old index. It isn't kept alongside because the ingest's refresh UPDATE is non-HOT, so every extra index costs a write on every re-observed statement.
  3. Rename the new index to statements_prop_value_idx, so the plan comments in orestar-api stay true.

atlas:txmode none + CONCURRENTLY, as in 20260805000002, so the ingest is never blocked.

Already done on prod (outside this MR)

  • Plain VACUUM (ANALYZE) on ftm.statements: yesterday's VACUUM FULL had left the visibility map at 0 of 853,575 pages.
  • ALTER ROLE pdxc_orestar_api SET statement_timeout = '180s': a stopgap. With it, d2 returns 200 in 74 s and washington-chair in 107 s.

Tested

  • Migration applied with Atlas to a local pdxcivic_test already at 20260805000003: ok, the index is valid, and the definition has INCLUDE (entity_id).
  • task orestar-api:test: 126 passed.
  • pytest packages/ingest against the migrated test DB: 195 passed, 1 skipped (template placeholder).
  • Not measured: the speedup on prod. That needs the index built there. After task db:migrate:prod, re-run the understory totals probe and, if d2 and washington-chair land well under 60 s, set the role timeout back to 60 s.

Applying

task db:migrate:prod:status
op run --env-file=cli/prod-migrate.env.op -- task db:migrate:prod

If the concurrent build is interrupted it leaves an INVALID statements_prop_value_cov_idx; drop it by hand before re-running (noted in the file).

🤖 Generated with Claude Code

Merge request reports