PostgreSQL architecture: processes, memory, files

processes_session.py

"""The processes of a PostgreSQL 18.6 server: alone with three connections, then with a streaming standby."""

from pg_session import Server

with Server(settings={"summarize_wal": "on"}) as pg:
    pg.up()
    with pg.sessions(2):
        pg.psql("processes.sql", note="the primary, with two more psql connections open")
    standby_port = pg.standby()
    pg.psql("processes.sql", note="the primary again, now with a streaming standby attached")
    pg.psql("processes.sql", port=standby_port, note="the standby")

-- the primary, with two more psql connections open
$ psql -X -a -f processes.sql
-- Which kinds of process does this server run, and how many of each?
SELECT current_setting('server_version') AS version, pg_is_in_recovery() AS standby;
 version | standby 
---------+---------
 18.6    | f
(1 row)

SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY backend_type ORDER BY backend_type;
         backend_type         | count 
------------------------------+-------
 autovacuum launcher          |     1
 background writer            |     1
 checkpointer                 |     1
 client backend               |     3
 io worker                    |     3
 logical replication launcher |     1
 walsummarizer                |     1
 walwriter                    |     1
(8 rows)

-- the primary again, now with a streaming standby attached
$ psql -X -a -f processes.sql
-- Which kinds of process does this server run, and how many of each?
SELECT current_setting('server_version') AS version, pg_is_in_recovery() AS standby;
 version | standby 
---------+---------
 18.6    | f
(1 row)

SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY backend_type ORDER BY backend_type;
         backend_type         | count 
------------------------------+-------
 autovacuum launcher          |     1
 background writer            |     1
 checkpointer                 |     1
 client backend               |     1
 io worker                    |     3
 logical replication launcher |     1
 walsender                    |     1
 walsummarizer                |     1
 walwriter                    |     1
(9 rows)

-- the standby
$ psql -X -a -f processes.sql
-- Which kinds of process does this server run, and how many of each?
SELECT current_setting('server_version') AS version, pg_is_in_recovery() AS standby;
 version | standby 
---------+---------
 18.6    | t
(1 row)

SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY backend_type ORDER BY backend_type;
   backend_type    | count 
-------------------+-------
 background writer |     1
 checkpointer      |     1
 client backend    |     1
 io worker         |     3
 startup           |     1
 walreceiver       |     1
 walsummarizer     |     1
(7 rows)

processes.sql

-- Which kinds of process does this server run, and how many of each?
SELECT current_setting('server_version') AS version, pg_is_in_recovery() AS standby;
SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY backend_type ORDER BY backend_type;

pg_session.py

"""Throwaway PostgreSQL 18.6 servers for the examples of this lesson.

Every session creates a cluster of its own with initdb in a new folder inside the examples folder, starts it, runs
psql scripts and server programs on it, prints what they printed, and stops and deletes it again, also when something
fails. The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file.
It listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system reports as
unused, with no Unix socket, and it asks for a password (scram-sha-256) made up for the run and never printed.
learn:verify's run sandbox cannot read the home folder, where mise keeps PostgreSQL, so these sessions are recorded
with LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with four substitutions so that a record never shows the computer
it ran on and is the same on every run: the run's folder is shown as ".", the folder of PostgreSQL's programs is left
out of their paths, the operating-system user's name is shown as "learner", and the dots pg_ctl prints while it waits
(one about every tenth of a second) as three. The sessions never print the port. A session stops with status 1 when
a command fails that should have worked, or reports a different number of errors than the session expects, so a run
that went wrong is never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"
OS_USER = pwd.getpwuid(os.geteuid()).pw_name


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway cluster: initdb, settings, start; stopped and deleted when the with block ends."""

    def __init__(self, settings=None, initdb=(), name="data"):
        self.settings = dict(settings or {})
        self.initdb_args = list(initdb)
        self.name = name
        self.running = []

    def __enter__(self):
        if not (BIN / "postgres").exists():
            sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        (self.base / "pwfile").write_text(self.password + "\n")
        self.env = {**os.environ, "PGHOST": "127.0.0.1", "PGPORT": str(self.port), "PGUSER": "postgres",
                    "PGPASSWORD": self.password, "PGDATABASE": "postgres", "PGCONNECT_TIMEOUT": "10"}
        for key in ("PGSERVICE", "PGSERVICEFILE", "PGOPTIONS", "PSQLRC"):
            self.env.pop(key, None)
        for script in Path(".").glob("*.sql"):
            shutil.copy(script, self.base / script.name)
        for data in Path(".").glob("*.csv"):
            shutil.copy(data, self.base / data.name)
        return self

    def __exit__(self, *exc):
        for folder, _port in reversed(self.running):
            self._call(["pg_ctl", "-D", folder, "-m", "fast", "-w", "-t", "60", "stop"])
        self.running = []
        shutil.rmtree(self.base, ignore_errors=True)

    # ---- the cluster ---------------------------------------------------------------------------------------------
    def initdb(self, show=False):
        args = ["initdb", "-D", self.name, "-U", "postgres", "--auth=scram-sha-256", "--pwfile=pwfile",
                *self.initdb_args]
        if not show:
            args.append("--no-instructions")
        self._expect(args, show=show)
        with open(self.base / self.name / "postgresql.conf", "a") as conf:
            conf.write("\n# Added by the session: 127.0.0.1 only, no Unix socket.\n")
            conf.write(f"listen_addresses = '127.0.0.1'\nport = {self.port}\nunix_socket_directories = ''\n")
            for key, value in self.settings.items():
                conf.write(f"{key} = {value}\n")

    def start(self, show=False, folder=None, port=None):
        folder = folder or self.name
        self._expect(["pg_ctl", "-D", folder, "-l", f"{folder}.log", "-w", "-t", "60", "start"], show=show)
        self.running.append((folder, port or self.port))

    def up(self):
        """initdb and start, quietly."""
        self.initdb()
        self.start()
        return self

    def standby(self, folder="standby"):
        """A streaming standby of this server, made with pg_basebackup -R, started on a port of its own."""
        self._expect(["pg_basebackup", "-D", folder, "-R", "-X", "stream", "-c", "fast"])
        port = free_port()
        with open(self.base / folder / "postgresql.conf", "a") as conf:
            conf.write(f"port = {port}\n")
        self.start(folder=folder, port=port)
        for _ in range(300):
            if self.query("SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'") == "1":
                return port
            time.sleep(0.1)
        sys.exit("the standby did not start streaming")

    # ---- running things ------------------------------------------------------------------------------------------
    def _call(self, args, env=None):
        args = [str(a) for a in args]
        done = subprocess.run([str(BIN / args[0]), *args[1:]], stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
                              text=True, cwd=self.base, env=env or self.env)
        return done.returncode, self.clean(done.stdout)

    def _expect(self, args, show=False, status=0):
        code, out = self._call(args)
        if show:
            _show(args, out)
        if code != status:
            if not show:
                print(out)
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The run's folder as ".", the user as learner, pg_ctl's waiting dots as three (see the docstring)."""
        text = text.replace(str(BIN) + "/", "").replace(str(self.base) + "/", "").replace(str(self.base), ".")
        text = re.sub(r"(waiting for server to (?:start|shut down))\.+", r"\1...", text)
        return text.replace(f'"{OS_USER}"', '"learner"')

    def wait_for(self, sql, value):
        """Waits (unprinted) until a query returns the value, for example after a reload."""
        for _ in range(300):
            if self.query(sql) == value:
                return
            time.sleep(0.1)
        sys.exit(f"{sql} never returned {value}")

    def show_file(self, path):
        """Prints a text file of the cluster, as `cat` would."""
        print(f"$ cat {path}")
        print((self.base / path).read_text().rstrip("\n"))
        print()

    def query(self, sql, port=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        code, out = self._call(["psql", "-X", "-At", "-c", sql], env=env)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only to give a role the run's password)."""
        self.query(sql)

    def psql(self, script, errors=0, status=None, user=None, db=None, port=None, flags=("-a",), note=None):
        """Runs a psql script with -X (no ~/.psqlrc) and the given flags, and prints the command and its output.
        `errors` is how many ERROR lines the script must print; `status` the exit status it must end with."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        if user:
            env["PGUSER"] = user
        if db:
            env["PGDATABASE"] = db
        args = ["psql", "-X", *flags, "-f", script]
        code, out = self._call(args, env=env)
        shown = ["psql", "-X", *flags, "-f", script]
        if user:
            shown[1:1] = ["-U", user]
        if db:
            shown[1:1] = ["-d", db]
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if "ERROR:" in line or ": error:" in line)
        expected_status = 0 if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")

    def sessions(self, count):
        """Opens `count` idle psql connections (each one a client backend) until the with block ends."""
        return _Idle(self, count)

    def shell(self, *args, status=0):
        """Runs one of PostgreSQL's programs and prints the command and its output."""
        return self._expect(list(args), show=True, status=status)

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the cluster (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"-- in {folder}" + (f", the names that start with {prefix}" if prefix else "") + ":")
        print(textwrap.fill("  ".join(names), width=78, initial_indent="   ", subsequent_indent="   ",
                            break_on_hyphens=False))
        print()
        return names


