Postgres Architecture

Snowflake for Developers/Postgres/Postgres Architecture

On this page

Postgres Architecture

Postgres

Elizabeth Christensen

Process Model

When you start Postgres, the first thing that runs is the postmaster process. It's the supervisor: it listens for incoming connections and starts and watches over everything else.

When a client connects, the postmaster forks a new backend process just for that connection. One process per connection. That backend parses, plans, and executes queries for that session, and it exits when the connection closes.

This is why connection pooling matters in production. If 500 application servers each open a connection, you get 500 backend processes eating memory and CPU, most of them sitting idle. Postgres doesn't ship a built-in pooler, so most people put PgBouncer (or a pooler provided by their platform) in front of it.

Beyond client backends, Postgres runs a set of background processes:

Process	Role
autovacuum launcher	Starts autovacuum workers to clean up dead tuples
checkpointer	Writes dirty buffers to disk at checkpoints
WAL writer	Flushes WAL buffers to disk
background writer	Writes some dirty pages ahead of time to smooth out checkpoint spikes
io worker	Performs asynchronous I/O on behalf of backends (Postgres 18+, when `io_method = worker`)
logical replication launcher / workers	Run logical replication subscriptions

If you've read older guides, you might be looking for a "stats collector." That process is gone as of Postgres 15. Cumulative statistics now live in shared memory.

Postgres 18 added asynchronous I/O. The io_method setting controls it: worker (the default) hands reads to a pool of I/O worker processes sized by io_workers, io_uring uses Linux's io_uring interface, and sync keeps the old synchronous behavior. Sequential scans, bitmap heap scans, and vacuum are the main beneficiaries so far.

You can see all of these processes 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
 12305 | io worker              |
 12299 | walwriter              |

Postgres tracks its own processes in system views, and you'll see that pattern all through this guide.

Snowflake Postgres runs and configures these processes for you, including memory settings like shared_buffers, WAL archiving, and backups. Knowing the architecture still pays off when you're reading EXPLAIN plans, chasing a slow query, or watching activity in Snowflake Postgres Insights.

Shared Memory and the Buffer Cache

All those backend processes share data through shared memory. The biggest piece of it is the buffer cache, sized by shared_buffers.

The buffer cache holds 8KB pages, the basic unit of storage in Postgres. When a query needs a row, Postgres first checks whether the page holding it is already in the buffer cache. If it is, no disk read. If not, it reads the page in (often from the operating system's page cache, which is why Postgres doesn't need all of RAM for itself).

A common starting point is shared_buffers at about 25% of 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 is 16,384 pages of 8KB each, so 128MB, which is the out-of-the-box default. On a production server with 64GB of RAM, you'd likely set it around 16GB.

Two background processes move data between the cache and disk. The background writer periodically finds "dirty" pages (modified but not yet written) and writes some of them out to smooth I/O over time. The checkpointer writes out all dirty pages at each checkpoint, which bounds how much WAL has to be replayed after a crash.

Here's how to check how well the cache is doing:

SELECT
  blks_hit,
  blks_read,
  round(blks_hit::numeric / nullif(blks_hit + blks_read, 0) * 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

For a transactional workload, you want this above 99%. If it drops noticeably, your working set has probably outgrown shared_buffers. Analytics workloads that scan big tables will naturally run lower.

MVCC: Multi-Version Concurrency Control

Postgres handles concurrency with MVCC: readers don't block writers and writers don't block readers. It gets there by keeping multiple versions of each row.

Every row has hidden system columns, including:

xmin, the ID of the transaction that created this row version
xmax, the ID of the transaction that deleted or updated this row version (or locked it), or 0 if none

You can select them 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 | 945110 |  4 | OldClient
 945102 |      0 |  5 | NewStartup

Row 4 has a non-zero xmax. If transaction 945110 committed a delete or update, that row version is dead to new snapshots, but it physically stays on disk until vacuum cleans it up. (A non-zero xmax can also just mean a row lock or a transaction that rolled back, so don't read too much into it on its own.)

When you UPDATE a row, Postgres doesn't change it in place. It sets the old version's xmax and inserts a new version with a new xmin. That's why updates create "dead tuples": old row versions no current transaction can see, still taking up space.

Check how many dead tuples have built up:

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  | 2026-10-15 03:22:14-05
 sessions     |    450123  |     34521  |     7.7  | 2026-10-15 02:45:01-05
 audit_log    |   8901234  |     12003  |     0.1  | 2026-10-14 22:10:33-05

This is why vacuum exists. It reclaims space from dead tuples so tables don't grow forever. Autovacuum takes care of it automatically, but knowing the mechanism helps when you're diagnosing bloat. The Vacuum guide goes much deeper.

Write-Ahead Logging and Storage

The Write-Ahead Log (WAL) is how Postgres guarantees durability. Every change is written to the WAL before it's written to the data files.

The reason: WAL writes are sequential (fast) and data file writes are random (slower). Logging changes sequentially first lets Postgres acknowledge a COMMIT as soon as the WAL record is flushed, write the actual data pages later in the background, and recover from a crash by replaying WAL from the last checkpoint.

Check the current WAL position:

SELECT
  pg_current_wal_lsn() AS current_lsn,
  round(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / (1024*1024*1024), 2) AS total_wal_gb;

 current_lsn | total_wal_gb
-------------+--------------
 3/A4F01C28  |        14.65

That's the total amount of WAL this cluster has ever generated, since the LSN only goes up.

WAL also powers streaming replication. Replicas receive and replay WAL records to stay in sync with the primary, using the same machinery as crash recovery. The WAL guide covers checkpoints, archiving, and point-in-time recovery.

Pages and TOAST

On disk, Postgres stores everything in 8KB pages, and a row can't span pages. So what happens with a big value? TOAST, The Oversized-Attribute Storage Technique.

TOAST kicks in automatically when a row gets wide, by default once it passes about 2KB. Postgres compresses large column values and, if they're still too big, moves them out to a separate TOAST table. You don't have to do anything.

Each column has a storage strategy:

Strategy	Behavior
PLAIN	No compression or out-of-line storage (used for fixed-length types)
EXTENDED	Compress first, then store out-of-line if still too big
EXTERNAL	Store out-of-line without compression
MAIN	Compress, and move out-of-line only as a last resort

Check the strategies for a table's 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
  AND NOT a.attisdropped
ORDER BY a.attnum;

  attname   |   typname   | toast_strategy
------------+-------------+-----------------
 id         | int8        | PLAIN
 title      | varchar     | EXTENDED
 body       | text        | EXTENDED
 metadata   | jsonb       | EXTENDED
 created_at | timestamptz | PLAIN

In practice: large text, jsonb, and bytea values get TOASTed automatically. If you SELECT * from a table with big columns you don't need, you're paying to fetch and decompress them from the TOAST table for nothing. List the columns you want.

The System Catalog

One of our favorite things about Postgres is that it describes itself with its own tables. The pg_catalog schema holds metadata for everything in your database (tables, columns, indexes, functions), and the statistics views cover settings, activity, and running queries. All of it is queryable with plain SQL.

Object hierarchy

Postgres organizes objects as Instance → Database → Schema → Objects. One Postgres instance (a "cluster" in Postgres terms) can host several databases. Each database has schemas, and each schema holds tables, views, functions, and other objects.

Every database has a public schema by default, and search_path controls which schemas Postgres checks when you use an unqualified table name:

SHOW search_path;

   search_path
-----------------
 "$user", public

Postgres looks first for a schema named after your user, 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 | 2026-10-15 10:22:01-05   | SELECT o.id, o.total FROM orders o JOIN...
 14891 | active | 2026-10-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

Lots of sequential scans on a large table usually means a missing index. On small tables, seq scans are fine and often faster.

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

Indexes with zero scans waste disk and slow down every write. Before dropping one, make sure it isn't backing a primary key or unique constraint, and check replicas too, since these stats are per server.

Configuration inspection:

SELECT name, setting, unit, source
FROM pg_settings
WHERE source != 'default'
  AND name NOT LIKE 'lc_%'
ORDER BY name;

This lists every setting that's been changed from its default, which is the fastest way we know to see how an instance has been tuned.

Conclusion

The process model, shared memory, MVCC, WAL, and the system catalog explain most of what you'll run into day to day: why you need a connection pooler, how to size memory, why tables bloat, and what an EXPLAIN plan is really telling you. When something looks off, start by querying the catalog. Postgres is already keeping track of itself in tables you know how to query.