X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7CC66D1DF4D for ; Thu, 18 Mar 2004 00:08:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 31783-04 for ; Wed, 17 Mar 2004 20:08:02 -0400 (AST) Received: from commsecure.com.au (CommSecureAustPtyLtd.sb1.optus.net.au [203.202.148.106]) by svr1.postgresql.org (Postfix) with ESMTP id A9703D1C9E6 for ; Wed, 17 Mar 2004 20:07:57 -0400 (AST) Received: from [172.16.15.13] (ws12.commsecure.com.au [172.16.15.13]) by commsecure.com.au (8.11.6/8.11.6) with ESMTP id i2I07ZV31135; Thu, 18 Mar 2004 11:07:35 +1100 Subject: Re: PostgreSQL Disk Usage and Page Size From: Stephen Robert Norris To: Seum-Lim Gan Cc: pgsql-performance@postgresql.org, ahsaleh@lucent.com In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-StJCWEB3WcFRnftYXnNK" Organization: CommSecure Australia Pty Ltd Message-Id: <1079568455.12785.3.camel@ws12.commsecure.com.au> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 (1.4.5-7) Date: Thu, 18 Mar 2004 11:07:35 +1100 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/251 X-Sequence-Number: 6107 --=-StJCWEB3WcFRnftYXnNK Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Thu, 2004-03-18 at 10:52, Seum-Lim Gan wrote: > Hi all, >=20 > we have a question about the pagesize in PostgreSQL: >=20 > Using different pagesizes: 4K, 8K, 16K, 32K, when we store different=20 > record sizes > such as in the following example: >=20 > CREATE TABLE TEST_1 ( > F1 VARCHAR(10), > F2 VARCHAR(5) ); >=20 > CREATE TABLE TEST_2 ( > F1 VARCHAR(10), > F2 VARCHAR(10) ); >=20 > we're consistently having the following storage behavior: >=20 > 60 records / 4k_page > 120 records / 8k_page > 240 records / 16k_page > 480 records / 32k_page. >=20 > 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. >=20 > Any clues if there's any parameter or bug causing that? >=20 > 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 --=-StJCWEB3WcFRnftYXnNK Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQBAWOhG4hFS2REFUecRAr5pAJwND6QRtsl6XafSFxuUArFyN7N0TQCcCA6R 2fEwsUrzWoWUiarfFdU2SPI= =J1py -----END PGP SIGNATURE----- --=-StJCWEB3WcFRnftYXnNK--