Thread: OID vs overall system performances on high load databases.

OID vs overall system performances on high load databases.

From
"Eric Lauzon"
Date:
What are the effect of having a table with arround 500 insert/update/delete on two to eight table in a time frame of 2
minutes24/24h, when you have oid enabled versus the same setup when you dont have oid? 

That deployment is done on a postgres with 8 to 9 databases, each having those 2 to 8 high load tables with oid
enabled.

Would the oid colum slow down table scan when you have over 20 millions row?

Would the cost of maintaining the oid column inside thoses high load tables when there is no oid reference used for
dataseeking costy for postgres ressources!? 



Eric Lauzon
[Recherche & Développement]
Above Sécurité / Above Security
Tél  : (450) 430-8166
Fax : (450) 430-1858

Re: OID vs overall system performances on high load

From
Andrew McMillan
Date:
On Fri, 2005-05-27 at 13:05 -0400, Eric Lauzon wrote:
> What are the effect of having a table with arround 500
> insert/update/delete on two to eight table in a time frame of 2
> minutes 24/24h, when you have oid enabled versus the same setup when
> you dont have oid?
>
> That deployment is done on a postgres with 8 to 9 databases, each
> having those 2 to 8 high load tables with oid enabled.
>
> Would the oid colum slow down table scan when you have over 20
> millions row?
>
> Would the cost of maintaining the oid column inside thoses high load
> tables when there is no oid reference used for data seeking costy for
> postgres ressources!?

The OID column is an extra few bytes on each row.  If you don't have any
use for it (and let's face it: most of us don't), then create your
tables "without OID".

The amount of impact that it makes will depend on what the general row
size is.  If they are rows with a couple of integers then the size of an
OID column will be a significant portion of the size of each row, and
removing it will make the physical on-disk data size significantly
smaller.  If the size of the average row is (e.g.) 2k then the OID will
only be a very small fraction of the data, and removing it will only
make a small difference.

Regards,
                    Andrew McMillan.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
             ... I want a COLOR T.V. and a VIBRATING BED!!!
-------------------------------------------------------------------------


Attachment

Re: OID vs overall system performances on high load

From
PFC
Date:

> The OID column is an extra few bytes on each row.  If you don't have any
> use for it (and let's face it: most of us don't), then create your
> tables "without OID".


    Also there are some useful hacks using the oid which don't work if it
wraps around, thus preventing it from wrapping around by not using on
every table could be useful in some cases...