How Postgres Runs on Linux

Concept. Linux runs on any hardware. Postgres runs using Linux's processes and a shared pool of RAM.

Intuition. Two programs on one machine can both use the same address, both hold a file open, and both run as if they had the processor to themselves. The kernel, the part of Linux that owns the machine, keeps the maps that make all of it work.

This page is optional background for the modules that follow.

The stack

Start at the bottom. CPUs, memory chips, an SSD, a card that reaches storage in another rack.

Linux is the only thing that touches any of it. Every program gets the same three things: virtual RAM, a virtual CPU, and files. The box holding those three is a process, and one process cannot see inside another, so a crash takes down one program and nothing else.

Four stacked layers. Layer one is hardware: CPUs, RAM, SSDs and disks, a network card. Layer two is Linux, the only layer that touches it. Layer three is the process, holding a memory space, open files, and time on a CPU. Layer four is Postgres, running inside layer three and never seeing layer one.

Figure 1. Linux runs on hardware and exports processes. A process includes a private memory space that Linux maps to physical RAM, a set of open files referenced through handles, and time on a CPU that the kernel schedules via context switches. Postgres runs as a program inside a process and uses only these interfaces.

Two of those three are counted in pages: virtual RAM and files. Linux's unit is the OS-page, a block of fixed size, and everything is measured in how many OS-pages it takes.

The sandbox is drawn around the process, not around each thread inside it. A single process can hold dozens of threads, all sharing that one memory space and those same open files, so two threads can reach the same byte at the same moment. That is why a program needs locks of its own.

Across processes there is one more exception, and a program has to ask for it: shared memory, a region several processes map at once and all of them can write.

One pool, many workers

Postgres runs as a set of processes, one per connection, and every one of them attaches to a single shared-memory region.

Most of that region is the buffer pool. Postgres reads a table in fixed chunks it calls blocks, and this course calls them DB-pages; the pool holds the DB-pages it has already read off the disk, and every connection sees the same copy. A hundred connections querying one table read it into memory once instead of a hundred times, and a row one connection commits is a row the next connection can see.

Every read() and write() Postgres makes crosses into the kernel, which moves the data in fixed-size OS-pages.

Shared memory moves the failure boundary. A crash in one process cannot corrupt another process's private memory, and the pool is not private. Postgres restarts every worker after one of them crashes, because the crashed worker may have left the pool inconsistent.

The table on disk is a field of small same-size page squares, thousands of them. Above it the Postgres server holds four workers, one per connection, and a short row of squares is the buffer pool they all share. Three pages turn orange in scattered places across the file and are lifted into the pool. Then worker 1, running SELECT, and worker 2, running UPDATE, both reach for the same one.

Figure 2. A table occupies many DB-pages on disk. The buffer pool holds a small subset of those DB-pages, pulled from scattered locations. Multiple workers can access the same cached DB-page, including one worker that reads it and another that modifies it. Linux treats both workers as ordinary processes that read and write ordinary memory.

Concurrent access is one problem. Durability is the other, and the Linux manual states it for write(): "A successful return from write() does not make any guarantee that data has been committed to disk." The call returns once Linux has copied the bytes into the kernel cache. A power failure drops them, and the caller was told the write succeeded.

Two questions:

  • Worker 1 is halfway through a DB-page when worker 2 changes it. What should worker 1 see?

  • Worker 2's write() has already returned. If the power fails now, is the change still there?

Each gets a module of its own later.

From one machine to many

An OS-page is 4 KB on x86-64, and a program gets no say in it. A DB-page is whatever size the database picks.

A notebook and a paperback side by side. The notebook has a large orange page rectangle and a short stack of ten bars beside it; the paperback has a page about half the size and a stack four times as tall. Both stacks sit on one baseline, so page size and page count read as separate choices.

Figure 3. Page size and page count vary independently. A DB-page also has a fixed size, and a table spans as many DB-pages as its data requires.

Postgres picks 8 KB. A big-data system picks 64 MB: bigger data, bigger unit, and 64 MB is what counts as one read. As a ratio of DB-page to OS-page, Postgres runs 1:2 and a big-data system runs 1:16,384.

A table on disk drawn as a long row of DB-page tiles, trailing off in an ellipsis. One tile is outlined and magnified into a large panel filled with much smaller squares, the 4 KB OS-pages it is made of, elided on both axes: 16,384 of them make one 64 MB DB-page. Two adjacent squares are then outlined, with a panel noting that a Postgres DB-page is 8 KB, two squares.

Figure 4. A table on disk consists of DB-pages. Each DB-page spans an integer number of 4 KB OS-pages on x86-64. Postgres groups two OS-pages into one 8 KB DB-page. A big-data system groups 16,384 OS-pages into one 64 MB DB-page.

Linux optimizes on what it has already seen. Postgres optimizes on what it is about to do, because it has read the query before it touches a byte. Paging & Storage makes the DB-page precise, and counts what each one costs.