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?
SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company.
This is a prefect flame fest topic - great scheduling for a vacation day. I will venture to guess the posts will be well into the 1000's Now what does it matter if Mysql partnered up with SCO. SCO as a O/S provider is history may a well extract a scraps of meat from the bones.
Sure it's slashdot and we all love free software, but how do these two compare with oracle, sql server and other non-free db's?
Religion for nerds. Stuff that really matters
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
I'm using both, but mainly Postgres. From what I can tell:
... well, bad)
Postgres 7.5
Pros:
- Supports stored procedures
- Supports triggers
- Supports schemas
Cons:
- Heavy on resources
MySQL 4.0
Pros:
- Fast
- Easier to find PHP scripts that use it
Cons:
- Bad relational support (and yes, I know about InnoDB, but even then, it's a bit
- No stored procedures/triggers
- Easily corrupted by crashes.
I sense much beer in you. Beer leads to intoxication, intoxication leads to hangover. Hangover leads to sobering.
I have been wondering, which is better, vi or emacs?
I am TheRaven on Soylent News
You are such a noob :-)
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.
MySQL is like Microsoft. It's not entirely compatible with the standard, but everybody is using it, so if you want to use their software, you have to use it too. I have a copy of PostgreSQL and a copy of MySQL on my server, because Wikipedia doesn't work with PostgreSQL. I presume this is because the developers started working with MySQL back in the bad old days when it was _really_ incompatible, and their code now contains dependencies on MySQL.
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.
http://www.geocities.com.nyud.net:8090/mailsoftwar e42/db/
It was updated March '05.
That's probably because those features are part of PostgreSQL and is the main argument for why people believe that PostgreSQL is overtaking MySQL. Also the fact the PostgreSQL can run PL/SQL with only some modifications, and visa-versa.
Running with Linux for over 20 years!
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.
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
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
So are you :)
I hate to burst your bubble here, but ALL dbms's are slightly different from eachother, this is largly because the SQL spec leaves room for these kind of things.
It's not like there is a HUGE difference, as long as no dbms specific procedural language was used (think plsql (oracle)) it's pretty trivial to port an application from one dbms to the other
The biggest differences are usually pretty subtile and indeed rather frustrating, but by no means hard to solve.
The biggest and most 'incompatible' difference between postgres and mysql is the autoincrement field really, the rest is just small fish to fix.
Fighting for peace is like fucking for virginity
If I go to the store and buy a copy of MSOffice, that's one thing.
If I get a site license from Microsoft, that's something else.
If Bill Gates and I do a press release about our new partnership, that's an entirely different thing.
SCO and MySQL AB did the press release thing. That's not the same as SCO buying a license to distribute.
Except for big iron (where DB2 dominates) and Micorosft environments (where SQL server dominates), Oracle is the dominant player.
I recently moved my deployments from Oracle to MySQL because:
1. MySQL supports all of the Oracle features you need to build and operate an enterprise software system.
2. MySQL's new administration tools are significantly better than Oracle's out of the box tools (This is why a year ago I refused to use MySQL for production, and now I've switched everything).
3. MySQL is much easier to manage. I don't know anybody who runs a heavily loaded Oracle server in production without spending significant $$$ on DBAs and commercial tools. I feel quite comfortable doing this with MySQL.
4. MySQL performs pretty much the same as Oracle out of the box (and I think it is easier to tune).
5. MySQL's supposed gotchas pale in to comparison to Oracle's. When I first used MySQL BLOBs it simply worked. I opened up the administration programs and I could actually see the images in the database. It was so beautiful I wanted to cry. I can't count the number of times I went through Oracle BLOB/CLOB hell with different platforms. (Not just getting them in there, but actually getting them to work with third party applications which is the real pain.)
I think that anybody deploying Oracle for non-Oracle applications is going to have to very seriously consider MySQL if for no other reason than all the DBA salaries you can get rid of.
If you want to buld a $1M cluser, stick with Oracle (for now). If you want to run application specifically designed by (or for) Oracle, stick with Oracle. Otherwise, switch at the first opportunity.
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.
So far I've stuck with MySQL for most of my projects since phpMyAdmin is so much better than phpPgAdmin. I can almost always get a web-based database tool running on the platform I'm developing for.
If there's a better web interface for Postgres than phpPgAdmin, let me know so I can try it.
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.
The one good thing I have to say about mysql is that its multi-user friendly for hundreds of accounts.
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.
http://saveie6.com/
Would a Kroger executive talk enthusiastically about your new "partnership" with them?
... but it changes when another company is quoted as saying anything more than "we sold them a license and we'll sell you one too!"
Usually, companies don't want to be seen publicly supporting nutcases who try to make a news story about buying some toothpaste.
SCO can have the press conferences it wants and tell everyone whatever they want
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.
I've used both databases for years and they both have their place. One thing that is rarely mentioned about Postgresql is the Table inheritance. I have yet to find this feature in another RDMS. Anyone know of a database out there that supports this feature?
It allows you to create a table that inherits the fields of a parent table. Each time you insert data into the child table the parent also gets a record. You can use regular expressions to select from all child tables. Very cool and useful.
"Since then both databases have evolved to wherever they are today." Thank you for that profound insight..
As are you. :-p
How do you think I feel?
Xenu loves you!
Where-as you're a long-timer? ;-)
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
True, but then again it also depends on who is bearing the news as well.
_ 948.html
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
To each his own, of course, and the situation may indeed have improved. I don't see any clear advantage that it has over PostgreSQL, though, and I doubt it'll ever gain much momentum.
Dewey, what part of this looks like authorities should be involved?
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.
I write my db code to abstract all my queries away from the rest of my program. You access the queries I've writen via a XML-RPC interface. That way if you need to switch db all you have to do is rewrite your queries and none of your app code needs to change.
Issues like speed, resource usage, sql features, etc really don't matter very much as for 99% of db applications it just makes no difference at all and for the 1% it does then you'd better hire someone that knows the differences in dbs without having to look it up on Slashdot.
Most of the time it is better just to use the basics. I see people doing EVERYTHING in the db. Stored procedures, adding 1 + 1, massice complicated joins that'd be easy to do in anything other than SQL, etc. A big mistake. The db is the single hardest portion of your application server to replicate and load balance (even with the db supporting things like clustering). It's better to write your glue code in a normal language and just abstract that glue code as a sepperate service and use the db just for storing and retrieving data.
At what price learning? At what cost wisdom? The price is a man's peace of mind, and the cost is his life.
...does this count as a dupe? Or is there a statute of limitations thing going on here?
~~~~~ BigLig2? You mean there's another one of me?
Comparing MySQL and PostgreSQL 2
Is that the only way that MySQL can look favorable? By comparing to version 2 of Postgres?
fnord
God Fucking Damnit
I once asked on slashdot about why people use MySQL and how does it compares to PostGreSQL. Got a bunch of interesting responses.
Prescriptive grammar:linguistics
MySQL is much easier to manage. I don't know anybody who runs a heavily loaded Oracle server in production without spending significant $$$ on DBAs and commercial tools. I feel quite comfortable doing this with MySQL.
In other words, you're not actually running an enterprise-scale software system (which would require significant $$$, professional DBAs, and tools no matter WHAT database product you're using). You're running a single-man shop.
This is a great troll -- all of your points are sufficiently broad as to be impossible to prove or disprove either way. They're all obvious personal opinions but phrased as facts. Who's to say that any arbitrary enterprise software system can be satisfied with MySQL's features, or another's isn't? Or that you find MySQL's administration tools better than Oracle's (which I find to be therichest out there). Or that any "gotchas" you've had with BLOBs and CLOBs seem purely anecdotal.
The number of features that Oracle has over MySQL is simply staggering, as is its ability to robustly handle enormous concurrent loads. Its clustering support, backup & recovery abilities, and query optimizer are second-to-none. I'm glad you've found a cheap and better alternative, but I hardly think it's applicable to all.
-Stu
Have you recompiled MySQL? If so, have you changed anything in the source, and are you going to sell this modified version? If so, you must include the source of your modified version with any sales, under the terms of the GPL. If not, you're probably okay without switching at all. This is probably the case if you can ship MySQL separately from whatever application that you're developing, and if you can use an existing MySQL database without installing a new one yourself. (obligatory IANAL bit here)
The World Wide Web is dying. Soon, we shall have only the Internet.
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?
Sorry billysara, I must have beat you by a few minutes. :)
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 hate to burst your bubble here, but ALL dbms's are slightly different from eachother, this is largly because the SQL spec leaves room for these kind of things."
I hate to burst your bubble, but the differences have nothing to do with a poorly designed spec. They have everything to do with companies either being too lazy, or too stubborn to adjust to and adhere to the specs. Even M$, who created the ODBC standard fails to adhere 100% to it in both Access and MS SQL.
In their defense however, DBMS's have often evolved far faster than the specs could keep up resulting in dozens of different ways to do something the specs didn't originally cover. However, my forgiveness ends right where the spec catches up and then the dbms developers fail to add compatibility to their product. The fear that compatibility will leave room open for customers to migrate to a competing product is exactly why the USA is about 2 to 3 years behind in technology right now. Go to NTT Docomo's website and look at their newest line of phones and you will note two very distinct trends:
#1 The lowest model blows away all US phones.
#2 They all share the same baseline standard design...which forces them to compete in the non-standard areas...which rewards the consumer with a consistant design and interface and innovation.
Wake me up when companies (or even OSS project managers) in the US stop screwing their customers with proprietary interfaces designed to lock in their customer base. I really don't measure a product's worth by the number of users it has....otherwise I'd still be using IE.
I've dirtied my hands writing poetry, for the sake of seduction; that is, for the sake of a useful cause. --Dostoevsky
Short story, mysql.com's interpretation of the GPL is frankly a lot more strict then mine or my reading of the FSF's FAQ on the GPL. If it wasn't for that, I'd still be using MySQL for my company's application since I'm more familar with it.
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.
Above all things... this is one of my favorite features of PostgreSQL. MySQL has nothing of the sort. Procedural Languages... yum. I recently showed on my blog how I could interact with an instance of DRb from inside of PostgreSQL. How cool is that? MySQL has nothing like this. I'd also guess that a majority of the applications that use MySQL could use SQLite instead and as soon as that becomes more popular, we'll see less MySQL usage.
Robby Russell
PLANET ARGON
Robby on Rails
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
After 4 years of using both PostgreSQL and MySQL, I'd say that one of the biggest differences between them is their extensibility.
If PG lacks a feature, you have a very good chance of finding a script or an extension which implements equivalent functionality. Materialized views, ordering by different locales and hierarchical queries are some examples of this.
On the other hand, if MySQL doesn't have a feature you need, you're pretty much screwed.
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
I don't know the details about the internal workings of either one, but they both bug me.
./configure
For years I've hated MySQL's install process. Call me old fashioned but I just want to
make
make install
It makes upgrading and maintaining my servers really nice. MySQL is just an annoyance.
Today I tried PostgresSQL, and I was happy to see a typical install process. Then I finished and I was blown away that it requires a shell in order to run. I understand that daemons need user accounts, but it just doesn't seem secure to give a daemon a shell.
IMO MySQL is a pain, and PostgresSQL is a joke.
There's more to uptime than the box(es) being up. Netcraft doesn't count when Slashdot has returned 500 status codes because the back-end (read: database) fell over. While it speaks volumes about the reliability of Apache over long periods of time, Netcraft uptimes mean didley in this case for MySQL.
Don't get me wrong. Slashdot's reliability has been steadily improving over the years. Perhaps this is an indication of the stability of MySQL improving over the same interval.
----
Also remember that older articles are archived -- removed from active database queries/updates and rendered to flat text files. Once again, it speaks highly of Apache rather than MySQL.
- I don't need to go outside, my CRT tan'll do me just fine.
And Friendster was also slower than tar for a very long time. In addition, when you make changes in Friendster, you may have to wait for those changes to become visible to others. They have some very aggressive caching.
On second thought, who knows? Maybe it used to. That would explain the constant downtime and speed problems.
- I don't need to go outside, my CRT tan'll do me just fine.
No. It is the difference between a bucket you *know* is empty and a bucket that contains an unknown.
LedgerSMB: Open source Accounting/ERP
When I write code I write a backend to support multiple databases. I have to if I want to sell any product.
Sqlite for the quick install 'embedded version'
Mysql/Pgsql for those that use it
MSDE/SQLServer for Microsoft types
Oracle for large businesss.
ODBC for everything else
Guess which one is the hardest to get working? Luckily Mysql support is EOL now... the license issues have killed it (can't use anything over mysql 3.23 and that doesn't have proper variable binding).
> 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
The case-insensitive comparison is done like this:
select * from T1, T2 where lower(x)=lower(y);
If you have normal indexes on x and y, this won't use them and you'll get a sequential table scan. To be useful for this query, you need indexes on lower(x) and lower(y), e.g.
CREATE INDEX idx1 ON T1 (lower(x));
CREATE INDEX idx2 ON T2 (lower(y));
This is described on the reference page for CREATE INDEX.
I suggest that you ask on the mailing list next time you find a "showstopper" problem - people really are using PostgreSQL for serious applications, and an obvious thing like this would have been fixed long ago.
That's the primary reason, as far as I can see.
If you're not part of the solution, you're part of the precipitate.
Breakfast served all day!
No other RDBMS has had the number or severity of viruses target it as MS SQL Server. Need I remind people of the SQL-Slammer worm? And this was not about blank SA passwords (unlike previous worms).
SQL-Server holds the dubious honor of being the host for the virus which spread around the world fastest.
LedgerSMB: Open source Accounting/ERP
> If you're trying to insert invlaid data, you're the only one to blame.
That would be fine if the database were compiled into the application and strongly type-checked by the compiler. Even small software can't be reliably checked by humans, and the database is connected to at runtime so the compiler can't really do it.
With schemas this can be done better. The compiler could know the types that the database supports and the software is compiled against the schema specification, then as you connect ot the database, the schema is checked for agreement and the compiler had assured the types (and the database author had provided classes to compile against). Then you can be *reasonably* safe, but shit can still happen (database version changes, nearly compatible competitor is substituted, etc). When that shit happens, you need to know that the database has its shovel ready. Its because of the loose connection between the application and database that the database must provide this assurance and it can't just be left to the application compiler to check everything at compile time.
If anybody here has actually studied database theory, you know that the relational model allows you to do a lot of cool things. For instance:
#1 updateable views (these are like subroutines calls in programming language: they allow you to abstract and refactor)
#2 arbitrary database contraints (these let you say, for instance, that tuple X refers to a tuple in relation A OR relation B, but not both. or that the string in attribute Z must not contain spaces).
#3 real type constraints: can't store integers into a string attribute, and vice-versa
#4 user-defined types: need to store IP addresses, JPEGs, XML data, etc, and declare constraints.
#5 arbitrary relational expressions. for example, I'd like to join two tables and then project the result, then join the result with something else. Compare with arithmatic: most languages let you nest and group expressions arbitrarily: A + ((B*C+2) * D)
Once I design my database using relational principles, I have to find an actual physical database to implement it with. Since there are no relational databases today (except maybe Dataphor), I have to settle for an SQL database. Now. Which should I choose? Open source is usually more flexible, let's see how MySQL and PostgreSQL fare:
MySQL
#1: no updateable views. In fact, no views at all in any release version.
#2: No triggers to implement these.
#3: ugh, no
#4: no
#5: yes, finally they have sub-selects, which is a wordy way of writing out relational expressions, but that's the best we can do with SQL.
Let's see how PostgreSQL meets my needs:
#1: no updateable views, but you can intercept inserts and updates and send them to your own code.. good enough.
#2: simulated with triggers, we can do it.
#3: yes
#4: yes
#5: yes, sub-selects here too, and Postgres had them longer
So, if the relational model is a "10", and MySQL is a "2", then Postgres is at least a "3" or "4". I.e., MySQL is a SUBSET of PostgreSQL. Maybe if you're hacking together a blog or LiveJournal or something, you can away without having this stuff (heck, you can probably just use flat files). But for any real database app that involves money or requires accuracy, I'll stick PostgreSQL.
Remember folks, the purpose of a database is DATA INTEGRITY, not data storage. Once you figure this out, like Codd did in the 70's, you'll be a much better programmer, and you'll laugh at stuff like MySQL (just like Lisp programmers laugh at BASIC programmers).
The DBMS is not validating the user's input. That is the application's job. The DBMS is validating that the data an application is trying to store conforms to the schema. The DBMS is your last hope of having correct data. Your application could be blowing chunks all over the place. As long as the data is correct, then the problem can be fixed.
In a small web environment this distinction is harder to see. There is usually only one version of the client application in existence. In a larger system you can't always trust the application. Servers in a large server farm may have slightly different versions (e.g. if updates are applied in stages to avoid crashing the entire system because something was missed in testing). Different applications sharing the data (e.g. common data but different needs: sales and management). You might also have a "thick" client application the versions of which are often very difficult to control. If one in-use version validates incorrectly then you can't trust the data anymore, even if the majority of in-use versions are correct.
I'm not sure why I don't see this mentioned yet, but this wikipedia article has a great grid that compares major database systems.
I call bullshit.
... from dual'), but which MySQL appears to make a specialty of (and appears to have little inclination to fix).
While it may not be the best design, SQL *is* the abstraction layer of choice for using an RDBMS. As written it's weird, inconsistent, potentially unparseable and arguably incomplete, but that doesn't mean it's inefficient (arguments about whether NULL values should be allowed notwithstanding).
I've written SQL for MySQL, Informix (IDS & SE), Postgresql and Oracle. Supporting all of them at once is essentially impossible (on date handling alone), but using the 'right kind of quotes', standard SQL syntax and type names for simple scalars and so on makes the differences minimal in most areas.
The remainder of the differences range from substantial missing features (stored procedures, foreign keys, triggers) which might rule a specific DBMS out for a given design, and stupid misfeatures which most databases have in some measure (I'll give Oracle a guernsey for 'select
I've been in the position of taking code originally written for Informix or MySQL and having to port it to PostgresQL or Oracle, and the more standard it looked the easier it was. Why port it if it was working fine? Well, *we* had more than one customer; try telling them that they should install MySQL alongside their Oracle server...
You refer to built-in functions, different syntax, different data types etc.: SQL provides a standard library of SQL functions, and most good DBMSes allow you to define your own in SQL, C or Java, as well as 'private' languages like PlSQL and PlPgSQL; differing syntax should have no impact on how efficient a query is; and while the standard SQL types are limited, they are sufficient for a wide range of applications, and where there's a genuine need the major players have responded with very similar features (e.g., int8 and 'serial' types) that map well onto each other.
If you care, and your RDBMS supports views and stored procedures, then there's an excellent chance that you can provide a clean, efficient (within the limits of the DBMS) and consistent interface across a number of RDBMS's; it may take more time than writing a straight-to-MySQL (or whatever) app might, but it will be more portable and maintainable (and for complex apps, it may reduce your coding time in any event).
huiac at internode.on.net
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?
It's true that MySQL does not have syntax for object-oriented queries. The object-relational model is significantly different from the relational model; there are a lot of applications where it's not needed.
I guess neither Postgres nor MySQL supports native storage of XML. However, it might not be too hard to implement. 6 years ago, neither database could store GIS data; today, they both can.
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
The nested loop is a type of query plan not a type of SQL Statement. It is hence a feature of an implimentation and not of the language.
For example.
Suppose I do:
SELECT * from mytable_1 where f_key IN (select p_key from mytable_2 where condition IS TRUE);
The simplest way to try to do this is to select every p_key from mytable_2 where condition IS TRUE and then query table_1 for the row in which mytable_1.f_key = mytable_2.p_key
This is the nested loop. It wasn't too long ago when nested loops where the default query plan for all types of joins. Now (for obvious reasons) they are the plan of last resort. More likely, PostgreSQL would choose a merge join for this type of subquery, or so we would hope.
Subqueries used to be popular when all joins were handled via nested loops because you could force the RDBMS engine to run fewer loops by forcing the inner-most subquery to be the one returning the fewest rows. Nowadays, all really good RDBMS's rewrite subqueries as if they were joins and will try to do other query plans on them instead.*
Does this make sense?
My favorite nested loop nightmare was when PostgreSQL was running a nested loop against an empty table thousands of times and eating up CPU activity doing it. Three seconds or so of doing something entirely meaningless.....
* This is not always possible. I have written many queries looking for possible duplicate entries into a database. These often run nested loop query plans because of the complexity of the search conditions in the self join.
LedgerSMB: Open source Accounting/ERP
Care to name a couple? DB2 comes from System/R. Oracle seems to be home grown. Sybase came indirectly from Britton Lee, but was mostly written from scratch. SQL Server came from Sybase. Certainly RTI's product was based on Ingres.
The original coder of Ingres later started a follow on project called Postgres and when SQL was standardised it became PostgreSQL.
Hate to break it to you, but there was a HELL of a lot more than one person coding Ingres, over a very long time.
Joe Kalash
Chief Programmer, Ingres Project
1982-1985
(I may still have some business cards to prove it)
I run quite a big forum, most active in its kind in Finland. One day in June the site just didn't let anyone sign in or show threads or messages. So I login:
[x@x forum]$ mysql -p x
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Didn't find any fields in table 'backup_yabbse_instant_messages'
Didn't find any fields in table 'backup_yabbse_log_activity'
Didn't find any fields in table 'backup_yabbse_log_errors'
Didn't find any fields in table 'yabbse_members'
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 168027 to server version: 4.1.11-Debian_2woody1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from yabbse_members;
ERROR 1016 (HY000): Can't open file: 'yabbse_members.MYI' (errno: 145)
mysql>
After that I repaired my members table, losing all the rows. Luckily backups were recent enough, losing only 7 latest registered members (of some thousands).
After that, I occasionally get MySQL error messages from the e-mail warning system, but repair table helped and didn't lose data.
?SYNTAX ERROR
MySQL MaxDB (formerly known as SAPDB) is comparable with Postgresql in terms of features. It suffers a similar lack of recognition with hosting companies and php developers. Ingres is another "enterprise" ready db that is available open source.
Who supports XA Transactions?
MySQL - Not yet (planned in 5.0.12?)
SAPDB/MAXDB - Yes (limitations?)
Postgresql - No - in the works (8.1/8.2?)
Firebird - Yes
Berkeley DB - Yes
Ingres - Yes (limitations?)
Pricing vs Proprietary
Oracle: $58K CDN per CPU + 10% maint
DB2: $55K CDN per CPU + 10% for maint
MS SQL Server: $3K CDN per year per server (enterprise edition)
MySQL MAX DB: $1,800 CDN per CPU + 10% maint
Postgresql: Free + Support
Ingres: Free + support
Why do people depend on the database?
Easy, it's centralized and easy to manage. This is the same reason that anyone uses a large robust server to begin with.
It is far easier to lock down one database than n+1 applications and ad hoc query users. Abstraction and modularization is good. This is computer science.
A Pirate and a Puritan look the same on a balance sheet.
This is the single most common response to people pointing out MySQLs data validation issues, and I think it pretty much sums up both the attitude of the typical MySQL developer as well as the reason for MySQLs popularity: People who totally misunderstand both the theory and importance of databases. Amusingly, this same sector of people (because this argument essentially becomes "don't write buggy code, every") have produced some of the most atrociously written and flawed applications to ever be used on the Internet. Except for sendmail.