Relational Databases and SQL
EE 547 - Unit 4
Shared Data Is How Instances Coordinate
Instances of one application share nothing but the network and the store. The store is what makes them one application.
Requests land on different instances
- An account created through instance 1 is looked up through instance 3
- A retry after a timeout lands on a different instance than the first attempt
- A background worker takes its jobs from what the web tier wrote
One seat map for twenty servers
- A seat sold through one server is unavailable through the other nineteen, at once
- That fact has exactly one place to live
A private copy per instance fails
- Copies diverge at the first concurrent write
- Deciding which copy is right is a coordination problem the network cannot settle: messages are lost, delayed, and reordered
Coordination happens at the store
- Every read sees what other instances wrote; every write is ordered against the other writes to the same record
- The store must keep the data through failures, give it a shape every instance agrees on, and order the writes to one record
Committed Writes Survive Process, Container, and Host
A persistent store must keep a write through the failure of whatever made it. A file write that returned is a weaker guarantee.
Process crash
- The application segfaults mid-operation, or
kill -9ends it without cleanup - Completed writes are present when the replacement process starts
Container restart
- A deployment replaces the container running the old code
- Data written by the old version is read by the new one, with no migration step
Host failure
- The server loses power with writes still buffered in memory
- After recovery, every committed write is intact and every uncommitted one is gone
Write-ahead log (WAL)
- A file written with
flush()is not on disk; the operating system buffers it - The database appends each change to a log and forces the log to disk (
fsync) before it acknowledges the commit - After a crash, recovery replays the log; the table pages catch up from it
Durability Has a Cost
Each level of durability is a mechanism, and each mechanism adds a wait before the write is acknowledged.
What each level waits for
- Process memory: nothing is written
- Local SSD: the log entry is on this host’s disk
- Replicated storage: a second host has the entry
- Cross-region replica: a second region has the entry
Six orders of magnitude end to end
- Process memory to cross-region replica: ~100 ns to ~100 ms
- A write that costs nanoseconds locally costs hundreds of milliseconds to survive a regional outage
Unstructured Storage Forces Full Scans
An application that keeps flight records as flat files, one JSON object per flight or a single CSV, reads and parses the entire dataset to answer any question about it.
File and object storage retrieve by name
# S3: retrieve a known object
s3.get_object(Bucket='flights', Key='2026/02/lax-jfk.json')
# Filesystem: read a known file
open('/data/flights/2026/02/lax-jfk.json')- The application must already know which object it wants
- There is no request for “the objects matching a condition”
“Which flights from LAX have seats?”
- Download every flight object
- Parse each one (JSON decode, CSV parse)
- Filter in application code
- 10 million records at 1 KB each: 10 GB transferred and parsed
The cost scales with the size of the dataset, however selective the question.
Concurrent Access Without Coordination Corrupts Data
Several processes writing the same data at the same time is the normal condition of any shared store.
Two users book the last seat on flight 547 at the same moment, through different application servers:
- App server A reads the seat count: 1 remaining
- App server B reads the seat count: 1 remaining
- App server A writes 1 - 1 = 0 remaining and confirms the booking
- App server B writes 1 - 1 = 0 remaining and confirms the booking
Two confirmed bookings for one seat.
Lost update
- The anomaly that occurs whenever a read-then-write pair is not atomic
- Both servers read the same value, compute the same result, and the second write silently replaces the first
- The same race as two threads incrementing one counter, now between two servers and one row
Not an application bug
- The code is correct for a single writer
- It fails because there are several writers and nothing coordinates them
Airline Booking Scenario
Airlines, airports, flights, aircraft, crews, passengers, and bookings.
Entities
- Airports - code, name, city, timezone
- Aircraft - registration, model, seat capacity, range
- Flights - number, origin, destination, departure, arrival, aircraft assignment
- Crew members - employee ID, name, certifications
- Passengers - name, contact, frequent flyer status
- Bookings - passenger, flight, seat, fare class, status
Relationships
- A flight departs from one airport and arrives at another
- A flight is operated by one aircraft
- A flight has several crew members; a crew member works several flights
- A booking connects one passenger to one flight
- An aircraft has a maintenance history
What the schema must handle
- Typed columns: timestamps, airport codes
- Constraints: a flight must reference existing airports
- Relationships: many-to-many crew assignments
- Concurrency: seat booking races
Every Row Has the Same Structure
A relational database stores data in tables: a fixed set of columns with declared names and types, and one row per record.
| flight_id | flight_number | origin | destination | departure_time | seats_remaining |
|---|---|---|---|---|---|
| 1 | AA 100 | LAX | JFK | 2026-02-12 08:00 | 23 |
| 2 | UA 512 | SFO | ORD | 2026-02-12 09:30 | 0 |
| 3 | DL 47 | ATL | LAX | 2026-02-12 11:15 | 84 |
What is fixed
- Every row has the same columns
- Every column has one declared type
- The store enforces the structure; a spreadsheet leaves it to convention
What is rejected
- A row missing a NOT NULL column
- A value of the wrong type for its column
- Any write that would leave a row outside the declared structure
Every Row Needs an Identity
A table with duplicate rows is ambiguous: which row does an update or a delete apply to? Without identity, operations on one record are undefined.
Primary keys provide identity
A primary key is a column, or a combination of columns, whose value identifies each row. The database enforces two rules:
- Uniqueness - no two rows share a primary key value
- NOT NULL - the primary key is never absent
CREATE TABLE airports (
airport_code CHAR(3) PRIMARY KEY,
name TEXT NOT NULL,
city TEXT NOT NULL,
timezone TEXT NOT NULL
);airport_codeidentifies each airport- A second row with
'LAX'is rejected - A row with no airport code is rejected
Foreign Keys Enforce Relationships Between Tables
Data about different entities lives in different tables. A flight has an origin airport and a destination airport. Rather than repeating the airport’s name, city, and timezone in every flight row, the flight table references the airports table.
CREATE TABLE flights (
flight_id SERIAL PRIMARY KEY,
flight_number TEXT NOT NULL,
origin CHAR(3) NOT NULL REFERENCES airports(airport_code),
destination CHAR(3) NOT NULL REFERENCES airports(airport_code),
departure_time TIMESTAMP WITH TIME ZONE NOT NULL,
aircraft_id INTEGER REFERENCES aircraft(aircraft_id),
seats_remaining INTEGER NOT NULL
);What REFERENCES enforces
originmust hold a value that exists inairports.airport_code- A flight with
origin = 'XYZ'is rejected if no airportXYZexists - This is referential integrity: every reference points to a real row
INSERT INTO flights (flight_number, origin, destination, ...)
VALUES ('AA 100', 'ZZZ', 'LAX', ...);
-- ERROR: insert or update on table "flights"
-- violates foreign key constraint
-- Key (origin)=(ZZZ) is not present
-- in table "airports"When the referenced row is deleted - shown for the nullable aircraft_id
- RESTRICT (default) unless the child has no meaning without the parent
- CASCADE when the child is part of the parent (an itinerary’s legs)
- SET NULL only where the reference was optional to begin with
Constraints Are Centralized Enforcement
Without a constraint in the schema, every application validates on its own.
Every writer validates on its own
- The web application checks
seats_remaining >= 0before confirming a booking - The batch import tool checks it before loading flight data
- The admin console checks it before a manual adjustment
- A mobile API is added six months later; its developer does not know the rule, and negative seat counts enter the table
One writer skips the check
- The data is now corrupt
- The source of the corruption is hard to trace, because every writer’s code looks correct on its own
One CHECK constraint on the column
- The fourth application’s bad write is rejected by the database
- The rule is stated once, in the schema, and holds for every client, present and future
NULL Propagation Affects Aggregation and Logic
Aggregation with NULLs
-- delay_minutes: [10, NULL, 30, NULL, 20]
SELECT COUNT(*) FROM flights; -- 5
SELECT COUNT(delay_minutes) FROM flights; -- 3
SELECT AVG(delay_minutes) FROM flights; -- 20.0COUNT(*)counts rows: 5 rows existCOUNT(delay_minutes)counts non-NULL values: 3AVG(delay_minutes)averages non-NULL values: (10 + 30 + 20) / 3 = 20.0, not (10 + 0 + 30 + 0 + 20) / 5 = 12
COUNT(*) and COUNT(column) return different numbers on the same data. Treating NULL as zero in an average silently changes the result.
Logical operations
FALSE AND NULLis FALSE: whatever the unknown value, the conjunction is falseTRUE OR NULLis TRUE for the same reasonTRUE AND NULLis NULL: the result depends on the unknown value
The Airline Schema
Airports, aircraft, flights, passengers, and bookings, connected by foreign keys.
References
| Column | References |
|---|---|
flights.origin |
airports.airport_code |
flights.destination |
airports.airport_code |
flights.aircraft_id |
aircraft.aircraft_id |
bookings.flight_id |
flights.flight_id |
bookings.passenger_id |
passengers.passenger_id |
What the schema enforces
- A flight references only existing airports and aircraft
- A booking references only an existing flight and passenger
- Seat counts never go negative
- Fare classes are one of F, J, W, Y
- No two bookings hold one seat on one flight
A Query Is Text the Engine Executes
The application never opens the database’s files. It sends SQL as text over a connection; the engine parses it, plans how to run it, runs it, and returns rows.
import psycopg2
conn = psycopg2.connect(host='db.example.internal',
dbname='airline', user='app')
cur = conn.cursor()
cur.execute("""
SELECT flight_number, seats_remaining
FROM flights
WHERE origin = %s AND seats_remaining > 0
""", ('LAX',))
rows = cur.fetchall() # list of tuples- The query is a string; the engine, not Python, interprets it
- The parameter
%sis sent separately from the text, so a value is never spliced into the query - The result arrives as rows over the same connection
- Parse, plan, execute is the same sequence for every statement, from
CREATE TABLEto a six-table join - The plan step is where the engine decides between a scan and an index
SQL Execution Order Differs from Written Order
A query is written SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY. The engine evaluates the clauses in a different order.
What follows from the order
- A column alias defined in SELECT cannot be used in WHERE; SELECT has not run yet
- HAVING can filter on an aggregate and WHERE cannot; WHERE runs before GROUP BY
- ORDER BY can use a SELECT alias; it runs after SELECT
Data Lives in Separate Tables
Each table holds one kind of entity. A relationship is a foreign key: an ID or a code that names a row in another table.
One booking row:
booking_id: 1001
passenger_id: 42
flight_id: 7
seat_number: 14A
fare_class: Y
status: confirmedPresent - the booking exists; which passenger, which flight, which seat
Absent - the passenger’s name, the flight number, origin, destination, departure time; each in another table
“Passenger 42’s itinerary with flight details and airport names”
- Needs bookings, flights, and airports
- Airports twice: once for the origin, once for the destination
JOIN Matches Rows Across Tables
A JOIN combines rows from two tables on a matching condition. For each row of the first table, the engine finds the rows of the second where the condition holds and produces one combined row per match.
SELECT b.booking_id, b.seat_number, f.flight_number, f.departure_time
FROM bookings b
JOIN flights f ON b.flight_id = f.flight_id
WHERE b.passenger_id = 42;FROM bookings b- start from the booking rowsJOIN flights f- for each booking, find the flightON b.flight_id = f.flight_id- the match condition: foreign key equals primary key- Result: one row per booking, with the flight’s columns attached
- Bookings 1001 and 1003 both match flight 7: two passengers on one flight, two result rows
- Flight 23 has no booking from passenger 42 and is absent from the result
- One-to-many: one flight, many bookings; the join produces one output row per booking, not per flight
INNER JOIN Returns Only Matching Rows
The default JOIN, also written INNER JOIN, includes a row in the result only if a match exists in both tables.
SELECT f.flight_number, a.model AS aircraft
FROM flights f
JOIN aircraft a ON f.aircraft_id = a.aircraft_id;flight_number | aircraft
--------------+-----------------
AA 100 | Boeing 737-800
UA 512 | Airbus A320
DL 47 | Boeing 767-300- 500 flights, 20 with no aircraft assigned (
aircraft_idis NULL) - The result has 480 rows; the 20 unassigned flights have no match in aircraft and are dropped
LEFT JOIN Keeps Rows With No Match
LEFT JOIN returns every row of the left table. Where a match exists on the right, its columns are filled; where none exists, they are NULL.
SELECT f.flight_number, a.model AS aircraft
FROM flights f
LEFT JOIN aircraft a ON f.aircraft_id = a.aircraft_id;flight_number | aircraft
--------------+-----------------
AA 100 | Boeing 737-800
UA 512 | Airbus A320
DL 47 | Boeing 767-300
SW 220 | NULL- All 500 flights appear, including the 20 with no aircraft
- The
aircraftcolumn is NULL for those 20 rows
Joins Put the Pieces Back Together
- The airport’s name is in one row and the flight’s details in one row; the join puts them back together at query time
- Reassembly costs CPU and I/O to match rows across tables
- Indexes on the join columns, the foreign keys and primary keys, keep that cost proportional to the size of the result, not of the tables
Aggregation Loses the Individual Rows
- 7 rows become 3 summary rows; the individual rows are gone from the result
- GROUP BY answers “how many” and “what average”, not “which flight”
- A question about individual rows in the context of their group needs something else
Window Functions Keep Every Row
GROUP BY collapses a group to one row. A window function computes over the rows of a group while every row stays in the result.
SELECT flight_number, origin, seats_remaining,
RANK() OVER (
PARTITION BY origin
ORDER BY seats_remaining DESC
) AS availability_rank
FROM flights
WHERE departure_time::date = '2026-02-12';flight_number | origin | seats | rank
--------------+--------+-------+-----
AA 100 | LAX | 84 | 1
UA 205 | LAX | 47 | 2
DL 300 | LAX | 23 | 3
UA 512 | ORD | 91 | 1
AA 340 | ORD | 55 | 2- Every row is present; the rank is computed within each origin partition
PARTITION BYis the grouping;ORDER BYinsideOVERis the order the function sees
Other window functions
- ROW_NUMBER() / RANK() / DENSE_RANK() - numbering and ranking
- SUM(…) OVER (…) - running totals
- LAG(col, n) / LEAD(col, n) - the value n rows before or after
ER Diagrams Show Entities, Attributes, and Relationships
Entities - things with an existence of their own that the system tracks
- Airport, Aircraft, Flight, Crew Member, Passenger
- Each becomes a table with a primary key
Attributes - properties of an entity
- Airport: code, name, city, timezone
- Aircraft: registration, model, seat capacity, range
- Each becomes a column with a type
Relationships - associations between entities
- A flight departs from an airport
- A flight is operated by an aircraft
- A passenger books a flight
- Relationships are named; the verb carries the direction and the meaning
Cardinality Says How Many on Each Side
- One-to-one - one aircraft, one registration certificate; rare, and often a sign the two belong in one table
- One-to-many - one airport, many departing flights; the most common relationship
- Many-to-many - a crew member works many flights and a flight carries many crew; neither side can hold a single reference to the other
Participation Decides Whether the Foreign Key Can Be NULL
Total participation - every instance takes part in the relationship
- Every flight has an origin airport; a flight without one makes no sense
- Implementation:
NOT NULLon the foreign key
origin CHAR(3) NOT NULL REFERENCES airports(airport_code)Partial participation - taking part is optional
- An aircraft may not be assigned to any flight yet: new, or in maintenance
- Implementation: the foreign key allows NULL
aircraft_id INTEGER REFERENCES aircraft(aircraft_id)
-- NULL means "not currently assigned"The diagram records the business rule; the DDL enforces it.
Subtypes Go in One Table or in Several
Some entities are subtypes of a more general one. Crew members are all employees with a name, an employee ID, and a hire date; pilots also have type ratings, and cabin crew have language certifications.
One table with a type column
CREATE TABLE crew (
crew_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
hire_date DATE NOT NULL,
crew_type TEXT NOT NULL CHECK (crew_type IN ('pilot', 'cabin')),
type_ratings TEXT[], -- array column*; NULL for cabin crew
flight_hours INTEGER, -- NULL for cabin crew
languages TEXT[], -- NULL for pilots
service_cert TEXT -- NULL for pilots
);- One table, no joins; every subtype’s columns are NULL in the other subtype’s rows
- Inapplicable columns accumulate as the subtypes diverge
Separate tables sharing the primary key
CREATE TABLE crew (
crew_id SERIAL PRIMARY KEY,
name TEXT NOT NULL, hire_date DATE NOT NULL
);
CREATE TABLE pilots (
crew_id INTEGER PRIMARY KEY REFERENCES crew(crew_id),
type_ratings TEXT[], flight_hours INTEGER
);
CREATE TABLE cabin_crew (
crew_id INTEGER PRIMARY KEY REFERENCES crew(crew_id),
languages TEXT[], service_cert TEXT
);- Each subtype holds only its own columns; shared plus specific attributes need a join
- The foreign key guarantees every pilot is a crew member
The Airline ER Diagram
Cardinalities
- Airport 1 - N Flight · Aircraft 1 - N Flight · Aircraft 1 - N Maintenance
- Crew M - N Flight
- Passenger 1 - N Booking · Flight 1 - N Booking
What the diagram already decides
- Every one-to-many puts the foreign key in the “many” table
- Crew-Flight needs the junction table
crew_assignments - Booking is a junction between Passenger and Flight that carries its own attributes: seat, fare class, status
- Maintenance is a weak entity, identified by aircraft plus date or sequence
What the Three Anomalies Look Like
Update anomaly: a partial update
UPDATE crew_flights
SET origin_name = 'Los Angeles International'
WHERE origin = 'LAX';
-- 1 of 2 rows updated before a timeout- Two rows, two names for one airport, and no constraint that says they must agree
Insert anomaly: an unrelated fact cannot enter
-- Record Denver International (DEN)
INSERT INTO crew_flights (origin, origin_name)
VALUES ('DEN', 'Denver International');
-- ERROR: crew_id, flight_id cannot be NULL- An airport exists only as part of a crew assignment
Delete anomaly: a fact leaves with an unrelated row
DELETE FROM crew_flights WHERE flight_id = 15;- Flight 15 was the only flight out of ATL; its name went with the assignments
A Functional Dependency Means One Value Determines Another
A functional dependency X → Y: if two rows agree on X, they must agree on Y. It is the formal way to say “Y is an attribute of X”.
In the airline schema
airport_code → name, city, timezone: two rows withLAXmust show the same nameflight_id → flight_number, origin, destination, departure_timecrew_id → crew_name, hire_date
Dependencies inside the wide table
| crew_id | crew_name | flight_id | flight_number | origin | origin_name |
|---|
crew_id → crew_name: a crew attributeflight_id → flight_number, origin: flight attributesorigin → origin_name: an airport attribute
Transitive dependency
flight_id → origin → origin_name- The airport’s name depends on the airport code, not on the flight; stored per flight, it repeats once per flight from that airport
Normalization Splits One Wide Table into Four
- 2NF takes
crew_nameout of the assignment rows andflight_numberout of them too; the pair(crew_id, flight_id)keeps onlyrole - 3NF takes
origin_nameout of the flight rows; the airport code stays as the reference - Each key ends up in two places: the table it identifies and the table that references it; every other column ends up in exactly one
A Crash Mid-Transaction Rolls Everything Back
- The UPDATE was applied in memory and logged; the INSERT never ran
- On recovery the engine finds a transaction with no COMMIT record in the log and undoes its changes;
seats_remainingis 50 again - Nothing the transaction did is visible to anyone, before or after the crash
The Engine Interleaves Concurrent Transactions
Several connections run transactions at the same time. The engine interleaves their statements, so another transaction’s write can land between one transaction’s read and its write.
- Neither transaction saw the other, which is what isolation promised; the anomaly is in the read-then-write gap between two statements, and isolation levels decide how much of it the engine closes
A Guarded UPDATE Prevents the Double Booking
Under READ COMMITTED, an UPDATE checks its WHERE clause against the row as committed at the moment the UPDATE runs, not as it looked at the earlier SELECT.
BEGIN;
SELECT seats_remaining FROM flights
WHERE flight_id = 7; -- returns 1
-- another transaction commits here,
-- setting seats_remaining to 0
UPDATE flights
SET seats_remaining = seats_remaining - 1
WHERE flight_id = 7
AND seats_remaining > 0;
-- rows affected: 0
-- application sees 0 rows: abort
ROLLBACK;Optimistic concurrency
- Read, compute, write with a condition that restates what the read assumed
- If the condition fails, the assumption was stale; retry or give up
- No lock is held between the read and the write
Writers to the Same Row Take Turns
Isolation is enforced with locks: markers on rows that decide which transactions must wait.
What takes a lock
- UPDATE, DELETE, and SELECT FOR UPDATE lock the rows they touch
- A plain SELECT takes no row lock; it reads the last committed version of the row and never waits for a writer*
Granularity
- Row-level - one row; the rest of the table is untouched
- Table-level - the whole table, taken by schema changes such as ALTER TABLE
Duration
- Held until COMMIT or ROLLBACK
- A long transaction holds its locks long, and everyone else waits
Contention
- A updates flight 7, B updates flight 15: no contention, both proceed
- A updates flight 7, B updates flight 7: B waits until A commits, then proceeds
The wait is the cost of isolation. Throughput depends on how often transactions touch the same rows and how long they hold them.
Deadlock: Two Transactions Wait for Each Other Forever
The sequence
- A locks flight 7
- B locks flight 15
- A asks for flight 15 and waits; B holds it
- B asks for flight 7 and waits; A holds it
Neither can proceed; without help, both wait forever.
What the engine does
- Detects the cycle in the lock-wait graph
- Aborts one transaction, the victim, and rolls its changes back
- The other proceeds
ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock
on transaction 5678; blocked by process 5678.What the application does
- Treats the error as expected under row-level locking and retries the transaction
- Locking rows in a consistent order (always the lower flight_id first) prevents most deadlocks
Without an Index, Every Query Scans Every Row
SELECT * FROM flights WHERE origin = 'LAX';With no index on origin, the engine has no way to reach the LAX rows except by examining every row.
- Sequential scan - the plan when no index applies; every page of the table is read once
- The cost follows the table size, not the number of matches: ten million rows take the same time whether 3 rows match or 3 million
- The scan itself is not slow per row; it is the number of rows that makes it slow
B-Tree Indexes Reach Any Row in a Few Page Reads
An index is a separate structure kept alongside the table. The usual kind is a B-tree: a balanced, sorted tree whose leaves point at rows.
- Sorted - serves equality (
= 'LAX') and ranges (BETWEEN 'LAX' AND 'ORD'), and returns rows in key order - Balanced and wide - every leaf is at the same depth, and a page holds hundreds of keys, so ten million rows are three or four levels deep: three or four page reads to reach any key, about log₂ n ≈ 23 comparisons in total
- Maintained automatically - every INSERT, UPDATE, and DELETE updates the index as well as the table
The Planner Skips the Index When Most Rows Match
- An index scan costs one jump into the table per matching row; for 3% of ten million rows that is far less than reading the table
- For 95% of the rows it would touch nearly every page in random order, so a single sequential pass is cheaper, and the planner takes it
- Selectivity decides:
CREATE INDEXmakes the option available, and the planner uses its statistics on the column’s values to decide when the option pays
Relational Guarantees Assume One Machine
A join, a transaction, and a foreign-key check each read rows from several tables in one step. The relational engine can promise what it promises because every row it needs is on the machine it runs on.
What depends on local data
- Joins - matching rows across tables is a local read of both
- Transactions - a commit covers every table at once because one log records them all
- Foreign keys - a write to
bookingschecks a row inflightsbefore it is accepted
So one database is one node
- All tables on one disk, in one memory, behind one process
- Growth means a bigger node, more connections into it, copies of it for reads, or splitting it at a cost
Every Connection Is a Process on the Server
A database connection is not a lightweight handle. PostgreSQL starts a backend process* for each one, with its own memory.
What one connection costs
- Several megabytes of server memory for the process
- Tens of milliseconds to open: TCP handshake, encryption setup, authentication
- Opening one costs more than running a short query on it
How connections add up
- 20 application containers × 20 connections each = 400 from one service
- Three services on the same database: 1,200
- Plus background workers, migration scripts, monitoring agents
The ceiling is set by the instance
- Memory bounds the backends:
max_connectionsis in the hundreds on a small instance, the low thousands on a large one - The limit is reached without any one service doing anything unreasonable
A Pooler Shares Few Connections Among Many Clients
An external pooler is a proxy between the applications and the database. Many application connections map onto a small number of database connections.
- 1,000 application connections become 50 backends
- A client holds a real backend only for a query or a transaction, then hands it back
- The database sees a small, steady number of processes whatever the number of clients
Where it is needed
- Many instances, or serverless functions, in front of one database
- On AWS the managed form is RDS Proxy in front of an RDS instance; PgBouncer is the common self-hosted one
Bigger Hardware Has a Ceiling
Vertical scaling - the same database on a larger instance
- More CPU cores: more queries at once
- More memory: more of the working set cached, fewer disk reads
- Faster storage: lower I/O latency
A large managed instance, db.r6g.16xlarge on RDS, has 64 vCPUs and 512 GB of memory and serves thousands of queries per second on millions of rows.
Two limits
- Above the largest instance there is no bigger node to buy
- Price grows with vCPU; throughput grows less, as lock contention, I/O bandwidth, and connection overhead take a larger share at each step
Read Replicas Scale Reads but Not Writes
Read replicas add capacity while keeping every relational guarantee.
- One primary takes all writes
- One or more replicas receive a copy of every write, asynchronously
- Read traffic is spread over the replicas
- Joins, foreign keys, and transactions all still work: each replica holds a complete copy
What replicas scale
- Read throughput, roughly in proportion to their number
- Isolation of heavy analytical queries from the primary
- Read latency by geography, with a replica per region
What they do not scale
- Write throughput: one primary takes every write
- Storage: every replica holds the whole dataset
Replication lag - a replica is milliseconds to seconds behind the primary. A write followed at once by a read from a replica may not see that write; applications that read their own writes read them from the primary.
Sharding Splits the Data Across Machines
Sharding partitions the rows across machines, each holding one slice: flights from airports A to M on one node, N to Z on another. The single-node ceiling is gone, and so is the assumption every guarantee rested on.
What breaks
- Joins across shards - the flight is on one node and the airport on another: a network round trip per matched pair
- Transactions across shards - a commit spanning two nodes needs a coordination protocol: slower, more ways to fail
- Foreign keys across shards - a node cannot check a reference it does not hold without asking the other node
- Global uniqueness - a
booking_idgenerated on one shard must not collide with one from another
All four are one fact: the guarantees assumed local data, and sharding removes it.