Question 1

What is Cloud SQL?

Accepted Answer

Cloud SQL is GCP's fully managed relational database service for MySQL, PostgreSQL and SQL Server. Google handles provisioning, patching, backups, replication, encryption and failover, while you keep full SQL compatibility and normal client drivers. It is the default choice for OLTP workloads that need a traditional relational database. The boundary to state is what it does *not* do: it is a single-region primary with vertical scaling limits, so it does not horizontally scale writes. When a candidate proposes Cloud SQL for a global write-heavy workload, that limit is exactly what the interviewer is probing.

Question 2

How does high availability work in Cloud SQL?

Accepted Answer

An HA configuration provisions a standby instance in a different zone within the same region, with synchronous replication of the underlying regional persistent disk. If the primary's zone fails or the primary becomes unresponsive, Cloud SQL automatically fails over to the standby, which takes over the same connection name and IP, typically within a minute or two. Two facts to include: the standby serves no read traffic — it is purely for failover, unlike a read replica — and HA protects against a zone failure, not a region failure. Regional protection requires a cross-region read replica that you promote manually, which is a different RPO and RTO story.

Question 3

What is the difference between a Cloud SQL HA standby and a read replica?

Accepted Answer

The HA standby replicates synchronously, is invisible to clients, serves no queries, and is failed over to automatically. A read replica replicates asynchronously, has its own connection endpoint, can serve read queries to offload the primary, can live in a different region or even outside GCP, and must be promoted manually to become a primary. The asynchronous nature of read replicas is the important consequence: there is replication lag, so read-after-write against a replica can return stale data. Any design that routes reads to a replica needs to identify which reads tolerate staleness — that judgement is what interviewers actually want.

Question 4

How do backups and point-in-time recovery work in Cloud SQL?

Accepted Answer

Automated backups run daily in a configurable window and are retained for a set number of days. Point-in-time recovery additionally retains write-ahead logs (PostgreSQL) or binary logs (MySQL), letting you restore to any second within the retention window by creating a new instance at that timestamp. On-demand backups can be taken any time and are retained until deleted. The critical operational detail is that PITR always restores to a *new* instance — you cannot roll the existing one back in place — so the recovery runbook involves a cutover. Also, restores can take a long time for large databases, which is why RTO must be measured rather than assumed.

Question 5

What is the Cloud SQL Auth Proxy and why use it?

Accepted Answer

The Cloud SQL Auth Proxy is a client-side process that creates an authenticated, encrypted tunnel to a Cloud SQL instance using IAM credentials, without needing to allowlist client IP addresses or manage SSL certificates. The application connects to localhost and the proxy forwards to the instance. The security benefit is the point: no public IP allowlisting, no static certificates to distribute and rotate, and access governed by the roles/cloudsql.client IAM role, which can be revoked instantly. The newer Cloud SQL language connectors do the same thing in-process without a separate binary.

Question 6

How should an application manage connections to Cloud SQL?

Accepted Answer

With a connection pool sized deliberately, because Cloud SQL instances have a maximum connection limit tied to machine size. In a serverless environment, the pool size must be multiplied by the maximum instance count — max instances times pool size must stay below the instance limit — and you should set conservative maximums on both. Connection exhaustion is the most common Cloud SQL production incident, and it usually starts with an autoscaling event rather than a code change. Naming a connection pooler such as PgBouncer, or AlloyDB's built-in pooling, as the answer for very high fan-out is a strong close.

Question 7

What machine and storage options does Cloud SQL offer, and how does storage scale?

Accepted Answer

Instances are sized by vCPU and memory (shared-core, standard and high-memory shapes), with SSD or HDD storage. Storage can be increased manually or automatically with storage auto-increase, and IOPS scale with provisioned size. Storage can never be decreased — shrinking requires exporting and recreating the instance. The auto-increase caveat is what interviewers probe: it prevents an outage from a full disk, but it also means a runaway process writing logs can silently grow storage and cost with no ceiling. Setting a maximum on auto-increase and alerting on growth rate is the mature configuration.

Question 8

How do you connect to Cloud SQL over a private IP?

Accepted Answer

Enable private IP on the instance, which requires a private services access connection — an allocated IP range in your VPC peered with Google's service producer network. The instance then receives an address in that range and is reachable from your VPC with no internet exposure. Serverless products reach it through Direct VPC egress or a Serverless VPC Access connector. The allocation step is where people get stuck: you must reserve a sufficiently large range up front, and it cannot easily be changed later. Also, because it uses VPC peering, the non-transitivity rule applies — an on-premises network connected by VPN cannot reach the instance without custom route advertisement.

