Processes, memory, pages and TOAST

conceptThe concept

Open the interactive lesson to use this diagram.

The postmaster accepts connections and forks a backend process for each one. Backends share shared_buffers (the page cache), the WAL buffers, the lock table and the commit log. Background processes handle writing dirty pages (background writer, checkpointer), flushing WAL, autovacuum, statistics and replication.

Each table and index is a relation stored as files under base/<database oid>/, split into 1 GB segments of 8 kB pages. Extra forks track free space (_fsm) and pages that are all-visible or all-frozen (_vm). A page holds a header, an array of line pointers, and tuples growing from the end.

A row must fit in a page, so values over about 2 kB are compressed (pglz or lz4) and, if still large, sliced into a separate TOAST table and replaced by a pointer. Reading a column that is not TOASTed never touches the TOAST table.

whyWhy it matters

Process-per-connection is why 5,000 direct connections hurt and a pooler helps. Double caching (shared_buffers plus the OS page cache) is why shared_buffers is usually about 25% of RAM, not 80%. TOAST is why "SELECT *" on a table with large JSON columns is much slower than selecting only the columns you need.

Use caseReal-world scenario

A DBA sizing a new server needs the data size, index size and TOAST size of the biggest tables, and the number of active connections at peak. All of it is available from SQL.

Try itExamples

Run and edit these in the interactive lesson.

SELECT relname, relkind, relpages, reltuples::bigint,
       pg_relation_filepath(oid) AS file
FROM pg_class
WHERE relname IN ('orders', 'orders_pkey');
SHOW block_size;

SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap,
       pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
       pg_size_pretty(coalesce(pg_total_relation_size(c.reltoastrelid), 0)) AS toast,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_class c
WHERE c.relnamespace = 'public'::regnamespace AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC;

BEGIN;
CREATE TABLE toast_lab (id int, doc text);
INSERT INTO toast_lab VALUES (1, repeat('PostgreSQL ', 50000)), (2, (SELECT string_agg(md5(g::text), '') FROM generate_series(1, 2000) g));
SELECT id,
       octet_length(doc) AS raw_bytes,
       pg_column_size(doc) AS stored_bytes,
       pg_column_compression(doc) AS compression
FROM toast_lab;
SELECT pg_size_pretty(pg_relation_size('toast_lab')) AS heap,
       pg_size_pretty(pg_total_relation_size(reltoastrelid)) AS toast
FROM pg_class WHERE relname = 'toast_lab';
ROLLBACK;

SELECT lower, upper, special, pagesize
FROM page_header(get_raw_page('employees', 0));

SELECT lp, lp_off, lp_len, t_xmin, t_ctid
FROM heap_page_items(get_raw_page('employees', 0))
LIMIT 5;

internalsUnder the hood

In this course the server is PGlite: the real PostgreSQL code compiled to WebAssembly, running as a single backend inside your browser tab. Storage, MVCC, the planner and the executor are the real thing; there are no separate background processes or network connections, so features like replication and concurrent sessions are explained rather than run.

Avoid / preferDo this, not that

CREATE TABLE documents (id int PRIMARY KEY, title text, pdf bytea);
SELECT * FROM documents;  -- in a listing page

SELECT id, title FROM documents;
-- and fetch pdf only when the user opens one

-- copy files out of the data directory as a backup

-- pg_basebackup or a backup tool such as pgBackRest or Barman

PitfallsCommon mistakes

• Setting shared_buffers to most of RAM. The OS cache already caches files; 25% is the usual starting point, and the rest stays useful.
• Using pg_relation_size to report table size and missing indexes and TOAST. pg_total_relation_size includes both.
• Opening thousands of direct connections. Each is a process with its own memory; use a pooler.

LimitsWhen not to use it

Do not store multi-megabyte files in PostgreSQL by default. Object storage plus a reference row is cheaper, faster to back up and easier to serve; keep blobs in the database only when transactional consistency with them is essential.

SecuritySecurity notes

Anyone who can read the data directory or the disk can read all data, regardless of GRANTs. Keep it mode 0700 owned by the postgres user, and use disk or volume encryption.

ChallengeTry it yourself

SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap,
       pg_size_pretty(pg_indexes_size(c.oid)) AS indexes,
       pg_size_pretty(coalesce(pg_total_relation_size(nullif(c.reltoastrelid, 0::oid)), 0)) AS toast
FROM pg_class c
WHERE c.relnamespace = 'public'::regnamespace AND c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Database physical storage
• TOAST