PostgreSQL

This page is about one directory. I show you where PostgreSQL keeps every table, every index, and every log record. After it you will be able to point at a file for any table, read the WAL position, check replica lag, and tell whether dead row versions are piling up.

PostgreSQL: in one line

A relational database. Rows in tables. One data directory. Correctness first.

Good at: Money, orders, bookings. Anything with joins, constraints, and questions nobody has thought of yet. One log does both jobs. The WAL handles crash recovery and replication.

Runs as: one postmaster + a backend process per connection

The architecture: processes, memory, disk

Read each column top to bottom. A request enters at the process layer. Memory serves it when it can. Disk makes it permanent. When something is slow or broken, it lives in exactly one of these three layers. Name the layer first. That is the first diagnostic step.

PostgreSQL: architecture

Request path

Processespostmaster + 1 backend per connection · checkpointer · bgwriter · WAL writer · autovacuum workers
→
Memoryshared_buffers (~25% of RAM) + per-operation work_mem, on top of the OS page cache
→
Disk: one data directorybase/ (table + index files) · pg_wal/ · pg_xact/ · global/pg_control
Who does what: Backends run queries. The checkpointer flushes dirty pages. The WAL writer fsyncs the log. Autovacuum removes dead row versions. One process dying is survivable. The postmaster restarts it.

Every log file a DBA needs to know

These are the exact names. They sit in the locations from the file trees above. Each log has one job. Each log has one thing to check during an incident. The deep dive on the five data logs is in The log files. This table adds the operational logs a DBA reads first: server, error, slow.

LogExact file nameWhere it sits (on-prem)What it doesWhat to check in it
PostgreSQL
Write-ahead log
pg_wal/000000010000000000000003pg_wal/ inside the data directoryEvery change, recorded before the data file changes. Commit means this log is fsynced. It also feeds replicas and point-in-time recovery.Total size (du -sh pg_wal) and archive_status/. A growing pile of .ready files means archiving is failing.
PostgreSQL
Server log
postgresql-18-main.log/var/log/postgresql/ (Debian) or log/ in the data directoryThe server's own diary. Checkpoints. Slow statements, if enabled. Errors. Crash recovery.Checkpoint frequency. "duration:" lines for slow statements. The first error at the incident's start time.

The parameters DBAs actually touch during outages

This is not the full configuration reference. It is the short list that comes up in real incidents. Each row states what the parameter does and the outage rule for it. Safe live lever means change it during the incident. Planned change means it needs a restart or a window. Do not improvise it mid-incident. Do not touch means changing it converts the incident into data loss or a longer outage.

PostgreSQL Outage parameters

ParameterWhat it doesDuring an outage
shared_buffersSize of the page cache. Start at about 25% of RAM.Planned change. It requires a restart. Never use it as the mid-incident lever. If the working set outgrew it, that is next week's change. Size it from pg_stat_bgwriter evidence.
work_memMemory per sort or hash operation. Per operation, not per connection. Total use is work_mem times operations times connections.Safe live lever, per session: SET work_mem='256MB' for the one reporting query that is spilling. A global raise mid-incident is an OOM risk at full concurrency.
max_connectionsHard cap on backend processes. Each one costs memory and bookkeeping.Do not raise to fix "pool exhausted" It admits more load into an already overloaded server. It needs a restart anyway. The fix is a pooler (PgBouncer) and killing idle-in-transaction sessions.
wal_levelHow much information the WAL carries: minimal, replica, or logical.Do not touch. It needs a restart. Replicas and logical subscribers are built on the current level. Changing it mid-incident breaks the copies you may be about to fail over to.
max_wal_sizeWAL volume that triggers a checkpoint. Default 1 GB.Safe live lever (reload, no restart) Use it when num_requested in pg_stat_checkpointer shows size-triggered checkpoint storms. Raise it and reload.
checkpoint_timeoutTime trigger for checkpoints. Default 5 min. Whichever fires first wins, size or time.Safe live lever (reload) alongside max_wal_size. Lengthening it spreads the flush I/O. The price is longer crash recovery.
autovacuum_*Naptime, max workers, and per-table vacuum and analyze scale factors. They set how aggressively dead tuples and stale stats are cleaned.Safe live lever for bloat: Raise workers, lower the scale factor on the one hot table (ALTER TABLE … SET). Never disable autovacuum to "help". That is how bloat and wraparound start.
-- check SHOW shared_buffers; SHOW work_mem; SHOW wal_level; SHOW max_wal_size; -- change the safe levers (reloadable, no restart) ALTER SYSTEM SET max_wal_size = '16GB'; ALTER SYSTEM SET checkpoint_timeout = '15min'; SELECT pg_reload_conf(); -- per-session + per-table levers SET work_mem = '256MB'; -- this session only ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
Files on disk · what ls actually shows

