Connect an application to a managed PostgreSQL

Every connection to PostgreSQL costs the database memory and a process. An application with a few replicas, each holding a pool of its own, can use up the database’s connections long before it uses up its CPU.

A PostgreSQL you install from the catalog comes with a connection pooler in front of it. Your applications connect to the pooler, and the pooler shares a small number of real database connections among all of them. The pooler is PgBouncer. You will see that name in the Sessions view, and nowhere else.

The pooler also changes what happens when the database moves to another cluster: writes wait in the pooler instead of failing. See Move an application to another cluster.

Two addresses

A managed PostgreSQL publishes two connection strings among its variables:

Variable	Goes to	Use it for
`FLUI_CONNECTION_URL`	The pooler	Your application’s normal traffic
`FLUI_DIRECT_URL`	The database itself	Schema migrations, `LISTEN`/`NOTIFY`, session advisory locks, admin tools

A database without a pooler has both variables too, and both point at the database. So an application can always name the one it means, and keeps working if the pooler is not there.

An application that takes its database from a manifest with fromService: url gets FLUI_CONNECTION_URL, the pooled one.

Run schema migrations on FLUI_DIRECT_URL. Tools such as Prisma migrate, Flyway and Liquibase hold a lock for the whole session while they work, and the pooler in transaction mode cannot keep that lock for them.

flui db tunnel and the browser console always reach the database directly. They never go through the pooler.

Choose it at install

The pooler is on by default. You decide two things when you install: whether there is a pooler, and which mode it runs in.

flui catalog install takes --option key=true|false, once per option:

# pooler on, transaction mode (the default)
flui catalog install postgresql

# pooler on, session mode
flui catalog install postgresql --option poolerSessionMode=true

# no pooler
flui catalog install postgresql --option pooler=false

Option	Default	Effect
`pooler`	`true`	Put a connection pooler in front of the database.
`poolerSessionMode`	`false`	Run the pooler in session mode instead of transaction mode. Only with the pooler.

catalog_get_app lists the two options with their defaults. app_install takes them in options, for example { "pooler": true, "poolerSessionMode": false }. A key left out takes its default.

Three things to know about the choice:

Existing databases are unchanged. A PostgreSQL installed before the pooler existed keeps connecting directly.
A database the catalog creates for another entry has no pooler. When a catalog app needs a database and you let the install provision one, that database is wired to its app directly.
It is fixed at install for now. You cannot add, remove or switch the mode of a pooler on an existing database yet. To get a different setting, install a new database with it and copy the data across with flui db dump and flui db restore.

Transaction mode or session mode

Transaction mode is the default. A client keeps a real database connection only for the length of one transaction. The next transaction may run on a different connection. That is what lets a few database connections serve many clients, and most web applications never notice.

Session mode gives each client one database connection for as long as the client stays connected. Everything PostgreSQL offers works, but you save far fewer connections. Pick it when your application relies on the session features in the table below and you cannot move them to FLUI_DIRECT_URL.

What works through the pooler

What your application does	Transaction mode	Session mode
Single statements and short transactions	Works	Works
Prepared statements sent by the driver (protocol level); the pooler tracks up to 200	Works	Works
`SET LOCAL` inside a transaction	Works	Works
Transaction advisory locks (`pg_advisory_xact_lock`)	Works	Works
Temporary tables used inside one transaction	Works	Works
`SET` without `LOCAL` (a session setting)	Breaks	Works
SQL `PREPARE` and `EXECUTE`	Breaks	Works
Cursors declared `WITH HOLD`	Breaks	Works
`LISTEN` / `NOTIFY`	Breaks	Works
Session advisory locks (`pg_advisory_lock`)	Breaks	Works
Temporary tables kept across transactions	Breaks	Works
Migration tools that hold a session lock (Prisma migrate, Flyway, Liquibase)	Breaks: use `FLUI_DIRECT_URL`	Use `FLUI_DIRECT_URL`

“Breaks” does not mean the pooler refuses the statement. It runs, but your next transaction may run on another database connection, where the setting, the lock or the table is not. If your application does one of these things, give that part of it FLUI_DIRECT_URL, or choose session mode at install.

PostgREST

PostgREST works behind the pooler in transaction mode with two settings:

Setting	Value	Why
`PGRST_DB_PREPARED_STATEMENTS`	`false`	Otherwise a request for a table that does not exist makes the pooler drop PostgREST’s connection, and other requests get a 503 for an instant.
`PGRST_DB_CHANNEL_ENABLED`	`false`	PostgREST reloads its schema cache by listening with `LISTEN`, which does not work in transaction mode.

With the channel off, PostgREST does not pick up schema changes by itself. Restart it after you change the schema.

Check the pooler

flui db pooler my-postgres
flui db pooler my-postgres --json

It prints the mode, how many instances of the pooler are ready, the image it runs, and the two addresses with the variable that carries each. When FLUI_CONNECTION_URL does not go through the pooler yet, for example while the pooler is still starting, it says so and why. A database with no pooler shows its one direct address.

The full flag list is in the databases CLI.

db_pooler_get takes the database application’s id and returns the same facts as the CLI: whether there is a pooler, its mode, whether FLUI_CONNECTION_URL goes through it and why not, and the two addresses. It only reads.

What it costs

The pooler runs next to the database, on the same cluster. Measured in use, it takes about 1m of CPU and 8 MiB of memory. Against the cluster’s capacity check it counts for 25m of CPU and 128 MiB of memory, so an install with the pooler needs that much room on top of the database.

The pooler runs as one instance. It accepts up to 1000 client connections and opens at most 50 to the database.

Where to go next

Open a database from your machine: the tunnel, the console, and the Sessions view.
Move an application to another cluster: what the pooler does to writes during a move.
Install an application from the catalog: the rest of the install.
The databases CLI: flui db pooler and flui db sessions.