Configuring Yandex Managed PostgreSQL: typical cluster failures
A cluster on a managed service rarely falls due to lack of resources — more often it is brought down by the default configuration copied from a test environment.
Configuring Yandex Managed PostgreSQL for production begins with connection limits, timeouts, autovacuum policy, and replica behavior being set for a specific load profile, not "like everyone else."
Connection overflow is the most frequent failure. The application opens a connection per worker thread; at peak their number exceeds max_connections, and the server starts rejecting new clients.
The application receives a connection error, built-in retries hammer the cluster even harder — instead of relief, an avalanche occurs.
Second scenario — query queue under load. Without a limit on query and transaction duration, one heavy report holds locks, other queries queue and wait for release.
From the outside it looks like "the site is slow"; inside, it is growing wait time and cluster degradation on every spike.
Third — replica lag and volume overflow. While the replica cannot keep up applying the change log, the amount of unfinished data grows: if the primary node fails, some confirmed operations are lost.
At the same time, the log and dead tuples consume disk, autovacuum cannot keep up with updates, and on a full volume the cluster goes into read-only mode — writes stop.
All failures are visible in advance by metrics, but usually not where they are looked for. We explain how this is closed by configuration.
What a properly assembled cluster provides: effect in measurable numbers
A production cluster differs from a training one not by the length of the parameter list but by predictability: under load it behaves the same as on the test bench.
The managed service Yandex Managed PostgreSQL takes on the hardware and platform, but the topology, connection limits, backup retention period, and failover rules are chosen by the engineer — these decisions determine the numbers below.
With a synchronous replica in another zone, failover to the standby node fits within tens of seconds and does not lose confirmed transactions. Without it, node failure means restore from a backup: hours of downtime and loss of the latest records.
The difference between these scenarios is the measurable effect of configuration — recovery time after failure is reduced by an order of magnitude.
A whole class of failures related to connection exhaustion also disappears. A pool in front of the database together with a limit on concurrent queries on the application side holds the peak: during a traffic surge, queries wait in a queue rather than being rejected.
The share of rejected connections falls from percent to fractions of a percent, and the application stops returning errors exactly when load is at its maximum.
Peak load stops being a lottery: resource limits, read replicas, and disk autoscaling set the boundaries the cluster does not exceed.
In the report this is visible as four verifiable values — recovery time, share of rejected connections, downtime duration over the period, and the number of incidents of the eliminated class.
PostgreSQL deployment and fault-tolerance options
The deployment scheme is a choice of the failure class you agree to accept, not just a performance question. The managed cluster Yandex takes on backup and role switching, but it does not solve zone availability and read offloading for you — that is a configuration question.
| Option | When it fits | Limitations |
|---|---|---|
| Single managed cluster node | Internal services, test environments, load without continuity requirements | Node stop means downtime during recovery; no read offloading, reports compete with production queries |
| Primary node + read replica | Reads prevail over writes, fast return to the last stable point is needed | Replication is asynchronous: on failover the last segment of transactions is lost; a code error propagates to the replica too |
| Cross-zone placement | The service is critical, must survive a zone failure without manual intervention | Write latency is higher — noticeable on frequent small transactions; resource reservation is more expensive |
| Separate environment for analytics | Heavy queries and exports interfere with production | Data lags, requires its own transfer scheme and desynchronization control |
Separately, we check the limitations of the scheme: available extensions, partitioning of large tables, row-level permissions, and who owns objects during failover.
We configure backups with point-in-time recovery and once a quarter restore a copy into a separate environment — a copy that has never been restored is not considered working.
Work order: from load audit to runbook handover
A managed cluster does not become productive just because it was deployed: the cloud covers hardware availability and engine maintenance, but does not protect against locks, connection pool exhaustion, and individual queries that bring the database down under real traffic.
We go from measurements to changes — for PostgreSQL, MySQL, and MongoDB the sequence is the same, only the metrics and parameter set differ.
- Load audit — collect statistics for a representative period: peak hours, number of connections, table growth rate, lock waits.
- Query profile — pull the top by total time and frequency, find index-less calls and queries against large tables.
- Schema and indexes — rebuild hot tables, remove duplicate indexes; run every change on a data copy and compare the execution plan before and after.
- Parameter tuning — bring memory limits, log settings, timeouts, and connection pool size in line with the measured profile, not with default typical values.
- Failover check — forcibly switch the role to the replica and measure unavailability time and the volume of lost transactions.
- Backup and recovery — verify not that copies exist, but restoration in a separate environment to the required point.
- Runbook handover — configuration description, fault-tolerance scheme, alert thresholds, and procedures for typical operations: role promotion, disk expansion, schema migration.
After handover, changes are made according to the described procedure, not "by hand" in the panel: the configuration remains reproducible, and an incident is analyzed by metrics.
What remains with you: configurations, schemas, access, runbooks
We do not leave the result in a chat and in one engineer's head. Everything done during Yandex Managed PostgreSQL configuration is recorded in artifacts: they can be handed to a new team, put into version control, and reapplied during the next rollout or environment migration.
- Configuration files. The cluster, replicas, parameter groups, backup schedule, and autoscaling rules are described declaratively — the next rollout is not reassembled from memory.
- Placement scheme. Availability zones, subnets, application connection endpoints, and the traffic path to the database: you can see where the fault-tolerance boundary lies and what happens if a zone is lost.
- Access policies. Database roles, permission separation by service, network rules, the procedure for issuing and rotating credentials, a separate environment for reads and analytics.
- Operations runbook. Minor version updates, backup restore checks, index and statistics maintenance, on-call actions during degradation.
- Documentation. Cluster datasheet, connection map, instructions for typical incidents, and the procedure for rolling back to the previous schema version.
- Monitoring and alerts. Metrics for load, connections, and replica lag, trigger thresholds, and notification recipients.
For MySQL and MongoDB the set is the same — only the parameter groups and replication method change.
Case: a cluster for peak load after migration
The customer deployed a Yandex Managed PostgreSQL cluster for an environment migration: the data was moved, but the parameters remained from the test environment, and all application connections went to a single node.
At the evening peak, write response went above one second, the replica lagged, background jobs failed by timeout and were restarted manually.
Baseline
Before the rebuild we recorded the numbers: five to seven manual interventions per week, replica lag up to 40 seconds, 1.8 seconds at the 95th percentile of queries, and service failure when the number of connections exceeded one thousand.
The cause was not load but the scheme: one read node, default autovacuum settings, and no connection limits.
What was changed
We separated the flows: writes to the primary node, reports and exports to separate replicas, application connections limited by number and lifetime.
Autovacuum was configured based on actual row changes, indexes were added for heavy queries, alert thresholds were tied to replica lag. Failover on node failure stopped being a manual operation.
Result
Replica lag stays within one second, 95th percentile response is 0.2 seconds, manual interventions over three months — zero.
The peak profile grew threefold, and the cluster does not hit the ceiling: headroom for connections and queries is visible on the graphs in advance, before users notice slowdown.
How does a managed cluster differ from a self-managed setup?
The managed service takes on the operations layer: deployment and initial configuration, replication, backups, minor version updates, replacement of a failed node, collection of basic metrics.
The responsibility boundary runs along the platform and hardware — the provider is responsible for cluster availability and replica integrity. This option (Yandex Managed PostgreSQL) removes from the team work that does not create product value.
The team's area of responsibility begins higher: data schema, indexes, DBMS parameters, connection pool, role permissions, migration runbook. Separately — checking recovery from a backup in an isolated environment: without it, the mere presence of copies guarantees nothing.
A self-managed installation leaves all of the above on us plus operations, but gives full control over the version and settings.
Maintenance without downtime is organized similarly, but costs differently. The platform updates nodes one by one and moves traffic to the replica automatically; in a self-managed setup, the same operation is performed by the on-call engineer according to a pre-written runbook.
The difference shows not on a calm day but at the moment of node failure at night or during peak load.
Self-managed setup wins where non-standard extensions, specific builds, or strict requirements for isolation and placement next to the application are needed.
The managed service wins where predictable maintenance and shipping the product without an in-house on-call shift are more important. The choice follows the responsibility boundary: what the team is ready to keep on itself and what is more profitable to give to the platform.
Common technical doubts of engineering teams
Teams arrive with the same set of doubts: can an already running cluster be touched, how to separate data access permissions, and where is the point after which recovery control stops being a formality. We configure Yandex Managed PostgreSQL and answer about behavior under load, not about theory.
We already have a running cluster — will it have to be rebuilt?
No. First, inventory: which databases and roles are in use, how backups are configured, which queries create the peak. We make changes one at a time and verify them on the replica — production traffic is not interrupted.
How do you separate data access permissions?
We describe roles by task: the application writes only to its schema, analytics reads, migrations run under a separate account. Permissions are minimal for each role — compromising an application key does not open the entire cluster.
How do you know recovery will work?
Recovery control is drills, not a checkbox in the panel. We deploy a copy in a separate environment, verify integrity, and measure bring-up time. If the copy does not come up, we learn about it during drills, not at the moment of an outage.
Do parameters affect behavior at peak?
Yes, and more than it seems. Memory settings, connection count, and log writes determine how the database handles the peak. We select them for the load profile and change them one at a time — the effect is visible in metrics.
What does our team get?
Knowledge transfer: we show where to change roles, how to read metrics, and what to do during degradation. After that, the team maintains the cluster itself and understands when to call us.
Let's discuss the task and get back with an estimate and plan
An infrastructure estimate is never accurate "by eye": the difference between a test environment and production PostgreSQL in a managed service is replication, scheduled backups, a fault-tolerant configuration, and separation of access roles.
That is why we first collect source data, and only then return with a plan and timelines, not with an "from and to" range.
What we need from you to discuss the task specifically:
- List of databases and their purpose: which environments are served, which queries prevail — transactional or analytical.
- Load profile: peak values for connections and operations, at what hours they occur.
- Current placement scheme: where the databases live now, how applications establish connections, whether there are intermediate layers such as a pooler.
- Availability and recovery requirements: acceptable downtime window, backup retention depth, target bring-up time after failure.
- Access rules: how permissions are issued to applications and administrators, whether network isolation and action logging are needed.
- Data volume and growth dynamics — based on them we calculate instance size and retention policy.
- The person responsible on your side who approves changes in the production environment.
We return with an estimate and plan: a placement scheme with justification for the chosen configuration, a list of configuration and migration work, a breakdown of work timelines by stage, and the points where your approvals are needed. After that, the start of work takes one agreed interval.