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