Search Postgresql Archives

Composite type storage overhead

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hey there,

I am currently exploring the options to utilize 128-bit numeric primary keys. One of the options I am looking at is to store them as composites of two 64-bit integers.

The documentation page on composite types does not tell too much about the internal storage, so I've made my own experiment:

CREATE TYPE entity_id AS
(​
    high bigint,​
    low bigint​
);

CREATE TABLE composite_test
(​
    entity_id entity_id NOT NULL,​
    CONSTRAINT composite_test_pkey PRIMARY KEY (entity_id)​
)

INSERT INTO composite_test (entity_id) VALUES (ROW(0, 0));

Now, as I am really interested in keeping the indexes compact, tried pageinspect to find out what's going on internally:

SELECT * FROM  bt_page_items(get_raw_page('composite_test_pkey', 1));

It seems wrapping the values into a composite type has a pretty significant storage overhead, as the index entry has a total size of 48 bytes, end the data look like this:

4b ff ff ff ff fa 40 00 00 ff ff ff ff 00 00 02 00 00 00 18 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

For comparison, when simply using a composite primary key of two columns, each index entry has a length of only 24 bytes - a 100% overhead from wrapping the values in a composite type.

Now, I understand there might be valid reasons to store a structure header alongside the plain data - e. g. to store version information so when the type is altered there is no need to rebuild the whole table.

However, I also think this should be highlighted in the documentation. (If it already is I apologise.)

Also, I would like ask if there is a way to instruct the storage engine to omit the housekeeping information and simply store the plain data, even if it comes with drawbacks.

I would highly appreciate any comments or additional information on this topic.

Best regards,
Tamas

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux