How We Fixed Postgres Connection Pooling on Serverless with PgDog

The Root Cause of Connection Spikes

Every deploy on Vercel triggered a flood of serverless functions, each scrambling to grab a database connection. While the database never ran out of CPU or memory, the connection limits were constantly hit. Supabase's default pooler, Supavisor, suffered from connections that wouldn't recycle properly. Switching to PgBouncer solved the stuck connections but introduced a new bottleneck during traffic spikes.

The fundamental limitation is that PgBouncer is single-threaded. When hundreds of serverless functions spin up simultaneously, a single thread simply cannot assign connections fast enough. If traffic is steady, PgBouncer performs flawlessly. But under bursty serverless traffic, it struggles. Tuning Postgres timeouts and indexes improved database health but didn't address the pooler's inability to handle these connection storms.

Discovering and Deploying PgDog

We discovered PgDog, a fork of the multi-threaded PgCat pooler originally built at Instacart. The philosophy behind PgDog aligned with our needs:

1. You shouldn't have to scale up a database just to raise connection limits—the pooler should solve that.
2. The pooler must be located close to both the database and the runtime to minimize latency.

We deployed PgDog on AWS EKS and connected it to our database. After rigorous testing with pgbench and simulated bursty traffic on staging, PgDog easily absorbed the massive connection spikes. Metrics registered the surges, but the pooler never faltered.

When we encountered an initial caching conflict with Prisma's prepared statements, the PgDog team diagnosed and shipped a fix within days.

Example Configuration Step

When configuring PgDog on AWS EKS, you define your database routing and pooling limits using the pgdog.toml file:

[databases.primary]
host = "db.example.com"
port = 5432
user = "postgres"
password = "supersecretpassword"
dbname = "production_db"

[pool]
pool_size = 100
max_client_connections = 10000

(Note: This is an illustrative example of pooler configuration inspired by the source concepts.)

Unexpected Benefits of PgDog

Beyond fixing connection spikes, PgDog brought several architectural improvements:

• Health-aware load balancing: PgDog constantly monitors the health of each Postgres instance. It automatically routes queries away from unhealthy hosts, meaning zero read downtime when Supabase is resizing or restarting instances.
• Enhanced Observability: PgDog emits metrics in OpenMetrics format. We can now scrape these with Prometheus and visualize them in Grafana, giving us real-time visibility into client connections, waiting clients, and query latencies.

The Outcome and Cost Savings

With PgDog in place, we eliminated a read replica and scaled our Supabase database down from a 12xl to a 4xl instance. Even during peak deploy spikes, pooler-to-database connections remained well within safe limits. We no longer overprovision our database for connections; we size it for actual compute resource usage.

Running PgDog on EKS costs a fraction of an overprovisioned database. More importantly, we can now confidently deploy to production at any hour of the day.

Frequently Asked Questions (FAQ)

1. Why is database connection pooling crucial for serverless applications?

Serverless architectures can scale out rapidly, generating a massive number of concurrent instances. Since each instance typically requires a database connection, this behavior can quickly exhaust the database's maximum connection limit unless an intermediary pooler manages and multiplexes the connections.

2. Why does PgBouncer struggle with bursty traffic?

PgBouncer is single-threaded. When there is a sudden spike in new connection requests—common during serverless deployments—it has to process them sequentially on one thread, which creates a significant bottleneck and latency for incoming queries.

3. How does PgDog differ from PgBouncer?

PgDog is a multi-threaded connection pooler. It can utilize multiple CPU cores to handle incoming connection requests simultaneously, making it far superior at managing sudden bursts of traffic without buckling.

4. Can PgDog handle read-replica load balancing?

Yes. PgDog features health-aware load balancing. It checks the status of database instances and seamlessly routes read queries to healthy replicas, preventing application downtime if a particular replica fails or is undergoing maintenance.

Kaynak / Source: https://circleback.ai/blog/how-we-fixed-postgres-connection-pooling-on-serverless-with-pgdog