Database options: Cloud SQL, Spanner, Firestore and Bigtable

The online shop runs on a MySQL database that Owen installed on a server in 2017. He patches it, backs it up every night, and once spent a Sunday restoring it after a disk failed. When Farah says that Google Cloud has more than half a dozen database services, Owen's first reaction is alarm. Her answer is that each one exists because different data behaves differently, and that most organizations need only two or three. "Pick by the shape of the data and the way it is used," she says, "not by the name you already know."

This lesson covers the four databases named in the course outline, Cloud SQL, Spanner, Firestore and Bigtable, and places a few others, such as AlloyDB, Memorystore and BigQuery, on the same map. It also introduces the vocabulary that Google Cloud exams use to describe data: relational and non-relational, transactional and analytical, regional and global.

Relational and non-relational data

Relational databases store data in tables of rows and columns with a fixed schema, link tables with keys, and are queried with SQL. They support transactions that follow the ACID properties: a group of changes either all happen or none do (atomicity), the data always obeys its rules (consistency), simultaneous transactions do not interfere (isolation), and committed changes survive failures (durability). Orders, payments, inventory and customer accounts are classic relational data, because their relationships and correctness matter.

Non-relational databases, often called NoSQL, store data in other shapes: documents (flexible records, often JSON-like), key-value pairs, wide-column tables with very many columns, or graphs. They usually relax the fixed schema, and many are built to scale out across many machines easily. They suit data whose structure varies, data read and written at enormous volume, or data that mobile apps sync in real time.

