Mastering PostgreSQL 9.6

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 do 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 those 10 million integer values.

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  --------+------------+-------+-------+----------+-------  public | idx_cos | index | hs ...