Slashdot Mirror
Comparing MySQL and PostgreSQL 2
Mr. Jax writes "6 years ago Mr Poet submitted the story Comparing MySQL and PostgreSQL. Since then both databases have evolved to wherever they are today.
Are the points raised 6 years ago still valid? What has changed? Are there other things to consider since then (e.g. licensing)?" This is certainly a valid question since both databases have had to evolve with the times. Have these applications been specialized to fit a particular niche market or are they both still strong competitors? What does the horizon look like for the development of these programs, especially considering the recent MySQL partnership with SCO?
there is a short (decent) comparison at this url. ;-)
From my point of view (web application developer, Ozone framework author and the author of a few rich-content websites I can say for sure: I am more than happy to discover PostgreSQL. Why? More Oracle-like, transactions, nested transactions, views, sql-schema... I doubt MySQL 5.0 will come even close to the standard of PosgtreSQL.
Some can say MySQL is fast. No, it is not. When you run more than 100 users at once PostgreSQL is faster. MySQL has stupid table-locking mechanism that decreases performance significantly under high load.
I would say: PostgreSQL seems to be slower, is not perfectly optimized, but much better goals in its design were used. And one of the goals ic SQL conformance. MySQL is FAR from the SQL standard.
If you want to migrate from MySQL to e.g. Oracle - it is a pain. But PG is much closer to it.
IMHO PostgreSQL is an industry-standard database and we use it for almost every project now. We have used MySQL some time ago and believe me - the difference is huuuuuge. PG is a real database. MySQL seems like a table-managing-application
best regards - michal
PostgreSQL compares very well to Oracle.
I use PostgreSQL as a test database against which I write and test QT applications. I can switch an app between the two backends by changing only a few lines of code and recompiling, or I can build the switching capability into the app. Using PostgreSQL reduces the number of access licenses required for Oracle, or doesn't waste existing connections.
If I had my way I'd use PostgreSQL as the primary database, but some folks believe you've gotta pay money or the app isn't any good. As long as it's their money and not mine.
Running with Linux for over 20 years!
Creating graphical tools to enable more people to do more things easier is a part of Microsoft's business model. SQL Server Enterprise Manager etc. are just examples of where they've succeeded here.
Personally I'd love to see an open source equivalent of these tools, the offerings I've looked at so far are unfortunately lacking.
And don't give me crap about being a click-and-drool reboot monkey. I'm sick of typing SQL to get things done, when I can grant permissions by picking users from a list and ticking the right boxes.
Did you use telnet to post your slashdot comment? No, you used a graphical browser. Because you don't want to type the http request, and the graphical browser presents the HTML to you in a way that is more natural and effective for you.
Seeing me use Enterprise Manager does not make my co-workers think that they could do my job just as easily. They do not end up thinking any "fool" can do it.
I had mod points, and as a Firebird user, I was going to mod up the grandparent -- Firebird is very often ignored despite a host of positive features.
... heck, why bother? Just develop a good RDBMS with a good API, and let others fight it out? (That's an open question.)
... And then there's reliability ... ugh. MySQL just wasn't designed with data integrity in mind, while Pg and Fb were. "Foreign key constraints can be
But as you had a bad experience, and you link to your problem list, I thought I'd respond instead. Better to answer questions than just mod up friendlies.
- Database path: Yes, firebird supports aliases. Our app doesn't use them, but they're there.
- ISQL: I hear Oracle's SQL*Plus isn't much better. However, I use isql every once in a while, and I have command-history, backspacing, etc. available to me. From what I hear, it's more of a function of the shell you use (around isql) than isql itself. If you set up your environment properly, isql and its ilk automatically get command-history. (That's what I'm told, note. Anyone who can explain this is welcome to. I'm not a sysadmin.)
- Never seen it freeze.
- Corruption: we've had exactly one database issue, where it seems a backup/restore script ran in the middle of the day, restoring the database to its state from 4 hours earlier. In 4 years of use, with somewhere around 60 users in a medical clinic/insurance/billing environment, we've had no corruption. Using forced-writes is important, however. The careful-write strategy is really, really reliable, but it still can't protect you from faulty hard drives or operating systems that refuse to send data to the disk in the order requested (cf. Windows). M1 Abrams tank story, anyone?
- IBDataPump and other third-party tools exist for some of the other features you're interested in. I'm not sure I know how even I feel about some things only being offered by third-parties. Oracle's tools suck enough people buy other products
Feature-wise, and maybe target-audience-wise, Firebird and PostgreSQL are similar. Stored procedures, triggers, check constraints, MVCC (Postgresql seems to have copied MVCC off of Interbase, note), savepoints/nested (but not concurrent) sub-transactions, etc. It lacks a lot of the UDT (type) features of PostgreSQL (you can define domains, but not entirely new datatypes) -- note that Postgres was specifically designed with UDT's in mind. Firebird does support UDF (function) features though, and you can get some of the same flexibility that way if you're masochistic (save data in octet or blob fields and use UDF's to interpret the data). Pg also has neat SP language support, letting you write your SPs in a variety of languages -- Fb doesn't. Unlike Postgres, it's really easy to install, particularly on windows (that was a problem for Pg up until semi-recently) and it practically maintains itself. (Happily, the Pg team eventually got their vacuum, equivalent to Fb's sweep, to not take down the database, so Pg can now run 24/7 too.) Fyracle has been trying to make Firebird more Oracle-like in SP language support and some of Oracle's more interesting query abilities (CONNECT BY). Yes, I occasionally get feature-lust and look at other DBMS's. I don't need Oracle features, but Pg features would sometimes be nice. But I don't use Pg, so I don't know what annoyances it has that Pg users would be thinking about. Maybe it's all-around better, I don't know.
Both are really good projects, with their own strengths. I would say comparing Firebird and PostgreSQL is a much fairer comparison than Pg and MySQL or MySQL and Fb. Pg and Fb are more of a 'niche' comparison. MySQL has nowhere near the features of either of them, isn't nearly as safe, and just isn't designed with the same requirements in mind.
Every single experience I've had with MySQL has been one of "fixing" stuff for a MySQL user who just couldn't get things to work. Joins that wouldn't work (but should have), joins that were slow, data being eaten
For an enterprise system,you also need:
1) Views
2) Triggers
3) Integrity Enforcement (i.e. if you try to insert 1000000 into a numeric(4,2) column of your enterprise accounting app you should get an error and not have something inserted).
As your system gets large you may also want:
1) Table partitioning
2) Functional Indexes, i.e. create index on table foo (md5(bar))
3) Partial indexes (i.e. create index on table foo (bar) where open IS TRUE)
MySQL hardly offers all of these capabilities.
PostgreSQL 8.1 will offer all of them in usable forms.
BTW, for those interested, my site has a whitepapers section which has a MySQL to PostgreSQL migration guide.
LedgerSMB: Open source Accounting/ERP
Please, Oracle has a ton of features that just aren't there in PostgreSQL
"What metric" is the right question. But I'm not convinced that the best answer is "comparative length of feature lists". One man's feature is another man's bloat, after all.
I've been writing database apps for a living since 1984. I've worked on trading systems for stockbrokers and multinational merchant banks; I've worked for telecoms giants and for manufacturers. I can't think of a single oracle feature that I've ever needed to use that wasn't available in PostgreSQL.
Admittedly, this has a lot ot do with my style - I'm old school enough that I write my logic in C, C++ or Perl and use the database purely for storing and retrieving data. DBMS vendors (and some database researchers, to be fair) would like coders to do program purely with database packages. I've always though this a supremely boneheaded idea - I trust database designers to design databases, but not progamming langauges thank you. However, if that approach appeals, then you probably need a lot more features than I do.
But they ain't necessary, and it most assuredly is possible to write non-trivial real-world apps using the PostgreSQL feature set.
Don't let THEM immanentize the Eschaton!
Finally, if you want to scale, getting your app code out of the DB is the best first step. Outside the database server you can throw cheap app servers at a problem if you need to. Growing your DB server is another beast altogether. Despite the IBM/Oracle propaganda, big grown-up businesses are very hesitant to cluster their databases. Not just the cost but for tuning and safety (the odds of bugs in this super-complex technology bringing them down). In general, you have one live DB server for an app and at least one failover. Growing that single DB server is a lot harder than throwing in a few more pizza boxes, or whatever.