id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
37379	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:

{{{#!python
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:

{{{#!python
# 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.
"	Cleanup/optimization	new	Migrations	5.2	Normal		migrations performance postgresql introspection	Anthony Ricaud	Accepted	0	0	0	0	0	0