MySQL vs. PostgreSQL

[7m25]

Hello! I need a little information about the difference between MySQL and PostgreSQL. What are the advantages of PostgreSQL against MySQL? If you we're to ask, what would you use?

Thanks a lot in advance. 🙂

[itchtoo]

check out http://www.phpbuilder.com/columns/tim20000705.php3

[chriskl]

Hahahahaah - don't make me cough up my lungs!!!

Anyone who thinks that MySQL is faster than Posgres has obviously never done more than simple selects, and is a rather poor DBA. Good god.

PostgreSQL advantages:

• Subselects (mysql still in alpha for these, pgsql's had them for 4 years) hey - you can replace 7 queries in mysql with 1 in postgres, and it's 10x faster hooray!
• stored procedures (which are 50% faster than issuing SQL) You can write stored procs in plpgsql, sql, c, java, ruby, perl, python, R among others.
• Prepared statements (heaps faster than
• Partial indexes. eg. Say you have a column that is 90% nulls, but you want to index it. CREATE INDEX blah ON tab(col) WHERE col IS NOT NULL. Result - tiny index that is way faster than MySQL
• Functional indexes. Say you want to do index the month of a date: CREATE INDEX blah on tab(extract(month from datecol));. Results - a much smaller and faster index than MySQL
• Views
• Foreign keys that cascade - instead of doing over 40 deletes (whcih we used to have to under MySQL) when we removed a user, we do 1 - and it's 10x faster than MySQL hooray!
• Create your own index types, types, operators, aggregates
• Rules allow enforcing of very complex constraints
• Triggers allow high-level optimisations, such as automatic maintenance of summary data for billion row tables (which is a common use of postgres)
• Independent sequences - share your autoincrement cols between tables, generate invoice numbers from a sequence, etc.
• Schemas - create namespaces for all your users so they can create all their own database objects without interfering with anyone elses
• Multi charset support, utf8, heaps of conversions, all done in the db (mysql is still in alpha for this)
• Domains
• GiST indexes (eg. ultra fast text indexing, and TREE indexing, yes that's right - indexed tree structures)
• Rtree indexes for polygons, arrays, etc.
• SQL standard compliant (MySQL code is not standard and is not portable)
• Write-ahead-log with REDO recovery. guarantees data intregrity if machine goes down. MySQL has no such guarantee.
• Highly active developer community
• Truly excellent query planner
• Genetic query optimisation for huge joins. Try doing a 30 way join in MySQL and watch it die...
• Runs on way more platforms than MySQL

Enough reasons for you?

PostgreSQL disadvantages

• Query caching needs to be done in middleware
• Upgrade process requires a dump and reload
• Several different competing replication solutions, none of with are tightly integrated
• Steeper learning curve, but vastly more power once mastered.
• Native Win32 version only available commercially. Postgres 7.4 will be windows native.

Feel free to ask any more questions.

Chris

[csn]

Yep, I haven't noticed any decrease in performance switching from MySQL to PostgreSQL. With all it's features, the productivity gains are huge.

BTW, what exactly is query caching? First I thought maybe the PREPARE (http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-prepare.html) statement. Or is it something like storing all rows db-side?

[Sxooter]

I see you're willing to talk crap about pgsql, but are you willing to back it up? Let's see!

Your points:

1: You don't need the features of pgsql, so you use mysql, since MySQL is fast.

This is a total cop out. How they perform under heavy parallel load is probably the most important metric, and you don't have a clue how they compare in that space. I.e. you don't really care whether your websites stay up under Kuro5hin / slashdot load levels, and you don't know if it can, because you haven't tested for the best solution, you're using the convenient solution. You're lazy . It's ok, I am too, but in a different way. I test the heck out of the options to pick the best one so my phone won't ring at 2:00am in the morning. And it never does. Because I use Postgresql no MySQL.

2: Postgresql is a poor imitation of Oracle.

However, my big gripe, is you're willing to call pgsql a poor imitation of Oracle. Back it up pal, what's it missing that it would need to be a good imitator? Can you even name one feature off the top of your head that Postgresql doesn't have that Oracle does. There are a lot of serious professionals who donate time and money and effort to Postgresql, and you now owe them apology. They're people just like you and me, and they're rightly proud of their work.

How much slower is postgresql in your benchmarks? How many connections can Oracle handle on the same box as Postgresql ? You probably don't know any of these things, so you have no basis for an opinion (well, one that's worth anything anyway).

When you have actual experience with Postgresql, feel free to post about it. If you can be bothered to actually try it out, you might be pleasantly surprised. You certainly won't be the first person to have that happen.

Sorry for the above message, it was pointed at someone who cowardly besmirched the rep of postgresql but didn't have the courage of his convictions to stay and prove his point. I.e. he's deleted his previous message rather than trying to defend his position.

[Sxooter]

In my mind, there are some very big differences between Postgresql and MySQL, and they go to the very core of the two "databases".

Postgresql began life as a university experiment in relation databases. It has since evolved into an enterprise class RDBMS that can handle massive parallel loads on the right hardware.

Postgresql is designed around a transactional model, and the developers strive to acheive transactional integrity in everything the database does. For instance, even DDL statements are transactional. You can do something like this:

begin;
select * into tmp1 from test1;
alter table test1 rename to old_and_backed_up;
alter table tmp1 rename to test1;
create unique index test1_dx on test1 (id);
rollback;

And the changes will happen in a split second without the users ever knowing.

Postgresql uses MVCC for locking, which means that writers don't block readers and vice versa.

[csn]

Access is the best database. Period.

;P

[jerdo]

I have a question about the point chriskl made, stating that Postgres runs on more platforms than MySQL. I beg to differ on this one point (I don't argue any of the other points). I would say they pretty much run on the same platforms. Although you can download binaries of MySQL for five more platforms than Postgres. (Of course you can simply compile either of them on most any OS) Oh, and just to play devils advocate. Sxooter, I think chriskl points out a couple of things that are needed in Postgres that might already be available in Oracle, most prominent the integrated replication. And correct me if I'm wrong but doesn't slashdot use MySQL? (At least it did as of April 2002, and this is a reference to this comment "I.e. you don't really care whether your websites stay up under Kuro5hin / slashdot load levels")

[[deleted]]

Just to give an idea about the quality of PostgreSQL codebase:

At some point in PostgreSQL's history some of the developers took the code and developed for themselves. Later this version became known as Informix, the worlds leading database (ie: bigger than oracle and bigger than DB2 (which is why IBM bought Informix))

Informix and PostgreSQL still share quite a lot of functions and 'ways of doing things'

The big difference between PgSQL and MySQL is that MySQL was designed only for speed, whereas PostgreSQL was designed to give reliable access to consistent data.

MySQL's filosofy is not to be good or reliable, but just to be fast.

MySQL let's you do impossible things and silently tries to correct your mistakes, and it will wing the rest. For example, MySQL will let you do stupid things like:

SELECT username, COUNT(1)
FROM tabel
GROUP BY gender;

This query is obviously not possible, yet mysql happily executes it, returning some results that are completele bollocks, but none of the mysql users seem to notice.

The most often used argument for mysql and against PostgreSQL is that mysql is faster.
And this is mostly true. MySQL is faster than postgreSQL, but only at the things that MySQL can do.

MYSQL was designed for speed, and you can only be faster than your competitors if you do less work than your competitors. MySQL have eliminated all the features that 'slow the query down'. The result: lightning fast queries. The side effect: The queryes have to be very very very simple.

And using very very very simple queries means that you have to do much much much more work in your script. All the functions that real databases can do inside the database have to be done in your application.

Again, none of the mysql users seem to notice this, they just see that a query under postgresql takes longer than under mysql. They don't see that the extra time that PostgreSQL might take is spent on making sure that the data is safe, and they don't see that they spend hours writing routines to do things that PostgreSQL does inside the database.

For example; the lack of cascading delete in mysql (which is only available in the latest MySQL versions in a limited way) means that you as a programmer have to run multiple queries to safely remove records that have dependancies, and the lack of transactions means that if you make a typo in one of those queries, your data is corrupt because half of it was deleted and the other half was not. Time for backuptapes.

Again none of the mysql users seem to notice/care. All they see is that their queries are very fast.

MySQL also has some very strange ideas about kolumn types.
Ever notices that if you have CHAR columns, and you add a VARCHAR column, that suddenly all your CHAR columns change to VARCHAR? MySQL's insane theory is that if you have one variable length field in a table, you might aswell make them all variable because the speed advantage of using fixed length fields is gone once you have one VARCHAR. The fact that CHAR gives completely different results from VARCHAR seems to be unknown to them. So what if your applications f*cks up, MySQL is not for quality, only speed.

Right now the only real missing feature in postgresql is replication, you need third-party software to do that. But then again, PostgreSQL can handle so much more than MySQL that you can probably survive very well on just one server anyway 🙂

[[deleted]]

"I would say they pretty much run on the same platforms. Although you can download binaries of MySQL for five more platforms than Postgres. (Of course you can simply compile either of them on most any OS) "

Seems like you have answered your own question there :-)

MySQL is for convenience, so they supply binaries for different OSes. PostgreSQL goes for quality so they supply sourcecode. All the serious database users run un*x so they don't need binaries anyway 🙂

In fact PostgreSQL does not run natively on windows (yet) it requires that you run a un*x emulator called cygwin. But again, no serious database user installs a database on windows unless it is MS-SQL.

Slashdot does use mysql, but they seem to have serious issues with it:
http://slashdot.org/faq/tech.shtml#te050

"Slashcode itself is based on Apache, mod_perl and MySQL. The MySQL and Apache configs are still being tweaked -- part of the trick is to keep the MaxClients setting in httpd.conf on each web server low enough to not overwhelm the connection limits of database,..."

"Fault tolerance was a big issue. We've started by load balancing anything that could easily be balanced, but balancing MySQL is harder. We're funding development efforts with the MySQL team to add database replication and rollback capabilities to MySQL (these improvements will of course be rolled into the normal MySQL release as well)."

Looks like what they want is PostgreSQL with replication, but instead they cling to mysql, probably because they have a ton of badly documented code that they fear to port to postgreSQL :-)

[ultraslacker]

I would say they pretty much run on the same platforms. Although you can download binaries of MySQL for five more platforms than Postgres. (Of course you can simply compile either of them on most any OS)

Compare mysql and postgresql under heavy load on *bsd and you will quickly gain appreciation for postgresql. Perhaps chris meant postgresql runs better on more platforms than mysql? I won't blame mysql completely for this one, but I see the process based model of postgresql as more suitable to my needs - and better groundwork for the future of postgresql, as I see it.

Mysql was meant to be a fast database, at the expense of ACID. Consider some elements of mysql - thread based, replication*, transactions, foreign keys, myisamchk - all these are signs of wrong headedness, bad judgements, poor instincts and will keep mysql from being regarded highly by dba's.

Interestingly, whenever these discussions come up, it seems most people who have used both weigh in on the postgresql side.

• True, postgresql needs replication, but not how mysql has implemented it!

[nnichols]

Now that I've come round from the ear bashing I received (thanx peeps), it was useful to make my completely rediculous comments directed at postgresql.

I have had a brief play with pg and done a very abbreviated translation of one of my apps over to pg. I'm intrigued and will be looking at it further.

Can one of you please suggest a bible for pg? A really good reference would be useful.

I apologise again for my previous, ignorant posts.

[ultraslacker]

PostgreSQL: Introduction and Concepts is a good primer and goes into some detail. It was written for 7.2.

And of course there is a wealth of documentation, tutorials, and reference materials at techdocs.postgresql.org

[jerdo]

Why is it when someone points out a flaw in an argument of "Postgres vs MySQL" the Postgres militants always come back with "Well Postgres is more powerful"? I never said Postgres wasn't more powerful, I just pointed out a flaw in the argument that stated Postgres runs on more platforms than MySQL when it does not. If you are going to convince DB newbies that Postgres is the better choice please at least respond to their queries instead of going of on some other tangent. These wild-tangent, mine's bigger than yours, arguments are what keeps great things in the background to lesser things (Linux -> Windows). Also, vincente, please don't ever consider MSSQL a real SQL database either. The arguments presented against MySQL can very easily be translated against MSSQL, as Microsoft thumbs their nose to quite a few SQL standards in MSSQL (believe me I've dealt with MSSQL and having to change column types to fit the Microsoft standard instead of the SQL is quite rediculous).

