a9s PostgreSQL Resources Considerations

This document describes concerns and limitations to be considered when allocating resources for your a9s PostgreSQL plans.

Differences Between Versions

Due to how logind handles shared memory cleanup for regular users when RemoveIPC is enabled, a9s PostgreSQL 15 and above runs the main PostgreSQL processes under a system user named postgres. Older versions run the main process under the vcap user.

Max Connections

a9s PostgreSQL allows the user to specify the number of supported max_connections. However, this configuration depends on the amount of memory and file descriptors. Each connection allocates a buffer for itself (work_mem) in a way that the total amount of consumed memory is number_of_connections * work_mem, the sum of this should not go over the shared_buffer too much on the penalty of performance degradation when the number of connection increases.

By default, the a9s PostgreSQL overcommit this value, considering that on average, connections do not execute big queries that could use up all the work_mem, our default configuration considers that only half of the reserved work_mem is used per connection on average.

In any case, the bigger the number of connections, the less performant your queries will be, and more memory will be needed to improve performance.

It is also important to notice that each connection uses at least one file descriptor. Currently, a9s PostgreSQL instances use a maximum of 1024 open file descriptors, if a higher number of files are open, the a9s PostgreSQL instance will present unpredictable behavior.

Replication Slots

a9s PostgreSQL clusters use replications slots to achieve replication stream between the primary and the standby nodes. Replication slots can also be used by several applications and can be created using the REPLICATION role privilege.

If for any reason, a replication slot is marked as inactive, the upstream node will keep the WAL files related to the inactive replication slots. This will increase the persistent disk storage usage (/var/vcap/store) and if no action is taken, they might occupy the whole available space, which can cause PostgreSQL to break.

a9s PostgreSQL ships with a cleanup replication slots routine that drops slots inactive for a configurable amount of time, or if the storage usage hits more than an amount of the disk. See the Replication Slots Cleanup page for more information.

Note: keep in mind that this means that if a standby node (or any other replication slot) is inactive for too long, a huge amount of the written data may be occupying the persistent disk. Keep also in mind that when a9s Parachute detects that 80% (default) of the persistent disk storage is used, all processes will shut down, leaving your cluster in an unhealthy state. So when configuring the max_storage_limit consider never crossing the a9s Parachute limit, as it would be ineffective.

Max Locks per Transaction

We currently hardcode the max_locks_per_transaction with 64, this affects the number of tables that can be locked inside the same transaction. So depending on the max_connections, if you are using a huge number of tables, for example, you might see an error like:

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

This is especially a concern when running logical backups, since pg_dump locks all tables during the operation, and if there is a huge number of tables it will not be able to execute the backup with success.

Disk Space

Since PostgreSQL uses WAL files to store operations on the disk some of the disk space needs to be empty to ensure proper function; however, the amount of reserved disk space depends heavily on the configuration. Out of experience, around 35% of the total disk space should be empty, but of course, it depends more than anything on the cluster usage and disk size.

You must also consider the replication slots cleanup configuration if changing the values for a9s Parachute.

Memory Usage

Backup

The a9s Backup Agent is using PostgreSQL tools pg_dump and pg_basebackup for the creation of backups. Due to the fact that we are streaming the backup content directly to an object store, we can not control how much memory it will use, and therefore there is a risk of the backup process using too much memory during a backup.

There are expected side effects in case the backup process uses too much memory, like not enough memory for the PostgreSQL process in a given moment, causing performance issues or even breaking the main process. For this reason the memory usage should be monitored.

The current memory usage can be seen with the metrics and via the a9s Service Dashboard.

Shared Memory

The operating system allocates a portion of the memory during initialization and provides access to this memory as a file system (tmpfs) in the /dev/shm device. By default, the operating system will allocate 50% of the RAM for shared memory.

PostgreSQL makes use of this memory for:

• shared_buffers: This is the amount of memory that the database server uses for shared memory buffers.
• work_mem: specifies the amount of memory that will be used by the operations executed in a connection like ORDER BY, DISTINCT and JOIN before writing to the temporary files on disk. This configuration is related to max_connections, since every new connection can allocate a new work_mem size in memory for it to use.
• temp_buffers: The temporary buffers are used to store the temporary tables used in each session. This parameter sets the maximum amount of memory for this task.
• maintenance_work_mem: This is the max memory that an operation like VACCUM, adding indexes or foreign keys can consume.
• autovacuum_work_mem: If specified, otherwise VACUUM uses maintenance_work_mem.
• wal_buffers: The amount of shared memory used for WAL data that has not yet been written to disk. The default setting is 3% of shared_buffers.

You need to choose the appropriate plan configuration thinking on the service instance usage considering the parameters above.

You can also check this article for more information, and of course the official PostgreSQL documentation about every property described above.

Note that the a9s PostgreSQL BOSH release also configures kernel.shmall and kernel.shmmax to use 25% of the total memory.

Issues related to not enough shared memory can be be logged by PostgreSQL as:

ERROR:  could not resize shared memory segment "/PostgreSQL.1423809149" to 4194304 bytes: No space left on device

Database Deletion and Ownership

There are three ways to create databases using a9s PostgreSQL, but a9s PostgreSQL never deletes databases. In case a database needs to be deleted, it needs to be done manually.

Internal Creation

In the case of a9s PostgreSQL Service Instances, the databases can be created so that the admin/cfuser user can access them, this is done in the same manner that the default database used by the a9s PostgreSQL Service Instances is declared.

On the other hand, standalone deployments (e.g. a9s-pg) declare databases using different internal properties, as these databases will be used by the anynines framework, thus differing from a normal a9s PostgreSQL deployment, as the ownership is enforced by the configuration.

Custom Databases

The third method is the Custom Databases one. If the user is configured with the CREATEDB privilege, this user can create any custom database with the CREATE DATABASE command.

PostgreSQL User Information

The versions differ in the user that runs the main PostgreSQL processes.

• a9s PostgreSQL 13 and below use the vcap user.
• a9s PostgreSQL 15 and above use the postgres system user.

Note 1

a9s PostgreSQL never deletes a database (except when executing a restore or disaster recovery). If a database is added and removed from a standalone deployment, it will have the ownership reassigned to the user running PostgreSQL. The same is valid in the normal deployment, so if a database in case it is modified. You need to watch for this behavior as this will not release storage.

Note 2

If any database is declared via the standalone's internal creation, all other databases not declared this way will have their ownership reassigned to the user running PostgreSQL during any restart of a node of the deployment. The same applies in normal deployments. Pay special attention to the custom databases if standalone's internal creation is used since they will have the ownership changed during an update. If standalone's internal creation is not used, a9s PostgreSQL will not update the ownership of custom databases.

The postgres Database

PostgreSQL creates a default database named postgres during bootstrap. This database is reserved for internal administrative use only and MUST not be used to store objects.

a9s PostgreSQL backups are created with the pg_dumpall tool and the dump does not include clean-up commands for this database and objects there will be preserved across restore operations. Therefore, objects must not be created in the postgres database, otherwise, a restore operation may fail with an 'already exists' error demanding manual intervention to manually remove any content of the database.