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:
- query load balancing
- named prepared statement support
- query cancelation
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:
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.
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.
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.
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!
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 Size | default_pool_size | max_connections | default_max_clients |
|---|---|---|---|
| Micro | 15 | 60 | 200 |
| Small | 15 | 90 | 400 (previously 200) |
| Medium | 15 | 120 | 600 (previously 200) |
| Large | 20 | 160 | 800 (previously 300) |
| XL | 20 | 240 | 1,000 (previously 700) |
| 2XL | 25 | 380 | 1,500 |
| 4XL | 32 | 480 | 3,000 |
| 8XL | 64 | 490 | 6,000 |
| 12XL | 96 | 500 | 9,000 |
| 16XL | 128 | 500 | 12,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.
If you're using the Supabase platform, you can already access the pooler URL in your project connect page.
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.