Mastering PostgreSQL 12 - Third Edition

Reducing space consumption

Indexing is nice, and its main purpose is to speed up things as much as possible. As with all good stuff, indexing comes with a price tag: space consumption. To work its magic, an index has to store values in an organized fashion. If your table contains 10 million integer values, the index belonging to the table will logically contain these 10 million integer values, plus additional overhead.

A B-tree will contain a pointer to each row in the table, and so it is certainly not free of charge. To figure out how much space an index will need, you can ask psql using the \di+ command:

test=# \di+ 
                         List of relations  Schema | Name       | Type  | Owner | Table    | Size --------+------------+-------+-------+----------+-------