Process and Memory Architecture

The Advanced SQL course built the practice of measuring how much work a query does: index definition, predicate phrasing, join order, statistics freshness. All of those decisions lived at the query level, and the engine itself stayed a black box. When the plan output read SCAN loan, it was known that two million rows were read; where those rows came from, whether they sat in memory or on disk, and who did the reading were never asked.

This lesson opens that box. Its question is: when a query arrives, which process handles it, which memory does the data come from, and how much does the size of that memory change the result? The difference between keeping a table in memory and reading it from disk is no smaller than the difference between a good index and a bad one.

The Engine’s Two Setup Forms

Relational engines are built in two distinct forms, and this choice determines the rest of the architecture.

A server-based engine runs as a process group independent of the application. The application sends a request over a network connection, the engine processes the request in its own processes, and returns the result. Only these processes touch the data files. Hundreds of application instances can connect to the same database; all of them talk to a single authoritative process.

An in-process engine is a library linked into the application’s own process. There is no separate server; the query runs in the same process as the function call the application makes. The data file is accessed directly through operating system calls; concurrency is carried out through file locks.

The distinction is not a quality ranking. An in-process engine eliminates network latency and inter-process communication entirely and needs no setup or administration; in exchange, having no central point of control means it does not handle high-write- concurrency workloads and fine-grained authorization as well as a server-based setup does. The runs in this course use an in-process engine (sqlite3); places where a server-based setup behaves differently are noted separately in the text.

The Division of Labor Among Background Processes

In a server-based engine, the process that handles an incoming query does only part of the work. The rest falls to background processes that run continuously without anyone requesting them. Their names vary by engine; their functions do not.

The connection handler. Accepts new connections, performs authentication, and assigns a worker process or thread to that connection. This worker is the one that parses, plans, and executes the query.

The log writer. Takes change records from the buffer waiting to be written to disk at commit time and transfers them to the file. The next two lessons examine in detail what this process writes.

The checkpoint process. Writes pages that have changed in memory to the data files at regular intervals. Its job is to bound how far back recovery has to go.

The cleanup process. Collects old versions of deleted or updated rows that no one sees anymore and makes their space reusable. In this course, this is the subject of the Dead Row Cleanup lesson.

The statistics collector. Updates information such as row counts, value distributions, and access counters for tables. In the Advanced SQL course, the numbers cited in the Statistics and Cardinality lesson as the planner’s source of estimates come from this process.

The reason for this separation is to avoid keeping the query waiting. If a query had to collect its own garbage or write every changed page to disk, response time would be inflated by work unrelated to the user’s request. Background processes move this work outside the query; in exchange, administration comes down to tuning how often and how aggressively these processes run.

Shared Memory and Per-Connection Memory

The memory an engine uses is not a single pool; it splits into two distinct classes, and this split is the basis of server sizing.

The shared buffer pool is the common area that holds pages read from the data file. All connections use the same pool. When a query reads a page, the page enters the pool; if another connection requests the same page, the disk is never read. The pool also sits on the write path: a modified page is changed in the pool first, and writing it to disk is deferred.

Per-connection memory is the space a single query allocates for its own work: a sort buffer, the hash table of a hash join, temporary result sets. This space is not shared; each connection requests its own share separately.

The split between the two classes matters for sizing because they scale differently. The shared pool is a fixed cost: doubling the connections does not require doubling the pool. Per-connection memory, though, is multiplied by the connection count; a sort buffer allocated per query can be allocated two hundred times over with two hundred concurrent connections. Most configurations that run out of memory arise from this multiplication being overlooked.

The Measured Effect of the Buffer Pool

In an in-process engine, the page cache is per connection and its size is set by a configuration value. The run below executes the same query three times with two different cache sizes and prints the page cache hit/miss counters. The data set is the library loan records carried over from earlier courses.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE book (
  book_id   INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  author    TEXT NOT NULL,
  year      INTEGER NOT NULL,
  branch_id INTEGER NOT NULL
);
INSERT INTO book (book_id, title, author, year, branch_id)
WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM counter WHERE n < 200000)
SELECT n, 'Book ' || n, 'Author ' || (n % 4000), 1950 + (n % 75), 1 + (n % 8)
FROM counter;
SQL

sqlite3 library.db "SELECT 'book table page count:', count(*) FROM dbstat WHERE name='book';"

for size in 200 2000; do
  printf '=== cache %s pages ===\n' "$size"
  sqlite3 library.db <<SQL 2>&1 | grep -E 'Page cache (hits|misses)'
PRAGMA cache_size = $size;
.stats on
SELECT count(*) FROM book WHERE year > 2000;
SELECT count(*) FROM book WHERE year > 2000;
SELECT count(*) FROM book WHERE year > 2000;
SQL
done

book table page count:|1780
=== cache 200 pages ===
Page cache hits:                     2
Page cache misses:                   1781
Page cache hits:                     2
Page cache misses:                   1779
Page cache hits:                     2
Page cache misses:                   1779
=== cache 2000 pages ===
Page cache hits:                     2
Page cache misses:                   1781
Page cache hits:                     1781
Page cache misses:                   0
Page cache hits:                     1781
Page cache misses:                   0

The numbers tell the whole story. The table holds 1780 pages. When the cache is 200 pages, nearly every scan has to reread almost all of the pages: by the end of the first scan only the last 200 pages remain in the cache, and the second scan starts from the beginning and requests pages that are not there. The hit rate stays close to zero across all three scans.

When the cache is 2000 pages, the first scan still produces 1781 misses — the data is not yet in memory. But the second and third scans produce not a single miss: every page is in the pool. Same query, same plan, same step count; the only difference is where the pages come from.

