pg_indexes spans every schema, so TestPostgresStore_CreatesTopicPublishedIDIndex also matched the message tables of parallel tests, and failed with 'could not open relation with OID' when one of them dropped its schema at that moment (seen once in CI on this branch).
On ntfy.sh, v2.30.0's own replay (topic = $1 AND id > $2) was expensive: on (topic, published,
time, id), id > marker is checked inside the index but walks every entry of the topic, or the plan
scans the primary key from the marker across all topics. Replica-1 spent about one vCPU on it.
idx_message_topic_published_id (topic, published, id), created by hand on ntfy.sh on 2026-10-16,
made it ~50x cheaper in the lab. It is now created on every start (IF NOT EXISTS, no schema
version, so rollbacks to 2.30.0 still work).
With that index, the late-written half of the replay (time >= marker AND id < marker) picked it
and walked the topic's history (6 ms -> 104 ms in the lab). The replay is now split by time
instead: rows published in the marker's second or later (time index, from the marker), and rows
written after the marker but published before it (the new index, from the marker; time + 0 keeps
it off the time index). Same result as before, and both parts start at the marker.
The plan test now runs on prod-shaped data (200k rows, a few busy topics) with statistics, and
judges each scan by its index: a scan must start at the marker on that index, and replays must
stay within the topic. It fails on v2.31.0's query and on v2.30.0's without the index.
The replay query of v2.31.0 only read whole topics in the plan Postgres makes without the
parameter values, which it switches to after a prepared statement has run five times (pgx
prepares statements); with the values, it had found a cheaper plan. The plan tests now check that
generic plan. They found the same problem in the tail read (id > cursor OR id = ANY(holes)): its
generic plan scans the whole message table through the primary key, 700 ms on 3.3M rows in the
lab, on the primary every second. SQLite, which always plans without the values, scanned the whole
table for it too. Both are now two parts joined with UNION ALL.
cache_postgres_internal_test.go and cache_sqlite_internal_test.go, the internal companions of
cache_postgres_test.go and cache_sqlite_test.go: the query constants are unexported.
binwiederhier