How to detect chained and migrated rows in PostgreSQL?

In PostgreSQL, a chained or migrated row is a row that is too large to fit in a single block of a table. When this happens, the row is split across multiple blocks, which is known as a "chained" row, or it may be moved to another location in the table, which is known as a "migrated" row. Here are some ways to detect chained and migrated rows in PostgreSQL:

Using pg_stat_all_tables: You can query the pg_stat_all_tables view to get statistics about table-level statistics, including the number of live and dead rows, and the number of chained and migrated rows.

SELECT relname, n_live_tup, n_dead_tup, n_tup_hot_upd, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd + n_tup_ins + n_tup_upd + n_tup_del as total_rows,
       n_tup_moved
FROM pg_stat_all_tables;

SELECT relname, n_live_tup, n_dead_tup, n_tup_hot_upd, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd + n_tup_ins + n_tup_upd + n_tup_del as total_rows,

n_tup_moved

FROM pg_stat_all_tables;

Using pgstattuple: You can use the pgstattuple contrib module to get a detailed report of the table statistics, including the number of chained and migrated rows.

SELECT tablename, n_live_tup, n_dead_tup, n_tup_hot_upd, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd + n_tup_ins + n_tup_upd + n_tup_del as total_rows,
       n_tup_moved
FROM pgstattuple('mytable');

SELECT tablename, n_live_tup, n_dead_tup, n_tup_hot_upd, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd + n_tup_ins + n_tup_upd + n_tup_del as total_rows,

n_tup_moved

FROM pgstattuple('mytable');

Using pg_class_stats: You can query the pg_class_stats function to get the statistics of table including number of chained rows.

SELECT relname, heap_blks_hit, heap_blks_read, idx_blks_hit, idx_blks_read, idx_scan,n_tup_ins, n_tup_upd, n_tup_del,
       heap_blks_hit + heap_blks_read as total_blocks,
       idx_blks_hit + idx_blks_read as total_index_blocks,
       heap_blks_hit + heap_blks_read + idx_blks_hit + idx_blks_read as total_blocks_read,
       idx_scan + heap_blks_hit + heap_blks_read + idx_blks_hit + idx_blks_read as total_access,
       n_tup_hot_upd, n_tup_moved
FROM pg_class_stats('mytable');

SELECT relname, heap_blks_hit, heap_blks_read, idx_blks_hit, idx_blks_read, idx_scan,n_tup_ins, n_tup_upd, n_tup_del,

heap_blks_hit + heap_blks_read as total_blocks,

idx_blks_hit + idx_blks_read as total_index_blocks,

heap_blks_hit + heap_blks_read + idx_blks_hit + idx_blks_read as total_blocks_read,

idx_scan + heap_blks_hit + heap_blks_read + idx_blks_hit + idx_blks_read as total_access,

n_tup_hot_upd, n_tup_moved

FROM pg_class_stats('mytable');

It is important to detect and address chained and migrated rows, because they can cause poor performance, slow queries, and increased disk I/O. The best way to solve this problem is to increase the fillfactor of the table, add more indexes to the table, or reduce the size of the table.

The WebScale Database Infrastructure Operations Experts in PostgreSQL, MySQL, MariaDB, MongoDB and ClickHouse

Enterprise-class 24*7 Consultative Support and Managed Services for PostgreSQL, MySQL, MariaDB, MongoDB and ClickHouse

How to detect chained and migrated rows in PostgreSQL?

How to detect chained and migrated rows in PostgreSQL?