In PostgreSQL, how do pgstattuple and pgstattuple_approx measure a table's bloat, and when would you choose one over the other?
answer
- an extension, not a view
- dead tuples plus free space
- full scan versus visibility map
- free space map fills the gaps
- pg_stat_scan_tables may call it
basics
~20 spgstattuple reads every page and returns exact live, dead and free bytes, so bloat is dead plus free space as a share of table_len. pgstattuple_approx skips all-visible pages via the visibility map and estimates the rest, far more cheaply.
solid answer
~50 sBoth are functions of the `pgstattuple` contrib extension, which must be created in the database; by default only superusers and members of `pg_stat_scan_tables` may call them. `pgstattuple('payroll_ledger')` scans the whole heap and returns exact `table_len`, live `tuple_len`/`tuple_percent`, `dead_tuple_len`/`dead_tuple_percent` and `free_space`/`free_percent`. Bloat is the part that is not live data: dead tuples vacuum has not removed yet plus free space it already reclaimed but the file still holds. `pgstattuple_approx` skips every page the visibility map marks all-visible, takes those pages' free space from the free space map and counts the rest as live, so on a mostly static table it reads a fraction of the pages (`scanned_percent`). Its dead-tuple figures are exact, its live and free figures estimated. Use the approximate one for routine checks on big tables, the exact one before deciding on a rewrite.
code
sql · 12 linesCREATE EXTENSION IF NOT EXISTS pgstattuple;
-- Cheap first look: skips all-visible pages
SELECT scanned_percent, approx_tuple_percent, dead_tuple_percent,
approx_free_percent, pg_size_pretty(table_len) AS heap_size
FROM pgstattuple_approx('payroll_ledger');
-- Exact figures before deciding on a rewrite
SELECT tuple_percent, dead_tuple_percent, free_percent,
pg_size_pretty(table_len) AS heap_size,
pg_size_pretty(table_len - tuple_len) AS not_live
FROM pgstattuple('payroll_ledger');go deeper
Recall that pgstattuple is an extension reporting live, dead and free space for a table, and that bloat is the share of the file holding no live rows.
Explain the trade: pgstattuple reads every page for exact numbers, while pgstattuple_approx skips all-visible pages using the visibility map and free space map and estimates the rest.
Show you can read the output: high dead space means vacuum is behind, high free space after vacuum marks a rewrite candidate, and measuring before and after proves the rewrite worked.
Treat measurement as policy: which tables get routine approximate checks, what free-space level justifies a rewrite, and who acts on it, rather than reacting to disk alerts.
## Why size and counters are not enough Bloat is the part of a table's files that holds no live data. Three cheap signals hint at it, and none of them measures it: - `pg_relation_size()` tells you how big the heap is, not how much of it is live. - The `n_dead_tup` counter in `pg_stat_user_tables` estimates dead tuples not yet vacuumed, but it says nothing about space vacuum already reclaimed that the file still holds. Right after a vacuum it is near zero even when half the file is empty. - Estimation queries that compare `pg_class.relpages` with an expected row width derived from `pg_stats` are fast, but they are guesses that drift on tables with wide, nullable or TOASTed columns. The `pgstattuple` contrib extension measures the pages themselves. It has to be created in the database first (`CREATE EXTENSION pgstattuple`), and by default only superusers and members of the predefined role **`pg_stat_scan_tables`** may execute its functions. ## pgstattuple: exact, at the price of a full scan `pgstattuple(regclass)` reads every page of the relation and classifies its bytes: | Column | Meaning | |---|---| | `table_len` | Physical size of the relation in bytes | | `tuple_count`, `tuple_len`, `tuple_percent` | Live tuples | | `dead_tuple_count`, `dead_tuple_len`, `dead_tuple_percent` | Dead tuples vacuum has not removed yet | | `free_space`, `free_percent` | Free space inside the pages | `table_len` is always a little more than the three parts added together, because page headers, line pointers and alignment padding belong to none of them. The function takes only an `ACCESS SHARE` lock, so it runs beside normal traffic, but the figures are accumulated page by page and are not an instantaneous snapshot. It supports heap tables only, not other table access methods, and it costs a read of the whole relation, which on a large table is real I/O. ## pgstattuple_approx: the cheap first look `pgstattuple_approx(regclass)` avoids most of that I/O: 1. For each page the **visibility map** marks all-visible, it assumes there are no dead tuples, takes the page's free space from the **free space map** and counts the rest of the page as live. 2. It reads only the remaining pages, counting tuples and free space on them exactly. 3. It extrapolates the live-tuple count the same way `VACUUM` estimates `pg_class.reltuples`. The output reports `scanned_percent` (how much it actually read), exact `dead_tuple_count`, `dead_tuple_len` and `dead_tuple_percent`, and estimated `approx_tuple_count`, `approx_tuple_len`, `approx_tuple_percent`, `approx_free_space` and `approx_free_percent`. On a mostly static table that is vacuumed regularly it reads a small fraction of the pages; on a table whose visibility map is mostly unset it degrades towards a full scan. ## Reading the numbers Take a payroll ledger whose heap doubled after a one-off backfill rewrote every row, and which autovacuum has since processed: | Pattern | What it means | Next step | |---|---|---| | High `dead_tuple_percent` | Vacuum has not caught up | Look at vacuum, not at a rewrite | | Low dead, high `free_percent` | Space reclaimed but still in the file | A rewrite candidate if new rows will not refill it soon | | Both low | Little bloat | Leave the table alone | The ledger shows the second pattern: roughly half the file is free. Plain vacuum has done all it can. Future entries will reuse that space, but if the ledger grows by a few percent a year that takes years, which is the case for rewriting the table with `VACUUM FULL`, `CLUSTER` or `pg_repack`. ## Practical notes - Use `pgstattuple_approx` for routine checks across many tables, and exact `pgstattuple` on the one table you are about to rewrite. - `pgstattuple` on a table measures its main heap only. TOAST data lives in a separate relation, found through `pg_class.reltoastrelid`, which you measure with the same function. - `pgstattuple_approx` accepts tables, materialized views and TOAST tables, not indexes; index density is measured with `pgstatindex`, a subject of index maintenance. - Measure before and after a rewrite: `table_len` falling while `tuple_len` stays the same is the proof that the rewrite paid off. - Do not expect a rewritten table to shrink to `tuple_len`. Page headers, line pointers and alignment padding always add overhead, and a table `fillfactor` below 100 leaves free space in every page on purpose, so a freshly rewritten table still shows some `free_percent`. - The exact function takes no stronger lock than a `SELECT`, but a full scan of a very large table competes with production for I/O, so run it off-peak and run it on the tables that the cheap signals have flagged, not on the whole database.
- On a PostgreSQL table just vacuumed, pgstattuple shows dead_tuple_percent near 0 and free_percent near 50; is the table bloated?In disk terms, yes: half the file holds no data. Vacuum has already turned the dead tuples into free space, which new rows in that table will reuse, but the file will not shrink by itself. Whether that matters depends on growth: if new inserts refill it within weeks, leave it; if not, plan a rewrite with `VACUUM FULL`, `CLUSTER` or `pg_repack`.
- How do you include TOAST data when measuring a PostgreSQL table's bloat with pgstattuple?Call it a second time on the TOAST relation: `SELECT * FROM pgstattuple((SELECT reltoastrelid FROM pg_class WHERE oid = 'payroll_ledger'::regclass)::regclass);`. The plain call measures only the main heap, so a table whose wide values are stored out of line can look healthy while its TOAST table carries most of the bloat.
- An n_dead_tup near zero proves the table has no bloat
- pgstattuple is a built-in system view that needs no extension
- pgstattuple locks the table exclusively, so it cannot run in production
- pgstattuple_approx samples rows like ANALYZE, so its dead-tuple counts are guesses
- free_percent is space already handed back to the operating system