PostgreSQL: concurrent UpdateTable calls that add a GSI exhaust a shared pool waiting on a second connection #715

Problem

On PostgreSQL, update_table_impl (crates/storage-postgres/src/update_table.rs) opens its catalog transaction on self.pool at lines 104 to 108 and keeps it open while it takes more connections from self.data_pool:

• line 825: self.data_pool.begin() for each new GSI's data table;
• lines 359 and 366: the stream_shards read and init_stream_shards when a stream is enabled;
• lines 699 and 810: the vector index hold and the build ownership.

When no separate data database is configured, data_pool is a clone of the catalog pool (crates/storage-postgres/src/lib.rs, lines 383 to 386). Each such UpdateTable then needs at least two connections from one pool at the same time. Once as many of these calls run at once as the pool has connections, each holds one connection and waits for another that none can get, until sqlx's acquire timeout fails it. Every other request that needs a connection from the pool waits as well.

#615 describes the same pattern for DeleteTable and the poller pass. DeleteTable on one shared database now runs its data work on the catalog transaction's own connection, and concurrent_delete_tables_do_not_exhaust_the_pool in tests/table_snapshot.rs covers it.

Measured

PostgreSQL, one database for catalog and data, 30 concurrent UpdateTable calls that each add a GSI to a table, called on the storage engine from a Rust test:

This ran on the branch of #708, which changes the GSI build in this function. The catalog transaction and the data_pool.begin() for the GSI data table are the same on main at f49c5f823, where it was not measured.

Expected

UpdateTable holds one connection at a time from a shared pool: when data_pool is the catalog pool, the data work runs on the catalog transaction's connection, as DeleteTable's does, or the catalog transaction commits before the data work starts. A test like concurrent_delete_tables_do_not_exhaust_the_pool runs more concurrent GSI adds than pool_size on both layouts and requires every call to return within a few seconds.