Query with globals and complex blocks significantly slower than a collection with the same blocks using postgreSQL

[Robbe95]

Describe the Bug

When creating a lot of blocks and using them in a GlobalConfig with postgreSQL, the query gets really slow.
Using those same blocks in a collections stays fast.
Here is an example using the global, it takes more than 1 second on average. This number grows the more blocks are added, in my project where i noticed this it is up to 2.5 seconds.

Using the exact same setup, but within a collection, suddenly the API call only takes 50ms on average.

The setup for the global and collection are basically exactly the same, for more details look in the reproduction repo

I don't think I am doing anything wrong, but I am not that familiar with PayloadCMS as it is my first project in it.

Link to the code that reproduces this issue

https://github.com/Robbe95/payload-slow-global

Reproduction Steps

1. Setup local postgreSQL server
2. Setup .env with DATABASE_URI and PAYLOAD_SECRET
3. Run the server
4. Add a complex blocks structure, see https://github.com/Robbe95/payload-slow-global/blob/main/src/blocks/testBlocks.ts
5. Make a pages collection and add the blocks structure
6. Make a global collection and add the blocks structure
7. Run the API call http://localhost:3000/api/pages/1
8. Run the API call http://localhost:3000/api/globals/home-page
9. Notice the speed difference

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

db-postgres

Environment Info

Binaries:
  Node: 22.6.0
  npm: 10.8.2
  Yarn: N/A
  pnpm: 9.15.4
Relevant Packages:
  payload: 3.17.1
  next: 15.1.0
  @payloadcms/db-postgres: 3.17.1
  @payloadcms/email-nodemailer: 3.17.1
  @payloadcms/graphql: 3.17.1
  @payloadcms/next/utilities: 3.17.1
  @payloadcms/payload-cloud: 3.17.1
  @payloadcms/richtext-lexical: 3.17.1
  @payloadcms/translations: 3.17.1
  @payloadcms/ui/shared: 3.17.1
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: darwin
  Arch: arm64
  Version: Darwin Kernel Version 24.0.0: Mon Aug 12 20:52:12 PDT 2024; root:xnu-11215.1.10~2/RELEASE_ARM64_T6020
  Available memory (MB): 32768
  Available CPU cores: 10

[r1tsuu]

I benchmarked difference between globals and collections querying and didn't notice any, the same for me:
(this executes find 200 times, 100 for global 100 for collection)

You can check the commit for the benchmark here, I used your blocks configuration.
240940d

The same for REST API. Your 1232ms definitely don't seem right, perhaps something was missing?
Ideally you should also include a seed function to your reproduction with the data

[Robbe95]

Hello and thank you for the fast response!
The problem only seems to occur on postgreSQL setups.
I've looked at your benchmark and changed the community config to use postgres, and now you can see the result.
(I've even had to change the amount to 10 times for global and collection, since it was so slow)

The only change I made was in the config.ts in test/_community to this, aka changed to non-default config, added a db adapter for postgres and added a secret, since it isn't in the default config

export default buildConfig({
  // ...extend config here
  secret: 'secret',
  db: postgresAdapter({
    pool: {
      connectionString: YOUR_POSTGRES_STRING,
    },
  }),
  collections: [PostsCollection, MediaCollection, { slug: 'blocks-collection', fields: [blocks] }],
  admin: {
    importMap: {
      baseDir: path.resolve(dirname),
    },
  },
  editor: lexicalEditor({}),
  globals: [
    // ...add more globals here
    MenuGlobal,
    {
      slug: 'blocks-global',
      fields: [blocks],
    },
  ],
  onInit: async (payload) => {
    await payload.create({
      collection: 'users',
      data: {
        email: devUser.email,
        password: devUser.password,
      },
    })

    await payload.create({
      collection: postsSlug,
      data: {
        title: 'example post',
      },
    })
  },
  typescript: {
    outputFile: path.resolve(dirname, 'payload-types.ts'),
  },
})

[r1tsuu]

I did use Postgres. This can be done by setting env variable PAYLOAD_DATABASE=postgres

[Robbe95]

I might be wrong but I think the .env doesn't change it to postgres.
In the initPayloadInt, it runs await runInit(testSuiteName, false, true). But the second argument is always false, so it doesn't generate the DB adapter because of this check I assume if (writeDBAdapter === 'true'), which in this case will always be false.
Manually setting this to true, and setting the DB to postgres, together with an URI, gives me the same result of it being slow again.

[Robbe95]

Hi @r1tsuu, has this issue been able to be reproduced yet, or should I try creating a second reproduction?
I've been able to reproduce it every time when setting postgres manually in my payload config and just running the project and doing a local API query.
Thanks for the previous responses already!