Postgres Architecture
Process Model
When you start a Postgres instance, the first thing that runs is the postmaster process. This is the supervisor — it listens on a TCP port for incoming connections and orchestrates everything else.
When a client connects, the postmaster forks a new backend process dedicated to that connection. This is a one-process-per-connection model. That backend handles parsing, planning, and executing queries for that single session, and it dies when the connection closes.
This architecture is why connection pooling matters in production. If you have 500 application servers each opening a connection, you have 500 backend processes consuming memory and CPU. Tools like PgBouncer or built-in connection pooling exist specifically to manage this.
Beyond client backends, Postgres runs several background worker processes:
| Process | Role |
|---|---|
| autovacuum launcher | Spawns autovacuum workers to reclaim dead tuples |
| checkpointer | Writes dirty buffers to disk at checkpoint intervals |
| WAL writer | Flushes WAL buffers to disk |
| background writer | Gradually writes dirty pages to reduce checkpoint spikes |
| stats collector | Gathers activity statistics |
| logical replication workers | Handle logical replication slots |
You can see all of these live:
SELECT pid, backend_type, state FROM pg_stat_activity ORDER BY backend_type;
pid | backend_type | state -------+------------------------+-------- 12301 | autovacuum launcher | 12298 | background writer | 12297 | checkpointer | 12303 | client backend | active 12304 | client backend | idle 12299 | walwriter |
Each of these processes shows up because Postgres tracks them in its own system views — a theme you'll see throughout this guide.
aside positive Snowflake Postgres manages these processes for you — autovacuum tuning, shared_buffers sizing, WAL management, and checkpointing are all handled automatically. You still benefit from understanding the architecture when reading EXPLAIN plans, diagnosing slow queries, or monitoring via Snowflake Postgres Insights.
Shared Memory and the Buffer Cache
All those backend processes need to share data, and they do it through shared memory. The largest chunk of shared memory is the buffer cache, controlled by the shared_buffers setting.
The buffer cache holds 8KB pages — the fundamental unit of storage in Postgres. When a query needs to read a row, Postgres first checks if the page containing that row is already in the buffer cache. If it is, great — no disk I/O needed. If not, it reads the page from disk into the cache.
A typical recommendation is to set shared_buffers to about 25% of available RAM:
SELECT name, setting, unit, short_desc FROM pg_settings WHERE name = 'shared_buffers';
name | setting | unit | short_desc ---------------+---------+------+-------------------------------------------- shared_buffers | 16384 | 8kB | Sets the number of shared memory buffers.
That 16384 value means 16384 pages of 8KB each = 128MB. On a production system with 64GB RAM, you'd likely set this to 16GB.
Two background processes manage the flow of data between cache and disk:
- Background writer — periodically scans for "dirty" pages (modified but not yet written to disk) and writes some of them out, smoothing I/O over time.
- Checkpointer — at intervals, forces ALL dirty pages to disk, creating a known-good recovery point.
You can monitor buffer cache effectiveness:
SELECT blks_hit, blks_read, round(blks_hit::numeric / (blks_hit + blks_read) * 100, 2) AS cache_hit_ratio FROM pg_stat_database WHERE datname = current_database();
blks_hit | blks_read | cache_hit_ratio -----------+-----------+----------------- 584729103 | 234891 | 99.96
A cache hit ratio above 99% means almost everything is being served from memory. If this drops significantly, your working set may be larger than shared_buffers.
MVCC: Multi-Version Concurrency Control
Postgres achieves high concurrency through MVCC — readers never block writers, and writers never block readers. It does this by keeping multiple versions of each row.
Every row in Postgres has hidden system columns:
- xmin — the transaction ID that created this row version
- xmax — the transaction ID that deleted (or updated) this row version (0 if still live)
You can see these directly:
SELECT xmin, xmax, id, name FROM customers LIMIT 5;
xmin | xmax | id | name --------+------+----+------------ 945021 | 0 | 1 | Acme Corp 945021 | 0 | 2 | Widgets Inc 945098 | 0 | 3 | DataCo 945098 | 9451 | 4 | OldClient 945102 | 0 | 5 | NewStartup
Row 4 has a non-zero xmax, meaning some transaction marked it for deletion. But the row still physically exists on disk until vacuum removes it.
When you UPDATE a row, Postgres doesn't modify it in place. It marks the old version's xmax and inserts a new version with a new xmin. This means updates create "dead tuples" — old row versions that are invisible to all current transactions but still occupy space.
Check how many dead tuples have accumulated:
SELECT relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) AS dead_pct, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC LIMIT 5;
relname | n_live_tup | n_dead_tup | dead_pct | last_autovacuum --------------+------------+------------+----------+------------------------- events | 2340891 | 89234 | 3.8 | 2025-01-15 03:22:14-05 sessions | 450123 | 34521 | 7.7 | 2025-01-15 02:45:01-05 audit_log | 8901234 | 12003 | 0.1 | 2025-01-14 22:10:33-05
This is why vacuum exists — it reclaims space from dead tuples so the table doesn't grow indefinitely. Autovacuum handles this automatically, but understanding the mechanism helps you diagnose bloat when it occurs.
Write-Ahead Logging and Storage
The Write-Ahead Log (WAL) is Postgres's durability guarantee. The rule is simple: every change must be written to the WAL before it's written to data files.
Why? WAL writes are sequential (fast), while data file writes are random (slow). By logging changes sequentially first, Postgres can:
- Acknowledge a COMMIT immediately after the WAL record is flushed
- Write the actual data pages to disk later, in the background
- Recover from crashes by replaying WAL from the last checkpoint
Check the current WAL position:
SELECT pg_current_wal_lsn() AS current_lsn, pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / (1024*1024*1024) AS total_wal_gb;
current_lsn | total_wal_gb -------------+-------------- 3/A4F01C28 | 14.65
WAL serves double duty — it's also the foundation of streaming replication. Replicas receive and replay WAL records to stay in sync with the primary, using the same crash-recovery mechanism.
Pages and TOAST
On disk, Postgres stores everything in 8KB pages. But what happens when a single row value exceeds what fits in a page? Enter TOAST — The Oversized Attribute Storage Technique.
TOAST kicks in automatically when a row would exceed the page size. It compresses and/or moves large column values to a separate TOAST table. This happens transparently — you don't need to do anything.
Each column has a TOAST strategy:
| Strategy | Behavior |
|---|---|
| PLAIN | No TOAST; value must fit in a page |
| EXTENDED | Compress first, then store out-of-line if still too big |
| EXTERNAL | Store out-of-line without compression |
| MAIN | Try compression only; avoid out-of-line if possible |
Check TOAST strategies for your columns:
SELECT a.attname, t.typname, CASE a.attstorage WHEN 'p' THEN 'PLAIN' WHEN 'x' THEN 'EXTENDED' WHEN 'e' THEN 'EXTERNAL' WHEN 'm' THEN 'MAIN' END AS toast_strategy FROM pg_attribute a JOIN pg_type t ON a.atttypid = t.oid WHERE a.attrelid = 'documents'::regclass AND a.attnum > 0 ORDER BY a.attnum;
attname | typname | toast_strategy ------------+---------+----------------- id | int8 | PLAIN title | varchar | EXTENDED body | text | EXTENDED metadata | jsonb | EXTENDED created_at | timestamptz | PLAIN
The practical takeaway: large text, jsonb, or bytea columns get TOASTed automatically. If you're doing SELECT * on tables with large columns you don't need, you're pulling data from TOAST tables unnecessarily — be specific with your column list.
The System Catalog
One of the most powerful things about Postgres is that it describes itself using its own relational tables. The pg_catalog schema contains every piece of metadata about your database — tables, columns, indexes, functions, settings, statistics, running queries — all queryable with standard SQL.
Object hierarchy
Postgres organizes objects in a hierarchy: Instance → Database → Schema → Objects. A single Postgres instance (cluster) can host multiple databases. Each database contains schemas, and each schema contains tables, views, functions, and other objects.
The public schema exists by default, and the search_path setting controls which schemas are checked when you reference an unqualified table name:
SHOW search_path;
search_path ----------------- "$user", public
This means Postgres first looks for a schema matching your username, then falls back to public.
Key catalog views for daily work
What's running right now:
SELECT pid, state, query_start, left(query, 60) AS query FROM pg_stat_activity WHERE state = 'active' AND pid != pg_backend_pid();
pid | state | query_start | query -------+--------+--------------------------+----------------------------- 14502 | active | 2025-01-15 10:22:01-05 | SELECT o.id, o.total FROM orders o JOIN... 14891 | active | 2025-01-15 10:22:03-05 | UPDATE inventory SET quantity = quanti...
Table statistics and health:
SELECT schemaname, relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 5;
schemaname | relname | seq_scan | idx_scan | n_tup_ins | n_tup_upd | n_tup_del ------------+-----------+----------+----------+-----------+-----------+----------- public | events | 45021 | 2340891 | 1203445 | 89201 | 12034 public | sessions | 12003 | 890234 | 450123 | 234521 | 190234 public | users | 3421 | 4502341 | 12034 | 89012 | 234
High seq_scan counts on large tables suggest missing indexes.
Index usage:
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 5;
indexrelname | idx_scan | idx_tup_read | idx_tup_fetch | size -------------------------+----------+--------------+---------------+--------- idx_events_legacy_type | 0 | 0 | 0 | 245 MB idx_orders_old_status | 12 | 48 | 48 | 128 MB idx_sessions_temp | 891 | 4201 | 4201 | 64 MB
Zero-scan indexes are wasting disk space and slowing down writes. Consider dropping them.
Configuration inspection:
SELECT name, setting, unit, source FROM pg_settings WHERE source != 'default' AND name NOT LIKE 'lc_%' ORDER BY name;
This shows every setting that's been changed from the default — useful for understanding how a Postgres instance has been tuned.
Conclusion
Understanding Postgres architecture — the process model, shared memory, MVCC, WAL, and the system catalog — gives you the mental model to make better decisions about connection management, memory tuning, vacuum configuration, and query optimization. These internals aren't academic curiosities; they show up every time you look at an EXPLAIN plan, investigate a performance issue, or wonder why a table is larger than expected.
The system catalog is your best friend for observability. Postgres keeps track of itself in the same relational tables you already know how to query — use that to your advantage.
Related Resources
Deep dive into PostgreSQL's internal architecture and processes.
Explore the system catalog and what Postgres tracks about itself.
How Postgres handles oversized data with TOAST storage.
Brian Pace explains how WAL file naming and sequence numbers work under the hood.
Brian Pace walks through WAL history files and timeline recovery scenarios.
How Snowflake manages Postgres infrastructure automatically.
This content is provided as is, and is not maintained on an ongoing basis. It may be out of date with current Snowflake instances
