PostgreSQL: foreign-key columns left unindexed (declare.py emits an FK index only for unique FKs)

[dimitri-yatsenko]

Summary

On the PostgreSQL backend, DataJoint does not create a secondary index on a child table's foreign-key columns except in the unique case. MySQL/InnoDB creates one implicitly as part of enforcing every foreign key; PostgreSQL does not, and there is no server setting to make it. This leaves an unindexed-FK performance gap on Postgres for a subset of foreign keys.

Background

• MySQL/InnoDB requires and auto-creates an index on the referencing (child) columns of every FK.
• PostgreSQL auto-indexes only the referenced (parent) side (the PK/unique constraint, already indexed). It never indexes the referencing (child) columns, and this is not configurable via postgresql.conf — it is a deliberate design choice, left to the DBA.

Current DataJoint behavior

compile_foreign_key() appends the constraint but emits an index only when the FK is unique:

# src/datajoint/declare.py (~288-307)
foreign_key_sql.append(
    f"FOREIGN KEY ({fk_cols}) REFERENCES {ref_table_name} ({pk_cols}) ON UPDATE CASCADE ON DELETE RESTRICT"
)
# declare unique index
if is_unique:
    index_cols = ", ".join(adapter.quote_identifier(attr) for attr in ref.primary_key)
    index_sql.append(f"UNIQUE INDEX ({index_cols})")

So on Postgres a non-unique FK gets no supporting index from DataJoint or from the engine.

