Re: Article on MySQL vs. Postgres

Tim Perdue wrote:

On wednesday or thursday, I'm going to be publishing my article on MySQL
vs. Postgres on PHPBuilder.com.

Before I do that I want to confirm the major problem I had w/postgres:
the 8K tuple limit. When trying to import some tables from MySQL,
postgres kept choking because MySQL has no such limit on the size of a
row in the database (text fields on MySQL can be multi-megabyte).

This is beeing fixed: http://www.postgresql.org/projects/devel-toast.html

Is it even possible to import large text fields into postgres? If not,
how in the world can anyone use this to store message board posts,
resumes, etc? Do you have to use pgsql-specific large-object
import/export commands?

I'm currently building a newspaper system and I just split the articles into
8K sections. This is just a workaround until the TOAST project is finished.

I actually intended the article to be a win for Postgres, as I've used
it and had good luck with it for such a long time, but if you look at
the results below, it seems very positive for MySQL.

Performace/Scalability:

MySQL was About 50-60% faster in real-world web serving, but it crumbles
under a real load. Postgres on the other hand scaled 3x higher than
MySQL before it started to crumble on the same machine. Unfortunately,
Postgres would probably still lose on a high-traffic website because
MySQL can crank out the pages so much faster, number of concurrent
connections is hard to compare. MySQL also seems to make better use of
multiple-processor machines like the quad-xeon I tested on. Postgres
never saturated all 4 processors as MySQL did.

Tools:
MySQL has some nice admin tools that allow you to watch individual
connections and queries as they progress and tools to recover from
corruption. I haven't seem any similar tools for postgres.

Have you looked at pgAdmin? http://www.pgadmin.freeserve.co.uk/
There is also a tool called pgAccess.

Long-term stability:
Postgres is undoubtably the long-run winner in stability, whereas MySQL
will freak out or die when left running for more than a month at a time.
But if you ever do have a problem with postgres, you generally have to
nuke the database and recover from a backup, as there are no known tools
to fix index and database corruption. For a long-running postgres
database, you will occasionally have to drop indexes and re-create them,
causing downtime.

Usability:
Both databases use a similar command-line interface. Postgres uses
"slash commands" to help you view database structures. MySQL uses a more
memorable, uniform syntax like "Show Tables; Show Databases; Describe
table_x;" and has better support for altering/changing tables, columns,
and even databases.

Features:
Postgres is undoubtedly far, far more advanced than MySQL is. Postgres
now supports foreign keys, which can help with referential integrity.
Postgres supports subselects and better support for creating tables as
the result of queries. The "transaction" support that MySQL lacks is
included in Postgres, although you'll never miss it on a website, unless
you're building something for a bank, and if you're doing that, you'll
use oracle.

Not true. Transactions are used to make atomic database operations. We use
transactions more than 60 times in our application (we use Cold Fusion).

Tim

--
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Poul L. Christiansen
Dynamic Paper