Payload with getPayloadHMR don't close or spams db connections during Static Site Generation

[Dovchik]

Link to reproduction

No response

Payload Version

3.0.0-beta.68

Node Version

v20.11.1

Next.js Version

next@15.0.0-rc.0

Describe the Bug

I'm using postgres 12, and static generation with getStatisProps combined with local api of getPayloadHMR
Having 32 statically generated pages I encounter a problem that the last few pages are not generated right away, worker for generation terminates and restarts, finishing page generation. On second screenshot you can see a peak of postgres connections - max 100 for my system - in idle state, after peak drops, that's when workers are terminated, restarted and finished. This works after some optimizations I've made, because before that, SSG were failing with too many clients from postgres.
Is this an intended behavior and I should scale connections as count of my pages growth or that's going something wrong with how connections are managed in local api?

Reproduction Steps

When I will have time maybe I will try to create an example repo, but rough reproduction:

1. Get a postgress db - set max_connection to 100 or less.
2. Create a bunch of pages
3. Try generate with next build

Adapters and Plugins

db-postgres

[jmikrut]

Hey @Dovchik — let us check this out on our end and get back to you

[Dovchik]

Great, thanks @jmikrut
This is seems to be related: #7254 (comment)
I'd as well encountered connection termination on Vercel, but seen this only once as I'm mostly rely on SSG which seems to start wrecking when you scaling the amount of pages.

[jmikrut]

OK so here's some more info.

I just did a test with over a hundred pages to statically generate:

import type { Page } from 'payload-types'
import { getPayloadHMR } from '@payloadcms/next/utilities'
import config from '@payload-config'

const Page: React.FC<{ params: { id: string } }> = async ({ params }) => {
  const payload = await getPayloadHMR({ config })

  const page = await payload.findByID({
    collection: 'pages',
    id: params.id,
  })

  console.log(`page ${page.title} rendering`)

  return page.title
}

export default Page

export const generateStaticParams = async () => {
  const payload = await getPayloadHMR({ config })

  const query = await payload.find({
    collection: 'pages',
    limit: 200,
  })

  return query.docs.map((doc) => ({
    id: doc.id,
  }))
}

I've also added a quick console.log('INITIALIZED') to my Payload config's onInit which will log each time that Payload is freshly initialized (thus creating a new database connection).

On build time, I see all of the pages generated (and successfully console logging the page title), but I only get 8 INITIALIZED logs.

Why is there more than 1 INITIALIZED message? Because Next.js tries to run different build steps in different Node.js threads, so that work can be done in parallel. That means that things like getStaticParams will be run in a separate thread, needing to initialize Payload again.

But the INITIALIZED messages don't scale linearly (or at all) related to how many pages I build. I could do more, and the INITIALIZED messages do not go up. That means to me that our internal Payload init memoization layer is working as intended, so something else must be happening with your config / setup.

Can you create a minimally reproducible example so that I can test further?

On a separate note, we're going to try and look into properly disconnecting the database once the process is terminated. It might just be that the connections are not being properly "cleaned up" and staying open, which might add up over time (especially in production). They should idle and timeout, but we can probably do something about that. Hang tight for that. In the meantime, I'd love to see if you are actually seeing a Payload initialization per static page you're generating. That would be helpful!

[Dovchik]

@jmikrut I think I had found a culprit
I've created a repo where I got the same behavior I described before, here is the link to the repo: https://github.com/Dovchik/reproduce-connection-overflow/tree/main
All was building smoothly, but then I added generateMetadata, which also fetches payloadHMR, and the error happened: https://github.com/Dovchik/reproduce-connection-overflow/blob/3c61431638384aa3dff5a75c2af243c2a607f0c7/src/app/%5Blocale%5D/pages/%5Bslug%5D/page.tsx#L11

[jmikrut]

@Dovchik nice, ok, we're on it

[denolfe]

Hey @Dovchik , thanks for the repo and helping get to the bottom of this.

I pulled it down and ran it. On build, it peaked at ~45 connections - still not great but not 100 like you are seeing. Started at 11, ended at 14.

What version of PG are you running?

We're looking into some connection optimization now.

[Dovchik]

Hey @denolfe , it's postgres v12.18
Now, the build is going good, peaking in 45 for a different project where I initially encountered the problem, and just 5 connections for repo I had posted 😕

For background, I'm running hyper-v VM linux with 30 virtual thread as my machine has 32 available.
I just did an experiment where I reduced virtual threads count available for VM from 30 to 8 and build came through okay
Now, setting it back to 30 and build is also okay ❓ 😕

Also, @jmikrut for repo I posted I get 27 INITIALIZED log in async onInit(payload) for 30 threads, and just 7 when thread count is 8 - which is obvious, meaning node utilized thread to generated in parallel - more thread more parallel processes - more connections.

30 threads:

Only 8 threads

Anyway, my workaround for local build is reducing thread count, but that's far from ideal, as I need to restart the VM each time when I need multi-thread performance for my other stuff.

Also, If I encounter the problem again I will try to reproduce again, I'll let you know if I will got any success.

[Dovchik]

Anyway, the bug persist, monitoring my Vercel deployments, gives around 50 active connections, which are not dropped.

[denolfe]

Hey @Dovchik , how are you monitoring connections?

I went ahead and deployed the repo to Vercel and connected it up to Vercel Postgres. However, I was only able to see the connections top out at ~20. I was monitoring by running this query every second:

SELECT pid, usename, application_name, client_addr, backend_start
FROM pg_stat_activity;

Anything I might be missing that you're doing differently?