02 - Storage Internals

11 min read

Storage Internals

Storage internals explain why the same SQL can behave differently as relation files grow, WAL volume accumulates, pages lose free space, and update chains start leaving dead tuples behind. In PostgreSQL the important physical units are blocks, relation forks, WAL records, free-space tracking, visibility tracking, and heap-only tuple behavior. The live stoxx database is small enough to inspect directly, which makes it a useful baseline for turning those concepts into operational signals instead of leaving them at the level of abstract engine theory.

Core Model

The first storage question is not “how many rows are in the table?” but “what physical units and files does PostgreSQL use to represent it?” Blocks and relation forks answer that directly.

Blocks and relation forks

This subsection establishes the physical units that every later storage and performance discussion depends on.

Confirm the PostgreSQL block size

Use this query when translating storage guidance into physical units, reading relation-size output, or validating assumptions about page-level reasoning before moving deeper into buffer or I/O analysis. It is typically triggered by storage-baseline work or by an engineer coming from another engine who wants to confirm PostgreSQL’s physical grain. The query reads one server setting through current_setting(). It is read-only. Its purpose is to prove the current cluster’s block size rather than assuming the default.

FieldSourceTypeMeaning
block_size_bytescurrent_setting('block_size')integerPhysical block size used by the server.
block_size_prettypg_size_pretty()textHuman-readable rendering of the block size.

This query confirms the physical block size used by the current PostgreSQL cluster.

SELECT
    current_setting('block_size')::int AS block_size_bytes,
    pg_size_pretty(current_setting('block_size')::bigint) AS block_size_pretty;
block_size_bytesblock_size_pretty
81928192 bytes

This cluster uses the standard PostgreSQL 8 KB block size. That is the physical grain behind heap and index storage, just as 8 KB pages are the public baseline in SQL Server storage discussions.

Inspect the relation forks of a real silver table

Use this query when the goal is to connect one logical relation to its physical files, or when a storage discussion needs to explain why PostgreSQL tracks free space and visibility separately from the main heap. It is typically triggered by catalog forensics, relation-size review, or storage internals teaching. The query reads pg_class and relation-size functions. It is read-only. Its purpose is to show the live main, FSM, and VM fork footprint of one real table.

FieldSourceTypeMeaning
relation_nameoid::regclassregclassSchema-qualified relation name.
main_fork_pathpg_relation_filepath(oid)textRelative path of the main relation fork.
main_fork_sizepg_relation_size(oid, 'main')textHeap or index data fork size.
fsm_fork_sizepg_relation_size(oid, 'fsm')textFree space map fork size.
vm_fork_sizepg_relation_size(oid, 'vm')textVisibility map fork size.

This query inspects the current physical forks of silver.stoxxusa50_ohlcv.

SELECT
    oid::regclass AS relation_name,
    pg_relation_filepath(oid) AS main_fork_path,
    pg_size_pretty(pg_relation_size(oid, 'main')) AS main_fork_size,
    pg_size_pretty(pg_relation_size(oid, 'fsm')) AS fsm_fork_size,
    pg_size_pretty(pg_relation_size(oid, 'vm')) AS vm_fork_size
FROM pg_class
WHERE oid = 'silver.stoxxusa50_ohlcv'::regclass;
relation_namemain_fork_pathmain_fork_sizefsm_fork_sizevm_fork_size
silver.stoxxusa50_ohlcvbase/16384/248728000 kB24 kB8192 bytes

The main fork holds the actual heap rows. The FSM fork records where free space remains available, and the visibility map tracks which pages are all-visible or all-frozen for vacuum and index-only-scan purposes. That is why one logical table already spans multiple physical files even though it looks like one object in SQL.

Write-Ahead Logging And Temporary Work Files

In PostgreSQL the durability path is WAL, and the temporary-work path is temp files on disk. Those are the two runtime storage surfaces that matter most when writes or large queries start stressing the engine.

WAL position and cumulative pressure

The live outputs below establish the current cluster’s WAL position and the amount of WAL activity accumulated since the current statistics reset.

Inspect the current WAL position and durability switches

Use this query when confirming the current WAL mode of the cluster, before reasoning about replication readiness, or while teaching how PostgreSQL protects data pages with full-page images. It is typically triggered by storage-baseline review or WAL troubleshooting. The query reads current settings and the current WAL insert location. It is read-only. Its purpose is to surface the current durability posture in one row.

