Before the details, let us understand the problem that Cloud SQL solves, using a simple story.

The problem: a database needs a lot of care

Imagine you build a small online shop. You need a place to store customers, products and orders. You pick a well-known database program, install it on a server, and it works. Then the shop grows. Now you must worry about many things at once.

• What if the server’s disk breaks and all orders are lost?
• What if the server crashes at midnight during a sale?
• What if a security flaw is found in the database program and you must update it without stopping the shop?
• What if someone deletes a table by mistake and you need to go back to yesterday?
• What if traffic doubles and the database becomes slow?

Each question needs knowledge, tools and time. Specialists called database administrators, or DBAs, are paid to handle them. Small teams often cannot afford one, and large teams would rather spend their skills on the product.

The Cloud SQL answer: a managed database

Cloud SQL is a managed service. That means Google handles the machines, the operating system, the database software installation, the security patches, the backups, the copies for failure protection and the monitoring. You get a database address, a user name and a password, and you start storing data.

Cloud SQL runs three very popular database engines: MySQL, PostgreSQL and SQL Server. An engine is the database program itself. Because Cloud SQL uses the real engines, your existing applications, tools and skills work with little or no change. This is a major reason people choose it.

A short history

Google offered a managed MySQL service in its early cloud years through App Engine. It was later rebuilt and grew into Cloud SQL as it is known today, with PostgreSQL added in the second half of the 2010s and SQL Server added soon after. Over time Google added regional high availability, read copies in other regions, stronger security options and a higher tier named Enterprise Plus aimed at the most demanding systems.

Where Cloud SQL sits among Google Cloud databases

Google Cloud offers many databases. Each is best at a different job.

To use Cloud SQL well, you need a picture of how a relational database thinks. Here it is, with no jargon left unexplained.

Tables, rows and columns

A relational database stores data in tables. A table is like a spreadsheet with a fixed set of columns. Each column holds one kind of fact, such as a name or a price. Each row is one record, such as one customer or one order.

Keys and relationships

Every row needs a way to be identified. A primary key is a column, such as a customer number, that is unique for each row. A foreign key is a column in one table that points to the primary key of another, such as an order that stores the customer number of the person who placed it. These links are the “relations” in relational. They let you keep each fact in one place and still join the facts together when you need them.

SQL: the language of databases

SQL, which stands for Structured Query Language, is the language used to talk to relational databases. You use it to ask questions, such as “show me all orders from last week”, and to change data, such as “add this new customer”. Almost every relational database speaks SQL, with small differences between engines.

Transactions and ACID

Many real actions need several changes that must succeed or fail together. When you pay for an order, the shop must record the payment, reduce the stock and create a delivery request. If the second step fails, the first must be undone. A transaction is a group of changes treated as one unit: all of it happens, or none of it does.

Relational databases promise four properties for transactions, known as ACID.

Indexes

Imagine finding one name in a phone book with no alphabetical order. You would read every page. An index is an extra, sorted lookup structure that lets the database jump straight to the right rows. Indexes make reads much faster, but they take space and make writes a little slower, because each change must also update the index.

Schema

A schema is the plan of your database: which tables exist, which columns they have, which keys and indexes apply. Changing the schema of a big live table needs care.

Relational versus other kinds of databases

Cloud SQL has its own small vocabulary. Learn these words once, and the rest of the guide will feel familiar.

Instance

A Cloud SQL instance is one managed database server. It has a database engine and version, a size in processors and memory, storage, a network setting and a location. You cannot log in to the machine under it, but you connect to the database engine running on it.

Database

One instance can hold many databases, which are separate collections of tables. For example, one instance could hold a “shop” database and a “reports” database.

Users

A database user is an account inside the database engine, with a name and password. Users are different from Google Cloud accounts. Cloud SQL can also link database users to Google identities, which we cover in the security chapter.

Region and zone

A region is a geographic area, such as a part of a country. A zone is one isolated group of data center resources inside a region, with its own power and cooling. A normal instance lives in one zone. A high-availability instance uses two zones.

vCPU and memory

A vCPU is a virtual processor, the part that does the thinking. Memory is the fast working space. Databases love memory, because data already in memory is far quicker to read than data on disk.

Storage

Cloud SQL stores data on network-attached disks. You choose the type, solid-state or hard drive, and the size. Solid-state is faster and is the usual choice. Storage can grow automatically when space runs low, if you turn that option on.

Flags

Database engines have many settings. In Cloud SQL, the settings you may change are called database flags. Google allows a chosen list of flags, so you cannot change everything, but you can change the ones most teams need.

Backup and point-in-time recovery

A backup is a saved copy of your data. Point-in-time recovery, often shortened to PITR, lets you rewind a database to any chosen moment within a set period, by replaying a log of changes on top of a backup.

High availability, replica and failover

High availability, or HA, means a standby copy in another zone takes over automatically if the main one fails. A read replica is another copy used to serve read-only questions. Failover is the switch from a failed machine to its standby.

Now let us zoom out and see how Cloud SQL connects to the applications that use it.

A typical setup has four groups of parts. First, the applications: web servers, containers or services that send SQL requests. Second, the network path: private addresses, firewall rules and secure connectors. Third, the Cloud SQL service itself: the primary instance, an optional standby and optional read replicas. Fourth, the supporting services: backups, logs, monitoring, identity and encryption keys.

flowchart TD
  subgraph APPS["Your Applications"]
    W1["Web Servers on Compute Engine"]
    K1["Containers on GKE"]
    R1["Cloud Run Services"]
  end
  PROXY["Cloud SQL Auth Proxy or Connectors"]
  W1 --> PROXY
  K1 --> PROXY
  R1 --> PROXY
  PROXY -->|"Encrypted private connection"| PRI
  subgraph CSQL["Cloud SQL Service Managed by Google"]
    subgraph ZA["Zone A"]
      PRI["Primary Instance"]
    end
    subgraph ZB["Zone B"]
      STB["Standby Instance for HA"]
    end
    PRI -->|"synchronous copy"| STB
    PRI -->|"asynchronous copy"| RR["Read Replica"]
    PRI --> DISK["Regional Storage"]
  end
  DISK --> BKP["Automated Backups and Logs"]
  KMS["Cloud KMS Encryption Keys"] -.-> CSQL
  IAM["IAM Permissions"] -.-> CSQL
  MON["Cloud Monitoring and Logging"] -.-> CSQL

Walking through the picture

At the top are your applications, running on virtual machines, containers or serverless services. They need to send SQL requests. Instead of connecting directly with a bare password over an open path, they usually go through the Cloud SQL Auth Proxy or a language connector. These tools open an encrypted connection and check permissions with IAM, so you do not need to manage address allow lists or certificates by hand.

The requests arrive at the primary instance, which accepts all writes. In a high-availability setup, a standby instance in a different zone receives a synchronous copy of every change, which means the primary waits for the copy to be safe before confirming a write. An optional read replica receives changes asynchronously, which means slightly later, and answers read-only questions so the primary has less work.

Behind the scenes, data lives on Google’s disks, and backups and logs are stored separately. Around everything sit Cloud KMS for encryption keys, IAM for permissions, and monitoring and logging to watch the health of the instance.

Why this design works

Each part has one job. Applications only need the standard database language. The proxy handles safe connections. The primary handles writes and the truth of the data. The standby handles survival. The replicas handle extra reading. Google handles the machines. This split lets each part be improved or repaired without redesigning the whole.

This is the part many tutorials skip. Knowing it explains why Cloud SQL behaves the way it does.

The machine under the database

Each Cloud SQL instance runs on a virtual machine managed by Google. Inside that machine, the real database engine runs, together with small helper programs that handle backups, health checks, logging and communication with the Cloud SQL control system. You never log in to that machine, which is why you cannot install your own software on it or change the operating system. This is the price of being managed, and it is also the source of the service’s safety.

How a query is answered

When an application sends a question, the database engine follows a series of steps.

Memory: the buffer pool

Disks are slow compared with memory. So databases keep recently used pieces of data in a large area of memory, often called the buffer pool or shared buffers. If a requested piece is already there, the answer is almost instant. If not, it must be read from disk first. A database with enough memory to hold its busiest data runs much faster than one that must keep going to disk.

How writes stay safe: the write-ahead log

Writing straight into the big data files on every change would be slow and risky. Instead, relational databases use a write-ahead log, often called the WAL in PostgreSQL, the redo log in MySQL’s InnoDB engine, and the transaction log in SQL Server. Before changing any data file, the engine first writes a short note of the change to the log, and forces that note safely to disk. Only then does it confirm the change. The data files are updated later in the background.

If the machine crashes, the engine reads the log on restart and replays any confirmed changes that had not yet reached the data files, and undoes any unfinished ones. This is how durability and atomicity are kept.

The log also powers copies and rewind

The same stream of log notes is used for three other jobs. It is sent to the standby and to read replicas so they can repeat every change. It is stored for point-in-time recovery, so you can rewind to a chosen moment. And it supports tools that read changes for other systems. Understanding that the log is the heart of the engine makes replication, failover and recovery much easier to understand.

Locks and isolation

When many users change data together, the engine must keep them from stepping on each other. It uses locks, which are temporary “do not touch” marks on rows or tables, and a technique called multiversion concurrency control, or MVCC in some engines, which lets readers see a consistent older picture of data while writers change it. As a result, readers often do not block writers. But long-running transactions can still hold locks and cause others to wait, and even to fail with deadlocks, which happen when two transactions each wait for the other.

Storage under the engine

Cloud SQL stores data on Google’s network-attached block storage, similar to Persistent Disk. Because the disk is separate from the machine, it survives the loss of a machine, and its speed grows with its size and type. In a high-availability instance, the data is written in a way that is available to both zones, so a standby can take over using the same data.

The control plane

When you click “create instance”, “add a replica” or “restore backup”, you are talking to the Cloud SQL control plane, the management system that builds machines, installs engines, sets up networks and starts replication. The data plane is the running database and its traffic. These are separate, so a problem in the management system does not stop an already running database from answering queries.

Cloud SQL offers three engines and, for two of them, two editions. Picking well at the start saves pain later.

The three engines

How to choose an engine

The best guide is what you already have. If your application was built for one engine, stay with it, because switching engines means rewriting queries and testing everything. If you are starting fresh, MySQL and PostgreSQL are both safe choices. PostgreSQL is often chosen for advanced features and extensions. MySQL is often chosen for simplicity and for many existing tools. Choose SQL Server mainly when your software depends on it.

Versions

Each engine has major versions. Cloud SQL supports several recent ones and retires old ones after a notice period. Newer versions bring speed and security improvements, while old ones eventually stop receiving fixes. Pick a currently supported version for new work, and plan upgrades before your version reaches the end of its support.

Editions: Enterprise and Enterprise Plus

For MySQL and PostgreSQL, Cloud SQL offers two editions, which are bundles of features and performance at different prices. Google has adjusted details over time, so always confirm the current list in the documentation. In general:

What you cannot do on a managed database

Being managed brings limits. You do not get operating system access to the machine. You cannot install arbitrary software or database add-ons that Google has not enabled. You do not get full “superuser” powers in the engine, but a special administrative role that covers nearly all normal tasks. Some low-level settings and file system paths are off limits. For most teams these limits are a feature, since they remove many ways to hurt yourself. If your application needs one of the blocked abilities, consider running the database yourself on Compute Engine, or use a different managed service.

From creation to deletion, every instance follows a story. Knowing each stage prevents surprises.

Creating an instance

You create an instance through the console, the command line, an API or an infrastructure tool such as Terraform. You choose the engine and version, the edition, the region and zone, the machine size, the storage type and size, the network settings, whether to enable high availability, the backup schedule and the maintenance window. You also set the password of the main administrative user.

What happens when an application saves a row

sequenceDiagram
  participant A as Application
  participant P as Auth Proxy
  participant D as Primary Database Engine
  participant L as Write Ahead Log on Disk
  participant S as Standby in Zone B
  participant R as Read Replica
  A->>P: Send SQL INSERT or UPDATE
  P->>D: Forward over encrypted connection
  D->>L: Write change note to log
  D->>S: Send log record to standby
  S-->>D: Standby confirms log is safe
  L-->>D: Log safely on disk
  D-->>P: Transaction committed
  P-->>A: Success returned
  D-->>R: Send log record later
  R->>R: Replay change on replica

What the picture shows

The application sends a change through the connection. The primary writes the change into its log. In a high-availability instance, the primary also sends the log record to the standby and waits for it to confirm that the record is safe. Only after both copies are safe does the primary tell the application “committed”. The read replica receives the same log record a little later, so replicas can briefly lag behind the primary.

What happens when an application reads data

For a read, the engine checks its memory first. If the needed data is there, it answers right away. If not, it reads from disk, places the data in memory for next time, and answers. Reads sent to a replica follow the same path on the replica’s own memory and disk, and may return data that is a moment old.

Data flow at runtime

While the instance runs, several streams of data move. Application queries flow in and results flow out. The log flows to the standby, to replicas and to storage for point-in-time recovery. Backups flow to protected storage. Metrics and logs flow to Cloud Monitoring and Cloud Logging. Administrative actions appear in audit logs.

Choosing the right size is like choosing the right vehicle: too small and you stall, too big and you waste money.

Machine types

Cloud SQL machines come in several styles. Shared-core machines are tiny and cheap, suitable for learning and light testing. Standard machines have a balanced amount of memory per processor. High-memory machines give much more memory per processor, which suits databases whose busy data is large. You can also create custom sizes within limits. Available shapes differ by engine and edition, so check the current list.

Storage type and size

You choose between SSD storage, which is faster and is the standard choice, and HDD storage, which is cheaper and slower and is rarely right for busy databases. Storage size matters twice: it sets how much data you can keep, and in many cases it also sets how fast the disk can work, since larger disks are allowed more operations per second. If a database is slow because of disk limits, adding space may be the fix, even when you are not short of room.

Automatic storage increase

If you turn on automatic storage increase, Cloud SQL adds space when free space runs low, so the database does not fill up and stop. It is a safety net worth enabling. But note that storage can only be increased, not decreased, so a sudden temporary surge, such as a huge one-time import, can leave you paying for space you no longer need. To shrink, you must create a new instance with a smaller disk and move data over.

The sizing rule of thumb

Because databases are fastest when their hot data fits in memory, the single most useful sizing question is: how big is the data that is used most often, and does it fit in memory with room to spare? If the answer is no, the database reads from disk often, and speed suffers. Add memory before adding processors, unless your charts show the processors are the limit.

Right-sizing step by step

1. Start with an estimate based on a similar system, or with a modest standard size.
2. Run a realistic test or a real workload and watch processor, memory, disk and connection charts for at least a week.
3. If the processor is above seventy to eighty percent for long periods, add processors. If the database reads from disk heavily and memory is full, add memory.
4. If the processor is always very low, consider a smaller size to save money.
5. Repeat after major changes, such as a big launch or a new feature.

Resizing

You can change the machine size of an instance later. This is usually a restart event with a short interruption, so plan it for a quiet time. High-availability instances reduce the interruption, because a standby can take the load while the primary changes. Storage can be increased without a restart in most cases.

Connection limits depend on size

Each engine has a maximum number of simultaneous connections, and in Cloud SQL the default limit depends on the memory of the machine. Each connection uses memory. A common surprise is that a larger fleet of application servers, each opening many connections, exhausts the limit long before the database runs out of processing power. We return to this in the performance chapter.

A database is only useful when applications can reach it, and only safe when strangers cannot. This chapter explains how both are achieved.

Public IP and private IP

An IP address is the number that identifies a machine on a network. A Cloud SQL instance can have a public IP, which is reachable from the internet, a private IP, which is reachable only from inside your private network, or both. For production systems, private IP is the safer default, because the database is then not exposed to the internet.

Private IP: how it works

Cloud SQL runs in a network owned by Google, while your applications run in your own VPC, a Virtual Private Cloud, which is your private network in Google Cloud. To join the two, Cloud SQL uses private services access, which creates a private link called peering, and you reserve a block of private addresses for it. Newer options, such as Private Service Connect, expose the instance as a single private endpoint in your own network. The available methods and details may vary, so check the current documentation.

Authorized networks

If you use a public IP, you must tell Cloud SQL which addresses may connect. These are called authorized networks. Never add “everyone”, which is written as a range covering the whole internet. Even with a password, an open database invites constant attack attempts.

The Cloud SQL Auth Proxy and connectors

The Cloud SQL Auth Proxy is a small program that runs next to your application. It opens an encrypted connection to the instance, checks the caller’s IAM permissions, and handles certificates automatically. Your application connects to the proxy as if it were a local database. Google also offers language connectors for popular languages such as Java, Python and Go, which do the same job inside your code without a separate program. Using the proxy or a connector is the recommended way to connect, because it works with both public and private addresses and removes the need to manage address lists and certificates.

flowchart LR
  subgraph PROJ["Your Google Cloud Project"]
    subgraph VPC["Your VPC Network"]
      APP1["App on Compute Engine"]
      APP2["App on GKE"]
      SRV["Cloud Run with VPC connector"]
    end
  end
  subgraph ONP["Office or On-premises"]
    DEV["Developer or Legacy System"]
  end
  subgraph GOOG["Google Managed Network"]
    SQLI["Cloud SQL Instance with Private IP"]
  end
  APP1 -->|"Private connection"| SQLI
  APP2 -->|"Private connection"| SQLI
  SRV -->|"Private connection"| SQLI
  DEV -->|"Cloud VPN or Interconnect"| VPC
  DEV -.->|"through your VPC"| SQLI
  NET["Public Internet"] -. "blocked: no public IP" .-> SQLI

Firewall and routing

Even with private IP, network rules matter. Your VPC firewall rules must allow outgoing traffic from applications to the database’s address and port. Routes must exist from where your clients are to the database’s network. If you connect from an office, you need a Cloud VPN tunnel or a Cloud Interconnect line, and address ranges that do not overlap with the ranges used by Google’s side.

Encryption in transit

Data traveling between the application and the database can be encrypted using SSL or TLS, which are standard methods for scrambling data on the wire. The Auth Proxy and connectors always use encryption. For direct connections, you can require SSL on the instance, so any unencrypted connection is refused. For anything sensitive, turn this on.

Connecting serverless services

Serverless services, such as Cloud Run and Cloud Functions, can connect to Cloud SQL using built-in connection support, or through a VPC connector. Since serverless services can create many instances quickly, watch the total number of connections, and use connection pooling, which is explained in the next chapter.

Connection checklist

Speed is where database knowledge pays off. Most slow databases are slow for a few common reasons, and most are fixable.

What “fast” means for a database

• Latency is how long a single query takes. Users feel this directly.
• Throughput is how many queries or transactions are handled per second.
• Concurrency is how many users or connections are active at the same time.

A system can have fine throughput and awful latency for some users, so look at both, and look at the slowest few percent of queries, not just the average.

The usual suspects

Find slow queries first: Query Insights

Never tune by guessing. Cloud SQL provides Query Insights, a built-in tool that shows which queries use the most time, how often they run, and where time goes. You can also turn on the engine’s slow query log to record queries that take longer than a chosen time. Start with the few queries that use the most total time, because fixing one of them often helps the whole system.

Indexes: the biggest single lever

Adding the right index can turn a query from minutes to milliseconds. Create indexes on columns that are used often in filters, joins and sorting. Use the engine’s explain command, which shows the plan the optimizer will use, to check whether an index is being used. Remember the trade-off: every index makes writes slower and takes space, so do not index everything. Remove indexes that are never used.

Write good queries

• Ask only for the columns and rows you need, not “everything”.
• Avoid running one query per item in a loop. Fetch many items in one query instead. This pattern, known as the N plus one problem, is a classic cause of slow pages.
• Use pagination for long lists rather than loading thousands of rows.
• Keep transactions short, and never keep one open while waiting for a person or a slow outside service.

Connection pooling

Opening a database connection is expensive, and each open connection uses memory. A connection pool keeps a small set of open connections and lends them to requests as needed, instead of opening a new one every time. Most application frameworks include pooling. For very large fleets, a separate pooling program, such as PgBouncer for PostgreSQL, or a managed pooling feature where Cloud SQL offers one, sits between the application and the database. Pooling lets thousands of application threads share a few dozen real connections.

Caching in front of the database

The fastest query is the one never sent. A cache, such as Memorystore for Redis or Memcached, stores answers to common questions in memory so the database is asked less often. Cache data that is read often and changes rarely, such as product details. Set a time limit on cached values so they refresh. Some editions of Cloud SQL also include an internal data cache that uses fast local storage to speed up reads.

Tuning with flags

Database flags let you change engine settings, such as memory areas, timeouts and logging. Change them carefully, one at a time, and measure. Some flags need a restart. Many popular tuning guides assume you control the entire server, so check that a flag is allowed on Cloud SQL before you rely on advice.

Operations that hurt performance

• Heavy reports run directly on the primary during business hours. Send them to a replica or a data warehouse instead.
• Large one-time updates or deletes in one huge transaction. Break them into small batches.
• Schema changes on big tables at busy times. Use online methods or off-peak windows.
• Missing regular maintenance, such as updating table statistics, which the optimizer uses to choose plans.

A simple performance routine

What do you do when one database is not enough? This chapter walks through the options, from the simplest to the most complex.

Why relational databases scale differently

A relational database keeps its data consistent through one main copy that accepts changes. That is why a single primary is simple and reliable. It is also why you cannot just add ten more servers and expect writes to be ten times faster. Scaling a relational database takes a few well-known steps.

Step 1: tune first

Before adding hardware, fix queries, add indexes, use pooling and add caching. This is cheapest and often removes the need for anything else.

Step 2: scale up (vertical scaling)

Scale up means moving to a bigger machine, with more processors and memory. It is simple and needs no change to the application. It has limits, since machines only get so big, and big machines get expensive, and resizing means a brief restart.

Step 3: scale out for reads with read replicas

Many applications read far more than they write. A read replica is a copy of the primary that receives changes through replication and serves read-only queries. By sending reports, search pages and browsing traffic to replicas, you take load off the primary. You can have several replicas, in the same region or in different regions. A replica in another region also brings data closer to far-away users and can serve as a disaster recovery copy.

flowchart TD
  APP["Application"] -->|"writes and fresh reads"| PRI["Primary Instance Region 1"]
  APP -->|"general reads"| RR1["Read Replica 1 Region 1"]
  APP -->|"general reads"| RR2["Read Replica 2 Region 1"]
  REP["Reporting Tools"] --> RR2
  PRI -->|"asynchronous replication"| RR1
  PRI -->|"asynchronous replication"| RR2
  PRI -->|"asynchronous replication"| XR["Cross-region Replica Region 2"]
  FAR["Users in another continent"] --> XR
  XR -.->|"promote in disaster"| NEWP["New Primary"]

Replica lag

Because replication is asynchronous, a replica can fall behind during heavy writing. The delay is called replica lag. Watch it in monitoring. For data that must be fresh, such as a customer’s own just-saved changes, read from the primary. For data that can be a little old, such as a public product list, a replica is fine.

Step 4: split the data (sharding and functional splitting)

When even a very large primary cannot keep up with writes or data size, teams split the data. Functional splitting moves different parts of the application to different databases, for example separating user accounts, orders and analytics. Sharding splits one huge table across several databases using a rule, such as customer number ranges. Sharding is powerful but adds real complexity to queries, transactions and operations, so it should come last. If you find yourself needing it, also look seriously at services built for scale, such as Spanner or AlloyDB.

Comparing the steps

Routing reads and writes

To use replicas, the application must know which connection is for writes and which is for reads. Many frameworks support a “read replica” setting. Some teams add a routing layer or a proxy that sends read-only statements to replicas automatically. Test the behavior carefully, especially for code that writes and then reads back immediately.

Machines break and buildings lose power. A highly available database is one that keeps going anyway, with little or no human help.

What availability means

Availability is the share of time a service works. It is often written in “nines”. Ninety-nine point nine percent allows under nine hours of downtime a year. Ninety-nine point ninety-five percent allows about four and a half hours. Ninety-nine point nine nine percent allows under an hour. Each added nine is harder and costlier to reach. Google publishes a written promise of uptime, called an SLA, for Cloud SQL, with different numbers for different editions and for instances with and without high availability. Read the current SLA for exact values and conditions.

A single-zone instance

Without high availability, an instance runs in one zone. Google protects the disk with copies inside its storage system, and if the machine fails, Cloud SQL restarts the instance, but this can take a while, and a zone-wide problem can stop it until the zone recovers. This is fine for development and for many low-risk systems, but not for important production work.

The high-availability configuration

When you enable high availability, Cloud SQL creates a regional instance. It keeps a primary in one zone and a standby in another zone of the same region. Both use a regional storage design, so a copy of the data is safely available to either one. Every change is written to both zones before it is confirmed, so the standby is always ready with all confirmed data. The standby does not serve queries. It only waits to take over.

flowchart TD
  APP["Applications"] --> IP["Single Instance Address"]
  IP --> PRI["Primary - Zone A"]
  PRI -->|"every write copied before confirm"| STB["Standby - Zone B"]
  PRI --> RD["Regional Disk across zones"]
  STB --> RD
  HM["Health Monitoring"] -.-> PRI
  HM -.->|"detects failure"| FO["Failover starts"]
  FO -->|"standby becomes primary"| STB
  FO -.->|"old primary rebuilt as new standby"| PRI

How failover works

During the switch, existing connections are dropped and applications must reconnect. A typical failover takes on the order of a minute or less, though this can vary. Applications that retry connections smoothly will recover without human help.

Designing applications that survive failover

