In Neon, you can provision Postgres databases in < 1s. Don't believe us? Check out this demo

Connection pooling

Learn how to enable connection pooling in Neon

Neon uses PgBouncer to support connection pooling, enabling up to 10,000 concurrent connections. PgBouncer is a lightweight connection pooler for Postgres.

How to use connection pooling

To use connection pooling with Neon, use a pooled connection string instead of a regular connection string. A pooled connection string adds the -pooler option to your compute endpoint ID, as shown below:

postgres://alex:AbC123dEf@ep-cool-darkness-123456-pooler.us-east-2.aws.neon.tech/dbname?sslmode=require

The Connection Details widget on the Neon Dashboard provides Pooled connection checkbox that adds the -pooler option to a connection string for you. You can copy a pooled connection string from the Dashboard or manually add the -pooler option to the endpoint ID in an existing connection string.

Connection Details pooled connection string

info

The -pooler option routes the connection to a connection pooling port at the Neon Proxy.

Connection limits without connection pooling

Each Postgres connection creates a new process in the operating system, which consumes resources. Postgres limits the number of open connections for this reason. The Postgres connection limit is defined by the Postgres max_connections parameter. In Neon, max_connections is set according to your compute size — and if you are using Neon's Autoscaling feature, it is set according to your minimum compute size.

Compute Size (CU)vCPURAMmax_connections
0.250.251 GB112
0.500.502 GB225
114 GB450
228 GB901
3312 GB1351
4416 GB1802
5520 GB2253
6624 GB2703
7728 GB3154
8832 GB3604

The formula used to calculate max_connections for Neon computes is RAM in bytes / 9531392 bytes. For a Neon Free Tier compute, which has 1 GB of RAM, this works out to approximately 112 connections. Larger computes offered with paid plans have more RAM and therefore support a larger number of connections. For example, a compute with 12 GB of RAM supports up to 1351 connections. You can check the max_connections limit for your compute by running the following query from the Neon SQL Editor or a client connected to Neon:

SHOW max_connections;

note

Four connections are reserved for the Neon-managed Postgres superuser account. For example, for a 0.25 compute size, 4/112 connections are reserved, so you would only have 108 available connections. If you are running queries from the Neon SQL Editor, that will also use a connection. To view the currently open connections, you can run the following query:

SELECT usename FROM pg_stat_activity WHERE datname = '<database_name>';

Even with the largest compute size, the max_connections limit may not be sufficient for some applications, such as those that use serverless functions. To increase the number of connections that Neon supports, you can use connection pooling. All Neon plans, including the Neon Free Tier, support connection pooling.

Connection pooling

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

important

Connection pooling supports up to 10,000 concurrent connections when multiple Postgres users are connecting to multiple Postgres databases. For a single user connecting to a single database, the maximum number of concurrent connections is 64. This limit is controlled by the PgBouncer default_pool_size parameter, which defines the default number of server connections per user/database pair. See Neon PgBouncer configuration settings. If your application connects to your database using a single Postgres role, expect to encounter this limit when exceeding the 64 concurrent connection limit. For information about creating additional Postgres roles, see Manage roles. Roles can created via the Neon Console, CLI, API, and SQL.

PgBouncer is an open-source connection pooler for Postgres. 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 Postgres 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/.

Neon PgBouncer configuration settings

Neon's PgBouncer configuration is shown below. The settings are not user-configurable, but if you are a paid plan user and require a different setting, please contact Neon Support.

[pgbouncer]
pool_mode=transaction
max_client_conn=10000
default_pool_size=64
max_prepared_statements=0
query_wait_timeout=120

The following list describes each setting. For a full explanation of each parameter, please refer to the official PgBouncer documentation.

  • pool_mode=transaction: The pooling mode PgBouncer uses, set to transaction pooling.
  • max_client_conn=10000: Maximum number of client connections allowed.
  • default_pool_size=64: Default number of server connections to allow per user/database pair.
  • max_prepared_statements=0: Maximum number of prepared statements a connection is allowed to have at the same time. 0 means prepared statements are disabled.
  • query_wait_timeout=120: Maximum time queries are allowed to spend waiting for execution. Neon uses the default setting of 120 seconds.

Connection pooling notes

Neon uses PgBouncer in transaction mode, which limits some functionality in Postgres. For a complete list of limitations, refer to the "SQL feature map for pooling modes" section in the pgbouncer.org Features documentation.

Optimize queries with PgBouncer and prepared statements

Protocol-level prepared statements are supported with Neon and PgBouncer as of the PgBouncer 1.22.0 release. Using prepared statements can help boost query performance while providing an added layer of protection against potential SQL injection attacks.

Understanding prepared statements

A prepared statement in Postgres allows for the optimization of an SQL query by defining its structure once and executing it multiple times with varied parameters. Here's an SQL-level example to illustrate. Note that direct SQL-level PREPARE and EXECUTE are not supported with PgBouncer (see below), so you can't use this query from the SQL editor. It is meant to give you a clear idea of how a prepared statement works. Refer to the protocol-level samples below to see how this SQL-level example translates to different protocol-level examples.

PREPARE fetch_plan (TEXT) AS
SELECT * FROM users WHERE username = $1;

EXECUTE fetch_plan('alice');

fetch_plan here is the prepared statement's name, and $1 acts as a parameter placeholder.

The benefits of using prepared statements include:

  • Performance: Parsing the SQL and creating the execution plan happens just once, speeding up subsequent executions. This performance benefit would be most noticeable on databases with heavy and repeated traffic.
  • Security: By sending data values separately from the query, prepared statements reduce the risk of SQL injection attacks.

You can learn more about prepared statements in the PostgreSQL documentation. See PREPARE.

Use prepared statements with PgBouncer

Since pgBouncer supports protocol-level prepared statements only, you must rely on PostgreSQL client libraries instead (direct SQL-level PREPARE and EXECUTE are not supported). Fortunately, most PostgreSQL client libraries support prepared statements. Here are a couple of examples showing how to use prepared statements with Javascript and Python client libraries:

pg
psycopg2
const query = {
  // give the query a unique name
  name: 'fetch-plan',
  text: 'SELECT * FROM users WHERE username = $1',
  values: ['alice'],
};
client.query(query);

Need help?

Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.

Last updated on

Edit this page
Was this page helpful?