Slashdot Log In
Comparing MySQL and PostgreSQL 2
Posted by
ScuttleMonkey
on Mon Sep 05, 2005 02:05 PM
from the backend-makes-the-frontend-go dept.
from the backend-makes-the-frontend-go dept.
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?
This discussion has been archived.
No new comments can be posted.
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
Full
Abbreviated
Hidden
Loading... please wait.
Another question (Score:5, Insightful)
Re:Another question (Score:5, Interesting)
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.
Parent
Re:Another question (Score:5, Informative)
Parent
Re:Another question (Score:5, Insightful)
Then arrange to have the benchmarking done in a country which won't uphold anti-competitive bullshit clauses (and when Oracle protest that the license lets them sue the guy in the jurisdiction of Buttfuck, Illinois, will tell them where they can stick their extradition request).
Although I reckon such a case (brought by Oracle) might still get thrown out in a US court, I wouldn't bet my life savings on that, and the US legal system means you're unlikely to get fees paid if Oracle lose (does this *ever* happen in the US?); a great way for the large company to effectively win by attrition if the benchmarkers don't have that much money.
Parent
Re:Another question (Score:5, Insightful)
My experience leads me to beleive the following things:
1) MS-SQL is a high quality database that is ridiculously easy to set up, tune, and maintain. It is also very expensive.
2) Postgres is a high quality database that is ridiculously easy to set up and maintain, but fairly difficult to tune. However, its performance is just as good as SQL server as long as you stay away from nested loops(*). It is also fairly inexpensive (free license, but increased TCO).
3) Oracle is a pig, and it requires a professional, certified swineherd. If you spend an amazing amount of money on licenses, gear, and certified DBAs you will presumably get good performance; I however was never able to get it past 60% of the performance of MS-SQL or Postgres.
(*) Nested loops are like candy to SQL server, and I've heard this is the same for Sybase (understandably). Deep sets of nested loops will kick the other databases I've tested in the teeth. Given an instruction with several nested loops and 16 million rows of data, I got results from SQL server in 5 minutes, results from Oracle 9 in an hour, and results from Postgres in 18 hours. This was a year ago and Postgres has changed, so it might be better now. Does MySQL handle them well?
Parent
Re:MySQL vs. Oracle (Score:5, Informative)
As of today, MySQL 4.1 [mysql.com] is the current release. 5.0, the current development snapshot, is the first to support stored procedures. Since the choice today is between a tested system and stored procedures, it most certainly does not "support all the Oracle procedures [I] need to build and operate an enterprice software system".
Next year? Maybe. Right now? No way, according to mysql.com.
Parent
Re:MySQL vs. Oracle (Score:5, Informative)
HAHA. Right. Tablespaces? Failover? High availability? Row-level locking? Stored procedures? Triggers? Multimaster replication? SQL conformity? I could go on, and on...
Yes, Oracle's builtin tools suck. However, others are available. This is basically "I'm not an Oracle DBA, but MySQL was easy for me, so it's better than Oracle!"
Yes, and anyone who's using a db tuned out of the box isn't doing significant work.
Blah, blah, Oracle is hard. Get a DBA and a real developer. This is what they're paid for.
Oh, that's right, you want us to get rid of the people with a clue, because you have to pay them. Brilliant! So I guess we'll call you at 3am on Sunday morning when our servers crashed, we have to restore from rollback segments on our failover cluster... oh wait. MySQL can't do that.
If you're building a big expensive app, you might look and see if PgSQL can support you. If you're building a crappy little webapp, you might check out PgSQL, because it's fun and you'll get some experience with a real database.
Given PgSQL is free and not all that hard to manage, I can't think of a single reason for switching to MySQL.
Parent
Re:MySQL vs. Oracle (Score:5, Informative)
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.
Parent
Also (Score:5, Funny)
Re:Also (Score:5, Funny)
Parent
Spatial databases (Score:5, Informative)
I used to like MySQL (Score:5, Insightful)
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 [sql-info.de] and Postgres [sql-info.de]. The worst MySQL is probably that it modifies data as you insert it without throwing an error. Yuk.
Heavy (Score:5, Informative)
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.
One thing to consider - collations and Unicode sup (Score:5, Informative)
There's a discussion about including support for IBM ICU, but as of right now there's no proper collations/unicode support in PgSQL, aside from storing character data in UTF-8.
MySQL is much better in this regard.
The scoreboard (Score:5, Informative)
Changes/corrections since that study was made:
PostgreSQL now natively supports BLOBs directly in tables (bytea type) as opposed to using oid references.
PostgreSQL has always had "better than row level" locking, Multi-Version Concurrency Control.
PostgreSQL has added Java and Ruby to its list of stored procedure languages.
----------------
Now, here's the caveat. MySQL 5.0 is still marked as a "development release (use this for previewing and testing new features)" so I didn't include it in the above. If we include MySQL 5.0, we must also include PostgreSQL 8.1, currently in beta.
MySQL 5.0 adds views, stored procedures, triggers, cursors, the bit data type, up to 65K varchar fields, two new storage engines (federated and archive), and a strict mode.
PostgreSQL 8.1 adds two-phase commits, a role system, shared row level locks using SELECT, and many speed improvements.
The strict mode in MySQL is most exciting to me. I always bought the argument that MySQL could have fewer features in exchange for greater speed. But there is no excuse (in my opinon of course) to accept random strings into numeric fields and other such contrivances (MySQL gotchas [sql-info.de]). Data integrity in a database should not be an optional feature.
Just compared MySQL 4.0.12 vs PG 8.0.3 (Score:5, Insightful)
Anyways, PostgreSQL IMHO has some things going for it:
- More features like triggers, stored procs, schemas, subselects, etc then the current stable version of MySQL supports. About the only thing I find myself using are subselects which are just a nice to have.
- Attempts to be "safer" with your data via WAL, etc. Good for unreliable environments.
- Tends to follow the SQL standards closer then MySQL
- Is BSD licenced so you don't have to worry about licensing issues.
- #postgresql on freenode is great. The people there are intelligent, knowledgeable and friendly if you're not an *sshole. They've helped me a lot.
The problems I have with PostgreSQL is that:
- INSERT is very slow (about 3x slower compared to MySQL/InnoDB) for my dataset. The "answer" is to use the COPY command or disable your indexes/FK's which is f*cking lame since you loose all your relational integrity. I was willing to trade off performance for disaster prevention (system crash, power failure, etc) by disabling WAL, but you can't actually do that.
- The OSS tools available aren't as good for postgres as they are for MySQL. I've yet to find anything as nice or complete as phpmyadmin for Pg or something that supports schema's for ER Diagrams. Frankly, I'm sick and tired of designing my DB in vim.
- Having to run vacuum all the time to help the query optimizer figure things out. Why this doesn't happen automagically in the background without me having to worry about it is beyond me.
- In general, I find the documentation on mysql.com superior to on postgresql.org, but #postgresql more then makes up for it.
Frankly, all the technical "problems" in MySQL or Pg can be worked around if you're willing to think out side of the box.
Re:Popularity (Score:5, Insightful)
I don't really know what to say about all of this - these incompatibilities are really frustrating as an end-user of this software, but I understand that it's hard to make things work with both MySQL and PostgreSQL, and resources are limited. What frustrates me is that these incompatibilities create a form of lock-in - once you've based your app on MySQL, you are stuck with it.
I suspect that if you were to start now, and to use the SQL spec rather than the MySQL documentation as a reference while doing your development, you would wind up with something that was a lot more portable, so this isn't actually an argument against using MySQL. It's more an argument towards sticking to standards when using whatever db you choose, so that when the time comes to use a different DB backend, you aren't faced with a monumental refactoring job.
Parent
Mysql is very isp friendly (Score:5, Insightful)
For a mom and pop ISP with only 3 or 4 employees this is significant. Is it feature filled? No
Its just included in the default user account which is difficult if not impossible with posgresql unless you manually install it for each account.
Users on the web dont need something heavy unless they are a commercial website. Also there are a ton of php and perl scripts and tools for users to use.
This is why msql is so popular. Its what ISP's prefer.
Parent
Re:popularity (Score:5, Informative)
PostgreSQL has this nice Object-Relational model where tables can be derived from each other, but there are some nasty bugs that mean I think those features are still best avoided.
Overall, though, I think PostgreSQL is by far the better RDBMS.
Parent
Re:My point of view (Score:5, Informative)
There is no postgresql release 7.5. The last 7.x release was 7.4 , the current stable is 8.0 with 8.1 in beta.
Parent
Haw haw (Score:5, Informative)
So, if I try to insert, say, a string of 10 chars into a varchar(9) field, what will it do? Will the magic version 5 reject it, as ever real database does, or will it truncate it silently, just as Toy databases (ala MySql 4.x) are wont to do?
What about the whole not-null thing? You know, if a field is set to NOT NULL and you don't populate it when you insert a row, a real database will reject it, where as a Toy database will accept it (MySql 4.x again!) and populate it with ... some other value.
Parent
Sco Partners With MySQL AB (Score:5, Informative)
"As part of the agreement, the companies will work together on a range of joint marketing, sales, training, business development and support programs"
http://www.mysql.com/news-and-events/news/article
burnin
Parent
Message bearer (Score:5, Insightful)
Considering you are nobody your press release wont really mean much. And I would also go so far as to say The SCO Group are nobody as well and their press releases don't mean much.
However, if Kroger made a big deal about your toothpaste purchase in the news section of their website it may actual be something to consider.
When SCO made their press release I didn't pay much attention because I have become very skeptical of any messages that come out of their organization. But it is disconcerting when MySQL considers the partnership to be news worthy as well:
http://www.mysql.com/news-and-events/news/article
burnin
Parent
SCO issued a press release. (Score:5, Informative)
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 [mysql.com]
FalconParent
Re:Get off it ScuttleMonkey (Score:5, Insightful)
Well, MySQL AB is a for-profit company, they sell things to people. And, last time I looked into it, SCO wasn't gassing people or mowing down rain forrest, or something. Sure, they are obnoxious, but the truth is, so are many commercial companies we deal with every day.
But does it even matter? The jokes on SCO, they paid to use something in a product very few companies will buy.
More fun than dragging out this, would be guessing what non-issue Slashdot will toss up on their front page next in an attempt to stir shit and boost their dieing readership?
Parent