Supavisor

https://github.com/supabase/supavisor
Admin
Supavisor is a scalable, cloud-native Postgres connection pooler capable of proxying millions of...
Tokens:123,129
Snippets:844
Trust Score:10
License:Apache-2.0
Update:1 week ago
Show doc for...
Tokens:
Raw
### Start and migrate Supavisor database

Source: https://github.com/supabase/supavisor/blob/main/docs/development/setup.md

Starts the Supavisor database and runs migrations to store tenant information.

```bash
mise run db:start && mise run db:migrate
```

--------------------------------

### Install toolchain and list tasks with mise

Source: https://github.com/supabase/supavisor/blob/main/docs/development/setup.md

Installs the toolchain pinned in `.tool-versions` and lists available tasks defined in `mise.toml`.

```bash
mise install
mise tasks
```

--------------------------------

### Launch Supavisor application

Source: https://github.com/supabase/supavisor/wiki/Installation-and-Usage

Starts the Supavisor application using the Makefile dev target.

```bash
make dev
```

--------------------------------

### Start PostgreSQL with docker-compose

Source: https://github.com/supabase/supavisor/blob/main/docs/development/installation.md

Starts a PostgreSQL 14 Docker container on port 6432 for Supavisor's tenant data. Requires the docker-compose.db.yml file. The supavisor schema is created by dev/postgres/00-setup.sql.

```bash
docker-compose -f ./docker-compose.db.yml up
```

--------------------------------

### Launch Fly.io app

Source: https://github.com/supabase/supavisor/wiki/Installation-and-Usage

Starts the Fly.io launch process. The 'toml.yaml' file should be in the 'deploy/fly' directory.

```bash
fly launch
```

--------------------------------

### Get dependencies and run migrations

Source: https://github.com/supabase/supavisor/blob/main/docs/development/installation.md

Fetches Elixir dependencies and applies database migrations to the _supavisor prefix. Ensure the supavisor schema exists before running migrations.

```bash
mix deps.get && mix ecto.migrate --prefix _supavisor --log-migrator-sql
```

--------------------------------

### Connect with external_id in options parameters

Source: https://github.com/supabase/supavisor/blob/main/docs/connecting/overview.md

Include the external_id as the 'reference' in the options parameters. The example uses external_id 'dev_tenant'.

```bash
psql postgresql://postgres:postgres@localhost:6543/postgres&options=reference%3Ddev_tenant
```

--------------------------------

### Set app secrets with fly secrets set

Source: https://github.com/supabase/supavisor/blob/main/docs/deployment/fly.md

Sets required secrets for the app. Replace the example values with your actual secrets.

```bash
fly secrets set DATABASE_URL="ecto://postgres:postgres@localhost:6432/postgres" \
VAULT_ENC_KEY="some_vault_secret" \
API_JWT_SECRET="some_api_secret" \
METRICS_JWT_SECRET="some_metrics_secret" \
SECRET_KEY_BASE="some_kb_secret"
```

--------------------------------

### Connect with external_id in username

Source: https://github.com/supabase/supavisor/blob/main/docs/connecting/overview.md

Include the external_id after a dot in the username. The example uses external_id 'dev_tenant'.

```bash
psql postgresql://postgres.dev_tenant:postgres@localhost:6543/postgres
```

--------------------------------

### Capture eFlambé trace with output directory

Source: https://github.com/supabase/supavisor/blob/main/docs/development/profiling.md

Run this Elixir snippet inside an IEx session to start tracing a function. It creates a unique directory under ./tmp and captures traces for Supavisor.ClientHandler.handle_event/4. The output_directory option stores traces in that directory for later viewing in Speedscope.

```elixir
dir = "./tmp/capture-#{DateTime.utc_now()}"; File.mkdir_p!(dir); :eflambe.capture({Supavisor.ClientHandler, :handle_event, 4}, 0, [output_directory: dir])
```

--------------------------------

### Connect with external_id via SNI

Source: https://github.com/supabase/supavisor/blob/main/docs/connecting/overview.md

Use the subdomain of the SNI from the TLS handshake as the external_id. The example uses 'dev_tenant'.

```text
dev_tenant.supabase.co
```

--------------------------------

### Build and serve documentation locally with mkdocs serve

