Thread

1. OID vs overall system performances on high load

   Eric Lauzon <eric.lauzon@abovesecurity.com> — 2005-05-29T20:17:11Z

   I am still in the dark due to my lack of knowledge on internal OID management,but
   i would presume that a table with OID enable and that has high load would require
   some more work from pgsql internal to maintain the OID index for the database.
    
   So OID can be beneficial on static tables, or tables that you want to be able to manipulate
   with pgadmin X , but can a table without OID increase performances on insert,delete,update,COPY?
    
   I am not really worried about disk space that an OID collumn can take, but i was wandering if an 
   insert in a table of 20 millions and more that has oid would slow the insert process. Since OID seem
   to act as a global index mabey maintaning that index can become costy over high table load by postgresql
   backend.
    
   -Eric Lauzon
    
    
    
    
    
    
   
   

2. Re: OID vs overall system performances on high load

   Andrew McMillan <andrew@catalyst.net.nz> — 2005-05-29T21:46:43Z

   On Sun, 2005-05-29 at 16:17 -0400, Eric Lauzon wrote:
   > I am still in the dark due to my lack of knowledge on internal OID management,but
   > i would presume that a table with OID enable and that has high load would require
   > some more work from pgsql internal to maintain the OID index for the database.
   >  
   > So OID can be beneficial on static tables, or tables that you want to be able to manipulate
   > with pgadmin X , but can a table without OID increase performances on insert,delete,update,COPY?
   >  
   > I am not really worried about disk space that an OID collumn can take, but i was wandering if an 
   > insert in a table of 20 millions and more that has oid would slow the insert process. Since OID seem
   > to act as a global index mabey maintaning that index can become costy over high table load by postgresql
   > backend.
   
   There is no OID index, unless you create one.
   
   The disk space that an OID column can take has an effect on performance:
   reducing the amount of physical disk reads will mean that more of your
   real data is cached, and so forth.  How much effect it will have will
   depend on the relative size of the OID column and the other columns in
   your data.
   
   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
                  http://survey.net.nz/ - any more questions?
   -------------------------------------------------------------------------
   
   

3. Re: OID vs overall system performances on high load

   Tom Lane <tgl@sss.pgh.pa.us> — 2005-05-29T21:47:18Z

   "Eric Lauzon" <eric.lauzon@abovesecurity.com> writes:
   > I am still in the dark due to my lack of knowledge on internal OID management,but
   > i would presume that a table with OID enable and that has high load would require
   > some more work from pgsql internal to maintain the OID index for the database.
   
   There is no "OID index"; at least not unless you choose to create one
   for a given table.  The only thing particularly special about OID is
   that there is an internal database-wide sequence generator for assigning
   new values.  Otherwise it works a whole lot like a serial column.
   
   			regards, tom lane
   
   

4. Re: OID vs overall system performances on high load

   Neil Conway <neilc@samurai.com> — 2005-05-30T04:02:44Z

   On Sun, 2005-05-29 at 16:17 -0400, Eric Lauzon wrote:
   > So OID can be beneficial on static tables
   
   OIDs aren't beneficial on "static tables"; unless you have unusual
   requirements[1], there is no benefit to having OIDs on user-created
   tables (see the default_with_oids GUC var, which will default to "false"
   in 8.1)
   
   -Neil
   
   [1] Such as a column that references a column in the system catalogs.