Formulas for parameter values

In addition to entering specific values for parameters in the DB Configuration tab, for some parameters you can specify formulas to calculate a value. You can use formulas for parameters of type integer and real in ternary formulas, such as the shared buffer example, using the following operators: + - / * > >= < <= == != && || ! ? : ( ). Use ? and : . 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 result if the target GUC is of type integer and not real.

Hybrid Manager (HM) also has what's refer to as pseudo GUCs to help with 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, HM 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 is a certain percentage of RAM but no higher than a certain size. For example:

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

Suppose you want wal_buffers to be 3% 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 example shows how GUCs can refer to other GUCs.

Common errors and error handling

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

• Following a number with two units, for example:

  work_mem = 10 GB kB

  This syntax gives 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

  This syntax gives the error:

  cycle in equation; GUC "effective_cache_size" already seen

• Mixing unit types, for example:

  work_mem = 10kB * 8s

  This syntax gives the error:

  both size and time units used in calculation; cannot proceed