API + Database
Case Study: API and Database
An authenticated JSON API backing a mobile app: accounts, a handful of resources, reads and writes against relational data. A few hundred users at the start, a few tens of requests per second, one small engineering team. This is the most common shape of production system in the world, and it is the design every other case study in this set departs from. Every stage after the first exists because of a specific incident — not because a diagram looked incomplete.
Requirements
- Serve an authenticated HTTPS API with predictable latency for a mobile client.
- Store relational data with transactions — accounts, ownership, referential integrity.
- Survive an instance restart, a deploy and a routine patch without a user-visible outage.
- Recover from an accidental data deletion, with a stated and tested recovery point.
- Be operable by two engineers who also write the application.
Deliberately not requirements
Half of a design is what it refuses to do. These are the refusals.
Out of scope, on purpose
- No multi-region: a regional failure is a documented, accepted outage at this stage.
- No container orchestration: the workload is one service, and a control plane would be more software than the product.
- No independent scaling of subsystems, because there are no subsystems yet.
How the design got here
In order. Each stage leads with the problem that forced it.
Stage 1
One machine
Forced by
The requirement. An API and a PostgreSQL instance on a single VM with a public address and a DNS record serve a few hundred users comfortably, deploy in one step, and cost the price of one machine. This stage is not a strawman: for an internal tool or a product still looking for users, it can be the correct final design for a long time.
Everything on one box. Simple, cheap, and one restart from an outage.PROVIDER-NEUTRAL
Mobile apppublic
DNS A recordpublic
Regioninternal
Zone Ainternal
Public subnetpublic
App VMpublic— runs the API and PostgreSQL side by side
Attached volumeprivate— the database files; a cron job copies a dump to object storage nightly
Mobile app→DNS A record· resolve
Mobile app→App VM· HTTPScrosses boundary
App VM→Attached volume· reads and writes
| Decision | Reason | Alternative | Trade-off |
|---|---|---|---|
| One VM running both the API and the database. | Two engineers, one deployable, no network hop between application and data. The operational surface is a single machine you can reason about entirely. | A managed database from day one, which costs more and removes an entire category of work — a defensible starting point if the budget allows. | You own patching, backups, tuning, disk sizing and recovery. Most importantly you own the fact that the application and the database compete for the same CPU and the same page cache, which makes performance problems harder to attribute. |
| A nightly dump copied to object storage. | It is the cheapest thing that satisfies "recover from an accidental deletion", and it is enormously better than nothing. | Point-in-time recovery via continuous archiving, which is what you actually want and which is more machinery than this stage warrants. | The recovery point is up to 24 hours, and — the part that matters — nobody has ever restored it. Until a restore has been performed, this is a file, not a backup (§98). |
Stage 2
Load balancer, two instances, managed database
Forced by
A kernel security update required a reboot. The API was down for four minutes in the middle of the working day, and PostgreSQL came back needing recovery because the shutdown had been less graceful than expected. In the same month a disk-full event took the API down as well, because the application log and the database data shared a volume. One machine means one restart is an outage, and it means every resource is shared with everything else.
The tiers separate, and each one becomes replaceable on its own.PROVIDER-NEUTRAL
Mobile apppublic
DNSpublic— points at the load balancer, not at any instance
Regioninternal
Zone Ainternal
App instance Apublic— still in a public subnet at this stage
Managed PostgreSQL (primary)public— multi-zone standby enabled
⚠ reachable on a public endpoint — the finding that forces the next stage
Zone Binternal
App instance Bpublic
Load balancerpublic— terminates TLS on 443; the one intentional public entry point
Mobile app→DNS
Mobile app→Load balancer· HTTPScrosses boundary
Load balancer→App instance A· HTTP + health check
Load balancer→App instance B· HTTP + health check
App instance A→Managed PostgreSQL (primary)· SQL
App instance B→Managed PostgreSQL (primary)· SQL
| Decision | Reason | Alternative | Trade-off |
|---|---|---|---|
| A load balancer with two instances in two zones. | It converts "reboot the machine" from an outage into a routine operation, and it gives DNS a stable target that survives instance replacement. | Two instances with DNS round-robin, which costs nothing extra and cannot health-check, so a dead instance keeps receiving half the traffic until a TTL expires. | A new component with its own hourly cost, its own configuration, its own idle timeout, and a health check that will eventually be wrong in an interesting way. The application must also become stateless for this to mean anything. |
| Move the database to a managed service with a multi-zone standby. | Backups, patching, replication and failover are the provider's to run, and they are the tasks a two-person team does worst and least often. Failover in particular is not something you build correctly in an afternoon. | Self-hosted PostgreSQL on its own VM with your own replication, which is cheaper per gigabyte and appropriate when you have deep operational expertise or unusual extension requirements. | Roughly double the database spend for the standby, a per-write cross-zone latency cost from synchronous replication, and — the part people forget — you still own the schema, the indexes, the queries, the connection limits, the capacity choice and the bill (§57). Managed moved the boundary; it did not remove your half. |
| Instances become immutable and stateless. | Once two instances exist, any local state is a correctness bug waiting for a load balancer to expose it, and any hand-edited configuration is a difference between machines nobody has recorded. | Configuration management on long-lived instances, which is a legitimate model with a real ecosystem and a much longer path back to a known-good state. | Every change is a rebuild-and-replace, so the image pipeline becomes load-bearing and image build time becomes deployment latency. Debugging by SSH stops working, which is the adjustment engineers resent most. |
Stage 3
Private subnets, NAT, security groups
Forced by
A routine scan found the database endpoint answering on 5432 from the internet, and the database log showed authentication attempts from three continents within a day of the endpoint being created. Nothing had been breached; the only thing standing between a password and the customer table was the password.
One public entry point on purpose; everything else is unreachable from the internet.PROVIDER-NEUTRAL
Mobile apppublic
Regioninternal
VPCinternal
Zone Ainternal
Public subnet Apublic— load balancer and NAT only — no workloads
Load balancerpublic— public on 443 — this is the design, not a finding
NAT gateway Apublic— outbound only, per zone
Private subnet Aprivate
App instance Aprivate— security group accepts traffic only from the load balancer
Managed PostgreSQLprivate— security group accepts 5432 only from the app instances' group
Zone Binternal
Public subnet Bpublic
NAT gateway Bpublic
Private subnet Bprivate
App instance Bprivate
Mobile app→Load balancer· HTTPS 443crosses boundary
Load balancer→App instance A· HTTP
Load balancer→App instance B· HTTP
App instance A→Managed PostgreSQL· SQL 5432
App instance B→Managed PostgreSQL· SQL 5432
App instance A→NAT gateway A· outbound to providerscrosses boundary
App instance B→NAT gateway B· outbound to providerscrosses boundary
| Decision | Reason | Alternative | Trade-off |
|---|---|---|---|
| Workloads move into private subnets; only the load balancer and the NAT gateways stay public. | It removes direct internet reachability from every component that has no reason to be reachable, so an attacker must first defeat something you designed to be attacked. | Keep everything public and rely on security groups alone, which is genuinely defensible — a security group is the enforcement point either way — and leaves one misconfigured rule between the internet and your data. | Every private workload now needs a NAT to reach anything outbound, which is a per-zone hourly charge plus per-gigabyte processing, and debugging gets harder: you need a bastion, a session manager or a VPN to reach a machine at all. |
| Security groups reference other security groups, not IP ranges. | The rule then expresses the intent — "the database accepts connections from application instances" — and keeps being true as instances are replaced and addresses change. | CIDR-based rules, which are necessary for anything outside the VPC and go stale the moment a subnet is resized. | It is provider-specific behaviour rather than a general networking concept, and the rules read less obviously to someone who has only ever configured a firewall by address. |
| One NAT gateway per zone, not one shared. | A single NAT is a single point of failure for every outbound call in every zone, and it fails in the most misleading way available: health checks stay green while every external call times out. | One NAT for the whole VPC, which halves the fixed cost and is a common, deliberate choice for non-critical environments. | A second hourly charge and a second thing to monitor. This line item is deleted in cost reviews more often than any other on this list — write the reason next to the number. |
Stage 4
Secret manager and workload identity
Forced by
The database password was set by hand as an environment variable on each instance. Rotating it meant editing every machine; one was missed and served errors for an hour. Then a new engineer found the same password in a deploy script in the repository, where it had been since the first week and was visible to everyone who had ever cloned it.
The credential path becomes explicit, auditable and rotatable.PROVIDER-NEUTRAL
Regioninternal
VPCinternal
Private subnetsprivate
App instancesprivate— no credentials in the image, in the environment or on disk
Managed PostgreSQLprivate
Secret managerprivate— reached over a private endpoint; every read is recorded in the audit trail
Key serviceprivate— encrypts the secret and the database volume
Instance roleinternal— may read exactly one secret and decrypt with exactly one key
App instances→Instance role· assume at boot
App instances→Secret manager· fetch credentialcrosses boundary
Secret manager→Key service· decrypt
App instances→Managed PostgreSQL· connect with fetched credential
| Decision | Reason | Alternative | Trade-off |
|---|---|---|---|
| The instance carries an identity, not a credential. | A workload should not borrow a human's key or hold a static one (§60). An identity attached to the instance produces short-lived credentials automatically, so there is no string to leak, to rotate or to commit. | A long-lived access key injected at deploy time, which works everywhere and is a permanent, un-expiring liability sitting in whatever system injected it. | Local development needs a documented path that is not "use the production credential", and the identity mechanism is one of the most provider-specific parts of any design — portability suffers, honestly and unavoidably. |
| The application fetches the database credential at startup and refreshes it. | It makes rotation an operation instead of an incident, and it removes the credential from images, environment variables and process listings. | Inject the secret as an environment variable at deploy time, which is far simpler and means rotation requires a full redeploy of every instance. | The secret manager becomes a hard dependency in the startup path, so it needs a private endpoint, a cache and a sensible behaviour when it is briefly unavailable. An application that cannot start because a secret fetch timed out is a new failure mode you just bought. |
| Encryption at rest with a customer-managed key. | It puts key usage in the audit trail and makes revocation possible, which is the difference between encryption as a checkbox and encryption as a control. | Provider-managed keys, which are simpler, cost less and are entirely adequate for many threat models. | Key lifecycle becomes yours: a deleted or disabled key is unrecoverable data, and keys are regional, which quietly constrains any future cross-region plan. |
Stage 5
Metrics, logs and alerts
Forced by
A customer reported an outage on social media forty minutes before anyone internal noticed. The instances had been failing readiness for most of that time; nobody was looking, because nothing was configured to look.
The signals path — the only part of the system whose job is to tell you the truth.PROVIDER-NEUTRAL
External synthetic checkpublic— runs a real HTTPS request from outside the region — the only check that survives a regional failure
Load balancerpublic— emits target health, request count, latency percentiles, 5xx
App instancesprivate— structured logs shipped off-instance; /healthz shallow, /readyz checks the database
Managed PostgreSQLprivate— connections, replication lag, free storage — the metric that predicts an outage days ahead
Metrics and log storeprivate— outside the failure domain it observes
Alertingprivate— pages on symptoms users feel, not on every anomaly
External synthetic check→Load balancer· synthetic HTTPScrosses boundary
Load balancer→Metrics and log store· metrics
App instances→Metrics and log store· logs and metrics
Managed PostgreSQL→Metrics and log store· metrics
Metrics and log store→Alerting· threshold and absence rules
| Decision | Reason | Alternative | Trade-off |
|---|---|---|---|
| Separate liveness from readiness. | Liveness answers "should this process be restarted?" and must stay shallow; readiness answers "should this instance receive traffic?" and may check dependencies. Conflating them means a two-second database blip restarts your entire fleet. | A single health endpoint, which is one less thing to explain and is wrong in both directions at the worst possible moment. | Two endpoints, two configurations, and a genuine risk that a deep readiness check ejects every instance simultaneously when the shared dependency wobbles. It needs a floor. |
| An external synthetic check over real TLS. | It is the only monitor that exercises DNS, the internet path, the certificate and the application together — and the only one that still works when the region it watches is gone. | Internal health checks only, which are cheaper and cannot see any failure that happens at or beyond the perimeter — expired certificates included. | A third-party dependency, a small bill, and alert tuning so that ordinary internet weather does not page anyone at 3am. |
| Ship logs off the instance immediately. | Logs on a machine that is gone are not logs, and immutable instances are replaced constantly. It also stops log growth from filling the volume the application runs on. | Local files with rotation, which is free and forfeits every investigation involving a terminated instance. | Log ingestion is billed by volume and is one of the most reliably underestimated line items in cloud infrastructure. Retention becomes a deliberate decision about how long your investigation window is. |
Stage 6
Backups, point-in-time recovery, and a rehearsed restore
Forced by
A migration dropped a column in production at 14:00. The attempted restore revealed that the nightly dump had been failing for eleven days, because the job's role had lost a permission during an unrelated cleanup and the failure was recorded only as a non-zero exit code in a scheduler nobody watched.
Recovery as a designed path, not a hope.PROVIDER-NEUTRAL
Primary regioninternal
Managed PostgreSQLprivate— automated snapshots plus continuous log archiving
Snapshot and log archiveprivate— point-in-time recovery to any second within the retention window
Restore rehearsal jobprivate— monthly: restores into a scratch instance, runs a row-count and integrity check, records the elapsed time
Second regioninternal— holds copies only — nothing runs here
Cross-region backup copyprivate— survives the loss of the primary region and of the account's primary key
Absence alertprivate— pages if no successful backup object appeared in the expected window
Managed PostgreSQL→Snapshot and log archive· snapshots and WAL
Snapshot and log archive→Cross-region backup copy· cross-region copy
Snapshot and log archive→Restore rehearsal job· restore into scratch
Restore rehearsal job→Absence alert· reports elapsed time and result
| Decision | Reason | Alternative | Trade-off |
|---|---|---|---|
| Point-in-time recovery, not just nightly snapshots. | The failure you actually get is "someone ran the wrong statement at 14:00", and a nightly snapshot answers that with "we lose today". Continuous log archiving lets you stop the clock one second before the mistake. | Daily snapshots alone, which are far cheaper in storage and give you an RPO of up to a day. | Continuous archive storage that grows with write volume, a retention window you must choose and pay for, and a restore procedure with more steps — which is exactly why it must be rehearsed. |
| Copy backups to a second region and a separate trust boundary. | A backup in the same region and the same account as the thing it protects shares a failure domain with it — including the failure where credentials are compromised and everything is deleted together. | Same-region backups only, which are cheaper, faster to restore from, and useless in precisely the two scenarios you keep backups for. | Cross-region transfer and duplicate storage cost, plus key management across regions — an encrypted backup whose key is regional is not a cross-region backup. |
| Alert on the absence of a successful backup, not on the failure of the backup job. | This exact incident was a job that failed in a way nothing was listening for. "An object matching this pattern must exist by 03:00, or page" catches every cause, including the ones you have not imagined. | Alert on job exit status, which catches the common case and misses the job that was silently descheduled, deleted, or never ran at all. | A monitor that must be kept in step with the schedule; a stale expectation becomes a false page, and two false pages are enough to get it muted. |
| A monthly rehearsed restore that records elapsed time. | A backup you have never restored is not proven recovery (§98). The rehearsal is also the only honest source of your recovery time objective — the number you would otherwise be guessing in front of an executive. | Trust the provider's snapshot status page, which reports that a snapshot exists and cannot tell you whether it restores into a working system. | A recurring cost in engineering hours and in the scratch instance it restores into, producing no feature. It is the highest-value hour on this list and the first one cut when the roadmap is full. |
What would break this
Every design has a load, a failure or an organization size at which it stops being the right one.
Breaking points
- Write throughput past what one primary can absorb. Read replicas buy time for reads and do nothing for writes; past that you are partitioning data, which is an application redesign, not an infrastructure change.
- Work that does not fit in a request: report generation, bulk imports, third-party calls that take a minute. Doing it inline holds a web worker and eventually times out at the load balancer — this is the problem that creates
cs-worker-pipeline. - Traffic that varies by more than about 3x across the day. Fixed instance counts mean paying for the peak overnight and still being short at the morning spike; that is the autoscaling stage of
cs-saas-platform. - A second team and a second service. One deployable behind one load balancer is a good design for one team; the coordination cost of shared deploys is what eventually justifies separate services and, much later, a platform to run them on.
- A regional outage becoming unacceptable to the business. That is a different design and a different budget — see
break-regionfor what the honest version of that conversation looks like. - File uploads. The moment users upload anything, instance-local disk is wrong and object storage with direct uploads is right — a small change made much larger by doing it late.
Cost shape
Drivers and relative weights. Never a price.
The shape of a small production API: mostly fixed, mostly capacity you are holding rather than using.ILLUSTRATIVE
Managed database (primary + standby) fixed
driven by instance size × hours, plus storage and IOPS · Usually the largest single line, and it roughly doubles the moment you enable the standby. That is the price of the failover you enabled it for.
Application instances fixed
driven by instance-hours at the count you hold · Fixed because the count is fixed at this stage. Autoscaling is what turns this line from fixed into usage-shaped.
Load balancer fixed
driven by hourly charge plus processed capacity units
NAT gateways · the surprisefixed
driven by hours per zone plus per-GB processed · Two hourly charges for a component that does nothing visible, plus a per-gigabyte fee on every byte of outbound traffic. The single most frequently questioned line on a small cloud bill.
Backups and snapshots usage
driven by gigabytes retained × retention window, plus cross-region copy
Logs and metrics ingestion · the surpriseusage
driven by gigabytes ingested and days retained · Grows with traffic *and* with log verbosity, so a debug flag left on in production shows up on the invoice weeks later.
Egress to the internet usage
driven by gigabytes returned to mobile clients · Small for a JSON API and the dominant line the moment the API starts serving media.
Bars are relative weights, not currency. Real rates depend on provider, region, commitment and volume.
Lessons behind this design