How to Check Dead Tuples in PostgreSQL: Simple Guide

To check dead tuples in PostgreSQL, use the pg_stat_user_tables system view which shows the count of dead tuples in each table under the n_dead_tup column. You can run a query like SELECT relname, n_dead_tup FROM pg_stat_user_tables; to see dead tuples per table.

Syntax

The main way to check dead tuples is by querying the pg_stat_user_tables system view. It contains statistics about user tables including live and dead tuples.

• relname: The name of the table.
• n_dead_tup: The number of dead tuples (rows marked for deletion but not yet cleaned).

sql

SELECT relname, n_dead_tup FROM pg_stat_user_tables;

Example

This example shows how to list all user tables with their dead tuple counts. It helps identify tables that may need vacuuming to reclaim space.

sql

SELECT relname AS table_name, n_dead_tup AS dead_tuples
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Output

 table_name | dead_tuples 
------------+-------------
 customers  |         123
 orders     |          45
 products   |          10
(3 rows)

Common Pitfalls

One common mistake is expecting n_dead_tup to update immediately after deletes. It only updates after autovacuum or manual vacuum runs.

Also, querying pg_stat_user_tables requires appropriate permissions.

Running VACUUM or ANALYZE regularly helps keep these stats accurate and the database healthy.

sql

/* Wrong: expecting immediate update after DELETE */
DELETE FROM customers WHERE id = 10;
SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'customers';

/* Right: run vacuum to update stats */
VACUUM ANALYZE customers;
SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'customers';

Quick Reference

Command/Query	Purpose
SELECT relname, n_dead_tup FROM pg_stat_user_tables;	Check dead tuples per table
VACUUM [table];	Clean dead tuples and reclaim space
ANALYZE [table];	Update statistics for query planner
SELECT * FROM pg_stat_all_tables;	View stats for all tables including system ones

Key Takeaways

Use pg_stat_user_tables and check the n_dead_tup column to find dead tuples.

Dead tuple counts update only after autovacuum or manual vacuum runs.

Regularly run VACUUM to clean dead tuples and keep stats accurate.

High dead tuples can slow queries and waste disk space.

Check permissions if you cannot access pg_stat_user_tables.