pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Keaton Adams <kadams@mxlogic.com>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: WAL log performance/efficiency question
Date: Thu, 17 May 2007 09:34:43 -0600
Message-ID: <1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com> (raw)
In-Reply-To: <464C6544.9070104@enterprisedb.com>
References: <1179410671.26604.3.camel@MXLRMT-208.corp.mxlogic.com>
	<464C6544.9070104@enterprisedb.com>

So for every data page there is a 20 byte header, for every row there is
a 4 byte identifier (offset into the page), AND there is also a 28 byte
fixed-size header (27 + optional null bitmap)?? (I did find the section
in the 8.1 manual that give the physical page layout.)  The other RDBMS
platforms I have worked with have a header in the 28 byte range and a
row pointer of 4 bytes, and that's it.  I find it a bit surprising that
PostgreSQL would need another 28 bytes per row to track its contents.

I'll try the pg_relpages function as you suggest and recalculate from
there.

Thanks for the info,

-Keaton



On Thu, 2007-05-17 at 15:23 +0100, Heikki Linnakangas wrote:

> Keaton Adams wrote:
> > Using an 8K data page:
> > 
> > 8K data page (8192 bytes)
> > Less page header and row overhead leaves ~8000 bytes
> > At 100 bytes per row = ~80 rows/page
> > Rows loaded: 250,000 / 80 = 3125 data pages * 8192 = 25,600,000 bytes /
> > 1048576 = ~ 24.4 MB of data page space.
> 
> That's not accurate. There's 32 bytes of overhead per row, and that 
> gives you just 61 tuples per page. Anyhow, I'd suggest measuring the 
> real table size with pg_relpages function (from contrib/pgstattuple) or 
> from pg_class.relpages column (after ANALYZE).
> 
> > We are running on PostgreSQL 8.1.4 and are planning to move to 8.3 when
> > it becomes available.  Are there space utilization/performance
> > improvements in WAL logging in the upcoming release?
> 
> One big change in 8.3 is that COPY on a table that's been created or 
> truncated in the same transaction doesn't need to write WAL at all, if 
> WAL archiving isn't enabled.
> 

view thread (6+ messages)  latest in thread

Message-ID: <1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com>
Permalink:  ../1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com/
Also on:    postgresql.org/message-id/1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: kadams@mxlogic.com, heikki@enterprisedb.com
  Subject: Re: WAL log performance/efficiency question
  In-Reply-To: <1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com>
  References: <1179410671.26604.3.camel@MXLRMT-208.corp.mxlogic.com> <464C6544.9070104@enterprisedb.com> <1179416083.26604.15.camel@MXLRMT-208.corp.mxlogic.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox