Index on related fields not being created in database (Postgres)

[JeremieLeblanc]

Link to reproduction

No response

Environment Info

Binaries:
  Node: 20.10.0
  npm: 10.2.3
  Yarn: N/A
  pnpm: 9.4.0
Relevant Packages:
  payload: 3.0.0-beta.108
  next: 15.0.0-canary.165
  @payloadcms/db-postgres: 3.0.0-beta.108
  @payloadcms/graphql: 3.0.0-beta.108
  @payloadcms/next/utilities: 3.0.0-beta.108
  @payloadcms/richtext-lexical: 3.0.0-beta.108
  @payloadcms/translations: 3.0.0-beta.108
  @payloadcms/ui/shared: 3.0.0-beta.108
  react: 19.0.0-rc-5d19e1c8-20240923
  react-dom: 19.0.0-rc-5d19e1c8-20240923
Operating System:
  Platform: linux
  Arch: x64
  Version: #1 SMP PREEMPT_DYNAMIC Thu Sep 12 18:26:09 UTC 2024
  Available memory (MB): 31787
  Available CPU cores: 12

Describe the Bug

When adding index: true to a field of type "relationship", the index does not get created in the database.

I have tested this on a fresh database with no previous migrations.

Example field:

{
    label: "Created By",
    name: "createdBy",
    type: "relationship",
    relationTo: "users",
    index: true,
    required: true,
    access: {
        update: () => false,
    },
    admin: {
        condition: (data) => Boolean(data?.createdBy),
        position: "sidebar",
        readOnly: true,
    },
    hooks: {
        beforeChange: [
            ({ operation, req: { user } }) => {
                if (operation === "create") {
                    if (user) {
                        return user.id;
                    }
                }
            },
        ],
    },
}

Reproduction Steps

Create a related field and add the index: true property on the field. Check the database to see that the index was not created.

Adapters and Plugins

db-postgres

[DanRibbens]

I believe this is expected behavior because in a relational database, the indexes for relationships are the foreign keys. Payload's schema generation adds these as constraints for you which is why you don't end up with extra indexes like you would see in the screenshot you shared.

Let me know if I've overlooked anything, but I believe we're doing everything right already here. Do tag me if I've overlooked something.

[JeremieLeblanc]

My understanding is that Postgres does not automatically add indexes to foreign keys. https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK

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. 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.

The constraint gets created, but I still need the index to be able to speed up my queries.

[DanRibbens]

My goodness, TIL!

I'm considering that this should be indexed by default since so much of Payload's UI and querying would be more performant with these indexes.

[wkentdag]

@DanRibbens this behavior also exists on v2, any chance we'll see a patch there as well?

IMO this falls under the umbrella of "critical fixes" that merit consideration for v2 since, effectively, index: true is broken for relationship fields.

[r1tsuu]

PR here https://github.com/payloadcms/payload/pull/8432!

I think we can port it @wkentdag, the only thing is that it requires changing the schema, though it doesn't force you to migrate.

[github-actions[bot]]

This issue has been automatically locked. Please open a new issue if this issue persists with any additional detail.

[github-actions[bot]]

🚀 This is included in version v3.0.0-beta.114

[github-actions[bot]]

🚀 This is included in version v2.30.2