Payload creates too long names/identifiers for postgress

[Xiphe]

Describe the Bug

Given I use payload with a postgress database

When combining features like nested arrays, localization and versioning
Then payload creates too long index and table names like _users_v_version_ingredient_sections_section_ingredients_locale_idx
Then Postgress like Neon with Vercel then truncates this to use only the first 63 chars
And that creates all sorts of problems (I assume)

On my end this surfaced when I removed some of the features that created the long index
Thereby creating a migration dropping the index and altering tables
Which postgress takes only 63 chars of thereby failing.

Link to the code that reproduces this issue

https://github.com/Xiphe/payload-long-index-names

Reproduction Steps

1. Check out the repo 👆
2. Run pnpm install
3. Connect with a postgress db
4. Run pnpm payload migrate
5. See this error

pnpm payload migrate

> [email protected] payload [...]/postgress-id-limit
> cross-env NODE_OPTIONS=--no-deprecation payload migrate

[20:43:29] WARN: No email adapter provided. Email will be written to console. More info at https://payloadcms.com/docs/email/overview.
[20:43:29] INFO: Reading migration files from [...]/postgress-id-limit/src/migrations
[20:43:29] INFO: Migrating: 20250412_183931
[20:43:29] ERROR: Error running migration 20250412_183931 relation "_users_v_version_ingredient_sections_section_ingredients_locale" does not exist.
    err: {
      "type": "DatabaseError",
      "message": "relation \"_users_v_version_ingredient_sections_section_ingredients_locale\" does not exist",
      "stack":
          error: relation "_users_v_version_ingredient_sections_section_ingredients_locale" does not exist
              at [...]/postgress-id-limit/node_modules/.pnpm/[email protected]/node_modules/pg/lib/client.js:526:17
              at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
              at Object.up ([...]/postgress-id-limit/src/migrations/20250412_183931.ts:4:3)
              at runMigrationFile ([...]/postgress-id-limit/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected][email protected]_graphql@_0a75b007319f798981d7c38cf55f021d/node_modules/@payloadcms/drizzle/src/migrate.ts:96:5)
              at Object.migrate ([...]/postgress-id-limit/node_modules/.pnpm/@[email protected]_@[email protected]_@[email protected][email protected]_graphql@_0a75b007319f798981d7c38cf55f021d/node_modules/@payloadcms/drizzle/src/migrate.ts:83:5)
              at migrate ([...]/postgress-id-limit/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/payload/src/bin/migrate.ts:87:7)
              at async start (file://[...]/postgress-id-limit/node_modules/.pnpm/[email protected][email protected][email protected]/node_modules/payload/bin.js:30:7)
      "length": 562,
      "name": "error",
      "severity": "ERROR",
      "code": "42P01",
      "where": "SQL statement \"ALTER TABLE \"_users_v_version_ingredient_sections_section_ingredients_locales\" ADD CONSTRAINT \"_users_v_version_ingredient_sections_section_ingredients_locales_parent_id_fk\" FOREIGN KEY (\"_parent_id\") REFERENCES \"public\".\"_users_v_version_ingredient_sections_section_ingredients\"(\"id\") ON DELETE cascade ON UPDATE no action\"\nPL/pgSQL function inline_code_block line 2 at SQL statement",
      "file": "namespace.c",
      "line": "636",
      "routine": "RangeVarGetRelidExtended"
    }
 ELIFECYCLE  Command failed with exit code 1.

Which area(s) are affected? (Select all that apply)

db-postgres

Environment Info

Binaries:
  Node: 20.11.0
  npm: 10.2.4
  Yarn: N/A
  pnpm: 10.4.1
Relevant Packages:
  payload: 3.33.0
  next: 15.3.0
  @payloadcms/db-postgres: 3.33.0
  @payloadcms/email-nodemailer: 3.33.0
  @payloadcms/graphql: 3.33.0
  @payloadcms/next/utilities: 3.33.0
  @payloadcms/payload-cloud: 3.33.0
  @payloadcms/richtext-lexical: 3.33.0
  @payloadcms/translations: 3.33.0
  @payloadcms/ui/shared: 3.33.0
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 24.1.0: Thu Oct 10 21:03:15 PDT 2024; root:xnu-11215.41.3~2/RELEASE_ARM64_T6000
  Available memory (MB): 32768
  Available CPU cores: 10

[Xiphe]

I'm not sure if CONSTRAINTS also fall under the 63 char limit... Some of them also get way longer then 63 chars in my migrations. All with (what I believe) reasonable names

Here is a relation from collection rcps_recipes to rcps_ingredients used under i.s.ingredient
I've already "minified" i and s to not run into the previous issue.

  DO $$ BEGIN
   ALTER TABLE "_rcps_recipes_v_version_i_s" ADD CONSTRAINT "_rcps_recipes_v_version_i_s_ingredient_id_rcps_ingredients_id_fk" FOREIGN KEY ("ingredient_id") REFERENCES "public"."rcps_ingredients"("id") ON DELETE set null ON UPDATE no action;
  EXCEPTION

[RafalFilipek]

#5973 (comment)

[Xiphe]

Thanks @RafalFilipek! This is really helpful.

I'd still suggest to warn/error pro-actively. This is does not fail gracefully - in my case I only found out when trying to migrate things on the tables that are already in prod.

[Xiphe]

Simply using custom dbName strings for my array collections caused this error when trying to load the collection UI in admin interface.

[error: operator does not exist: uuid = integer] {
  length: 201,
  severity: 'ERROR',
  code: '42883',
  detail: undefined,
  hint: 'No operator matches the given name and argument types. You might need to add explicit type casts.',
  position: '6735',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_oper.c',
  line: '635',
  routine: 'op_error'
}

I then went ahead and used dbName: ({ tableName }) => tableName + '_some_postfix' - same error for when I have localized fields in the array field.

I finally landed at dbName: ({ tableName }) => some_prefix_${shortHashOf(tableName)}`.

Edit: Using a hash here felt off, so I created a more explicit mapping, effectively doing this:

{
  /* ... */
  dbName: ({ tableName }) => {
    const relationTables = {
      parent: 'xyz',
      _parent_v: 'abcv'
    }
  
    if (!relationTables[tableName]) {
      throw new Error('unexpected relation');
    }
  
    return relationTables[tableName]
  }
}

---

I don't have capacity to dig any further right now, It feels like the combination with localization and versioning still produces naming collisions somehow.

Happy to assist with debugging this