How to detect chained and migrated rows in PostgreSQL?

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:
  1. 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.
  1. 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.
  1. Using pg_class_stats: You can query the pg_class_stats function to get the statistics of table including number of chained rows.
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.
About Shiv Iyer 437 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.