• Stars
    star
    1,545
  • Rank 28,867 (Top 0.6 %)
  • Language
    Elixir
  • License
    Apache License 2.0
  • Created about 1 year ago
  • Updated 9 days ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

A cloud-native, multi-tenant Postgres connection pooler.

Supavisor

Supavisor - Postgres connection pooler

Overview

Supavisor is a scalable, cloud-native Postgres connection pooler. A Supavisor cluster is capable of proxying millions of Postgres end-client connections into a stateful pool of native Postgres database connections.

For database managers, Supavisor simplifies the task of managing Postgres clusters by providing easy configuration of highly available Postgres clusters (todo).

Motivation

We have several goals with Supavisor:

  • Zero-downtime scaling: we want to scale Postgres server compute with zero-downtime. To do this, we need an external Pooler that can buffer and re-route requests while the resizing operation is in progress.
  • Handling modern connection demands: We need a Pooler that can absorb millions of connections. We often see developers connecting to Postgres from Serverless environments, and so we also need something that works with both TCP and HTTP protocols.
  • Efficiency: Our customers pay for database processing power, and our goal is to maximize their database capacity. While PgBouncer is resource-efficient, it still consumes some resources on the database instance. By moving connection pooling to a dedicated cluster adjacent to tenant databases, we can free up additional resources to better serve customer queries.

Architecture

Supavisor was designed to work in a cloud computing environment as a highly available cluster of nodes. Tenant configuration is stored in a highly available Postgres database. Configuration is loaded from the Supavisor database when a tenant connection pool is initiated.

Connection pools are dynamic. When a tenant client connects to the Supavisor cluster the tenant pool is started and all connections to the tenant database are established. The process ID of the new tenant pool is then distributed to all nodes of the cluster and stored in an in-memory key-value store. Subsequent tenant client connections live on the inbound node but connection data is proxied from the pool node to the client connection node as needed.

Because the count of Postgres connections is constrained only one tenant connection pool should be alive in a Supavisor cluster. In the case of two simultaneous client connections starting a pool, as the pool process IDs are distributed across the cluster, eventually one of those pools is gracefully shutdown.

The dynamic nature of tenant database connection pools enables high availability in the event of node outages. Pool processes are monitored by each node. If a node goes down that process ID is removed from the cluster. Tenant clients will then start a new pool automatically as they reconnect to the cluster.

This design enables blue-green or rolling deployments as upgrades require. A single VPC / multiple availability zone topologies is possible and can provide for greater redundancy when load balancing queries across read replicas are supported (todo).

Docs

Features

  • Fast
    • Within 90% throughput as compared to PgBouncer running pgbench locally
  • Scalable
    • 1 million Postgres connections on a cluster
    • 250_000 idle connections on a single 16 core node with 64GB of ram
  • Multi-tenant
    • Connect to multiple different Postgres instances/clusters
  • Single-tenant
    • Easy drop-in replacement for PgBouncer
  • Pool mode support per tenant
    • Transaction
  • Cloud-native
    • Cluster-able
    • Resilient during cluster resizing
    • Supports rolling and blue/green deployment strategies
    • NOT run in a serverless environment
    • NOT dependant on Kubernetes
  • Observable
    • Easily understand throughput by tenant, tenant database or individual connection
    • Prometheus /metrics endpoint
  • Manageable
    • OpenAPI spec at /api/openapi
    • SwaggerUI at /swaggerui
  • Highly available
    • When deployed as a Supavisor cluster and a node dies connection pools should be quickly spun up or already available on other nodes when clients reconnect
  • Connection buffering
    • Brief connection buffering for transparent database restarts or failovers

