electric-sql / electric

Sync little subsets of your Postgres data into local apps and services.
https://electric-sql.com
Apache License 2.0
6.47k stars 156 forks source link

Separate `DATABASE_URL`s for replication and query connections. #1885

Open thruflo opened 1 month ago

thruflo commented 1 month ago

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.

balegas commented 1 month ago

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.