We added a "next-item" style query as part of #1298 to find the next available external IP address. This currently searches over all pools, and all ranges in those pools. Because of the implementation in CRDB, this is particularly bad for performance. Subqueries are fully-materialized and stored in memory before the surrounding query is run. So, if a user creates an IP Pool with an IPv6 /64 range, which is a pretty natural thing to do, the query will absolutely grind to a halt. The database doesn't appear to fall over now, but the query will basically never complete. Some of this is referred to in #1371.

Additionally, as part of #1458, we modified the query to now search all pools that are "available" to an instance, e.g., pools with a NULL project ID or with the project ID equal to the instance's project ID. That's fine, but we're also currently duplicating the project ID in the parent ip_pool table and the ip_pool_range table. That denormalizes the database a bit, but it's to avoid yet another join in the already-terrible query, which would be used to get the project ID from the parent table (joining on the ip_pool_range.ip_pool_id foreign-key into the ip_pool table). This was discussed here.

The query is here. It should just be...better...but it's not clear exactly how yet.