FieldSourceTypeMeaning
current_wal_lsnpg_current_wal_lsn()pg_lsnCurrent write-ahead log position.
wal_levelcurrent_setting('wal_level')textLevel of WAL detail produced by the server.
full_page_writescurrent_setting('full_page_writes')textWhether PostgreSQL writes full page images after checkpoints to protect against torn pages.
wal_compressioncurrent_setting('wal_compression')textWhether full-page images are compressed in WAL.

This query surfaces the current WAL position and the most important durability switches of the cluster.

SELECT
    pg_current_wal_lsn() AS current_wal_lsn,
    current_setting('wal_level') AS wal_level,
    current_setting('full_page_writes') AS full_page_writes,
    current_setting('wal_compression') AS wal_compression;
current_wal_lsnwal_levelfull_page_writeswal_compression
0/455F5E8replicaonoff

The cluster is in a normal durable posture: wal_level = replica keeps physical-replication and PITR features available, and full_page_writes = on protects against torn-page hazards after checkpoints. wal_compression = off means full-page images are not being compressed in the current baseline.

Measure cumulative WAL generation since the current statistics reset

Use this query when checking whether large writes are generating meaningful WAL volume, after a bulk load, or during capacity and replication review. It is typically triggered by performance analysis or by an operational note that needs concrete WAL counters rather than generic statements about logging cost. The query reads pg_stat_wal. It is read-only. Its purpose is to expose how much WAL the cluster has generated since the current statistics reset.

FieldSourceTypeMeaning
wal_recordspg_stat_wal.wal_recordsbigintNumber of WAL records generated.
wal_fpipg_stat_wal.wal_fpibigintNumber of full-page images written into WAL.
wal_bytespg_stat_wal.wal_bytestextHuman-readable volume of WAL written.
wal_buffers_fullpg_stat_wal.wal_buffers_fullbigintNumber of times WAL buffers filled before being written out.
stats_resetpg_stat_wal.stats_resettimestamptzTime when the WAL statistics were last reset.

This query reports cumulative WAL activity for the current cluster.

SELECT
    wal_records,
    wal_fpi,
    pg_size_pretty(wal_bytes) AS wal_bytes,
    wal_buffers_full,
    stats_reset
FROM pg_stat_wal;
wal_recordswal_fpiwal_byteswal_buffers_fullstats_reset
30796616444 MB11412026-04-18 20:37:33.679131+00

This baseline already proves that the cluster is not idle from a durability perspective. About 44 MB of WAL has been generated since the current stats reset. That is modest, but it is enough to make the point that even a small lab accumulates WAL quickly under migration, demo, and analysis work.

Temporary work files instead of tempdb

PostgreSQL does not centralize temporary workload into a tempdb database. Instead, it creates temp files when query operators spill or need on-disk intermediate state.

Inspect temp-file counters for the current database

Use this query when investigating sort or hash spills, or when trying to prove that a workload is exceeding memory and materializing temporary files on disk. It is typically triggered by slow-query review, spill diagnostics, or storage-baseline teaching. The query reads pg_stat_database. It is read-only. Its purpose is to show whether the current database has already produced temp files and how much data those files contained.

FieldSourceTypeMeaning
datnamepg_stat_database.datnamenameDatabase name.
temp_filespg_stat_database.temp_filesbigintNumber of temporary files created by this database.
temp_bytespg_stat_database.temp_bytestextHuman-readable volume written to temp files.
deadlockspg_stat_database.deadlocksbigintNumber of detected deadlocks.
checksum_failurespg_stat_database.checksum_failuresbigintNumber of checksum failures, when checksums are enabled.

This query shows the current temp-file footprint of the stoxx database.

SELECT
    datname,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_bytes,
    deadlocks,
    checksum_failures
FROM pg_stat_database
WHERE datname = 'stoxx';
datnametemp_filestemp_bytesdeadlockschecksum_failures
stoxx11888 kB0

Even this small lab has already created at least one temp file, totaling about 1888 kB. That is the PostgreSQL equivalent of saying “a query already spilled beyond memory and touched the temp-work path.” It is not inherently a problem, but it is a concrete signal that temp-file monitoring belongs in the operational toolkit.

Mutation Costs

The key PostgreSQL difference from in-place-update engines is that updates create new tuple versions. The question then becomes whether PostgreSQL can keep that new version on the same page as a HOT update or whether it has to pay more index and cleanup cost.

HOT updates, dead tuples, and fillfactor

The following demo creates a small table with fillfactor = 70, updates half the rows, and then reads the user-table statistics to see how many updates qualified as HOT.

Create and mutate a fillfactor-aware demo table

Use this demo when explaining why fillfactor exists and why PostgreSQL update cost depends on available page space. It is typically triggered by storage-internals teaching or by a workload that performs frequent updates on non-indexed columns. The statements create a reusable demo table in demo_stc, populate it, update half the rows, and then return the resulting row counts. The purpose is to establish a concrete update workload before reading the HOT counters.

FieldSourceTypeMeaning
total_rowsCOUNT(*)bigintNumber of rows present after the demo load.
updated_rowsCOUNT(*) FILTER (...)bigintNumber of rows carrying the widened updated payload.

This demo seeds a table with free space and then widens half the rows to create update churn.

DROP TABLE IF EXISTS demo_stc.storage_hot_demo;
 
CREATE TABLE demo_stc.storage_hot_demo
(
    id integer PRIMARY KEY,
    payload text
)
WITH (fillfactor = 70);
 
INSERT INTO demo_stc.storage_hot_demo
SELECT
    g,
    repeat('x', 40)
FROM generate_series(1, 200) AS g;
 
UPDATE demo_stc.storage_hot_demo
SET payload = repeat('y', 60)
WHERE id <= 100;
 
SELECT
    COUNT(*) AS total_rows,
    COUNT(*) FILTER (WHERE payload = repeat('y', 60)) AS updated_rows
FROM demo_stc.storage_hot_demo;
total_rowsupdated_rows
200100

The demo establishes exactly 200 rows and updates 100 of them. That is enough to produce visible tuple-version churn without creating a large or dangerous sandbox object.

Measure HOT and non-HOT updates after the mutation

Use this query immediately after the demo write workload, or after any real workload where the question is whether PostgreSQL managed to keep many updates HOT. It is typically triggered by bloat analysis, fillfactor review, or storage teaching. The query reads pg_stat_user_tables. It is read-only. Its purpose is to show how many inserted and updated tuples were recorded, how many of those updates were HOT, and how many dead tuples now exist.

FieldSourceTypeMeaning
schemanamepg_stat_user_tables.schemanamenameSchema that owns the table.
relnamepg_stat_user_tables.relnamenameTable name.
n_tup_inspg_stat_user_tables.n_tup_insbigintNumber of inserted tuples.
n_tup_updpg_stat_user_tables.n_tup_updbigintNumber of updated tuples.
n_tup_hot_updpg_stat_user_tables.n_tup_hot_updbigintNumber of HOT updates.
n_dead_tuppg_stat_user_tables.n_dead_tupbigintEstimated number of dead tuples waiting for cleanup.

This query reads the live HOT-update counters for the demo table after the mutation workload.

SELECT
    schemaname,
    relname,
    n_tup_ins,
    n_tup_upd,
    n_tup_hot_upd,
    n_dead_tup
FROM pg_stat_user_tables
WHERE schemaname = 'demo_stc'
  AND relname = 'storage_hot_demo';
schemanamerelnamen_tup_insn_tup_updn_tup_hot_updn_dead_tup
demo_stcstorage_hot_demo20010048100

Only 48 of the 100 updates qualified as HOT. That is the real lesson: a lower fillfactor improves the odds, but it does not guarantee that every update stays on-page. The remaining updates still created non-HOT row versions and left 100 dead tuples behind for later cleanup.

What this means operationally

  • Relation size alone is not the whole story. PostgreSQL also maintains FSM and visibility metadata in separate forks that matter for free-space reuse and vacuum behavior.
  • WAL is always part of the write path for normal durable tables. Large loads should be evaluated for WAL volume, not just row count.
  • Temp-file counters are the PostgreSQL equivalent of “temporary work touched disk”. They matter any time sorts or hashes exceed memory.
  • HOT updates are a probabilistic optimization shaped by fillfactor, page space, and indexed-column rules. They reduce update cost when they happen, but they do not remove the need to watch dead tuples and vacuum behavior.

Graph View

Backlinks