MigrationRecorder.has_table() lists every table in the database, 3 times per migrate

[Amirshokh]

MigrationRecorder.has_table() checks whether django_migrations exists by calling
connection.introspection.table_names(), i.e. by listing every table the connection can see:

with self.connection.cursor() as cursor:
    tables = self.connection.introspection.table_names(cursor)
self._has_table = self.Migration._meta.db_table in tables

On PostgreSQL that is get_table_list(): a sequential scan of all of pg_class with
pg_table_is_visible() evaluated on every table. The cost grows with the size of the whole
catalog, not the project, and it is paid even when there is nothing to migrate.

A single migrate pays it 3 times. _has_table is cached per recorder instance, but each of these
creates its own MigrationRecorder:

• MigrationLoader.build_graph() (loader.py, applied_migrations())
• MigrationLoader.check_consistent_history() (loader.py, applied_migrations())
• MigrationExecutor (executor.py, ensure_schema() in migrate())

Why it matters. Large PostgreSQL catalogs are common: heavy table partitioning, or many
schemas (schema-per-tenant setups run migrate once per schema). On a database with 5.2M pg_class
rows (705k tables), measured:

• one get_table_list() call: 1.3–3.5 s, about 3M shared buffer hits
• pg_table_is_visible() loads every pg_class row into the backend's catalog cache: after one call the backend's "Catalog tuple context" was 352 MB
• it was the top statement on the database during a deployment's migrate run, above every DDL statement, and the per-backend memory growth across parallel migrate processes exhausted the server's memory

Suggestion. Check for the one table directly. For example, add an introspection method
that backends can override:

# django/db/backends/base/introspection.py
def table_exists(self, cursor, table_name):
    return table_name in self.table_names(cursor)

# django/db/backends/postgresql/introspection.py
def table_exists(self, cursor, table_name):
    cursor.execute(
        "SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c "
        "WHERE c.oid = to_regclass(%s) AND c.relkind IN ('f', 'm', 'p', 'r', 'v'))",
        [self.connection.ops.quote_name(table_name)],
    )
    return cursor.fetchone()[0]

and have MigrationRecorder.has_table() call introspection.table_exists(cursor, db_table).
to_regclass() resolves the name through search_path exactly like pg_table_is_visible() does
today, so results are unchanged. It is an index lookup: about 0.1 ms on the catalog above.

Separately, migrate could reuse one recorder (or share the result on the connection), so the
check runs once per command instead of 3 times.

Happy to work on a patch if this direction is acceptable.

[Simon Charette]

Thank you for your detailed report!

As you've described I think the corrective should be broken down in three commits

1. Introduce BaseDatabaseIntrospection.table_exists or a a new .table_names(only_tables:Iterable[str] | None = None) kwarg (or better name) and add tests for its. In either case the default implementation should do the filtering in memory by default to allow third party backends to catch up and be implemented on all supported backends in core. The additional kwarg approach that accepts multiple entries instead of a single one has the benefit of allowing other optimization when a set of tables is already known without either fetching all tables which happens a few times in the test suite.
2. A second commit that takes advantage of the new or adjusted method. On top of the call sites you identified ​there is one in createcachetable and likely a few ones in the migrations and schema test suites.
3. Lastly the different MigrationRecorder path should be adjusted.