Question 9

What is AlloyDB and how does it differ from Cloud SQL for PostgreSQL?

Accepted Answer

AlloyDB is a PostgreSQL-compatible database built for demanding enterprise workloads. It separates compute from a distributed, log-based storage layer, offers a columnar engine that accelerates analytical queries on the same data, provides much faster read replica creation and higher throughput, and adds machine-learning-driven autopilot features such as adaptive autovacuum. The claim to state carefully is that Google reports substantially higher transactional throughput and much faster analytics than standard PostgreSQL, and that the columnar engine means you can run reporting queries against the operational database without a separate warehouse for moderate volumes. The trade-off is higher cost and a newer, smaller operational track record.

Question 10

When would you choose Cloud SQL, AlloyDB, Spanner or Firestore?

Accepted Answer

Cloud SQL for standard relational OLTP that fits in one region and one primary — most applications. AlloyDB when you have outgrown Cloud SQL's performance envelope but still want PostgreSQL compatibility, or want hybrid transactional-analytical queries. Spanner when you need horizontal write scaling, global distribution and strong consistency with relational semantics. Firestore for document-shaped data with real-time client sync and simple access patterns. The framing that scores is to lead with the constraint that forces the step up: a single primary that cannot take the write volume, or a multi-region strong-consistency requirement. Recommending Spanner by default is a red flag, because its cost and modelling discipline are only justified by those constraints.

Question 11

What is Cloud SQL IAM database authentication?

Accepted Answer

It lets users and service accounts authenticate to MySQL and PostgreSQL instances with their Google identity and a short-lived token instead of a database password. Access is then managed through IAM, so removing a principal from a group revokes database access without touching the database. The advantage is eliminating shared static database passwords, which are otherwise stored in secret managers, config files and developers' laptops. Note that the database still needs a corresponding user object created, and IAM controls authentication while in-database grants still control authorisation.

Question 12

How do Cloud SQL maintenance windows work?

Accepted Answer

Google applies maintenance — patches and version updates — during a window you configure, causing a brief restart or failover. You can set the day and hour, set a maintenance timing preference (earlier or later in the rollout schedule), and declare deny periods during which maintenance will not occur, such as a peak sales week. The practical advice is to set the window, use the "later" timing for production so issues are caught in earlier environments, and put staging on "earlier" so you see the change first. HA instances still experience a short failover during maintenance, so the application must handle reconnection.

Question 13

How would you migrate a self-managed PostgreSQL database to Cloud SQL with minimal downtime?

Accepted Answer

Use Database Migration Service, which performs a continuous replication migration: an initial dump and load, then ongoing change replication from the source, so the cutover window is just the time to stop writes, let replication catch up and repoint the application. Validate schema, extensions and users beforehand, and test the cutover in a rehearsal. The details that show experience are checking for unsupported extensions and superuser dependencies before starting, because those are the usual blockers, and running a rehearsal cutover to measure the actual lag-drain time rather than assuming it is instant.

Question 14

What are the main causes of Cloud SQL performance problems?

Accepted Answer

Missing or unused indexes causing table scans; connection exhaustion or excessive connection churn; long-running transactions holding locks; autovacuum falling behind on PostgreSQL leading to bloat and transaction-ID wraparound risk; undersized instance memory so the buffer cache thrashes; and storage IOPS limits because the disk is too small. The GCP-specific tool to name is Query Insights, which gives per-query latency, load and execution plans with tags down to the application layer. Saying you would start with Query Insights rather than guessing is what makes the answer practical.

Question 15

What is Query Insights in Cloud SQL?

Accepted Answer

Query Insights is a built-in performance monitoring tool that shows database load broken down by query, user and, with application tagging, by the calling endpoint or ORM operation. It surfaces the top queries by load, their execution plans and wait events, over a rolling window. The tagging capability is the underused part: annotating queries with the application route means you can attribute database load to a specific API endpoint, which turns a database problem into an actionable application ticket rather than a general "the database is slow".

Question 16

How does Cloud SQL handle encryption?

Accepted Answer

Data is encrypted at rest by default with Google-managed keys, or with CMEK from Cloud KMS if you need key custody. Connections are encrypted in transit — enforced when you require SSL, and automatic when using the Auth Proxy or a language connector, which handle certificates for you. The configuration to recommend is requiring SSL and disabling the public IP entirely, so the only access path is private IP or the proxy. Leaving a public IP with an allowlist is common and much weaker, because allowlists drift and office IPs change.

Question 17

What happens during a Cloud SQL failover and what must the application handle?

Accepted Answer

