Supavisor 1.0: a scalable connection pooler for Postgres

2023-12-13

7 minute read

After launching Supavisor in August, we've successfully migrated all projects on the platform. Every new Supabase project launched now gets a Supavisor connection string to use for connection pooling.

Supavisor 1.0 symbolizes production readiness and comes with many bug fixes. It includes three important features:

  1. query load balancing
  2. named prepared statement support
  3. query cancelation

What is connection pooling?

Supavisor is built with Elixir. Since the Dashbit team have been helping with the development we invited Jose Valim, the creator of Elixir, to explain connection pooling, OTP, and why Elixir is a great fit for a connection pooler:

SQL parsing with Rust

To implement the latest set of features, we now parse all SQL statements from connected clients.

Supavisor, developed in Elixir, supports high concurrency and rapid I/O. Elixir doesn't have great performance for parsing, but it provides excellent interop with Rust via Rustler. For efficient SQL parsing, we use pg_query.rs.

Load Balancing

When set up with a Postgres cluster, Supavisor load-balances read requests between the primary server and its replicas. It randomly distributes these read requests across the entire Postgres cluster.

Supavisor targets write operations to the primary automatically by probing read replicas until it hits the primary with a successful write, similar to libpq. The trade-off here is that writes may take a few milliseconds longer to complete in favor of zero additional client-side complexity. This write strategy also makes transparent primary failovers painless because detecting the primary for writes is automatic.

Read-after-writes

With automatic primary detection, it's easy to guarantee read-after-writes from the same client by wrapping the read and write in a transaction.

Future work is planned to allow custom server targeting with SQL statements such as SET SERVER 'primary' to let clients guarantee read-after-writes outside of transactions or across clients.

Named Prepared Statements

Many clients use named prepared statements when generating parameterized SQL. During statement preparation Postgres parses, plans, and optimizes queries.

If a client can create named prepared statements, then such a client can re-use these query plans and simply submit parameters for them.

The problem with named prepared statements and pooling in the transaction mode is that statements are not shared across Postgres backends (connections). Each client connection must issue prepared statements for each query they will run.

Supavisor now supports named prepared statements. Supavisor parses each query and identifies PREPARE statements. When a PREPARE statement is received on one connection, it is broadcast across all connections. This approach allows every client to access named prepared statements that have been issued by other connections. This adds a slight increase in memory overhead when duplicating query plans for each Postgres connection but should come with significant throughput gains.

Query Cancelation

With 1.0 we get query official cancelation as well. If you're in psql typing Ctrl+C will actually cancel your query now.

Especially useful if you accidentally run a heavy query!

Platform Updates

For the Supavisor rollout, we maintained consistent pooling settings between PgBouncer and Supavisor.

Now, we're raising the client connection limit for smaller projects in Supavisor. Here are the updated default configurations:

Database Sizedefault_pool_sizemax_connectionsdefault_max_clients
Micro1560200
Small1590400 (previously 200)
Medium15120600 (previously 200)
Large20160800 (previously 300)
XL202401,000 (previously 700)
2XL253801,500
4XL324803,000
8XL644906,000
12XL965009,000
16XL12850012,000

In this table:

  • default_pool_size: the number of connections from Supavisor to your database (configurable)
  • max_connections: the max number of direct connections Postgres is configured to allow (configurable)
  • default_max_clients : the maximum number of clients allowed to connect to Supavisor (upgrade to increase)

IPv4 Deprecation

Effective February 1, 2024 AWS is charging for all allocated IPV4 addresses. Rather than passing that fee onto our customers Supavisor can mediate connections from IPv4 to IPv6.

If you're using the PgBouncer connection string and haven't migrated to the new Supavisor connection string make sure to do this before January 15th, 2024.

Getting started

If you're using the Supabase platform, you can already access the pooler URL in your database settings.

If you're looking to self-host Supavisor, check out GitHub repository and documentation.

You can expect Supavisor 1.0 to hit the platform next week along with the new pooling configuration changes. If you've set a custom pooler configuration, or we've set one for you, your settings won't change.

Share this article

Build in a weekend, scale to millions