Re: Row size overhead

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(at)comcast(dot)net]
Sent: Thursday, March 20, 2008 5:44 PM
To: pgsql-general(at)postgresql(dot)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(dot)deolasee(at)gmail(dot)com]
> Sent: Wednesday, March 19, 2008 8:23 PM
> To: Zubkovsky, Sergey
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Row size overhead
>
> 2008/3/19 Zubkovsky, Sergey <Sergey(dot)Zubkovsky(at)transas(dot)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(at)comcast(dot)net