Обсуждение: PostgreSQL Disk Usage and Page Size

Hi all,

we have a question about the pagesize in PostgreSQL:

Using different pagesizes: 4K, 8K, 16K, 32K, when we store different
record sizes
such as in the following example:

CREATE TABLE TEST_1 (
F1 VARCHAR(10),
F2 VARCHAR(5) );

CREATE TABLE TEST_2 (
F1 VARCHAR(10),
F2 VARCHAR(10) );

we're consistently having the following storage behavior:

60 records / 4k_page
120 records / 8k_page
240 records / 16k_page
480 records / 32k_page.

So it seems that it doesn't matter whether the record size is
15 bytes or 20 bytes, there's maximum number of records per page
as shown above.

Any clues if there's any parameter or bug causing that?

Gan (for Amgad)
--
+--------------------------------------------------------+
| Seum-Lim GAN                 email : slgan@lucent.com  |
| Lucent Technologies                                    |
| 2000 N. Naperville Road, 6B-403F  tel : (630)-713-6665 |
| Naperville, IL 60566, USA.        fax : (630)-713-7272 |
|       web : http://inuweb.ih.lucent.com/~slgan         |
+--------------------------------------------------------+

On Thu, 2004-03-18 at 10:52, Seum-Lim Gan wrote:
> Hi all,
>
> we have a question about the pagesize in PostgreSQL:
>
> Using different pagesizes: 4K, 8K, 16K, 32K, when we store different
> record sizes
> such as in the following example:
>
> CREATE TABLE TEST_1 (
> F1 VARCHAR(10),
> F2 VARCHAR(5) );
>
> CREATE TABLE TEST_2 (
> F1 VARCHAR(10),
> F2 VARCHAR(10) );
>
> we're consistently having the following storage behavior:
>
> 60 records / 4k_page
> 120 records / 8k_page
> 240 records / 16k_page
> 480 records / 32k_page.
>
> So it seems that it doesn't matter whether the record size is
> 15 bytes or 20 bytes, there's maximum number of records per page
> as shown above.
>
> Any clues if there's any parameter or bug causing that?
>
> Gan (for Amgad)

Well, you're size counts are completely wrong, for starters.

Each varchar uses 4 bytes + length of the string, so that's 8 more bytes
per row. Then you may have an OID as well for another 4 bytes. I'd also
not be surprised if the length of the string is rounded up to the
nearest word (although I don't know if it is), and I'd be amazed if the
length of the record isn't rounded to some boundary too.

There's a handy page in the documentation that talks about how to know
how big rows are, I suggest you start there...

    Stephen

On Wed, 17 Mar 2004, Seum-Lim Gan wrote:

> we have a question about the pagesize in PostgreSQL:
>
> Using different pagesizes: 4K, 8K, 16K, 32K, when we store different
> record sizes
> such as in the following example:
>
> CREATE TABLE TEST_1 (
> F1 VARCHAR(10),
> F2 VARCHAR(5) );
>
> CREATE TABLE TEST_2 (
> F1 VARCHAR(10),
> F2 VARCHAR(10) );
>
> we're consistently having the following storage behavior:
>
> 60 records / 4k_page
> 120 records / 8k_page
> 240 records / 16k_page
> 480 records / 32k_page.
>
> So it seems that it doesn't matter whether the record size is
> 15 bytes or 20 bytes, there's maximum number of records per page
> as shown above.

The rows aren't 15 or 20 bytes, they're something closer to:

row header (24 bytes?) + f1 length (4 bytes) + actual bytes for f1 +
f2 length (4 bytes) + actual bytes for f2
(I'm not sure about additional padding, but there's probably some to word
boundaries)

And since you're using varchar, you won't see an actual row size
difference unless you're using different data between the two tables.

If you're in a one byte encoding and putting in maximum length strings,
I'd expect something like 52 and 56 bytes for the above two tables.

On Thu, 18 Mar 2004, Saleh, Amgad H (Amgad) wrote:

> Stephan / Stephen
>
> We know about the overhead and do understand the math you've provided.
> This is not the question we're asking. We've just provided the table definitions as
> examples.
>
> The real question was, even with the 52 & 56 (assuming right),' I wouldn't get
> the same number of records per page for all 4k, 8k, 16k, and 32k pages.

On my system, I don't using your tests, IIRC I got 134 with TEST_1 and
like 128 or so on TEST_2 when I used strings of maximum length for the
columns.

>
> To make it more clear to you here's an example:
>
> For an 8k-page: we've got 120 records/page for both tables and other tables such as
>
>  CREATE TABLE TEST_3 (
>  F1 VARCHAR(10),
>  F2 VARCHAR(12) );

Are you storing the same data in all three tables or different data in all
three tables? That's important because there's no difference in length
between varchar(5) and varchar(12) when storing the same 5 character
string.

On Thu, 18 Mar 2004, Saleh, Amgad H (Amgad) wrote:

>
> Stephan:
>
> In each table we're storing the max. string length.
>
> For example:
>
> for TEST_1, we're storing 'abcdefghjk'  and 'lmnop'
> for TEST_2, we're storing 'abcdefghjk'  and 'lmnopqrstu'
> for TEST_3, we're storing 'abcdefghjk'  and 'lmnopqrstuvw'

Hmm, on my machine it seemed like I was getting slightly different row
count per page results for the first two cases. The last two aren't going
to be different due to padding if the machine pads to 4 byte boundaries.