Technologies/PostgreSQL/postgresql.dead_rows
PostgreSQLPostgreSQLMetric

postgresql.dead_rows

Estimated dead rows
Dimensions:None
Available on:DatadogDatadog (1)Native (1)PrometheusPrometheus (1)
Interface Metrics (3)
DatadogDatadog
Enabled with `relations`. The number of dead rows on the toast table of a relation. This metric is tagged with db, schema, table.
Dimensions:None
Native
Estimated number of dead rows
Dimensions:None
PrometheusPrometheus
Estimated number of dead rows
Dimensions:None
Sources
postgresql.toast.dead_rowsgithub.com
n_dead_tupgithub.com
stat_user_tables_n_dead_tupgithub.com

Technical Annotations (44)

Configuration Parameters (9)
autovacuum_vacuum_scale_factorrecommended: 0.05
controls when autovacuum runs; default 0.2 too high for large tables
autovacuum_analyze_scale_factorrecommended: 0.005
Fraction of table size that triggers auto-analyze
autovacuum_vacuum_cost_delayrecommended: 2
Delay between vacuum cost limit checks
autovacuum_max_workersrecommended: 5-6 for high-write workloads
Number of parallel autovacuum workers (default 3)
autovacuum_vacuum_thresholdrecommended: 1000
Combined with lower scale factor to prevent over-aggressive cleanup of small tables
idle_in_transaction_session_timeoutrecommended: 30min
prevents indefinite idle transactions that block autovacuum
autovacuum_analyze_thresholdrecommended: 50
updates statistics frequently for query planner
autovacuum_vacuum_cost_limitrecommended: 1000
enables faster autovacuum processing
track_countsrecommended: on
required for autovacuum daemon to function
CLI Commands (11)
SELECT relname AS table_name, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 15;diagnostic
ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005);remediation
VACUUM ANALYZE table_name;remediation
SELECT count(*) filter (where state = 'idle in transaction') as idle_in_transaction FROM pg_stat_activity;diagnostic
SELECT * FROM pg_stat_user_tables WHERE autovacuum_count > 0;monitoring
VACUUM citiesremediation
ALTER TABLE cities SET (autovacuum_vacuum_scale_factor = 0.01);remediation
SELECT schemaname, relname, n_dead_tup, n_live_tup, ROUND((n_dead_tup::float / NULLIF(n_live_tup + n_dead_tup, 0)) * 100, 2) AS dead_tuple_percent, last_autovacuum, autovacuum_count FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY n_dead_tup DESC;diagnostic
VACUUM FULLremediation
CLUSTERremediation
TRUNCATEremediation
Technical References (24)
dead tuplesconceptautovacuumcomponentpg_stat_user_tablescomponentn_dead_tupcomponentpg_stat_all_tablescomponentpg_class.relrowscomponentpgwatchcomponentVACUUMcomponentpg_stat_activitycomponenttable bloatconceptMVCCconceptautovacuum daemoncomponentvisibility mapcomponentheapcomponentxminconceptxmaxconceptpg_stat_user_tables_n_dead_tupcomponentautovacuum throttlingconceptvacuum_cost_page_hitcomponentvacuum_cost_page_misscomponentvacuum_cost_page_dirtycomponentautovacuum workerscomponentpg_stat_all_tables.n_dead_tupcomponentpgstattuplecomponent
Related Insights (16)
High dead tuple percentage degrades query performance and bloats tableswarning
▸
High dead tuple ratio causes table bloat and slow querieswarning
▸
High autovacuum scale factor causes 200GB dead row accumulation on 1TB tableswarning
▸
Index and table bloat wastes disk space and degrades query performancewarning
▸
Idle transactions prevent autovacuum and cause table bloatwarning
▸
Table bloat from suboptimal autovacuum settings slows query performancewarning
▸
Autovacuum lag causes table bloat and performance degradationwarning
▸
Autovacuum daemon fails without statistics collection enabledcritical
▸
Index-only scans fail when visibility map has dead rows/pageswarning
▸
Dead tuples accumulation indicates insufficient VACUUM frequencywarning
▸
Dead tuple accumulation causes table bloat and performance degradationwarning
▸
High dead tuple count triggers vacuum alertwarning
▸
Table bloat from insufficient vacuuming requires VACUUM FULLwarning
▸
Pre-PG14 autovacuum throttling limits cleanup to 4-40 MB/s causing backlogwarning
▸
Increasing autovacuum_max_workers without raising cost_limit does not increase cleanup throughputinfo
▸
MVCC dead rows consume disk space and degrade query performancewarning
▸