PostgreSQL connection limit SaaS

In a SaaS setup, PostgreSQL connection limits are a common scaling pain point because each app server, worker, or tenant can consume connections quickly.

Why it matters

PostgreSQL handles connections with relatively high overhead. If your SaaS opens too many direct DB connections, you can run into:

• “too many connections” errors
• higher memory usage on the DB server
• slower performance from connection churn
• instability during traffic spikes

Typical approaches for SaaS

1. Use a connection pooler

Best practice for most SaaS systems.

Common options:

• PgBouncer: lightweight and widely used
• Pgpool-II: more features, more complexity
• Managed pooling from some cloud providers

Why it helps:

• reuses a small number of PostgreSQL backend connections
• lets many app clients share pooled connections
• reduces connection overhead

Important note:

• With PgBouncer, transaction pooling is usually the most scalable, but some PostgreSQL features need session-level state and may not work the same way.

---

2. Limit app-side connection counts

Make sure each service instance has a conservative max pool size.

Example:

• 10 app instances
• each instance pool max 10
• total potential DB connections = 100

If your PostgreSQL limit is 200, that may be fine. If you scale to 30 instances, you may exceed it.

Rule of thumb:

• calculate:
  instances × pool size + workers + background jobs + admin connections
• keep margin for maintenance and failover

---

3. Separate workloads

In SaaS, different workloads often compete:

• web requests
• background jobs
• analytics/reporting
• migrations
• admin tasks

You can reduce contention by:

• using separate pools for web and jobs
• isolating heavy reporting on a replica
• throttling batch jobs

---

4. Consider multi-tenant architecture carefully

If each tenant gets its own schema or database, connection management becomes important.

Patterns:

• shared database, shared schema: easiest for connections
• shared database, separate schema: still manageable
• database per tenant: can multiply connection and operational complexity

For database-per-tenant SaaS, you may need:

• proxy layer
• lazy connection creation
• strict per-tenant connection caps

---

5. Tune PostgreSQL and pool sizes together

PostgreSQL max_connections should not just be increased blindly.

Higher max_connections:

• uses more memory
• can reduce efficiency
• may hide app pooling problems

Better:

• keep PostgreSQL connections moderate
• use poolers to multiplex traffic
• size pools based on actual concurrency needs

---

Practical SaaS recommendations

A solid baseline:

• Put PgBouncer in front of PostgreSQL
• Keep app pool sizes small, e.g. 5–20 per instance depending on traffic
• Reserve connections for:
  • migrations
  • monitoring
  • admin access
  • background workers
• Use read replicas for read-heavy workloads
• Monitor:
  • active connections
  • queue wait time
  • pool utilization
  • connection errors

Common pitfalls

• opening a new connection per request
• forgetting background workers in the connection budget
• using session pooling with software that relies on session state
• increasing max_connections instead of fixing pooling
• not accounting for autoscaling

If you want, I can help you with:

1. a connection sizing formula for your SaaS,
2. a PgBouncer setup, or
3. a PostgreSQL max_connections recommendation based on your app size.