Configure Database System

From a user’s perspective, SynxDB functions as a complete relational database management system (RDBMS). Physically, however, it is a distributed system composed of multiple PostgreSQL instances. To enable these independent instances to work together as a unified cluster, SynxDB manages distributed processing for data storage, computing, communication, and management. It abstracts away the complexity of the distributed system, presenting users with a single, logical database view.

Server configuration parameters affect the behavior of SynxDB. They are part of the PostgreSQL “Grand Unified Configuration” system, so they are sometimes called “GUCs”. Most of the SynxDB server configuration parameters are the same as the PostgreSQL configuration parameters, but some are specific to SynxDB.

Coordinator and local parameters

Server configuration files contain parameters that configure server behavior. The SynxDB configuration file, postgresql.conf, resides in the data directory of the database instance.

The coordinator and each segment instance have their own postgresql.conf file. Some parameters are local: each segment instance examines its postgresql.conf file to get the value of that parameter. Set local parameters on the coordinator and on each segment instance.

Other parameters are coordinator parameters that you set on the coordinator instance. The value is passed down to (or in some cases ignored by) the segment instances at query run time.

Set configuration parameters

Many configuration parameters limit who can change them and where or when they can be set. For example, to change certain parameters, you must be a SynxDB superuser. Other parameters can be set only at the system level in the postgresql.conf file or require a system restart to take effect.

Many configuration parameters are session parameters. For example, SynxDB supports timeout management, which can be controlled using the statement_timeout parameter for timeout. You can set session parameters at the system level, the database level, the role level or the session level. Database users can change most session parameters within their session, but some require superuser permissions.

For example, SynxDB controls session handling through several GUC parameters. You can configure idle_session_timeout to automatically terminate sessions that have been idle for a specified period. In addition, when a coordinator node receives a concurrent session request from a client, it will terminate the new session request to manage connection resources.

Set a local configuration parameter

To change a local configuration parameter across multiple segments, update the parameter in the postgresql.conf file of each targeted segment, both primary and mirror. Use the gpconfig utility to set a parameter in all SynxDB postgresql.conf files. For example:

$ gpconfig -c gp_vmem_protect_limit -v 4096

Restart SynxDB to make the configuration changes effective:

$ gpstop -r

Set a coordinator configuration parameter

To set a coordinator configuration parameter, set it at the SynxDB coordinator instance. If it is also a session parameter, you can set the parameter for a particular database, role or session. If a parameter is set at multiple levels, the most granular level takes precedence. For example, session overrides role, role overrides database, and database overrides system.

Set parameters at the system level

Coordinator parameter settings in the coordinator postgresql.conf file are the system-wide default. To set a coordinator parameter:

Edit the $COORDINATOR_DATA_DIRECTORY/postgresql.conf file.
Find the parameter to set, uncomment it (remove the preceding # character), and type the desired value.
Save and close the file.
For session parameters that do not require a server restart, upload the postgresql.conf changes as follows:
```
$ gpstop -u
```
For parameter changes that require a server restart, restart SynxDB as follows:
```
$ gpstop -r
```

Set parameters at the database level

Use ALTER DATABASE to set parameters at the database level. For example:

ALTER DATABASE mydatabase SET search_path TO myschema;

When you set a session parameter at the database level, every session that connects to that database uses that parameter setting. Settings at the database level override settings at the system level.

Set parameters at the row level

Use ALTER ROLE to set a parameter at the role level. For example:

ALTER ROLE bob SET search_path TO bobschema;

When you set a session parameter at the role level, every session initiated by that role uses that parameter setting. Settings at the role level override settings at the database level.

Set parameters in a session level

Any session parameter can be set in an active database session using the SET command. For example:

SET statement_mem TO '200MB';

The parameter setting is valid for the rest of that session or until you issue a RESET command. For example:

RESET statement_mem;

Settings at the session level override those at the role level.

View server configuration parameter settings

The SQL command SHOW allows you to see the current server configuration parameter settings. For example, to see the settings for all parameters:

$ psql -c 'SHOW ALL;'

SHOW lists the settings for the coordinator instance only. To see the value of a particular parameter across the entire system (and all segments), use the gpconfig utility. For example:

$ gpconfig --show max_connections

View parameters using the `pg_settings` view

In addition, you can use the pg_settings view to check all parameter settings:

SELECT name, setting, unit, category, short_desc, context, vartype, min_val, max_val
FROM pg_settings
ORDER BY category, name;

Field descriptions:

name: Parameter name.
setting: Current parameter value.
unit: Parameter unit (if applicable, such as ms and kB).
category: The configuration category that the parameter belongs to.
short_desc: Brief description of the parameter.
context: Required context for parameter modification, possible values include:
- internal: Used internally by the system, cannot be modified directly.
- postmaster: Requires database instance restart to take effect.
- sighup: Loads configuration via pg_ctl reload or SELECT pg_reload_conf();.
- superuser: Only superusers can modify.
- user: Regular users can modify.
vartype: Parameter type, such as bool, integer, real, string.
min_val and max_val: Minimum and maximum allowed values (if applicable).

Use cases:

View parameters of a specific category:

SELECT name, setting, short_desc
FROM pg_settings
WHERE category = 'Resource Usage / Memory';

Search for parameters containing specific keywords:

SELECT name, setting, short_desc
FROM pg_settings
WHERE name LIKE '%work_mem%';

Check parameters requiring restart to take effect:

SELECT name, setting, context
FROM pg_settings
WHERE context = 'postmaster';

Display parameters effective in the current database session:

SELECT name, setting
FROM pg_settings
WHERE context = 'user';

Note

In SynxDB, because of its distributed architecture, some parameters can only be configured on the coordinator node or cluster-wide. When modifying parameters using the gpconfig tool, pay attention to synchronization between primary, mirror, and segment nodes.

To change configuration parameters, you can use SQL commands, for example:

ALTER SYSTEM SET parameter_name = 'value';

Or edit the postgresql.conf file and make it effective through pg_reload_conf() or service restart.