Database Development

Is there a way to decrease the size of some indexes by trading off some precision?

I have a table that has a number of 32-bit int IDs that are used as foreign keys.

The actual cardinality is under a million unique values.

I want to reduce the size of these indexes so that they can all fit in a certain amount of RAM.

Is there a way I can tell postgres to not bother indexing every single value, and perhaps index only the lower 16 bits?

With a bit of testing, I noticed that a 16-bit index would be about 32% smaller.

I know I could create some expression indexes and modify my queries, but is there a way to do this transparently?

I already looked into BLOOM and BRIN indexes but didn’t find a solution.

Leave a Reply

Your email address will not be published. Required fields are marked *