Future Work

  • Load balancing
    • Queries can be load balanced across read-replicas
    • Load balancing is independant of Postgres high-availability management (see below)
  • Query caching
    • Query results are optionally cached in the pool cluster and returned before hitting the tenant database
  • Session pooling
    • Like PgBouncer
  • Multi-protocol Postgres query interface
    • Postgres binary
    • HTTPS
    • Websocket
  • Postgres high-availability management
    • Primary database election on primary failure
    • Health checks
    • Push button read-replica configuration
  • Config as code
    • Not only for the supavisor cluster but tenant databases and tenant database clusters as well
    • Pulumi / terraform support

Benchmarks

Local Benchmarks

  • Running pgbench on PgBouncer (transaction mode/pool size 60)
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)
  • Running pgbench on Supavisor (pool size 60, no logs)
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)

Load Test

Supavisor load test virtual users chart

Supavisor load test qps chart

  • Supavisor two node cluster
    • 64vCPU / 246RAM
    • Ubuntu 22.04.2 aarch64
  • 1_003_200 concurrent client connection
  • 20_000+ QPS
  • 400 tenant Postgres connection
  • select * from (values (1, 'one'), (2, 'two'), (3, 'three')) as t (num,letter);
  • ~50% CPU utilization (pool owner node)
  • 7.8G RAM usage

Acknowledgements

José Valim and the Dashbit team were incredibly helpful in informing the design decisions for Supavisor.

Inspiration

Commercial Inspiration

More Repositories

1

supabase

The open source Firebase alternative.
TypeScript
62,268
star
2

realtime

Broadcast, Presence, and Postgres Changes via WebSockets
Elixir
6,315
star
3

postgres_lsp

A Language Server for Postgres
Rust
3,073
star
4

pg_graphql

GraphQL support for PostgreSQL
Rust
2,672
star
5

supabase-js

An isomorphic Javascript client for Supabase. Query your Supabase database, subscribe to realtime events, upload and download files, browse typescript examples, invoke postgres functions via rpc, invoke supabase edge functions, query pgvector.
TypeScript
2,625
star
6

ui

Supabase UI Library
TypeScript
1,478
star
7

postgres

Unmodified Postgres with some useful plugins
Shell
1,182
star
8

gotrue

A JWT based API for managing users and issuing JWT tokens
Go
989
star
9

pg_jsonschema

PostgreSQL extension providing JSON Schema validation
Rust
907
star
10

postgrest-js

Isomorphic JavaScript client for PostgREST.
TypeScript
865
star
11

auth-helpers

A collection of framework specific Auth utilities for working with Supabase.
TypeScript
849
star
12

postgres-meta

A RESTful API for managing your Postgres. Fetch tables, add roles, and run queries
TypeScript
786
star
13

cli

Supabase CLI. Manage postgres migrations, run Supabase locally, deploy edge functions. Postgres backups. Generating types from your database schema.
Go
729
star
14

supa_audit

Generic Table Auditing
PLpgSQL
607
star
15

supabase-flutter

Flutter integration for Supabase. This package makes it simple for developers to build secure and scalable products.
Dart
589
star
16

storage

S3 compatible object storage service that stores metadata in Postgres
TypeScript
520
star
17

edge-runtime

A server based on Deno runtime, capable of running JavaScript, TypeScript, and WASM services.
TypeScript
487
star
18

stripe-sync-engine

Sync your Stripe account to you Postgres database.
TypeScript
433
star
19

wrappers

Postgres Foreign Data Wrapper development framework in Rust.
Rust
418
star
20

auth-ui

Pre-built Auth UI for React
TypeScript
405
star
21

supabase-dart

A Dart client for Supabase
Dart
402
star
22

pg_crdt

POC CRDT support in Postgres
Rust
370
star
23

dbdev

Database Package Registry for Postgres
PLpgSQL
308
star
24

realtime-js

An isomorphic Javascript client for Supabase Realtime server.
JavaScript
279
star
25

examples-archive

Supabase Examples Archive
TypeScript
265
star
26

gotrue-js

An isomorphic Javascript library for GoTrue.
CSS
263
star
27

pg_netstat

PostgreSQL extension to monitor database network traffic
Rust
244
star
28

grid

