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?
Not only is this article just 2 links to some other slashdot articles, but the "comparison" of mysql and postgres article from 6 years ago.. Doesn't compare them! It's an article, like this one, asking for some comments.. So not only does this article add no news for nerds, it even misrepresents links to this very site, which the editors, again, are too lazy to even follow? Come on, people!
This sort of whoring-for-comments article should be a poll.
SCO employee? Check out the bounty
In recent versions of PostgreSQL, VACUUM still exists but is non-locking (i.e. it will not block concurrent database activity). There are tools available to run VACUUMs automatically as the database is in use, and recent versions of Postgres have been tuned so that the I/O performed by VACUUM should have less of an effect on the rest of the system.
I don't have any thoughts about the more general question, but PostgreSQL is much better at storing spatial data than MySQL. MySQL has spatial functions built in, but it only supports a subset of the OpenGIS functions (basically anything that can be done entirely with bounding boxes). PostgreSQL uses an external modulem PostGIS, which supports the full OpenGIS specification and a bunch of other extension functions besides. I've used MySQL by default simply because it is more familiar to me, but I've switched to PostgreSQL for my current project simply because of the spatial data module.
Friends don't let friends misuse the subjunctive.
MySQL's biggest problem is that if you try to update or insert with invalid data, in many cases it successfully inserts wrong data. PostgreSQL doesn't do that.
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 is comparing non-current versions, and making up false "cons" for mysql, out of thin air...
./ has been running on mysql + innodb for years. It also support clustering "out of the box".
Mysql 4.1 is the current stable version, and 5.0 is nearing release.
4,1 has excellent relational support, it is damn near impossible to corrupt if db design is correct, and innodb is great. IIRC
5.0 has views, triggers, stored procedures etc, and it's still amazingly fast.
Note: I base my mysql 4.1 comments on the linux version. I have heard that there is a version of mysql for windoze, but I can't vouch for it, and for all I know, it could be a disaster, but I don't really have anything definitive to say about it. Who knows, maybe original poster is talking about mysql on windoze (shrug)
Our company has developed an app used at several hundred sites on the Interbase/Firebird platform. (Firebird is now the only open source version). It is stable, quick, low maintenance with support for transactions, triggers, row level lockinge, etc. I would consider MySQL for web development because it comes preinstalled on many hosts and because of the number of tools available.
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.
-- Oh Well
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.
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.
Yeah, right.
Get your facts straight coward:
_ 948.html
"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
mysql> select count(*) from users where id not in (select id from users where id%2 != 0);
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.01 sec)
mysql>
One thing to consider - collations and Unicode support. Believe it or not, folks, Postgres does NOT support case-insensitive string comparisons. Or, more exactly it does, but you end up doing full table scan and converting everything into upper/lowercase, which is not an option on all but the smallest of the datasets. And even converting to upper/lowercase is a BIG problem for PostgreSQL, because it's UNICODE support is quite poor. So if your project has even remote possibility of using non-English textual data in lookups, steer clear of PostgreSQL.
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.
http://www.huihoo.com/postgresql/mysql-vs-pgsql.ht ml
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). Data integrity in a database should not be an optional feature.
- I don't need to go outside, my CRT tan'll do me just fine.
As of today, MySQL 4.1 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.
Dewey, what part of this looks like authorities should be involved?
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.
Don't think of it as a flame---it's more like an argument that does 3d6 fire damage
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?
Debian: apt-get install postgresql (or use Synaptic)
;-)
Gentoo: emerge postgresql
Fedora: rpm -Uvh postgresql-8.0.3.i386.rpm (or select the "Database" package during install)
Windows: setup.exe
Easy administration from Windows, OS X, Linux, and BSD with PgAdmin.
Or were you talking about a manual install? Sure that's harder, but most of us don't do manual installs. Just those crazy Slackware folks and their ilk.
- I don't need to go outside, my CRT tan'll do me just fine.
but ALL dbms's are slightly different from eachother
MySQL is far away and completely alone in it's flavor of SQL and behavior. There are all kinds of strange things it does, like use the "`" to quote identifiers. Oracle, PostgreSQL, SQL Server, &c. all have their differences. But they all look a lot more like eachother than any of them look like MySQL.
The biggest and most 'incompatible' difference between postgres and mysql is the autoincrement field really, the rest is just small fish to fix.
Actually, the biggest differences are regarding types and constraints, in my opinion. For instance, MySQL thinks February 31st is a date, PostgreSQL does not. PostgreSQL enforces constraints, MySQL does not. MySQL NULL handling is non-standard.
Social scientists are inspired by theories; scientists are humbled by facts.
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.
Most databases are descended from the Interactive Graphics Retrieval System INGRES. The original coder of Ingres later started a follow on project called Postgres and when SQL was standardised it became PostgreSQL. Basically, there are only about 4 database families: SAP, Ingres, DB2 and Oracle. I prefer using Postgres simply because it is funded by the nice US taxpayers through DARPA. If it is good enough for the military, it should be good enough for me...
Oh well, what the hell...
I've read a few of the replies to this story. It's interesting to read some of the pro-PostgreSQL peoples' opinions. They're rather dated.
The more I learn about MySQL (from the perspective of someone who was initially gung-ho about PostgreSQL), the more I realised the shortcomings of MySQL weren't really shortcomings. They were misunderstandings. Yes, this can sometimes be as bad, when a default option is a stupid option (like table-level locking, as the parent and other PostgreSQL fans complain about).
Then I quit that job and went to work at Friendster, which is also a big MySQL shop. What I learned then was that when used properly, MySQL can scale to amazing proportions. Millions of transactions per hour (I won't be too specific being as I don't want to be sued into oblivion now that I'm an ex-Friendster employee).
Keep in mind that Friendster isn't alone. Google and Yahoo! use MySQL. For production loads. Big, big production loads.
What I didn't like about PostgreSQL was the weird licensing problems. Yes, bizarre as it may be, the BSD license they chose over GPL causes it to be bizarre. You can't get replication without downloading some weird third-party patch and recompiling (because the patch is GPL). Screw that. MySQL has it built in to the supported binaries you get from their site.
Without replication, your DBMS is useless. It's pretty clear from reading the parent post that Michalf doesn't really understand replication. If he did, he might think a moment about his statement that MySQ can't scale to more than 100 users at once. Friendster had millions (at once). Yahoo! has at last estimate nearly a hundred million users at once.
Last I checked PostgreSQL (admittedly, 6-9 months ago?) it just wasn't viable. Really replication was about the only thing holding it up, except I know another engineer who worked extensively with PostgreSQL internals (hacking it up to create a DBMS cluster, actually) and he said their I/O internals were bad/slow. Hopefully he's wrong, but I know before I deploy PostgreSQL I'm going to be carefully benchmarking it before doing so. Keeping in mind that I never deploy an RDBMS in a tiny little "more than 100 users" environment like the parent poster.
Sorry for the long-winded rant. It's just that I've been wishing/hoping/praying PostgreSQL would be the winning RDBMS in this battle for years, and every time I think it's going to be any good, it goes and shoots itself in the foot somehow, which makes me sad. Currently, I'm still a fulltime MySQL DBA.
Caveat: Much of what I've said here only applies in high volume RDBMS environments. If your environment is low volume, PostgreSQL may be a better choice.
fifth sigma, inc.
The one good thing I have to say about mysql is that its multi-user friendly for hundreds of accounts.
Not really. PostgreSQL had MySQL beat there. You can assign permissions to groups. With large numbers of accounts, this becomes problematic with MySQL.
PostgreSQL 8.1 beta further improves this by supporting the concept of roles. A role is like a user or group except that they can be nested. This allows even easier administration of databases with hundreds or thousands of user accounts.
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.
Commercial as in what? A mom-and-pop web-store? I would call that commercial and because MySQL will *truncate* numbers to fit fields if they are too big, I would say PostgreSQL is needed for that.
You are right in that MySQL is quite adequate for content management provided (of course) that you don't need to integrate it with other buisness apps. If you do, you can either go to PostgreSQL (and take advantage of schemas) or use PostgreSQL with DBI-Link to pull the data from MySQL. DBI-Link is easily one of the coolest new projects for PostgreSQL as it allows any data source reachable via Perl's DBI interface to be seen as a table from within PostgreSQL.
LedgerSMB: Open source Accounting/ERP
No benchmarks here but benchmarks are largely useless in the database world anyway unless they are run on your specific application.
Oracle: Very portable database, replaces many OS functions and is extremely tunable. Downside: $$ and the fact that the tuning options are extremely complex allowing your DBA to spend all his time tuning the database, and your second DBA to spend all his time tuning the tables..... (/sarcasm)
One of the odd problems with Oracle is that empty strings and nulls are seen as equivalent (and Oracle DBA's seem to think that an empty string and a null are the same thing). The general concensus in the RDBMS industry is that these are not the same.
MS SQL Server: A Windows-only RDBMS which is tightly integrated with Windows in terms of memory management. Quite extensible, less costly and simpler to administrate than Oracle. Will tie you to Windows. Troubled security history.
PostgreSQL: An Open Source RDBMS designed to target Oracle's market. Extremely powerful and full featured. Attempts to tune itself to the greatest extent possible and relies on the OS for additional tuning. Downside is that it is not as widely used as the others listed above. Stored procedures are available in a much wider number of languages than in any other RDBMS in this comparison.
MySQL: A popular open source database manager (neither really relational nor a management system). Provides a simple non-standard subset of SQL for the interaction with various resources. Downside is that it does not do much integrity checking and does not enforce much integrity (valid dates include 0000-00-00 and 2004-02-31). Furthermore it will *truncate* numbers that are too large to fit in a number field making it unfit for any purpose where it must track money. It is more widely used than any other open source RDBMS.
FirebirdSQL. A good RDBMS designed really for Windows but ported over to UNIX/Linux. Fairly extensible and stable but largely undocumented. Lacks many of the data types available in all other databases listed here.
LedgerSMB: Open source Accounting/ERP
If at first you don't succeed, skydiving is not for you
Can you be more specific? How often does slashdot go down?
Slashdot has a subtle ``down'' state where they only serve static pages. It causes neat things to break like the RSS feed that I get for my home page (any request returns a static page).
Wikimedia Foundation also runs on a small cluster of MySQL servers
Perhaps you don't remember their recent outtage that took the entire thing off the internet for a day or two while they had to completely rebuild their database from backups. All of the mySQL apologists were quick to point out that databases should be expected to be all corrupt and stuff when they lose power. Users of real databases were amazed that anyone would think that.
-- The world is watching America, and America is watching TV.
We haven't had any serious MySQL load problems in over a year, with the exception of one targeted DDoS which wedged up our search DB slave for a while. Slashdot hasn't had any MySQL reliability problems since we moved to 4.0. Our master DB has been running the same version of 4.0.x since early 2003 and it just keeps going, it never crashes. Later versions of 4.0.x are probably more reliable, but we have no need to upgrade because it just works. The only time it went down was last month when the OS finally threw a kernel panic, which sucked, but wasn't MySQL's fault.
Anyway, the point someone was trying to make is that MySQL isn't ready for high-traffic enterprise sites, which I hope we can all agree is just silly. Slashdot's not even the best example, go look at Wikipedia, CraigsList, LiveJournal, Yahoo, Google, etc.
1: ANSI is "Hello World" MySQL is '0'
2: ANSI is error, and abort the inserting transaction. MySQL inserts 'Hell'.
Another case in point:
mysql> create table test (
-> test numeric(4,2));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into test (test) values (10000000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+--------+
| test |
+--------+
| 999.99 |
+--------+
1 row in set (0.00 sec)
So if this number was important and meant something (which it would in production) you just entered bad data into your database!
LedgerSMB: Open source Accounting/ERP