Connection Management
How Application Connections Work
Before diving into connection problems and pooling solutions, it helps to understand the full lifecycle of a database connection — 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 — establishing the encrypted network connection (often 20-50ms over the network)
- Postmaster accepts — the Postgres main process receives the connection request
- Fork a backend process — Postgres creates a dedicated OS process for this connection (this is the expensive part)
- Authenticate — password, SCRAM-SHA-256, or certificate validation
- Ready — the backend is allocated memory and waits for queries
This entire sequence costs 50-100ms for a new connection. If your application connects fresh for every request and then runs a 1ms query, you're spending 98% of the time just establishing the connection.
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 actively querying or sitting idle
- Is visible in
pg_stat_activityas a separate row
This is fundamentally different from connection models in MySQL (thread-per-connection) or application servers (thread pools). The OS process overhead is what makes connection management critical in Postgres — you can't just open 10,000 connections and let the server sort it out.
Connection Limits and Reserved Slots
The default max_connections = 100 and this can be altered by session or for the system. However setting max_connections to a really high number like 2,000 won't solve your connection issues. 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 PostgreSQL 16, reserved_connections provides a separate pool for non-superuser roles with the pg_use_reserved_connections privilege — giving your monitoring and admin roles guaranteed access without requiring 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 — all of which interact with 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 a query needs to execute. This is the single most effective way to reduce resource consumption on your database server.
When to Add a Connection Pooler
You don't need a connection pooler on day one. With 1-2 application servers opening 5-10 connections each, even a small Postgres instance handles that fine. The question is: at what point does connection management become worth the operational complexity?
Three Layers of Connection Pooling
Connection pooling isn't a single thing — it operates at different layers, and each 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 50-100ms connection cost each time), the pool keeps connections open and reuses them.
This is table stakes for production — every major framework supports it:
- Python: SQLAlchemy's
pool_size, Django'sCONN_MAX_AGE - Java: HikariCP, c3p0
- Node.js:
pgmodule'sPool - Ruby: ActiveRecord's
poolsetting
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 99% of the time most of them are idle. 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 — and especially if 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 growing with app instances — each new deployment adds connections regardless of actual query load
- "Too many connections" errors — you've hit
max_connectionsbecause idle connections are eating all the slots - TLS handshake latency — new connections take 50ms+ to establish, especially across availability zones
- Memory pressure on the database server — connection overhead is competing with
shared_buffersandwork_mem - 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
aside positive Snowflake Postgres includes built-in PgBouncer on every instance — you just need to install the
snowflake_poolerextension and connect on port 5431. No separate deployment, no infrastructure to manage. RunCREATE EXTENSION snowflake_pooler;and configure your application to connect through 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 solution — 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 — the default choice |
statement | After each statement | Only for simple autocommit workloads; breaks multi-statement transactions |
Transaction Mode Limitations
Transaction mode is correct for 90% of deployments. Session mode provides no pooling benefit if connections are long-lived. Statement mode breaks basic application logic. Transaction pooling means 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)PREPARE/EXECUTEnamed prepared statements (use protocol-level prepared statements or session mode)SETsession variables (reset after each transaction)- Temporary tables (dropped when connection returns to pool)
- Advisory locks held beyond a transaction
aside positive Snowflake Postgres includes built-in connection pooling — no separate PgBouncer deployment needed. The platform manages connection multiplexing automatically, so your application can open thousands of connections while Postgres sees only what it needs.
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 doing it:
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 most dangerous state is idle in transaction — it holds locks, prevents VACUUM from cleaning 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 identify a connection that needs to go — a long-running idle-in-transaction, 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 sends a cancel signal and the client can reconnect. pg_terminate_backend() is the sledgehammer — 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();
The lock_timeout is especially important — without it, a single DDL statement waiting on a table lock can queue up behind it, causing a cascade where all queries on that table block. Set it system-wide to a low value (5-10s), and override per-session when you intentionally need a longer lock hold during migrations.
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
Connection management in Postgres comes down to one principle: keep the number of active backends low. Use connection pooling (PgBouncer or your platform's built-in pooler) to multiplex application connections. Monitor pg_stat_activity to catch idle-in-transaction bloat and connection leaks. Set timeouts aggressively so abandoned connections don't accumulate. And resist the temptation to just bump max_connections higher — that's treating the symptom while the disease gets worse.
Related Resources
When to add a pooler: application-side pooling, server-side pooling with PgBouncer, and connection plexing across databases.
How to enable and configure the built-in PgBouncer pooler on Snowflake Postgres — transaction mode, prepared statements, and the snowflake_pooler extension.
Official docs on max_connections, timeouts, and authentication settings.
When a single PgBouncer isn't enough — running multiple pooler instances for high-connection-count workloads.
Broader strategies for scaling Postgres — read replicas, partitioning, sharding, and offloading to the lakehouse.
This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances
