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.
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 connectionRead 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.
Request path
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.
| Log | Exact file name | Where it sits (on-prem) | What it does | What to check in it |
|---|---|---|---|---|
| PostgreSQL Write-ahead log | pg_wal/000000010000000000000003 | pg_wal/ inside the data directory | Every 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 directory | The 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. |
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.
| Parameter | What it does | During an outage |
|---|---|---|
| shared_buffers | Size 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_mem | Memory 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_connections | Hard 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_level | How 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_size | WAL 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_timeout | Time 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. |
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.
$ 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
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.
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.
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.
-- 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;
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.
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.
One command. It tells you whether cleanup is keeping up.
If n_dead_tup keeps climbing on a hot table, VACUUM is not keeping up.