Computer systems foundations

Sections: 5
Pages: 143
Reading time: ~7 h
Take first: Nothing

What you will be able to do

Explain what the CPU, caches, memory and OS do when your program runs, and estimate the cost of each with real latency numbers.
Reason about threads, locks, async I/O and event loops, and spot race conditions and deadlocks in code review.
Trace a request end to end through DNS, TCP, TLS and HTTP/1.1, HTTP/2 and HTTP/3, and explain where latency comes from.
Describe how B-tree and LSM-tree storage engines, WAL, MVCC and query planners work, and read an EXPLAIN plan.
Choose data structures and serialization formats based on real costs rather than big-O alone.
Explain hashing, encryption, signatures, sessions, JWTs and OAuth well enough to implement auth safely.
Profile a slow program and read a flame graph.

Every system design trade-off and every production bug eventually comes down to how a machine runs code, moves bytes and stores data. A cache helps because DRAM is about 1,000× faster than an SSD read. A service's p99 latency spikes because its garbage collector paused. A deploy breaks because a certificate expired, a database slows down because a query planner picked a sequential scan, and a counter goes negative because it was 32 bits wide. None of these are exotic. They are the normal working life of a backend engineer, and they all live in the layers these pages cover.

These pages build a working mental model of a single machine and a single network hop: CPUs and memory, the operating system, processes and concurrency, TCP/IP through TLS and HTTP/3, the storage engines inside databases, and the cryptography and tooling you rely on every day. It is deliberately practical. You'll learn the latency numbers you should know by heart, the tools you'll use at work (strace, perf, EXPLAIN ANALYZE, tcpdump, heap profilers, flame graphs) and the failure modes you'll meet in production.

Why it matters for the rest of the degree

The other topics build on this one. System design starts with back-of-envelope estimates that use the latency and bandwidth numbers from the memory hierarchy. Replication and sharding only make sense once you know what a single node's storage engine and transactions can and can't do. Infrastructure assumes you understand processes, file descriptors and memory limits. Inference engineering is the memory-bound vs compute-bound reasoning from these pages, applied to GPUs. If you skip the foundations, the rest of the degree turns into memorizing patterns; if you have them, the patterns follow from first principles.

Who it's for

These pages are written for a junior software engineer working toward SDE2: comfortable writing code in at least one language, probably shipping features in a web or backend codebase, but hazy on what happens below the framework. It's also a good refresher for engineers who came through a bootcamp or a non-CS degree, or who studied this material years ago and forgot the numbers.

You don't need any math beyond arithmetic and powers of two. Code examples are mostly in Go, Python, TypeScript, C and SQL, and they're short enough to follow if you know any one of those.

How to study it

Pace. There are 143 lessons, about 7 hours of reading. Each one is short; the experiments below are what take the time. Don't rush the first section: it sets up numbers and vocabulary used everywhere else.

Run things. The single most effective habit is to reproduce the experiments yourself. Paste the C function into Compiler Explorer and read the assembly. Time the Python and Go loops on your own laptop. Run strace on a hello world in a Linux container. Use curl -v and dig on a real domain, run EXPLAIN ANALYZE on a local Postgres, and make a flame graph of one of your own services. The numbers you measure yourself are the ones you'll remember in a design review.

Projects that tie it together. Pick one or two:

Build a tiny HTTP/1.1 server on raw sockets, then make it handle 10,000 idle connections with an event loop. You'll touch processes, syscalls, async I/O, TCP and HTTP.
Build a key-value store with a write-ahead log and fsync, then add an LSM-style memtable and SSTables. You'll touch files, durability, data structures and storage engines.
Profile a real service at work (or an open-source one), find its top three hot spots with a flame graph, and write up what you'd change.

Further learning. Every lesson ends with videos and articles. Watch the videos when a concept doesn't click from text; they're chosen because they explain something better than prose can. Treat the books below as references to dip into, not things to read cover to cover before starting.

Roadmap