Source: https://github.com/supabase/supavisor/blob/main/docs/development/docs.md

Run mkdocs serve to build and serve the documentation locally.

```bash
mkdocs serve
```

--------------------------------

### Launch Fly.io app with fly launch

Source: https://github.com/supabase/supavisor/blob/main/docs/deployment/fly.md

Initializes a new Fly.io app. When prompted, answer 'yes' to copy the configuration to the new app.

```bash
fly launch
```

```bash
Would you like to copy its configuration to the new app? (y/N)
```

--------------------------------

### Answer Fly.io configuration prompt

Source: https://github.com/supabase/supavisor/wiki/Installation-and-Usage

When prompted, answer 'yes' to copy the configuration to the new app.

```bash
Would you like to copy its configuration to the new app? (y/N)
```

--------------------------------

### Run Supavisor application with mise

Source: https://github.com/supabase/supavisor/blob/main/docs/development/setup.md

Launches the Supavisor application using the `dev` task.

```bash
mise run dev
```

--------------------------------

### pgbench on Supavisor (pool size 60, no logs)

Source: https://github.com/supabase/supavisor/blob/main/README.md

Runs pgbench against Supavisor with pool size 60 and no logs. The output shows 10000 transactions processed, 0 failed, latency average 528.463 ms, and tps 189.228103.

```bash
PGPASSWORD=postgres pgbench -M extended --transactions 100 --jobs 10 --client 100 -h localhost -p 7654 -U postgres.localhost postgres
pgbench (15.2, server 14.6 (Debian 14.6-1.pgdg110+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 10
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 528.463 ms
initial connection time = 178.591 ms
tps = 189.228103 (without initial connection time)
```

--------------------------------

### Deploy app with fly deploy

Source: https://github.com/supabase/supavisor/blob/main/docs/deployment/fly.md

Deploys the app on Fly.io.

```bash
fly deploy
```

--------------------------------

### Verify Supavisor connections

Source: https://github.com/supabase/supavisor/blob/main/docs/migrating/pgbouncer.md

Run this SQL to verify Supavisor is using the expected number of connections based on default_pool_size.

```sql
SELECT
  COUNT(*) as count,
  usename,
  application_name
FROM pg_stat_activity
WHERE application_name ILIKE '%Supavisor%'
GROUP BY
  usename,
  application_name
ORDER BY application_name DESC;
```

--------------------------------

### Edit documentation nav in /mkdocs.yaml

Source: https://github.com/supabase/supavisor/blob/main/docs/development/docs.md

The navigation configuration is in /mkdocs.yaml.

```text
/mkdocs.yaml
```

--------------------------------

### Check used connections

Source: https://github.com/supabase/supavisor/blob/main/docs/migrating/pgbouncer.md

Run this SQL to see how many connections are currently in use.

```sql
select count(*) from pg_stat_activity;
```

--------------------------------

### Production documentation GitHub Action workflow

Source: https://github.com/supabase/supavisor/blob/main/docs/development/docs.md

Production documentation is built on merge into main using the GitHub Action at /.github/workflows/docs.yml.

```text
/.github/workflows/docs.yml
```

--------------------------------

### pgbench on PgBouncer (transaction mode, pool size 60)

Source: https://github.com/supabase/supavisor/blob/main/README.md

Runs pgbench against PgBouncer in transaction mode with pool size 60. The output shows 10000 transactions processed, 0 failed, latency average 510.310 ms, and tps 195.959361.

```bash
PGPASSWORD=postgres pgbench -M extended --transactions 100 --jobs 10 --client 100 -h localhost -p 6452 -U postgres postgres
pgbench (15.2, server 14.6 (Debian 14.6-1.pgdg110+1))
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: extended
number of clients: 100
number of threads: 10
maximum number of tries: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
number of failed transactions: 0 (0.000%)
latency average = 510.310 ms
initial connection time = 31.388 ms
tps = 195.959361 (without initial connection time)
```

--------------------------------

### Add/update tenant with full options via curl

Source: https://github.com/supabase/supavisor/blob/main/docs/development/setup.md

Adds or updates a tenant with all available configuration options. Includes comments showing allowed values for `ip_version`, `require_user`, `upstream_ssl`, `enforce_ssl`, `upstream_verify`, and `upstream_tls_ca`. Uses the API endpoint `/api/tenants/dev_tenant`.

