Skip to main content
View rawEdit

09. Single table analysis

Deep dive into one table's statistics, growth, and maintenance history. Use this after identifying a problem table in the aggregated view.

When to use​

  • Checkup reports bloat for a specific table
  • Verifying that vacuum is effective on a high-write table
  • Evaluating whether a table needs partitioning, archiving, or repack
  • Tuning fillfactor or autovacuum settings for a specific table

Key panels​

  • Table size over time — total size including TOAST and indexes
  • Dead tuples over time — dead tuple count and ratio to live tuples
  • Vacuum and autovacuum history — when vacuums ran and their effectiveness
  • Sequential vs index scans — access patterns for this table
  • Insert/update/delete rates — DML activity over time
  • HOT update ratio — percentage of updates that are HOT (no index update needed)
  • Bloat estimate — estimated wasted space from table bloat

What good looks like​

  • Dead tuple ratio stays low after each vacuum cycle
  • Vacuum runs regularly (every few minutes for high-write tables)
  • Size growth is consistent with the application's write pattern
  • HOT update ratio is high for frequently-updated tables

What to investigate​

SignalNext step
Dead tuples rising continuouslyCheck if long-running transactions are blocking cleanup; verify autovacuum settings
Vacuum runs but dead tuples don't decreaseA transaction or replication slot may be holding back the xmin horizon
Low HOT update ratioConsider reducing fillfactor (e.g., to 70-80) to leave space for HOT updates
Table growing despite stable row countTable bloat — consider VACUUM FULL or pg_repack

Fillfactor guidance​

The default fillfactor is 100 (pages are filled completely). For tables with frequent updates, reducing fillfactor (e.g., to 70 or 80) leaves room on each page for HOT updates, which avoids index updates and reduces bloat:

ALTER TABLE my_table SET (fillfactor = 80);
-- Then VACUUM FULL or pg_repack to apply the new fillfactor
  • F001 — autovacuum current settings (CLI)
  • F004 — heap bloat estimated (monitoring stack)