[development] Why do we avoid auto-incrementing db columns?

[Sammy Spets]

Hi everyone,

I'm PostgreSQL maintainer for Drupal and since people are up in the air
about what is going on down in the DB layer re autoincrement and serial
fields I figured i'd shed some light. Many people have already stated
this stuff in earlier mails and hopefully getting the info in one big
pile will make it more concrete.

PostgreSQL does implement atomic sequencing with the serial and
bigserial types. MySQL does the same with the auto_increment field
property. These are used in Drupal to produce a guaranteed unique ID
for tables using them. Implementing this in PHP will certainly generate
non-unique IDs unless you lock tables thus introducing a massive
performance penalty.

We use db_next_id() in Drupal to provide a db-independent method of
getting the next unique ID into the application for use just after the
insert operation on the DB. The performance hit of using a sequence is
next to nothing considering all that's involved is a mutex lock, read,
increment and mutex unlock. Making this more efficient is simply not
possible.

Sure there are two different use cases for insertions: those where you 
need the ID afterwards (e.g node) and those you don't (e.g watchdog). 
If you want to write code without the use of db_next_id() then do an 
INSERT and omit the id column. I don't see anyone wanting to spend 
effort patching Drupal core to gain 0.01% efficiency across the 
application.

/me turns his torch off

-- 
Sammy Spets
Synerger Pty Ltd
http://synerger.com

On 25-Apr-07 10:15, Barry Jaspan wrote:
> 
> Could someone either clue me in or point to a prior discussion about
> why Drupal avoids using auto-incrementing database columns?  If it is
> database compatibility, which versions of which databases that we
> support do not have it?
> 
> Thanks,
> 
> Barry
>