Define SLOs, Workload Classes, Data Model, Capacity Forecast, and Architecture Decisions

Learning outcomes

ServiceHub has outgrown its “one database server and some indexes” phase. The application now schedules field-service work for multiple tenants, receives mobile updates, generates operational reports, and must survive operator mistakes and host failure. The engineering mistake at this stage is to choose PostgreSQL knobs or high-availability topology first and write requirements afterward. A defensible production design reverses that order: define measurable service-level objectives (SLOs), classify work, forecast capacity, then derive architecture and record why alternatives were rejected.

1. Create a disposable capstone cluster, not a production-like accident

The capstone uses one dedicated PostgreSQL database cluster: one server instance/data directory containing databases. That is different from an HA cluster, which is an operational topology of primary/standby servers. Later lessons add a standby and a restore target, but all destructive work stays under a dedicated ch24_lab directory.

export CH24_ROOT="$PWD/ch24_lab"export CH24_PRIMARY="$CH24_ROOT/primary"export CH24_STANDBY="$CH24_ROOT/standby"export CH24_RESTORE="$CH24_ROOT/restore"export CH24_ARCHIVE="$CH24_ROOT/archive"export CH24_BACKUP="$CH24_ROOT/basebackup"mkdir -p "$CH24_ROOT" "$CH24_ARCHIVE"initdb \  --pgdata="$CH24_PRIMARY" \  --username=postgres \  --auth-local=trust \  --auth-host=scram-sha-256 \  --pwpromptpg_ctl -D "$CH24_PRIMARY" \  -l "$CH24_ROOT/primary.log" \  -o "-p 55480 -c listen_addresses=127.0.0.1" startcreatedb -h 127.0.0.1 -p 55480 -U postgres servicehub_capstone

