Unit 5.3: Relational Databases (Managed PostgreSQL / MariaDB)
Introduction
The relational tier is where FinCorp's customer accounts and payment records live, and where a wrong decision costs data rather than minutes. Three decisions dominate: how many committed transactions a failover may lose, where read capacity comes from when the standbys do not serve reads, and how the data survives the loss of a whole cluster.
The managed engines answer each one in a specific way. DBaaS for PostgreSQL offers two replication modes and promotes a standby automatically; DBaaS for MariaDB replicates asynchronously only. Neither is reachable from the internet by default, and data moves in and out by dump and restore. This unit designs the tier around those facts and ends by building FinCorp's portal database in the DCD.
1. Replication Mode: Deciding How Much a Failover May Lose
1.1 The Cluster Shape
A PostgreSQL cluster has one primary and one or more standby nodes that run a copy of the active database, so a cluster of n instances has n-1 standbys. A cluster has a maximum of five instances. In every mode, a transaction is first committed on the leader and then replicated to the standbys. The mode decides when the client gets its confirmation.
1.2 Asynchronous (the Default)
In asynchronous mode, the primary confirms a transaction after writing it to its own disk and replicates in the background. The benefit is lower latency. The cost is that recent transactions might be lost when a standby is promoted: non-replicated data is lost during a failover. The lag between leader and standby tends to be a few milliseconds, so the recovery point objective (RPO) of a failover is the replication lag at the moment of failure: usually milliseconds, but not bounded by a guarantee. Monitor pg_replication_lag; for critical workloads, alert when it exceeds 30 seconds.
1.3 Strictly Synchronous
In Strictly Synchronous mode, a commit is confirmed only after a synchronous standby has it, and the primary is not allowed to continue alone. If no standby is available, no writes are accepted: the mode sacrifices availability for replicated durability. Data loss cannot occur during a failover, and data is not lost even if the primary's storage fails at the same time as all standbys.
The mode has a minimum cluster size. It needs at least three instances, and at least two nodes must be operating to accept writes. With three instances, the cluster keeps accepting writes while one node is down; with every further node lost, it stops writing rather than risk a loss.
The following table compares the two modes:
| Aspect | Asynchronous | Strictly Synchronous |
|---|---|---|
| Primary failure | A standby will be promoted if the primary node becomes unavailable. | Only standby nodes that contain all confirmed transactions can be promoted. |
| Standby failure | No effect on primary. Standby catches up once it is back online. | At least one standby must be available to accept write requests. There is a short delay in transaction processing if the synchronous standby changes. |
| Consistency model | Strongly consistent (except for lost data.) | Strongly consistent (except for lost data.) |
| Data loss during failover | Non-replicated data is lost. | Not supported. |
| Data loss during primary storage failure | Non-replicated data is lost. | Not supported. |
| Latency | Limited by the performance of the primary. | Limited by the performance of the primary, the strictly synchronous standby, and the latency between them (usually below 1ms). |
As shown above, "Not supported" in the data-loss rows means that the mode does not lose data in that event. The latency penalty is a constant overhead per commit, independent of the transaction's size or duration, so it weighs most on many small transactions.
1.4 MariaDB: Asynchronous Only
DBaaS for MariaDB clusters run 1 to 5 instances, and replication between the primary and secondary instances is always asynchronous. A failover promotes a replica automatically, and data committed only on the primary may be lost. If a workload needs zero data loss on failover, it belongs on PostgreSQL in Strictly Synchronous mode; if it must stay on MariaDB, accept the replication-lag RPO and protect against storage loss with backups.
2. Read Scaling Without Read Replicas
2.1 Why More Nodes Do Not Mean More Reads
In a PostgreSQL cluster, the standbys run as warm standbys and read connections to them are not allowed. Horizontal scaling provides high availability; it does not increase performance. The same holds for MariaDB. A fourth or fifth instance therefore protects against failure and adds cost, but the primary still serves every query.
Vertical scaling raises capacity. When you change cores or RAM, the system creates a new node for each instance, replaces the standbys first and then the primary, which results in one switchover. That switchover terminates application connections and aborts running queries, so scale outside peak hours.
2.2 The Connection Ceiling
The number of connections is derived from RAM:
| RAM Size | max_connections |
|---|---|
| 4GB | 384 |
| 5GB | 512 |
| 6GB | 640 |
| 7GB | 768 |
| 8GB | 896 |
| > 8GB | 1000 |
The platform reserves 11 of these for internal use. Above 8 GB the ceiling stays at 1000, so more RAM no longer buys connections. A horizontally scaled compute tier (Unit 4.3) multiplies connection pools quickly: twenty replicas with fifty connections each already reach the ceiling.
2.3 The Native Pattern: Pooler Plus Cache
Read scaling is composed from two parts (Unit 1.3). The managed connection pooler (pgbouncer) lets many client connections share fewer database connections. You select it per cluster:
- Transaction: a connection is assigned for one transaction and returned to the pool when it ends. Use it for stateless request handling.
- Session: a connection is assigned for the client session and reused across its transactions. Use it when the application relies on session state.
- Disabled: every request opens its own connection.
The pooler raises how many clients a cluster can serve, not how many queries the primary can answer. To take repeated reads off the primary, add DBaaS for In-Memory DB as a cache in front of it (Unit 5.5). Unit 2.4 compared the cost: a small cache instance costs less than scaling every node of a three-node cluster.
3. Failover and Recovery Across Sites
3.1 Inside the Cluster
Failover is automatic. When the primary fails, a standby is promoted and the system provisions a replacement node. The new leader sends a gratuitous ARP packet so that clients reach it on the same address, but open TCP connections are reset, and in a planned switchover the server closes them. Clients must therefore reconnect: use an exponential back-off retry with an initial immediate retry. Uncontrolled disconnections can also occur during maintenance and configuration changes.
3.2 Across Clusters and Sites
Replication runs between the instances of one cluster, and the instances are placed in the location of the cluster's LAN. A cluster's protection against losing its site is therefore its backup. Backups combine a daily base backup with continuous Write-Ahead Log (WAL) archiving, and are stored in the Object Storage location you choose as the backup location; choose one outside the cluster's region for off-site backups. WAL files are archived to local disk first and uploaded to Object Storage every 5 minutes, and only then are they available for point-in-time recovery (PITR). The RPO of a site loss is therefore the WAL not yet uploaded: up to about five minutes.
Recovery from a site loss is a new cluster created from a backup with a recovery target time, followed by a DNS change. A Cloud DNS record answers with whatever was last written to it, so your monitoring, running outside both sites, detects the failure and updates the record through the API (Unit 3.7). Rehearse the whole path, including the time to create and fill the new cluster, because that time is your recovery time objective.
3.3 Point-in-Time Recovery and Its Constraints
Backup retention is 1 to 365 days, with a default of 7. A restore brings the data up to, but not including, the recovery target time. An in-place restore uses only active backups of that cluster, and only one restore can run at a time. Because WAL shipping continuously catches up with the workload, stop the workload before a recovery if the new cluster must hold every last change. A major version upgrade is irreversible and creates a backup automatically; take a dump before upgrading if you need a way back.
4. Private Access and Migration
4.1 Private Endpoint Only
DBaaS clusters connect only to private networks. Each cluster attaches to a LAN of a VDC with a private IP address, and clients connect through the cluster's DNS name. Some ranges are blocked for the service: 10.208.0.0/12, 10.233.0.0/18, 192.168.230.0/24 and 10.233.64.0/18. Take the cluster's address from the range .2 to .9 that the address plan of Unit 3.1 reserves for managed services, so that it cannot collide with a DHCP-assigned VM. Every client needs a NIC on the cluster's LAN: FinCorp gives each portal server a second NIC on fc-data for its database connections.
External access is a deliberate design, not a setting. A Network Load Balancer with a reserved public IP and a TCP forwarding rule on port 5432 can act as a reverse proxy to the cluster's private address. For administrators, the VPN of Unit 3.6 to a management host with a NIC on fc-data is the narrower choice.
4.2 Dump and Restore
Data moves into a managed cluster with the native tools: pg_dump with psql or pg_restore, and mariadb-dump for MariaDB. The initial PostgreSQL user has CREATEDB and CREATEROLE but no superuser rights, and superuser access is not available, so pg_dumpall does not work. A standard dump includes GRANT statements but not user definitions: create the users and groups on the target first, or the import fails when it applies permissions. Plan the cutover as a write freeze: stop writes, dump, restore, verify, switch the application.
DCD Implementation Walkthrough
You will create FinCorp's portal database fc-pg-portal on the private fc-data LAN of fc-prod-fra-core: three instances in Strictly Synchronous mode, the transaction pooler, off-site backups and a fixed maintenance window. This realises the zero-loss failover decision of Section 1.3 and the read-scaling pattern of Section 2.3.
Prerequisites: the VDC fc-prod-fra-core with the fc-data LAN and at least one VM on it (Unit 3.1); an account with the Access and manage DBaaS privilege; the address .2 on fc-data reserved for the cluster.
Build goal: Build a multi-node private cluster with replication mode, maintenance window, and connection details.
Steps (in the Data Center Designer):
- Go to Menu > Databases > PostgreSQL and select Create cluster. Check the Resource allocation section against your contract limits.
- Under the cluster properties, enter the Cluster Name
fc-pg-portaland select the Frankfurt Location. The location cannot be changed after creation. - In Cluster configuration, select the PostgreSQL Version, enter
3Instances, and set Synchronization mode to Strictly Synchronous. - Set Connection Pooler to Transaction.
- Select a Backup Location outside Frankfurt, and set Backup retention (days) to the period FinCorp's risk office requires, between 1 and 365.
- In Instance configuration, set the Number of CPUs and RAM Size per instance; above 8 GB of RAM, the connection ceiling is 1000. Enter a Storage Size of at least 100 GB, the size recommended for optimal performance, with margin for WAL and logs.
- In Network configuration, select
fc-prod-fra-coreas the Virtual Data Center,fc-dataas the VDC LAN, and the.2address as the Private IP. - In Maintenance period, select a Day and a Start Time (UTC) in the lowest-traffic period. Maintenance runs in a 4-hour window.
- In Observability, turn on Enable logs and Enable metrics; they take effect once the observability services are activated for the contract (Unit 7.2).
- In User Creation, enter the Username, Password and Database Name. Store the password in FinCorp's secret store, not in the runbook.
- Select Save. The state shows PROVISIONING and then AVAILABLE. Copy the DNS Name from the cluster details for the application configuration.
Common mistakes:
- Planning Strictly Synchronous on fewer than three instances. Three instances is the minimum, and two nodes must be operating to accept writes.
- Adding instances to fix slow reads. Standbys do not serve reads; add the cache and the pooler.
- Keeping the backup location in the cluster's own region. A site loss then takes the backups with it.
- Giving the database an address from the DHCP range. Use the managed-service range .2 to .9.
- Opening the database to the internet "temporarily" through an NLB. Use the VPN for administrators.
- Running a major version upgrade without a separate dump. The upgrade cannot be reversed.
Summary
The relational tier is decided by three numbers: the RPO of a failover, the read and connection capacity of one primary, and the RPO and recovery time of a site loss. Strictly Synchronous mode with at least three instances gives zero-loss failover; asynchronous mode trades milliseconds of possible loss for lower latency. Standbys add availability, not reads, so the pooler and the cache carry read scaling. Across sites, recovery is a new cluster from off-site backups plus a DNS change your monitoring makes.
Key Points:
- PostgreSQL offers Asynchronous (default) and Strictly Synchronous replication; MariaDB is asynchronous only.
- Strictly Synchronous needs at least three instances and two operating nodes to accept writes.
- Standbys do not accept read connections; horizontal scaling does not increase performance.
max_connectionsfollows RAM up to 1000, with 11 reserved; the managed pooler stretches it.- WAL reaches Object Storage every 5 minutes; backup retention is 1 to 365 days.
- Clusters are private; migration uses
pg_dumpormariadb-dump, neverpg_dumpall.
Important Terminology:
- Recovery point objective (RPO): the amount of committed data a failure may lose, measured in time.
- Warm standby: a replica that holds a current copy for promotion but does not serve queries.
- Write-Ahead Log (WAL): PostgreSQL's change log, archived continuously to make point-in-time recovery possible.