Repository navigation
PostgreSQL: a TTL disable's index drop waits with no lock timeout and queues the table's reads and writes behind it #606
Description
Summary
On PostgreSQL, UpdateTimeToLive with Enabled: false runs DROP INDEX IF EXISTS on the data table with no lock_timeout. The drop needs ACCESS EXCLUSIVE; while any transaction holds the table (a long read, an open cursor), the drop waits, and every later lock request on the table queues behind it, so the table's data plane stalls until the holder finishes.
Measured (PostgreSQL, 2026-10-26)
TTL ENABLED with its index built, a transaction holding ACCESS SHARE on the data table, then a disable and a GetItem:
| Disable | GetItem sent right after |
|
|---|---|---|
| holder present | answered 200 after 14.0 s, when the holder released | timed out at the client's 8 s, waiting on ACCESS SHARE behind the DROP INDEX (pg_locks) |
| no holder | 5 to 7 ms | 4 to 9 ms |
Measured on main and on #599's base alike (four rounds each). The service's UpdateTimeToLive does not block reads.
Notes
drop_ttl_index in crates/storage-postgres/src/metadata_engine.rs. The poller's table drops set lock_timeout = '3s' and retry; the TTL index drop sets none. CREATE INDEX CONCURRENTLY on enable holds SHARE UPDATE EXCLUSIVE for the build and conflicts with the poller's table drop, which times out and retries, so an enable followed by a delete on a large table delays the delete by the length of the build.
Activity