Skip to main content

Supabase Connection Pooling Guide: Transaction Mode (Port 6543) vs Session Mode

 You deploy a Supabase Edge Function. It works perfectly during testing. Then, you launch. Traffic spikes, thousands of users hit your endpoint simultaneously, and your logs fill with the dreaded PostgreSQL error:

FATAL: remaining connection slots are reserved for non-replication superuser connections

Or simply: FATAL: sorry, too many clients already.

Your serverless functions are scaling, but your database is crashing. This is the classic "Serverless vs. Stateful" impedance mismatch. In this guide, we will solve this by implementing Supabase’s connection pooler correctly, distinguishing between Port 6543 and 5432, and forcing Transaction Mode to handle high concurrency.

The Root Cause: Why Serverless Kills Postgres

To fix the problem, you must understand the architecture of the failure.

PostgreSQL uses a process-based model. For every established connection, the database spins up a new backend process. This process consumes a significant amount of RAM (roughly 2-10MB per connection depending on workload) and requires CPU cycles for context switching.

The Math of Failure

A standard Postgres instance usually defaults to a max_connections limit of 100 to 500 (depending on your compute tier).

Serverless environments (Supabase Edge Functions, Vercel, AWS Lambda) are ephemeral and stateless. If you have 1,000 concurrent function invocations, they will attempt to open 1,000 distinct TCP connections to your database.

  1. Direct Connection (Port 5432): Your function opens a TCP socket.
  2. Postgres: Spawns a process.
  3. Result: At the 101st invocation, the DB rejects the connection. The remaining 900 users get 500 errors.

You cannot solve this by simply increasing max_connections in Postgres postgresql.conf. You will run out of RAM before you satisfy the scale of a serverless architecture. You need a connection pooler.

The Solution: Supavisor and Port 6543

Supabase comes with a built-in connection pooler called Supavisor. It acts as a proxy between your serverless functions and the actual database.

Supavisor maintains a relatively small, warm pool of connections to the real database (Port 5432). It then accepts thousands of incoming connections from your serverless functions (Port 6543) and maps them to that small pool.

However, simply changing the port isn't enough. You must select the correct Pooling Mode.

Session Mode vs. Transaction Mode

This is where developers get stuck.

  1. Session Mode (Default behavior): A client connects to the proxy. The proxy assigns it a specific backend connection. That connection is locked to the client until the client disconnects.

    • Why it fails serverless: In serverless, functions often stay "warm" or idle. If the function holds the session, the pool slot is occupied even if no query is running.

  2. Transaction Mode (The Fix): The client connects to the proxy. The proxy does not assign a backend connection immediately.

    • When the client sends a query (e.g., SELECT * FROM users), the proxy borrows a connection, runs the query, and immediately returns the connection to the pool.
    • Result: A pool of 20 real DB connections can serve thousands of concurrent serverless functions, provided the queries are fast.

You must use Transaction Mode on Port 6543 for Edge Functions.

Implementation: Connecting via Transaction Mode

Let’s look at a real-world implementation using a Supabase Edge Function (Deno) and postgres.js (a high-performance PostgreSQL driver for Node/Deno).

1. Configuration Check

First, verify your project settings:

  1. Go to the Supabase Dashboard -> Settings -> Database.
  2. Scroll to Connection Pooling.
  3. Ensure "Pool Mode" is set to Transaction.
  4. Note the port: 6543.

2. The Connection String

Your connection string must point to the pooler, not the direct DB.

Direct (Do not use for Edge Functions): postgres://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-ID].supabase.co:5432/postgres

Pooled (Use this): postgres://postgres.[YOUR-PROJECT-ID]:[YOUR-PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres?pgbouncer=true

Note: The user format changes to user.project_ref in the pooler string.

3. The Code (Deno / TypeScript)

Here is a robust pattern for connecting inside an Edge Function.

// supabase/functions/process-data/index.ts
import { serve } from "https://deno.land/std@0.208.0/http/server.ts";
import postgres from "https://deno.land/x/postgresjs@v3.4.3/mod.js";

// Construct the connection string dynamically or pull from env
// Ensure you are using the POOLER connection string (Port 6543)
const DB_URL = Deno.env.get("SUPABASE_DB_URL_POOLER")!;

if (!DB_URL) {
  throw new Error("Missing SUPABASE_DB_URL_POOLER environment variable");
}

// Initialize the SQL client outside the handler to take advantage of
// container reuse (warm starts).
const sql = postgres(DB_URL, {
  // CRITICAL: Prepare must be false for Transaction Mode
  prepare: false, 
  // Idle timeout ensures connections don't hang if the lambda freezes
  idle_timeout: 5, 
  // Limit connections per lambda instance to avoid local exhaustion
  max: 1 
});

serve(async (req) => {
  try {
    // 1. Parse Input
    const { userId } = await req.json();

    // 2. Execute Query
    // Because we are in Transaction Mode, this query borrows a connection
    // strictly for the duration of this execution.
    const result = await sql`
      SELECT id, email, created_at 
      FROM auth.users 
      WHERE id = ${userId}
    `;

    return new Response(
      JSON.stringify(result),
      { headers: { "Content-Type": "application/json" } },
    );

  } catch (error) {
    console.error("Database error:", error);
    return new Response(
      JSON.stringify({ error: "Internal Server Error" }),
      { status: 500, headers: { "Content-Type": "application/json" } },
    );
  }
});

Deep Dive: The prepare: false Requirement

In the code above, you will notice prepare: false. This is technically critical and often overlooked.

Modern SQL drivers attempt to optimize performance using Prepared Statements.

  1. Driver sends SQL template: SELECT * FROM users WHERE id = $1.
  2. DB parses, optimizes, and caches the plan, returning a statement ID.
  3. Driver executes statement ID with variables.

The Problem: Prepared statements are bound to a specific backend connection (Session state). In Transaction Mode, your first request (Prepare) might go to Backend Process A, and your second request (Execute) might get routed to Backend Process B. Process B has no idea about the statement prepared on Process A.

This results in the error: prepared statement "s1" does not exist.

By setting prepare: false, you force the driver to send the raw SQL query every time (Simple Query Protocol). While theoretically slower due to re-parsing, the network latency dominates serverless execution time anyway, making the difference negligible compared to the stability gains.

Handling ORMs (Prisma & Drizzle)

If you are using an ORM within your functions, you must configure them to disable prepared statements or strictly adhere to PgBouncer compatibility.

Drizzle ORM

If using Drizzle with postgres.js, pass the { prepare: false } option to the driver client, exactly as shown in the code snippet above.

Prisma

Prisma requires specific connection string parameters to work with Supavisor in Transaction Mode.

  1. Connection String: Append ?pgbouncer=true to the URL.
  2. Schema: Add directUrl for migrations (migrations must happen on port 5432).
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL") // Port 6543 (Transaction Mode)
  directUrl = env("DIRECT_URL")   // Port 5432 (Session Mode)
}

Prisma's query engine handles the prepared statement incompatibility internally when pgbouncer=true is set.

Common Pitfalls and Edge Cases

1. Schema Migrations fail on Port 6543

Issue: You try to run supabase db push or a migration script using the pooler URL. Reason: Transaction mode does not support the complex session-state locking required for DDL (Data Definition Language) changes. Fix: Always use the Direct Connection (Port 5432) for CI/CD pipelines and migrations.

2. LISTEN/NOTIFY features

Issue: You want to use PostgreSQL LISTEN to react to database changes in real-time. Reason: LISTEN requires a persistent connection to receive events. Transaction mode recycles connections immediately. Fix: You cannot use Transaction Mode for listeners. You must use a dedicated long-lived server (not serverless) connected to Port 5432 or Session Mode.

3. "SET LOCAL" parameters

Issue: You try to use SET LOCAL search_path or set RLS claims via SQL set_config. Reason: Since the connection is returned to the pool immediately after the transaction commits, these settings can be lost or, worse, leak to the next user who borrows that connection. Fix: If you need to assume a user identity for RLS in a direct connection, wrap the entire operation in a single BEGIN ... COMMIT block.

Conclusion

Scaling Supabase Edge Functions requires shifting your mental model from "persistent connections" to "stateless transactions."

  1. Use Port 6543 for all Edge Functions.
  2. Set the pooler to Transaction Mode.
  3. Disable Prepared Statements in your SQL driver.
  4. Keep migrations on Port 5432.

By implementing these changes, you decouple your frontend traffic spikes from your database's physical connection limits, allowing your application to handle massive concurrency without timeouts.

Labels: