So how does PostGreSQL compare to MySQL?

Posted Jan 1, 2010 20:28 UTC (Fri) by nevyn (guest, #33129)
In reply to: So how does PostGreSQL compare to MySQL? by ringerc
Parent article: The ongoing MySQL campaign

However, there are some very commonly used MySQL extensions (like AUTO_INCREMENT) which you must do a different way in PostgreSQL

I've only ever used PostgreSQL, so I'm wondering how AUTO_INCREMENT is different? Looking at the docs. the only "interesting" property I could see was it's use with more than one primary key for the table (but I'd assume/hope this wasn't common).

to post comments

So how does PostGreSQL compare to MySQL?

Posted Jan 2, 2010 2:13 UTC (Sat) by ringerc (subscriber, #3071) [Link]

Transactional behaviour.

Postgresql's sequences are lock-free and don't block other transactions. They allow high INSERT concurrency, but don't guarantee gapless sequences (ie: on ROLLBACK or statement failure, you might have a "missing" number, which you shouldn't care about for a primary key).

Looking at some MySQL documentation, though, it looks like for recent versions of InnoDB AUTO_INCREMENT works much like PostgreSQL's sequences. I don't know enough about the MySQL side to go into depth on any differences. It looks like InnoDB's AUTO_INCREMENT these days may behave very like a sequence if InnoDB has the configuration param "innodb_autoinc_lock_mode = 2" set, but that's about all I can say.

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increm...

( PostgreSQL doesn't support statement level replication, so the statement-replication concerns about auto-increment don't apply ).