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.
Customizing Postgres configurations provides advanced control over your database, but inappropriate settings can lead to severe performance degradation or project instability.
Viewing settings
To list all Postgres settings and their descriptions, run:
_10select * 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:
_10select * from pg_settings where context = 'user';
As an example, the statement_timeout setting for the can be altered:
_10alter database "postgres" set "statement_timeout" TO '60s';
To verify the change, execute:
_10show "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:
| Setting | Description |
|---|---|
auto_explain.log_min_duration | Logs query plans taking longer than this duration. |
auto_explain.log_nested_statements | Log nested statements' plans. |
log_min_messages | Minimum 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_role | Sets the session's behavior for triggers and rewrite rules. |
track_io_timing | Collects timing statistics for database I/O activity. |
For example, to enable log_nested_statements for the postgres role, execute:
_10alter role "postgres" set "auto_explain.log_nested_statements" to 'on';
To view the change:
_10select_10 rolname,_10 rolconfig_10from pg_roles_10where 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 changes permanently overwrite default settings, so reset all and set to default commands won't revert to the original values.
CLI supported parameters
If a setting you need is not yet configurable, share your use case with us! Let us know what setting you'd like to control, and we'll consider adding support in future updates.
The following parameters are available for overrides:
- effective_cache_size
- maintenance_work_mem
- max_connections (CLI only)
- max_locks_per_transaction (CLI only)
- max_parallel_maintenance_workers
- max_parallel_workers_per_gather
- max_parallel_workers
- max_worker_processes (CLI only)
- session_replication_role
- shared_buffers (CLI only)
- work_mem
Managing Postgres configuration with the CLI
To start:
To update Postgres configurations, use the postgres config command:
_10supabase --experimental \_10--project-ref <project-ref> \_10postgres-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:
_10supabase --experimental \_10--project-ref <project-ref> \_10postgres-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_10alter database "postgres" set "<setting_name>" to default;_10_10-- reset all settings at the database level_10alter database "postgres" reset all;
For role level configurations, you can run:
_10alter role "<role_name>" set "<setting_name>" to default;
Considerations
- 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.
- 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.