Postgres Configuration Tuning
When you created your Fly Postgres cluster, Postgres configuration parameters were set to reasonable defaults for your VM resources.
flyctl exposes some of these parameters so you can tune them to your application’s needs, or to match any VM scaling you do.
Before you start tuning, it’s important to understand the tradeoffs that you’re making. Choose values for these parameters with the guidance of the Postgres docs.
View current configuration
View your cluster’s current configurable settings with fly postgres config show:
fly postgres config show --app <pg-app-name>
NAME VALUE UNIT DESCRIPTION PENDING RESTART
log-min-duration-statement -1 ms Sets the minimum execution time above which all statements will be logged. (-1, 2147483647) false
log-statement none Sets the type of statements logged. [none, ddl, mod, all] false
max-connections 300 Sets the maximum number of concurrent connections. (1, 262143) false
shared-buffers 8192 8kB Sets the number of shared memory buffers used by the server. (16, 1073741823) false
shared-preload-libraries Lists shared libraries to preload into server. false
wal-level replica Sets the level of information written to the WAL. [minimal, replica, logical] false
(The shared-buffers value is indeed in units of 8kB, so a value of 8192 corresponds to 64MB of RAM that Postgres will use for shared buffers.)
Update configuration settings
To demo how this works we’ll update the max-connections setting from 300 to 500 using fly postgres config update.
fly postgres config update --max-connections 500 --app <pg-app-name>
NAME VALUE TARGET VALUE RESTART REQUIRED
max-connections 300 500 true
? Are you sure you want to apply these changes? Yes
Acquired lease f912530da158 on machine: 4d89649b460987
Performing update...
Update complete!
Please note that some of your changes will require a cluster restart before they will be applied.
To review the state of your changes, run: `fly postgres config show`
? Restart cluster now? Yes
Identifying cluster role(s)
Machine e286030f792186: replica
Machine e784977a42e983: replica
Machine 4d89649b460987: leader
Restarting machine e286030f792186
Waiting for e286030f792186 to become healthy (started, 3/3)
Machine e286030f792186 restarted successfully!
Restarting machine e784977a42e983
Waiting for e784977a42e983 to become healthy (started, 3/3)
Machine e784977a42e983 restarted successfully!
Attempting to failover 4d89649b460987
Restarting machine 4d89649b460987
Waiting for 4d89649b460987 to become healthy (started, 3/3)
Machine 4d89649b460987 restarted successfully!
Postgres cluster has been successfully restarted!
Each VM belonging to your Postgres app will be restarted to apply the changes. If you have a replica for high availability, this is updated and restarted first, and it becomes leader before the old leader gets updated.
We can take one last look at our config show and see that our max-connections value has now officially been applied!
fly postgres config show --app <pg-app-name>
NAME VALUE UNIT DESCRIPTION PENDING RESTART
log-min-duration-statement -1 ms Sets the minimum execution time above which all statements will be logged. (-1, 2147483647) false
log-statement none Sets the type of statements logged. [none, ddl, mod, all] false
max-connections 500 Sets the maximum number of concurrent connections. (1, 262143) false
shared-buffers 8192 8kB Sets the number of shared memory buffers used by the server. (16, 1073741823) false
shared-preload-libraries Lists shared libraries to preload into server. false
wal-level replica Sets the level of information written to the WAL. [minimal, replica, logical] false
Gotchas
If you mismatch Postgres parameters and VM RAM, you may end up with an unhealthy cluster and errors like this in your logs (these are from an older Stolon-managed cluster):
[info]keeper | 2022-11-13 16:46:20.260 UTC [717] FATAL: could not map anonymous shared memory: Cannot allocate memory
[info]keeper | 2022-11-13 16:46:20.260 UTC [717] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory, swap space, or huge pages. To reduce the request size (currently 1135091712 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
If you’re scaling your instances up, do that before adjusting the Postgres config to match. If you’re scaling down, adjust the cluster’s Postgres parameters to match the reduced resources before scaling the VM.