• Retry connections with a short delay and a limit, instead of failing at the first error.
• Use connection pools that detect broken connections and replace them.
• Keep transactions short and safe to repeat, since a transaction that was in progress during failover is rolled back.
• Test failover in a staging environment. Cloud SQL lets you start a manual failover so you can watch how your application behaves.

What high availability does not do

• It does not protect against mistaken deletes or bad updates, since those are copied to the standby too.
• It does not protect against the loss of an entire region. For that, you need a cross-region replica or backups stored elsewhere.
• It does not make the standby serve reads. For read scaling, use read replicas.

Disaster recovery across regions

To survive the loss of a whole region, create a cross-region read replica. In a disaster, you promote the replica, which turns it into a standalone primary, and you point your applications at it. Promotion is a one-way step, so practice it first. A recovery point objective, the amount of recent data you could lose, depends on replica lag at the moment of the disaster, since replication to another region is asynchronous.

Two numbers to decide

RTO, the Recovery Time Objective, is how long you can afford to be down. RPO, the Recovery Point Objective, is how much recent data you can afford to lose. A shop that cannot lose any orders and can accept only a minute of downtime needs high availability within a region and a tested recovery plan for regional disasters. A small internal tool that can be down for an hour and lose a day of data can use just daily backups.

Data gets deleted by mistake, damaged by bugs and sometimes attacked. Backups are the way back.

Why high availability is not a backup

High availability copies every change instantly, including the bad ones. If someone runs a command that deletes a table, both zones lose it within moments. A backup is an older copy that is not changed when live data changes. HA protects against hardware failure. Backups protect against human error, bad code and attacks.

Automated backups

Cloud SQL can take a daily automated backup during a window you choose. It keeps a number of recent backups, with a default of about a week, and you can change how many to keep. Backups are incremental after the first one, which means they store only what changed. Google stores them separately from the instance’s disk. You can also take an on-demand backup at any time, for example before a risky change.

Point-in-time recovery

Point-in-time recovery, or PITR, lets you restore the database to any chosen second within a set period. It works by keeping the continuous log of changes, which you learned about earlier, and replaying it on top of a backup up to the moment you pick. For example, if someone deleted a table at 2:14 in the afternoon, you can recover the database to 2:13 and one second. The recovery period depends on how long logs are kept, and Enterprise Plus can keep them longer. Turning on PITR also uses extra storage for the logs.

Restoring: choices

Exports and imports

Besides backups, you can export a database to a file in Cloud Storage, in SQL or CSV form, and import it elsewhere. Exports are useful for moving data between systems and for long-term archives, but they are logical copies and can take a long time on large databases. Backups and PITR are the main tools for fast recovery.

Protecting backups

Backups hold all your data, so treat them as seriously as the database. Restrict who can restore, since a restore creates a readable copy. Restrict who can delete, since deleting removes your safety net. Remember that deleting an instance may remove its automated backups, so keep a final on-demand backup or an export before you delete anything important.

A sensible protection plan

Retention and rules

Some industries require you to keep data for years, or to delete it after a set time. Plan retention to match your rules. Long-term archives can be kept as exports in Cloud Storage with lifecycle rules, which is cheaper than keeping many backups inside the database service.

A database holds the most valuable things a company owns. Good security uses several layers, so one mistake does not open the door.

The shared responsibility model

Google secures the data centers, the hardware, the host machines, the operating system, the patching of the database engine and the storage system. You are responsible for who can reach the database, who can manage it, which users exist and what they can do inside, how data is protected in your application, and how passwords and keys are kept. Because Cloud SQL is managed, your share is smaller than with a database you run yourself, but it is never zero.

Layer 1: network

Use private IP, and avoid public IP for production. If you must use public IP, list a few specific authorized addresses, never the whole internet. Keep database instances in separate projects or networks from public-facing test systems. Use narrow firewall rules.

Layer 2: encrypted connections

Use the Cloud SQL Auth Proxy or connectors, which always encrypt. For direct connections, require SSL so unencrypted connections are refused. Consider requiring client certificates for the highest sensitivity.

Layer 3: IAM for management and connection

IAM controls who can create, change, back up, restore or delete instances, and who can connect through the proxy. Roles such as Cloud SQL Admin, Editor, Client and Viewer bundle permissions. Follow least privilege: give each person or program only what it needs. Keep the power to delete instances and backups limited to a few trusted people.

Layer 4: users inside the database

Inside the engine, create separate users for separate applications and for people, each with only the rights they need on only the databases they need. Never let applications use the main administrative user. If one application is compromised, the damage is limited to what that user could do.

Layer 5: IAM database authentication

Cloud SQL supports IAM database authentication for MySQL and PostgreSQL, where a user logs in with a short-lived Google access token instead of a stored password. This removes long-lived passwords from configuration files and makes access easier to audit and revoke. Where passwords are still used, keep them in Secret Manager, never in source code or images, and rotate them regularly.

Encryption at rest and customer-managed keys

Data on disk, backups and logs are encrypted by default using keys that Google manages. If your rules demand more control, you can use customer-managed encryption keys, or CMEK, through Cloud KMS, so you control the keys and can disable them to make data unreadable. Choose CMEK at creation time, since it generally cannot be added to an existing instance.

Auditing