```bash
curl -X PUT \
  'http://localhost:4000/api/tenants/dev_tenant' \
  --header 'Accept: application/json' \
  --header 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJvbGUiOiJhbm9uIiwiaWF0IjoxNjQ1MTkyODI0LCJleHAiOjE5NjA3Njg4MjR9.M9jrxyvPLkUxWgOYSf5dNdJ8v_eRrq810ShFRT8N-6M' \
  --header 'Content-Type: application/json' \
  --data-raw '{
  "tenant": {
    "db_host": "localhost",
    "db_port": 6432,
    "db_database": "postgres",
    "ip_version": "auto", // "auto" | "v4" | "v6"
    "require_user": true, // true | false
    "upstream_ssl": true, // true | false,
    "enforce_ssl": false, // true | false,
    "upstream_verify": "peer", // "none" | "peer"
    "upstream_tls_ca": "-----BEGIN CERTIFICATE-----\nblalblalblablalblalblaba\n-----END CERTIFICATE-----\n", // "",
    "default_max_clients": 200,
    "default_pool_size": 15,
    "users": [
      {
        "db_user": "postgres",
        "db_password": "postgres",
        "mode_type": "transaction",
        "pool_checkout_timeout": 100,
        "pool_size": 10
      }
    ]
  }
}'
```

--------------------------------

### Check Postgres max_connections

Source: https://github.com/supabase/supavisor/blob/main/docs/migrating/pgbouncer.md

Run this SQL to see the current max_connections setting on your Postgres database.

```sql
show max_connections;
```

--------------------------------

### Simple auth_query for Supavisor

Source: https://github.com/supabase/supavisor/blob/main/docs/connecting/authentication.md

A simple authentication query that returns the role name and password for a given role. It is used when the client connection user is not found for a tenant, and the tenant has an auth_query set.

```sql
SELECT rolname, rolpassword FROM pg_authid WHERE rolname=$1
```

--------------------------------

### Create auth function for Supavisor

Source: https://github.com/supabase/supavisor/blob/main/docs/connecting/authentication.md

Creates a dedicated Supavisor user and a function that returns a username and password for a given user. The function is defined as SECURITY DEFINER and its execution is restricted to the supavisor user.

```sql
CREATE USER supavisor;

REVOKE ALL PRIVILEGES ON SCHEMA public FROM supavisor;

CREATE SCHEMA supavisor AUTHORIZATION supavisor;

CREATE OR REPLACE FUNCTION supavisor.get_auth(p_usename TEXT)
RETURNS TABLE(username TEXT, password TEXT) AS
$$
BEGIN
    RAISE WARNING 'Supavisor auth request: %', p_usename;

    RETURN QUERY
    SELECT usename::TEXT, passwd::TEXT FROM pg_catalog.pg_shadow
    WHERE usename = p_usename;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION supavisor.get_auth(p_usename TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION supavisor.get_auth(p_usename TEXT) TO supavisor;
```

--------------------------------

### Update auth_query to use function

Source: https://github.com/supabase/supavisor/blob/main/docs/connecting/authentication.md

Updates the tenant's auth_query to use the created function. This query is used to match client connection credentials.

```sql
SELECT * FROM supavisor.get_auth($1)
```

--------------------------------

### Add tenant via curl PUT request

Source: https://github.com/supabase/supavisor/blob/main/docs/development/setup.md

Adds the `dev_tenant` with credentials to the database. Uses the API endpoint `/api/tenants/dev_tenant` with a bearer token. The request body includes tenant configuration such as db_host, db_port, and user credentials.

```bash
curl  -X PUT \
  'http://localhost:4000/api/tenants/dev_tenant' \
  --header 'Accept: */*' \
  --header 'User-Agent: Thunder Client (https://www.thunderclient.com)' \
  --header 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJvbGUiOiJhbm9uIiwiaWF0IjoxNjQ1MTkyODI0LCJleHAiOjE5NjA3Njg4MjR9.M9jrxyvPLkUxWgOYSf5dNdJ8v_eRrq810ShFRT8N-6M' \
  --header 'Content-Type: application/json' \
  --data-raw '{
  "tenant": {
    "db_host": "localhost",
    "db_port": 6432,
    "db_database": "postgres",
    "ip_version": "auto",
    "enforce_ssl": false,
    "require_user": false,
    "auth_query": "SELECT rolname, rolpassword FROM pg_authid WHERE rolname=$1;",
    "users": [
      {
        "db_user": "postgres",
        "db_password": "postgres",
        "pool_size": 20,
        "mode_type": "transaction",
        "is_manager": true
      }
    ]
  }
}'
```

