Oracle Cloud Infrastructure Documentation

Managing OCI Database with PostgreSQL Configurations

Create and use lists of database configuration variables for an OCI Database with PostgreSQL database system.

Configurations are lists of variables that you can set to tune or optimize a database. When you create a database system, a set of default variables that are optimized based on the database system configuration are applied. You can adjust the default values, save them as a custom configuration list, and apply them to other database systems.

Important

Because the OCI Database with PostgreSQL service optimizes configuration variables for you, only advanced users might need to change and manage them. For more information, see PostgreSQL reference manual.

Manage configuration lists using the following tasks:

Variables

Each configuration contains a list of variables. Variable types include:

  • User variables, which can be changed in a custom configuration list. You can edit user variables when you create or copy a configuration. Some user variables are default user variables. You can't remove these default user variables from a configuration.

    • Dynamic variables, which can be changed without restarting the database.
    • Static variables, which require PostgreSQL to be restarted to be changed.
  • System variables, which can't be changed. System variables are defined according to the shape or requirements of the database.

To view the values associated with a specific configuration variable, see Getting Default Configuration Details and Getting Custom Configuration Details.

More Information for Configuration Variables

oci.pagecache_size

The community version of PostgreSQL relies on the Linux OS to manage memory on the system. Among other items, it uses Linux kernel page cache to avoid going to the disk for most database pages. Linux page cache is necessarily very general and isn't customized for PostgreSQL.

In contrast, OCI Database with PostgreSQL does its own memory management. OCI Database with PostgreSQL implemented a custom page cache and doesn't rely solely on the Linux page cache. The custom page cache is implemented fully in user land, and is more effective for PostgreSQL workloads (such as avoiding double buffering). OCI Database with PostgreSQL pre-allocates a fixed portion of memory for this custom page cache and will not use it for any other purposes.

OCI Database with PostgreSQL is, like the community version, setup to allocate some amount of memory for shared_buffers.

Both the oci.pagecache and shared_buffers can be controlled by using configuration variables.

  • oci.pagecache_size controls the amount of memory allocated to the OCI Database with PostgreSQL custom page cache. This variable is defined in MiBytes.
  • shared_buffers controls the amount of memory allocated to PostgreSQL shared memory for data pages. This variable is defined in block size units (8KiB).

The default values are approximately 50% memory for oci.pagecache_size and 25% memory for shared_buffers. With default settings, about 75% of the total memory of the database system is always allocated. Metrics show this amount as used, but this memory is used for PostgreSQL data buffers as intended and doesn't result in any negative effects.

Flexible Configurations and Integer Variables

Some user variables are specified by integer values. If you're using a fixed configuration, you might set a variable such as max_connections to a specific value. That value can't be changed later, and might make the configuration incompatible with other database systems.

A flexible configuration lets you use integer expressions for variable values. Variables using integer expressions can flexibly scale along with database systems of differing hardware configurations. For example, instead of a set value for max_connections, you can tie it to the memory of the instance:

min(DB_INSTANCE_MEMORY_IN_BYTES/76251136,5000)

OCI Database with PostgreSQL supports the following variables in integer expressions:

  • DB_INSTANCE_OCPU_COUNT
  • DB_INSTANCE_MEMORY_IN_BYTES
  • DB_INSTANCE_MEMORY_IN_KILOBYTES
  • DB_INSTANCE_MEMORY_IN_MEGABYTES
  • DB_INSTANCE_MEMORY_IN_GIGABYTES
  • DB_INSTANCE_MEMORY_IN_TERABYTES

Required IAM Policy

The following policy statements allow a group of administrators to manage OCI Database with PostgreSQL resources:

Allow group <postgresql-admin-group> to manage postgres-db-systems in compartment <database_compartment>
Allow group <postgresql-admin-group> to manage postgres-backups in compartment <database_compartment>
Allow group <postgresql-admin-group> to manage postgres-configurations in compartment <database_compartment>
Allow group <postgresql-admin-group> to read postgres-work-requests in compartment <database_compartment>

For more information, see OCI Database with PostgreSQL Policies.