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.