PgBouncer is now available in Supabase

Summary

Serverless JavaScript frameworks and developer tools frequently establish separate database connections during bursty traffic, rapidly exhausting PostgreSQL connection limits in the absence of traditional middleware. To resolve connection surges, Supabase integrated the open-source connection pooler PgBouncer directly onto the PostgreSQL server across all newly created projects. Rather than increasing the total number of connections PostgreSQL can open, PgBouncer recycles open connections and queues excess requests until active connections become available. Developers can manage pooling through the dashboard across Session, Transaction, and Statement modes, with Transaction mode recommended for serverless functions despite disabling session features like prepared statements. The default pool size is initially configured to 15 connections.

Context

Serverless functions lack middleware servers to maintain persistent database connections, causing them to create a new connection for each concurrent request. During bursty workloads, these direct connections can rapidly overwhelm a PostgreSQL database and exceed its connection limit.

Approach / What changed

Supabase installed the open-source PgBouncer pooler on the same server as PostgreSQL for all new projects. PgBouncer manages pre-opened connections and queues incoming requests when all connections are in use, offering dashboard management for Session, Transaction, and Statement pool modes.

Takeaways

• PgBouncer does not increase the maximum number of connections a Postgres instance can open; it maintains open connections and queues incoming requests instead of rejecting them when the pool is full.
• Transaction pool mode is recommended for serverless functions because it assigns connections only for the duration of a transaction, though it does not support features like prepared statements.
• Statement pool mode returns connections to the pool after every individual statement, which works best with AUTOCOMMIT and disallows multi-statement transactions.

Related reading

• Supabase · Apr 6, 2021

  Supabase Beta March 2021

  Supabase announced a series of platform updates for March 2021, headlined by the launch of Supabase Storage and new local development capabilities. The new storage offering integrates an open-source Fastify and TypeScript API backed by S3, while direct database connections now bundle PgBouncer for connection pooling. Developers can run the platform locally via the Supabase CLI, utilize Docker tooling for self-hosting, and access the open-sourced Supabase UI component library. Community contributions brought OAuth scopes to Supabase Auth for requesting elevated third-party provider permissions, alongside newly published free and pro pricing tiers. In addition, development is underway on an Elixir-based workflow engine compatible with the Amazon States Language for automated event handling.

  Paul Copplestone

• Supabase · Apr 2, 2021

  Supabase Dot Com

  Supabase announced a migration of its primary website from supabase.io to supabase.com over the upcoming week. User API endpoints will remain unchanged on supabase.co, requiring no configuration adjustments for active applications. The company originally selected supabase.io as a meme-inspired placeholder after releasing a Postgres Realtime engine on GitHub in 2019 and failing to find an available dot com name. Following advice from Y Combinator to secure a dot com address, the founders bypassed domain brokers charging percentage fees and negotiated directly with the domain owner, whose business address was located one hundred meters from the founder's home. The migration updates public website destinations while preserving existing developer infrastructure unaffected.