Cloud Audit Logs record administrative actions, such as who created or deleted an instance. For PostgreSQL, the pgAudit extension and for MySQL and SQL Server, engine audit options can record what users do inside the database. Auditing is essential for rules about sensitive data and for investigating incidents. Keep logs long enough to meet your rules.

Organization policies and guardrails

A central team can use organization policies to enforce safe rules across many projects, such as forbidding public IP on database instances, or requiring customer-managed keys. Rules enforced by the platform are stronger than rules in a document that people may forget.

Data-level protection

Think about what is stored. Do not keep secrets or card numbers in plain form if you can avoid them. Mask or tokenize sensitive values. Use application-level encryption for the most sensitive fields. Limit test and development databases to fake or masked data, not copies of real customer information.

Even managed databases must be updated. This chapter explains how Google does it and how you can make it painless.

Why maintenance happens

Software has flaws that are found and fixed over time. Operating systems and database engines receive security fixes and improvements. Hardware is replaced and updated. Cloud SQL applies these updates for you, which is a large part of the value of a managed service. But applying many updates needs a restart of the database, so the timing matters.

Maintenance windows

You choose a maintenance window, a weekly time slot, such as late on a Sunday night, in which Cloud SQL may perform updates. You can also set an order of update to make test instances update before production ones, so you see problems early. Pick the quietest period of your traffic, in the time zone of your users.

Deny periods

A maintenance deny period blocks maintenance for a chosen range of days, up to a limit set by Google. Use it around critical dates, such as a holiday sale or a financial year end, so that no update interrupts the busiest time. Security-critical updates may still be applied in rare cases.

How long does maintenance take

For the standard edition, maintenance usually causes a short outage, often measured in minutes. High-availability instances reduce the downtime by updating the standby first, switching over, and then updating the old primary. In the Enterprise Plus edition, Google aims for near-zero downtime maintenance, with pauses measured in seconds. Details depend on the type of update, so check current documentation.

Self-service maintenance

You can sometimes ask for maintenance to happen sooner than the scheduled window, such as to receive a fix quickly. Some updates are optional and can be rescheduled, and others, particularly security updates, are required.

Major version upgrades

Engines release new major versions, such as a new number of PostgreSQL or MySQL. Cloud SQL supports in-place major version upgrades for some engines, and you can also upgrade by creating a new instance and migrating data. Major upgrades can change behavior, remove old features and change how queries are planned. Always test your application against the new version first. A good method is to clone the production instance, upgrade the clone, run your tests and compare speed. Only then upgrade production.

End of support

Old database versions eventually reach the end of their support period. After that, Google may charge extra for extended support, or require an upgrade. Keep a calendar of end-of-support dates, and plan upgrades months ahead, not in the final week.

Schema changes

Changing the structure of tables, such as adding a column or an index, can lock a large table and block users. Use methods designed for live systems, such as adding indexes in a way that does not block writes where the engine supports it, making changes in small steps, and testing on a clone. Avoid large schema changes at peak times.

You cannot fix what you cannot see. Monitoring turns a mysterious slow database into a clear picture.

The three kinds of signals

Metrics are numbers over time, such as processor use. Logs are written messages about events, such as a slow query or a failed login. Alerts are messages sent when something needs attention. Together they answer: Is something wrong? What went wrong? Who must act?

Metrics Cloud SQL provides

Cloud SQL sends metrics to Cloud Monitoring automatically. Important ones include processor utilization, memory utilization, disk utilization and disk read and write activity, the number of connections, queries per second, network traffic, and for replicas, the replication lag. Names can change, so check the current list in the console.

What to watch

Query Insights

Query Insights shows the top queries by total time, the frequency and the load they cause, and can connect a slow spike to the application code and the user that sent it. It is the first place to look when a database is slow. Use it regularly, not only in emergencies.

Logs

Cloud SQL writes engine logs, such as error logs and, if enabled, slow query logs and audit logs, to Cloud Logging. Admin actions are in Cloud Audit Logs. You can search them, chart them and send copies to storage or other tools. Turn on the slow query log with a sensible threshold, and keep an eye on log volume, since very verbose logging costs money and can slow the database.

Recommendations

Google Cloud provides automatic recommendations that spot patterns, such as an oversized instance, an instance with no backups, a database with open access or a table that would benefit from an index. Review them monthly as a free source of ideas.

Alerts that help, not annoy

Alert on symptoms that matter to users and that a person can act on, such as disk almost full, connections near the limit, high replica lag, failed backups, or long queries blocking others. Avoid alerts that fire constantly for small things. Attach a runbook, a short written list of steps, to each alert, so whoever is on duty knows what to do.

Golden signals for databases

• Latency: how long queries take, especially the slowest few percent.
• Traffic: queries or transactions per second.
• Errors: failed queries, failed connections, deadlocks.
• Saturation: how close processor, memory, disk and connections are to their limits.

A managed database costs more than a bare machine, and the bill has several parts. Understanding them is how you avoid surprises.

What you pay for

• Compute: processors and memory, billed for every hour the instance is running, at a rate that depends on the engine, the edition, the size and the region.
• Storage: the provisioned disk, billed per gigabyte per month, with different prices for SSD and HDD.
• Backups: the stored backup data beyond what is included, and the log storage for point-in-time recovery.
• High availability: the standby adds compute and storage cost, so the instance costs roughly twice a single one.
• Read replicas: each replica is billed as its own instance, with its own compute and storage.
• Network: traffic leaving Google Cloud and some traffic between regions.
• Licenses: for SQL Server, the license cost is part of the hourly price and can be a large share.

