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:

cmd

fly postgres config show --app <pg-app-name>

(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.

cmd

fly postgres config update --max-connections 500 --app <pg-app-name>

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!

cmd

fly postgres config show --app <pg-app-name>

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.