$env:CH24_ROOT = Join-Path $PWD "ch24_lab"$env:CH24_PRIMARY = Join-Path $env:CH24_ROOT "primary"$env:CH24_STANDBY = Join-Path $env:CH24_ROOT "standby"$env:CH24_RESTORE = Join-Path $env:CH24_ROOT "restore"$env:CH24_ARCHIVE = Join-Path $env:CH24_ROOT "archive"$env:CH24_BACKUP = Join-Path $env:CH24_ROOT "basebackup"New-Item -ItemType Directory -Force $env:CH24_ROOT,$env:CH24_ARCHIVE | Out-Nullinitdb.exe `  --pgdata=$env:CH24_PRIMARY `  --username=postgres `  --auth-local=trust `  --auth-host=scram-sha-256 `  --pwpromptpg_ctl.exe -D $env:CH24_PRIMARY `  -l (Join-Path $env:CH24_ROOT "primary.log") `  -o "-p 55480 -c listen_addresses=127.0.0.1" startcreatedb.exe -h 127.0.0.1 -p 55480 -U postgres servicehub_capstone

--pwprompt avoids embedding the PostgreSQL superuser password in course files. Host connections use SCRAM-SHA-256 authentication from the start. The local trust rule exists only to make a disposable same-host lab recoverable; do not copy it into a production HBA policy.

SELECT version() AS server_version,       current_database(),       current_setting('server_version_num') AS server_version_num,       current_setting('data_directory') AS data_directory,       current_setting('port') AS port;SELECT name,setting,context,source,pending_restartFROM pg_settingsWHERE name IN (  'wal_level',  'max_connections',  'shared_buffers',  'archive_mode',  'max_wal_senders',  'max_replication_slots')ORDER BY name;

Save this baseline with the capstone evidence. It proves what the running server actually accepted; it does not prove that the psql/libpq/client-driver versions or operating-system packages match, so record those separately.

2. SLOs are acceptance tests, not aspirational prose

An SLO is a measurable target for a service property. Availability asks whether the database-backed service can accept correct work. Latency specifies response-time distributions, not averages. Durability describes how much acknowledged data may be lost. RPO is the maximum acceptable data-loss interval after recovery; RTO is the maximum acceptable elapsed recovery time. These targets can conflict: synchronous replication can tighten RPO while increasing commit latency, and a very small RTO can justify more operational complexity.

3. Classify work before forecasting capacity

“500 concurrent users” is not a database workload. Some users are thinking, some API requests are waiting on networks, some transactions are short writes, and some reports scan many rows. PostgreSQL's process-per-connection architecture means server connections are real backend processes, so client concurrency and simultaneously active database work must be modeled separately.

WITH workload(class, peak_clients, target_active_backends, tx_per_second, notes) AS (  VALUES    ('write_api', 450, 24, 180, 'short OLTP transactions'),    ('read_api', 700, 16, 260, 'key/tenant lookups'),    ('ops_reports', 60, 6, 8, 'seconds-scale aggregates'),    ('maintenance', 4, 3, NULL, 'vacuum/analyze/index/backup coordination'))SELECT *FROM workloadORDER BY class;

The backend counts are design hypotheses to validate with load tests, not settings to paste into max_connections. If clients greatly outnumber useful database concurrency, a connection pooler becomes an admission-control option. The mandatory course path remains core PostgreSQL; an external pooler such as PgBouncer is optional and must be versioned/tested separately.

4. Forecast growth with explicit assumptions

A capacity forecast should expose every multiplier so a future engineer can replace assumptions with observed bytes/row, events/order, retention, index ratio, WAL amplification, backup copies, and headroom. PostgreSQL physical size is measured later with pg_total_relation_size; the first forecast is deliberately transparent rather than falsely precise.

WITH a AS (  SELECT    120000::numeric AS new_orders_per_day,    5::numeric      AS events_per_order,    13::numeric     AS online_months,    900::numeric    AS estimated_order_bytes,    260::numeric    AS estimated_event_bytes,    1.45::numeric   AS table_plus_index_factor,    1.35::numeric   AS free_space_and_growth_headroom),calc AS (  SELECT *,    new_orders_per_day * 30.4 * online_months AS online_orders,    new_orders_per_day * events_per_order * 30.4 * online_months AS online_events  FROM a)SELECT round(online_orders) AS orders,       round(online_events) AS events,       pg_size_pretty((         (online_orders * estimated_order_bytes +          online_events * estimated_event_bytes) *          table_plus_index_factor *          free_space_and_growth_headroom       )::bigint) AS rough_online_footprintFROM calc;

This calculation does not predict real storage. It excludes WAL archive volume, temporary spill, dead tuples, backups, standby copies, logs, filesystem overhead, TOAST behavior, compression outside PostgreSQL, and actual index definitions. Its purpose is to force those categories into the design conversation.

SELECT current_database(),       pg_size_pretty(pg_database_size(current_database())) AS database_size;SELECT n.nspname AS schema_name,       pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS total_relation_sizeFROM pg_class AS cJOIN pg_namespace AS n ON n.oid=c.relnamespaceWHERE c.relkind IN ('r','p','m')  AND n.nspname IN ('app','auth','ops')GROUP BY n.nspnameORDER BY n.nspname;

After the schema/data exist, replace estimated bytes with these measured relation/database totals and trend them over time. Database size still excludes WAL archive copies, operating-system logs, backup copies, and filesystem free-space requirements.

# Linux/macOS:df -h "$CH24_ROOT"# Windows PowerShell equivalent:# Get-Volume | Select-Object DriveLetter,Size,SizeRemaining

Capacity acceptance must include host/storage free space as well as PostgreSQL relation size. A database that fits today can still breach availability when WAL, temporary files, backups, logs, or retained replication-slot WAL exhaust the volume.

5. Derive the data model from write/read/retention behavior

ServiceHub's current order row is frequently read and updated; keeping it in one ordinary table avoids partition-key constraints on every uniqueness rule. The append-heavy event history grows much faster and is retained by time, so it is the partition candidate. This is a workload/retention choice—not a claim that partitioning automatically accelerates queries.

6. Derive HA and backup from the RPO/RTO contract

A physical standby replays WAL and can reduce failover time, but an asynchronous standby can lag and is not a backup against accidental DELETE. Point-in-time recovery (PITR) combines a physical base backup with continuous WAL archive so you can restore to a chosen point before an operator mistake. These mechanisms solve different failures, so the capstone uses both.

Clients  |  +--> routing / application config          |          +--> PostgreSQL 18.4 primary :55480          |       |          |       +--> WAL stream --> physical standby :55481          |       |          |       +--> WAL archive --> immutable/off-host target in production          |          +--> PITR restore drill :55482 (normally stopped)Core PostgreSQL provides streaming replication and promotion.Fencing the failed primary and rerouting clients are external operational responsibilities.

7. Write ADRs with rejection criteria

An architecture decision record (ADR) states the context, decision, alternatives, measurable consequences, and review trigger. “Use PostgreSQL because it is robust” is not an ADR. The decision must be falsifiable.

ADR-024-01 — Partition only work_order_eventsContext:- event history dominates growth and has 13-month online retention- work_orders needs simple tenant-scoped uniqueness and frequent point updatesDecision:- keep work_orders unpartitioned- range-partition work_order_events monthly on occurred_atRejected:- partition every table by tenant: complicates operational balance and uniqueness- partition work_orders by month: current orders span months and need stable OLTP keysAcceptance evidence:- EXPLAIN shows pruning for time-bounded event queries- partition creation exists before month rollover- retention detach/drop runbook stays within maintenance SLOReview trigger:- measured work_orders size/churn or tenant skew makes current layout breach SLO

8. Deliberately wrong approach: “HA means zero RPO”

The repair is not automatically “turn on synchronous replication.” First decide whether zero-loss is actually required, then measure the latency/topology consequence. This capstone's explicit RPO is ≤60 seconds, so asynchronous replication plus tested WAL archive is consistent with the stated requirement. A stricter contract would require a different design and acceptance test.

9. Build the acceptance matrix before implementation

WITH acceptance(area, test, pass_rule) AS (  VALUES    ('correctness','cross-tenant RLS test','tenant A cannot read/write tenant B rows'),    ('latency','representative OLTP replay','p95 <= 150 ms under declared load profile'),    ('vacuum','dead-tuple recovery','autovacuum/manual evidence clears churn without wraparound risk'),    ('backup','restore drill','pg_verifybackup passes and target data/business invariants pass'),    ('RPO','PITR target','restore reaches named point within <= 60 s loss contract'),    ('RTO','failover drill','fence + promote + route + verify <= 15 min'),    ('upgrade','supported-version runbook','release notes + pg_upgrade --check + workload comparison'))SELECT * FROM acceptance ORDER BY area;

The database can help produce evidence, but SQL alone cannot prove DNS routing, storage durability, operator response time, certificate validation, or business correctness. The final lesson combines database and operational evidence into one design defense.

Authoritative references

Use current upstream PostgreSQL documentation and release/support pages as the source of truth for version-, security-, topology-, and recovery-sensitive behavior.

• PostgreSQL Versioning Policy
• PostgreSQL 18 — Server Administration
• PostgreSQL 18 — High Availability, Load Balancing, and Replication
• PostgreSQL 18 — Backup and Restore
• PostgreSQL 18 — Table Partitioning