Automatic/Implicit Index Creation for Foreign Keys (PostgreSQL and SQLite)

[mathiasgrimm]

Background

In MySQL, foreign key columns automatically receive corresponding indexes. However, PostgreSQL requires explicit index creation for foreign key columns. While this behavior is inherent to PostgreSQL and not related to Laravel, we can enhance Laravel's handling of this database-specific difference.

Despite our Schema DDL abstraction layer, migrations can produce different database structures when run on MySQL versus PostgreSQL/SQLite due to how each database handles index creation for foreign keys.

Proposed Solution

Add a configuration option in Laravel to automatically create indexes for PostgreSQL foreign key columns, making the behavior consistent with other supported databases.

Configuration

// config/database.php
'connections' => [
    'sqlite' => [
        // ... existing configuration ...
        'foreign_key_implicit_index_creation' => true,
    ],

    'pgsql' => [
        // ... existing configuration ...
        'foreign_key_implicit_index_creation' => true,
    ],
]

Current Usage

// Currently requires explicit index creation
$table->foreignId('tenant_id')->index()->constrained();

Desired Usage

// With the new configuration enabled
$table->foreignId('tenant_id')->constrained();

Implementation Considerations

A mechanism should be provided to override the automatic index creation when needed

References

MySQL

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

"MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created automatically."

SQLite

https://www.sqlite.org/foreignkeys.html

"an index should be created on the child key columns of each foreign key constraint"

PostgreSQL

https://www.postgresql.org/docs/current/indexes-unique.html

"PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint"

https://www.postgresql.org/docs/current/ddl-constraints.html

"Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, the declaration of a foreign key constraint does not automatically create an index on the referencing columns."

[Caio-Tera]

I can confirm I am experiencing this exact same issue on Laravel 12.x with PostgreSQL 17.x.

Following a similar schema structure (permissions table with foreign keys to roles and features, and a composite unique(['role_id', 'name'])), the index for the second foreign key (feature_id) is also not being created.

A direct query against pg_indexes confirms the index is missing. Adding $table->index('feature_id'); explicitly solves the problem.

This definitely seems like an unexpected behavior with the schema builder's optimization logic.

[Caio-Tera]

Further investigation led me to PR #49392, which seems to address this exact bug and was merged in Laravel 10.41.0. However, I am still experiencing this issue on a clean install of Laravel 12.x with PostgreSQL, which suggests there might be a regression or a different edge case that the original fix didn't cover. This seems to confirm the schema builder's index optimization logic is still not behaving as expected in all scenarios.

[Caio-Tera]

#56266