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 620D3D1B560 for ; Thu, 18 Mar 2004 00:18:04 +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 36481-02 for ; Wed, 17 Mar 2004 20:18:04 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 3702ED1E90F for ; Wed, 17 Mar 2004 20:18:01 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 169993543E; Wed, 17 Mar 2004 16:18:02 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 14F883543D; Wed, 17 Mar 2004 16:18:02 -0800 (PST) Date: Wed, 17 Mar 2004 16:18:02 -0800 (PST) From: Stephan Szabo To: Seum-Lim Gan Cc: pgsql-performance@postgresql.org, ahsaleh@lucent.com Subject: Re: PostgreSQL Disk Usage and Page Size In-Reply-To: Message-ID: <20040317160012.G32224@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/252 X-Sequence-Number: 6108 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.