6

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.

  • 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 – a_horse_with_no_name Apr 25 '16 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 Apr 25 '16 at 18:12
  • That's very likely, yes. You can check that for yourself by looking at pg_statio_user_tables – a_horse_with_no_name Apr 25 '16 at 19:31
  • 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. – Ziggy Crueltyfree Zeitgeister Apr 26 '16 at 2:22
  • @ZiggyCrueltyfreeZeitgeister. Thanks for asking and the pointers. The keys are generated by a spatial hash function so that span the entirety of a 64 bit integer space but not continuous. I include value in the index to avoid a second lookup in the table for the value. In other words, the idea is to make the index self sufficient. My test shows that it is slightly better to store value, improves performance by 5% (although at significant more storage). Brin index sounds promising and I will check it out. – user3000172 Apr 26 '16 at 17:55
0

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.

  • 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 Jun 14 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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