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:
select * from pg_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:
select * from pg_settings where context = 'user';
As an example, the statement_timeout setting can be altered:
alter database "postgres" set "statement_timeout" TO '60s';
To verify the change, execute:
show "statement_timeout";
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:
auto_explain.* Configures the auto_explain module. Can be configured to log execution plans for queries expected to exceed x seconds, including function queries. log_lock_waits Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. log_min_duration_statement Causes the duration of each completed statement to be logged if the statement ran for at least the specified amount of time. log_min_messages Minimum severity level of messages to log. log_replication_commands Logs all replication commands log_statement Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). log_temp_files Controls logging of temporary file names and sizes. pg_net.ttl Sets how long the pg_net extension saves responses pg_net.batch_size Sets how many requests the pg_net extension can make per second pg_stat_statements.* Configures the pg_stat_statements extension. 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. wal_compression This parameter enables compression of WAL using the specified compression method.
For example, to enable log_nested_statements for the postgres role, execute:
alter role "postgres" set "auto_explain.log_nested_statements" to 'on';
To view the change:
select rolname, rolconfigfrom pg_roleswhere rolname = 'postgres';
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.
In order to overwrite the default settings, you must have Owner or Administrator privileges within your organizations.
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:
To start:
To update Postgres configurations, use the postgres config command:
supabase --experimental \--project-ref <project-ref> \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:
supabase --experimental \--project-ref <project-ref> \postgres-config update --config max_parallel_workers=3 \--replace-existing-overrides
To delete specific configuration overrides, use the postgres-config delete command:
supabase --experimental \--project-ref <project-ref> \postgres-config delete --config shared_buffers,work_mem
By default, changing the configuration, whether by updating or deleting, causes the database and all associated read replicas to restart. You can use the --no-restart flag to prevent this behavior, and attempt to reload the updated configuration without a restart. Refer to the Postgres documentation to determine if a given parameter can be reloaded without a restart.
Postgres requires several parameters to be synchronized between the Primary cluster and Read Replicas.
By default, Supabase ensures that this propagation is executed correctly. However, if the --no-restart behavior is used in conjunction with parameters that cannot be reloaded without a restart, the user is responsible for ensuring that both the primaries and the read replicas get restarted in a timely manner to ensure a stable running state. Leaving the configuration updated, but not utilized (via a restart) in such a case can result in read replica failure if the primary, or a read replica, restarts in isolation (e.g. due to an out-of-memory event, or hardware failure).
supabase --experimental \--project-ref <project-ref> \postgres-config delete --config shared_buffers --no-restart
To reset a setting to its default value at the database level:
-- reset a single setting at the database levelalter database "postgres" set "<setting_name>" to default;-- reset all settings at the database levelalter database "postgres" reset all;
For role level configurations, you can run:
alter role "<role_name>" set "<setting_name>" to default;
wal_keep_size) can increase disk utilization, triggering disk expansion, which in turn can lead to increases in your bill.RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4