9

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.

Improve this question
6
  • 2
    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
    – user1822
    Commented Apr 25, 2016 at 16:18
  • 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)?
    – user3000172
    Commented Apr 25, 2016 at 18:12
  • 2
    That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables
    – user1822
    Commented Apr 25, 2016 at 19:31
  • 2
    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.
    – Ezequiel Tolnay
    Commented Apr 26, 2016 at 2:22
  • 2
    If you have OIDs, recreate your table using WITHOUT OIDS.
    – ngreen
    Commented Jun 14, 2019 at 4:45

2 Answers 2

Reset to default
0

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.

Improve this answer
-1

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.

Improve this answer
1
  • 2
    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.)
    – ngreen
    Commented Jun 14, 2019 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.