YugabyteDB YSQL control parameters play an important role in optimizing and enhancing database performance.
In a previous tip, we learned how we can view control parameter values via a wildcard search.,
These control parameters can be set at the session, user and database level.
Below is a quick example showing how to list all of the control parameters that have been set at the database level for the current database.
First I’ll create a new database, connect to it, then, because it’s a new database, verify that there are no control parameters set at the database level.
yugabyte=# CREATE DATABASE my_db;
CREATE DATABASE
yugabyte=# \c my_db;
You are now connected to database "my_db" as user "yugabyte".
my_db=# SELECT datname AS database,
my_db-# pg_catalog.array_to_string(setconfig, E'\n') AS settings
my_db-# FROM pg_catalog.pg_db_role_setting s
my_db-# JOIN pg_catalog.pg_database d ON d.oid = setdatabase
my_db-# WHERE datname = current_database()
my_db-# ORDER BY 1, 2;
database | settings
----------+----------
(0 rows)
Now I will change a couple of control parameters at the database level.
my_db=# ALTER DATABASE my_db SET yb_read_from_followers=true;
ALTER DATABASE
my_db=# ALTER DATABASE my_db SET yb_enable_expression_pushdown=true;
ALTER DATABASE
The catalog query that previously returned no rows will now list the control parameters I just changed.
my_db=# SELECT datname AS database,
my_db-# pg_catalog.array_to_string(setconfig, E'\n') AS settings
my_db-# FROM pg_catalog.pg_db_role_setting s
my_db-# JOIN pg_catalog.pg_database d ON d.oid = setdatabase
my_db-# WHERE datname = current_database()
my_db-# ORDER BY 1, 2;
database | settings
----------+------------------------------------
my_db | yb_read_from_followers=true +
| yb_enable_expression_pushdown=true
(1 row)
Have Fun!
