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?
The DBAs I worked with always told me "Postgres is better". But I tried it a good few years ago, couldn't install it, it didn't "just work", and I was not that good with Linux at the time, so I just moved on to the next thing - MySQL.
:%s/mysql_/pg_/g in all my PHP files. Change mysql_error to pg_last_error, and fiddle with pg_num_rows, and it all worked. Moreover, one huge query that took 25 seconds to complete in MySQL (lots of JOINS and nastiness) took about 1 second in Postgres.
MySQL was good enough, and all the stuff that hardened DBAs said to me - "It doesn't do transactions", or "It handles NULLs wierdly", etc, just didn't apply.
But when I tried to do a query like this: SELECT * FROM foo where bar NOT IN (SELECT blib from wheee) - MySQL advised me that it "didn't do" "NOT IN" queries. I tried to work around it, but after trying all the JOINs I could, it just didn't seem like something that I could get round. (I wasted quite a long time trying to work around this, and although I'm sure that some really top DBAs out there can do it, I couldn't.)
So, mysqldump > mysql.dump, and then restore into Postgres.
I've never looked back. MySQL is now just coming to fill in all the gaps it's missing - but just go with Postgres. It's rather good.
No mention of SQL servers can go without the Gotchas: Mysql and Postgres. The worst MySQL is probably that it modifies data as you insert it without throwing an error. Yuk.
Get your own free personal location tracker
This page is the best document I've seen comparing each of the majordatabases (Oracle, Postgresql, DB2, MySQL, SQL Server) not directly against each other, but against the SQL Standard. In cases where at least one of the databases differs from the standard, this guy's article shows both the SQL called for by the standard, and how each of the implementations may either follow or deviate from the standard.
I'm using PostgreSQL and MySQL, from my experience:
1. I've never encountered corrupted data with mysql (It seems to be urban legend), and I have worked on tables with billions rows for two years.
2. PostgreSQL has more features and/or is more complete (simple example can be auto_increment vs. sequences)
3. PostgreSQL is heavier, and I hate statistics collector subprocess via udp (which seems to be eating 1-2% cpu all the time)*
4. mysql isn't much (if any) faster.
* - it's unlikely but possible my configs are to blame.
You can use MySQL for your enterprise apps, but it is not Oracle. MySQL, while boasting impressive database sizes, is not even close to competing with Oracle (or DB2 or Sybase) on the largest deployed database sizes.
The enterprise is not as price-sensitive as the SOHO market. Very few that buy an enterprise Oracle license use the out-of-the-box tools.
See my answer to number 2.
Only in environments that MySQL can handle. Oracle can handle scenarios where MySQL cannot run at all let alone run fast.
Agreed. Oracle definitely has its warts.
That said, migration to and from Oracle is easier with PostgreSQL or Firebird -- especially if you start on the lower end. MySQL has been so far from SQL standard compliance, you may not know when you're doing something really weird. MySQL 5.0's strict mode has helped tremendously with this. Too bad it's not ready for production yet.
- I don't need to go outside, my CRT tan'll do me just fine.
There isn't one from MySQL AB.
Actually there is a press release on MySQL's website:
SCO Partners With MySQL AB to Lower Costs and Increase the Power & Scalability of Modern Database Solutions
FalconShould there be a Law?
I disagree.
Putting everything (by everything I mean business logic) in the DB is the only sane way to keep your data consistent across multiple access methods. You simply can't thow data at a DB and then try to code and maintain consistent business logic in a half dozen client apps. You might be able to get away with a shared client access lib but even that can get messy.
Let's face it: Your data's in the DB. Why pull it all into the application to work on some small subset? Do all the queries and joins and clauses and increments in the database. The DB knows best where the data is and how you're going to be tinkering with it (so long as you give it sufficent hints), so it's the only sane method to access your data in a logical fashion. That's precisely why all these scripting languages and language interfaces exist.
I too use XML-RPC and SOAP (moreso the latter it seems, as XML-RPC is a little too light IMO) to access my data, but you can bet your sweet bippy I'm having the DB do as much as possible in order to transfer as little data as possible across my app-db link.
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.
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!
So what exactly is the difference between the MySQL-SCO relationship and the PostgreSQL-SCO realtionship that were announced at about the same time?
MySQL has only one commercial vendor, who helpfully call themselves MySQL AB, so even Slashdot readers can understand what they sell. So SCO made a deal with them to compile and test a certified MySQL binary for SCO.
PostgreSQL has had a number of failed commercial vendors over the years, but one current one is EnterpriseDB. Maybe not having the word PostgreSQL in the company name confused slashdot readers who think Walmart sell Wals?
eWeek report it as the same deal. "SCO has added open source database vendors MySQL and EnterpriseDB to its partner list, said SCO President and CEO Darl McBride"
What is the difference?
Oh, I forgot. This is slashdot where MySQL is evil because they charge for some things and where we all sit around and pretend that MySQL does not have transactions and that PostgreSQL vacuum is a good thing.
Yay for Postgres/Perl. Boo for MySQL/PHP. Can I have mod points now?
So this is what passes for "Score 5: Informative" now?
Invent an imaginary version of postgres to compare to a real version of MySQL, then spout some fictional cons. In that case:
I'm using both, but mainly Postgres. From what I can tell:
Postgres 7.841
Pros:
- Supports african dialects such as Kaliharinese
- Adds extra features when it detects that the user is a Womble
- Compatible with IP/feline
Cons:
- Runs slowly if you try to quieten your hard drives with banana peels
MySQL 4.841
Pros:
- Written entirely by Ooompa Loompas
- Discourages the use of Perl
Cons:
- Supports animal testing. Drips of MySQL are places in the eyes of penguins to check for irritation.
- Shows signs of money contamination, which brings hippies out in a rash
- Does not support transactions.
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.
I've worked for several years both creating programs inside the database and on a server layer outside it (and also just about every other layer).
I have to agree with grassbeetle above.
Software architecture-wise:
- You can't make a scalable architecture if you put everything in one single place (in this case the database).
- You will be hard-pressed to create a failure tolerant architecture if you stuff everything in a single point of failure.
- Databases are NOT application servers. They are designed with data storage and retrieval in mind, not reliable execution of complex business logic. Amongst other things databases do not make available in an easy and/or reliable way some of the standard application server functionality.
- All external components of the application (for example UIs) have to connect to the database. You're now stuck to using the connection protocols from the chosen database. This might cause all sort of problems with security, firewalls, use of asychronous messaging, availability of adaptors in the platform you are deploying your applications to, etc...
- Spliting your application accross several servers or in a multi-tiered geographical distribution is much harder.
- All coders have to have a good knowledge on how to work with the specific database you are using.
- Programing inside databases is not standartized. Different databases and indeed different versions of the same database have sometimes different versions of the same language or different libraries available. The language/libraries have not been so throughly used/tested/examined by a big user comunity (while for example standard C/Java/etc libraries have been thouroughly debugged in billions of man-hours of use). This means more library bugs and a lack of third party tools for software design and development inside the database.
- Facilities such as version control, source control, etc are either not available or difficult to use in a reliable manner.
- Availability of compatible 3rd party libraries or application modules is very, very restricted by comparison to NOT having your server side logic all inside the database.
- Forget about moving databases in the future. Also, simple migrating to a newer version of the database can be a nightmare.
Software design-wise, the design of the software will be strongly constrained by the internal structure of the database:
- Information flows will mostly have to be database-like information flows
- A true object oriented structure is pretty much impossible. At the most you can do weakly connected islands with an objecte oriented structure. If the database language you have to use is procedural forget about OO design.
- Server-side initiated connections to outside entities, thread control, ditributed transactions and other more advanced functionalities are pretty much impossible.
- Usage/integration with 3rd party libraries or application modules is very hard or even impossible.
Software programming-wise, and from my experience (mostly Oracle):
- The language sucks.
- The application libraries (not the DBA ones) suck big time.
Simply put, a software architect that puts all server-side logic inside the database is with this single choice removing almost all his other architecture options and creating/fortifying vendor lock-in of the application to the database itself and 3rd party tools and also of the development team itself by means of the knowledge experience they have/will gain with said database and said 3rd party tools.
Such a person should IMHO either be demoted to a place were he/she can't cause any damage or fired outright.