Using Supavisor as a Connection Pool for PostgreSQL

Introduction

Connection Pool

• A Connection Pool is a mechanism to manage and reuse database connections.
• Without a connection pool, when an application sends a request to the database, it must undergo a successful TCP Handshake before executing the query and after receiving results, it must disconnect to release resources
• You can see the limitations of the traditional approach which is when there is a large volume of connections to the database, every request must experience all the steps mentioned above, greatly impacting system performance
• With a connection pool, right from startup, it pre-creates a fixed amount of connections to the database and keeps them alive, so when a request comes in, it only needs to take a connection from the pool and can use it immediately to execute the query and after completion, it just returns the connection to the pool for other applications to use instead of disconnecting

Use cases

• Web/API applications with medium to large traffic: Any application serving multiple users simultaneously like web servers needs a Pool to ensure response speed and low latency.
• When the Database has resource constraints (RAM/CPU): Database management systems like PostgreSQL consume a lot of RAM for each new connection (about 2-10MB/connection). A Pool helps you strictly limit the maximum number of connections to protect the DB from running out of RAM, which leads to crashes during Traffic Spikes.
• Systems running Microservices or Serverless architecture: Many small services connecting to a single DB simultaneously will cause the number of connections to skyrocket, making a connection pooler necessary as an intermediate proxy to aggregate requests.

Supavisor

• Supavisor is a high-performance Cloud Connection Pooler solution with an open-source version for self-hosting, developed by Supabase and written in the Elixir language. It is designed to handle millions of concurrent connections to a PostgreSQL database with extremely low latency.
• Outstanding advantages of Supavisor include powerful scalability, server resource savings thanks to the asynchronous routing model of Elixir/Erlang, support for both Transaction Mode and Session Mode and built-in flexible multi-tenancy management capabilities.

Detail

First, create docker-compose.yml as follows

services:
  postgres-db:
    image: postgres:alpine
    container_name: postgres-db
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    ports:
      - "5432:5432"
    volumes:
      - ./pg_data:/var/lib/postgresql

  pgadmin4:
    image: dpage/pgadmin4
    container_name: pgadmin4
    ports:
      - 8080:80
    volumes:
      - ./pgadmin_data:/var/lib/pgadmin
    environment:
      - PGADMIN_DEFAULT_EMAIL=${PGADMIN_DEFAULT_EMAIL}
      - PGADMIN_DEFAULT_PASSWORD=${PGADMIN_DEFAULT_PASSWORD}
    depends_on:
      - postgres-db

  supavisor:
    image: supabase/supavisor:2.9.7
    container_name: supavisor-pooler
    ports:
      - "4000:4000"
      - "6543:6543"
      - "5452:5452"
    environment:
      PORT: 4000
      PROXY_PORT_TRANSACTION: 6543
      PROXY_PORT_SESSION: 5452
      
      DATABASE_URL: "ecto://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres-db:5432/${POSTGRES_DB}"
      
      SECRET_KEY_BASE: ${SECRET_KEY_BASE}
      VAULT_ENC_KEY: ${VAULT_ENC_KEY}
      API_JWT_SECRET: ${API_JWT_SECRET}
    command: sh -c "/app/bin/migrate && /app/bin/server"
    depends_on:
      - postgres-db

Above, I have created 3 services: PostgreSQL, pgAdmin and Supavisor The configuration for PostgreSQL and pgAdmin is quite straightforward, I will explain the values used in Supavisor

• Port 4000: used for REST API
• Port 5452: used for Session Mode, this occupies an actual physical connection, operating similarly to connecting directly to PostgreSQL, used for critical operations that need to maintain important, long-term connections such as migration
• Port 6543: used for Transaction Mode, using this mode means it only creates a connection when a query is needed and will disconnect immediately upon receiving results, used for most common query types
• DATABASE_URL: is the connection string to the PostgreSQL database
• SECRET_KEY_BASE: you can generate this arbitrarily, but it must be 64 characters
• VAULT_ENC_KEY: generate arbitrarily, 32 characters
• API_JWT_SECRET: generate arbitrarily, will be used to call the tenant management api

Create a .env file with the following content

POSTGRES_USER = <POSTGRES_USER>
POSTGRES_PASSWORD = <POSTGRES_PASSWORD>
POSTGRES_DB = <POSTGRES_DB>

