The SaaS Founder's Guide to Database Connection Pooling

The SaaS Founder's Guide to Database Connection Pooling

Key Facts

• Direct answer: The direct answer is that a connection pool is a set of reusable database connections that your app maintains. Instead of opening a new connection for each request (which is slow and resource-intensive), the app reuses connections from the pool.

• What Is a Connection Pool?: Imagine a restaurant.

• Why Connection Pooling Matters for SaaS: Connection pooling matters for three business reasons.

• How to Configure the Connection Pool: The connection_limit parameter controls the pool size (default: 10).

• How to Choose the Right Pool Size: The right pool size depends on two factors.

Database connection issues are the number one cause of SaaS outages. When your app cannot connect to the database, every request fails, and your SaaS is effectively down. The root cause is usually connection pool exhaustion: your app opens too many connections to the database, the database rejects new connections, and requests fail. This article explains connection pooling in plain language and shows you how to configure it to prevent outages.

The direct answer is that a connection pool is a set of reusable database connections that your app maintains. Instead of opening a new connection for each request (which is slow and resource-intensive), the app reuses connections from the pool. The pool size needs to be configured correctly: too small, and requests queue up (slow responses); too large, and the database runs out of connections (outage). For more on database management, see our article on fixing DATABASE_URL not set.

What Is a Connection Pool?

Imagine a restaurant. If the restaurant hires a new waiter for every customer (opening a new connection per request), the restaurant quickly runs out of waiters (connection limit) and customers wait (slow responses). If the restaurant has a fixed number of waiters who serve multiple customers (a connection pool), the restaurant can serve more customers without running out of waiters.

A connection pool works the same way:

• The app creates a fixed number of database connections (the pool size) when it starts.
• Each request borrows a connection from the pool, uses it, and returns it.
• If all connections are in use, the request waits for one to become available (up to a timeout).
• If the wait exceeds the timeout, the request fails with a "connection pool exhausted" error.

Why Connection Pooling Matters for SaaS

Connection pooling matters for three business reasons:

• Prevents outages. Without a connection pool, each request opens a new connection. Under load (e.g., a traffic spike), the app opens hundreds of connections, the database runs out (most databases have a 100-200 connection limit), and all requests fail. With a properly sized pool, the app reuses connections, and the database never runs out.

• Improves performance. Opening a database connection takes 50-200ms (TCP handshake, authentication, SSL negotiation). With a pool, connections are reused, which means requests start immediately (0ms connection overhead).

• Controls resource usage. A pool limits the number of connections, which prevents the app from overwhelming the database. This is especially important when you have multiple containers (each with its own pool) connecting to the same database.

How to Configure the Connection Pool

For Prisma (Node.js)

// In your schema.prisma or connection string:
// postgresql://user:pass@host:5432/db?connection_limit=10&pool_timeout=10

The connection_limit parameter controls the pool size (default: 10). The pool_timeout parameter controls how long to wait for a connection before failing (default: 10 seconds).

For SQLAlchemy (Python)

engine = create_engine(
    DATABASE_URL,
    pool_size=10,          # Number of connections in the pool
    max_overflow=5,        # Additional connections allowed beyond pool_size
    pool_timeout=10,       # Seconds to wait before giving up
    pool_recycle=3600,     # Recycle connections after 1 hour
)

For pgx (Go)

config, _ := pgxpool.ParseConfig(DATABASE_URL)
config.MaxConns = 10
config.MaxConnIdleTime = 30 * time.Minute
pool, _ := pgxpool.NewWithConfig(context.Background(), config)

How to Choose the Right Pool Size

The right pool size depends on two factors:

1. The database's connection limit. Most managed Postgres providers have a limit of 100 connections (Supabase free tier: 60, Neon free tier: 100). Your total pool size across all containers should not exceed 80 percent of the database's limit (to leave room for admin connections and migrations).

1. The number of containers. If you have 5 containers, each with a pool size of 10, your total connections are 50. If your database limit is 100, you have room to grow. But if you scale to 10 containers, your total connections are 100, which is the limit. You need to reduce the pool size per container.

Formula

pool_size_per_container = (database_connection_limit * 0.8) / number_of_containers

For example:

• Database limit: 100
• Number of containers: 5
• Pool size per container: (100 * 0.8) / 5 = 16

For Blue/Green Deployments

During a blue/green deployment, both the old version (blue) and the new version (green) are running, which means the total connections double temporarily. Account for this:

pool_size_per_container = (database_connection_limit * 0.8) / (number_of_containers * 2)

For more on blue/green deployments and connection pooling, see our article on database connection pooling across blue/green deployments.

Common Pitfalls and Troubleshooting

The first pitfall is an oversized pool. If each container has a pool of 50, and you have 5 containers, your total connections are 250, which exceeds most database limits. The fix is to calculate the pool size based on the formula above.

The second pitfall is connection leaks. If your app does not return connections to the pool (e.g., a missing await pool.close()), the pool gradually empties, and requests start failing. The fix is to use an ORM (like Prisma or SQLAlchemy) that manages connections automatically, and to ensure all connections are returned to the pool.

The third pitfall is long-running transactions. If a request holds a connection for a long time (e.g., a report that takes 5 minutes), other requests cannot use that connection, which effectively reduces the pool size. The fix is to break long-running transactions into smaller ones or to use a separate pool for long-running tasks.

The fourth pitfall is not accounting for blue/green. During a blue/green deployment, both versions are running, which doubles the connections. The fix is to account for the doubling in the pool size calculation.

The fifth pitfall is not monitoring the pool. Without monitoring, you do not know if the pool is near exhaustion. The fix is to monitor the pool usage (via the ORM's metrics or the database's connection metrics) and to alert when the pool is near exhaustion. For more on monitoring, see our article on monitoring your SaaS without hiring a DevOps engineer.

Conclusion: Pool Smart, Stay Online

Database connection issues are the number one cause of SaaS outages, but they are preventable. By understanding what a connection pool is, configuring the right pool size (based on the database limit and the number of containers), and monitoring the pool usage, you can prevent connection exhaustion and keep your SaaS online. The key formula is: pool size per container = (database limit * 0.8) / (number of containers * 2).

Ready to configure your connection pool? Calculate the right pool size using the formula above, update your ORM configuration, and monitor the pool usage. For more, see database connection pooling across blue/green deployments and how to scale your SaaS from MVP to first customers. Explore our free developer tools to speed up your workflow.