Modifying database configuration parameters

The database parameters listed on the DB Configuration tab are also referred to as Grand Unified Configuration (GUC) variables. See What Is a GUC Variable? for more information. One exception is the EDB Postgres Tuner (pg_tuner) extension. You enable edb_pg_tuner on the DB Configuration tab of the Create or Edit Cluster page of the BigAnimal portal.

The list of parameters is populated based on the type of database you selected on the Operational Settings tab when you created your cluster. For more information about the parameters for your database type:

• For PostgreSQL parameters, see Setting Parameters and Server Configuration in the Postgres documentation.

• For EDB Postgres Advanced Server, see Summary of Configuration Parameters and Configuration Parameters.

• For more information on the types of values parameters accept, see Parameter Names and Values. The types apply to both PostgreSQL and EDB Postgres Advanced Server parameters.

Using formulas for parameter values

In addition to entering specific values for parameters, for some parameters you can specify formulas to automatically calculate a value. You can use formulas for parameters of type integer and real using the following operators: + - / * > >= < <= == != && || ! ? : ( ). Use ? amd : in ternary formulas such as the shared buffer example. Use ( ) to specify order of operations, if needed. GUCs used in formulas must also be of type integer or real. All arithmetic is done on 64-bit floating point values rounded to an integer resultif the target GUC is of type integer and not real.

BigAnimal has what we refer to as psuedo GUCs to facilitate creating equations. These read-only GUCs are:

• cpu_cores — number of cores provided by the VM running the cluster nodes
• disk_iops — number of IOPS allocated to the disks backing the primary data volume
• disk_size — amount of memory backing the primary data volume, in KB
• ram — amount of memory provided by the VM running the cluster nodes, in KB
• replica_count — total number of nodes in the cluster

Formulas can calculate a value relative to the specifications you selected for the cluster or relative to other non-pseudo GUCs. If you resize your cluster, BigAnimal recalculates formulas using the new values for the pseudo GUCs.

Order of operations

* and / are at the same operator precedence. + and - are at the same operator precedence. * and / are evaluated before + and -. Use parentheses to specify additional order of operations.

Units

GUCs can either be a bare number, such as 8, or have a unit, such as GB. Units are categorized into kinds of units such as size or duration.

The kind of unit of the target GUC must match the kind of unit used in the formula. For example, you can't use a formula that tries to multiply two seconds with three kilobytes (2s * 3KB), but you can use a formula that multiplies two sizes even if they have different scales (2KB * 8GB).

The unit of the target GUC must match the unit used in the formula. For example, you can multiply two seconds by four days but you can't assign the result to a GUC of unit KB.

If a target GUC has a unit, a bare number in an equation is assumed to be in the default unit for the target GUC.

Recursion

We recommend no more than approximately 100 levels of recursion depth.

Examples

Instead of entering a specific value for the shared_buffers parameter, with GUC equations you can specify that the value for shared_buffers should be a certain percentage of RAM, but no higher than a certain size. For example:

((ram * 0.25) > 8 GB) ? 8 GB : (ram * 0.25)

What if we want wal_buffers to be three percent of shared_buffers, up to a maximum of 16MB? The formula is:

((shared_buffers * 0.03) > 16MB) ? 16MB : (shared_buffers * 0.03)

The formula refers to the shared_buffers, which, in turn, has its own formula in this example. This second example shows how GUCs can refer to other GUCs.

Common errors and error handling

Malformed formulas result in an errors. Here are some examples:

• Following a number with two units, for example:

  work_mem = 10 GB kB

  produces the error:

  unexpected token "kB" at end of input (line 1, col 7)

• Creating an infinite evaluation loop by having a GUC referring to itself, for example:

  effective_cache_size = effective_cache_size

  gives the error:

  cycle in equation; GUC "effective_cache_size" already seen

• Mixing unit types, for example:

  work_mem = 10kB * 8s

  gives the error:

  both size and time units used in calculation; cannot proceed

Modify a parameter

1. Go to the parameter you want to modify using these methods:

   • To search for a specific parameter, use the search field.
   • To filter the parameters that show, select one of the following in the Show only list:
     • Custom Values — Shows only parameters that changed from the default values, either in the current session or modified in a previous session and applied to the cluster.
     • Currently Edited Values — Shows only parameters that changed during the current editing session.

2. Enter the new value in the parameter value field.

   Parameters identified with a yellow exclamation point icon trigger a restart when you save your changes. The restart terminates all open connections and takes a few minutes to complete. It takes a few more seconds for the new connection to establish. During this process, it isn't possible to connect to the database.

3. Save your changes.

• On this page
• Using formulas for parameter values
• Modify a parameter