Database Naming¶

PostgreSQL index and constraint names in Baander follow one scheme: a type prefix, then the table, then the columns. The prefixes are the lower-case forms of those Doctrine generates (IDX_, UNIQ_, FK_). A name therefore says what it covers without a catalog lookup.

Rules¶

• List columns in index order, separated by underscores: idx_notifications_user_id_created_at.
• A B-tree index takes no suffix. The suffixes for other indexes are defined in Extension Indexes.
• A partial index declared on an entity with options: ['where' => ...] is not migration-only and takes the column name: idx_scheduled_jobs_recovery_after_id.
• Renaming a unique constraint also renames the index that backs it, so both carry the uniq_ name.
• PostgreSQL truncates identifiers longer than 63 bytes. When a name would exceed that, shorten the column part consistently instead of letting PostgreSQL cut the name. Collapse each polymorphic <x>_type, <x>_id pair to <x>: the unique index on recommendations (source_type, source_id, target_type, target_id, name, user_id) is uniq_recommendations_source_target_name_user_id.

Extension Indexes¶

The initial migration enables citext, ltree, pg_stat_statements, pg_trgm, pgcrypto, pgroonga and uuid-ossp. Only some of them affect index or constraint names.

Core access methods without an extension take the method as the suffix: _gin, _gist, _brin, _hash, _spgist. No such index exists today.

The _trgm suffix is functional, not only descriptive. BaanderPostgreSQLSchemaManager hides every index whose name ends in _trgm from schema comparison, because ORM SchemaTool drops a non-unique trigram index when a unique constraint covers the same column. A trigram index with another name would therefore surface as drift. Other migration-only indexes that Doctrine cannot model are hidden by name: add a case to UnmanagedCustomIndex when you create one.

PGroonga¶

A PGroonga index name records the search it serves, because the access method alone does not: PGroonga provides different operator classes for full-text, prefix and regular-expression search, and an index's tokenizer options change what it matches.

The database image installs PGroonga package 4.0.5-1. The operator classes and options below are those of PGroonga 4; check a new option against that release before using it, and confirm the installed version with pg_extension.extversion on the target database.

• The unsuffixed _pgroonga name means exactly the standard full-text index. The five existing catalog indexes (idx_albums_title_pgroonga, idx_artists_name_pgroonga, idx_genres_name_pgroonga, idx_movies_title_pgroonga, idx_songs_title_pgroonga) follow it.
• Use the operator class for the actual column type. varchar, array and jsonb columns have their own operator classes; see the PGroonga reference.
• Declare the index on the entity with the same name, flags: ['pgroonga'] and the options under with, as AlbumEntity does. BaanderPostgreSQLPlatform renders the flag as USING pgroonga, options.operator_class as the operator class, and options.with as the WITH (...) clause.
• Schema comparison does not capture every PGroonga option. A clean doctrine:schema:validate does not prove the tokenizer or normalizer; inspect pg_get_indexdef() to verify them.

Search describes how repositories query these indexes.

Where Names Are Set¶

• Migrations name every index and constraint explicitly. Do not rely on PostgreSQL's generated names (<table>_<column>_key, <table>_<column>_fkey) or on Doctrine's hashed names (IDX_…, FK_…).
• Doctrine mappings name indexes and unique constraints with the same value as the migration (#[ORM\Index(name: 'idx_…')], #[ORM\UniqueConstraint(name: 'uniq_…')]). Schema comparison matches indexes by name. A mapping that omits the migration's name therefore makes doctrine:schema:validate and migration diffs report a rename.
• Cross-context foreign keys on scalar UUID columns are not visible to Doctrine. Their owning context declares each one in a ForeignKeyDeclarationProviderInterface service with the constraint's real name, so schema comparison keeps the database FK. See src/Notification/Infrastructure/Doctrine/NotificationForeignKeys.php.
• Association foreign keys cannot be named in a mapping. DBAL matches foreign keys by definition, so a migration-chosen fk_ name does not show up as drift.
• Implicit foreign-key indexes. For every mapped or declared foreign key, DBAL adds an index named IDX_<hash> unless an index on exactly the same columns already exists. When a migration creates that single-column index itself, declare it on the entity with its idx_ name, as PushSubscriptionEntity does; otherwise schema comparison reports a rename to IDX_<hash>. Do not create that index when another index already leads with the foreign-key column, such as a unique index on (user_id, media_type): the composite index serves the lookups, and because DBAL then adds the same IDX_<hash> to both the mapped and the introspected table, schema comparison stays clean.

tests/Integration/DatabaseNamingConventionTest.php reads the migrated catalog and fails on any foreign key, unique constraint or index whose name breaks these rules. It also checks that schema comparison is clean.

See Also¶

• Coding Conventions — column types and primary keys
• Search — querying PGroonga indexes