[[deleted]]

"Also, vincente, please don't ever consider MSSQL a real SQL database either. The arguments presented against MySQL can very easily be translated against MSSQL, as Microsoft thumbs their nose to quite a few SQL standards in MSSQL (believe me I've dealt with MSSQL and having to change column types to fit the Microsoft standard instead of the SQL is quite rediculous)."

I consider MS-SQL to be a 'real' database in the sense that you can build a proper application with it, it has all the features that a real database needs (unlike MySQL).

But ofcourse I would never recommend using it, the fact that it runs on windows is enough for that, and the strange MS-dialect of SQL makes my skin crawl too, but compared to MySQL it is a much better choice for those who have this strange urge to run databases under windows.

[csn]

What specifically makes Windows (2000, XP, ...) a poor choice as a database server?

[[deleted]]

"What specifically makes Windows (2000, XP, ...) a poor choice as a database server?"

There is nothing specifically wrong, it's a combination of many things. The reboot-to-activate stuff is wrong, you can't reboot a database server to install some feature. And there's the endless stream of security issues, the GUI that cannot be switched off and is allways using memory, cpu and potentially generating (fatal) errors.

The idea behind a good database server is that it is a database server and nothing else, the fewer other things it does, the less chance there is that those other things interfere with the database's core business: being a database.

