electric-sql
Separate `DATABASE_URL`s for replication and query connections.
I've been doing a pass on deployment docs, including verifying the steps to connect to managed Postgres providers. In a few cases (Supabase, GCP, etc.) they provide a direct IP address for logical replication and a connection-pooled IP address for interactive access.
We currently accept a single DATABASE_URL. Because this is used for logical replication, it must be the direct IP address. Which works but is inefficient. Because we only need to special case the logical replication connection. Our interactive queries could perfectly well go through the user's normal DB pooler, reducing our impact on their database.
I propose that we adjust our config to support a single DATABASE_URL as the current default but also support providing a pair of REPLICATION_DATABASE_URL and INTERACTIVE_DATABASE_URL or whatever the best naming is.
Plus we would need to extend this pattern for multi-tenancy, so that apps can be configured with a pair of values, instead of one URL per database ID.
That sounds good and potentially can help with initial sync latency, since we're making multiple requests on a non-pooled connection on the other side.
Another way to think about this is keeping DATABASE_URL as the primary one, which can be configured standalone just like right now, but adding an optional config named DATABASE_POOL_URL or READ_DATABASE_URL. Setting the latter will cause Electric to use it for general queries.
In terms of implementing this separation, it seems quite straightforward: add database connection options to replication_opts that are passed to Electric.Connection.Manager and make the manager use those for opening a replication connection. Boom, we're done.
Well, almost. We'll also need to handle the fallback from SSL to noSSL for one more set of connection options. Just another argument for adding that fallback logic to the Postgrex proper.
Picking this up as it has become quite necessary with the deploying of multiple examples and sharing of postgres instances.
