Postgres Configuration Tuning

This page is archived. Fly Postgres (unmanaged) is no longer maintained, and Fly.io Support is not able to provide support or guidance for it. We now offer Fly.io Managed Postgres, our fully-managed database service that handles all aspects of running production PostgreSQL databases.These pages are kept for reference and are not listed in the navigation. The full set is indexed on the Fly Postgres (Unmanaged) page.

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.

​View current configuration

​Update configuration settings

​Gotchas

View current configuration

Update configuration settings

Gotchas