[csn]

Oh right - that rebooting is really annoying. Especially with all the updates that seem to come out. Haven't rebooted Linux in months...

See what one of the PG hackers (Tom Lane) has to say about Windows:

http://candle.pha.pa.us/mhonarc/todo.detail/win32/msg00011.html

[Sxooter]

Originally posted by jerdo

Oh, and just to play devils advocate. Sxooter, I think chriskl points out a couple of things that are needed in Postgres that might already be available in Oracle, most prominent the integrated replication. And correct me if I'm wrong but doesn't slashdot use MySQL? (At least it did as of April 2002, and this is a reference to this comment "I.e. you don't really care whether your websites stay up under Kuro5hin / slashdot load levels")

"Integrated" does not necessarily mean better replication. While replication for postgresql is currently available in several free and commercial forms, the fact that it's not integrated is hardly an issue. Is it available? yes. does it work reliably and well? yes. Is it hard to install? no.

My main point wasn't that there weren't things that postgresql needed to have before it would be a full match for Oracle, but that the guy saying Postrgresql was a pale imitation of Oracle had no clue what he was talking about, he was just flaming and likely couldn't tell the difference between asynchronous replication and syncrhonous replication. I.e. he was using what little knowledge he'd got in a magazine.

So, the integrated part of that means little to me, as those other criteria I pointed out are far more important. Is the fact that the replication is integrated worth $40,000 a year per CPU to most folks?

