This document describes concerns and limitations to be considered when allocating resources for your a9s PostgreSQL plans.
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.
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
/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
max_storage_limit consider never crossing the a9s Parachute limit, as it would be inefective.
Max Locks per Transaction
We currently hardcode the
64, this affects the number of tables that can be locked inside the
same transaction. So depending on the
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.
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.
The a9s Backup Agent is using PostgreSQL tools
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.
The operating system allocates a portion of the memory during initialization and provides access to
this memory as a file system (tmpfs)
/dev/shm device. By default, the operating system will allocate 50% of the RAM for shared
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
JOINbefore writing to the temporary files on disk. This configuration is related to
max_connections, since every new connection can allocate a new
work_memsize 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
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
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
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 Deletetion 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.
In the case of a9s PostgreSQL service instances, the databases can be created
so that the
cfuser user can access them, this is done in the same
manner that the default database used by the a9s PostgreSQL service instances
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 to the configuration.
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
vcap user. 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
vcap user 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.
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
database, otherwise, a restore operation may fail with an 'already exists' error
demanding manual intervention to manually remove any content of the database.