PostgreSQL migration performance due to hasTable behavior #53002
Description
Laravel Version
11.21.0
PHP Version
8.3.11
Database Driver & Version
Postgres 17.0
Description
In Laravel 11.21.0 and since Laravel 10.34, migrations' behaviour has changed. It causes issues on Postgres.
It call Builder::hasTable behind the scene and here is the issue :
public function hasTable($table)
{
$table = $this->connection->getTablePrefix().$table;
/** @phpstan-ignore arguments.count (SQLite accepts a withSize argument) */
foreach ($this->getTables(false) as $value) {
if (strtolower($table) === strtolower($value['name'])) {
return true;
}
}
return false;
}We see here that this function calls getTables() to check if a table exists. There is perhaps more direct way to do that, but the issue is not directly here.
As you can see, for SQLite, there is a false argument used to prevent calculating size of tables. We should have the same kind of behaviour for Postgres, because now the query made look like that :
select c.relname as name, n.nspname as schema, pg_total_relation_size(c.oid) as size,
obj_description(c.oid, 'pg_class') as comment from pg_class c, pg_namespace n
where c.relkind in ('r', 'p') and n.oid = c.relnamespace and n.nspname not in ('pg_catalog', 'information_schema')
order by c.relnameAnd before it was something like this :
select * from information_schema.tables where table_catalog = $1 and table_schema = $2 and table_name = $3 and table_type = 'BASE TABLE'So a request dedicated to find if a table exists, not loading all tables size infos.
For the same kind of migration done, it causes a RAM usage 5 times bigger and a CPU 4 times bigger in our case. We have a lot of tables in this database (~10k).
Can not quickly provide a benchmark, but it is easy to understand the issue here. Checking for a table existence should not list and calculate size of all the tables in the db.
Steps To Reproduce
Start a new empty Laravel project settuping PostgreSQL as your database.
Launching migration will call getTable() and load all tables with their size. A simpler request should be done.