Database

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:


_10
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:


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

As an example, the statement_timeout setting can be altered:


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

To verify the change, execute:


_10
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:

SettingDescription
auto_explain.log_min_durationLogs query plans taking longer than this duration.
auto_explain.log_nested_statementsLog nested statements' plans.
log_min_messagesMinimum severity level of messages to log.
pgaudit.*Configures the PGAudit extension. The log_parameter is still restricted to protect secrets
pgrst.*PostgREST settings
plan_filter.*Configures the pg_plan_filter extension
session_replication_roleSets the session's behavior for triggers and rewrite rules.
track_io_timingCollects timing statistics for database I/O activity.

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


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

To view the change:


_10
select
_10
rolname,
_10
rolconfig
_10
from pg_roles
_10
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:

  1. effective_cache_size
  2. logical_decoding_work_mem (CLI only)
  3. maintenance_work_mem
  4. max_connections (CLI only)
  5. max_locks_per_transaction (CLI only)
  6. max_parallel_maintenance_workers
  7. max_parallel_workers_per_gather
  8. max_parallel_workers
  9. max_replication_slots (CLI only)
  10. max_slot_wal_keep_size (CLI only)
  11. max_standby_archive_delay (CLI only)
  12. max_standby_streaming_delay (CLI only)
  13. max_wal_size (CLI only)
  14. max_wal_senders (CLI only)
  15. max_worker_processes (CLI only)
  16. session_replication_role
  17. shared_buffers (CLI only)
  18. wal_keep_size (CLI only)
  19. wal_sender_timeout (CLI only)
  20. work_mem

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:


_10
supabase --experimental \
_10
--project-ref <project-ref> \
_10
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:


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

Resetting to default config

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


_10
-- reset a single setting at the database level
_10
alter database "postgres" set "<setting_name>" to default;
_10
_10
-- reset all settings at the database level
_10
alter database "postgres" reset all;

For role level configurations, you can run:


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

Considerations

  1. Changes through the CLI must restart the database and will cause 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.
  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.