In lesson 02-03 we migrated the tienda database to Cloud SQL and everything fitted nicely: products, orders, customers and stock are textbook relational data. But as the application has been built, things have appeared that fit badly in that model. The shopping cart changes with every click and does not need transactions across several tables. User sessions are read on every request and thrown away after a few days. And Lucía wants to record every click on every product to find out what gets looked at and not bought: that is millions of events a month which, put into PostgreSQL, would grow the database until it choked the part that really matters.

Putting all of that into a relational database is not impossible, but it is forcing the tool. In this lesson you will see why other data models exist, you will understand consistency and the CAP theorem without dogma, you will get to know Firestore, Bigtable, Spanner and Memorystore with real AlpinaShop examples, and you will finish with a decision tree and the documented allocation of which data lives where.

1. Why not everything fits in a relational database
2. Consistency and the CAP theorem without dogmatism
3. Firestore: a document database
4. Firestore at AlpinaShop: cart and sessions
5. Bigtable: ordered rows and wide columns
6. Bigtable at AlpinaShop: click telemetry
7. Spanner: distributed relational
8. Memorystore: caching with Redis
9. Cross-cutting comparison table
10. Decision tree
11. AlpinaShop's decision

The relational model has been working for fifty years and is still the right answer most of the time. Its virtues are enormous: a universal declarative language, referential integrity, ACID transactions, normalisation that avoids duplicating data and an optimiser that resolves queries you had not anticipated.

Its limits appear on three specific fronts:

• Write scale. PostgreSQL scales vertically. When a single machine can take no more, that is that. Spreading writes across several nodes while maintaining ACID is a very hard problem.
• Data with no fixed schema or heavily nested. A cart with products, quantities, options and promotions can be modelled with five tables and five JOINs, or stored as a single document that is read in one go.
• Extreme volume with very simple access. Billions of events of which you only ever ask "all of them for product X between these two dates" do not need a full SQL engine; they need a key-ordered store that is very fast.

From that come the major data models:

The term "NoSQL" is misleading: it does not mean "without SQL" — Spanner is pure SQL and Firestore has its own query language — but "not exclusively relational". The useful reading is "Not Only SQL": use the tool that suits each piece of data.

The CAP theorem says that a distributed system cannot simultaneously guarantee the following three properties:

• C (Consistency): every read returns the most recent write.
• A (Availability): every request gets a response, even if it is not the most current one.
• P (Partition tolerance): the system carries on working even if the network splits between nodes.

The popular formulation "choose two out of three" is misleading. In a real distributed system network partitioning is not optional: cables get cut, data centres become isolated. So P is always there, and the real choice is what to do during a partition: answer with possibly stale data (AP) or reject the request so as not to lie (CP).

And the nuance that is usually left out: nearly all modern systems are configurable, and the choice is made per operation, not once and for all.

Types of consistency you will come across:

The practical consequences, which are what matter:

• When paying for an order, you want strong consistency. Nobody should buy the last tent twice. Cloud SQL or Spanner.
• On a "127 people are viewing this" counter, eventual consistency is more than enough. Saying 125 for two seconds harms nobody.
• In the cart, you want the user to see their own changes instantly, but it does not matter if another device takes a second to find out.

The right question is never "is this database consistent?", but "what happens if this particular piece of data is a second out of date?". If the answer is "nothing", you have design freedom and far more performance and cost options.

Firestore stores documents — JSON-like structures — inside collections. A document can contain subcollections, forming a hierarchy.

Key characteristics:

• No fixed schema. Two documents in the same collection can have different fields. Flexible and dangerous in equal measure: the discipline is up to you, in the code.
• Strong consistency on document reads, even in the multi-region configuration. It is a notable difference from other document databases.
• ACID transactions across several documents.
• Real time. A client can subscribe to a document or a query and receive changes automatically, without polling.
• Offline mode. The mobile and web SDKs keep a local cache and synchronise when the connection returns.
• Declarative security rules: clients can access the database directly with no intermediate backend, with the rules controlling what each user can read and write.
• Automatic scaling without provisioning anything.

Indexes and queries. Firestore automatically indexes every field, which makes simple queries work with no configuration. But queries with several filters, or a filter plus an ordering, require an explicit composite index. The first time you run a query like that you will get an error... with a direct link for creating the missing index, a very well-judged usability touch.

Limitations you need to know before designing:

• A document cannot exceed 1 MiB. Carts and sessions fit comfortably; a growing history inside a document does not.
• A limit on sustained writes to the same document (on the order of one per second). A very active global counter requires the sharded counters technique.
• There are no JOINs. The data is denormalised: the product's name and price are duplicated inside the cart line, and that duplication is accepted.
• Aggregations are limited. There are count(), sum() and average(), but it is not an analytical database. For that, BigQuery.

Firestore has two modes: Native (the modern one, with real time and offline) and Datastore (compatibility with the old Cloud Datastore). For a new project, always Native.

eur3 is a European multi-region location. For a single region it would be europe-west1. The location is immutable, just as in Cloud Storage.

The cart is a textbook case for Firestore: one entity per user that is read whole, written to frequently, has a nested structure and does not need to be related to anything else at the moment of reading it.

Important points in the code:

• The transaction is necessary because we read the cart, modify it and write it back. Without it, two browser tabs adding products at the same time could tread on each other. Firestore retries the transaction automatically if it detects a conflict.
• SERVER_TIMESTAMP uses the server's clock, not the client's. Never trust the time on the user's device.
• The denormalisation is deliberate. We store nombre and precio inside the line. If the price changes tomorrow, the cart keeps the price at which it was added, which is also the correct business behaviour.

Sessions with automatic expiry:

That query with two inequality filters and an ordering requires a composite index. It is declared like this:

Firestore can additionally delete documents automatically based on a date field, which solves session cleanup without writing any process at all:

Security rules. If the browser or the mobile app access Firestore directly, the rules are the only barrier:

The if false rule does not block the backend: service accounts with IAM permissions bypass the security rules, which apply only to the client SDKs.

Bigtable is the system Google built for its own search indexes, and the one HBase was built on. Its model is deceptively simple:

• A table is a set of rows ordered lexicographically by their key.
• Each row has column families, and inside each family, arbitrary columns.
• Each cell can store several versions with a timestamp.
• The only efficient way to access data is by row key or by key range.

Strengths: consistently single-digit millisecond latency, millions of operations per second, scaling to petabytes, and horizontal growth by adding nodes without stopping the service.

Limitations, just as important: there are no secondary indexes (you can only search by row key), there are no JOINs, there are no transactions across rows (only atomicity within a row) and there is no SQL in the traditional sense.

The row key design is everything. Because rows are stored in order and distributed in contiguous blocks across nodes, the key simultaneously decides which queries will be efficient and whether the load will be spread well.

The problem to avoid is called a hotspot: if the keys are sequential — a timestamp at the front, for example — all the new writes land in the same block, that block goes to a single node, and that node saturates while the rest of the cluster is idle.

The rule sums up like this: start the key with a high-cardinality field that appears in your queries, and add the time afterwards. And design the key from the queries you are going to make, not from the structure of the data: in Bigtable, the access pattern is the schema.

Another frequent trick is to use an inverted timestamp (9999999999 - epoch) when you want the most recent events to come out first in a scan, since the ordering is always ascending.

Lucía wants to know which products get looked at a lot and bought little. That means recording every product page view, every add to cart and every removal: during the campaign, several million events a month. In PostgreSQL that table would grow out of control and compete with the orders for resources.

Note the end_key trick: since the ordering is lexicographic and $ (0x24) comes right after # (0x23) in ASCII, using {sku}$ as the upper bound captures exactly all the keys starting with {sku}#. It is the idiomatic prefix-scan pattern in Bigtable.

Data lifecycle. Bigtable can expire cells automatically by age or by number of versions, which stops the table growing indefinitely:

