Technologies/PostgreSQL/n_dead_tup

PostgreSQLMetric

n_dead_tup

Dead row estimate

Dimensions:None

Available on:

Datadog (1)Native (1)

Prometheus (1)

Interface Metrics (3)

Datadog

postgresql.toast.dead_rows

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

n_dead_tup

Estimated number of dead rows

Dimensions:None

Prometheus

stat_user_tables_n_dead_tup

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 (54)

Configuration Parameters (7)

autovacuum_vacuum_scale_factorrecommended: 0.1

VACUUM when 10% dead tuples to prevent bloat

autovacuum_analyze_scale_factorrecommended: 0.05

ANALYZE when 5% change for better query planning

autovacuum_max_workersrecommended: 4

more workers to handle vacuum load across tables

autovacuum_naptimerecommended: 30s

run vacuum checks more frequently

autovacuum_vacuum_cost_delayrecommended: 2

Delay between vacuum cost limit checks

autovacuum_vacuum_thresholdrecommended: 50

minimum dead tuples before vacuum triggers; keep at default for most cases

autovacuum_vacuum_cost_limit

Decrease to throttle autovacuum I/O operations

CLI Commands (23)

VACUUM ANALYZE table_name;remediation

SELECT schemaname, relname AS table_name, n_live_tup AS live_tuples, n_dead_tup AS dead_tuples, ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

diagnostic

ALTER TABLE events SET (autovacuum_vacuum_threshold = 1000, autovacuum_vacuum_scale_factor = 0.005, autovacuum_analyze_threshold = 500, autovacuum_analyze_scale_factor = 0.005, autovacuum_vacuum_cost_delay = 0, autovacuum_vacuum_cost_limit = 3000);

remediation

SELECT * FROM pg_replication_slots;diagnostic

SELECT pg_drop_replication_slot(slot_name);remediation

SELECT relname, n_live_tup, n_dead_tup, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_stat_user_tables WHERE schemaname = 'public';

diagnostic

VACUUM FULL table_name;remediation

VACUUMremediation

ANALYZEremediation

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

SELECT schemaname, relname AS table_name, n_dead_tup AS dead_tuples, n_live_tup AS live_tuples, last_autovacuum, last_autoanalyze, CASE WHEN n_live_tup > 0 THEN round(100.0 * n_dead_tup / n_live_tup, 1) ELSE 0 END AS dead_tuple_ratio FROM pg_stat_user_tables WHERE n_dead_tup > 500 ORDER BY n_dead_tup DESC LIMIT 15;

diagnostic

ALTER TABLE sim_events SET (autovacuum_vacuum_scale_factor = 0.02, autovacuum_vacuum_threshold = 500, autovacuum_vacuum_cost_delay = 2, autovacuum_vacuum_cost_limit = 1000);

remediation

SELECT relname, reloptions FROM pg_class WHERE relname = 'sim_events';diagnostic

SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT NULL AND relkind = 'r';diagnostic

gcloud sql instances patch MY_INSTANCE --database-flags=autovacuum_max_workers=6,autovacuum_vacuum_scale_factor=0.02,autovacuum_analyze_scale_factor=0.01,autovacuum_vacuum_cost_limit=2000,autovacuum_vacuum_cost_delay=0,autovacuum_naptime=15

remediation

SELECT relname AS table, n_dead_tup AS dead_rows FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;diagnostic

ALTER TABLE your_table SET (autovacuum_vacuum_threshold = 1000, autovacuum_vacuum_scale_factor = 0.05);remediation

VACUUM (VERBOSE, ANALYZE)remediation

SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%';diagnostic

VACUUM ANALYZE your_table_name;remediation

Technical References (24)

autovacuumcomponentdead tuplesconceptMVCCconceptbloatconceptpg_repackcomponentpg_stat_user_tablescomponentpgwatchcomponentVACUUMcomponentcatalog_xminconceptWAL (Write-Ahead Log)componentreplication slotcomponentAutovacuumcomponentVACUUM FULLcomponentn_dead_tupcomponentpg_stat_user_tables_n_dead_tupcomponenttable bloatconceptHOT updatesconceptXID wraparoundconceptpg_classcomponentPostgreSQL server logscomponentCloud SQL database flagscomponentautovacuum_vacuum_thresholdcomponentautovacuum_vacuum_scale_factorcomponenttransaction ID wraparoundconcept

Related Insights (22)

Table bloat from dead tuples degrades performance and wastes disk spacewarning

▸

High dead tuple ratio causes table bloat and slow querieswarning

▸

High dead tuple percentage causes query degradation and cache inefficiencywarning

▸

Index and table bloat wastes disk space and degrades query performancewarning

▸

Autovacuum falling behind on tables causes bloat accumulationwarning

▸

Inactive replication slots prevent autovacuum from removing dead tuples cluster-widecritical

▸

High-volume write workload causes I/O saturation when autovacuum runswarning

▸

Dead tuples accumulate massively when autovacuum is blocked from cleanupcritical

▸

Bloated tables with dead rows skew performance metrics and waste storagewarning

▸

Inefficient autovacuum process delays storage reclamationwarning

▸

Dead tuple accumulation causes table bloat and performance degradationwarning

▸

High dead tuple count triggers vacuum alertwarning

▸

Large tables miss autovacuum due to scale factor defaultswarning

▸

Table bloat from insufficient vacuuming requires VACUUM FULLwarning

▸

Autovacuum triggers too late on large tables causing bloat and query degradationwarning

▸

Autovacuum disabled on individual tables causes uncontrolled bloatcritical

▸

Vacuum duration increasing over time signals bloat accumulation between runswarning

▸

Autovacuum falls behind on high-write tables due to conservative defaultswarning

▸

Dead tuples exceed threshold causing table and index bloatwarning

▸

Default autovacuum thresholds insufficient for high-churn tableswarning

▸

High-churn table with millions of updates shows degraded SELECT performancecritical

▸

High autovacuum duration causes table bloat and performance degradationwarning

▸