Reading the output requires one caution: the .stats on counters belong to the engine’s own page cache and do not cover the operating system’s file cache. A miss does not necessarily mean the data came from disk — it only means it was not found in the engine’s pool. The page count, however, is independent of the environment: the same data and the same page size give 1780 pages on every machine.

Pool Size and the Working Set

The measurement showed the jump from one extreme to the other. The curve in between is what actually matters when choosing a pool size, and trying it out one value at a time on a real engine is expensive. The model below shows it cheaply: a portion of page requests piles onto a small slice of the table, and the pool makes room by evicting the least recently used page.

This is a model; it is not the engine’s own buffer pool. The real engine’s eviction policy is more elaborate, and writes affect the pool as well as reads. What the model shows is the shape of the relationship between size and hit rate.

cat > pool.mjs <<'JS'
// Model: a page pool that evicts the least recently used page.
class Pool {
  constructor(capacity) { this.capacity = capacity; this.pages = new Map(); this.hits = 0; this.misses = 0; }
  read(page) {
    if (this.pages.has(page)) { this.pages.delete(page); this.pages.set(page, 1); this.hits++; return; }
    this.misses++;
    if (this.pages.size >= this.capacity) this.pages.delete(this.pages.keys().next().value);
    this.pages.set(page, 1);
  }
  rate() { return this.hits / (this.hits + this.misses); }
}
function generator(seed) {               // deterministic pseudorandom generator
  let a = seed >>> 0;
  return () => { a = (a + 0x9e3779b9) >>> 0;
    let t = a; t = Math.imul(t ^ (t >>> 15), t | 1); t ^= t + Math.imul(t ^ (t >>> 7), t | 61);
    return ((t ^ (t >>> 14)) >>> 0) / 4294967296; };
}
const TABLE = 2000, HOT = 200, REQUESTS = 500000;
function genLoad(seed) {                 // 90% of requests go to the 200 hot pages
  const r = generator(seed), l = new Int32Array(REQUESTS);
  for (let i = 0; i < REQUESTS; i++)
    l[i] = r() < 0.9 ? Math.floor(r() * HOT) : HOT + Math.floor(r() * (TABLE - HOT));
  return l;
}

console.log('— single pool: hit rate by capacity —');
const load = genLoad(2024);
for (const k of [20, 50, 100, 200, 400, 1000, 2000]) {
  const h = new Pool(k);
  for (const s of load) h.read(s);
  console.log(String(k).padStart(6), 'pages →', (h.rate() * 100).toFixed(1).padStart(5), '%');
}

console.log('— same total memory: four private caches / one shared pool —');
const loads = [1, 2, 3, 4].map((i) => genLoad(2024 + i));
const priv = [1, 2, 3, 4].map(() => new Pool(100));
let hitsPriv = 0, missPriv = 0;
loads.forEach((y, i) => { for (const s of y) priv[i].read(s); });
priv.forEach((h) => { hitsPriv += h.hits; missPriv += h.misses; });
const shared = new Pool(400);
for (let i = 0; i < REQUESTS; i++) for (const y of loads) shared.read(y[i]);
console.log('four × 100 pages private →', ((hitsPriv / (hitsPriv + missPriv)) * 100).toFixed(1), '%');
console.log('one × 400 pages shared →', (shared.rate() * 100).toFixed(1), '%');
JS
node pool.mjs

— single pool: hit rate by capacity —
    20 pages →   8.1 %
    50 pages →  20.1 %
   100 pages →  39.4 %
   200 pages →  73.6 %
   400 pages →  91.1 %
  1000 pages →  94.4 %
  2000 pages →  99.6 %
— same total memory: four private caches / one shared pool —
four × 100 pages private → 39.4 %
one × 400 pages shared → 91.1 %

The shape of the curve is uniform: the hit rate rises quickly until the pool grows large enough to hold the frequently requested set of pages, and past that point the gain flattens out. This set is called the working set. In the model the working set is 200 pages; when the pool reaches 200, the rate is 73.6%, at 400 it is 91.1%, and beyond that doubling the memory again gains only three points.

The administrative decision follows from this: size the pool not to the size of the whole table, but to the portion that is actually touched. The working set of a hundred-gigabyte database can be a few gigabytes; raising memory just past that threshold is both cheaper and nearly as effective as trying to fit the entire database in memory.

The second part of the model turns the split from the beginning of the lesson into a number. Total memory is 400 pages in both arrangements. When four connections each keep their own 100-page cache, the hit rate stays at 39.4%; when the same memory is one shared pool, it rises to 91.1%. The reason is clear: the four connections’ working sets overlap heavily, the same hot pages are stored four times over in private caches, and each copy costs its own space. This is why server-based engines share their buffer pool.

Summary

• A server-based engine runs as a separate process group and only those processes touch the data files; an in-process engine is a library linked into the application’s own process.
• Background processes move log writing, checkpointing, cleanup, and statistics collection off the query’s response path.
• Memory splits into two classes: the shared buffer pool does not scale with the connection count, while the sort and hash areas allocated per connection do.
• In the measurement, a 1780-page table was reread on every scan with a 200-page cache; with a 2000-page cache, it produced no misses after the second scan.
• The model showed that the hit rate rises quickly until the working set fits in the pool and then flattens, and that the same total memory gives 91.1% in a shared pool versus 39.4% in four private caches.

Next Step

In this lesson, a page appeared as a unit held in memory: something that enters the pool, gets evicted, produces a hit or a miss. What the page itself is, how many rows fit inside it, what happens when a row does not fit, and how all these pages sit on disk in files were not asked. The next lesson takes up the data as it exists on disk: it measures the relationship among page size, page count, and file size, counts the table’s and the index’s share of the file separately, and defines the tablespace concept.