PostgreSQL Support and Consulting

We configure autovacuum thresholds per table #

When PostgreSQL updates a row it does not delete the old version; it writes a new row version and marks the old one dead. That design, called multi-version concurrency control (MVCC), keeps reading transactions from waiting on writing ones. In return, dead rows have to be cleaned up regularly, and the autovacuum process does that cleaning.

With its default settings autovacuum works without trouble on small tables. The problem appears as the table grows. The default threshold starts the cleanup once roughly twenty per cent of the rows in the table have become dead. On a table of a hundred million rows, that threshold means maintenance starts after twenty million dead rows have accumulated. At that volume the cleanup both takes a long time and noticeably affects the production load. The right approach is to lower the thresholds per table — to run maintenance more often, in smaller pieces.

Another consequence of dead rows left uncleaned is bloat. Table and index files keep growing on disk, queries have to scan more disk blocks to read the same data, and the planner starts producing wrong estimates. We measure bloat with pgstattuple and compact the tables we find with pg_repack, which runs without stopping production.

The harshest consequence is transaction ID wraparound. PostgreSQL keeps transaction IDs within a limited range, and autovacuum freezes those IDs regularly to keep the range from running out. On an unmaintained system, once the range is exhausted the database stops accepting new writes in order to protect data integrity. The only way out of a wraparound is maintenance carried out during a planned outage, and the length of that outage grows in direct proportion to the data volume.

On every PostgreSQL system we take on, the first thing we measure is which tables accumulate the most dead rows, when they were last autovacuumed, and how much room is left in the transaction ID range.

We build the connection pool around how the application behaves #

PostgreSQL starts a separate operating system process for every client that connects to it. The memory and scheduler cost per process grows linearly as the connection count rises. On systems reaching several hundred concurrent connections, the server starts spending its time managing connections rather than doing the real work.

The fix is not to raise max_connections but to put a connection pool in place. PgBouncer sits between the application and PostgreSQL and serves incoming connections over a far smaller number of backend connections.

Choosing the pool mode is the real decision in that installation:

• Session mode keeps a connection bound to the same backend connection until the client closes it. It is the safest mode from the application’s point of view, and the gain stays at its lowest.
• Transaction mode returns the backend connection to the pool at the end of every transaction and gives the highest efficiency. In return, features that hold session-level state — advisory locks, session variables, LISTEN/NOTIFY and temporary tables — do not work reliably in that mode.

We measure whether the application uses those features before the installation. On a system moved to transaction mode without that measurement, the errors that surface are of the kind that is hard to reproduce one by one.

We plan version upgrades around the downtime budget #

PostgreSQL publishes a new major version every year, and each major version is supported for five years from its first release. No security patches are published for versions past end of support. On production systems we put the version plan on a calendar and run it as part of the support scope.

An upgrade is done by one of two methods, and the choice depends on the acceptable downtime:

• pg_upgrade, run in link mode, does not copy the data files. The upgrade time depends on the number of objects in the database rather than on the data volume, and it is far shorter than a copying method. In return a short planned outage is taken, and a backup made beforehand is needed for the rollback.
• An upgrade over logical replication brings the new version up on a separate server and establishes the data flow. The outage lasts only as long as repointing the application. It should be known that sequences and schema changes are not replicated, and the migration plan has to handle those separately.

We choose extensions and the distribution from what the system needs #

A significant part of PostgreSQL’s capability arrives through extensions, and which one is installed is determined by what the system has to do. On the operational side we use these regularly: pg_stat_statements for query statistics, pgstattuple for bloat measurement, pg_repack for maintenance, pgaudit for audit logging, and PostgreSQL Anonymizer for data masking and anonymisation.

Encryption at rest is a separate heading, and here the choice of distribution is decisive. Core PostgreSQL does not include that capability. Encryption is provided by the pg_tde component that comes with Percona’s open source distribution, and that component needs the distribution’s own patches. When encryption comes up in an audit, we produce a plan that also covers the move from core PostgreSQL to the Percona distribution.

We run that migration independently of the vendor as well. Every component we use is open source licensed, and our right to install and integrate comes from those licences.

PostgreSQL support on managed cloud services #

We also support organisations using Amazon RDS for PostgreSQL, Amazon Aurora PostgreSQL, Google Cloud SQL and Azure Database for PostgreSQL. On a managed service no true superuser privilege is granted, and the extensions that can be installed are limited to the provider’s permitted list. Parameters such as shared_preload_libraries can only be changed through parameter groups.

Nearly all of the maintenance work this page describes nonetheless applies on a managed service. Autovacuum thresholds are tuned per table, bloat is measured, pg_stat_statements data is analysed, and index design and connection management are handled the same way. The provider manages the hardware, the backups and the infrastructure of the version upgrade. Table maintenance and query design remain the organisation’s own responsibility.

In Aurora PostgreSQL the replication architecture is built on a shared storage layer and behaves differently from standard streaming replication. We design for availability with that difference accounted for.

How we work #

We connect to your systems remotely under a non-disclosure agreement (NDA), through your jump host or your VPN. For our PostgreSQL user we request only the privileges the work requires, and we record every change we apply.

The work starts with measurement. When we take on a PostgreSQL system for the first time, we establish the version and configuration inventory, the autovacuum and bloat picture, the replication topology, the state of backup and restore, the connection profile and the query profile of the workload. Our recommendations are built on that measurement.

On systems whose maintenance we carry, we assess load and slow query measurements in near real time as part of proactive monitoring, and report problems before they surface. The same measurements also feed your capacity and load planning.

Changes destined for production are verified in a test environment first, and a rollback step is prepared in advance for each one. Every change is reported and approved by you before it is applied, and following the principle of change management we apply one change at a time. In addition to our own change record, we ask that a PostgreSQL audit log be kept so everything we do can be followed on the server side.

Related services #

We carry out the same work for MySQL, MariaDB and MongoDB: MySQL Support · MariaDB Support · MongoDB Support

At the connection layer the equivalent on the MySQL side is ProxySQL. Separating read and write traffic solves the same problem: ProxySQL Support and Consulting

Send us your PostgreSQL support request