Lesson 2: The Standard Workload Profile

Why a profile comes before any product

In Level 1 you saw every Google Cloud database on one page. The quickest way to choose badly is to start from that list ("we like Spanner, let's use it"). Experienced teams start from the workload. They write down what the application actually does, as numbers, on one page. Every later lesson in this course measures a service against that page.

Think of it as a building survey before you buy a house. You don't judge the kitchen until you know the size of the family, the budget and whether the house has to cope with floods. The workload profile is that survey, and it has twelve fields:

Step 1: Measure, don't guess

The most common mistake is "about 1,000 QPS, I think". Database counters are cumulative, so take two snapshots over your busiest hour and subtract the first from the second. An average across the whole day hides the peak you actually have to size for.

-- PostgreSQL: run at 12:00 and again at 13:00 on your peak day, then diff
SELECT now(),
       xact_commit + xact_rollback          AS transactions,
       tup_returned + tup_fetched           AS rows_read,
       tup_inserted + tup_updated + tup_deleted AS rows_written,
       pg_size_pretty(pg_database_size(datname)) AS db_size
FROM pg_stat_database
WHERE datname = 'shop';

-- Slowest statements by mean time (needs the pg_stat_statements extension)
SELECT calls, round(mean_exec_time::numeric, 1) AS mean_ms, left(query, 60)
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

-- MySQL equivalent: cumulative counters, diff two snapshots
SHOW GLOBAL STATUS WHERE Variable_name IN
  ('Questions','Com_select','Com_insert','Com_update','Com_delete');

Worked result: one hour apart, transactions grows by 6,480,000. That is 6,480,000 / 3,600 = 1,800 TPS at peak. Reads outnumber writes about 9:1. Database size went from 410 GB to 422 GB over 30 days, so growth is roughly 12 GB a month. Take latency from the application's own traces (p99, not the average), because the end user feels the tail.

Gotcha: take a snapshot during a batch job or a seasonal peak such as Black Friday or month-end close as well. A profile built from a quiet Tuesday under-sizes everything.

Step 2: Turn business words into targets

The business says things like "orders must never be lost" and "we can't be down for long". Turn each one into a number:

• RPO (Recovery Point Objective) is how much recent data you can afford to lose, measured in time. "Losing the last 5 minutes of orders is the most we accept" gives RPO = 5 min. "Never lose a confirmed order" gives RPO ≈ 0, which needs synchronous replication.
• RTO (Recovery Time Objective) is how long you can take to be serving again. "Back within 15 minutes" gives RTO = 15 min.
• Availability in minutes, not "nines":

99.9%   -> ~43.8 min down per month  (~8.8 h/year)
99.99%  -> ~4.4 min per month        (~52.6 min/year)
99.999% -> ~26 s per month           (~5.3 min/year)

Every extra nine costs real money. For reference, Spanner documents at least 99.99% for regional configurations and 99.999% for dual-region and multi-region ones. Ask the business which row it can afford, not which one it would like.

Step 3: Mark the hard constraints first

Some fields are filters, not scores. If a filter fails, the service is out before any comparison starts. Mark them HARD in the profile:

• Engine lock-in. The app relies on PL/SQL packages or other Oracle-only features. Only the Oracle options stay in (Oracle Database@Google Cloud supports both bring-your-own-licence and licence-included). T-SQL and SQL Server Agent jobs rule out the PostgreSQL-only services.
• Licences you own. Existing Oracle or SQL Server licences change the cost picture and which services you can use.
• Multi-region writes with strong consistency. Users on two continents write to the same rows, and every read must see the latest write. Few services can do that. Spanner's multi-region configurations replicate synchronously and keep strong consistency. Bigtable's replication with multi-cluster routing is eventually consistent.
• Data residency. "EU customer data stays in the EU" limits regions for every service.

Step 4: Record scaling and the shard key

Write down how the workload grows. If a bigger machine covers three years of growth (scale up), sharding may never matter. If it won't, you scale out, and you have to name the key the data splits on:

• A good key spreads load evenly and keeps one transaction's rows together, for example tenant_id in a SaaS app.
• A bad key is a timestamp or auto-increment ID. Every new write lands on the "latest" end, which creates a hotspot.
• Note any queries that would have to cross shards ("monthly report across all tenants"). They point toward a separate analytics store.

The finished page

# workload-profile.yaml  - shop-orders, measured 2026-10-20 (peak hour)
data_model: relational (orders, order_lines, customers)
read_write_mix: 90/10
peak_tps: 1800            # measured delta, not estimate
latency_p99_ms: 20        # from app traces, checkout path
transactions: multi-row ACID (order + lines + stock)
consistency: strong on writes path; reports may be 15 min stale
size_gb: 422
growth_gb_per_month: 12
scaling: scale-up OK for 3 yrs; future shard key = tenant_id
availability: 99.95%
rpo: 5m
rto: 15m
multi_region: read replicas EU only; writes single region  # HARD: EU residency
engine: PostgreSQL 16 features used (jsonb, partitioning)  # HARD
licensing: none owned
workload_type: OLTP; nightly analytics -> separate store
team_skills: strong PostgreSQL, no Oracle DBA
cost_model: steady load, budget ceiling set by finance

Best practice: date the profile and keep it in the repo next to the code. When traffic doubles, update the numbers and run the comparison again rather than arguing from memory.

Key takeaways

• Build the profile before looking at products. It is the yardstick for every later lesson.
• Measure peak-hour deltas from database counters and p99 latency from traces. Don't use daily averages.
• Turn business phrases into RPO, RTO and availability in minutes. Each extra nine costs money.
• Mark hard constraints (engine, licences, multi-region strong writes, residency) so they filter services out first.
• Record whether you scale up or out. If out, name a shard key that spreads load evenly and avoid monotonically increasing keys.