Customizing Postgres configs

Each Supabase project is a pre-configured Postgres cluster. You can override some configuration settings to suit your needs. This is an advanced topic, and we don't recommend touching these settings unless it is necessary.

Viewing settings#

To list all Postgres settings and their descriptions, run:

1
select * from pg_settings;

Configurable settings#

User-context settings#

The pg_settings table's context column specifies the requirements for changing a setting. By default, those with a user context can be changed at the role or database level with SQL.

To list all user-context settings, run:

1
select * from pg_settings where context = 'user';

As an example, the statement_timeout setting can be altered:

1
alter database "postgres" set "statement_timeout" TO '60s';

To verify the change, execute:

1
show "statement_timeout";

Superuser settings#

Some settings can only be modified by a superuser. Supabase pre-enables the supautils extension, which allows the postgres role to retain certain superuser privileges. It enables modification of the below reserved configurations at the role level:

For example, to enable log_nested_statements for the postgres role, execute:

1
alter role "postgres" set "auto_explain.log_nested_statements" to 'on';

To view the change:

1
select
2
  rolname,
3
  rolconfig
4
from pg_roles
5
where rolname = 'postgres';

CLI configurable settings#

While many Postgres parameters are configurable directly, some configurations can be changed with the Supabase CLI at the system level.

CLI supported parameters#

The following parameters are available for overrides:

Use the examples below with supabase --experimental --project-ref <project-ref> postgres-config update:

Managing Postgres configuration with the CLI#

To start:

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

To update Postgres configurations, use the postgres config command:

1
supabase --experimental \
2
--project-ref <project-ref> \
3
postgres-config update --config shared_buffers=250MB

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:

1
supabase --experimental \
2
--project-ref <project-ref> \
3
postgres-config update --config max_parallel_workers=3 \
4
--replace-existing-overrides

To delete specific configuration overrides, use the postgres-config delete command:

1
supabase --experimental \
2
--project-ref <project-ref> \
3
postgres-config delete --config shared_buffers,work_mem

By default, CLI v2 (≥ 2.0.0) checks the parameter’s context and requests the correct action (reload or restart):

• If the setting can be reloaded (pg_settings.context = 'sighup'), then the Management API will detect this and apply the change with a configuration reload.
• If the setting requires a restart (pg_settings.context = 'postmaster'), then both the primary and any read replicas will restart to apply the change.

To check whether a parameter can be reloaded without a restart, see the Postgres docs.

You can verify whether changes have been applied with the following checks:

1
supabase --version;

1
-- Check whether the parameters were updated (and if a restart is pending):
2
select name, setting, context, pending_restart
3
from pg_settings
4
where name in ('max_slot_wal_keep_size', 'shared_buffers', 'max_connections');

1
-- If the timestamp hasn’t changed, no restart occurred
2
select pg_postmaster_start_time();

You can also pass the --no-restart flag to attempt a reload-only apply. If the parameter cannot be reloaded, the change stays pending until the next restart.

1
supabase --experimental \
2
--project-ref <project-ref> \
3
postgres-config delete --config shared_buffers --no-restart

Resetting to default config#

To reset a setting to its default value at the database level:

1
-- reset a single setting at the database level
2
alter database "postgres" set "<setting_name>" to default;
3
4
-- reset all settings at the database level
5
alter database "postgres" reset all;

For role level configurations, you can run:

1
alter role "<role_name>" set "<setting_name>" to default;

Considerations#

1. Changes through the CLI might restart the database causing momentary disruption to existing database connections; in most cases this should not take more than a few seconds. However, you can use the --no-restart flag to bypass the restart and keep the connections intact. Keep in mind that this depends on the specific configuration changes you're making. if the change requires a restart, using the --no-restart flag will prevent the restart but you won't see those changes take effect until a restart is manually triggered. Additionally, some parameters are required to be the same on Primary and Read Replicas; not restarting in these cases can result in read replica failure if the Primary/Read Replicas restart in isolation.
2. Custom Postgres Config will always override the default optimizations generated by Supabase. When changing compute add-ons, you should also review and update your custom Postgres Config to ensure they remain compatible and effective with the updated compute.
3. Some parameters (e.g. wal_keep_size) can increase disk utilization, triggering disk expansion, which in turn can lead to increases in your bill.