When this actually bites (and when it doesn't)

Because a DataJoint FK inherits the parent's primary key into the child, the FK columns are often a left-prefix of the child's primary key — in which case the child's PK index already covers FK lookups on both backends, and no extra index is needed.

The gap is real only when the FK columns are not a usable left-prefix of an existing index, i.e.:

• secondary (non-primary) foreign keys — FK attributes not in the child PK; and
• FK columns that sit in a non-leading position of a composite PK (e.g. PK (a, b) where b comes from the FK), so lookups/cascades filtering on the FK alone can't use the PK index.

In those cases, on Postgres, cascade deletes (ON DELETE), joins on the FK, and the parent-existence check scan the child without index support — a performance cliff on large child tables, and an asymmetry vs. MySQL.

Proposal

On the Postgres DDL path, have declare.py emit an explicit CREATE INDEX on the FK columns when they are not already covered by a left-prefix of the PK or an existing index. MySQL can continue to rely on InnoDB's implicit index (avoid duplicating it). Points to work through:

• Skip when FK columns are a left-prefix of the child PK (or an existing declared index) to avoid redundant indexes.
• Idempotency with alter() (don't churn/duplicate indexes on redeclare).
• Index naming.
• The unique FK case already emits a UNIQUE INDEX; keep that.

References

• src/datajoint/declare.py — compile_foreign_key() (~L288-307); PostgreSQL index handling (~L494-510).
• Adapters: adapters/mysql.py (InnoDB implicit index), adapters/postgres.py.
• Docs note added in datajoint-docs Added set_password and show_definition #227 (explanation/referential-integrity.md §5) currently advises users to declare an explicit index(...) on Postgres; if this is fixed in the framework, that guidance flips back to "the database indexes them."

[dimitri-yatsenko]

Alternative solutions (incl. server-side)

Surveyed the option space. TL;DR: there is no PostgreSQL server/database setting for this — every path is either DB-side automation you install, a detect-then-create workflow, or the framework/user creating the index explicitly.

A. Server-side configuration — none exists

PostgreSQL has never auto-created an index on a foreign key's referencing columns, and there is no postgresql.conf GUC or per-database option to enable it (checked through current releases). The docs state it is deliberate: "Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns." Only the referenced (parent) side is auto-indexed, and only because it must carry a PK/unique constraint. So a pure server-config fix is off the table.

B. Server-side automation you install (not a setting, runs in the DB)

• DDL event trigger on ddl_command_end: a PL/pgSQL function that fires when an FK is added and creates a covering index if one is missing. Automatic thereafter, per database. Costs: it is an installed DB object requiring superuser to create; it fires for all FK DDL (can't tell DataJoint tables from others); and it still needs the "already covered by a PK/left-prefix index?" guard to avoid redundant indexes. DataJoint could optionally install/manage it, but that pushes logic into the server.
• Scheduled catalog sweep (pg_cron/external job) running the query in (C) and creating the missing indexes in batch — same effect, on a cadence instead of on-DDL.

C. Detect-then-create (diagnostic)

A catalog query flags FKs whose referencing columns are not a left-prefix of any existing index; create indexes manually or in a migration. Roughly (see Cybertec for the battle-tested version):

SELECT c.conrelid::regclass AS child_table, c.conname AS fk,
       pg_catalog.pg_get_constraintdef(c.oid) AS definition
FROM   pg_catalog.pg_constraint c
WHERE  c.contype = 'f'
  AND  NOT EXISTS (
         SELECT 1 FROM pg_catalog.pg_index i
         WHERE  i.indrelid = c.conrelid
           AND  (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
                OPERATOR(pg_catalog.@>) c.conkey)   -- FK cols are the leading index cols
ORDER  BY pg_catalog.pg_relation_size(c.conrelid) DESC;

Could ship as a DataJoint diagnostic (report, or --fix) — low-magic, opt-in, cross-backend-safe.

D. Framework-side in declare.py (the original proposal) — variants

1. Emit CREATE INDEX on the FK columns on the Postgres DDL path, skipping when they are a left-prefix of the child PK or an existing declared index. Gives InnoDB parity transparently; DataJoint already knows the PK, so it can avoid the redundant-index problem that a naive "index every FK" (InnoDB-style) would create.
2. Same, but opt-in via config (e.g. dj.config['database.index_foreign_keys'], or per-schema) — safest rollout; lets existing deployments opt in.
3. Emit only for secondary (non-PK) FKs — the subset that actually lacks coverage, since primary-dependency FKs are usually already a PK left-prefix.

Prior art: SQLAlchemy users solve the identical problem in the DDL/compilation layer (custom event listener that adds the index at create_table) — the direct analog of doing it in declare.py.

E. User-side (status quo)

Declare an explicit index(...) on the FK columns in the table definition. Zero framework change; this is what datajoint-docs #227 currently advises. Burden on the user and easy to forget.

Recommendation

D1 or D3 (framework-side, coverage-aware) best fits DataJoint: it keeps the "the database indexes FK columns" mental model true on both backends, is idempotent-friendly through alter(), and — because DataJoint knows the PK — indexes only the FKs that Postgres actually leaves uncovered, avoiding the over-indexing InnoDB does. A config-gated D2 is the conservative first step. Server-side automation (B) works but adds an installed, all-FK trigger to every Postgres database — more operational surface and no DataJoint-awareness. E stays the documented fallback until a framework fix lands.

---

Sources: PostgreSQL docs — 5.4.5 Foreign Keys · Cybertec — Index your foreign keys · SQLAlchemy — auto-index FK columns via custom DDL · Automating FK index creation with PL/pgSQL

[dimitri-yatsenko]

Index lifecycle: dropping foreign keys and dropping tables

Good question — index cleanup is the hard half of any framework-managed-index proposal (D1–D3), and it should be designed together with FK-alter support, not bolted on later.

Dropping a table — nothing special needed

DROP TABLE removes all of the table's indexes with it on both MySQL and PostgreSQL, so an FK-support index never outlives its table. DataJoint's cascade drop() removes dependent (child) tables first, so their FK indexes go too. No extra handling.

Dropping a foreign key — the real case

Neither backend cleans the index up on its own:

• PostgreSQL — an explicit index on the FK columns is an independent object; dropping the FK constraint leaves the index behind → orphaned index unless DataJoint drops it.
• MySQL/InnoDB — the auto-created FK index is likewise not removed when the constraint is dropped (and InnoDB requires it to exist while the FK does, so the order is drop-FK-then-drop-index). So even the "implicit" backend leaks an index after an FK is removed.

Current state — moot today. alter() refuses FK and index changes outright (declare.py:678-688: NotImplementedError("table.alter cannot alter foreign keys (yet)") and "...cannot alter indexes (yet)")), so removing an FK today means drop-and-recreate the table — which falls under the clean table-drop case above. The lifecycle question only goes live when alter() gains FK support, which is exactly when this index proposal would land too.

Recommended design — declarative reconciliation, not bookkeeping

Treat the FK-support index set as a pure function of the current definition. On alter(), diff old vs. new and reconcile:

1. Compute the FK-support indexes implied by the new definition (same coverage-aware rule as creation: skip when the FK columns are a left-prefix of the PK or of a user-declared index).
2. Create the missing ones; drop the ones DataJoint would no longer emit.
3. Drop an FK index only when it is (a) DataJoint-created FK support, (b) no longer implied by the definition, and (c) not still covering another remaining FK, a user index(...), or a PK left-prefix.

This requires distinguishing a DataJoint-created FK index from a user index(...):

• Definition-derived recomputation (preferred): persist no ownership metadata — recompute the implied set from the definition each time and reconcile. Survives out-of-band edits; no state to corrupt.
• Naming convention (fallback): name FK-support indexes with a reserved pattern (e.g. an fk__-style prefix) so alter can recognize and drop them. Simpler, but collision-prone and fragile if a user declares a similarly named index.

Consistency with the existing unique-FK index

DataJoint already emits a UNIQUE INDEX for unique FKs (declare.py:304-307). Whatever drop/reconcile rule we adopt must also govern that index, so the unique-FK and non-unique-FK paths behave identically under alter.

Bearing on the server-side automation option (B)

The DDL-event-trigger route would need a matching drop-side trigger (sql_drop / on-FK-drop) that locates and drops the correct index — and it can't easily tell which index it "owns." That is materially harder to get right than framework reconciliation, and is another argument for keeping this in declare.py/alter() rather than in the server.

[dimitri-yatsenko]

@kavenk — assigning you for a design review. The open question is how FK-support indexes should be managed on the PostgreSQL path: whether DataJoint should emit them in declare.py (and how to keep them coverage-aware vs. the PK left-prefix), plus the index lifecycle on FK drop / alter() — see the reconciliation approach in the comment above. Interested in your take on the framework-side vs. server-side (event-trigger) trade-off.

[dimitri-yatsenko]

Decision — implement in the framework, target v2.3.3

After discussion we're going with the framework-side approach (option D above): declare.py will emit the index on the PostgreSQL DDL path, coverage-aware — skip when the FK columns are already a left-prefix of the child's primary key (or of an existing declared index), so we don't create redundant indexes. This gives MySQL/InnoDB parity transparently and keeps the "the database indexes FK columns" mental model true on both backends. (Server-side event-trigger automation is not the route.)

Target release: v2.3.3.

Scope for this fix: index creation only — the CREATE INDEX emitted at table declaration on the Postgres path.

Deferred: the index lifecycle on schema changes (dropping/reconciling FK-support indexes when a foreign key is removed) is out of scope here — that belongs to a separate schema-changes / .alter() design session. This supersedes my earlier lifecycle comment; .alter() is not part of the v2.3.3 fix.

cc @kavenk