I've been doing some digging into the size of our production PostgreSQL 9.6 database and found some results I thought were surprising.
We have a table (let's call it
foos) with about 10 million records. The primary key is an
integer. We have a B-tree index on this table for an optional foreign key into another table (let's call it
bars). Let's call the index
bars.id is just an
integer data type column.
When I look at the index size using the
\di+ meta command I see that it occupies somewhere in the neighborhood of 1GB of space. Some back-of-the-envelope math would imply that each entry in the index thus takes about 1GB / 10 million = 100 bytes of space per-row.
There's almost no deletion taking place on the
foos table, so the bloat is non-existent.
In my mind, an index would effectively contain something like sorted pairs of numbers mapping the indexed column to the primary key of the relevant table. However, since it's only
integer types, that would only use about 4 + 4 = 8 bytes per row, which is way off from the 100 bytes per row that's actually occupied. I guess the fact that it's a tree structure could bump that up slightly, but the over 10x difference was a bit of an eyebrow-raiser for me.
What accounts for all of this "extra" space being used by the index?