What is it? #
Understanding roughly how PostgreSQL stores data makes several otherwise-mysterious behaviours obvious: why deleting rows does not free disk space, why a table can be mostly empty and still huge, and why an UPDATE writes more than you expect.
Everything lives in the data directory. Inside it, each table is one or more files, and each file is divided into fixed pages of 8 kilobytes. A page holds tuples — physical row versions.
Values too large for a page are moved aside into TOAST storage automatically.
In memory, shared buffers cache the pages being used. Changes are written first to the WAL, then applied to data files later at a checkpoint.
That path — memory, WAL, disk — is the heart of both performance and durability.
Think of it like this #
A warehouse of identical shelves, each holding exactly 8 kilograms.
A row is a box on a shelf. Updating a box does not modify it in place; a new box is placed on a shelf and the old one is marked "superseded". The old box still occupies space until a cleaner comes round — that cleaner is VACUUM.
A box too big for a shelf goes to an overflow room with a note pointing to it. That is TOAST.
And every change is written into a logbook by the door before the shelves are touched. If the lights go out mid-shift, the logbook is how the warehouse works out what it was doing. That is the WAL.
Simple example #
You delete a million rows from a table and check the disk usage. It has not gone down at all.
Nothing is broken. The rows are marked dead but the pages still hold them, and the space is only reused after VACUUM runs. That single fact explains most confusion about PostgreSQL disk usage.
Code #
-- ---------- The data directory ----------
SHOW data_directory; -- e.g. /var/lib/postgresql/16/main
-- Inside it:
-- base/ the databases (one subdirectory per database OID)
-- pg_wal/ the write-ahead log <- NEVER delete files here by hand
-- global/ cluster-wide catalogs
-- pg_tblspc/ tablespace symlinks
-- postgresql.conf, pg_hba.conf, postgresql.auto.conf
-- Where a specific table's file lives:
SELECT pg_relation_filepath('orders'); -- base/16384/24576---------- Pages and tuples ----------
A table file is a sequence of 8 KB PAGES:
+------------------------------------------+
| page header |
| item pointers -> -> -> |
| |
| (free space) |
| |
| [tuple 3][tuple 2][tuple 1] | <- tuples fill from the END
+------------------------------------------+
A TUPLE is one physical ROW VERSION, not one row.
An UPDATE creates a NEW tuple and marks the old one dead.
So one logical row may exist as several tuples until VACUUM removes the dead ones.-- ---------- Seeing row versions directly ----------
-- ctid is the physical location: (page number, item number within page)
SELECT ctid, xmin, xmax, id, status FROM orders WHERE id = 1;
-- (0,1) 1234 0 1 pending
UPDATE orders SET status = 'paid' WHERE id = 1;
SELECT ctid, xmin, xmax, id, status FROM orders WHERE id = 1;
-- (0,7) 1235 0 1 paid <- DIFFERENT ctid: a new physical location
-- xmin: the transaction that created this row version
-- xmax: the transaction that deleted/superseded it (0 means still live)
-- The old tuple at (0,1) is still on disk, now dead, awaiting VACUUM.-- ---------- Why disk space does not drop after DELETE ----------
SELECT pg_size_pretty(pg_total_relation_size('orders')); -- 2400 MB
DELETE FROM orders WHERE placed_at < '2020-01-01'; -- DELETE 4000000
SELECT pg_size_pretty(pg_total_relation_size('orders')); -- STILL 2400 MB
-- The rows are marked dead. The pages remain allocated to the table.
VACUUM orders; -- marks that space REUSABLE by this table...
SELECT pg_size_pretty(pg_total_relation_size('orders')); -- STILL 2400 MB
-- ...but does NOT return it to the operating system.
-- Only VACUUM FULL (or a rewrite) shrinks the file. See the VACUUM lesson.-- ---------- Measuring size properly ----------
SELECT
pg_size_pretty(pg_table_size('orders')) AS table_only,
pg_size_pretty(pg_indexes_size('orders')) AS indexes,
pg_size_pretty(pg_total_relation_size('orders')) AS table_plus_indexes;
-- Biggest objects in the database:
SELECT relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
SELECT pg_size_pretty(pg_database_size(current_database()));---------- TOAST: The Oversized-Attribute Storage Technique ----------
A tuple cannot span pages, so it must fit within 8 KB.
When a row grows beyond about 2 KB, PostgreSQL automatically:
1. COMPRESSES the large values, and if that is not enough,
2. MOVES them to a separate TOAST table, leaving a pointer behind.
This happens silently. You never manage it directly.
WHY IT MATTERS:
A text column holding an occasional 5 MB document does NOT slow down
queries that never SELECT that column — the data is not in the main
table's pages at all. This is a real argument for naming your columns
instead of using SELECT *.-- Find a table's TOAST table and its size:
SELECT c.relname AS table_name,
t.relname AS toast_table,
pg_size_pretty(pg_relation_size(t.oid)) AS toast_size
FROM pg_class c
JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relname = 'articles';---------- The write path: memory -> WAL -> disk ----------
Application
│ UPDATE orders SET status='paid' WHERE id=1;
▼
SHARED BUFFERS (PostgreSQL's page cache, in RAM)
│ the page is modified here first and marked "dirty"
│
├──▶ WAL BUFFER ──▶ pg_wal/ (flushed to disk on COMMIT)
│ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
│ THIS is what makes COMMIT durable
│
▼ ...later, at a CHECKPOINT...
DATA FILES on disk (base/...)
KEY POINT: COMMIT does NOT wait for the data files to be written.
It waits only for the WAL to be flushed. Writing a small sequential
log entry is far faster than writing scattered pages.
If the server crashes, the data files may be out of date — but the WAL
holds every committed change, so PostgreSQL replays it on startup.-- ---------- Cache effectiveness ----------
SELECT relname,
heap_blks_read AS disk_reads,
heap_blks_hit AS cache_hits,
round(100.0 * heap_blks_hit /
NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS cache_hit_pct
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;
-- A cache hit ratio below ~95% on a busy OLTP database usually means
-- shared_buffers is too small, or the working set genuinely exceeds RAM.-- ---------- Checkpoints ----------
-- A CHECKPOINT writes all dirty pages from shared buffers to the data files,
-- so WAL before that point is no longer needed for crash recovery.
CHECKPOINT; -- force one manually (usually only before maintenance)
SELECT * FROM pg_stat_bgwriter;
-- checkpoints_timed : triggered by checkpoint_timeout <- GOOD
-- checkpoints_req : triggered by max_wal_size filling <- too many means
-- max_wal_size is too small (see the configuration lesson)---------- Fill factor: leaving room for updates ----------
By default PostgreSQL packs pages completely full. If a row is then
updated, the new version usually will not fit on the same page and must
go elsewhere — which also requires updating every index.
Leaving free space allows a HOT update (Heap-Only Tuple): the new version
stays on the same page and the INDEXES DO NOT NEED UPDATING AT ALL.
ALTER TABLE orders SET (fillfactor = 85); -- leave 15% free per page
VACUUM FULL orders; -- !! rewrite to apply it
Worth doing only on tables updated very frequently. It costs disk space
in exchange for much cheaper updates.How it works #
PostgreSQL never modifies a row in place. An UPDATE writes a new tuple and marks the old one as superseded by recording the updating transaction in its xmax. This is the physical basis of MVCC: a transaction whose snapshot predates the change still finds the old version and reads it without waiting.
The cost is that dead tuples accumulate. Until VACUUM reclaims them, they occupy pages, get read from disk, and fill the cache — which is exactly why the next lesson exists.
It is also why DELETE does not free disk space. The rows are marked dead; the pages stay allocated to the table. A plain VACUUM makes that space reusable by the same table, but does not hand it back to the operating system. Only a rewrite such as VACUUM FULL shrinks the file.
A tuple cannot span pages, so anything approaching 8 KB must be dealt with. TOAST does this automatically: it compresses large values, and moves them to a side table with a pointer if compression is insufficient. The practical consequence is worth knowing — a large text or jsonb column costs nothing for queries that do not select it, because those bytes are not in the main table's pages. It is another reason to name columns rather than writing SELECT *.
The write path explains PostgreSQL's durability guarantee. A change is made in shared buffers and recorded in the WAL. On COMMIT, only the WAL is flushed to disk — a small, sequential write. The modified data pages are written later, at a checkpoint. This is why commits are fast despite guaranteeing durability: sequential log writes are far cheaper than scattered page writes, and the WAL contains everything needed to reconstruct the rest.
A checkpoint then writes all dirty pages to the data files, after which the preceding WAL is no longer needed for crash recovery. pg_stat_bgwriter distinguishes checkpoints triggered by time (healthy) from those forced by max_wal_size filling up (a sign the setting is too small).
Fill factor controls how full pages are packed. With free space available, an update whose new version fits on the same page can be a HOT update, which skips updating the indexes entirely — a significant saving on heavily updated tables.
Real-world use #
The most common real-world surprise is disk usage that does not fall after a large delete. Knowing that pages stay allocated turns a worrying incident into an expected one, and points at the right response: either let the table reuse the space naturally, or plan a rewrite during a maintenance window.
Monitor database and table sizes over time rather than checking them during incidents. Growth that is faster than your data genuinely growing is usually bloat, and the VACUUM lesson covers diagnosing it.
The cache hit ratio is a good top-level health indicator for an OLTP database. Consistently below about 95% suggests shared_buffers is too small or the working set no longer fits in RAM. Analytics workloads legitimately read more from disk, so interpret it in context.
Keep pg_wal on a filesystem with enough space and watch it. If pg_wal fills, PostgreSQL stops accepting writes — and the most common cause is an inactive replication slot retaining WAL indefinitely, which the replication lesson covers.
Never delete files from the data directory by hand. Removing WAL files, in particular, can make the database unrecoverable. Everything in there is managed by the server, and the supported ways to reclaim space are VACUUM, dropping objects, and archiving old data.
Fill factor is a targeted optimisation, not a general one. On a table updated constantly — a counters table, a session store — lowering it to 80 or 85 can meaningfully reduce write amplification. On an append-mostly table it simply wastes space.
Common mistakes #
- Expecting disk space to be freed by DELETE, when pages remain allocated until a rewrite.
- Deleting files from pg_wal or the data directory by hand, which can make the database unrecoverable.
- Using SELECT * on tables with large TOASTed columns, pulling megabytes you never needed.
- Ignoring a falling cache hit ratio until queries become noticeably slow.
- Letting pg_wal fill up, usually because of an inactive replication slot, which halts all writes.
Practice #
Create a table, insert a row, and record its ctid. Update the row and observe that the ctid changes, proving a new tuple was written. Then insert a few hundred thousand rows, measure the table size, delete most of them, and confirm the size does not drop. Run VACUUM and confirm it still does not drop. Finally, check your cache hit ratio with the pg_statio_user_tables query and note it as a baseline.