The real thing Oracle has that Postgresql would need to compete with it is load balancing clusters. And that is still a ways away. If you don't need load balanced clutsters, there isn't much you can do in Oracle you can't do with Postgresql.

My point about slashdot and kuro5hin was that they CAN'T handle their load. They crash all the time, and have had to buy truly huge database servers to get stable operation from MySQL. MySQL has serious issues under heavy parallel load that don't show up until you're committed to it as your database.

Sourceforge is a great example of how great postgresql can do. It was actually much faster, in my opinion, when it was running on postgresql before they switched to DB2 in a marketing led maneuver last year. It took almost six months for the search engine part to start working halfway decent, and it's still slower doing searches than it was on postrgresql, and it still doesn't match nearly as many terms. I'm guessing that had to cut down on the size of the keyword catalog to get decent performance.

[Sxooter]

Originally posted by csn
What specifically makes Windows (2000, XP, ...) a poor choice as a database server?

I think there are many things that make all flavors of Windows a poor choice for a database server. One is that it tends to fail under heavy load.

The next reason is that windows has a heavy process / light thread design, which means that it expects to run a couple of heavy processes each running dozens of threads.

Multi-threaded is a great way to gain performance, but unfortunately, it can lead to a rather fragile program environment, since one crashed thread will usually kill all it's brother threads.

The next reason is that if you open a file in windows and let the kernel buffer it, you can't force a flush of that file from the kernel buffers. You can force one from your own program buffers, but the kernel doesn't guarantee to actually flush buffers when you fsync.

So, if you want reliable buffering, YOU get to do it or you can let the kernel do it and hope the OS never crashes.

There's been a thread from Tatsuo on the pgsql-hackers list lately about power off failure testing on Windows failing. Basically, what it's come down to is that in order to get reliable performance on windows with a power off failure, the hackers have to keep a list of all dirty files in an array and flush them individually to get buffered performance WITH reliability. In any flavor of Unix, a simple fsync() is all you need.

And the last reason I consider Windows not worthy of hosting a database is that it changes too much from version to version, and you HAVE to upgrade to a new version every 5 years or so to stay supported. If it was just one or the other I wouldn't mind, as either condition wouldn't be so onerous, but the two together make for serious maintenance issues of legacy application databases, driving up TCO.