Which database do I use: Postgres or MySQL? This age-old question has plagued
developers for, what, at least a couple years now. I’ve used both databases
extensively (MySQL for about one year and Postgres for about 2 years) and was
curious if the performance differences between the two were as stark as the
MySQL website suggests.
developers for, what, at least a couple years now. I’ve used both databases
extensively (MySQL for about one year and Postgres for about 2 years) and was
curious if the performance differences between the two were as stark as the
MySQL website suggests.
I had actually benchmarked the two databases back in September 1999 when we
were starting to lay the groundwork for SourceForge. At the time, the
performance difference was so stark that we had to go with MySQL even though I
had always used Postgres for all my work. The rest of the developers were used
to MySQL and that pretty much cinched the decision.
were starting to lay the groundwork for SourceForge. At the time, the
performance difference was so stark that we had to go with MySQL even though I
had always used Postgres for all my work. The rest of the developers were used
to MySQL and that pretty much cinched the decision.
This time around, rather than using some contrived benchmarking scheme, I
wanted to grab a “real life” web page from a “real” web site and see how it
performed on the two databases. The page in question was the discussion forum
on SourceForge. It involves some relatively straightforward joins of three
tables, each with 20-30,000 rows of data. It also involves some recursive
queries to show nested messages, so the database is the true bottleneck on this
page, not PHP.
wanted to grab a “real life” web page from a “real” web site and see how it
performed on the two databases. The page in question was the discussion forum
on SourceForge. It involves some relatively straightforward joins of three
tables, each with 20-30,000 rows of data. It also involves some recursive
queries to show nested messages, so the database is the true bottleneck on this
page, not PHP.
To get started, I dumped real data out of the production database, modified the
table SQL and imported it all into MySQL 3.22.30 and PostgreSQL 7.0.2 on Red
Hat Linux 6.2 and a VA Linux quad-xeon 4100 server with 1GB RAM.
table SQL and imported it all into MySQL 3.22.30 and PostgreSQL 7.0.2 on Red
Hat Linux 6.2 and a VA Linux quad-xeon 4100 server with 1GB RAM.
The first problem I ran into was that Postgres has an arcane limit of 8k of data
per row. In a message board, you’re going to occasionally surpass 8k of data
in a row, and so postgres choked on the import. To get around this, I just
dropped out the “body” of the message and re-imported the data. The Postgres
development team is aware of this limitation and are fixing it in v7.1, and
they also noted that you can recompile Postgres to support up to 32k,
although at a possible detriment to overall performance.
per row. In a message board, you’re going to occasionally surpass 8k of data
in a row, and so postgres choked on the import. To get around this, I just
dropped out the “body” of the message and re-imported the data. The Postgres
development team is aware of this limitation and are fixing it in v7.1, and
they also noted that you can recompile Postgres to support up to 32k,
although at a possible detriment to overall performance.
At this point, I ran into another small issue with Postgres – its “serial” data type (the
equivalent of MySQL’s auto_increment) creates a “sequence” which does not get
dropped when its parent table is dropped. So if you try to re-create the table,
you’ll get a name conflict for this sequence. A lot of new users would be
confused by this, so Postgres loses a couple points for that. Also, MySQL is
“smart” enough to increment its auto_increment value when you import data,
whereas Postgres’ sequence does not get reset when you import data, causing all
new inserts to fail.
equivalent of MySQL’s auto_increment) creates a “sequence” which does not get
dropped when its parent table is dropped. So if you try to re-create the table,
you’ll get a name conflict for this sequence. A lot of new users would be
confused by this, so Postgres loses a couple points for that. Also, MySQL is
“smart” enough to increment its auto_increment value when you import data,
whereas Postgres’ sequence does not get reset when you import data, causing all
new inserts to fail.
