PostgreSQL 10 High Performance - Third Edition

Index bloat

PostgreSQL's default index type is the binary tree (B-tree). While a B-tree gives good index performance under most insertion orders, there are some deletion patterns that can cause large chunks of the index to be filled with empty entries. Indexes in that state are referred to as bloated. Scanning a bloated index takes significantly more memory, disk space, and potentially disk I/O than one that only includes live entries.

There are a few main sources for index bloat to be concerned about. The first involves deletion, and is concisely described by the documentation about routine re-indexing:

"Index pages that have become completely empty are reclaimed for re-use. There is still a possibility for inefficient use of space: if all ...