PGADMIN_DEFAULT_EMAIL = <PGADMIN_DEFAULT_EMAIL>
PGADMIN_DEFAULT_PASSWORD = <PGADMIN_DEFAULT_PASSWORD>

SECRET_KEY_BASE = <SECRET_KEY_BASE>
VAULT_ENC_KEY = <VAULT_ENC_KEY>
API_JWT_SECRET = <API_JWT_SECRET>

DATABASE_URL = postgresql://{username}.{external_id}:{password}@{postgres-url}:6543/{postgres-database-name}?pgbouncer=true&connection_limit=10

DIRECT_URL = {username}.{external_id}:{password}@{postgres-url}:5452/{postgres-database-name}?pgbouncer=true&connection_limit=10

• You can replace the corresponding values as desired, note that DATABASE_URL uses port 6543 for Transaction Mode for regular queries, while DIRECT_URL uses port 5452 for Session Mode for important operations like schema synchronization and migration
• connection_limit: this is the maximum number of connections that the application will establish concurrently to Supavisor

After starting the services

docker compose up -d

You can access the Swagger API of Supavisor to view information about supported APIs at port 4000

To get a token for use, visit https://www.jwt.io and create a JWT Signature based on the API_JWT_SECRET configured above and the JSON payload as follows

{
  "role": "admin",
  "iss": "supavisor"
}

Next, create a tenant with the following payload and make sure to change the values to suit your setup:

{
  "tenant": {
    "allow_list": [
      "0.0.0.0/0",
      "::/0"
    ],
    "db_database": "postgres",
    "db_host": <db_host>,
    "db_port": 5432,
    "enforce_ssl": false,
    "ip_version": "auto",
    "require_user": true,
    "users": [
      {
        "db_password": <db_password>,
        "db_user": <db_user>,
        "max_clients": 25000,
        "mode_type": "transaction",
        "pool_checkout_timeout": 1000,
        "pool_size": 10
      }
    ]
  }
}

Check results

Then you need to replace the created external_id value into the corresponding .env file

Create a schema.prisma file, with a schema as simple as follows

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
}

model products {
  id                Int    @id @default(autoincrement())
  name              String
  description       String
}

Create a prisma.config.ts file to select the corresponding connection string for each query type, if the CLI is related to Prisma then use DIRECT_URL, otherwise use DATABASE_URL

import {defineConfig, env} from '@prisma/config'
import * as dotenv from 'dotenv'

dotenv.config()

const isPrismaCLI = process.argv.some(arg => arg.includes('prisma'))

export default defineConfig({
  datasource: {
    url: isPrismaCLI ? env('DIRECT_URL') : env('DATABASE_URL'),
  },
})

Create an example for the products.controller.ts file as follows

import {Controller, Get} from '@nestjs/common'
import {PrismaService} from 'src/service/prisma.service'

@Controller('product')
export class ProductController {
  constructor(private prisma: PrismaService) {}

  @Get()
  async findAll() {
    return this.prisma.products.findMany()
  }
}

Then you can use pgAdmin to check when Supavisor connects to PostgreSQL, it will create an additional schema named _supavisor

SELECT * FROM _supavisor.tenants

SHOW max_connections;

SELECT 
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
    count(*) AS current_connections,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') - count(*) AS remaining_connections
FROM pg_stat_activity;

• Query 1: View existing tenants, which were created by calling the api as mentioned above
• Query 2: View the maximum number of connections that PostgreSQL supports, this is the physical connection that the database can handle
• Query 3: View detailed information about the maximum connections and the remaining number of connections available for use

Conclusion

Thus, through the content of this article, you need to pay attention to the following values

• connection_limit configured in the app, this is a parameter in the connection string of Prisma
  • Represents the maximum number of connections that the app will create to Supavisor
  • If this number is exceeded, they will be placed in a queue, waiting until there is a result from an already processed request before sending the next request
• Supavisor:
  • max_clients: maximum number of connections that Supervisor allows apps to connect to at the same time, Supervisor will reject other connections if this number is exceeded
  • pool_size: number of connections from Supervisor to the database, Supervisor will convert virtual connections (max_clients) from apps into physical connections (pool_size) to Postgres
• max_connections is the configuration of Postgres, this is the maximum connection limit that Postgres can handle.

Happy coding!

See more articles here.