I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.
The only query I perform on this table is an equality predicate on the key to fetch the corresponding value, which makes use of the B-tree.
The storage consumed by PostgreSQL is terrible here. It stores the OID, key, value in the table and stores key, value in the index. I am in essence storing everything twice!
How do I configure this table so that it is space efficient? Ideally, how can I store the tuple just once in the B-tree.
Best Answer
I think you should give a try for TimescaleDB. It is a Postgres extension for huge tables. Splitting table by key in 1000 sections gives you tens of millions records in each section. It will work fast without indexes at all.