Re: WAL log performance/efficiency question

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.
>