Google Cloud / Intermediate: Service Contracts and Operations / Lesson 9
Cloud SQL and Relational Data Contracts
Design managed-database access, connection capacity, availability, and recovery without treating managed as responsibility-free.
On this page
Lesson goals
- Separate network, IAM, and database authorization.
- Budget application connection demand.
- Distinguish HA, replicas, backups, and point-in-time recovery.
Managed infrastructure does not design the schema
Cloud SQL provides managed relational database capabilities for supported engines and editions. The application owner still designs schema, queries, migrations, transaction boundaries, users, connection pooling, data classification, and recovery objectives. Features and defaults differ by engine, version, edition, region, and current product support. This lesson uses PostgreSQL documentation for a concrete HA reference rather than pretending every engine is identical.
A database can be healthy while an application exhausts connections or issues expensive queries. Infrastructure administration and data-model correctness are different responsibilities. Record maintenance and upgrade assumptions, engine compatibility, extensions, and application retry behavior before selecting a configuration.
Scaling applications can overload a database
The first program estimates application connections from invented instance and pool limits, then adds a reserve. The result fits a synthetic limit of sixty. It is not a Cloud SQL quota or tested capacity. Real limits depend on the selected engine/configuration, other clients, administrative needs, rollout overlap, and actual pool behavior.
A Cloud Run or VM autoscaler can increase connection demand faster than a database scales. Bound pools, idle connections, retries, and request concurrency together. A maximum-instance setting alone is not a precise database-connection guarantee under every rollout or scaling event. Monitor measured pressure and reject or queue work under an explicit policy.
instances = 8
pool_per_instance = 5
reserve = 10
synthetic_limit = 60
modeled = instances * pool_per_instance + reserve
print(f"modeled={modeled} limit={synthetic_limit} fits={modeled <= synthetic_limit}")
modeled=50 limit=60 fits=True
Eight pools of five plus ten reserved slots total fifty; these are not Google limits.
A private address is not complete database access
Connectivity, Google-side permissions, database authentication, database roles, and encrypted transport are distinct layers. Cloud SQL connectors and the Auth Proxy have documented authentication and connection roles, but do not replace every network or database authorization requirement. Choose the supported connection method for the actual runtime and address mode.
Do not place a password or key in source, container images, or public examples. Secret delivery, rotation, and least-privilege database roles need ownership. Diagnose whether a failure is routing, connector identity, engine login, or SQL permission before widening access. This lesson opens no database connection, local or cloud.
HA and recoverable history are different guarantees
The documented PostgreSQL regional HA design has primary and standby zones within the configured region and synchronous storage replication for committed writes under that contract. Failover can close connections, so the application must reconnect and handle uncertain transaction outcomes safely. A read replica is not simply the HA standby, and cross-region replicas have their own replication and promotion semantics.
Backups and point-in-time recovery address recovery history and logical incidents; they are not equivalent to an HA flag. Retention, log configuration, engine/edition support, and restoration procedures matter. Do not quote one universal failover or data-loss number. Define RTO/RPO and validate the application path through an authorized restore/failover exercise.
A recovery checklist must reveal missing evidence
The second program checks a synthetic design record where HA is documented but restoration evidence is missing. It prints the gap rather than inferring recoverability from HA. A checked Boolean in a design file is not proof that a real backup exists; the record should link to controlled evidence, date, scope, and owner in an operational system.
Review migration compatibility, maintenance, access, connection demand, backup retention, restore testing, and rollback as a whole. Protect backups and restore privileges separately from routine application access. The course performs only offline document reasoning and makes no claim of executing SQL, testing an engine, or recovering a database.
evidence = {"ha_design_documented": True, "restore_test_recorded": False, "application_reconnect_reviewed": True}
missing = [name for name, present in evidence.items() if not present]
print("missing evidence", ",".join(missing))
missing evidence restore_test_recorded
The record lacks restore-test evidence even though HA is documented; no database is queried.
Common errors and fixes
A private-IP setting is assumed to grant SQL access.
- Why it happens
- Connectivity and authorization layers were merged.
- How to fix it
- Review network, connector, engine login, and database roles separately.
- Prevent it next time
- Record the complete connection path.
Autoscaling exhausts database sessions.
- Why it happens
- Per-instance pools were not included in capacity design.
- How to fix it
- Bound application demand and monitor actual connections.
- Prevent it next time
- Include rollout overlap and nonapplication clients.
HA is presented as a tested backup restore.
- Why it happens
- Availability and historical recovery were conflated.
- How to fix it
- Define and verify the required restore/PITR procedure.
- Prevent it next time
- Keep separate evidence for HA and recovery history.
Check your understanding
1. A service scales from two to twenty instances, each allowed ten connections. What changes in the review?
Show worked solution
Potential application demand changes from twenty to two hundred connections before reserves, other clients, retries, or rollout overlap. Check the actual engine/configuration limits and observed pool behavior; do not assume the database expands at the same rate. Bound admission and pool sizes deliberately and document degradation when the budget is exhausted. The numbers are a model, not a quota claim.
2. Why must an application consider an unknown transaction outcome after a connection breaks during failover?
Show worked solution
The server may have committed a transaction before the client lost its response. Blindly repeating a non-idempotent action can duplicate an effect. Use appropriate transaction identifiers, idempotency, reconciliation, and retry policy for the domain. HA can improve availability, but it does not infer application-level exactly-once behavior or remove the need to reconnect safely.
Key takeaways
- Managed database infrastructure still needs application and data ownership.
- Connections are a shared capacity budget across scaling and rollout.
- HA, replicas, backups, and PITR require separate evidence.