Is there any difference in terms of performance to build my website's database using PostGreSQL than to built it using MySQL?

A few years back, the choice was between raw speed, in which case you chose MySQL, and features and reliability, in which case you chose PostgreSQL. (If you needed speed, features, and reliability, you sold your soul and bought a commercial RDBMS.)

Since then, MySQL has added features, and PostgreSQL has improved its speed significantly, so they’re considerably closer. I don’t know precisely how close they are, since back in the MySQL 3 days I made the choice to use PostgreSQL for personal projects (because it had triggers, stored procedures, and constraints), and all the jobs I’ve worked at since have had proprietary databases.

One place you really need to be aware of this difference, though, is in reference material. If you buy a MySQL book, make sure it covers MySQL 5. Because of MySQL’s former feature limitations, a lot of things like data integrity checks had to be done in the application rather than in the database. That’s a really poor architectural choice, since verifying data integrity is what databases are built to do, but in older versions of MySQL there was simply no alternative.

Not having that overhead of the data verification is what made mySQL fast in part. It makes sense when what you are building is better managed handling that in the code. In a lot of instances you have to code the error handling for improper data any way so having the database also do it is redundant. It is the DB’s way of protecting itself. Bad things can happen once data is corrupted. Which is why with anything financial or transactional you are going to want the second set of checks. For a blog or personal site it probably does not matter.

PostgreSQL performs as well as any commercial RDBMS, so I do not agree that you can only get speed and features with paying money on the DB software. You need to know how to properly configure and build your DB regardless of which platform you use to get the performance you need.

The difference to me was PostgeSQL was much more difficult to work with as there were few if any GUI tools to work on PostgreSQL previously. This has changed now and there are several clients you can use to develop with PostgeSQL making it an ideal choice. However, there is much more material and documentation for mySQL as well as users using it and therefore likely answers to challenges/problems you have can be found on message boards and support forums.

Basically to answer your question, if you can get away without data verification in the DB then mySQL will be a better performer in general (as would any DB that dropped those features). Otherwise, it is also going to depend on your DB design and the kind of data it contains as to whether one performs better with certain queries. Then there is also the type of server you are going to run it on… buying a commercial DB may not help you if you run it on a machine that has the minimum memory requirements.

I don’t think for most websites you would notice the difference. Traditionally, MySQL has outperformed PostgreSQL, but anymore there doesn’t seem to be a noticeable difference. Commercial databases have progressed a lot too, but at work our MySQL queries generally outperform queries to our Oracle database, but that Oracle database is quite aged.

@jumpo7: PostgreSQL is still lacking in areas like automatic replication and optimization techniques like table partitioning. People who pay for Oracle or DB2 are not merely doing so because they have money to burn.

@cwilbur oh certainly there may be distinct features that any DB may have that leads to its selection aside from price (there is partitioning and there are replication options for postgreSQL by the by just not as advanced as some others perhaps). I was just responding to the “you can’t have features and speed unless you spend money” comment. And, well, I think that really depends on the features you need.

Regardless of features, a business may also want support. It costs to get support for any software whether you hire internal or external for an open source product or go with a commercial product license. Given the economic conditions you may be able to get a really good price on Orcale, DB2, or MSSQL and that a biz is more comfortable getting that from one of the big names. Nothing wrong with spending money to get a tool that does what you need the way you need it. That’s why I buy a Mac for my personal use.

@jumpo7 Well told from the perspective of business politics. I use Microsoft SQL Server at work for a lot of things simply because management doesn’t trust all that god awful open-sores bottom feeder nonsense. Unfortunately office politics have a bigger hand in decisions than common sense.

@jumpo7: Cast your mind back to 1998 or so. MySQL existed; PostgreSQL existed. If you needed speed and data integrity, let alone features like stored procedures, foreign keys, and triggers, which one would you have chosen?

Ah yes back in the day… I made that choice and it was mySQL. Because I was both DBA and developer (and designer, project manager, tester,...), I could handle data integrity in the application layer.