Connection Management

Snowflake for Developers/Postgres/Connection Management

On this page

Connection Management

Postgres

Elizabeth Christensen

How Application Connections Work

Before getting into connection problems and pooling, it helps to know what a database connection actually costs, from your application code through the network and into Postgres.

The Connection Lifecycle

When your application opens a connection to Postgres, here's what happens:

┌─────────────┐      ┌──────────────────┐      ┌─────────────────────────────────┐
│ Application │      │    Network       │      │         Postgres                │
│             │      │                  │      │                                 │
│  1. Open    │─────►│  2. TCP + TLS    │─────►│  3. postmaster accepts          │
│  connection │      │     handshake    │      │  4. fork() new backend process  │
│             │◄─────│                  │◄─────│  5. authenticate client         │
│  6. Ready   │      │                  │      │  6. backend ready for queries   │
└─────────────┘      └──────────────────┘      └─────────────────────────────────┘

Each step takes real time:

Application requests a connection. Your code (or connection library) issues a connect call.
TCP + TLS handshake. This sets up the encrypted network connection, often 20 to 50ms over the network.
Postmaster accepts. The main Postgres process receives the request.
Fork a backend process. Postgres creates a dedicated OS process for this connection. This is the expensive part.
Authenticate. Password (SCRAM-SHA-256), certificate, or another configured method.
Ready. The backend has its memory and waits for queries.

A new connection commonly costs somewhere around 50 to 100ms end to end. If your application connects fresh for every request and then runs a 1ms query, nearly all of that request's database time is spent just connecting. On Postgres 18, log_connections = 'setup_durations' will log how long each connection's setup took, so you can measure this on your own system instead of guessing.

The Process-Per-Connection Architecture

Unlike many databases that use threads, Postgres uses a process-per-connection model. The postmaster process is the gatekeeper. It listens on port 5432 and forks a new backend process for every client that connects:

                          ┌──────────────────┐
                          │    postmaster     │  ← listens on port 5432
                          │   (main process)  │
                          └────────┬─────────┘
                                   │ fork()
              ┌────────────────────┼────────────────────┐
              │                    │                    │
     ┌────────▼───────┐  ┌────────▼───────┐  ┌────────▼───────┐
     │  backend (PID  │  │  backend (PID  │  │  backend (PID  │
     │  14521)        │  │  14892)        │  │  15201)        │
     │  app_user      │  │  app_user      │  │  monitor       │
     │  idle          │  │  active        │  │  idle          │
     └────────────────┘  └────────────────┘  └────────────────┘
              ▲                    ▲                    ▲
         Web server          Worker process       Monitoring

Each backend process:

Has its own memory space (5-10 MB minimum, more under load with work_mem)
Maintains its own session state (variables, prepared statements, temp tables)
Persists for the lifetime of the connection, whether it's running queries or sitting idle
Is visible in pg_stat_activity as a separate row

This is fundamentally different from connection models in MySQL (thread-per-connection) or application servers (thread pools). That per-process overhead is why connection management matters so much in Postgres. You can't just open 10,000 connections and let the server sort it out.

Connection Limits and Reserved Slots

The default is max_connections = 100. It's a server-wide setting and changing it requires a restart. Setting it to something huge like 2,000 won't fix your connection problems, though. Beyond a few hundred connections, memory pressure, OS context switching, and lock contention may degrade performance. You can check your current state with:

SELECT
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
  (SELECT count(*) FROM pg_stat_activity) AS current_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'superuser_reserved_connections') AS reserved_for_superuser;

Postgres reserves a few slots so administrators can always connect, even when max_connections is exhausted:

-- PG 16 and earlier: only superusers get reserved slots
SHOW superuser_reserved_connections;  -- Default: 3

Starting in Postgres 16, reserved_connections sets aside slots for non-superuser roles that have the pg_use_reserved_connections privilege. That gives your monitoring and admin roles guaranteed access without making them superuser:

-- PG 16+: reserved_connections for privileged non-superuser roles
SHOW reserved_connections;  -- Default: 0

-- Grant access to a monitoring role
GRANT pg_use_reserved_connections TO monitoring_role;

Where Connections Fit in the Postgres Instance

A Postgres instance is more than just the database engine. It's a collection of services that work together:

The database cluster (the set of databases, schemas, and roles) is the core, but production instances also need connection pooling, monitoring, backups, and high availability, and all of those touch how connections are managed.

Connection Flow With and Without Pooling

Without a connection pooler, every application process connects directly to Postgres:

┌─────────────────────────────────────────┐
│           Application                    │
│  ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐   │
│  │ Web  │ │ Web  │ │ Web  │ │ Web  │   │  20 workers × 10 servers
│  │Worker│ │Worker│ │Worker│ │Worker│   │  = 200 direct connections
│  └──┬───┘ └──┬───┘ └──┬───┘ └──┬───┘   │
└─────┼────────┼────────┼────────┼────────┘
      │        │        │        │
      ▼        ▼        ▼        ▼
┌─────────────────────────────────────────┐
│           Postgres                       │
│  200 backend processes                   │  ← 200 × 10 MB = 2 GB just for connections
│  (190 idle at any given moment)          │
└─────────────────────────────────────────┘

With a connection pooler, the picture changes dramatically:

┌─────────────────────────────────────────┐
│           Application                    │
│  200 connections to PgBouncer            │
└──────────────────┬──────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────┐
│          PgBouncer (pooler)              │
│  Accepts 200 client connections          │
│  Maintains only 20 server connections    │
└──────────────────┬──────────────────────┘
                   │
                   ▼
┌─────────────────────────────────────────┐
│           Postgres                       │
│  20 backend processes                    │  ← 20 × 10 MB = 200 MB
│  (efficiently utilized)                  │
└─────────────────────────────────────────┘

The pooler acts as a multiplexer. It queues client requests and hands out real Postgres connections only when there's work to run. For most busy apps, this is the single biggest resource win you can get on the database server.

When to Add a Connection Pooler

You don't need a connection pooler on day one. With 1 or 2 application servers opening 5 to 10 connections each, even a small Postgres instance handles that fine. The real question is when the extra moving part starts paying for itself.

Three Layers of Connection Pooling

Connection pooling happens at different layers, and each one solves a different problem:

1. Application-side pooling (connection libraries)

Most frameworks maintain a local pool of connections per application process. Instead of connecting fresh for every request (paying the connection cost each time), the pool keeps connections open and reuses them.

This is table stakes for production, and every major framework supports it:

Python: SQLAlchemy's pool_size, Django's CONN_MAX_AGE
Java: HikariCP, c3p0
Node.js: pg module's Pool
Ruby: ActiveRecord's pool setting

Application-side pooling solves connection latency but introduces a new problem: each application instance holds open connections whether it needs them or not.

2. Server-side pooling (i.e. PgBouncer)

When you scale to multiple application servers, the math gets ugly. If each of your 20 app servers maintains a pool of 10 connections, that's 200 connections to Postgres, and most of them are idle most of the time. Those idle connections still consume memory and process slots.

A server-side pooler like PgBouncer sits in front of Postgres and multiplexes all those application connections through a much smaller number of actual database connections. Your 200 application connections might share just 20 real backends.

3. Read routing across replicas

At the highest scale, you route read queries to replicas to distribute load across multiple nodes. This is a separate concern from connection pooling and is covered in the Scaling Guide.

Signals That You Need Server-Side Pooling

The best indicator is the ratio of idle to active connections. Run this query:

SELECT count(*),
       state
FROM pg_stat_activity
GROUP BY 2;

 count |             state
-------+-------------------------------
     7 | active
    69 | idle
    26 | idle in transaction
    11 | idle in transaction (aborted)

If you see more idle connections than active ones, especially once you're in the high tens or hundreds of total connections, a pooler is a quick win. In the example above, only 7 of 113 connections are doing work. The other 106 are consuming memory for nothing.

Other signals:

Connection counts grow with every app instance you add, regardless of actual query load.
You're seeing "too many connections" errors because idle connections are eating all the slots.
New connections are slow to establish (TLS handshakes, especially across availability zones).
The database server is under memory pressure, with connection overhead competing with shared_buffers and work_mem.
Your workload is bursty: traffic spikes need hundreds of connections briefly, but the baseline is much lower.