--------------------------------

### Metrics Endpoints

Source: https://github.com/supabase/supavisor/blob/main/docs/monitoring/metrics.md

Documentation for the metrics endpoints exposed by Supavisor, including the main /metrics endpoint and the per-tenant filtered endpoint.

```APIDOC
## GET /metrics

### Description
Retrieves metrics in the Prometheus format. The endpoint is secured using Bearer authentication with a JWT generated using the `METRICS_JWT_SECRET` environment variable. When a node receives a request, it polls all nodes in the cluster, accumulates their metrics, and appends service tags such as region and host.

### Method
GET

### Endpoint
/metrics

### Authentication
Bearer token (JWT) - Required - A JSON Web Token generated using the HS256 algorithm and the `METRICS_JWT_SECRET` environment variable as the secret key.

### Response
#### Success Response (200)
Metrics in Prometheus format, including application, BEAM, Phoenix, Ecto, and system monitoring metrics (CPU utilization, RAM usage, load average, disk space usage, network counters, TCP socket usage).

#### Response Example
```
# HELP beam_utilization CPU utilization
# TYPE beam_utilization gauge
beam_utilization 0.25
```

### Error Handling
- 401 Unauthorized - Invalid or missing JWT token.
```

```APIDOC
## GET /metrics/:external_id

### Description
Retrieves metrics filtered for a specific tenant, identified by its `external_id`. The endpoint is secured using Bearer authentication with a JWT generated using the `METRICS_JWT_SECRET` environment variable. Metrics are tagged with the tenant `external_id` to allow drill-down per tenant.

### Method
GET

### Endpoint
/metrics/:external_id

### Path Parameters
- **external_id** (string) - Required - The external ID of the tenant for which to retrieve metrics.

### Authentication
Bearer token (JWT) - Required - A JSON Web Token generated using the HS256 algorithm and the `METRICS_JWT_SECRET` environment variable as the secret key.

### Response
#### Success Response (200)
Metrics in Prometheus format for the specified tenant, including pool checkout queue time, number of connected clients, query duration and query counts, and network usage for client sockets and database sockets.

#### Response Example
```
# HELP tenant_pool_checkout_queue_time Pool checkout queue time
# TYPE tenant_pool_checkout_queue_time gauge
tenant_pool_checkout_queue_time{tenant="tenant1"} 0.5
```

### Error Handling
- 401 Unauthorized - Invalid or missing JWT token.
```

--------------------------------

### Edit documentation in /docs

Source: https://github.com/supabase/supavisor/blob/main/docs/development/docs.md

Documentation files are stored in the /docs directory.

```text
/docs
```

--------------------------------

### Allocate IPv4 address with fly ips allocate-v4

Source: https://github.com/supabase/supavisor/blob/main/docs/deployment/fly.md

Reserves an IPv4 address for the app, required because the pooler uses an additional port (7654) for the PostgreSQL protocol.

```bash
fly ips allocate-v4
```

--------------------------------

### Load test query: SELECT from VALUES

Source: https://github.com/supabase/supavisor/blob/main/README.md

SQL query used in the load test to generate a small result set. It selects three rows from a VALUES list.

```sql
SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num, letter);
```

--------------------------------

### Delete tenant via curl DELETE request

Source: https://github.com/supabase/supavisor/blob/main/docs/development/setup.md

Deletes the `dev_tenant` using the API endpoint `/api/tenants/dev_tenant` with a bearer token.

```bash
curl -X DELETE \
  'http://localhost:4000/api/tenants/dev_tenant' \
  --header 'Accept: application/json' \
  --header 'Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJvbGUiOiJhbm9uIiwiaWF0IjoxNjQ1MTkyODI0LCJleHAiOjE5NjA3Njg4MjR9.M9jrxyvPLkUxWgOYSf5dNdJ8v_eRrq810ShFRT8N-6M'
```
Tokens:
Raw