Skip site navigation (1) Skip section navigation (2)

Re: Row size overhead

From: "Zubkovsky, Sergey" <Sergey(dot)Zubkovsky(at)transas(dot)com>
To: "Adrian Klaver" <aklaver(at)comcast(dot)net>,<pgsql-general(at)postgresql(dot)org>
Cc: "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com>
Subject: Re: Row size overhead
Date: 2008-03-20 16:13:04
Message-ID: 528853D3C5ED2C4AA8990B504BA7FB850106DF24@sol.transas.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Mason HaleDate: 2008-03-20 17:10:02
Subject: Re: unexpected results with NOT IN query
Previous:From: Josh TrutwinDate: 2008-03-20 16:02:21
Subject: ALTER TABLE with USING clause for timestamp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group