Before every query, pg-connect runs, taking 20ms each time

[Wiz1991]

Link to reproduction

No response

Environment Info

Binaries:
  Node: 18.20.2
  npm: 10.5.0
  Yarn: 1.22.22
  pnpm: N/A
Relevant Packages:
  payload: 3.0.0-beta.96
  next: 15.0.0-canary.104
  @payloadcms/db-postgres: 3.0.0-beta.96
  @payloadcms/email-nodemailer: 3.0.0-beta.96
  @payloadcms/graphql: 3.0.0-beta.96
  @payloadcms/next/utilities: 3.0.0-beta.96
  @payloadcms/plugin-redirects: 3.0.0-beta.96
  @payloadcms/plugin-search: 3.0.0-beta.96
  @payloadcms/richtext-lexical: 3.0.0-beta.96
  @payloadcms/richtext-slate: 3.0.0-beta.96
  @payloadcms/translations: 3.0.0-beta.96
  @payloadcms/ui/shared: 3.0.0-beta.96
  react: 19.0.0-rc-06d0b89e-20240801
  react-dom: 19.0.0-rc-06d0b89e-20240801
Operating System:
  Platform: linux
  Arch: x64
  Version: #1 SMP PREEMPT_DYNAMIC Fri, 12 Jul 2024 00:06:53 +0000
  Available memory (MB): 48102
  Available CPU cores: 12

Done in 0.92s.

Describe the Bug

I was benchmarking the app and noticed that on every single db query, there is a connected pg-pool connect beforehand.
Each connect taking upwards of 20ms, which quickly adds up to response times of 200-700ms (in rare cases 1s+) when doing a data heavy route.

Bear with me, lots of screenshots and will try to include as much info as possible. First of all, here is one page and the queries i do on it server side.

Page.tsx

const GET_NOVEL_QUERY = gql(/** GraphQL */ `
  query GetNovel($id: String!) {
    Novel(id: $id) {
      id
      originalTitle
      title
      external {
        id
        novelUpdates 
      }
      cover {
        id
        url
        thumbnailURL,
        blurhash
      }
      sypnosis
      author {
        name
        id
      }
      staff {
        id
        username
      }
      tags {
        id
        name
      }
    }
  }
`);

Moving on inside a child component:

export async function TiersList({ novelId }: { novelId: string }) {
const payload = await getPayload({ config });

const { user } = await payload.auth({ headers: headers() });

const { docs: tiers, totalDocs } = await payload.find({
    collection: 'tiers',
    where: {
        novel: {
            equals: novelId,
        },
    },
    depth: 3,
});

if (!totalDocs) return null;

const getActiveMembership = async () => {
    if (!user) return null;

    const { memberships } = await payload.findByID({
        collection: 'users',
        id: user.id,
        showHiddenFields: true,
        depth: 2,
    });

    const membership = memberships?.find((membership) => {
        if (propertyIsRelation(membership) && propertyIsRelation(membership.tier)) {
            return membership.tier.novel === novelId;
        }
    }) as Membership | null;

    if (!membership) return null;

    return {
        ...membership,
        tier: (membership?.tier as Tier).id,
    };
};

// just to avoid an extra query
const activeMembership = user ? await getActiveMembership() : null;

return <TierSelector tiers={tiers} activeMembership={activeMembership}></TierSelector>;

}

This is pretty much all the queries I am doing, with one stupid caveat from my side is that i call this TierList component twice (one for mobile and one for desktop).

This is how the collections look like:

import { CreatePayPalProduct } from '@/hooks/create_paypal_product';
import { CollectionConfig } from 'payload';
import { allowAnonymous, allowUserWithRole } from 'payload-rbac';

const Novels: CollectionConfig = {
    slug: 'novels',
    versions: {
        drafts: true,
    },
    hooks: {
        beforeValidate: [CreatePayPalProduct],
    },
    timestamps: true,
    access: {
        read: allowAnonymous(),
        admin: allowUserWithRole('staff'),
        delete: allowUserWithRole('moderator'),
        update: allowUserWithRole('staff'),
        readVersions: allowUserWithRole('staff'),
        create: allowUserWithRole('moderator'),
    },
    admin: {
        useAsTitle: 'title',
        listSearchableFields: ['title', 'originalTitle'],
        defaultColumns: ['title', 'originalTitle', 'staff', 'tags', '_status'],
    },
    fields: [
        // Base fields
        {
            type: 'text',
            name: 'title',
            required: true,
        },
        {
            type: 'text',
            name: 'originalTitle',
            label: 'Original Title',
            required: false,
        },
        {
            type: 'richText',
            name: 'sypnosis',
        },
        {
            type: 'relationship',
            name: 'author',
            relationTo: 'authors',
            admin: {
                position: 'sidebar',
            },
        },
        {
            type: 'relationship',
            name: 'staff',
            relationTo: 'users',
            hasMany: true,
            admin: {
                position: 'sidebar',
            },
        },
        {
            type: 'relationship',
            name: 'goal',
            relationTo: 'goals',
            hasMany: false,
            admin: {
                position: 'sidebar',
            },
        },
        // Generic configuration - Sidebar
        {
            type: 'relationship',
            name: 'tags',
            relationTo: 'tags',
            hasMany: true,
            admin: {
                allowCreate: true,
                position: 'sidebar',
            },
        },
        {
            type: 'relationship',
            name: 'external',
            relationTo: 'external',
            hasMany: false,
            admin: {
                position: 'sidebar',
            },
        },
        {
            type: 'group',
            admin: { position: 'sidebar' },
            name: 'discord',
            fields: [
                {
                    type: 'relationship',
                    name: 'discordRegular',
                    label: 'Regular',
                    relationTo: 'discord',
                    hasMany: false,
                    admin: {
                        position: 'sidebar',
                    },
                },
                {
                    type: 'relationship',
                    name: 'discordSupporter',
                    label: 'Supporter',
                    relationTo: 'discord',
                    hasMany: false,
                    admin: {
                        position: 'sidebar',
                    },
                },
            ],
        },
        // Media
        {
            type: 'row',

            fields: [
                {
                    type: 'upload',
                    name: 'cover',
                    relationTo: 'media',
                },
                {
                    type: 'upload',
                    name: 'banner',
                    relationTo: 'media',
                },
            ],
        },
        {
            type: 'text',
            name: 'paypalId',
            label: 'PayPal',
            admin: {
                position: 'sidebar',
                readOnly: true,
            },
        },
    ],
};

