sql: slow metadata queries after upgrading FKs from 19.1 to 19.2

[thoszhang]

In 19.2, we started upgrading table descriptors to the new foreign key representation. Whenever a table descriptor is fetched from KV, if the foreign key representation hasn't already been upgraded, we also fetch all the tables that have foreign key relationships with that table.

We've seen a case where a geodistributed cluster, after being upgraded to 19.2, now takes ~30 seconds to return results from queries on information_schema.tables because of how long it takes to fetch all the FK dependencies for every table. This can cause problems for ORMs and migration tools which rely on these introspection queries. Furthermore, this state persists until the state of the table descriptor with the new foreign key representation has been written back to disk, which only happens when there's a schema change.

A workaround for this is to do some relatively trivial schema change (like GRANT for a dummy user) on every single table in the cluster, but this isn't a very satisfactory solution because it requires manual intervention.

Jira issue: CRDB-4317

[thoszhang]

cc @jordanlewis

It seems like filter pushdown for virtual tables would mitigate this; right now, every single table with un-upgraded FKs contributes to the problem. Also, hopefully by 21.1 we'll have a migration to take care of the old tables once and for all.

I don't have any good ideas for shorter-term solutions at the moment. I think any solution that we would potentially backport to 19.2/20.1 would have to be quite extensive (e.g., better caching, some background task that upgrades descriptors), so we'd only do it if the impact were determined to be high.

[rohany]

I think it's possible for us to write a sqlmigration that upgrades out of date foreign keys (if any exist) now right?

[thoszhang]

In theory, yeah, but I think we're trying to avoid blocking starting a new node on a migration that takes O(tables) time to run.

[jordanlewis]

Vtable pushdown will be available in 20.2. We could consider backporting some of it if necessary.

[thoszhang]

We're not going to do anything about this beyond having the planned long-running migration to get rid of old-style descriptors, tracked in ##48743.