Postgresql with just index storage

Question

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.

• user1822: What you are asking for is known in Oracle as "index organized table" or "clustered index" in other DBMS. Postgres simply doesn't support that, there is no workaround
• user3000172: Thanks. If this table is never accessed (since my index is self contained with all necessary information) does it mean that the table is simply dead space (does not really affect performance, table pages are never loaded into page cache)?
• user1822: That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables
• Ezequiel Tolnay: Could you expand on how you use the table and why? If you're concerned with unnecessary use of space, or performance, perhaps there are better ways than the table with that format and an index. For example, if your lookups are by key, why do you include value in the index? Also, if you use PostgreSQL 9.5 and your key field is sequential in the table's natural order, you could use a BRIN index. You could, also for example, create your table dropping the last two digits of key, and store the values as an array of 100 doubles.
• ngreen: If you have OIDs, recreate your table using WITHOUT OIDS.

Answer 1

The way PostgreSQL stores data, it keeps transaction information in the heap table so it is impossible to store data just in a BTREE. The heap is necessary.

To have a high transaction rate, Postgres uses a form of transaction control called multi-version-concurrency-control (MVCC) for high throughput. MVCC requires storing extra information relating to transaction numbers with every row. This is stored in the heap (part of the reason for the 24-byte-per-row overhead in PG). When a row is updated, it actually gets a new spot in the heap (not updated in place). The way btrees are organized, they usually update-in-place, which is another reason it's hard to have btree-only tables.

Answer 2

If you need a key-value store, use a key-value store; Postgres is not it.

Answer 3

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.

• ngreen: Scanning a partition is going to be considerably slower than an index lookup, unless each partition has fewer than 128 entries. (Assuming 8KB page size.)