PostgreSQL GSI queue: another table's online GSI build stalled index propagation for 38 s #476

Summary

On the PostgreSQL backend, while one table was in an online GSI build, another table's GSI queue rows did not apply for 38 s. The cause is not established; the measurement and the code paths that could produce it are below.

What I measured

One server, index_propagation_delay_ms = 2000, two tables, each with one queue-maintained GSI.

Table A: 50,000 items, UpdateTable adding a GSI; the online build ran about 55 s.

Table B: 21 PutItem calls issued while table A's build was running.

Table B's 21 queue rows did not apply in any of the four partitions for 38 s. gsi_pending was sampled every 2.3 s and the per-partition counts stayed at 11, 4, 3, 3; no worker was waiting on a row lock; no lock timeout in the PostgreSQL log; no batch error in the server log. When table A's build finished, all 21 applied and table B's index equalled its table. The same 21 puts with nothing else running applied in 1.7 to 2.2 s.

Code paths that could produce this (not yet confirmed as the cause)

1. Queue partitions are shared across tables. In crates/storage-postgres/src/gsi_queue.rs, partition_for hashes the base-table key alone (hash(base_key) % NUM_WORKERS), and enqueue_gsi_pending floors ready_at at MAX(ready_at) over the whole partition (WHERE worker_partition = $2, no table_id). Each worker drains its partition in id order, 100 rows per wakeup. Any rows table A put on the queue during the window would sit ahead of table B's rows and set their earliest eligible time. Whether table A enqueued rows in this run was not recorded; the build copies items into the index directly and enqueues only repair rows for writes that predate the index.

2. Connection pool occupancy. The build's copy and the queue workers share the server's PostgreSQL pool. If the build held every connection, the workers' claim queries would wait on the pool rather than on a row lock, which matches the sample (no lock wait, no timeout, nothing applied).

To settle it

Repeat the run with pg_stat_activity sampled alongside gsi_pending (count of connections by application_name and wait_event), and with table A's rows in gsi_pending counted separately from table B's. Whichever of the two shows up in the samples is the one to fix.

Per-key FIFO order needs all rows for one key in one partition; it does not need rows from different tables to share a partition or a ready_at floor, so option 1 has a fix with no ordering cost: include table_id in the partition hash and scope the floor to (table_id, worker_partition), with the drain-first restart the module comment already describes for a hash change.

Measured on the #401 head (f721d742) during its verification. The partition hash and the ready_at floor described above are on main unchanged by that PR; whether main shows the same stall was not measured.