Hello PostgreSQL developers,
I’m observing a storage behavior with arrays in a table that differs from
my expectations, and I’d appreciate your insights. I was to store key value
pairs in a very dense data model. I don't haver the requirement of search
so that's why I was thinking an array of a composite type would work well.
I can see that padding might be involved using the int4 and int8
combination but there is more overhead. Anyone know where the following it
coming from?
Context
I have defined a composite type and a table as follows:
sql
Copy code
CREATE TYPE property_entry_data_type AS (
property_key_id int4,
property_value_id int8
);
CREATE TABLE property_set_data
(
property_set_data_id int8 PRIMARY KEY,
parent_set_id int8 NULL,
owner_id uuid NOT NULL,
property_entry_data property_entry_data_type[] NULL,
created_at timestamptz DEFAULT now(),
modified_at timestamptz DEFAULT now()
);
Observations
I inserted 10,000 rows with varying numbers of elements in the
property_entry_data array. I noticed the following pattern:
- For a small number of elements (up to around 40), each array element
consumes roughly *40–50 bytes*.
- After reaching the *2 KB threshold*, PostgreSQL appears to start
compressing the array data within the main table.
- When the number of elements exceeds *220*, the data starts getting
stored in the TOAST table.
Expected Behavior
Based on my understanding, each element in the array should take *12-16
bytes* (4-8 bytes each for property_key_id depending on padding and
property_value_id). Including some additional overhead, I expected the
storage requirement to be roughly 24 + 12 * N bytes per row for N elements
in the array. However, the actual usage is significantly higher (~40–50
bytes per element), which I didn’t anticipate.
My Question
Could you please help me understand the following:
1. Why is PostgreSQL using *more than the expected 12 bytes per element*?
What factors contribute to the additional storage overhead?
2. How does PostgreSQL handle compression and alignment for arrays
stored in composite types, and could these factors explain the discrepancy?
3. Is there a way to *minimize the per-element overhead* for such arrays
in a table, or is this behavior expected given PostgreSQL’s internal
storage mechanisms?
This is the query I use to see the table size:
sql
Copy code
WITH table_info AS (
SELECT
n.nspname AS schema_name, -- Schema name
c.oid AS main_oid,
c.relname AS table_name,
c.relkind,
c.reltoastrelid AS toast_oid,
c.relispartition
FROM
pg_class c
JOIN
pg_namespace n ON n.oid = c.relnamespace -- Join to get
schema information
WHERE
c.relname = 'property_set_data' -- Replace with your table name
)
SELECT
ti.schema_name, -- Add schema to the output
ti.table_name,
CASE
WHEN ti.relispartition THEN 'Partitioned Table'
ELSE 'Regular Table'
END AS table_type,
pg_relation_size(ti.main_oid) AS main_table_size,
-- pg_size_pretty(pg_relation_size(ti.main_oid)) AS
main_table_size,
pg_size_pretty(pg_indexes_size(ti.main_oid)) AS indexes_size,
CASE
WHEN ti.toast_oid = 0 THEN 'No TOAST table'
ELSE pg_total_relation_size(ti.toast_oid)::text END AS toast_size,
--ELSE
pg_size_pretty(pg_total_relation_size(ti.toast_oid)) END
AS toast_size,
pg_size_pretty(pg_total_relation_size(ti.main_oid)) AS total_size
FROM
table_info ti;
Thank you for your assistance, and I appreciate any insights you can
provide!