Where Bigtable fits in the data architecture. Bigtable stores and serves the events with minimal latency, but it does not answer "how many views by category were there in October": that is an analytical aggregation and its place is BigQuery. The usual pattern is for the application to write to Bigtable — or publish to the pedidos-nuevos Pub/Sub topic — and for a periodic process to export to BigQuery for the analysis. That whole circuit is module 4.

On cost, with the honesty it deserves: Bigtable bills per node per hour, whether in use or not, plus storage. One node costs on the order of several hundred dollars a month, and production recommends three. It has no free tier and does not scale to zero. For AlpinaShop's current volumes, Bigtable is a premature decision: the same events can be published to Pub/Sub and inserted straight into BigQuery at a far lower cost. We study it here because it is the right service when volume and latency requirements justify it, and because it is worth being able to recognise that moment. In section 11 we will document this decision.

Spanner is Google's answer to a problem that was considered unsolvable: a database that scales horizontally without giving up SQL, ACID transactions or strong consistency, even between continents.

How it does it:

• Automatic sharding of the tables into fragments distributed across nodes, which split and rebalance themselves.
• TrueTime: a time API backed by atomic clocks and GPS in Google's data centres, which bounds clock uncertainty to a few milliseconds and allows transactions to be ordered globally and correctly.
• Synchronous replication across zones and regions through Paxos consensus.

The result: strongly consistent transactions at global scale, with an availability SLA of up to 99.999 % in the multi-region configuration, and standard SQL (with the GoogleSQL dialect or PostgreSQL compatibility).

When it justifies its cost. Spanner is billed by processing units (from 100 PU, roughly a tenth of a node) plus storage, and a multi-region production configuration is substantially more expensive than an equivalent Cloud SQL instance. It is justified when:

• Writes exceed what the largest Cloud SQL or AlloyDB instance can absorb.
• You need active writes in several regions with strong consistency.
• The availability required is five nines and a one-minute failover window is unacceptable.
• You handle financial or global inventory data where an inconsistency has a real and direct cost.

Typical cases: banking, global gaming platforms, multinational inventory, booking systems.

For AlpinaShop, Spanner is oversized, and saying so clearly is part of learning to choose. A Spanish shop with seasonal peaks is several orders of magnitude away from needing it. If the company grew to operate across several continents with shared inventory, that would be the right conversation; and the natural intermediate step would be AlloyDB first, which multiplies performance while keeping PostgreSQL compatibility.

Memorystore is managed Redis (and Valkey and Memcached). It is not really a database: it is an in-memory cache with microsecond latency.

The basic tier is a single node with no replica — appropriate for a cache, where losing the data only means recalculating it; standard adds a replica and automatic failover.

Natural uses at AlpinaShop:

• Catalogue cache. The front page queries the same 50 products thousands of times a day. Caching them for 5 minutes removes almost all of that load from Cloud SQL.
• Sessions. An alternative to Firestore when all that matters is speed and losing them is tolerable.
• Rate limiting. Per-IP counters with automatic expiry.
• Lightweight queues and distributed locks.

Three warnings about caches, which cause more incidents than you might think:

• Every entry must have an expiry. With no TTL, a stale value can stay indefinitely.
• Invalidation is the hard part. When Marta changes a price, someone has to delete the corresponding key. If not, the shop shows the old price for minutes.
• Memorystore is only reachable by private IP inside your VPC. It requires the client to be on the same network; for App Engine or Cloud Run you need a serverless VPC access connector (03-01).

Two readings of this table are worth underlining. First: BigQuery is not an operational database. Its queries take seconds and its billing penalises frequent low-volume reads; never put it in the path of a web request. Second: latency is not everything. Bigtable is faster than Cloud SQL, but if your data is relational, choosing Bigtable means reimplementing by hand what SQL gave you for free.

The tree is a guide, not a dogma. Two pieces of advice go with it:

• When in doubt, start with Cloud SQL. It is the most versatile, the best known and the easiest to abandon if you get it wrong. Moving from a relational database to something else is always easier than the other way round.
• Do not use five databases just because you can. Each one adds a technology to operate, monitor, back up and learn. Complexity is paid for every day; the benefit only appears if the problem genuinely justifies it.

With all of the above, this is the documented allocation of AlpinaShop's data:

And what we do not use, with the reason why, because rejecting things on good grounds is as valuable as choosing:

• Spanner: oversized by several orders of magnitude. A Spanish shop does not need global transactions across continents. If transactional scale ever became tight, the previous step would be AlloyDB.
• Bigtable, for now: the right service for the problem, but premature for the current volume. Its per-node, per-hour billing, with no scaling to zero, is not offset by a few million events a month that BigQuery absorbs without breaking a sweat. The decision will be revisited when the telemetry grows by an order of magnitude or when individual events need reading with millisecond latency.

This is exactly the kind of decision that has to be documented and dated: not because it will not change, but so that a year from now it is understood why it was taken and on what evidence.

• Choosing NoSQL "because it scales" without needing it. Most applications never reach the limit of a well-sized Cloud SQL instance.
• Modelling Firestore as if it were relational. With no JOINs, making ten chained reads to compose a screen is slow and expensive. Denormalise.
• Designing a Bigtable row key with the timestamp at the front. Guaranteed hotspot: all writes to the same node.
• Using a pure hash as the row key. It spreads well, but you lose the ability to scan ranges, which is the reason for using Bigtable.
• Putting BigQuery in the path of a web request. Latency of seconds and billing by data scanned.
• Caching without a TTL. Eternally stale data.
• Forgetting to invalidate the cache when a price changes. The shop shows old prices.
• Trusting the client's clock. Use SERVER_TIMESTAMP in Firestore and server-side timestamps generally.
• Exceeding 1 MiB in a Firestore document by accumulating history inside it. Use subcollections.
• Leaving a Bigtable instance running "for testing". It bills per node per hour and does not scale to zero.
• Tip: always start with the relational database and move only when you have a measured reason.
• Tip: design the Bigtable schema from the queries, never the other way round.
• Tip: create Firestore's composite indexes from the link the error gives you. It is the fastest and most reliable route.
• Tip: enable TTL in Firestore for everything that expires; it saves you writing and maintaining a cleanup process.
• Tip: document and date the decision about which data goes to which database. Your future self will thank you.

For each piece of AlpinaShop data, state the service, a reasonable alternative and a two-line justification:

1. The price history of each product, for auditing changes (a few hundred rows a month).
2. Customer ratings and reviews, with free text, associated photos and nested replies.
3. The GPS position of the delivery drivers during a round, one reading every 5 seconds per driver.
4. The number of times the front page has been viewed today, shown on the internal panel.
5. The PDF invoices generated for each order.

1. Create a Firestore database in Native mode.
2. Write Python functions to add a line to the cart, change the quantity of a line and empty the cart, using a transaction where appropriate.
3. Write a query that returns the carts worth more than €100 with no activity in the last 48 hours.
4. Explain what composite index that query needs and how you would create it.
5. Write the security rule that lets each customer access only their cart and lets nobody read the sessions.

AlpinaShop wants to record in Bigtable, later on, the internal search engine's searches: term searched, number of results, whether there was a click, country and device. The queries planned are: (a) all the searches for a term within a date range; (b) the searches on a specific day, for export.

1. Propose a row key for query (a) and justify it.
2. Explain why that key does not work well for query (b) and what you would do about it.
3. Identify a key design that would produce hotspots and explain the mechanism.
4. Define the column families and which columns would go in each one.
5. State what expiry policy you would apply and why.

Solution 1

Solution 2

4. The query filters on two different fields and orders by both, so Firestore needs a composite index on (total, actualizado) in the carritos collection. The automatic single-field indexes are not enough when there is more than one inequality filter. When you run the query without the index, Firestore returns a FAILED_PRECONDITION error with a link that creates it with the exact definition; it can also be declared like this:

Solution 3

1. Row key for query (a):

The term goes first because it is high cardinality (thousands of different terms, which spreads the load well across nodes) and because it is the field that appears in the query: scanning from backpack_40l#20260805 to backpack_40l#20260812 returns exactly the searches for that term in that week, reading only contiguous rows. The random suffix avoids collisions between simultaneous searches for the same term.

2. That key does not work for query (b) — "all the searches on one day" — because the rows for a given day are scattered throughout the table, spread across all the terms: you would have to scan the whole table and filter. The options are:

• The recommended one: export to BigQuery and do the date-based queries there. Bigtable handles operational access by key; analytics over other dimensions is BigQuery's job.
• Maintain a second table with the key <date>#<salt>#<term>#<ts>, where <salt> is a number from 0 to N that artificially spreads the load and avoids the hotspot of a date prefix. It doubles the storage and requires writing twice.

3. A design that would produce hotspots:

Because rows are stored ordered by key and distributed in contiguous blocks across nodes, all the writes from the same instant share a prefix and land in the same block, managed by a single node. That node saturates while the rest of the cluster is idle: horizontal scaling stops working. It is exactly the same problem as with sequential keys like 0000001, 0000002.

4. Column families:

Grouping into families the columns that are read together improves performance, because Bigtable stores and retrieves each family independently. It is best to keep few families (ideally fewer than ten) and with short names, since the name is repeated in every stored cell.

5. Expiry policy: maxage=180d on both families. Searches have operational value for a few months — spotting terms with no results, tuning the search engine — and after that their value is purely historical, which is already covered by the export to BigQuery, where storage is far cheaper. Automatic expiry stops the table growing without limit and the storage cost rising indefinitely.

You no longer have a single tool for all your data. You know that the relational model is still the default answer and why — integrity, transactions, SQL, an optimiser that resolves queries you had not anticipated — and also where its three real limits lie: write scale, nested data with no fixed schema and extreme volume with trivial access. You have understood the CAP theorem without the usual dogmatism: network partitioning is not optional, the real choice is what to do during a partition, and in practice it is decided per operation. The useful question is not whether a database is consistent, but what happens if that particular piece of data is a second out of date.

You have got to know Firestore — documents and collections, strong per-document consistency, transactions, real time, offline mode, automatic TTL, security rules and the need for composite indexes — and you have stored AlpinaShop's cart and sessions in it, denormalising on purpose and using transactions where they were needed. You have got to know Bigtable — rows ordered by key, column families, no secondary indexes and no JOINs — and, above all, you have learned that the row key design is the whole design: start with a high-cardinality field, put the time afterwards, avoid the hotspots any sequential key causes, and model from the queries rather than from the data. You have placed Spanner as the answer to a very specific problem — global scale with ACID — and you have seen why it is not for AlpinaShop. You have added Memorystore as a cache with its three rules: always a TTL, invalidation is the hard part, and it is only reachable from the VPC.

The cross-cutting comparison table and the decision tree give you a reusable method, and AlpinaShop's data allocation is a documented and dated decision: Cloud SQL for products, orders and customers; Firestore for the cart and sessions; Memorystore for the front page cache; Cloud Storage for the images; Pub/Sub and BigQuery for the telemetry, with Bigtable explicitly and deliberately rejected for now.

With this, AlpinaShop has its compute solved at four levels and its data across five services. There remains the question that ties it all together and closes the module: faced with a specific workload, which compute service should you choose? In 02-07, How to Choose the Right Compute Service, we will travel the continuum of abstraction from VM to function, compare Compute Engine, GKE Standard and Autopilot, App Engine, Cloud Run and Cloud Functions against measurable criteria, calculate the cost of the same traffic scenario in each one, look at the lift-and-shift, replatform and refactor migration patterns, and document AlpinaShop's definitive architecture, which will serve as the basis for the rest of the course.