export default Novels;

Any relations on this that you see are extremely simple, primary key + 1-2 fields. Nothing else.

import { authenticate, patch, PlansService } from '@/lib/paypal';
import { CollectionConfig } from 'payload';
import { allowUserWithRole } from 'payload-rbac';
import { CreateSubscriptionPlan } from '@/hooks/create_subscription_plan';

const Tiers: CollectionConfig = {
    slug: 'tiers',
    admin: {
        useAsTitle: 'name',
        description: 'Tiers are the different levels of subscription that users can purchase.',
        hidden(args) {
            return !args.user?.roles?.includes('moderator');
        },
    },
    hooks: {
        beforeValidate: [CreateSubscriptionPlan],
    },
    access: {
        read: allowUserWithRole('moderator'),
        admin: allowUserWithRole('moderator'),
        delete: allowUserWithRole('moderator'),
        update: allowUserWithRole('moderator'),
        readVersions: allowUserWithRole('moderator'),
        create: allowUserWithRole('moderator'),
    },
    fields: [
        {
            type: 'relationship',
            name: 'novel',
            relationTo: 'novels',
            required: true,
        },
        {
            type: 'text',
            name: 'name',
            required: true,
        },
        {
            type: 'textarea',
            name: 'description',
        },
        {
            type: 'row',
            admin: {
                condition: (data) => !data.id,
            },
            fields: [
                {
                    type: 'number',
                    name: 'price',
                    admin: {
                        step: 0.1,
                        disableBulkEdit: true,
                        description: 'You will not be able to change after creating. ',
                    },
                    required: true,
                },
                {
                    type: 'select',
                    name: 'currency',
                    required: true,
                    options: [{ label: 'USD', value: 'usd' }],
                    defaultValue: 'usd',
                },
            ],
        },
        {
            type: 'array',
            name: 'features',
            fields: [
                {
                    type: 'textarea',
                    name: 'description',
                },
            ],
        },
        {
            type: 'relationship',
            name: 'discordRoles',
            relationTo: 'discord',
            hasMany: true,
            admin: {
                position: 'sidebar',
            },
        },
        {
            type: 'checkbox',
            name: 'status',
            label: 'Enabled',
            defaultValue: true,

            admin: {
                position: 'sidebar',
                description:
                    'When disabled, nobody will be able to subscribe to this tier. Existing subscibers will remain',
            },
        },
        {
            type: 'text',
            name: 'paypalId',
            unique: true,
            label: 'PayPal',
            admin: {
                position: 'sidebar',
                readOnly: true,
            },
        },
    ],
};

export default Tiers;

That's it. Now the following is the benchmarks i have.

Before each query, there is a pg connect being ran. Each one taking ~20ms

This can be seen here too:

Reproduction Steps

Provided screenshots above

Adapters and Plugins

No response

[r1tsuu]

Hey @Wiz1991, update on this issue:

I did some investigation and can say that these pg-pool.connect should be a normal thing for a postgres pool.

Fresh Next.js + Postgres only using the pg package, without Payload / Drizzle with this code:

import pg from "pg";

export const getDB = async () => {
  if (!globalThis.client) {
    console.log("CLIENT CREATE");

    const client = new pg.Pool({
      connectionString: `postgres://postgres:[email protected]:5432/payloadtests`,
    });

    client.on("connect", () => {
      console.log("CLIENT CONNECT");
    });

    await client.connect();
    globalThis.client = client;
  }

  return globalThis.client as pg.Pool;
};

import { getDB } from "./db";

export const dynamic = "force-dynamic";

export default async function Home() {
  const db = await getDB();

  const res = await db.query(`SELECT * FROM payload_migrations`);

 return <div>Home</div>
}

• with Sentry logging I can see these pg-pool connections:
  

Important to note that

 await client.connect();

does get called here only 1 time and then gets cached. We do the same, unless your project is deployed on a serverless platform we do .connect() only once.

We disabled transactions for read operations so the performance can be potentially better now.

I'm converting this to a discussion since it's not an issue but just how underlying pg.Pool works.

[Wiz1991]

That is... interesting. Thanks for taking the time to investigate