Connection pooling

Each PostgreSQL connection creates a new process in the operating system, which consumes resources. For this reason, PostgreSQL limits the number of open connections. Neon permits 100 simultaneous PostgreSQL connections by default with a max_connections=100 setting, which is the typical default for this parameter. A small number of those connections are reserved for administrative purposes. A connection limit of 100 may not be sufficient for some applications. To increase the number of connections that Neon supports, you can enable connection pooling.

Some applications open numerous connections, with most eventually becoming inactive. This behavior can often be attributed to database driver limitations, to running many instances of an application, or to applications with serverless functions. With regular PostgreSQL, new connections are rejected when reaching the max_connections limit. To overcome this limitation, Neon supports connection pooling using PgBouncer, allowing Neon to support up to 10000 concurrent connections.

PgBouncer is an open-source connection pooler for PostgreSQL. When an application needs to connect to a database, PgBouncer provides a connection from the pool. Connections in the pool are routed to a smaller number of actual PostgreSQL connections. When a connection is no longer required, it is returned to the pool and is available to be used again. Maintaining a pool of available connections improves performance by reducing the number of connections that need to be created and torn down to service incoming requests. Connection pooling also helps avoid rejected connections. When all connections in the pool are being used, PgBouncer queues a new request until a connection from the pool becomes available.

Neon uses PgBouncer in transaction mode. For limitations associated with transaction mode, see Connection pooling notes and limitations. For more information about PgBouncer, refer to https://www.pgbouncer.org/.

Enable connection pooling

Enabling connection pooling in Neon requires adding a -pooler suffix to the compute endpoint ID, which is part of the hostname. Connection requests that specify the -pooler suffix use a pooled connection.

Add the -pooler suffix to the endpoint ID, as shown:

postgres://sally:<password>@ep-throbbing-boat-918849-pooler.us-east-2.aws.neon.tech/neondb

Enabling the Pooler option in the Connection Details widget on the Neon Dashboard adds the -pooler suffix to the connection string, which you can copy:

Enable pooler option

To connect to the same database directly with a non-pooled connection, use the same connection string without the -pooler suffix:

postgres://sally:<password>@ep-throbbing-boat-918849.us-east-2.aws.neon.tech/neondb

note

The previous method of enabling connection pooling for a compute endpoint is deprecated. When using a pooling-enabled connection, as described above, ensure that connection pooling is not enabled for the compute endpoint. To disable pooling for a compute endpoint, refer to the instructions in Edit a compute endpoint.

Connection pooling notes and limitations

Neon uses PgBouncer in transaction mode, which does not support PostgreSQL features such as prepared statements or LISTEN/NOTIFY. For a complete list of limitations, refer to the "SQL feature map for pooling modes" section in the pgbouncer.org Features documentation.

Some clients and applications may require connection pooling. For example, using Prisma Client with PgBouncer from a serverless function requires connection pooling. To ensure that connection pooling is enabled for clients and applications that require it, you can add the ?pgbouncer=true flag to your Neon connection string, as shown in the following example:

postgres://casey:<password>@ep-square-sea-260584-pooler.us-east-2.aws.neon.tech:5432/neondb?pgbouncer=true

Prisma Migrate, however, requires a direct connection to the database, and currently does not support connection pooling with PgBouncer. Attempting to run Prisma Migrate commands in any environment that enables PgBouncer for connection pooling results in the following error:

Error: undefined: Database error
Error querying the database: db error: ERROR: prepared statement "s0" already exists

When updating your database schema using Prisma Migrate, you need to use a non-pooled connection URL. You can configure the non-pooled connection string by using the directUrl property in the datasource block.

Update your .env file with the following changes:

Rename the DATABASE_URL environment variable to DIRECT_URL
Create a DATABASE_URL environment variable and paste in the new connection string from the dashboard as its value
Append the ?pgbouncer=true flag to the DATABASE_URL variable

Your .env file should resemble the following:

DATABASE_URL="postgres://casey:<password>@ep-square-sea-260584-pooler.us-east-2.aws.neon.tech:5432/neondb?pgbouncer=true"
DIRECT_URL="postgres://casey:<password>@ep-square-sea-260584.us-east-2.aws.neon.tech:5432/neondb"

Update your Prisma schema by setting the directUrl in the datasource block:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  directURL         = env("DIRECT_URL")
}

note

This feature is available from Prisma version 4.10.0 and higher.

You may encounter this error with other applications that require a direct connection to PostgreSQL or applications that are not compatible with PgBouncer in transaction mode. To address this issue, Neon supports both pooled and non-pooled connections to the same database. For more information, see Enable connection pooling.

For more information about using Prisma in a PgBouncer-enabled environment, refer to the Prisma documentation.

PostgreSQL features such as prepared statements and LISTEN/NOTIFY are not supported with connection pooling in transaction mode. For a complete list of limitations, refer to the "SQL feature map for pooling modes" section, in the pgbouncer.org Features documentation.

Need help?

Send a request to support@neon.tech, or join the Neon community forum.