Page 1 of 1

Big Danger with OIDs and PostgreSQL

Posted: Sat Feb 07, 2009 3:02 pm
by supermike
Check out this FAQ item from the PostgreSQL support team:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.12
4.12) What is an OID? What is a CTID?

If a table is created WITH OIDS, each row gets a unique a OID. OIDs are automatically assigned unique 4-byte integers that are unique across the entire installation. However, they overflow at 4 billion, and then the OIDs start being duplicated. PostgreSQL uses OIDs to link its internal system tables together.

To uniquely number rows in user tables, it is best to use SERIAL rather than OIDs because SERIAL sequences are unique only within a single table. and are therefore less likely to overflow. SERIAL8 is available for storing eight-byte sequence values.

CTIDs are used to identify specific physical rows with block and offset values. CTIDs change after rows are modified or reloaded. They are used by index entries to point to physical rows.
Basically, the OID can be dangerous to use if you have a very busy database because it can be a timebomb. Even if your database cleans out old records and creates new ones, that OID is still growing and is still used as one serial ID across several tables. Eventually that OID will run out of space and will start all over again. Sure, it takes 4 billion rows for that to happen, but take for instance a job search site where records are added and deleted constantly. Eventually within like 5 or 6 years, with enough load, you just might hit 4 billion rows created, even though your database size might only be like 2GB at a given moment in time.

So, if you were using your OID mechanism as, say, a way for your admin pages to allow you to edit a record from a grid click, you would eventually be up the creek and get an error.

In other words, OIDs cannot be trusted and should not be used. If you really want to trust a way to have a grid click to edit a single record, you must key it some how. Therefore, add a SERIAL8 column to your table. That way, you guarantee uniqueness to that table and to that row.

Re: Big Danger with OIDs and PostgreSQL

Posted: Sat Feb 07, 2009 6:50 pm
by VladSun
Basically, the OID can be dangerous to use if you have a very busy database because it can be a timebomb.
The same applies to serial8, right?

Re: Big Danger with OIDs and PostgreSQL

Posted: Sat Feb 07, 2009 7:33 pm
by supermike
The difference is that according to the FAQ, the OID generator generates a sequence that goes across all tables, where as a SERIAL8 goes against one table and thus has a timebomb, yes, but a timebomb that would occur far later in your database lifespan. Get it? In other words, when you create a table row in table A, you might get an OID of 4500. But when table B gets a new row and needs a new OID, it gets 4501. And so on through your other tables. So, if you have like 15 active tables, you just sucked down the OID 15 times faster. But with a SERIAL8 column in each table, and if you have like 15 active tables, it is 15x less likely to run into this issue because the SERIAL8 sequence stays with each table.

Re: Big Danger with OIDs and PostgreSQL

Posted: Sat Feb 07, 2009 7:59 pm
by VladSun
The same would apply to serial(2^n)...
There will always be a timebomb...
But with a SERIAL8 column in each table, and if you have like 15 active tables, it is 15x less likely to run into this issue because the SERIAL8 sequence stays with each table.
It's rarely true.

Usually one has one (or 2, or 3 ...) table that grows really fast and N-1 (or 2, or 3) tables that will not grow that fast (I mean times and times slower than the mentioned ones)