The standby is promoted, the connection name and private IP move to it, and existing connections are dropped. The application must detect the broken connections, retry with backoff, and re-establish the pool. In-flight transactions are lost and must be retried idempotently. The application-side requirement is the substance of the answer: automatic failover only helps if the client reconnects gracefully. A connection pool with health checks and a retry policy on transient errors turns a failover into a blip; without it, the application stays broken until someone restarts it.

Question 18

How do you scale reads in Cloud SQL?

Accepted Answer

Add read replicas and route read-only queries to them, ideally through a read endpoint or application-level routing. Cache hot reads in Memorystore to remove them from the database entirely. Scale the primary vertically for the remaining load. For very high read fan-out, consider AlloyDB or, if the data model allows, a different database. The important qualification is replication lag: reads that must reflect a just-completed write cannot go to a replica. The pattern to describe is routing by query intent — reporting and browse queries to replicas, post-write reads to the primary — rather than a blanket split.

Question 19

How do you scale writes when a single Cloud SQL primary is not enough?

Accepted Answer

In order of preference: optimise first — indexes, batching, removing unnecessary writes; scale vertically to a larger machine; offload non-transactional writes such as events and logs to Pub/Sub and BigQuery; shard by tenant or key across multiple instances at the application layer; or move to Spanner, which scales writes horizontally natively. Sharding at the application layer is the honest middle option and its cost should be named: cross-shard queries, rebalancing and operational complexity. Interviewers ask this to see whether you reach for a rewrite immediately or exhaust the cheaper options first.

Question 20

What is Database Migration Service?

Accepted Answer

A managed service for migrating MySQL, PostgreSQL and SQL Server databases into Cloud SQL or AlloyDB, supporting both one-time and continuous replication migrations, with connectivity over private IP, VPC peering or reverse SSH tunnel, and a conversion workspace for heterogeneous migrations such as Oracle to PostgreSQL. The heterogeneous path is worth naming because it involves schema and code conversion, not just data movement, and it is where migration projects actually fail. Mentioning that stored procedures and proprietary types are the usual blockers shows you have thought past the tooling.

Question 21

What is the difference between an export and a backup in Cloud SQL?

Accepted Answer

A backup is an internal, instance-level snapshot managed by Cloud SQL, used for restore and PITR, and it cannot be downloaded or restored to another engine. An export writes a SQL dump or CSV to a Cloud Storage bucket, which is portable, human-readable and suitable for moving data elsewhere or for long-term archival outside the instance lifecycle. The disaster-recovery point is that backups are deleted when the instance is deleted, so an export to a separate project's bucket is what protects you against accidental or malicious instance deletion. That distinction is a favourite because it exposes an incomplete DR plan.

Question 22

How would you protect against accidental deletion of a Cloud SQL instance?

Accepted Answer

Enable deletion protection on the instance, restrict the cloudsql.instances.delete permission to a small group, export backups to a bucket in a separate project with its own IAM and a locked retention policy, and add an organisation-level deny policy for deletion outside a break-glass group. The layered answer is what is wanted: a flag anyone can turn off, plus IAM, plus an out-of-band copy, plus a deny policy. Any single control fails against a determined mistake or a compromised credential, and interviewers are testing whether you think in layers.

Question 23

What is Memorystore and how does it complement Cloud SQL?

Accepted Answer

Memorystore is managed Redis, Valkey and Memcached. It complements Cloud SQL by absorbing repetitive reads, holding session state, rate-limit counters and computed results, so the relational database serves only what genuinely needs transactional consistency. It offers standard tier with replication and automatic failover. The design caution to add is cache invalidation and the thundering-herd problem: when a hot key expires, many requests hit the database simultaneously. Naming mitigations such as staggered TTLs or a single-flight lock shows the difference between having used a cache and having operated one.

Question 24

What are Cloud SQL flags and what would you commonly change?

Accepted Answer

Database flags expose engine configuration parameters that Cloud SQL allows you to set — for PostgreSQL, things like max_connections, work_mem, shared_buffers behaviour, log_min_duration_statement and autovacuum tuning; for MySQL, innodb_buffer_pool_size behaviour, slow query log settings and character sets. Some flags require a restart. The one to name for diagnostics is log_min_duration_statement (or the MySQL slow query log), because turning it on is usually the fastest route from "the database is slow" to a specific offending query. Also worth noting that some critical flags are managed by Cloud SQL and cannot be set.

Question 25

What is the difference between Cloud SQL and running a database on Compute Engine?

Accepted Answer

Cloud SQL gives you managed backups, patching, HA failover, replication and monitoring, at the cost of restricted superuser access, a fixed set of supported extensions and versions, and less control over tuning. Running on Compute Engine gives full control — any extension, any version, custom replication topology — but you own every operational responsibility including patching and failover. The right recommendation is Cloud SQL unless a specific requirement forces self-management, and being able to name such a requirement — an unsupported extension, a specific replication topology, a version outside the supported range — is what makes the answer credible rather than dogmatic.

Question 26

How do you monitor a Cloud SQL instance?

Accepted Answer

Cloud Monitoring exposes CPU, memory, disk utilisation, IOPS, active connections, replication lag and transaction counts. The alerts that matter are disk utilisation approaching the limit, connection count approaching the maximum, replication lag exceeding the tolerance for replica reads, and CPU sustained near saturation. Query Insights covers per-query performance. Replication lag is the alert that most teams add only after an incident, because a lagging replica silently serves stale data rather than erroring. Naming it unprompted is a good signal that you have run replicas in production.

Question 27

What is a cross-region read replica and how does it help disaster recovery?

Accepted Answer

A read replica placed in another region replicates asynchronously from the primary. In a regional outage you promote it to a standalone primary and repoint the application. Because replication is asynchronous, the recovery point objective is non-zero — you may lose the transactions that had not replicated — and the recovery time includes promotion and cutover. Being explicit about RPO and RTO rather than describing this as "regional HA" is the mark of a senior answer. Promotion is also one-way: the promoted instance no longer replicates, so failback requires establishing replication in the opposite direction.

Question 28

What does "serverless" mean for databases on GCP, and which options qualify?

Accepted Answer

Firestore and Spanner (in its granular-capacity form) scale without you sizing instances, and BigQuery is serverless for analytics. Cloud SQL and AlloyDB are managed but not serverless — you choose a machine size and pay for it whether or not it is used, although AlloyDB and Cloud SQL Enterprise Plus have added automatic scaling capabilities. The precision matters because "serverless" is used loosely. The practical consequence is cost shape: a Cloud SQL instance has a fixed monthly floor, while Firestore for a low-traffic application can cost almost nothing. That distinction drives architecture for small services.

Question 29

How do you handle schema migrations safely against a live Cloud SQL database?

Accepted Answer

Use expand-and-contract: add the new column or table first, deploy code that writes to both old and new, backfill in batches, switch reads to the new shape, then remove the old one in a later release. Avoid long-running locking DDL on large tables during peak hours, use tools that perform online schema changes, and run migrations from CI with a versioned migration tool rather than by hand. The failure to pre-empt is a blocking ALTER TABLE on a large table taking a lock and stalling the application. Naming expand-and-contract explicitly, and the requirement that a release must be backwards-compatible with the previous schema, is what an interviewer is listening for.

Question 30

What is connection pooling and where should the pool live in a serverless architecture?

Accepted Answer

A connection pool reuses established database connections instead of opening one per request, because connection setup is expensive and the database has a hard connection limit. In a serverless architecture each instance has its own pool, so the effective total is instances multiplied by pool size — which means small per-instance pools plus a capped maximum instance count, or an external pooler such as PgBouncer as a shared layer. The arithmetic is the answer: 100 Cloud Run instances with a pool of 10 is 1,000 connections, which will exhaust most Cloud SQL instances. Being able to state that calculation is the difference between knowing the concept and having debugged the outage.

Question 31

What is AlloyDB's columnar engine?

Accepted Answer

It keeps a columnar representation of selected tables or columns in memory alongside the row store, and the query planner automatically uses it for analytical scans and aggregations while transactional queries continue to use the row store. It can accelerate analytical queries substantially without moving data to a warehouse. The positioning to be careful about: it makes moderate analytical workloads viable on the operational database, but it does not replace BigQuery for petabyte-scale analytics or for serving many analysts. Overstating it is a mistake an interviewer will push back on.

Question 32

How is Cloud SQL billed?

Accepted Answer

By instance vCPU and memory per hour whether or not queries run, plus provisioned storage per GB per month, plus backup storage, plus network egress. HA roughly doubles the compute and storage cost because the standby is a full instance. Committed use discounts are available for one and three year terms. The cost lever people miss is that a stopped instance still bills for storage, and that HA doubles the base cost — so non-production environments should almost never be HA. Naming that as a concrete saving is more useful than general advice.

Question 33

What is a Cloud SQL instance's connection name and why does it matter?

Accepted Answer

The connection name has the form project:region:instance and is the stable identifier used by the Auth Proxy and language connectors instead of an IP address. Because it resolves to whichever instance is currently primary, it survives failover automatically, which an IP-based configuration does not. This is why the proxy or connector approach is more resilient than hardcoding an IP: after a failover or an instance recreation, the connection name still works. It is a small detail that explains a real reliability difference.