The numbered list is a shorter path. The sections below are every lesson in the topic.

Machines and programs

How a single program actually runs on hardware, and how data is represented.

How code runs: compilers, interpreters and JITs, the CPU's fetch-decode-execute loop, branch prediction and system calls. Why the same loop is about 50× slower in Python than in Go.
The memory hierarchy: from L1 cache to the network, the latency table every engineer should know, cache lines, locality, false sharing and NUMA.
Memory management and garbage collection: stack and heap, ownership and reference counting, how tracing collectors work, why GC pauses show up in p99, and how to find leaks.
Numbers, text and encodings: integer overflow, floating point, why money is never a float, Unicode and UTF-8, endianness, timestamps and Base64.

Operating systems and concurrency

What the OS gives every program, and how to do many things at once safely.

Processes, threads and the kernel: isolation, scheduling, virtual memory and system calls.
Concurrency and synchronization: race conditions, locks, atomics, deadlocks and the patterns that keep shared state safe.
Blocking, non-blocking and async I/O: event loops, epoll, async/await and green threads, or how one process serves 10,000 connections.
Files, disks and durability: the page cache, fsync and SSD internals, and what "written to disk" really means.

Networking

How bytes get from one machine to another, and where the latency comes from.

Networking: IP, TCP and UDP: packets, ports, handshakes and congestion control.
DNS: how names become addresses, and why DNS is both a load balancer and a frequent cause of outages.
HTTP/1.1, HTTP/2 and HTTP/3: semantics, caching headers and how each version fixes the last one's latency problems.
TLS and certificates: confidentiality, integrity and identity, and what breaks when certificates expire.
Life of a request: the capstone, from typing a URL to seeing a page, with a latency budget for every step.

Data structures and storage engines

How data is organized in memory and on disk, and how databases use that to answer queries.

Data structures that matter in practice: arrays, hash maps, heaps, trees and tries, and their real costs beyond big-O.
Probabilistic data structures: Bloom filters, HyperLogLog and count-min sketch.
Storage engines I: pages, B-trees and the WAL: how Postgres and MySQL store rows and find them in a few page reads.
Storage engines II: LSM trees: memtables, SSTables and compaction in RocksDB and Cassandra.
Transactions, MVCC and isolation levels: ACID on one node, and the anomalies each isolation level allows.
How a SQL query executes: parser, planner, indexes, join algorithms and reading EXPLAIN ANALYZE.
Serialization, schemas and compression: JSON, Protobuf, Avro and friends, and evolving schemas safely.

Engineer's toolkit

The security and tooling knowledge you use every week.

Cryptography for engineers: hashes, MACs, symmetric and public-key crypto, signatures and password storage.
Authentication and authorization: sessions, cookies, JWTs, OAuth 2.0, OIDC, passkeys and permission models.
Git internals and workflow: Git as a content-addressed DAG, so rebases and recoveries stop being scary.
Performance and profiling: benchmarks, CPU and memory profilers, flame graphs and Amdahl's law.

What you'll be able to do

After reading these pages you should be able to:

Explain what the CPU, caches, memory and OS do when your program runs, and estimate the cost of each with real latency numbers.
Reason about threads, locks, async I/O and event loops, and spot race conditions and deadlocks in code review.
Trace a request end to end through DNS, TCP, TLS and HTTP, and explain where its latency comes from.
Describe how B-tree and LSM-tree storage engines, the WAL, MVCC and query planners work, and read an EXPLAIN plan.
Choose data structures and serialization formats based on real costs rather than big-O alone.
Explain hashing, encryption, signatures, sessions, JWTs and OAuth well enough to implement auth safely.
Profile a slow program and read a flame graph.

Course resources

Crash Course Computer Science is a fast, friendly tour from transistors to operating systems and networks. It's ideal if you want the big picture before diving into these pages.

Early Computing: Crash Course Computer Science #1CrashCourse