When You Don't Need It

Skip the pooler if:

You have fewer than 50 total connections across all application instances
Your connections are long-lived and consistently active (batch processing, ETL)
You're already within your memory budget and nowhere near max_connections

Snowflake Postgres includes PgBouncer on every instance, so there's nothing separate to deploy. Run CREATE EXTENSION snowflake_pooler; and point your application at port 5431.

Connection Pooling with PgBouncer

Connection pooling solves the fundamental mismatch between what applications want (thousands of connections for simplicity) and what Postgres can efficiently handle (tens to low hundreds of active backends). PgBouncer is the standard answer: a lightweight, single-process pooler that sits between your application and Postgres.

How PgBouncer Works

The math: your application opens 1000 connections to PgBouncer. PgBouncer maintains only 20 actual connections to Postgres. When a client needs to execute a query, PgBouncer assigns it one of the 20 server connections, and when the transaction completes, returns that connection to the pool for reuse.

┌─────────────────┐          ┌─────────────┐          ┌──────────────┐
│   Application   │          │  PgBouncer  │          │   Postgres   │
│                 │          │             │          │              │
│  1000 conns  ───┼────────►│   Pool: 20  ├────────►│  20 backends │
│                 │          │             │          │              │
└─────────────────┘          └─────────────┘          └──────────────┘

Pool Modes

PgBouncer supports three pool modes that control when a server connection is released back to the pool:

Mode	Connection Released	Use Case
`session`	When client disconnects	Legacy apps needing session state (LISTEN/NOTIFY, prepared statements)
`transaction`	After each transaction commits/rolls back	Most web applications; start here
`statement`	After each statement	Only for simple autocommit workloads; breaks multi-statement transactions

Transaction Mode Limitations

Transaction mode is the right call for most deployments. Session mode gives you little pooling benefit when connections are long-lived, and statement mode breaks multi-statement transactions. Transaction pooling does mean different client requests may use different server connections. This breaks features that rely on per-connection state:

LISTEN/NOTIFY (use session mode for pub/sub)
SQL-level PREPARE/EXECUTE (use protocol-level prepared statements, which PgBouncer 1.21+ supports in transaction mode via max_prepared_statements, or use session mode)
SET session variables (reset after each transaction)
Temporary tables (dropped when connection returns to pool)
Advisory locks held beyond a transaction

On Snowflake Postgres, the built-in pooler runs in transaction mode, so the limitations above apply. See the connection pooling docs for prepared statement support and configuration.

Monitoring and Troubleshooting Connections

When your application starts throwing "too many connections" errors or response times spike, you need to quickly identify what's consuming connection slots and why.

pg_stat_activity: Your Connection Dashboard

pg_stat_activity shows every backend process: who connected, when, what they're doing, and how long they've been at it. Here's a query we use all the time:

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  state_change,
  now() - state_change AS time_in_state,
  left(query, 60) AS current_query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY state_change ASC;

  pid  | usename  | application_name | client_addr  |     state     |     query_start     |    state_change     | time_in_state |              current_query
-------+----------+------------------+--------------+---------------+---------------------+---------------------+---------------+---------------------------------------------
 14521 | app_user | web-api          | 10.0.1.42    | idle in transaction | 2026-06-17 08:12:01 | 2026-06-17 08:12:01 | 04:15:32      | SELECT * FROM orders WHERE id = $1
 14892 | app_user | worker           | 10.0.1.43    | active        | 2026-06-17 12:27:10 | 2026-06-17 12:27:10 | 00:00:23      | UPDATE large_table SET status = 'processed'
 15201 | monitor  | pgwatch          | 10.0.1.10    | idle          | 2026-06-17 12:27:30 | 2026-06-17 12:27:30 | 00:00:03      | SELECT count(*) FROM pg_stat_activity

The state to worry about is idle in transaction. It holds locks, keeps VACUUM from cleaning up dead rows, and wastes a connection slot. The process at pid 14521 has been sitting idle in a transaction for over 4 hours.

Finding Connection Hogs

-- Who is using the most connections?
SELECT
  usename,
  application_name,
  client_addr,
  count(*) AS connections,
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn,
  count(*) FILTER (WHERE state = 'active') AS active
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, client_addr
ORDER BY connections DESC;

