DB‎ > ‎Postgres‎ > ‎

PostgreSQL mearing index bloat

Thank you for visiting this page, this article has an updated version, see Measure PostgreSQL index bloat
For new tables, the size of the index will grow almost linerly with the size of the table. As for old table, it's not unusual for a bloated index to be significantly larger than the actual data in the table. How to check it?
First, you can monitor how bloated an index is, by watching the index size relative to the table size, which is easy to check with the following query:

select nspname,relname round(100 * pg_relation_size(indexrelid) / pg_relation_size(indrelid)) / 100 as index_ratio, pg_size_pretty(pg_relation_size(indexrelid)) as index_size, pg_size_pretty(pg_relation_size(indrelid)) as table_size from pg_index I left join pg_class C on (c.oid = i.indexrelid) left join pg_namespace N on (n.oid = c.relnamespace) where nspname not in


=> select nspname,relname, round(100 * pg_relation_size(indexrelid) / pg_relation_size(indrelid)) / 100 as index_ratio,
           pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
           pg_size_pretty(pg_relation_size(indrelid)) as table_size from pg_index I
           left join pg_class C on (c.oid = i.indexrelid)
           left join pg_namespace N on (n.oid = c.relnamespace) where nspname not in ('pg_catalog','information_schema','pg_toast') and c.relkind='i' and pg_relation_size(indrelid) >0;

 nspname |          relname          | index_ratio | index_size | table_size
---------+---------------------------+-------------+------------+------------
 public  | t_level_2_pkey            |        0.56 | 1737 MB    | 3076 MB
 public  | i_dirs_iparent            |        0.63 | 2888 MB    | 4527 MB
 public  | i_dirs_ipnfsid            |         0.7 | 3209 MB    | 4527 MB
 public  | t_access_latency_pkey     |        0.32 | 1711 MB    | 5263 MB
 public  | t_level_1_pkey            |        0.31 | 11 MB      | 36 MB
 public  | t_inodes_checksum_pkey    |        0.94 | 1702 MB    | 1802 MB
 public  | t_acl_pkey                |         0.7 | 2384 MB    | 3393 MB
 public  | t_locationinfo_trash_pkey |        1.25 | 159 MB     | 127 MB
 public  | t_retention_policy_pkey   |        1.02 | 1713 MB    | 1677 MB
 public  | t_storageinfo_pkey        |        0.66 | 156 MB     | 233 MB
 public  | t_tags_inodes_pkey        |        0.41 | 3736 kB    | 9040 kB
 public  | t_tags_pkey               |        0.83 | 1813 MB    | 2160 MB
 public  | i_t_acl_rs_id             |         0.6 | 2047 MB    | 3393 MB
 public  | i_locationinfo_ipnfsid    |        0.48 | 1971 MB    | 4023 MB
 public  | t_dirs_pkey               |        0.91 | 4150 MB    | 4527 MB
 public  | t_locationinfo_pkey       |        0.81 | 3270 MB    | 4023 MB
 public  | t_inodes_data_pkey        |           2 | 16 kB      | 8192 bytes
 public  | t_inodes_pkey             |        0.73 | 2498 MB    | 3414 MB

If you have initial ratio number when you started you db, or there is newly reloaded copy to compare with, you will clearly see if table index is bloated or not.


If you don't have that data available, it's possible to get a rough estimate of how much dead row bloat is in a table or an index by running some computations based on the size of various structures in the table and index, A full list of bloat checking utilities is available at http://wiki.postgresql.org/wiki/index_maintenance




Comments