Re: 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