Owen tries the relational idea with SQLite, a small database built into Python, using a slice of the shop's data.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT, price REAL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, sku TEXT REFERENCES products(sku), qty INTEGER);
INSERT INTO products VALUES ('BT-201', 'Ridge hiking boot', 189.00), ('JK-310', 'Shell jacket', 249.00);
INSERT INTO orders (sku, qty) VALUES ('BT-201', 2), ('JK-310', 1), ('BT-201', 1);
""")
query = """SELECT p.name, SUM(o.qty) AS units, SUM(o.qty * p.price) AS revenue
           FROM orders o JOIN products p ON p.sku = o.sku GROUP BY p.name ORDER BY revenue DESC"""
for row in db.execute(query):
    print(row)

('Ridge hiking boot', 3, 567.0)
('Shell jacket', 1, 249.0)

The JOIN links orders to products through the SKU, and the database adds up units and revenue. This is what relational databases are good at, and it is why the shop's catalog and orders will stay relational in the cloud.

Transactional and analytical workloads

A second distinction is between online transaction processing (OLTP), many small, fast reads and writes such as placing an order or updating stock, and online analytical processing (OLAP), fewer but much larger queries that scan millions of rows to answer business questions, such as revenue by region and month. Databases are tuned for one or the other. Cloud SQL, Spanner, Firestore and Bigtable are operational databases for applications. BigQuery is Google Cloud's serverless data warehouse for analytics; it is introduced here so it is on the map and covered properly in GCP-102 and the data courses.

Cloud SQL

Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL and SQL Server. Google handles installation, patching, backups, replication and failover; the customer chooses the engine, version and machine size, designs the schema and writes the queries. Because it runs the standard engines, existing applications usually move to Cloud SQL with few or no code changes, which makes it the default choice for most traditional relational workloads.

For availability, Cloud SQL offers a high availability configuration in which a standby instance in a second zone of the same region receives every write synchronously and takes over automatically if the primary fails. Read replicas copy data asynchronously to other instances, in the same region or another, to spread read-heavy traffic or to keep a copy far away. Automated daily backups and point-in-time recovery let the team restore the database to any moment within the retention window, for example to just before someone ran a bad update.

Cloud SQL scales vertically, by moving to a larger machine, and its storage can grow automatically. A single primary instance handles the writes, which is plenty for the vast majority of applications, including Birchline's shop, but sets a ceiling for very large global systems. That ceiling is what Spanner is for.

Spanner

Spanner is Google's globally distributed relational database. It offers SQL, schemas and ACID transactions like a traditional relational database, but it scales horizontally: adding capacity spreads data and work across more machines, with no practical ceiling for most organizations. It keeps strong consistency even across regions, using Google's tightly synchronized clocks, so a reader anywhere sees the latest committed data. Multi-region Spanner configurations carry Google's highest database availability SLA, 99.999 minutes out of every hundred, which allows only about 26 seconds of downtime a month.

Spanner suits applications that need relational data and global scale or extremely high availability: global inventory, financial ledgers, gaming platforms and large retail systems. It costs more than Cloud SQL for small workloads and asks for some design care, such as choosing keys that spread writes evenly. Birchline's shop does not need Spanner today; Farah notes it as the path if Birchline ever runs a single store system across several continents.

Firestore

Firestore is a serverless NoSQL document database. Data is stored as documents, sets of fields, grouped into collections, and documents in the same collection can have different fields. Firestore scales automatically, needs no instances to size, and charges for reads, writes, deletes and storage. It supports transactions and strong consistency, and it can push changes to mobile and web apps in real time, with offline support on devices. Firestore is part of Firebase, Google's platform for app developers, as well as Google Cloud.

Birchline's mobile app team plans to use Firestore for shopping wish lists and in-app notifications: data that belongs to one user, varies in shape and must appear instantly on the phone. Firestore can also run in Datastore mode, which keeps compatibility with the older Datastore service for server-side applications.

Bigtable

Bigtable is a NoSQL wide-column database built for very large volumes of data with consistently low latency, from terabytes to petabytes, and very high read and write throughput. Rows are looked up by a single row key and can have thousands of columns grouped into column families. Bigtable is ideal for time series, IoT sensor readings, financial market data, user analytics and recommendation features, and it powers several of Google's own large services. It is compatible with the open-source HBase API.

Bigtable does not support SQL joins or multi-row transactions in the way relational databases do, and it is not economical for small datasets. Birchline has one candidate: the warehouse scanners and store sensors generate millions of readings a day, and Ravi's team may use Bigtable for them in future.

Other databases on the map

Choosing a database

Farah gives Owen a short set of questions and a decision flow.

• Is the data relational with transactions? If yes, does it need horizontal global scale or the highest availability? Then Spanner; otherwise Cloud SQL, or AlloyDB for demanding PostgreSQL work.
• Is it document data for apps, especially mobile, with flexible fields and real-time sync? Then Firestore.
• Is it huge volumes of key-based reads and writes, such as time series? Then Bigtable.
• Is it a cache in front of another database? Then Memorystore.
• Is it for analytics across large historical datasets? Then BigQuery.

Managed databases change Owen's job more than any other service. He no longer installs, patches or fails over the shop's MySQL; Google does. His work moves up the stack to schema design, query performance, access control, backup testing and capacity planning, the parts that actually need knowledge of Birchline's business.

Moving the shop database

Choosing Cloud SQL for MySQL is the easy decision; moving the live shop database without losing orders is the careful part. Google's Database Migration Service helps with exactly this. It connects to the source MySQL server, copies a full snapshot of the data to a new Cloud SQL instance, and then keeps applying every change from the source through continuous replication, so the cloud copy stays only seconds behind while the shop keeps running on the old server.

Farah walks Owen through the plan. Weeks before the cutover, the migration job starts and the two databases run side by side. The team tests the new instance with a copy of the shop pointed at it, checks row counts and spot-checks orders, and measures how far behind the replica runs at the busiest times. On cutover night the shop shows a short maintenance page, the last changes drain across, the team promotes the Cloud SQL instance to be the primary, and the shop's configuration switches to its address. The planned downtime is about fifteen minutes, against the hours a dump-and-restore would take for a database this size.

Connections matter too. Applications reach Cloud SQL through a private IP address inside the VPC network, or through the Cloud SQL Auth Proxy and connectors, which handle encryption and IAM-based authorization so that no database port is opened to the internet. Birchline uses private IP for the shop's Cloud Run services through a VPC connection, and IAM database authentication for staff who need to query it directly, so that leaving the company removes database access along with everything else.

Finally, Farah insists that backups are tested, not assumed. Once a month, the operations team restores the previous night's Cloud SQL backup into a scratch instance in the sandbox folder, runs a few checks, records how long it took, and deletes it. The first test took forty minutes and found that one reporting user had been forgotten in the restore notes. That is exactly the kind of gap a test is meant to find before a real failure does.