There is no hidden state. Every table, index, and log on this page is a file in one directory. One process owns it. You can inspect it with ls and du while the server runs. When disk fills in production, the failure is always a specific file class. It is never "the database" in the abstract. This tree tells you which file class in under a minute. Paths below are the common Linux defaults. The command under each tree asks the server for its own.

PostgreSQL: the data directory
$ ls /var/lib/postgresql/18/main
PG_VERSION        # "18": which version owns this dir
base/             # one subfolder per database
global/           # cluster-wide tables + pg_control,
                  # the file startup reads first
pg_wal/           # THE WAL: 16 MB segment files:
  000000010000000000000003
  000000010000000000000004
pg_xact/          # which transactions committed
pg_multixact/  pg_logical/  pg_stat/
postgresql.conf  pg_hba.conf
                  # (on Debian/Ubuntu the .conf files
                  # live in /etc/postgresql/18/main/)

$ ls base/16384/   # database oid 16384
16397             # ← table "users" is THIS file
16397_fsm  16397_vm # free-space + visibility maps
16405             # ← index "users_pkey": also a file
Find any table's file: SELECT pg_relation_filepath('users'); gives base/16384/16397. File names are numbers (relfilenodes), not table names. The catalog maps them.
The log files · one by one

Every durability, replication, and recovery guarantee on this page reduces to one of five logs. For each log I give you the exact file name, where it sits, the mechanism, the failure mode when it is undersized, misplaced, or allowed to fill, and the command that shows its current state. Hold this pattern. A log's size divided by the write rate is a time window. Every operational budget on this page is one of those windows: recovery time, replica downtime tolerance, checkpoint headroom.

PostgreSQL

Write-Ahead Log (WAL)

pg_wal/000000010000000000000003
Where it sits
The pg_wal/ folder inside the data directory. A chain of segment files, 16 MB each by default, with hex names: timeline plus log plus segment.
What it does
Every change is written here before the data file is allowed to change. That is the "write-ahead". COMMIT only returns after the WAL record is forced to disk (fsync). The same stream feeds replicas and, when archived, point-in-time recovery.
What breaks without it
No crash safety at all. A power cut could leave half-written pages and silently lost commits. No streaming replication either. Replicas have nothing to replay.
Look at it
$ ls $PGDATA/pg_wal $ pg_waldump pg_wal/000000010000000000000003 -- in psql: SELECT pg_current_wal_lsn();

The other logs: MySQL · InnoDB: redo log and binary log · MongoDB · WiredTiger: journal and oplog · the pattern behind all five, and where they break

See the real thing: PostgreSQL 14 Internals by Egor Rogov, free PDF. The best single source on how Postgres actually works, and the book behind half the claims on this page.

See it yourself · copy-paste commands

Open a real database and check every claim on this page

Every mechanism above is observable on a running server in under two minutes. These commands are grouped by the question they answer. Where are the files. Where is the log position. How far behind are the copies. Is cleanup keeping up. Run them against any install. The output either confirms the model on this page or shows you the specific place your system deviates from it.

PostgreSQL · psql + shell
-- where is everything?
SHOW data_directory;
SELECT pg_relation_filepath('users');
-- → base/16384/16397

-- the WAL: where are we writing?
SELECT pg_current_wal_lsn();
$ ls $PGDATA/pg_wal
$ pg_waldump $PGDATA/pg_wal/000000010000000000000003

-- replicas and their lag
SELECT client_addr, state, replay_lag
  FROM pg_stat_replication;

-- dead row versions piling up?
SELECT relname, n_dead_tup
  FROM pg_stat_user_tables;

Try it live: a real Postgres, in your browser

No install, no account. This panel runs a real PostgreSQL in your browser. It starts with the same users table as the Home walkthrough, ids 10, 25, and 87. Run the insert, then the select, then the EXPLAIN, and watch the same numbers come out of a real plan.

Live Postgres is not started yet. Press the button below.

Nothing run yet.

One incident I keep coming back to

The app slowed down over days. The file grew to 5 times its size. Live row counts did not move.

I checked the oldest open transaction first. One long transaction was stopping VACUUM, so dead tuples piled up and the heap file bloated.

The fix was the transaction, not the space. Kill it or fix the code path, set idle_in_transaction_session_timeout, and run reports on a replica.

Real versions of this story: Uber hit the same write amplification at scale, and Notion hit the same VACUUM stall. Both are on the Stories page. How to host Postgres on-prem, on EC2, in Kubernetes, or managed is on the Cloud page.

Before you leave, check this

One command. It tells you whether cleanup is keeping up.

SELECT relname, n_dead_tup FROM pg_stat_user_tables;

If n_dead_tup keeps climbing on a hot table, VACUUM is not keeping up.