Oracle Cloud Infrastructure Documentation

Managing OCI Database with PostgreSQL Configurations

Use configurations to manage variables and enable extensions for an OCI Database with PostgreSQL database system.

Configurations are lists of variables and enabled and configured extensions that you can set to tune, optimize, or extend a database. Sets of default variables are optimized for the hardware shapes associated with database systems. Many extensions are automatically enabled for database systems.

Administrators can set more variables and enable extra extensions by creating a new custom configuration or copying an existing configuration. After a configuration is assigned to a database, any enabled extensions are available to the database admin.

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.

Variables

Shape-specific default configurations include variables that have been optimized to provide good out-of-the box performance and behavior. You can customize these variables when creating a new custom configuration or copying an existing configuration and apply the custom configuration to a database system.

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 database configurations:

Allow group <postgresql-admin-group> to read compartments in tenancy
Allow group <postgresql-admin-group> to manage postgres-configuration in [ tenancy | compartment <compartment_name> | compartment id <compartment_OCID> ]
Allow group <postgresql-admin-group> to use tag-namespaces in tenancy

For more information, see OCI Database with PostgreSQL Policies.