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:
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!
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
Thanks Tom for your response!
Yes, I did expect that the first element should take 24+12 bytes and let's
round that to 50 bytes.
If I store another element, I would expect another 12. (or 16 depending on
padding) and take say ~65 bytes. I'm seeing close to 100 bytes.
If I have 3 elements, it's using 150, 4 -> 200, etc all the way up
to around 40 elements as it seems to hit the 2KB limit and starts
compressing the data.
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
Anders
On Tue, Oct 22, 2024 at 6:34 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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 keyvalue
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 workwell.
I can see that padding might be involved using the int4 and int8
combination but there is more overhead. Anyone know where the followingit
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
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. This isn't the case when I start adding
elements to the table. Here are some samples adding 10,000 rows with
difference elements in the composite array:
Row countArray elementsMain table sizeTOASTBytes / RowBytes / 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
On Tue, Oct 22, 2024 at 6:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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
On Tue, Oct 22, 2024 at 3:46 PM Erik Sjoblom <sjoblom65@gmail.com> wrote:
Yes, I did expect that the first element should take 24+12 bytes and let's
round that to 50 bytes.
Assuming the 24 is coming from the array overhead you are expecting that
storing a custom composite typed value takes zero overhead. That is a
faulty assumption. A user created custom type always takes some overhead
because it is considered a variable structure, even in the case where all
of its fields are fixed-width. Furthermore, it is self-describing, and so
that description has to go somewhere. Therefore, there must be a non-zero
per-element overhead to store composite values within an array. As Tom
told you, the specific non-zero number is 24 bytes.
David J.
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 J.
On Tue, Oct 22, 2024 at 4:42 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
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.
Since you are posting to -hackers anyway (not that figuring this out from
the user-facing documentation seems easy if possible at all...) here is
what Tom is referring to.
David J.
"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
Thank you both for explaining this!
Tom, your statement sounds like a good solution: " In principle we could
squeeze those out and store them only once per array, but we don't."
On Tue, Oct 22, 2024 at 8:44 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
"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