Prices change and differ by region and edition. Always read the current pricing pages and use the pricing calculator before you commit to a design.

Ways to save money

1. Right-size. Use the monitoring charts and recommendations to match size to the real load.
2. Use the right edition. Pay for the higher edition only where its benefits matter.
3. Use high availability only where needed. Development and test instances rarely need it.
4. Stop idle non-production instances. A stopped instance does not charge for compute, though storage still costs. Schedules can stop test databases at night and on weekends.
5. Delete unused instances, replicas and old backups. Use labels and owners to find them.
6. Use committed use discounts for steady production instances, where Google offers a lower price in return for a one- or three-year promise.
7. Avoid over-large storage. Storage can grow but not shrink, so grow in steps, and archive old data to cheaper storage.
8. Reduce unnecessary logging and long backup retention if your rules do not require them.
9. Cache and tune. Cheaper than a bigger machine, as discussed earlier.
10. Set budgets and alerts so someone is told before costs grow too far.

Compare the options on total cost

A pattern is a proven way to solve a common problem. An anti-pattern looks reasonable but causes pain later.

Patterns that work

Anti-patterns to avoid

This chapter gathers the habits of teams that run databases well, and the mistakes of those who learned the hard way.

Best practices: planning

• Choose the engine your application and team already know, and use a currently supported version.
• Choose the edition and high-availability setting from the real cost of downtime, not just the monthly bill.
• Place the database in the same region as most of its applications, to cut delay on every query.
• Use separate projects and networks for development, testing and production.
• Define instances, users, flags and backup settings as code with a tool such as Terraform, so every change is reviewed and repeatable.
• Label every instance with owner, project, environment and cost center.

Best practices: reliability

• Enable high availability for production, and test a manual failover.
• Turn on automated backups and point-in-time recovery, and test restores each quarter.
• Write applications to retry connections and to cope with short pauses.
• Use read replicas for reporting, and a cross-region replica when the business must survive a regional disaster.
• Set maintenance windows in quiet times and deny periods around critical dates.

Best practices: performance

• Check Query Insights every week and fix the top queries first.
• Index columns used in filters, joins and sorting, and remove unused indexes.
• Use connection pooling, and keep transactions short.
• Size memory so that hot data fits, and add storage if disk limits are reached.
• Add caching for data that is read often and changes rarely.

Best practices: security

• Use private IP, the Auth Proxy or connectors, and require SSL.
• Use least-privilege IAM, separate database users per application and IAM database authentication where possible.
• Keep secrets in Secret Manager and rotate them.
• Use customer-managed keys when your rules demand control of keys, and enable audit logging.
• Keep test and development data masked or fake.

Common mistakes

Most teams do not start with an empty database. They have data that lives somewhere else and must be moved without stopping the business.

Migration styles

Database Migration Service

Database Migration Service, or DMS, is Google’s managed tool for moving databases into Cloud SQL. For same-engine moves, such as MySQL to MySQL or PostgreSQL to PostgreSQL, it can do an initial copy and then continuous replication, so your old and new databases stay in step until you cut over. It handles many details and gives you a view of progress. Some paths use separate tools or offer conversion helpers for moves between different engines, so check the current support list.

A safe migration path

Things that surprise teams

• Unsupported features. Cloud SQL does not allow every extension, flag or privilege. Check your list early.
• Users and permissions. These may need to be recreated with the proper limited roles, since you do not get a full superuser.
• Latency. If the application stays in the old site while the database moves to the cloud, every query crosses a long link and pages may feel slow. Move tightly connected parts together.
• Large objects and special data types may need extra care during copy.
• Sequences and identity counters must be set correctly after the move, or new rows may clash with old ones.
• Time zones and character sets can differ, causing subtle data differences. Test carefully.
• Cost shape. Cloud billing is ongoing and flexible, so a lifted-and-moved system may cost more than expected until right-sized.

After the move

Review the sizing against real load, set up alerts, test failover and restores, enable recommendations and schedule regular reviews. A migration is not finished at cutover. It is finished when the new system is measured, protected and understood by the people who run it.

Let us look at the kinds of work that managed relational databases carry every day. Companies seldom publish detailed designs, so these examples describe typical patterns in each industry.

What these stories have in common

Every example shares the same ideas. A relational database holds the data that must be correct. The application connects through a safe, encrypted path with limited users. High availability handles failures, backups and point-in-time recovery handle mistakes, and replicas handle extra reading. Caches and pooling protect the database from needless work. Costs follow size, availability level and housekeeping. The industries differ, but the design habits are the same.

Short, direct answers to questions that beginners ask most often.

You have now seen Cloud SQL from the outside and from the inside. Here is the whole story in one place.

Cloud SQL gives you a real MySQL, PostgreSQL or SQL Server database that Google installs, patches, backs up and watches. Applications connect through a safe, encrypted path to a primary instance. Every change goes into a log first, and that log feeds the standby, the replicas and the rewind feature. You choose the size, the edition and the availability level, and you design the queries, indexes, users and recovery plan that make the database fast, safe and dependable.