View all Postgres settings with pg_settings
To list all Postgres settings and their descriptions, run: SELECT * FROM pg_settings;
Supabase · Database · all subjects
postgres configuration
17 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
User-context settings can be changed at role or database level
Settings in the pg_settings table with context = 'user' can be changed at the role or database level using SQL. To list all user-context settings, run: SELECT * FROM pg_settings WHERE context = 'user';
Alter database setting example with statement_timeout
To set statement_timeout for a database: ALTER DATABASE "postgres" SET "statement_timeout" TO '60s'; To verify the change, run: SHOW "statement_timeout";
Supabase pre-enables supautils extension for superuser settings
Supabase pre-enables the supautils extension, which allows the postgres role to retain certain superuser privileges. This enables modification of reserved configurations at the role level, including auto_explain, deadlock_timeout, logging settings, and other advanced parameters.
Superuser-configurable settings table
The following settings can only be modified by a superuser (via supautils extension at role level): auto_explain.* (configures auto_explain module), deadlock_timeout (time to wait on lock before checking for deadlock), log_duration (logs query duration but not entire query), log_lock_waits (logs when session waits longer than deadlock_timeout to acquire lock), log_min_duration_statement (logs duration and text of statements exceeding threshold), log_min_error_statement (determines if query is recorded based on severity level), log_min_messages (minimum severity level to log), log_parameter_max_length (maximum bytes of bind parameter values logged), log_replication_commands (logs all replication commands), log_statement (controls which SQL statements logged; valid values: none, ddl, mod, all), log_temp_files (controls logging of temporary file names and sizes), pg_stat_statements.* (configures pg_stat_statements extension), pgaudit.* (configures PGAudit extension; log_parameter restricted to protect secrets), pgrst.* (PostgREST settings), plan_filter.* (configures pg_plan_filter extension), safeupdate.enabled (enables safeupdate extension requiring WHERE clause on UPDATE/DELETE), session_replication_role (sets behavior for triggers and rewrite rules), track_functions (controls function call tracking; valid values: none, pl, all), track_io_timing (collects timing statistics for database I/O), wal_compression (enables WAL compression using specified method).
Alter role setting example with auto_explain
To enable log_nested_statements for the postgres role: ALTER ROLE "postgres" SET "auto_explain.log_nested_statements" TO 'on'; To view the change: SELECT rolname, rolconfig FROM pg_roles WHERE rolname = 'postgres';
CLI-configurable Postgres parameters with restart requirements
The following parameters are configurable via Supabase CLI using 'supabase postgres-config update --project-ref <project-ref> --experimental': cron.log_statement (CLI only, Restart: Yes, type boolean, example: --config cron.log_statement=false), checkpoint_timeout (CLI only, Restart: No, type duration, example: --config checkpoint_timeout=15min), effective_cache_size (CLI + SQL, Restart: No, type memory, example: --config effective_cache_size=8GB), hot_standby_feedback (CLI only, Restart: No, type boolean, example: --config hot_standby_feedback=true), logical_decoding_work_mem (CLI + SQL, Restart: No, type memory, example: --config logical_decoding_work_mem=128MB), log_autovacuum_min_duration (CLI only, Restart: No, type duration, example: --config log_autovacuum_min_duration=10s), log_checkpoints (CLI only, Restart: No, type boolean, example: --config log_checkpoints=true), log_connections (CLI only, Restart: No, type boolean, example: --config log_connections=true), log_disconnections (CLI only, Restart: No, type boolean, example: --config log_disconnections=true), log_lock_waits (CLI + SQL, Restart: No, type boolean, example: --config log_lock_waits=true), log_recovery_conflict_waits (CLI only, Restart: No, type boolean, example: --config log_recovery_conflict_waits=true), log_replication_commands (CLI + SQL, Restart: No, type boolean, example: --config log_replication_commands=true), log_startup_progress_interval (CLI only, Restart: No, type duration, example: --config log_startup_progress_interval=10s), log_temp_files (CLI + SQL, Restart: No, type memory, example: --config log_temp_files=10MB), maintenance_work_mem (CLI + SQL, Restart: No, type memory, example: --config maintenance_work_mem=512MB), max_connections (CLI only, Restart: Yes, type integer, example: --config max_connections=200), max_locks_per_transaction (CLI only, Restart: Yes, type integer, example: --config max_locks_per_transaction=128), max_logical_replication_workers (CLI only, Restart: Yes, type integer, example: --config max_logical_replication_workers=10), max_parallel_maintenance_workers (CLI + SQL, Restart: No, type integer, example: --config max_parallel_maintenance_workers=2), max_parallel_workers_per_gather (CLI + SQL, Restart: No, type integer, example: --config max_parallel_workers_per_gather=2), max_parallel_workers (CLI + SQL, Restart: No, type integer, example: --config max_parallel_workers=4), max_replication_slots (CLI only, Restart: Yes, type integer, example: --config max_replication_slots=10), max_slot_wal_keep_size (CLI only, Restart: No, type memory, example: --config max_slot_wal_keep_size=4GB), max_standby_archive_delay (CLI only, Restart: No, type duration, example: --config max_standby_archive_delay=30s), max_standby_streaming_delay (CLI only, Restart: No, type duration, example: --config max_standby_streaming_delay=30s), max_sync_workers_per_subscription (CLI only, Restart: No, type integer, example: --config max_sync_workers_per_subscription=10), max_wal_size (CLI only, Restart: No, type memory, example: --config max_wal_size=2GB), max_wal_senders (CLI only, Restart: Yes, type integer, example: --config max_wal_senders=10), max_worker_processes (CLI only, Restart: Yes, type integer, example: --config max_worker_processes=8), session_replication_role (CLI only, Restart: No, type enum, example: --config session_replication_role=replica), shared_buffers (CLI only, Restart: Yes, type memory, example: --config shared_buffers=256MB), statement_timeout (CLI + SQL, Restart: No, type duration, example: --config statement_timeout=60s), track_activity_query_size (CLI only, Restart: Yes, type integer, example: --config track_activity_query_size=2048B), track_commit_timestamp (CLI only, Restart: Yes, type boolean, example: --config track_commit_timestamp=true), wal_keep_size (CLI only, Restart: No, type memory, example: --config wal_keep_size=1GB), wal_sender_timeout (CLI only, Restart: No, type duration, example: --config wal_sender_timeout=60s), work_mem (CLI + SQL, Restart: No, type memory, example: --config work_mem=64MB).
CLI postgres-config update command syntax
To update Postgres configurations via CLI: supabase postgres-config update --config shared_buffers=250MB --project-ref <project-ref> --experimental. By default, the CLI merges provided config overrides with existing ones. Use --replace-existing-overrides flag to replace all existing overrides instead.
CLI postgres-config delete command syntax
To delete specific configuration overrides: supabase postgres-config delete --config shared_buffers,work_mem --project-ref <project-ref> --experimental
CLI automatically determines reload vs restart based on parameter context
CLI v2 (≥ 2.0.0) checks the parameter's context and requests the correct action: if setting can be reloaded (pg_settings.context = 'sighup'), Management API applies change with configuration reload; if setting requires restart (pg_settings.context = 'postmaster'), both primary and read replicas restart. Use --no-restart flag to attempt reload-only apply; if parameter cannot be reloaded, change stays pending until next restart.
Check Postgres parameter updates with SQL
To verify whether Postgres parameter changes have been applied: SELECT name, setting, context, pending_restart FROM pg_settings WHERE name IN ('max_slot_wal_keep_size', 'shared_buffers', 'max_connections'); To check if restart occurred: SELECT pg_postmaster_start_time();
Reset Postgres setting to default value
To reset a single setting at database level: ALTER DATABASE "postgres" SET "<setting_name>" TO DEFAULT; To reset all settings at database level: ALTER DATABASE "postgres" RESET ALL; For role-level configurations: ALTER ROLE "<role_name>" SET "<setting_name>" TO DEFAULT;
Pitfall: CLI changes permanently overwrite defaults; reset won't revert
CLI changes permanently overwrite default settings, so 'reset all' and 'set to default' commands won't revert to original values. This is a destructive operation.
Pitfall: Parameters must be synchronized between Primary and Read Replicas
Postgres requires several parameters to be synchronized between the Primary cluster and Read Replicas. If using --no-restart with parameters that cannot be reloaded without restart, the user is responsible for ensuring both primaries and read replicas restart timely. Leaving configuration updated but not used via restart can result in read replica failure if primary or replica restarts in isolation due to out-of-memory or hardware failure.
Pitfall: Custom Postgres Config overrides Supabase default optimizations
Custom Postgres Config will always override the default optimizations generated by Supabase. When changing compute add-ons, review and update custom Postgres Config to ensure compatibility and effectiveness with updated compute.
Pitfall: Some parameters can increase disk utilization and billing
Some parameters (e.g. wal_keep_size) can increase disk utilization, triggering disk expansion, which can lead to increases in billing.
Owner or Administrator privileges required to modify CLI settings
To overwrite default settings via CLI, user must have Owner or Administrator privileges within their organization.