Relational DB: what it does in a system design

Home › Reference › Components › Relational DB

Relational DB

Primary + optional read replicas with bounded connection pools.

Settings

Instance tier: Machine size of each database node: more CPU cores, a higher query throughput ceiling and more allowed connections. Bigger handles more load before queries queue, and costs more.
Read replicas: Number of extra copies that serve reads. With replicas, reads rotate across them and the primary only takes writes, so read capacity grows, but replicas lag behind and can return stale data.
Max connections / node: How many connections one node accepts at once. Once all are in use, new callers are refused or wait. Raising it avoids refusals but lets more queries pile up inside the node.
Preloaded keys: How many rows already exist at the start of the run, so early reads find data. If left empty, the load profile's dataset size is used.
Read routing: Where reads go when there are replicas. Replicas only is fastest but can return stale data. Primary after write sends reads of a recently written row to the primary, so writers see their own changes.
Transactions: Makes each write a read-then-write on the same row. Plain takes no lock, so overlapping updates can be lost. Locking queues them safely. Optimistic checks at commit and retries on conflict.
Time between read and write: Time a transaction spends between reading the row and writing it back (the app doing work). Longer widens the window for conflicts: more lost updates, lock waits or retries. Shapes: constant, lognormal, exponential, uniform.
Attempts per transaction: For optimistic transactions, how many times one is tried before giving up and failing. Each retry starts over from the read. Higher succeeds more often under contention but adds latency.
Indexes: Number of indexed columns. A search on an indexed column is two quick lookups, while one on an unindexed column scans the whole table. Each index also makes every write a bit slower.
Searches start at key: Reads of keys at or above this number are treated as searches instead of lookups by id; each block of 1000 keys is one column. Leave empty for no searches.
Rows in the table: How many rows an unindexed search has to scan. More rows means proportionally slower full-table scans; indexed searches and lookups by id are not affected.
Scan time per row (µs): How long a full-table scan spends on each row, in microseconds. Total scan time is this times the row count. Higher makes unindexed searches slower and uses more CPU.
Replication: When a write is acknowledged. Async answers right away and ships to replicas later, so a failover can lose recent writes. Sync waits until one replica has it: safer, but every write is slower.
Failover: What happens if the primary crashes. None means writes fail until it returns. Automatic promotes a replica after the failover time (needs at least one replica), which then stops serving reads.
Failover time (ms): How long after a primary crash a replica is promoted. Writes keep failing during this window, so shorter means less downtime. Only used with automatic failover.
Commit mode: Whether a write waits for the disk flush before it is acknowledged. Sync waits (safe, slightly slower). Async answers first, so a crash before the flush loses it. Async skips the sync replication wait.
Async loss probability (0-1): With async commit, the chance that an acknowledged write is silently dropped and never stored. Higher means more lost data; 0 means writes are only lost if the primary crashes before flushing.
Read latency: Time a read takes when the node is idle. It grows as the node gets busy. Pick a shape: constant, lognormal, exponential or uniform. Higher slows every read.
Write latency: Time a write takes when the node is idle; it grows as the node gets busy. Pick a shape: constant, lognormal, exponential or uniform. Higher slows every write.
Replication lag: How long a write takes to reach each replica after the primary commits it. Longer means replica reads stay stale for longer. Shapes: constant, lognormal, exponential, uniform. Empty means no lag.
Sync standby confirm time: With sync replication, how long the standby replica takes to confirm it has the write. This is added to every write's latency. Shapes: constant, lognormal, exponential, uniform.
Async commit: time until on disk: With async commit, how long after the acknowledgement the write actually reaches disk. A primary crash inside this window loses the write, so longer means more risk. Shapes: constant, lognormal, exponential, uniform.
Crashes: Scheduled outages of one database node. A crashed primary fails writes until it returns or a failover happens; reads route around a crashed replica.