Connection Pooling with Supabase's New Supavisor

Intro

Supavisor is Supabase's new connection pooler, now available on all new project (and being added to existing projects by Oct 15th). This is now the recommended connection method when creating a Supabase datasource in Appsmith. 

Release Notes: https://github.com/orgs/supabase/discussions/17817

In this post, we'll dive-in and see what connection pooling is all about, and why Supabase is upgrading from the old pgboucer that you may be more familiar with. We'll also discuss the shift to IPv6, which was a major factor in this connection pooler update. And lastly, we'll review the new, recommended method for creating a Supabase datasource in Appsmith. 

Direct Database Connections vs. Connection Pooling

Direct connections are just like they sound; you initiate a connection when needed and close it when you're done, just like calling someone on the phone. However, this can be resource-intensive, as each new connection setup and tear-down consumes valuable server resources. In scenarios with high traffic or numerous simultaneous requests, this can lead to performance bottlenecks, impacting the overall user experience. 

Although a direct connection from Appsmith will work at first, you'll likely see connection issues after heavy use, which may require rebooting the project in Supabase, or waiting for connections to close.  To avoid this, connection pooling should be used.

Connection pooling, on the other hand, optimizes resource utilization. Instead of opening and closing connections for every request, a pool of connections is maintained. These connections are reused as needed, reducing the overhead of repeatedly establishing new connections. The pooler also has logic and settings for when to close connections and how many to allow per user, or overall. 

IPv4 vs. IPv6: The Need for Transition

Supabase has released this new connection pooler to add support for IPv6. IPv4 and IPv6 are address systems used to identify devices on the internet. 

The move from IPv4 to IPv6 is imperative. IPv4, with its 32-bit addressing scheme, has been the backbone of the internet for decades. However, the explosive growth of internet-connected devices and services has exhausted available IPv4 addresses.

IPv4 Limitations

The shortage of IPv4 addresses hampers the expansion of the internet. Network Address Translation (NAT) has been used as a workaround, but it adds complexity and can cause issues with certain applications. Additionally, IPv4's limited address space is a major security concern.

IPv6 Advantages

IPv6 offers a vast pool of unique addresses, thanks to its 128-bit addressing system. This not only solves the address shortage but also simplifies network configurations. IPv6 brings enhanced security features and enables more efficient data routing.

Connecting to Supabase from Appsmith

1. Ensure your Supabase project has the new Supavisor pool mode available
2. Copy the credentials from the connection string
3. Create a new Postgres datasource in Appsmith

Make sure you use the right connection string! The new format looks like this:

postgres://postgres.XYZ123:[YOUR-PASSWORD]@aws-0-us-west-1.pooler.supabase.com:6543/postgres

Notice the differences in the URL:

• Same address for everyone (aws-0-us-west-1.pooler.supabase.com) instead of the project ID being in the URL
• User Name has an extra ID added on (.XYZ123)
• Port changes from 5432 to 6543

Using this configuration will ensure your Appsmith connections to Supabase remain performant and responsive. 

Conclusion

Connection pooling is a vital technique for optimizing database connections, particularly in high-traffic scenarios. The transition from IPv4 to IPv6 is equally crucial for the internet's sustained growth and security. Supabase will eventually be deprecating the pgbouncer pooler, make sure to switch over to Supavisor before the deadline.