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 likeORDER BY
,DISTINCT
andJOIN
before writing to the temporary files on disk. This configuration is related tomax_connections
, since every new connection can allocate a newwork_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 likeVACCUM
, adding indexes or foreign keys can consume.autovacuum_work_mem
: If specified, otherwiseVACUUM
usesmaintenance_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% ofshared_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.
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.
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.
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.