Understanding how PostgreSQL works internally transforms you from someone who uses databases to someone who truly understands them. This knowledge helps you make better design decisions and debug complex issues.
Hands-On Lab 1: Inspecting PostgreSQL Processes and Shared Memory
Goal: Master the tools for observing PostgreSQL’s internal process architecture and memory structures.
Examine the Process Hierarchy:
-- View all PostgreSQL processesSELECT pid, usename AS username, application_name, client_addr, backend_start, state, backend_type, queryFROM pg_stat_activityORDER BY backend_type, backend_start;-- Identify process types:-- - client backend: Your query connections-- - autovacuum launcher/worker: Cleanup processes-- - logical replication launcher/worker: Replication processes-- - background writer: Writes dirty buffers-- - checkpointer: Performs checkpoints-- - walwriter: Writes WAL records
Investigate Shared Memory Configuration:
-- Check shared memory settingsSELECT name, setting, unit, context, short_descFROM pg_settingsWHERE name IN ( 'shared_buffers', 'wal_buffers', 'max_connections', 'max_prepared_transactions', 'max_locks_per_transaction', 'shared_memory_size')ORDER BY name;-- Calculate total shared memory usageSELECT pg_size_pretty( current_setting('shared_buffers')::bigint * (SELECT setting FROM pg_settings WHERE name = 'block_size')::bigint) AS shared_buffers_size;
Monitor Buffer Pool Activity:
-- Install pg_buffercache extension (if not already installed)CREATE EXTENSION IF NOT EXISTS pg_buffercache;-- See what tables are cached in shared buffersSELECT c.relname AS table_name, COUNT(*) AS buffer_count, pg_size_pretty(COUNT(*) * 8192) AS size_in_cache, ROUND(100.0 * COUNT(*) / (SELECT setting FROM pg_settings WHERE name='shared_buffers')::int, 2) AS percent_of_cacheFROM pg_buffercache bJOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database()) AND c.relkind IN ('r', 'i') -- tables and indexesGROUP BY c.relnameORDER BY buffer_count DESCLIMIT 20;-- Check buffer cache hit ratioSELECT SUM(heap_blks_hit) AS heap_read_hits, SUM(heap_blks_read) AS heap_disk_reads, ROUND(100.0 * SUM(heap_blks_hit) / NULLIF(SUM(heap_blks_hit) + SUM(heap_blks_read), 0), 2) AS cache_hit_ratioFROM pg_statio_user_tables;-- Target: > 99% for well-tuned systems
Analyze WAL Activity:
-- Check current WAL positionSELECT pg_current_wal_lsn() AS current_wal_position, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')) AS total_wal_generated;-- View WAL writer statisticsSELECT wal_records, wal_fpi, -- Full Page Images wal_bytes, pg_size_pretty(wal_bytes) AS wal_bytes_pretty, wal_buffers_full, wal_write, wal_sync, stats_resetFROM pg_stat_wal;
Monitor Background Workers:
-- Check background writer statsSELECT checkpoints_timed, -- Scheduled checkpoints checkpoints_req, -- Requested checkpoints (may indicate tuning needed) buffers_checkpoint, -- Buffers written during checkpoint buffers_clean, -- Buffers cleaned by bgwriter buffers_backend, -- Buffers written by backends (should be low) buffers_alloc, -- Buffers allocated stats_resetFROM pg_stat_bgwriter;-- If buffers_backend is high, consider increasing bgwriter activity
Challenge Exercises:
Create a table with 1 million rows and observe how it affects the buffer cache
Run a full table scan and watch the buffer pool fill up
Compare cache hit ratios before and after the scan
Identify which auxiliary process is consuming the most CPU using pg_stat_activity
Calculate the percentage of shared memory used by each major component
When you can confidently navigate PostgreSQL’s internal monitoring views and interpret the results, you’ve mastered process and memory inspection.
Hands-On Lab 2: Observing MVCC Behavior and VACUUM Effects
Goal: Understand how MVCC creates tuple versions and how VACUUM reclaims space.
Set Up a Test Environment:
-- Create a test tableCREATE TABLE mvcc_test ( id SERIAL PRIMARY KEY, data TEXT, updated_at TIMESTAMP DEFAULT NOW());-- Insert initial dataINSERT INTO mvcc_test (data)SELECT 'Initial data ' || iFROM generate_series(1, 10000) AS i;-- Check initial table sizeSELECT pg_size_pretty(pg_table_size('mvcc_test')) AS table_size, pg_size_pretty(pg_total_relation_size('mvcc_test')) AS total_size_with_indexes;
Observe Tuple Versioning:
-- Enable pageinspect extensionCREATE EXTENSION IF NOT EXISTS pageinspect;-- View tuple details including xmin and xmaxSELECT ctid, -- Physical location (page, offset) xmin, -- Transaction ID that created this tuple xmax, -- Transaction ID that deleted/updated (0 if current) id, dataFROM mvcc_testWHERE id <= 5;-- Note the xmin values - they're the transaction IDs that created these rows
Create Dead Tuples Through Updates:
-- Update every row to create dead tuplesUPDATE mvcc_test SET data = 'Updated once', updated_at = NOW();-- Check table statsSELECT n_live_tup AS live_tuples, n_dead_tup AS dead_tuples, n_tup_upd AS total_updates, n_tup_del AS total_deletes, last_vacuum, last_autovacuumFROM pg_stat_user_tablesWHERE relname = 'mvcc_test';-- Check table size (should have increased significantly)SELECT pg_size_pretty(pg_table_size('mvcc_test')) AS table_size_after_update;
Observe Dead Tuples with Multiple Updates:
-- Do several more updates to accumulate dead tuplesUPDATE mvcc_test SET data = 'Updated twice' WHERE id % 2 = 0;UPDATE mvcc_test SET data = 'Updated thrice' WHERE id % 3 = 0;UPDATE mvcc_test SET data = 'Updated fourth' WHERE id % 4 = 0;-- Check bloat accumulationSELECT relname AS table_name, n_live_tup, n_dead_tup, ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_percent, pg_size_pretty(pg_table_size(relname::regclass)) AS table_size, last_vacuum, last_autovacuumFROM pg_stat_user_tablesWHERE relname = 'mvcc_test';-- You should see significant dead tuple accumulation
Run VACUUM and Observe Effects:
-- Run VACUUM (not FULL)VACUUM VERBOSE mvcc_test;-- Check resultsSELECT n_live_tup, n_dead_tup, pg_size_pretty(pg_table_size('mvcc_test')) AS table_sizeFROM pg_stat_user_tablesWHERE relname = 'mvcc_test';-- Note: Table size may not shrink much because VACUUM doesn't return space to OS-- It marks space as reusable within the table
Experiment with VACUUM FULL:
-- VACUUM FULL rewrites entire table (locks table exclusively)VACUUM FULL VERBOSE mvcc_test;-- Check size reductionSELECT pg_size_pretty(pg_table_size('mvcc_test')) AS table_size_after_vacuum_full;-- Should see significant size reduction
Observe Transaction ID Visibility:
-- In one session, start a transactionBEGIN;SELECT txid_current() AS my_transaction_id;INSERT INTO mvcc_test (data) VALUES ('Uncommitted data');-- Note the transaction IDSELECT ctid, xmin, xmax, id, dataFROM mvcc_test WHERE data = 'Uncommitted data';-- In another session (different connection), try to see this rowSELECT * FROM mvcc_test WHERE data = 'Uncommitted data';-- Result: Empty! This demonstrates MVCC isolation-- Back in first session, commitCOMMIT;-- Now second session can see itSELECT * FROM mvcc_test WHERE data = 'Uncommitted data';
Monitor Autovacuum Activity:
-- Check autovacuum configurationSELECT name, setting, unit, short_descFROM pg_settingsWHERE name LIKE 'autovacuum%'ORDER BY name;-- View recent autovacuum activitySELECT schemaname, relname, last_autovacuum, autovacuum_count, n_live_tup, n_dead_tupFROM pg_stat_user_tablesWHERE autovacuum_count > 0ORDER BY last_autovacuum DESC NULLS LAST;
Advanced Challenge:
Create a workload with 50,000 updates and measure bloat accumulation
Configure autovacuum to run more aggressively and observe the difference
Use pageinspect to examine the internal structure of a page before and after VACUUM
Simulate a long-running transaction and observe how it prevents VACUUM from reclaiming space
Calculate the “bloat ratio” for all tables in your database
When you can explain how MVCC creates multiple tuple versions, how dead tuples accumulate, and how VACUUM reclaims space (with and without returning it to the OS), you’ve mastered PostgreSQL’s core concurrency mechanism.
PostgreSQL uses a multi-process architecture (not multi-threaded). Each client connection gets its own backend process.Real-world analogy: Think of PostgreSQL as a hospital emergency room. The Postmaster is the triage nurse at the front desk who greets every patient (client connection) and assigns them a dedicated doctor (backend process). Each doctor works independently in their own examination room — if one doctor makes a mistake, the other patients are unaffected. The support staff working behind the scenes (auxiliary processes) are the specialists: the janitor who cleans rooms for reuse (Autovacuum), the pharmacist who stocks the medicine cabinet from the warehouse (Background Writer flushing dirty pages), the record keeper who writes everything into the logbook before any procedure happens (WAL Writer), and the charge nurse who does periodic full inventory counts (Checkpointer). This process-per-connection design means PostgreSQL survives individual backend crashes without bringing down the whole hospital.
PostgreSQL uses Unix signals for inter-process communication:
┌─────────────────────────────────────────────────────────────────────────────┐│ SIGNAL HANDLING │├─────────────────────────────────────────────────────────────────────────────┤│ ││ Signal │ Sender │ Effect on Backend ││ ────────────┼───────────────┼───────────────────────────────────────────── ││ SIGTERM │ Postmaster │ Smart shutdown (finish current txn, exit) ││ SIGINT │ Postmaster │ Fast shutdown (rollback current txn, exit) ││ SIGQUIT │ Postmaster │ Immediate shutdown (crash recovery needed) ││ SIGHUP │ pg_reload_conf│ Reload configuration files ││ SIGUSR1 │ Postmaster │ Latch wakeup (check for work) ││ SIGUSR2 │ Various │ Recovery-related ││ SIGALRM │ Self │ Statement timeout, lock timeout ││ ││ Postmaster signal handling: ││ • SIGTERM/SIGINT → Initiate shutdown sequence ││ • SIGCHLD → Child process died, check if restart needed ││ • SIGHUP → Signal all children to reload config ││ ││ Example: Graceful shutdown ││ ┌────────────────────────────────────────────────────────────────────────┐ ││ │ 1. Admin: pg_ctl stop -m smart │ ││ │ 2. Postmaster receives SIGTERM │ ││ │ 3. Postmaster sends SIGTERM to all backends │ ││ │ 4. Backends finish current transaction │ ││ │ 5. Backends exit │ ││ │ 6. Postmaster waits for all children │ ││ │ 7. Postmaster performs checkpoint │ ││ │ 8. Postmaster exits │ ││ └────────────────────────────────────────────────────────────────────────┘ ││ │└─────────────────────────────────────────────────────────────────────────────┘
-- View all PostgreSQL processes-- Why backend_type matters: it distinguishes client backends from system processes.-- If you see many 'client backend' entries in 'idle in transaction' state, those are-- holding snapshots open and preventing VACUUM from reclaiming dead tuples.SELECT pid, usename, application_name, client_addr, state, query, backend_typeFROM pg_stat_activity;-- Send signals to backendsSELECT pg_terminate_backend(pid); -- SIGTERM: finish current work, then exitSELECT pg_cancel_backend(pid); -- SIGINT: cancel current query only (backend stays)-- Reload configuration without restart-- Why this is safe: only certain parameters (e.g., work_mem, shared_buffers) require-- a full restart. Most GUCs (logging, autovacuum tuning) take effect via reload.SELECT pg_reload_conf(); -- SIGHUP to postmaster-- View postmaster start time -- useful for calculating uptimeSELECT pg_postmaster_start_time();-- Performance pitfall: each backend process consumes ~10MB of private memory plus-- any work_mem allocations. With max_connections=1000 and work_mem=256MB, a burst-- of complex queries could demand 256GB of RAM. Use connection pooling (PgBouncer)-- to keep active backends low, and set max_connections to the actual concurrency-- your hardware can sustain (typically 2-4x CPU cores for OLTP).
Everything in PostgreSQL is stored in 8KB pages (blocks).Real-world analogy: An 8KB page is like a fixed-size filing cabinet drawer. The drawer label on the front (page header) records the last time anything was filed here (pd_lsn) and a tamper-evident seal (pd_checksum). Inside, sticky tabs at the front (line pointers) point to individual folders (tuples) stacked from the back. New folders are added from the back toward the front, and new sticky tabs are added from the front toward the back. When the sticky tabs meet the folders, the drawer is full. This “grow toward each other” layout means PostgreSQL never needs to shift data around within a page just to insert a new row.
-- See system columns for any rowSELECT ctid, -- Physical location (page, offset) xmin, -- Transaction that created xmax, -- Transaction that deleted (0 if live) cmin, -- Command ID for insert cmax, -- Command ID for delete tableoid, -- OID of the table *FROM users WHERE id = 1;-- Result:-- ctid | (0,1) -- Page 0, tuple 1-- xmin | 12345 -- Created by txn 12345-- xmax | 0 -- Not deleted-- cmin | 0 -- First command in its txn-- cmax | 0
PostgreSQL’s MVCC allows readers and writers to work without blocking each other.Real-world analogy: MVCC works like Google Docs version history. When you edit a document, Google does not overwrite the old version — it creates a new version while keeping the old one accessible. Anyone who opened the document before your edit continues to see the version they started reading (their “snapshot”). Only people who open the document after your edit see the new version. PostgreSQL does the same thing at the row level: an UPDATE creates a new tuple version while the old one remains visible to transactions that started before the update. The old versions pile up like document revisions and must eventually be cleaned up by VACUUM — PostgreSQL’s equivalent of “delete old revision history.”
-- Simplified visibility logic (actual code is more complex)-- A tuple is visible if:-- 1. xmin is committed AND-- 2. xmin started before our snapshot AND-- 3. (xmax is 0 OR xmax is aborted OR xmax started after our snapshot)-- Check transaction statusSELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();-- View commit log statusSELECT pg_xact_status(xmin) as xmin_status, pg_xact_status(xmax) as xmax_statusFROM users WHERE id = 1;-- Returns: 'committed', 'aborted', 'in progress', or NULL
-- REPEATABLE READ takes a snapshot at first queryBEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;SELECT * FROM accounts WHERE id = 1; -- Snapshot taken HERE-- Returns balance = 1000-- Another transaction updates and commits...SELECT * FROM accounts WHERE id = 1;-- Still returns balance = 1000! (same snapshot)COMMIT;-- New transaction sees the updateSELECT * FROM accounts WHERE id = 1;-- Returns balance = 1200
Performance pitfall — the “silent bloat killer”: In a high-write OLTP system, dead tuples can accumulate faster than autovacuum cleans them up. The default autovacuum trigger is threshold + scale_factor * table_rows, which means a 100-million-row table must accumulate 20 million dead tuples (20%) before autovacuum fires. For large tables, set per-table autovacuum_vacuum_scale_factor = 0.01 (1%) to trigger sooner. Also watch for idle in transaction sessions — a single forgotten BEGIN without COMMIT prevents VACUUM from reclaiming any rows created after that transaction’s snapshot.
┌─────────────────────────────────────────────────────────────────────────────┐│ THE DEAD TUPLE PROBLEM │├─────────────────────────────────────────────────────────────────────────────┤│ ││ After many updates, pages fill with dead tuples: ││ ││ Page: ││ ┌──────────────────────────────────────────────────────────────────────┐ ││ │ [DEAD] [LIVE] [DEAD] [DEAD] [LIVE] [DEAD] [LIVE] [DEAD] [DEAD] │ ││ │ │ ││ │ 70% dead tuples = wasted space + slow scans! │ ││ └──────────────────────────────────────────────────────────────────────┘ ││ ││ Problems: ││ • Table bloat: 10GB of data uses 100GB on disk ││ • Slow sequential scans: must read dead tuples too ││ • Index bloat: indexes point to dead tuples ││ • Transaction ID wraparound risk ││ ││ After VACUUM: ││ ┌──────────────────────────────────────────────────────────────────────┐ ││ │ [FREE] [LIVE] [FREE] [FREE] [LIVE] [FREE] [LIVE] [FREE] [FREE] │ ││ │ │ ││ │ Space marked as free for reuse (but not returned to OS) │ ││ └──────────────────────────────────────────────────────────────────────┘ ││ ││ After VACUUM FULL (rewrites table): ││ ┌────────────────────────────────────────────────────────────────────┐ ││ │ [LIVE] [LIVE] [LIVE] │ ││ │ │ ││ │ Compacted! Space returned to OS. But table is locked during. │ ││ └────────────────────────────────────────────────────────────────────┘ ││ │└─────────────────────────────────────────────────────────────────────────────┘
-- View current settingsSELECT name, setting, unit, short_descFROM pg_settingsWHERE name LIKE '%autovacuum%';-- Per-table settings for high-traffic tablesALTER TABLE orders SET ( autovacuum_vacuum_threshold = 1000, -- Min dead tuples autovacuum_vacuum_scale_factor = 0.01, -- + 1% of table autovacuum_analyze_threshold = 500, autovacuum_analyze_scale_factor = 0.005, autovacuum_vacuum_cost_delay = 2 -- Less aggressive);-- Check if autovacuum is runningSELECT pid, datname, usename, state, query, backend_start, xact_startFROM pg_stat_activity WHERE query LIKE '%autovacuum%';
Real-world analogy: WAL is like the black box flight recorder on an airplane. Before the pilot (backend process) makes any maneuver (data modification), the action is recorded in the black box (WAL) first. If the plane crashes (server power loss), investigators can replay the black box recording to reconstruct exactly what happened and recover to a consistent state. The key insight is that writing sequentially to the black box is much faster than updating every instrument panel (random data page writes) in real time. PostgreSQL exploits this same principle: sequential WAL writes are 10-100x faster than random data file writes, so committing a transaction only requires flushing the WAL — the actual data pages can be written lazily later by the background writer.
-- View WAL settingsSHOW wal_level; -- minimal, replica, logical -- 'replica' is needed for streaming replication + PITR -- 'logical' adds row-level change decoding (CDC)SHOW max_wal_size; -- Maximum WAL before forced checkpoint (default: 1GB) -- Setting this too low causes frequent checkpoints (I/O spikes). -- Setting it too high means longer crash recovery times.SHOW min_wal_size; -- Minimum WAL to keep (default: 80MB)SHOW wal_buffers; -- WAL buffer size (auto-tuned from shared_buffers)-- View current WAL position -- the LSN (Log Sequence Number) is a monotonically-- increasing pointer into the WAL stream. Two LSNs can be subtracted to calculate-- bytes of WAL generated between them.SELECT pg_current_wal_lsn();-- View WAL file info -- wal_fpi (full page images) count is important: after a-- checkpoint, the first write to each page must include the entire 8KB page in WAL-- (not just the diff). High wal_fpi means your checkpoint interval is too short.SELECT * FROM pg_stat_wal;-- Force a checkpoint (flushes all data to disk)-- Performance pitfall: calling CHECKPOINT manually during peak traffic causes a-- massive I/O storm. Let PostgreSQL manage checkpoints automatically via-- checkpoint_timeout and max_wal_size.CHECKPOINT;-- View checkpoint stats -- if checkpoints_req >> checkpoints_timed, your-- max_wal_size is too small and WAL is filling up between scheduled checkpoints.SELECT * FROM pg_stat_bgwriter;
-- Buffer cache hit ratioSELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) as cache_hit_ratioFROM pg_statio_user_tables;-- Should be > 0.99 for well-tuned system-- Per-table buffer statsSELECT relname, heap_blks_read, -- Blocks read from disk heap_blks_hit, -- Blocks found in cache round(100.0 * heap_blks_hit / nullif(heap_blks_hit + heap_blks_read, 0), 2) as hit_ratioFROM pg_statio_user_tablesORDER BY heap_blks_read DESCLIMIT 20;-- What's in the buffer cache? (requires pg_buffercache extension)CREATE EXTENSION pg_buffercache;SELECT c.relname, count(*) AS buffers, pg_size_pretty(count(*) * 8192) AS size_in_cacheFROM pg_buffercache bJOIN pg_class c ON b.relfilenode = c.relfilenodeWHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())GROUP BY c.relnameORDER BY count(*) DESCLIMIT 20;
-- Setup: Create a test scenarioCREATE TABLE visibility_test (id SERIAL PRIMARY KEY, value TEXT);-- Session 1BEGIN;INSERT INTO visibility_test (value) VALUES ('first');-- Note the xmin-- Session 2 (before Session 1 commits)SELECT ctid, xmin, xmax, * FROM visibility_test;-- What do you see?-- Session 1COMMIT;-- Session 2SELECT ctid, xmin, xmax, * FROM visibility_test;-- What changed?
-- Create and bloat a tableCREATE TABLE bloat_test ASSELECT generate_series(1, 100000) AS id, 'data' AS value;-- Update every row multiple timesUPDATE bloat_test SET value = 'updated1';UPDATE bloat_test SET value = 'updated2';UPDATE bloat_test SET value = 'updated3';-- Check bloatSELECT pg_size_pretty(pg_table_size('bloat_test')) AS table_size, n_live_tup, n_dead_tup, last_vacuumFROM pg_stat_user_tables WHERE relname = 'bloat_test';-- Run vacuum and compareVACUUM bloat_test;-- Check sizes again
Explain how PostgreSQL's MVCC implementation differs from MySQL/InnoDB's approach and the operational consequences of each.
Strong Answer:
PostgreSQL stores old row versions directly in the main heap. When a row is updated, the old version remains with xmax set and a new version is appended. This requires VACUUM to reclaim space. The benefit is consistent read performance — no undo chain traversal to reconstruct old versions.
InnoDB stores only the latest version in the clustered index. Old versions go to a separate undo log. Reads of old snapshots require reverse-applying undo records, which becomes expensive for long-running transactions. But the main table stays compact without a VACUUM equivalent.
PostgreSQL consequence: table bloat is a first-class operational concern. Write amplification is higher because every UPDATE creates a full new tuple (mitigated by HOT updates when no indexed column changes). InnoDB consequence: undo log can grow large under long transactions, and “history list length” is the monitoring metric to watch.
Follow-up: What is a HOT update and when does it fail?HOT (Heap-Only Tuple) allows an UPDATE to skip all index maintenance if no indexed column changes and the new tuple fits on the same page. The old line pointer is redirected. It fails when an indexed column is modified, no free space exists on the same page, or fillfactor is set too high. Monitor with pg_stat_user_tables.n_tup_hot_upd vs n_tup_upd.
Walk through what happens inside PostgreSQL when you execute a SELECT, from SQL text arrival to result delivery.
Strong Answer:
Parsing: Lexer (scan.l) tokenizes SQL, parser (gram.y) builds a raw parse tree — pure syntax, no semantic meaning.
Analysis: Analyzer resolves names against system catalogs (pg_class, pg_attribute, pg_proc), producing a type-checked Query tree.
Rewriting: View expansion and rule application. Most queries pass through unchanged.
Planning: The planner generates execution paths, estimates costs using pg_statistic, considers join orders and index usage, and selects the cheapest Plan tree.
Execution: The executor uses a demand-driven pull model. Top node requests tuples from children recursively down to scan nodes reading from tables/indexes via the buffer manager.
Result delivery: Tuples are serialized into the PostgreSQL wire protocol and streamed to the client over TCP.
Follow-up: Where does the buffer pool come into play during execution?When a scan node needs a page, it calls the buffer manager which checks the buffer mapping hash table. Buffer hits return a pinned pointer immediately. Buffer misses trigger clock-sweep eviction, a disk read (possibly satisfied by OS page cache), and loading into shared_buffers. The Buffers: shared hit=X read=Y in EXPLAIN directly reflects this.
Why does PostgreSQL use a multi-process architecture instead of threads? What are the implications for modern workloads?
Strong Answer:
PostgreSQL predates POSIX threads standardization. The fork()-based model gives each connection its own OS process with isolated address space, communicating via shared memory.
Key advantage: a segfault in one backend does not crash the server — postmaster restarts just that process. Shared memory is explicitly managed, simplifying concurrency reasoning.
Key cost: 5-10MB private memory per backend process. 1000 connections means 5-10GB overhead. Context switching is more expensive than threads. Connection pooling (PgBouncer) is mandatory for high-connection workloads.
Active community work on AIO patches and background worker threading. A full thread migration would require making all global variables thread-safe across 1.3M lines of C.
Follow-up: How does this compare to MySQL’s threading model?MySQL uses one thread per connection within a single process. Lower per-connection overhead means MySQL handles thousands of connections natively. The tradeoff: a thread crash can corrupt the entire process memory. In practice, connection pooling neutralizes the difference — both architectures operate with limited active workers regardless of external connection count.
