If you’ve ever changed session configuration parameters using SET …, it’s handy to know which settings differ from the defaults.
YugabyteDB exposes this via the pg_settings view.
Run this query to find non-default parameters:
SELECT name,
setting,
source
FROM pg_settings
WHERE source <> 'default'
ORDER
BY name;
What this does:
-
nameenable_nestloop,work_mem,yb_read_from_followers) -
setting -
sourcesession,user,database,configuration file, etc.)
This shows only those parameters that have been changed in the session or are overridden at the database or user level.
Example:
yugabyte=# SET enable_nestloop = off;
SET
yugabyte=# SET work_mem = 1000;
SET
yugabyte=# SET yb_read_from_followers = on;
SET
yugabyte=# SELECT name,
yugabyte-# setting,
yugabyte-# source
yugabyte-# FROM pg_settings
yugabyte-# WHERE source <> 'default'
yugabyte-# ORDER
yugabyte-# BY name;
name | setting | source
----------------------------+-------------------------------------------------------+--------------------
DateStyle | ISO, MDY | configuration file
TimeZone | UTC | configuration file
application_name | ysqlsh | client
client_encoding | UTF8 | client
config_file | /root/var/data/pg_data/ysql_pg.conf | override
data_directory | /root/var/data/pg_data | override
default_text_search_config | pg_catalog.english | configuration file
dynamic_shared_memory_type | posix | configuration file
enable_nestloop | off | session
hba_file | /root/var/data/pg_data/ysql_hba.conf | override
ident_file | /root/var/data/pg_data/ysql_ident.conf | override
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8 | configuration file
lc_numeric | en_US.UTF-8 | configuration file
lc_time | en_US.UTF-8 | configuration file
listen_addresses | 127.0.0.1 | command line
log_directory | /root/var/data/yb-data/tserver/logs | command line
log_timezone | UTC | configuration file
logging_collector | on | command line
max_connections | 300 | configuration file
max_wal_size | 1024 | configuration file
min_wal_size | 80 | configuration file
port | 5433 | command line
shared_buffers | 16384 | configuration file
shared_preload_libraries | pg_stat_statements,yb_pg_metrics,pgaudit,pg_hint_plan | configuration file
transaction_deferrable | off | override
transaction_isolation | read committed | override
transaction_read_only | off | override
unix_socket_directories | /tmp/.yb.127.0.0.1:5433 | command line
unix_socket_permissions | 0700 | command line
work_mem | 1000 | session
yb_read_from_followers | on | session
(32 rows)
Whoa, that’s a lot of non-default configuration settings in YugabyteDB!
YugabyteDB inherits PostgreSQL’s GUC system, but because it’s a distributed system with its own architecture, many non-default settings are baked in intentionally to support features like:
• Distributed reads (
yb_read_from_followers)• Enhanced observability (
pg_stat_statements,pgaudit, etc.)• Cluster safety and consistency (e.g.,
transaction_read_only = off, tunedwalsizes)• Compatibility and stability tweaks (e.g., shared memory type, socket paths)
session level, simply change the query as follows:
yugabyte=# SELECT name,
yugabyte-# setting,
yugabyte-# source
yugabyte-# FROM pg_settings
yugabyte-# WHERE source = 'session'
yugabyte-# ORDER
yugabyte-# BY name;
name | setting | source
------------------------+---------+---------
enable_nestloop | off | session
work_mem | 1000 | session
yb_read_from_followers | on | session
(3 rows)
Have Fun!