Killing Problem Connections

When you find a connection that needs to go (a long idle-in-transaction session, a runaway query, or a leaked connection):

-- Cancel the current query (graceful: client gets an error, connection stays open)
SELECT pg_cancel_backend(14892);

-- Terminate the entire connection (forceful: closes the connection)
SELECT pg_terminate_backend(14521);

pg_cancel_backend() is the polite option. It cancels the running query and leaves the connection open. pg_terminate_backend() is the sledgehammer, and the connection is gone.

-- Terminate all idle-in-transaction connections older than 1 hour
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '1 hour';

Preventing Runaway Connections - Set at Statement Timeout

Set timeouts to automatically clean up abandoned connections and stalled transactions:

-- Kill idle-in-transaction backends after 5 minutes
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';

-- Kill completely idle connections after 30 minutes (PG 14+)
ALTER SYSTEM SET idle_session_timeout = '30min';

-- Kill any single statement running longer than 30 seconds
ALTER SYSTEM SET statement_timeout = '30s';

-- Prevent long lock waits from cascading into outages
ALTER SYSTEM SET lock_timeout = '10s';

SELECT pg_reload_conf();

Don't skip lock_timeout. Without it, a DDL statement waiting for a table lock sits in the lock queue, and every query that arrives after it on that table queues up behind it too. A low value (5 to 10s) makes that DDL give up instead of taking the table down with it. Override it per session when a migration legitimately needs to wait longer.

Be careful with a global statement_timeout, though. It also applies to things like manual VACUUM, index builds, and reporting queries, so you may want to set it per role (ALTER ROLE app_user SET statement_timeout = '30s') instead.

Application-Level Connection Strings

Your connection string can include parameters that prevent connection leaks at the application layer:

# Connection timeout: don't wait forever to establish a connection
postgresql://user:pass@host:5432/myapp?connect_timeout=5

# Statement timeout at the connection level
postgresql://user:pass@host:5432/myapp?options=-c%20statement_timeout%3D30000

# Multiple hosts with failover
postgresql://user:pass@primary:5432,replica:5432/myapp?target_session_attrs=read-write&connect_timeout=5

Most connection pool libraries (HikariCP, SQLAlchemy, node-pg) also expose settings for:

Maximum pool size: Keep this at 10-20 per application instance
Connection max lifetime: Recycle connections periodically (30-60 minutes)
Idle timeout: Return unused connections to the OS
Leak detection: Alert when connections are checked out too long

# SQLAlchemy example: application-side pool settings
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://user:pass@pgbouncer:6432/myapp",
    pool_size=10,           # Steady-state connections
    max_overflow=5,         # Burst capacity above pool_size
    pool_timeout=5,         # Wait max 5s for a connection
    pool_recycle=1800,      # Recycle connections every 30 min
    pool_pre_ping=True,     # Verify connection is alive before use
)

Conclusion

Keep the number of Postgres backends low. Use a pooler (PgBouncer or your platform's built-in one) to multiplex application connections, watch pg_stat_activity for idle-in-transaction sessions and leaks, and set timeouts so abandoned connections don't pile up. Bumping max_connections higher treats the symptom and makes the underlying problem worse.

Related Resources

Your Guide to Connection Management in Postgres, by Craig Kerstiens (Crunchy Data)

When to add a pooler: application-side pooling, server-side pooling with PgBouncer, and connection plexing across databases.

Snowflake Postgres Connection Pooling Documentation

How to enable and configure the built-in PgBouncer pooler on Snowflake Postgres: transaction mode, prepared statements, and the snowflake_pooler extension.

PostgreSQL Documentation: Connections and Authentication

Official docs on max_connections, timeouts, and authentication settings.

Postgres at Scale: Running Multiple PgBouncers, by Elizabeth Christensen (Crunchy Data)

When a single PgBouncer isn't enough: running multiple pooler instances for high-connection-count workloads.

Scaling Guide

Broader strategies for scaling Postgres: read replicas, partitioning, sharding, and offloading to the lakehouse.

Updated Oct 27, 2026

This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances