In my test all 3358604 rows were inserted by single 'COPY FROM file'
command.
So it's obviously that each row has only one version.
> For 8.2 onwards, the tuple header is
> 23 bytes. Add another 4 bytes for one line pointer for each row. If
you
> have
> null values, another 5 bytes for null bitmap and alignment. Plus add
few
> bytes
> for page header and any unusable space in a page (because a row can
not
> fit in the remaining space).
> Simple calculations show that each row occupies 76 bytes
approximately.
> But anticipated row size would be 41 or near.
Row overhead: 23 + 4 + 5 = 32
Total row size: 32 + 41 = 73
Unfortunately value '76' has an objective explanation.
Thanks,
Sergey Zubkovsky
-----Original Message-----
From: Adrian Klaver [mailto:aklaver@comcast.net]
Sent: Thursday, March 20, 2008 5:44 PM
To: pgsql-general@postgresql.org
Cc: Zubkovsky, Sergey; Pavan Deolasee
Subject: Re: [GENERAL] Row size overhead
On Thursday 20 March 2008 7:24 am, Zubkovsky, Sergey wrote:
> Thanks for your reply.
>
> I had used PG 8.3.1 on 32-bit WinXP platform.
> "PostgreSQL 8.3.1, compiled by Visual C++ build 1400"
> But I suppose this fact doesn't change anything essentially.
>
> Thanks,
> Sergey Zubkovsky
What you are probably seeing is row depth not row width. Postgres uses
MVCC
and so there can be multiple versions of a row in existence at one time.
For
a better explanation see:
http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html
Try running Vacuum and/or Vacuum Full and see what happens to table
size.
>
>
> -----Original Message-----
> From: Pavan Deolasee [mailto:pavan.deolasee@gmail.com]
> Sent: Wednesday, March 19, 2008 8:23 PM
> To: Zubkovsky, Sergey
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Row size overhead
>
> 2008/3/19 Zubkovsky, Sergey <Sergey.Zubkovsky@transas.com>:
> > Simple calculations show that each row occupies 76 bytes
>
> approximately.
>
> > But anticipated row size would be 41 or near.
>
> You haven't mentioned PG version. For 8.2 onwards, the tuple header is
> 23 bytes. Add another 4 bytes for one line pointer for each row. If
you
> have
> null values, another 5 bytes for null bitmap and alignment. Plus add
few
> bytes
> for page header and any unusable space in a page (because a row can
not
> fit in the remaining space).
>
> Also ISTM that you might be loosing some space because of alignment
> in the tuple itself. Try moving booleans and char(3) at the end. There
> is not
> much you can do with other overheads.
>
>
> Thanks,
> Pavan
>
>
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
--
Adrian Klaver
aklaver@comcast.net
