Cloud SQL setup: where production falls apart
After launch, Cloud SQL looks like a healthy database: default settings handle traffic, graphs are flat, no alerts. But they are designed for development and light load, not production — with a real workload profile they diverge exactly when it is too late to reconfigure.
The first failure point is connection exhaustion. The application opens a connection for every request, the number of its copies grows at peak, and the database stops accepting new sessions: requests queue up, response time grows, some calls fail with an error.
A connection pool and an explicit limit on the application side remove the problem — but only if set in advance.
Degradation under peak load is more subtle. Stale statistics or a changed execution plan push a query into a full table scan, disk throughput is no longer sufficient, and the queue grows at the I/O level.
Indexes, regular statistics collection, and offloading reads to a replica keep response time predictable.
A separate scenario is failure during replica failover. The instance moves to another zone, the application keeps dead connections in the pool, and request retries create a storm on the just-recovered node. Without proper timeouts and reconnection logic, failover turns into noticeable downtime.
Each of these failures is predictable and can be addressed during managed database setup. Below is exactly what we check and configure for this.
What a managed database provides in production as load grows
An unconfigured managed database keeps production running only until the first serious load increase. The instance lives in one zone, if the host fails the database is brought up manually, and service downtime is measured in hours, not seconds of response time.
Database high availability is not a checkbox in a panel, but a synchronous replica in another zone, automatic failover, and a connection proxy in front of the instance.
Cloud SQL, when properly configured, provides recovery time after a failure within one failover: the application survives it by reconnecting its connection pool, without manual actions by the on-call engineer and without losing committed transactions.
Database backup covers the other half of the risks: automatic snapshots together with the change log let you return to a minute before an erroneous update, rather than to the last nightly dump.
Recovery is regularly tested on a separate instance — an untested backup does not count as a recovery point.
The third effect is the speed of environment provisioning: a clone of the production instance gives a test environment in minutes, so schema migrations and new indexes are tested on real data volumes before rollout.
Overall, it is not an individual metric that changes, but the class of failures: manual database bring-up, data loss when a disk fails, and emergency recovery from a dump go away.
Database deployment options: comparing modes
Deployment mode is chosen by workload profile, not by habit. As long as it is short transactions with a predictable number of connections, a managed cloud database (Cloud SQL) removes backup, high availability, and updates from the team.
As soon as reporting appears alongside it, the same instance shares the buffer cache and disk I/O between analytics and production — response time degrades for both.
| Option | When it fits | Limitations |
|---|---|---|
| Managed transactional database | Short transactions, stable query profile, automatic failover to a replica is needed | No access to superuser and filesystem, some extensions are unavailable, maintenance is on the provider's schedule, analytics competes with transactions for memory and disk I/O |
| Analytical engine alongside the transactional one | Reporting and aggregations read the same data as production but must not slow it down | A separate instance with its own monitoring and updates, replication lag, some queries require rewriting for columnar execution |
| Own machine with a DBMS | Non-standard extensions are needed, fine-grained kernel tuning, full control over the version and query plans | Backup, high availability, patches, and recovery are entirely on our side; a configuration error costs production downtime |
Most often, the transactional core is left on a managed database, reporting is moved to an analytical engine, and an own machine is chosen only when requirements for extensions and kernel tuning outweigh the cost of maintenance.
The reverse transition is harder: roles, access permissions, and some queries will have to be rewritten.
Procedure for configuring a managed database under load
Load on a managed database grows unevenly: first it hits the connection limit, then disk operations, then the query plan, which stops matching the actual data distribution. A configuration assembled with defaults and tested on test traffic holds only until the first peak. That is why the work sequence is fixed — from gathering facts to operations regulations.
- Database configuration audit: we capture instance parameters, planner statistics, and top queries by total time and by disk reads. The output is a list of bottlenecks with an estimate of what will yield the most at the next peak.
- Replication scheme: we determine which reads go to the replica, what lag margin is acceptable for reports, and what synchronism mode critical transactions need. We separately document behavior when a node is lost.
- Instance and role configuration: cache for the working data set, autovacuum thresholds, connection limits, and a pool in front of the database. We separate access for the application, analytics, and the administrator.
- Backups and recovery: backup window, retention depth, point-in-time recovery. We test recovery on a copy, not based on the mere existence of copies.
- Load testing: a run with a profile that replicates production in read/write ratio, with peak and slow degradation tested separately. We capture response time and the share of queries outside the target time.
- Handover of the database to operations: metrics and alerts, a regulation for changing parameters, and the procedure for rolling back to the previous configuration and copy. The responsible person on the customer side receives a document, not verbal agreements.
Each stage ends with a verifiable artifact: a report, a diagram, or a test run log. This lets you change the configuration further without us and not lose the logic behind decisions.
Project artifacts: diagrams, access policies, regulations
The project is completed not with a report, but with a set of artifacts that let the database be developed and maintained without us.
We put everything in the customer's repository: configuration is described declaratively, changes are reviewed before being applied, and edit history and authorship are preserved. Below is exactly what remains in your team's hands.
- Database infrastructure diagram — instances, replicas, network segments, placement zones, and service connection points. It shows what will fail if a zone is lost and where traffic will go.
- Access policies and roles — separation into owner, administrator, developer, and service accounts. Applications run under separate users with minimal privileges, and direct human access to production is closed and logged.
- Backup policy — snapshot schedule, retention depth, mandatory recovery testing in a separate environment. It records who brings the database up and within what timeframe in case of data loss.
- Database documentation — instance parameters, engine version, enabled extensions, connection limits, and the procedure for applying migrations.
- Instructions for common operations — storage expansion, changing the instance class, planned switchover to a replica, adding an index on a large table without blocking writes.
- Monitoring and alerts — query latency graphs, connection counts, and remaining space, trigger thresholds, and the incident triage procedure.
Verifiability is the main property of such a set: your engineers deploy the configuration from scratch and compare the result with the documented state, without asking us for explanations.
Case study: database recovery time cut threefold
Initial state: production on Cloud SQL with intensive writes — orders, delivery statuses, payment history.
All connections, including reports and nightly reconciliation, went to the primary database: about 400 concurrent sessions, autovacuum could not keep up with updates to hot tables, and backups were taken in full on a schedule.
Recovery after a failure took up to 75 minutes: restore the copy, manually roll forward some transactions, and reconcile discrepancies — this consumed about 12 hours of on-call engineer time per month.
What we changed
We separated the environments: hot reads and analytics went to replicas through a separate connection pool, while writes remained on the primary instance.
We enabled continuous journaling and point-in-time recovery with a 30-day retention window — rollback no longer depended on the backup schedule.
Order and payment history was moved to AlloyDB, leaving the transactional core in Cloud SQL, and failover was automated based on health checks.
What the result was
Recovery time dropped from 75 to 25 minutes: the engineer selects a point before the failure, the database is brought up from the journal, and manual roll-forward is not needed.
The share of incidents with full production downtime decreased by several times — read degradation is now absorbed by switching to a replica.
The shift's labor costs for recovery work decreased from 12 hours to 1.5 per month, and reporting stopped affecting the primary database's response time.
What to do if the database is already deployed on your own machine?
Migrating an already running database to a managed service does not require a dump and downtime.
The working approach is to bring up the target database alongside the current one and enable continuous change replication, leaving the source server as primary: the application continues to run on its own infrastructure and does not notice the preparation.
This is gradual migration, where the switchover is a separate, short step, not the start of the work.
While synchronization is in progress, a full copy is deployed on the managed side, and we monitor the change application lag.
We separately run checksum verification across tables so that discrepancies surface before the switchover, not in production, and the application's test environment runs on the new database under a real workload profile.
By the time traffic is switched, we reduce the DNS record TTL in advance so that old addresses stop being cached.
We perform the switchover itself in a low-load window: we stop writes on the source database, wait for zero replication lag, move the application to the new address, and only after that enable writes on the managed database.
We prepare the rollback plan before, not during: while monitoring is underway, replication is set up in the reverse direction, so returning takes minutes.
Rollback triggers are measurable — error rate, application lag above a threshold, deviation of query response time from the baseline. We do not shut down the source server: it remains a ready rollback point.
The duration of the transition is determined not by database size, but by how long data verification takes and how quickly replication lag is cleared under peak.
Describe your current configuration and the acceptable window of write unavailability — we will come back with a work procedure.
Frequently asked technical questions about managed databases
Doubts about managed databases rarely concern capabilities — more often they are about guarantees: what happens if a zone fails, how long an update takes, how to verify recovery, and how difficult it is to move to another provider. Below are short answers for each scenario.
What happens if a zone fails?
We deploy the instance in a regional configuration: a synchronous replica lives in another zone and automatically takes over the primary role.
The application survives the failover through the connection pool — request retries are configured, so the user sees a short delay, not an error.
How are updates performed without downtime?
We apply patches in an agreed window and one replica at a time: while one is updating, reads go through the others, then we switch the primary. Before and after the window we capture latency metrics — if there is degradation, we return to the previous revision.
How do you make sure the backup is usable?
We restore the backup to a separate instance on a schedule and verify record counts and checksums. We separately test recovery to an arbitrary point in time — without such a rehearsal, the backup exists only formally.
How great is the dependence on the cloud provider?
We keep the design portable: standard SQL, migrations in the repository, exports in open formats. We move to another environment using logical replication — the source continues serving requests, and we switch traffic after data verification.
Let's discuss the task and come back with an estimate and plan
Estimates and a work plan are accurate when there are facts, not general statements. To discuss the task substantively, a short description is enough — we will clarify details on a call, without a twenty-point questionnaire.
- which services work with the database and what load it must handle: peak queries, data volume, expected growth;
- current architecture: where the database is deployed, how replication, backup, and high availability are set up;
- availability and maintenance window requirements — what can be stopped and what cannot;
- environment constraints: network rules, regulations, who approves changes in the production environment;
- known pain points — slow queries, heavy migrations, incidents over the last months.
This is enough to prepare for project start.
In response, we come back not with a rough estimate, but with a package: a target architecture diagram with replicas, a connection pool, and network perimeters; a stage-by-stage work plan with dependencies; team composition and timelines; a list of what will be required from your side — access, roles, a test environment, and a switchover window.
Then a 30–40 minute call: we go over the architecture, stage sequence, and risks. Describe the task in two or three paragraphs, and we will come back with an estimate and a plan.