Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Home

Custom Postgres Config

Supabase projects come with a Postgres cluster that is pre-configured for optimal performance. The configuration is based on a diverse range of workloads as well as the compute add-ons being used in the project. You can override this configuration to better optimize the Postgres cluster for specific workloads.

Custom Postgres config

While most Postgres parameters can be configured from within SQL, some parameters must either be set using a config file, or require superuser access. Custom Postgres Config allows you to configure such parameters.

From the perspective of Postgres, config overrides will show up in the global configuration file. Role or database specific configuration could override them for some scenarios; please refer to the Postgres docs on each parameter for additional details.

Supported parameters

The following parameters are available for overrides:

  1. effective_cache_size
  2. maintenance_work_mem
  3. max_connections
  4. max_locks_per_transaction
  5. max_parallel_maintenance_workers
  6. max_parallel_workers_per_gather
  7. max_parallel_workers
  8. max_worker_processes
  9. session_replication_role
  10. shared_buffers
  11. statement_timeout
  12. work_mem

Setting config using the CLI

To get started:

  1. Install the Supabase CLI 1.69.0+.
  2. Log in to your Supabase account using the CLI.

The postgres config command of the CLI can be used for setting configuration parameters:


_10
$ supabase --experimental --project-ref <project-ref> postgres-config update --config max_parallel_workers=6 --config shared_buffers=250MB
_10
- Custom Postgres Config -
_10
Config |Value |
_10
shared_buffers |250MB |
_10
max_parallel_workers |6 |
_10
- End of Custom Postgres Config -

By default, the CLI will merge any provided config overrides with any existing ones. The --replace-existing-overrides flag can be used to instead force all existing overrides to be replaced with the ones being provided:


_10
$ supabase --experimental --project-ref <project-ref> postgres-config update --config max_parallel_workers=3 --replace-existing-overrides
_10
_10
- Custom Postgres Config -
_10
Config |Value |
_10
max_parallel_workers |3 |
_10
- End of Custom Postgres Config -

Considerations

  1. The Postgres cluster will be restarted in order to change the configuration being used. This will cause momentary disruption to existing database connections; in most cases this should not take more than a few seconds.
  2. Custom Postgres Config will always override the default optimizations generated by Supabase. When changing compute add-ons, this may require manual updates to any relevant overrides that have been applied.

Pooler config

You can also customize some parameters for the Connection Pooler:

  1. Pooling Mode
  2. Default Pool Size

The default pool size, and the maximum number of clients allowed to connect concurrently is automatically optimized based on the compute add-on being used. At the moment, the Dashboard only reflects any custom configuration being used, and does not include the default optimized numbers used for your project.

Custom Pooler Config may also require manual updates to any relevant overrides when changing compute add-ons.