def _arg(a):
    """A command-line argument as a reader would type it: double quotes around text with single quotes in it."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()


class _Idle:
    """Idle psql connections, closed again when the with block ends."""

    def __init__(self, server, count):
        self.server, self.count, self.procs = server, count, []

    def __enter__(self):
        before = int(self.server.query("SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"))
        for _ in range(self.count):
            self.procs.append(subprocess.Popen([str(BIN / "psql"), "-X", "-q"], stdin=subprocess.PIPE,
                                               stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL,
                                               env=self.server.env, cwd=self.server.base))
        sql = "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"
        for _ in range(300):
            # the query's own connection is one more client backend
            if int(self.server.query(sql)) == before + self.count:
                return self
            time.sleep(0.1)
        sys.exit("the idle connections did not open")

    def __exit__(self, *exc):
        for p in self.procs:
            p.stdin.close()
            p.wait(timeout=30)

memory_session.py

"""Shared and per-query memory on a PostgreSQL 18.6 server with its default settings."""

from pg_session import Server

with Server() as pg:
    pg.up()
    pg.psql("memory.sql")

$ psql -X -a -f memory.sql
-- Memory every process shares, sized when the server starts, and memory each query takes for itself.
SELECT name, setting, unit, context FROM pg_settings
WHERE name IN ('shared_buffers', 'wal_buffers', 'work_mem', 'maintenance_work_mem', 'temp_buffers',
               'effective_cache_size', 'max_connections')
ORDER BY name;
         name         | setting | unit |  context   
----------------------+---------+------+------------
 effective_cache_size | 524288  | 8kB  | user
 maintenance_work_mem | 65536   | kB   | user
 max_connections      | 100     |      | postmaster
 shared_buffers       | 16384   | 8kB  | postmaster
 temp_buffers         | 1024    | 8kB  | user
 wal_buffers          | 512     | 8kB  | postmaster
 work_mem             | 4096    | kB   | user
(7 rows)

-- shared_buffers is counted in pages of block_size bytes: 16384 pages of 8192 bytes.
SELECT current_setting('block_size') AS block_size,
       pg_size_pretty(16384 * current_setting('block_size')::bigint) AS shared_buffers;
 block_size | shared_buffers 
------------+----------------
 8192       | 128 MB
(1 row)

SHOW shared_memory_size;
 shared_memory_size 
--------------------
 150MB
(1 row)

SELECT name, pg_size_pretty(allocated_size) AS size FROM pg_shmem_allocations
WHERE name IN ('Buffer Blocks', 'XLOG Ctl') ORDER BY allocated_size DESC;
     name      |  size   
---------------+---------
 Buffer Blocks | 128 MB
 XLOG Ctl      | 4110 kB
(2 rows)

-- One sort of 200,000 rows with the default work_mem, then with 64MB.
CREATE TABLE readings AS SELECT n AS id, md5(n::text) AS tag FROM generate_series(1, 200000) AS n;
SELECT 200000
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM readings ORDER BY tag;
                         QUERY PLAN                         
------------------------------------------------------------
 Sort (actual rows=200000.00 loops=1)
   Sort Key: tag
   Sort Method: external merge  Disk: 9208kB
   ->  Seq Scan on readings (actual rows=200000.00 loops=1)
(4 rows)

SET work_mem = '64MB';
SET
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM readings ORDER BY tag;
                         QUERY PLAN                         
------------------------------------------------------------
 Sort (actual rows=200000.00 loops=1)
   Sort Key: tag
   Sort Method: quicksort  Memory: 17082kB
   ->  Seq Scan on readings (actual rows=200000.00 loops=1)
(4 rows)

memory.sql

-- Memory every process shares, sized when the server starts, and memory each query takes for itself.
SELECT name, setting, unit, context FROM pg_settings
WHERE name IN ('shared_buffers', 'wal_buffers', 'work_mem', 'maintenance_work_mem', 'temp_buffers',
               'effective_cache_size', 'max_connections')
ORDER BY name;
-- shared_buffers is counted in pages of block_size bytes: 16384 pages of 8192 bytes.
SELECT current_setting('block_size') AS block_size,
       pg_size_pretty(16384 * current_setting('block_size')::bigint) AS shared_buffers;
SHOW shared_memory_size;
SELECT name, pg_size_pretty(allocated_size) AS size FROM pg_shmem_allocations
WHERE name IN ('Buffer Blocks', 'XLOG Ctl') ORDER BY allocated_size DESC;
-- One sort of 200,000 rows with the default work_mem, then with 64MB.
CREATE TABLE readings AS SELECT n AS id, md5(n::text) AS tag FROM generate_series(1, 200000) AS n;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM readings ORDER BY tag;
SET work_mem = '64MB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) SELECT * FROM readings ORDER BY tag;

files_session.py

"""Where one table's rows live on disk, and the forks that appear beside its main file."""

import os

from pg_session import Server

# Autovacuum is off on this server so that only the session's own VACUUM runs.
with Server(settings={"autovacuum": "off"}) as pg:
    pg.up()
    pg.psql("files.sql")
    folder, name = os.path.split(pg.query("SELECT pg_relation_filepath('orders')"))
    pg.listing(f"data/{folder}", prefix=name)
    pg.psql("vacuum.sql")
    pg.listing(f"data/{folder}", prefix=name)
    pg.psql("truncate.sql")
    pg.listing("data")
    pg.listing("data/pg_wal")

$ psql -X -a -f files.sql
-- A table of 10,000 orders, and the file that holds it.
CREATE TABLE orders (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer text NOT NULL,
                     total numeric(10,2) NOT NULL);
CREATE TABLE
INSERT INTO orders (customer, total)
SELECT 'customer ' || n, (n % 500) + 0.5 FROM generate_series(1, 10000) AS n;
INSERT 0 10000
SELECT oid, datname FROM pg_database ORDER BY oid;
 oid |  datname  
-----+-----------
   1 | template1
   4 | template0
   5 | postgres
(3 rows)

SELECT oid, relfilenode, pg_relation_filepath('orders') AS path FROM pg_class WHERE relname = 'orders';
  oid  | relfilenode |     path     
-------+-------------+--------------
 16385 |       16385 | base/5/16385
(1 row)

SELECT pg_relation_size('orders', 'main') AS main, pg_relation_size('orders', 'fsm') AS fsm,
       pg_relation_size('orders', 'vm') AS vm;
  main  |  fsm  | vm 
--------+-------+----
 606208 | 24576 |  0
(1 row)

-- in data/base/5, the names that start with 16385:
   16385  16385_fsm

$ psql -X -a -f vacuum.sql
-- VACUUM records which pages hold only rows every transaction can see: that needs the visibility map.
VACUUM orders;
VACUUM
SELECT pg_relation_size('orders', 'main') AS main, pg_relation_size('orders', 'fsm') AS fsm,
       pg_relation_size('orders', 'vm') AS vm;
  main  |  fsm  |  vm  
--------+-------+------
 606208 | 24576 | 8192
(1 row)

-- in data/base/5, the names that start with 16385:
   16385  16385_fsm  16385_vm

$ psql -X -a -f truncate.sql
-- TRUNCATE gives the table a new, empty file: the OID stays, the file name (relfilenode) changes.
TRUNCATE orders;
TRUNCATE TABLE
SELECT oid, relfilenode, pg_relation_filepath('orders') AS path FROM pg_class WHERE relname = 'orders';
  oid  | relfilenode |     path     
-------+-------------+--------------
 16385 |       16395 | base/5/16395
(1 row)

-- in data:
   PG_VERSION  base/  global/  pg_commit_ts/  pg_dynshmem/  pg_hba.conf
   pg_ident.conf  pg_logical/  pg_multixact/  pg_notify/  pg_replslot/
   pg_serial/  pg_snapshots/  pg_stat/  pg_stat_tmp/  pg_subtrans/  pg_tblspc/
   pg_twophase/  pg_wal/  pg_xact/  postgresql.auto.conf  postgresql.conf
   postmaster.opts  postmaster.pid

-- in data/pg_wal:
   000000010000000000000001  archive_status/  summaries/

files.sql

-- A table of 10,000 orders, and the file that holds it.
CREATE TABLE orders (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer text NOT NULL,
                     total numeric(10,2) NOT NULL);
INSERT INTO orders (customer, total)
SELECT 'customer ' || n, (n % 500) + 0.5 FROM generate_series(1, 10000) AS n;
SELECT oid, datname FROM pg_database ORDER BY oid;
SELECT oid, relfilenode, pg_relation_filepath('orders') AS path FROM pg_class WHERE relname = 'orders';
SELECT pg_relation_size('orders', 'main') AS main, pg_relation_size('orders', 'fsm') AS fsm,
       pg_relation_size('orders', 'vm') AS vm;

vacuum.sql

-- VACUUM records which pages hold only rows every transaction can see: that needs the visibility map.
VACUUM orders;
SELECT pg_relation_size('orders', 'main') AS main, pg_relation_size('orders', 'fsm') AS fsm,
       pg_relation_size('orders', 'vm') AS vm;

truncate.sql

-- TRUNCATE gives the table a new, empty file: the OID stays, the file name (relfilenode) changes.
TRUNCATE orders;
SELECT oid, relfilenode, pg_relation_filepath('orders') AS path FROM pg_class WHERE relname = 'orders';