Thread: Unexpected table size usage for small composite arrays
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:
sqlCREATE 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:
- Why is PostgreSQL using more than the expected 12 bytes per element? What factors contribute to the additional storage overhead?
- How does PostgreSQL handle compression and alignment for arrays stored in composite types, and could these factors explain the discrepancy?
- 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:sqlWITH 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!
Erik Sjoblom <sjoblom65@gmail.com> writes: > 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? Composite values use the same 24-byte tuple headers as table rows do. So you'd be looking at 40 bytes per array element in this example. A large array of them would probably compress pretty well, but it's never going to be cheap. Can you store the int4's and int8's in two parallel arrays? regards, tom lane
Erik Sjoblom <sjoblom65@gmail.com> writes:
> 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?
Composite values use the same 24-byte tuple headers as table rows do.
So you'd be looking at 40 bytes per array element in this example.
A large array of them would probably compress pretty well, but
it's never going to be cheap.
Can you store the int4's and int8's in two parallel arrays?
regards, tom lane
Erik Sjoblom <sjoblom65@gmail.com> writes: > I don't see why it's using 50 bytes per element. There should be just one > 24 byte header for the array, not one per element [ shrug... ] I just told you that's not so. regards, tom lane
Row count | Array elements | Main table size | TOAST | Bytes / Row | Bytes / Entry |
---|---|---|---|---|---|
10000 | 0 | 933888 | 8192 | 93 | 0.0 |
10000 | 1 | 1417216 | 8192 | 142 | 49.0 |
10000 | 2 | 1826816 | 8192 | 183 | 45.0 |
10000 | 3 | 2220032 | 8192 | 222 | 43.0 |
10000 | 5 | 3039232 | 8192 | 304 | 42.2 |
10000 | 10 | 5120000 | 8192 | 512 | 41.9 |
10000 | 100 | 10240000 | 8192 | 1024 | 9.3 |
10000 | 200 | 20480000 | 8192 | 2048 | 9.8 |
10000 | 400 | 1015808 | 41467904 | 4248 | 10.4 |
10000 | 1000 | 1015808 | 87080960 | 8810 | 8.7 |
Erik Sjoblom <sjoblom65@gmail.com> writes:
> I don't see why it's using 50 bytes per element. There should be just one
> 24 byte header for the array, not one per element
[ shrug... ] I just told you that's not so.
regards, tom lane
Yes, I did expect that the first element should take 24+12 bytes and let's round that to 50 bytes.
I hear what you are saying Tom and what I have read says that it would take 24 + 12 x N bytes for the array.
On Tue, Oct 22, 2024 at 4:40 PM Erik Sjoblom <sjoblom65@gmail.com> wrote:I hear what you are saying Tom and what I have read says that it would take 24 + 12 x N bytes for the array.Whatever you are reading, or your interpretation of it, is flawed.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Tue, Oct 22, 2024 at 4:40 PM Erik Sjoblom <sjoblom65@gmail.com> wrote: >> I hear what you are saying Tom and what I have read says that it would >> take 24 + 12 x N bytes for the array. > Whatever you are reading, or your interpretation of it, is flawed. I wonder whether Erik is confusing the array's overhead (which by chance is also 24 bytes) with the composite-type overhead appearing within each array entry. In hopes of clarifying: in an array of composite, some though by no means all of the composite-type overhead fields will be the same in every entry. In principle we could squeeze those out and store them only once per array, but we don't. It'd require essentially duplicating a lot of the low-level array access code for this different sort of array, and some operations would get slower. Even simply fetching an element would get slower, since it'd have to reconstitute a valid composite-type value from two pieces. regards, tom lane
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tue, Oct 22, 2024 at 4:40 PM Erik Sjoblom <sjoblom65@gmail.com> wrote:
>> I hear what you are saying Tom and what I have read says that it would
>> take 24 + 12 x N bytes for the array.
> Whatever you are reading, or your interpretation of it, is flawed.
I wonder whether Erik is confusing the array's overhead (which
by chance is also 24 bytes) with the composite-type overhead
appearing within each array entry.
In hopes of clarifying: in an array of composite, some though by no
means all of the composite-type overhead fields will be the same in
every entry. In principle we could squeeze those out and store them
only once per array, but we don't. It'd require essentially
duplicating a lot of the low-level array access code for this
different sort of array, and some operations would get slower.
Even simply fetching an element would get slower, since it'd have
to reconstitute a valid composite-type value from two pieces.
regards, tom lane