PostgreSQL vs. MySQL comparison 390
prostoalex writes "Ever find yourself wondering which open source database is the best tool for the job? Well, wonder no more, and let your tax dollars do the work in the form of Fermi National Accelerator Laboratory publishing this unbiased review of MySQL vs. PostgreSQL. After reading it, however, it seems that MySQL ranks the same or better on most of the accounts." My poor sleepy eyes misread the date of posting on here; caveat that this is more then 15 months old.
Unbiased ? (Score:2, Interesting)
Re:Foreign Keys (Score:2, Interesting)
It's the subselects that get me - without them you have to jump through a lot of hoops. The sentence quoted basically translates as 'as long as you are only storing your CD collection and not doing anything serious with a database, then use mysql'.
TFA also fails to mention that mysql cannot be used in commercial development without paying $200 per client - which makes it more expensive than most other solutions (except maybe oracle, and even they have cheap licenses for some uses).
LAMP (Score:1, Interesting)
The low entry bar to PHP coding and the fact that most of the bargain bin webhosts offer a poorly configured MySQL install as part of a $1/mnth plan only serves to perpetuate this horrid combination of software. What is worse is the way in which it has seeped into corporate applications. I hate you all.
Why we moved from MySQL to PG (Score:5, Interesting)
1) Postgresql is more full featured than MySQL
2) MySQL is faster in a read-mostly environment
That's pretty much the same as the anecdotal arguments have been for years.
In my job, we moved from mysql to postgres several years ago (around PG 7.0). At the time, we needed to make the move for performance reasons. We are in a read-write system, and MySQL's locking was killing us (this was before InnoDB was well established). The features are better too, as our developers were used to having data integrity features, server side programming, and all of the SQL92 constructs available. We also learned a bit about PG performance, which I'll share.
1) Run EXPLAIN ANALYZE on everything. Postgresql is touchier about query performance than MySQL was. This just needs to be a habit if you're using PG. (You really should do performance analysis no matter your DB. It's just a good practice). The biggest gain will be making sure you're using index scans rather than sequential scans.
2) Use persistent connections. Everyone likes to point out the forking issue with PG vs. MySQL's threaded. PG's connection handling is slow, there's no doubt about it. But there's an easy answer. Just limit how often you connect. If you can keep a connection pool, and just reuse those connections, you'll save this big hit.
3) Full vacuum and reindex regularly. We've found the docs to be a bit off on this. It indicates that you should run these occasionally. If you're in a read-write system, a full vacuum on a regular basis is very important. It really doesn't take that long if you do it regularly. Also, we've had trouble with indexes getting unbalanced (we see 50->90% tuple turnover daily). This has gotten better, but it doesn't hurt to let your maintenance scripts make things ideal for you. So, we run a full vacuum and reindex of our tables nightly through cron.
4) Get your shared memory right. PG's shared buffers is probably the most important config attribute. It controls how much of your DB is memory resident vs disk resident. Avoiding disk hits is a big deal for any DB, so get this right. If you can fit your whole DB in memory, then do it. If not, make sure your primary tables will fit. The more you use the shared memory, and the less you have to page data in/out, the better your overall performance will be.
Most DB systems seem to be read-mostly, so I can understand the performance comparisons focusing on that. In our read-write system though, the locking was the biggest issue and it tilted the performance comparison toward PG.
more recent benchmarks (Score:5, Interesting)
They compare PostgreSQL 8.2 vs MySQL 4.1.20 and MySQL 5.1.20a.
Re:stability (Score:3, Interesting)
I've never seen MySQL do those things, either, to be fair, but I don't use it as much, and I *did* have it destroy a bunch of my data once when a machine was rebooted without being properly shut down.
The comment that Postgresql is unstable, even in the older version numbers, seems to indicate some kind of separation from reality. I didn't see anything in the article about their testing methods. I wonder, given some of the other complaints, if they tested them on *WINDOWS* despite the lousy support for Windows, and the outright recommendation that you don't try it, from the devs.
Seriously. Anyone else with me on that? Even outdated as this review is, it STILL doesn't make sense... unless you think maybe they did it on Windows. Then it makes more sense. Postgresql on Windows used to suck, and for good reason. It wasn't supported, either, and for good reason.
Postgres For Larger Datasets (Score:5, Interesting)
Re:Foreign Keys (Score:3, Interesting)
The truth of the matter is that postgres is a rapidly fluxuating target. I remember waiting five years for mysql 5.0 to finally see the light of day, and during that time postgres has truly experienced a ridiculous flurry of revisions and tweaks sometimes requiring alterations to their core storage format. That they do this all while providing full ACID compliance is a Godsend.
I don't begrudge MySQL their success, but the roots of that database were thanks to simplicity and speed, like where SQLite is now. It's also an evolving product, but I think its lost the original focus in the mad rush of (friendly) competition all products eventually engage.
Re:stability (Score:3, Interesting)
Re:stability (Score:3, Interesting)
Come to think of it, I haven't had PostgreSQL ever die on me, once. Even when my server crashed and rebooted, PostgreSQL would just happily pick up where it left off and get back to work.
Meanwhile, on more than one Linux server I had the pleasure to maintain, MySQL was notorious for randomly dying.
Something tells me that the person who wrote this report simply read the MySQL mailling lists, and compiled his report from the discussions, without any real testing, much less considering that a checklist is meaningless unless you also evaluate how WELL each DBMS implements said feature.
Re:Foreign Keys (Score:4, Interesting)
Re:Foreign Keys (Score:2, Interesting)
Very few (except special purpose) databases do ancient dates correctly.
AD alone is highly revised. BC is all over the map,
This bug report for DEC VMS is amazing in its analysis:
D I G I T A L
SPR ANSWER FORM
SPR NO. 11-60903
SYSTEM VERSION PRODUCT VERSION COMPONENT
SOFTWARE: VAX/VMS V3.2 VAX/VMS V3.2 Run-Time Library
PROBLEM:
The LIB$DAY Run-Time Library service "incorrectly" assumes the year
2000 is a leap year.
RESPONSE:
Thank you for your forward-looking SPR.
Various system services, such as SYS$ASCTIM assume that the year 2000
will be a leap year. Although one can never be sure of what will
happen at some future time, there is strong historical precedent for
presuming that the present Gregorian calendar will still be in affect
by the year 2000. Since we also hope that VMS will still be around by
then, we have chosen to adhere to these precedents.
The purpose of a calendar is to reckon time in advance, to show how
many days have to elapse until a certain event takes place in the
future, such as the harvest or the release of VMS V4. The earliest
calendars, naturally, were crude and tended to be based upon the
seasons or the lunar cycle.
The calendar of the Assyrians, for example, was based upon the phases
of the moon. They knew that a lunation (the time from one full moon
to the next) was 29 1/2 days long, so their lunar year had a duration
of 364 days. This fell short of the solar year by about 11 days.
(The exact time for the solar year is approximately 365 days, 5 hours,
48 minutes, and 46 seconds.) After 3 years, such a lunar calendar
would be off by a whole month, so the Assyrians added an extra month
from time to time to keep their calendar in synchronization with the
seasons.
The best approximation that was possible in antiquity was a 19-year
period, with 7 of these 19 years having 13 months (leap months). This
scheme was adopted as the basis for the religious calendar used by the
Jews. (The Arabs also used this calendar until Mohammed forbade
shifting from 12 months to 13 months.)
When Rome emerged as a world power, the difficulties of making a
calendar were well known, but the Romans complicated their lives
because of their superstition that even numbers were unlucky. Hence
their months were 29 or 31 days long, with the exception of February,
which had 28 days. Every second year, the Roman calendar included an
extra month called Mercedonius of 22 or 23 days to keep up with the
solar year.
Even this algorithm was very poor, so that in 45 BC, Caesar, advised
by the astronomer Sosigenes, ordered a sweeping reform. By imperial
decree, one year was made 445 days long to bring the calendar back in
step with the seasons. The new calendar, similar to the one we now
use was called the Julian calendar (named after Julius Caesar). It's
months were 30 or 31 days in length and every fourth year was made a
leap year (having 366 days). Caesar also decreed that the year would
start with the first of January, not the vernal equinox in late March.
Caesar's year was 11 1/2 minutes short of the calculations recommended
by Sosigenes and eventually the date of the vernal equinox began to
drift. Roger Bacon became alarmed and sent a note to Pope Clement IV,
who apparently was not impressed. Pope Sixtus IV later became
convinced that another reform was neede
Re:PostgreSQL vs. Oracle? (Score:2, Interesting)
In a way-oversimplified nutshell, Postgres seems sorta-kinda modelled after Oracle. But many times I look into a feature that I've used on Oracle and find it doesn't exist in Postgres or it's not up to my expectations. The Postgres feature might be a little half-assed. One example is the multi-version concurrency control. If you update a row in Oracle, the undo tablespace contains a snapshot of the old version. All other concurrent users are directed to the undo tablespace until the transaction completes. They get the old version of the uncommitted row from the undo tablespace. Postgres on the other hand makes a new row in the table for your update, and the old one is marked as a "dead tuple" whose data can be retrieved by other transactions.
Might not seem like a major difference, but you'll have to "vacuum" your Postgres database periodically to clean up your dead tuples. Otherwise, table scans have to scan over all the dead tuples in addition to the "real" data. Some DBAs simply run a vacuum job every night, or use the auto-vacuum daemon. It's not always so simple however. Imagine you have to update data every morning and get it done ASAP. Well, now that you've updated your 500,000 rows, you have to vacuum the table to keep queries running at a decent speed. When I was still a Postgres newbie, I would just vacuum whenever I remembered. One time I forgot about it for a few days, when I finally ran the vacuum it took 17 hours to complete - granted it was a one disk server. It's just another thing to worry about.
Once I looked for a replication solution for Postgres. Some were incomplete. Slony-I - the most popular Postgres replication solution - seemed like the way to go. But upon delving into it, I found it imposed limitations because it sort of "corrupts" the Postgres data dictionary. I believe this corruption broke triggers on all replicated tables (don't quote me on that). Also Slony-I does not support multi-master replication at all.
Sometimes a feature is just great. Rarely is the Postgres feature better, except in terms of user-friendliness. For instance the psql command line tool blows sqlplus out of the water IMO. (Wow, paginated results, tab completion!)
Oracle has a wealth of OLAP/business intelligence features that are lacking in Postgres. There is a flavor of Postgres called Bizgres that is a bit better in this area, though still lacking in comparison. Bizgres is more cutting edge and doesn't seem to have nearly as strong a community as Postgres. Go to http://www.bizgres.com/bb/ [bizgres.com] and you'll see there are very few posts.
Oracle has an expert named Tom - asktom.oracle.com - who is a genius and answers all my questions. Postgres has an expert, also named Tom, who is a genius and answers all my questions on the newsgroup.
When would I use one over the other? Personally I would use Oracle if there were no budgetary constraints, but if Postgres definitely met all my requirements, I'd go with that. (Yes yes, I am Captain Obvious.) If you need Oracle's enterprise-level features then Postgres is out of the question. There is however EnterpriseDB, a commercial, Postgres-based enterprise-level database. Several months ago Sony "snubbed" Oracle and went with EnterpriseDB for its MMORPGs. I have not looked into EnterpriseDB myself so I can't comment further.
Well I'm rambling and not getting work done. Hope that helps just a little bit.
Foreign keys are an enterprise feature (Score:5, Interesting)
Where foreign keys and the other referential integrity features really shine is in true enterprise scenarios, when you may have hundreds or thousands of applications, written in multiple languages, working against the same shared database(s).
In that scenario, the only viable way to duplicate the functionality of foreign keys at the application level is to have a middle layer which all other applications are required to go through. Realistically, that middle layer has to be implemented as a server, serving requests for object/record creation, update and delete over the network. Implementing it as a library to be linked into applications doesn't work well, because there are multiple applications accessing the database, and integrity enforcement needs to be centrally coordinated.
Implementing a middleware data server for an application isn't all that difficult, but integrating it into applications can be. Most application development environments know how to talk to databases, but don't automatically know how to talk to your application-specific, language-independent, data server. So now you're writing a client library for each app dev platform used in the enterprise, and dealing with things like integrating your custom interface with data-bound controls in the user interface. BTW, this is where people start resorting to e.g. SOAP, and projects start going off the rails (no pun intended, Ruby fans).
Luckily, as it turns out, there are already standardized, widely-available, well-supported systems that implement a centralized data serving service which enforces referential integrity. They're called databases. And foreign keys are an essential part of the service they provide.
Re:Foreign Keys (Score:2, Interesting)
Good point. Al lot of "small" Web applications are actually "single application - single database". In addition to that, many are heavily read oriented with very few writes and manipulate data that is not very critical. In environment like this, having "application level" data consistency might be quick and dirty solution that actually works quite well in practice.
However, I'm under impression that many people learn these quick and dirty solutions first and never quite take the time u understand why the "slow and clean" solutions were invented in the first place.
In addition to that, I would argue that most development goes into "large" Web applications (they are fewer, but require significantly more development). Also, Web is by no means the only area where databases find their application.
Re:Foreign Keys (Score:3, Interesting)
The FSF's interpretation of their own license holds that both static and dynamic linking create a derivative-work relationship. I don't know what their legal argument is, but the advice they issue is that if a binary does not work unless a specific shared library available, it uses that library and is therefore required to respect that library's license. Note that a even dynamically linked executable does generally have specific references to parts of the shared library compiled into it, and on some platforms is even statically linked to an import library, so there is some logic in this position.
Now, if what you had in mind was an application that wasn't linked to MySQL libraries at all, but interacted with MySQL by running the mysql program with specific command-line options and parsing the output, then you would be correct: that does not create a derivative-work relationship, and even RMS himself is of the opinion that a program that works that way would not infringe on the GPL and could be licensed however you please. Of course, there would probably be something of a performance hit.
Re:Foreign Keys (Score:3, Interesting)
Not looking for legal advice, just curious because you have some interesting thoughts on things.