Thread: 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
Attachment
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.