Question 34

A Cloud SQL instance shows 100% CPU. What do you do?

Accepted Answer

Check Query Insights for the queries contributing most load, look for missing indexes and full scans, check for lock contention and long-running transactions, verify whether a batch job or a new deployment coincided with the change, and check connection count in case the load is connection churn rather than query cost. Short term, scale up the instance or throttle the offending workload; long term, fix the query or the index. The sequencing matters: diagnose before scaling, because scaling a badly-indexed query just makes the same problem more expensive. Interviewers specifically listen for whether you treat vertical scaling as a diagnosis or as a temporary mitigation.

Question 35

What is the difference between MySQL and PostgreSQL on Cloud SQL from an operations perspective?

Accepted Answer

Both are fully managed with the same HA, replica, backup and PITR features. PostgreSQL brings a richer extension ecosystem, stricter standards compliance and more advanced data types, but requires attention to autovacuum and transaction-ID wraparound. MySQL has simpler replication semantics and is often already familiar to existing teams. Extension and version support differs, so check before committing. The practical advice is to choose on team familiarity and application requirements rather than abstract superiority, but to verify supported extensions early — a dependency on an unsupported PostgreSQL extension is a project-blocking discovery if made late.

Question 36

What is transaction-ID wraparound in PostgreSQL and why does it matter on Cloud SQL?

Accepted Answer

PostgreSQL uses 32-bit transaction IDs; if autovacuum cannot freeze old rows fast enough, the database approaches wraparound and will eventually refuse writes to protect data integrity. It matters on Cloud SQL because autovacuum tuning is your responsibility even though the service is managed, and heavy write workloads with long transactions can starve it. Knowing this is a strong senior signal because it is a genuine production risk that managed hosting does not remove. The monitoring answer is to alert on the age of the oldest unfrozen transaction rather than waiting for warnings in the logs.

Question 37

How do you give an analyst read-only access to a Cloud SQL database?

Accepted Answer

Create a dedicated database user with SELECT-only grants on the relevant schemas, ideally against a read replica so analyst queries cannot affect production performance, and authenticate with IAM database authentication so access is tied to their Google identity and revoked with their group membership. The replica routing is the part that shows operational judgement — an unconstrained analytical query against the primary is a classic cause of production latency. Better still is replicating to BigQuery with Datastream so analysts never touch the operational database at all.

Question 38

What is Cloud SQL Enterprise Plus edition?

Accepted Answer

Enterprise Plus is a higher tier offering better performance through a data cache, near-zero-downtime planned maintenance, faster failover, longer log retention for point-in-time recovery, and larger machine shapes. Standard Enterprise edition remains the general-purpose tier. The feature to highlight is near-zero-downtime maintenance, because maintenance restarts are one of the few remaining sources of planned interruption on a managed database and removing them changes what SLA you can offer.

Question 39

How would you design the data layer for a multi-tenant SaaS application on GCP?

Accepted Answer

Decide the isolation model first: shared schema with a tenant_id column and row-level security is cheapest and scales to many small tenants; schema-per-tenant balances isolation and cost; database-or-instance-per-tenant gives the strongest isolation and simplest per-tenant restore but is expensive and operationally heavy. Then choose the engine — Cloud SQL or AlloyDB for most, Spanner if tenant growth demands horizontal write scaling — and add Memorystore for caching and BigQuery for cross-tenant analytics. The consideration that separates a senior answer is per-tenant operations: restoring one tenant's data from a shared table is hard, and noisy-neighbour isolation is impossible without either resource controls or physical separation. Naming those two problems shows you have run multi-tenant systems rather than designed them on paper.

Question 40

Design a resilient relational data layer with an RPO of near zero and RTO under 15 minutes.

Accepted Answer

Cloud SQL Enterprise Plus with HA in-region for zone failure, giving automatic failover in about a minute and effectively zero data loss thanks to synchronous replication. A cross-region read replica for regional disaster recovery, with a documented and rehearsed promotion runbook and automation to repoint the application, accepting a small RPO from asynchronous replication. Automated backups plus PITR, with exports to a bucket in a separate project protected by a locked retention policy. Connection handling with retries so failovers are transparent, and a quarterly failover drill that is actually executed. The closing scenario. The differentiators are being explicit that in-region HA gives near-zero RPO while cross-region gives non-zero, and insisting on rehearsed drills — an untested DR plan has an unknown RTO, which is the same as not having one.

GCP Cloud SQL & AlloyDB Interview Questions and Answers

How to use this set