Herb Sutter's talk on machine architecture is an excellent bridge between this topic's first two sections. It explains why the hardware you program is nothing like the simple machine your language pretends it is.

Herb Sutter @ NWCPP: Machine Architecture: Things Your Programming Language NeveKevin Frei

Crash Course Computer Science (full playlist)youtube.com Computer Systems: A Programmer's PerspectiveCS:APP3e, Bryant and O'Hallaroncsapp.cs.cmu.edu Operating Systems: Three Easy Piecespages.cs.wisc.edu High Performance Browser NetworkingHigh Performance Browser Networking (O'Reilly)High Performance Browser Networking

Designing Data-Intensive ApplicationsDesigning Data-Intensive Applications (DDIA) — an O’Reilly book by Martin Kleppmann (The Wild Boar Book)dataintensive.net CMU 15-445/645: Database SystemsThis is Carnegie Mellon UniversityCMU 15-445/645

Nand2TetrisHome | nand2tetrisnand2tetris Teach Yourself Computer ScienceAll the resources you need to give yourself a world class computer science educationteachyourselfcs.com

Machines and programs

How code runs

From source file to running instructions. Covers the CPU's loop, compilers, interpreters and JITs, branch prediction and the system call boundary.

The CPU loop

What a core does billions of times a second, why pipelining and out-of-order execution exist, and what instructions per cycle (IPC) tells you about your code.

Compiled, interpreted and JIT

The three ways a language turns source into machine work, who pays the cost and when, and why the same summing loop runs about 57 times faster in Go than in CPython.

Branch prediction

Why a CPU guesses which way every if and loop goes, what a wrong guess costs, and why sorting an array can make an unrelated loop several times faster.

System calls

How a program asks the kernel to read files, open sockets or allocate memory, what each request costs, and how strace shows every call a process makes.

The memory hierarchy

From registers to SSDs to the network, the latency numbers that dominate performance, and why the tier your data sits in matters more than the code you write.

Reasoning with latency and bandwidth

Turn the latency table into quick cost estimates, separate latency from bandwidth, and use Little's law to find how many requests must be in flight to use a link or memory fully.

Cache lines and locality

Why caches move 64-byte blocks, how sequential access and hardware prefetching make arrays fast, and why walking a 2D array in the wrong order is six times slower.

Array of structs vs struct of arrays

How the layout of records in memory decides how many useful bytes each cache line carries, why struct of arrays is faster for scans, and when the opposite layout wins.

False sharing and NUMA

Why two threads writing to different variables in the same cache line slow each other down, and why memory on the far socket of a big server costs more to read.

Stack, heap and allocation

Where values live in a running program, what escape analysis decides for you, and why allocating in a hot loop slows a program even when allocation itself is cheap.

Memory management and garbage collection

How languages decide where values live and when memory is given back, the trade-offs of manual, ownership, reference-counted and tracing approaches, and how to find leaks and size heaps safely.

Four ways to free memory

Manual free, ownership, reference counting and tracing garbage collection, what each one costs, and which languages use which.

How tracing garbage collectors work

Mark-sweep, compaction and copying, the generational idea that most objects die young, and how concurrent marking with write barriers keeps programs running while the collector works.

GC pauses and tuning

The throughput, latency and memory trade-off every collector makes, how a 100 ms pause every five seconds becomes a p99 problem, and the knobs that change it in Go and the JVM.

Memory leaks and OOM kills

What a leak looks like in a garbage-collected language, how to find one by diffing heap profiles, why process memory is bigger than the heap, and how a container gets OOM-killed.

Binary, integers and overflow

How integers are stored as bits, why signed counters wrap around, and why byte order matters when you read raw binary.

Numbers, text and encodings

How integers, floats, text, timestamps and Base64 are really stored as bytes, and the production bugs each one causes. Start here for the data-representation map.

Floating point and money

Why 0.1 + 0.2 is not 0.3, how floats lose precision, and why money must be stored as exact integers or decimals.

Unicode and UTF-8

How text becomes bytes. Code points, UTF-8 and UTF-16, why "length" has four answers, and how mojibake and normalization bugs happen.

Base64 and URL encoding

How arbitrary bytes travel through text-only channels with Base64, and how URL encoding keeps query strings from breaking. Covers the injection bug that string concatenation causes.

Timestamps and time zones

How to store and send times so they stay correct across time zones and daylight saving, how leap seconds break things, and why durations need a monotonic clock.

Operating systems and concurrency

File descriptors

The small integer your program holds for each open file, socket or pipe, and why "too many open files" is either a low limit or a leak.

Processes, threads and the kernel

What the OS gives every program, and how to see it on a live Linux box. Start here for the process model.

Process lifecycle

How a process is born with fork, takes on a new program with exec, and exits with a code. Why zombies build up when a parent never calls wait.

Virtual memory

Why every process sees its own private memory, how the kernel maps it to RAM in 4 KiB pages, why ps shows two memory numbers, and what triggers the OOM killer.

CPU scheduling

How the kernel decides which thread runs on which core, why extra threads cost time, and why a container with low average CPU can still stall.

Signals and graceful shutdown

How the kernel tells a process to stop, why SIGTERM can be caught and SIGKILL cannot, and how a service drains work before it exits. Covers the PID 1 trap in containers.

Race conditions

A race condition happens when the result depends on the order in which threads run. How a read-modify-write loses an update, and why check-then-act bugs are so common.

Concurrency

Why shared state breaks when two threads touch it at once, and the tools that fix it, from locks and semaphores to atomics, message passing and database constraints.

Mutexes, semaphores and condition variables

The three basic blocking primitives. A mutex gives exclusive access, a semaphore counts available resources, and a condition variable waits for a state change.

Atomics and memory ordering

Atomic operations change one word in a single indivisible step, compare-and-swap builds lock-free updates, and memory ordering decides what other threads may see.

Deadlock and livelock

Deadlock is threads waiting on each other forever. The four Coffman conditions that must all hold, the fixes that break one of them, and how livelock differs.

Shared state without locks

Designs that avoid shared mutable state, such as message passing, database constraints and fencing tokens, and why they hold up better across machines.

Blocking I/O and epoll

How a thread blocks on a socket, why select and poll slow down at scale, and how epoll and kqueue report only the sockets that are ready.

Async I/O

Why waiting on the network should not hold a thread, how event loops and async functions handle thousands of connections, and when goroutines or thread pools fit better.

Event loops

An event loop runs a queue of ready callbacks and waits on the kernel for more. Why one slow callback stalls everything, and how to keep the loop responsive.

Async and await state machines

What async and await compile down to. Each await is a suspension point, the function becomes a state machine, and function color is why async spreads through a codebase.

Goroutines and green threads

Lightweight threads scheduled by the runtime instead of the OS. How goroutines use a netpoller, why M:N scheduling scales, and the trade-off against async/await.

Backpressure and bounded queues

Backpressure lets a slow consumer push back on a fast producer. Bounded queues make the limit explicit, and each overflow policy trades latency against lost work.

File systems and journaling

How a file system maps names to inodes, why hard links share one inode, and how a journal keeps metadata consistent after a crash.

Page cache and fsync

Why a successful write only reaches RAM, how fsync and fdatasync force data to disk, when O_DIRECT helps, and why a failed fsync cannot be retried.

Files and storage

How file systems map names to inodes, how journaling and the page cache affect durability, and how the storage devices underneath behave, from SATA disks and SSDs to cloud volumes.

Durable file writes

The atomic replace pattern for updating a file so a crash leaves either the old or the new version, plus how torn writes are detected with checksums.

Storage devices and SSDs

How HDDs, SATA SSDs and NVMe drives differ in latency and random I/O, why flash must erase before writing, and how write amplification and endurance limit SSDs.

Cloud storage and RAID

How RAID trades capacity for redundancy, how erasure coding does it at scale, and what cloud block volumes, instance stores and object storage each promise.

Networking

Packets, layers and addresses

How data is wrapped in layered headers, what the MTU and MSS limit, and how IPv4 addresses, CIDR blocks, private ranges and NAT decide what can reach what.

Networking: IP, TCP and UDP

Packets, addresses, ports, and the handshakes and congestion control that decide how fast your bytes arrive. Explains why a new connection is slow and how to check it on the wire.

TCP handshake and reliability

How TCP sets up a connection with a three-way handshake, numbers every byte, recovers lost packets, and closes cleanly, along with the costs each step adds.

Congestion control and BDP

Why a new TCP connection starts slow, how slow start works with a 100 KB example, how CUBIC and BBR differ, and how the bandwidth-delay product tells you how much data must be in flight.

TCP latency traps and UDP

Why TCP's ordered stream causes head-of-line blocking, how Nagle's algorithm and delayed ACKs combine into a 40 ms bug, the fix with TCP_NODELAY, and when UDP is the better transport.

Network tools

How to check the claims in the networking pages with ping, mtr, ss and tcpdump, including how to read a handshake in a capture and what each timestamp means.

DNS resolution walk

Trace a lookup from the stub resolver through recursive resolvers, root, TLD and authoritative servers, and see where each cache sits along the way.

DNS

How names become addresses, which record types matter, how caching and TTLs shape change speed, and why DNS failures look like outages of everything else.

TTLs and caching

How TTLs set the speed of DNS changes, how to lower them ahead of a migration, and why some resolvers ignore small values.

DNS traffic steering and anycast

How DNS answers can route users by weight, location, latency or health, and how anycast sends the same IP address to the nearest of many servers.

DNS outages and ndots

Why DNS failures take down unrelated services at once, what the 2016 Dyn and 2021 Facebook outages teach, and how the Kubernetes ndots setting multiplies lookups.

DNS security

How DNS answers can be forged, what DNSSEC does and does not protect, and how DNS over HTTPS and TLS hide queries from networks in between.

Requests, methods and status codes

What an HTTP request and response look like on the wire, which methods are safe and idempotent, and which status codes tell clients and proxies what to do next.

HTTP/1.1, HTTP/2 and HTTP/3

The protocol of the web. Separates HTTP's meaning (methods, status codes, caching) from its wire format, and shows how each version fixes the latency problem of the one before.

HTTP/1.1 and HTTP/2 multiplexing

Why HTTP/1.1 connections handle one request at a time, how browsers and developers worked around it, and how HTTP/2 multiplexes streams over one connection while still stalling on TCP loss.

HTTP/3 and QUIC

How HTTP/3 runs on QUIC over UDP to recover lost packets per stream, merge transport and TLS setup into one round trip, and keep connections alive when the phone changes networks.

HTTP caching

How Cache-Control, ETag and Vary let browsers and CDNs skip work, with a standard pattern of fingerprinted assets for a year and revalidated HTML.

Cookies, CORS and streaming

How cookies carry state in the browser, why CORS preflights cost a round trip, how compression and chunked responses shape delivery, and when to pick server-sent events, WebSockets or gRPC.

What TLS guarantees

The three things TLS does for every HTTPS connection (confidentiality, integrity and authentication), where it sits in the stack, and what it does not hide.

TLS and certificates

How HTTPS keeps data private and proves the server's identity, how the TLS 1.3 handshake works, and why expired certificates keep causing outages. Start here for TLS.

The TLS 1.3 handshake

How client and server agree on secret keys in one round trip, why the keys are thrown away afterwards, and what 0-RTT resumption costs in replay risk.

Certificates and chains of trust

What an X.509 certificate says, how a leaf certificate links to a trusted root through intermediates, why a missing intermediate breaks some clients, and how Let's Encrypt and Certificate Transparency fit in.

Certificate expiry and renewal

Why certificates expire on purpose, why expired ones take sites down, and the playbook for automated renewal, independent monitoring and a full inventory. Includes a check you can run in a minute.

Terminating TLS and mTLS

Where an encrypted connection ends and plaintext begins, the three common placements, and how mutual TLS with short-lived workload identities protects traffic between services.

Life of a request

Every step between typing a URL and seeing a page, with a latency budget for each hop, why the second request is faster, and a method for finding where a slow request loses its time.

Latency budget and round trips

How to put numbers on a cold request from Mumbai to Virginia, why round trips dominate for distant users, and how a CDN cuts the time to first byte from about 890 ms to about 62 ms on a cache hit.

Connection reuse

Why the second request to the same server is several times faster than the first, where connection reuse appears across browsers, CDNs, services and databases, and how to configure a Go HTTP client to pool connections correctly.

Measuring a request

How to read a browser waterfall, send Server-Timing headers, follow a request with distributed traces, and break down the 60 ms of server time for a product page.

Debugging a slow endpoint

A hop-by-hop method for a p99 regression. Start with whether the median moved, pull traces of slow requests, and test one hypothesis per hop with the matching tool.

Data structures and storage engines

Arrays and memory layout

Why contiguous arrays are fast on real hardware, how cache lines and prefetching reward sequential access, and how a fixed-size ring buffer handles streams with bounded memory.

Hash maps

How hash maps map keys to buckets, how load factor and collisions set their speed, and why adversarial keys can turn O(1) lookups into O(n).

Data structures in practice

Choosing data structures by memory layout, access pattern and the operations that matter, with the common ones for arrays, hash maps, heaps, trees, caches and graphs.

Heaps and top-k

How a binary heap keeps the smallest or largest item at the top, and how a heap of size k finds the top k in one pass without sorting everything.

Sorted trees, skip lists and tries

The structures that keep keys in order for range queries, the red-black and skip list options for in-memory sorting, and tries for prefix search.

LRU caches

How an LRU cache evicts the least recently used item in O(1) by pairing a hash map with a doubly linked list, and why a plain array or sorted list is too slow.

Graphs and topological sort

How to represent a graph with adjacency lists, and how Kahn's algorithm orders tasks with dependencies, and detects a cycle when no valid order exists.

Bloom filters

A compact set-membership test that never misses an item it has seen, but sometimes says yes to one it has not. How to size it for a target error rate.

Probabilistic data structures

Compact structures that answer membership, distinct-count, frequency, sampling and similarity questions with a small, bounded error. Start here for the family and how to choose one.

HyperLogLog

How to count distinct items in a fixed 12 KB with about 0.8% error, using the longest run of leading zeros in hashed values. Why these sketches merge cleanly.

Count-min sketch

A fixed-size table that estimates how often each item appears, never underestimates, and finds trending items. Covers the error bound and how to size it.

Reservoir sampling

How to pick a fair random sample of k items from a stream when you do not know its length, in one pass and k slots of memory. Includes why every item has the same chance.

MinHash and LSH

How to estimate how similar two sets are from tiny signatures, and how locality-sensitive hashing finds near-duplicate pairs without comparing every pair.

Pages and row layout

A database page is a fixed-size block with a header, slots and rows packed from both ends. How that layout drives row IDs, updates and wasted space.

Storage engines: pages, B-trees and the WAL

How a relational database stores rows in fixed-size pages, finds them in a B+tree, caches them in a buffer pool, and survives crashes with a write-ahead log.

B+tree lookups

How a B+tree finds a key in a few page reads, why its fanout is large, how page splits happen, and why random keys hurt inserts.

Buffer pool

The buffer pool caches database pages in RAM. How eviction chooses which page to drop, why sequential scans can wipe out the cache, and how midpoint insertion protects hot pages.

Write-ahead log

The write-ahead log records every change before the page is written. How commits become durable, how recovery replays the log, and what group commit and checkpoints do.

Composite and covering indexes

How column order in a multi-column index decides which queries it serves, and how including extra columns lets a query skip the table entirely.

Storage engines II: LSM trees

Write-optimized storage built from memtables, sorted immutable files and background compaction. Covers how it reads, deletes and pays for its writes, and how it compares with a B-tree.

Writes, memtables and SSTables

How an LSM tree turns every write into a log append and a memtable insert, flushes sorted immutable files, and never overwrites data on disk.

Reads, deletes and Bloom filters

How an LSM tree checks several places to find the newest value, how Bloom filters keep that close to one disk read, why range scans are the weak spot, and how tombstones make deletes hard, including Cassandra's zombie rows.

Compaction strategies

Why LSM trees must merge their files, how leveled compaction keeps few sorted runs at high write cost, and how size-tiered compaction keeps writes cheap at the price of more files and more disk space.

Write amplification

The read, write and space costs every storage engine trades off, a worked example of how leveled compaction multiplies one application write into about 43 device writes, and what that does to SSD endurance.

Transaction anomalies

The concurrency bugs a database can allow when transactions overlap, shown as timelines of two transactions with the SQL that causes each one: dirty read, non-repeatable read, phantom read and lost update.

Transactions, MVCC and isolation levels

What ACID guarantees on one database node, which concurrency bugs each isolation level still allows, how MVCC gives snapshots without blocking, and the code patterns that keep concurrent writes correct. Start here for transactions.

Isolation levels

What each isolation level promises, which anomalies it still allows, and how Postgres and MySQL differ in practice, including write skew, which snapshot isolation does not prevent.

MVCC and snapshots

How multi-version concurrency control lets readers and writers avoid blocking each other, how Postgres row versions and snapshots work, and why vacuum and long-running transactions matter.

Locking and concurrency control

How row locks, SELECT FOR UPDATE, SKIP LOCKED, deadlocks, version columns and advisory locks make writers wait safely, with a job queue and an optimistic update as worked examples.

Concurrency patterns that prevent races

Five habits that stop most concurrency bugs in application code, with SQL for atomic updates, unique and exclusion constraints, and a Python retry loop for serializable transactions.

Query plans and execution

How a database turns SQL text into a tree of operators, picks the cheapest one, and runs it row by row. Introduces the query plan and the difference between EXPLAIN and EXPLAIN ANALYZE.

How a SQL query executes

How a database plans and runs a query, which access paths and join algorithms it chooses between, and how to read EXPLAIN ANALYZE when a query is slow. Start here for query performance.

Access paths and selectivity

How a database finds a table's rows, either by reading every page or by using an index. Why the planner sometimes skips an index you expected, and what selectivity has to do with it.

Join algorithms

The three ways a database joins two inputs (nested loop, hash join and merge join), what each costs, and when the planner picks each one.

Statistics and cardinality estimation

How the planner estimates how many rows each step produces, why those estimates go wrong for correlated columns, and how to refresh statistics before rewriting a query.

Reading EXPLAIN ANALYZE

How to read a real query plan bottom up, find the step that costs the time, and fix it with a matching index. Includes the common query patterns that cause slow plans.

Text vs binary formats

What serialization is, why a service's data format sets its size and cost, and how JSON compares with Protobuf, Avro and Parquet on the same record.

Serialization, schemas and compression

How data becomes bytes for the wire and for disk, how formats like JSON, Protobuf and Avro differ, how to change a schema safely, and when compression pays. Start here for data formats.

Binary schemas, Protobuf and Avro

How Protobuf uses permanent field numbers and Avro uses writer and reader schemas to read compact binary data, and when to choose each.

Schema evolution

How to change a record's shape without breaking the producers and consumers that use it. Backward and forward compatibility, safe Protobuf and Avro changes, and schema registry modes.

Compression tradeoffs

When compressing a payload saves more than it costs, how the common codecs trade size for CPU, and how to measure the trade on your own data.

Serialization pitfalls

The production bugs that come from encoded data. Large integers in JSON, mixed timestamp units, enums, null versus absent, money as floats, and checksums that are not security.