Problem:

I was curious to know how Next.js handles connection management with databases because each API route is a separate serverless function that establishes a new connection to the database. Unlike Express APIs, which run in a more traditional server-like environment and can maintain persistent connections using connection pools (like those provided by the pg library), each serverless function in Next.js spins up and down rapidly. This makes it challenging to reuse connections, leading to increased connection overhead.

The edge runtime introduces even more complexity because it doesn’t support standard TCP connections, which are typically required by traditional relational databases like PostgreSQL. Instead, HTTP-based database proxies or serverless database platforms (like Neon) are needed to establish connections, which impacts latency and introduces additional layers of complexity.

While exploring these challenges, I decided to compare how two popular databases, PostgreSQL and Firestore, handle connection management differently and what implications this has for serverless and edge runtimes.

Postgres connections

Postgres uses a process-per-client model, meaning whenever a new client connects, Postgres creates a new background process for that client. These child processes are spawned by the postmaster process, and all queries are executed on behalf of these child processes. This model imposes several challenges, especially when using a Postgres database in a serverless environment, because each function is considered a separate client.

By default postgres allows you to have 100 concurrent connections you can check this number by

SELECT name, setting 
FROM pg_settings 
WHERE name = 'max_connections';

Another popular way to manage connections in a PostgreSQL database is by using a separate connection pooler like pgBouncer, an open-source connection pooler that efficiently manages database connections. When a client attempts to connect to the database, the request is routed to pgBouncer, which assigns a connection from its pool. Once the response is sent back to the client, the connection is returned to the pool, making it available for subsequent clients.

Neon uses pgBouncer to support connection pooling, as detailed here, while Supabase uses a custom-built connection pooler called Supavisor, which you can learn more about here.

PgBouncer supports three modes:

1. Session Mode: The default mode, which retains the connection until the client disconnects. This is the safest but least efficient mode.
2. Transaction Mode: Releases the connection immediately after the transaction is executed, optimizing resource usage while maintaining transaction integrity.
3. Statement Mode: The most efficient mode when configured correctly, as it frees resources as soon as the query finishes. However, it doesn’t support transactions containing multiple statements, making it suitable for read-heavy workloads but not complex write operations.

Using pgBouncer effectively can greatly improve connection management, especially in serverless environments where maintaining persistent connections is challenging.

Firestore Connection Management

Firestore is an impressive NoSQL serverless database that provides strong consistency and multi-region availability. Client requests arrive at the user’s nearest GCP point of presence, where the database region is looked up from metadata. The requests are then routed to the frontend tasks of the region where the database is located, which subsequently pass the RPC calls to the backend tasks that translate them to Spanner operations.

Since Firestore doesn’t rely on persistent connections or TCP, it integrates smoothly with serverless frameworks like Next.js and edge runtimes, bypassing the connection management challenges faced by traditional databases like PostgreSQL.

You can learn more about Firestore’s architecture here