Re: Unexpected table size usage for small composite arrays - Mailing list pgsql-hackers

From Erik Sjoblom
Subject Re: Unexpected table size usage for small composite arrays
Date
Msg-id CAAW=00UjRF8oE-A3BgzNYD_Tj+VLe0v0Hr1oyxhcbg8aXq1=Pg@mail.gmail.com
Whole thread Raw
In response to Re: Unexpected table size usage for small composite arrays  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Unexpected table size usage for small composite arrays
Re: Unexpected table size usage for small composite arrays
List pgsql-hackers
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:
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

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Unexpected table size usage for small composite arrays
Next
From: Tom Lane
Date:
Subject: Re: Unexpected table size usage for small composite arrays