PolarDB lets you configure parameters at the cluster level from the console, or at the database, role, and session level using SQL commands on a connected client. Each scope level overrides the one below it: session settings take precedence over role settings, which take precedence over database settings, which take precedence over cluster-level defaults.
Prerequisites
Before you begin, ensure that you have:
A PolarDB cluster
Access to the PolarDB console or a connected SQL client
Modify parameters on a client
Use SQL commands to set parameters at a finer scope than the cluster level.
Understand scope and priority
Three scope levels are available. A higher-priority scope overrides a lower one for the same parameter:
| Command | Scope | Override priority | Session reconnect required |
|---|---|---|---|
SET | Current session only; resets when the session ends | Highest | No |
ALTER ROLE | Per-user; overrides database-level settings | High | Yes |
ALTER DATABASE | Per-database; different values for different databases | Medium | Yes |
SET changes are lost when the session disconnects. To persist a parameter value across sessions, use ALTER DATABASE or ALTER ROLE.Set a database-level parameter
Use ALTER DATABASE to apply a parameter to all sessions that connect to a specific database. The setting persists until you reset it. Reconnect after running the command for it to take effect.
test1=> ALTER DATABASE test1 SET vacuum_cost_delay = 10;
ALTER DATABASESet a role-level parameter
Use ALTER ROLE to apply a parameter for a specific user across all databases. Role-level settings override database-level settings for the same parameter. Reconnect after running the command for it to take effect.
test1=> ALTER ROLE test1 SET random_page_cost = 10;
ALTER ROLETo verify the stored role and database settings, query pg_db_role_setting:
test1=> SELECT * FROM pg_db_role_setting;
setdatabase | setrole | setconfig
------------+---------+------------------------
41891 | 0 | {vacuum_cost_delay=10}
0 | 41284 | {random_page_cost=10}
(2 rows)Set a session-level parameter
Use SET to override a parameter for the current session only. The change takes effect immediately and requires no reconnect. The setting reverts when the session ends.
test1=> SET random_page_cost = 100;
SET
test1=> SHOW random_page_cost;
random_page_cost
------------------
100
(1 row)