Re: OID as Primary Key

From: "Jonas Bengtsson" <jonas(dot)b(at)home(dot)se>

> Why doesn't PostgreSQL has the same feature as MySQL (auto_increment) when
> dealing with PK? In nearly every relation I use a SERIAL.
> In PHP with MySQL you just use mysql_insert_id() instead of double
queries.

There's nothing to stop you defining a sequence (CREATE SEQUENCE) separate
from a field if you like. You can manually use currval('seqname') and
nextval('seqname'). You can access the sequence created by a serial in the
same way - use \d tablename to see the sequence name.

IIRC mysql limits you to one auto_increment column per table whereas you can
have as many sequences used by a table as seems reasonable to you.

> I have to admit that I like PostgreSQL a lot more than MySQL. Is there any
> good, objective, comparison between these to available? I'm mostly
> interested in the performance.

I think mysql have some comparisons and greatbridge did a test too. Take
them all with a pinch of salt - the only way to tell for sure which works
better for you is to try both.

In my experience, for small systems with relatively few clients and doing
mostly reads mysql can be 3-5 times faster. Once you start doing a lot of
writes with a couple of dozen clients and multi-way joins PG catches up
fast.

The two systems actually have completely different characters so check your
requirements carefully and pick which works for the current project.

- Richard Huxton