--- title: "The Real Cost of a Single Insert in Postgres" published: 2026-09-11T16:23:58.000-04:00 updated: 2026-09-11T16:23:58.000-04:00 excerpt: "PostgreSQL write amplification explained: why indexes multiply WAL volume, how to measure it on your tables, and four ways to reduce it without schema changes." tags: PostgreSQL, PostgreSQL Performance authors: NanoHertz Communications --- > **TimescaleDB is now Tiger Data.** A 40-byte sensor reading does not cost 40 bytes to store. On a table with three indexes, Postgres writes four separate WAL records for that one row, and the log grows by roughly 345 bytes before the commit returns. That gap is called [write amplification](https://www.tigerdata.com/blog/write-amplification-in-postgres-the-3-4x-tax-on-every-insert): the ratio of physical bytes the database writes to logical bytes your application sent. Every index you add pushes it higher. If you have ever added an index to fix a slow dashboard and watched p95 insert latency climb two weeks later, you have already paid this tax without measuring it. This post counts the bytes, gives you a probe that measures the real ratio on your own tables, and covers four ways to reduce it. Every number below was measured on PostgreSQL 16 with default settings. ## What You Will Learn - Why one logical insert triggers far more physical I/O than the row's raw size. - The three components of write amplification: heap tuple, index entries, and WAL. - How to measure your own write amplification with `pg_current_wal_lsn()` and `pg_stat_wal`. - Four ways to reduce it, ordered by risk. ## Why It Matters Write amplification is the mechanism underneath most ingest ceilings. It is why a workload that looks trivial on paper [saturates disk throughput](https://www.tigerdata.com/blog/when-continuous-ingestion-breaks-traditional-postgres) in production, and why read optimizations quietly tax your writes. It also compounds. [Understanding Postgres Performance Limits for Analytics on Live Data](https://www.tigerdata.com/blog/understanding-postgres-performance-limits-for-analytics-on-live-data) traces how MVCC overhead, index maintenance, and WAL volume feed each other on high-frequency time-series workloads: more indexes produce more WAL, more WAL causes replica lag, and bloat pressure drives you toward still more indexes. Amplification is the number that ties those loops together. ## Set Up the Test Table Everything below runs against this table. Create it in a scratch database: ```SQL CREATE TABLE device_metrics ( ts timestamptz NOT NULL, device_id bigint NOT NULL, metric text NOT NULL, value double precision ); CREATE INDEX idx_device_metrics_device_ts ON device_metrics (device_id, ts DESC); CREATE INDEX idx_device_metrics_ts ON device_metrics (ts DESC); CREATE INDEX idx_device_metrics_metric ON device_metrics (metric); ``` Three indexes, which is modest for a time-series table. The payload is about 40 bytes per row. Postgres never writes just the payload. ## Start With the Row Itself Every heap tuple carries a fixed 23-byte header holding transaction visibility fields: `t_xmin`, `t_xmax`, and five more. Those fields exist so Multi-Version Concurrency Control can decide which transactions can see the row. The header is padded to 24 bytes so data starts on an 8-byte boundary, and the page adds a 4-byte line pointer so the tuple can be located within its 8 KB page. You can see the header in the stored size. `pg_column_size(t.*)` reports the row as a composite datum, which includes that 24-byte header: ```SQL SELECT pg_column_size(t.*) AS stored_bytes, pg_column_size(t.*) - 24 AS payload_bytes FROM device_metrics t LIMIT 1; stored_bytes | payload_bytes --------------+--------------- 64 | 40 ``` The table must already contain at least one row or this returns nothing. Before any index or log entry, the 40-byte reading occupies 64 bytes as a tuple, and 68 once the page's line pointer is counted. That is 1.7x, and it buys visibility bookkeeping for a row that will never be updated. Append-only workloads pay [the full MVCC price](https://www.tigerdata.com/blog/mvcc-feature-youre-paying-for-but-not-using) on data they never touch again. ## Every Index Is a Second Insert Postgres has no partial index maintenance on write. Every index is updated by every insert, synchronously, inside the same transaction. A two-column `(device_id, ts)` B-tree entry costs roughly 32 bytes: an 8-byte index tuple header, 16 bytes of key, a 4-byte line pointer, and alignment. Three indexes means three B-tree descents from root to leaf and three leaf page modifications per row. The traversal matters as much as the bytes. On a monotonically increasing timestamp key, every insert lands on the same rightmost leaf page, which fills, splits, and can cascade toward the root. A high-cardinality index scatters those same inserts across the tree, so the pages you need are less likely to be in `shared_buffers`. Index count is a [permanent write tax](https://www.tigerdata.com/blog/why-adding-more-indexes-eventually-makes-things-worse), charged on every row, forever. An index serving one weekly report costs you 32 bytes and a tree descent on every insert for the life of the table. ## The WAL Record You Did Not Ask For Postgres writes durability before it writes data. Every heap insertion and every index insertion generates its own write-ahead log record, and those records hit disk before the commit returns. On the three-index table, that is four records per row, which the measurement below confirms exactly. Then there is `full_page_writes`. [The PostgreSQL documentation](https://www.postgresql.org/docs/current/wal-reliability.html) explains the mechanic plainly: "the first modification of a data page after each checkpoint results in logging the entire page content." After every checkpoint, the first touch of each heap page and each index leaf page writes a full page image into the log. WAL volume does not sit at a steady rate. It spikes after each checkpoint and settles between them. ## Try This Now: Measure Your Write Amplification Postgres exposes both the WAL position and a running record count, so you can measure exactly what a batch of inserts costs. Run this whole block in one `psql` session: ```SQL CHECKPOINT; DROP TABLE IF EXISTS wal_probe; CREATE TEMP TABLE wal_probe AS SELECT pg_current_wal_lsn() AS start_lsn, wal_records, wal_fpi FROM pg_stat_wal; INSERT INTO device_metrics (ts, device_id, metric, value) SELECT now() - (s || ' seconds')::interval, s % 500, 'temperature', random() * 100 FROM generate_series(1, 100000) AS s; SELECT pg_sleep(1); SELECT w.wal_records - p.wal_records AS wal_records, w.wal_fpi - p.wal_fpi AS full_page_images, pg_size_pretty( pg_wal_lsn_diff(pg_current_wal_lsn(), p.start_lsn) ) AS wal_written, pg_size_pretty((100000 * r.row_bytes)::numeric) AS logical_written, round( pg_wal_lsn_diff(pg_current_wal_lsn(), p.start_lsn) / (100000 * r.row_bytes), 2 ) AS write_amplification FROM pg_stat_wal w, wal_probe p, LATERAL (SELECT pg_column_size(t.*) - 24 AS row_bytes FROM device_metrics t LIMIT 1) r; ``` The row width is computed for you, so there is nothing to substitute. Three notes before you run it: `CHECKPOINT` requires superuser or the `pg_checkpoint` role, `pg_stat_wal` requires PostgreSQL 14 or later, and both counters are cluster-wide, so run this during a quiet window or the numbers will include everyone else's writes. On a 500,000-row table with three indexes, that 100,000-row batch produces: ```SQL -------------+------------------+-------------+-----------------+--------------------- 402099 | 1448 | 41 MB | 3906 kB | 10.74 ``` 402,099 records for 100,000 rows. That is four per row: one heap insert plus one for each of the three indexes, exactly as described above. 41 MB of log for 3.8 MB of readings. Now run the same block again without the leading `CHECKPOINT`: ```SQL wal_records | full_page_images | wal_written | logical_written | write_amplification -------------+------------------+-------------+-----------------+--------------------- 402286 | 50 | 33 MB | 3906 kB | 8.73 ``` Same record count, 8 MB less log. The `full_page_images` column explains the entire difference: 1,448 full pages in the first run against 50 in the second. Your production workload lives between those two numbers, and the spread tells you how much of your I/O budget checkpoint tuning can recover. Now drop all three indexes and run it once more: ```SQL wal_records | full_page_images | wal_written | logical_written | write_amplification -------------+------------------+-------------+-----------------+--------------------- 100045 | 20 | 9516 kB | 3906 kB | 2.44 ``` One record per row, and amplification falls from 8.73 to 2.44. Roughly 70% of the WAL on this table was index maintenance, not data. That is the price of three indexes, quantified on your own hardware. ## Multiply by Ingest Rate Steady state on the three-index table is roughly 345 bytes of WAL per 40-byte row. At 50,000 inserts per second that is 17 MB/s of log for 2 MB/s of readings, before autovacuum and before a single query runs. Each additional index adds roughly one more record and 80 more bytes per row, so a five-index table on the same workload lands near 25 MB/s. Provisioning more IOPS raises the ceiling but leaves the ratio untouched, which is why hardware upgrades on this workload tend to buy about six months. ## Four Ways to Bring It Down These are ordered by risk. Start at the top. **1\. Batch your writes with** [**`COPY`**](https://www.tigerdata.com/learn/testing-postgres-ingest-insert-vs-batch-insert-vs-copy)**.** Single-row `INSERT` statements generate one WAL record per tuple plus a commit record per statement. `COPY` packs many tuples into a single multi-insert WAL record, cutting record count; multi-row `INSERT` shares one commit, cutting per-commit fsync overhead.  No schema change, no durability tradeoff. **2\. Drop indexes that do not earn their keep.** The measurement above puts a number on this. Cross-reference your index list against [pg\_stat\_user\_indexes](https://www.postgresql.org/docs/current/monitoring-stats.html): any index with `idx_scan` near zero is charging a record and a tree descent on every row for no read benefit. Confirm the counter has been accumulating since your last `pg_stat_reset()` before you act on it. **3\. Turn on** [**`wal_compression`**](https://www.postgresql.org/docs/current/runtime-config-wal.html)**.** This compresses full-page images rather than the whole log, which targets exactly the 1,448-image spike measured in the first run. The tradeoff is CPU on the write path, so re-run the probe after enabling it. **4\. Raise `max_wal_size` and `checkpoint_timeout`.** Fewer checkpoints means fewer first-touch full-page writes. The tradeoff is real: longer crash recovery and a larger `pg_wal` directory. Move one setting at a time and re-run the probe. All four reduce the volume of writes. None removes the irreducible per-row cost: the 24-byte header and the row's own WAL bytes are properties of the storage engine. When amplification stays high after all four, you are looking at architecture rather than configuration. Batched columnar ingestion amortizes those headers across up to 1,000 rows per compressed segment and logs WAL at the segment level, which is how Tiger Data's [hybrid row-columnar engine](https://www.tigerdata.com/blog/hypercore-a-hybrid-row-storage-engine-for-real-time-analytics) drives write amplification toward 1:1 on sustained append workloads. The [architecture whitepaper](https://www.tigerdata.com/docs/about/latest/whitepaper) covers the write path in detail. ## Conclusion An insert is not a write. It is a heap tuple, a header, one B-tree descent per index, and a WAL record for each of them. Measure that ratio and you stop guessing about which optimization is worth making. Run the probe against your largest table during your next quiet window, then drop one index and run it again. If the number stays above 10x, [try it on Tiger Cloud](https://console.cloud.timescale.com/signup) and measure the same workload against columnar ingestion.