A react component to display your Postgresql table data. Used in Supabase Dashboard app.
TypeScript
196
star
29

vecs

Postgres/pgvector Python Client
Python
183
star
30

libcluster_postgres

Postgres strategy for libcluster
Elixir
171
star
31

vault

Extension for storing encrypted secrets in the Vault
PLpgSQL
155
star
32

pg_net

A PostgreSQL extension that enables asynchronous (non-blocking) HTTP/HTTPS requests with SQL
PLpgSQL
155
star
33

postgrest-dart

Dart client for PostgREST
Dart
137
star
34

workflows

Elixir
130
star
35

walrus

Applying RLS to PostgreSQL WAL
PLpgSQL
117
star
36

supabase-grafana

Observability for your Supabase project, using Prometheus/Grafana
Shell
114
star
37

headless-vector-search

Supabase Toolkit to perform vector similarity search on your knowledge base embeddings.
TypeScript
110
star
38

storage-js

JS Client library to interact with Supabase Storage
TypeScript
104
star
39

postgres-deno

A PostgreSQL extension for Deno: run Typescript in PostgreSQL functions and triggers.
103
star
40

realtime-dart

A dart client for Supabase Realtime server.
Dart
85
star
41

setup-cli

A GitHub action for interacting with your Supabase projects using the CLI.
TypeScript
80
star
42

repository.surf

🏄
JavaScript
80
star
43

supabase-ui-web

TypeScript
72
star
44

embeddings-generator

GitHub Action to generate embeddings from the markdown files in your repository.
TypeScript
59
star
45

self-hosted-edge-functions-demo

A demo of how to self-host Supabase Edge Functions on Fly.io
TypeScript
58
star
46

functions-js

TypeScript
53
star
47

supabase-admin-api

API to administer the Supabase server (KPS)
Go
51
star
48

supautils

PostgreSQL extension that secures a cluster on a cloud environment
C
47
star
49

gotrue-dart

A dart client library for GoTrue.
Dart
47
star
50

grafana-agent-fly-example

Deploy a Grafana Agent on Fly to scrape Prometheus metrics from Supabase and send them to Grafana Cloud
Shell
36
star
51

supabase-action-example

TypeScript
36
star
52

functions-relay

API Gateway for Supabase Edge functions
TypeScript
35
star
53

benchmarks-archive

Infrastucture benchmarks
Nix
31
star
54

benchmarks

SCSS
30
star
55

hibp

Go library for HaveIBeenPwned.org's pwned passwords API.
Go
28
star
56

nix-postgres

Experimental port of supabase/postgres to Nix
Nix
24
star
57

storage-dart

Dart client library to interact with Supabase Storage
Dart
22
star
58

livebooks

A collection of Elixir Livebooks for Supabase
Dockerfile
20
star
59

.github

Org-wide default community health files & templates.
11
star
60

auth-elements

Components to add Supabase Auth to any application
TypeScript
11
star
61

rfcs

11
star
62

functions-dart

Dart
8
star
63

terraform-provider-supabase

Go
8
star
64

test-reports

Repository to store test reports data and host reporting in gh-pages
7
star
65

plug_caisson

An Elixir Plug library for handling compressed requests
Elixir
6
star
66

flyswatter

Deploy a global pinger on Fly
Elixir
6
star
67

scoop-bucket

4
star
68

tests

TypeScript
4
star
69

pgextkit

Rust
3
star
70

homebrew-tap

Ruby
3
star
71

fly-preview

TypeScript
3
star
72

shared-types

TypeScript
3
star
73

supa_type

The Missing PostgreSQL Data Types
Nix
3
star
74

test-inspector

Check your test results against the reference run and compare coverage for multiple client libraries
Go
2
star
75

productions

Supabase SynthWave. The best soundtrack to build an app in a weekend and scale to billions.
TypeScript
1
star
76

design-tokens

1
star
77

mailme

A clone of Netlify's mailme package used in Supabase Auth / GoTrue.
Go
1
star