Comparing MySQL and PostgreSQL 2 902
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?
Get off it ScuttleMonkey (Score:4, Insightful)
Re:Get off it ScuttleMonkey (Score:4, Interesting)
No, it's like MySQL _sold_ them something.
There are (I expect) a large number of people reaing this who believe that SCO is not a company you should do business with.
It would be interesting to know why MySQL did business with SCO, maybe on principle they turn no customer away, or maybe a need for money overrode. The latter case might be a legitimate concern for the community.
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?
Re:Get off it ScuttleMonkey (Score:3, Interesting)
More precisely:
The joke's on SCO, they paid for something that very few companies have to pay for, to use in a product that very few companies will buy. Additionally, for those companies already own UnixWare or OpenUNIX, MySQL AB already provides installation instructions [mysql.com] and patches [mysql.com] for them. Finally, for the exceptionally lazy, SCO themselves provide a GPL'd version for you to download for free [sco.com]!
More on-t
Not exactly ... (Score:4, Insightful)
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.
Re:Not exactly ... (Score:4, Insightful)
I could do a press release about how I just bought a tube of toothpaste at the local Kroger. The wire service(s) would happily take my money and put the story on their distribution network(s). Big whoop.
But would Kroger co-release it? (Score:3, Interesting)
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
Re:But would Kroger co-release it? (Score:3, Funny)
But it would make a great story in the Onion...
"Kroger DEO David Dillon said that he was 'very pleased to have the opportunity to supply toothpaste and other oral hygiene products to Quarters, who is widely known for is impeccable white teeth and fresh breath.'"
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
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]
FalconSco 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
PostgreSQL SCO "relationship" (Score:5, Insightful)
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 [eweek.com] 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?
Yawn.... (Score:3, Insightful)
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.
Re:Another question (Score:3, Informative)
There are good reasons to pay someone for support, if the people you're paying know their stuff. If you're building enterprise level, mission critical data warehouses, you'll want immediate access to expert help when things go horribly wrong. And Sorbannes/Oxley reinforces that need.
For those seeking paid support, there are several companies working to do interesting things with Pg:
Re:Another question (Score:5, Interesting)
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.
Re:Another question (Score:5, Insightful)
The best approach IMO is somewhere in the middle. I think both extremes are boneheaded.
Your RDBMS is there to do three things:
1) Store data
2) Maintain data (i.e. triggers, check constraints, etc)
3) Present your data (views, possible some stored procedures, etc).
I generally try to avoid programming directly against stored procedures. If necessary, I will write a view and update rules/triggers (depending on the RDBMS) to make this appear like a table.
In general your data model logic should be entirely contained within and enforced by the database (Date's Central Rule). This does not mean that application function should be a stored procedure. Far from it. Instead, by doing things this way, you can do a fair bit of heavy lifting in the RDBMS where appropriate, and have your application logic in the application where it belongs.
For example, sending email from the database backend is not really the best solution to any problem I am aware of in large part because this would happen outside of transactional control. Instead, use a trigger to place an item in a queue that an external program can use to send it. This way if your transaction rolls back after the email is sent....
People on both extremes simply don't understand the features and limitations of the transactional and relational models.
Re:Another question (Score:5, Interesting)
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.
Re:Another question (Score:4, Interesting)
Let's just stop right here. The answer to this question was settled sometime around the introduction of the electric lightbulb, and it has little to do with programming paradigm.
Logic in the database is almost always a bad idea. It introduces complexity in source code management, release engineering, application de-centralization, etc. The database's code optimization is also rarely on-par with external langauges. It's also essential at times. There is a fine line to be drawn between performance needs and software engineering needs. As a very, very rough metric, based only on personal experience, I would say that you should place code into the database only when that code reduces the amount of data that must be moved to the application by at least and order of magnitude. This goes for everything from the most complex stored procedure to the simplest sort (though be careful to be aware of when sorts are free because of storage implementation, and don't be afraid to put conditional logic into code based on storage engine).
Re:Another question (Score:5, Insightful)
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.
Re:Another question (Score:5, Informative)
Re:Another question (Score:3, Informative)
I loved the CHAR type section, specifically the MySQL entry:Beautiful.
Re:Another question (Score:3, Funny)
Comparison of MySQL, PostgreSQL, Oracle, MS SQL... (Score:5, Informative)
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.
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.
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?
Re:Another question (Score:3, Insightful)
(secretly posting this to lure someone into
Re:Another question (Score:3, Informative)
Re:Another question (Score:4, Interesting)
Yes, you could use a cursor instead, but to avoid problems if the table gets updated while your cursor is open, you'd either have to lock the table or deal with errors from the server.
Re:Another question (Score:5, Interesting)
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.
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.
SP's are only the beginning (Score:5, Interesting)
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 [metatrontech.com] which has a MySQL to PostgreSQL migration guide.
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.
Re:MySQL vs. Oracle (Score:3, Insightful)
Because a project you download is hard-coded to it. (Mambo, some BBSes, eCommerce sites, etc.) I like PgSQL better than MySQL, but I keep having to use MySQL for the PHP projects I play with.
I must disagree. (Score:3, Insightful)
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
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.
Re:MySQL vs. Oracle (Score:3, Interesting)
Mind telling me how you're going to move ten terabytes of data around on a $10K server? With full replication (so you're going to need another server at a remote location), tens of millions of transactions every hour, with complete integrity checking and automatic failover to secondary and tertiary systems?
Because that's what McKesson does. I don't work there, just got a tour of the datacenter when I was looking for a job a few
Helllooo?? Editors?? (Score:4, Informative)
This sort of whoring-for-comments article should be a poll.
Also (Score:5, Funny)
Re:Also (Score:5, Funny)
Found one! :) (Score:3, Informative)
It was updated March '05.
Re:Found one! :) (Score:3, Informative)
If they're going to compare releases, compare apples to apples. Either pick stable releases or development releases. And FWIW, nobody that I know personally has been able to get MySQL 5.0 to run reliably, if they can even get it to launch. If we use MySQL for a project, it's 4.1.
Spatial databases (Score:5, Informative)
PostgreSQL is supreme A LOT (Score:4, Interesting)
From my point of view (web application developer, Ozone [ozoneframework.org] 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
Re:PostgreSQL is supreme A LOT (Score:3, Informative)
Re:PostgreSQL is supreme A LOT (Score:4, Funny)
You're right that InnoDB has supported row-level locking for some time now. PostgreSQL uses MVCC so that you don't need a lock at all most of the time.
Considering how often Slashdot goes down when they have a cluster of MySQL boxes for redundancy, that's hardly an endorsement of quality.
Slashdot uptime (Score:3, Interesting)
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 articl
Re:Slashdot uptime (Score:5, Informative)
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. [mysql.com]
Re:PostgreSQL is supreme A LOT (Score:5, Informative)
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.
MySQL vs. PostgreSQL -- Real World (Score:5, Informative)
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.
Re:MySQL vs. PostgreSQL -- Real World (Score:4, Informative)
PostgreSQL replication? is here [slony.info]. BSD licensed too.
Oh, and
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.
Re:I used to like MySQL (Score:3, Informative)
+----------+
| count(*) |
+----------+
| 93 |
+----------+
1 row in set (0.12 sec)
mysql> create table strings (stuff varchar(8));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into strings values ('abcdefghijk');
ERROR 1406 (22001): Data too long for column 'stuff' at row 1
mysql>
Re:I used to like MySQL (Score:4, Informative)
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.01 sec)
mysql>
Re:I used to like MySQL (Score:3, Informative)
I'm certainly no power user but Postgres strikes me as an extremely well featured DB. I use MSDE / MS SQL server at work for an app with transactions, stored procedures, triggers and views and expect it would be straightforward (not trivial but straightforward) to port it to
Don't forget Interbase/Firebird (Score:3, Informative)
Forget Interbase/Firebird (Score:3, Informative)
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
Re:Forget Interbase/Firebird (Score:5, Interesting)
But as you had a bad experience, and you link to your problem list, I thought I'd respond instead. Better to answer questions than just mod up friendlies.
- Database path: Yes, firebird supports aliases. Our app doesn't use them, but they're there.
- ISQL: I hear Oracle's SQL*Plus isn't much better. However, I use isql every once in a while, and I have command-history, backspacing, etc. available to me. From what I hear, it's more of a function of the shell you use (around isql) than isql itself. If you set up your environment properly, isql and its ilk automatically get command-history. (That's what I'm told, note. Anyone who can explain this is welcome to. I'm not a sysadmin.)
- Never seen it freeze.
- Corruption: we've had exactly one database issue, where it seems a backup/restore script ran in the middle of the day, restoring the database to its state from 4 hours earlier. In 4 years of use, with somewhere around 60 users in a medical clinic/insurance/billing environment, we've had no corruption. Using forced-writes is important, however. The careful-write strategy is really, really reliable, but it still can't protect you from faulty hard drives or operating systems that refuse to send data to the disk in the order requested (cf. Windows). M1 Abrams tank story, anyone?
- IBDataPump and other third-party tools exist for some of the other features you're interested in. I'm not sure I know how even I feel about some things only being offered by third-parties. Oracle's tools suck enough people buy other products
Feature-wise, and maybe target-audience-wise, Firebird and PostgreSQL are similar. Stored procedures, triggers, check constraints, MVCC (Postgresql seems to have copied MVCC off of Interbase, note), savepoints/nested (but not concurrent) sub-transactions, etc. It lacks a lot of the UDT (type) features of PostgreSQL (you can define domains, but not entirely new datatypes) -- note that Postgres was specifically designed with UDT's in mind. Firebird does support UDF (function) features though, and you can get some of the same flexibility that way if you're masochistic (save data in octet or blob fields and use UDF's to interpret the data). Pg also has neat SP language support, letting you write your SPs in a variety of languages -- Fb doesn't. Unlike Postgres, it's really easy to install, particularly on windows (that was a problem for Pg up until semi-recently) and it practically maintains itself. (Happily, the Pg team eventually got their vacuum, equivalent to Fb's sweep, to not take down the database, so Pg can now run 24/7 too.) Fyracle has been trying to make Firebird more Oracle-like in SP language support and some of Oracle's more interesting query abilities (CONNECT BY). Yes, I occasionally get feature-lust and look at other DBMS's. I don't need Oracle features, but Pg features would sometimes be nice. But I don't use Pg, so I don't know what annoyances it has that Pg users would be thinking about. Maybe it's all-around better, I don't know.
Both are really good projects, with their own strengths. I would say comparing Firebird and PostgreSQL is a much fairer comparison than Pg and MySQL or MySQL and Fb. Pg and Fb are more of a 'niche' comparison. MySQL has nowhere near the features of either of them, isn't nearly as safe, and just isn't designed with the same requirements in mind.
Every single experience I've had with MySQL has been one of "fixing" stuff for a MySQL user who just couldn't get things to work. Joins that wouldn't work (but should have), joins that were slow, data being eaten
Web Tools (Score:3, Insightful)
If there's a better web interface for Postgres than phpPgAdmin, let me know so I can try it.
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.
good one (Score:3, Funny)
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.
It really doesn't matter. (Score:3, Insightful)
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.
Re:It really doesn't matter. (Score:5, Informative)
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.
Re:It really doesn't matter. (Score:4, Insightful)
And go ahead, tell my that having corupt data doesn't matter. Then I'll throw sarbanes-oxley at you and laugh.
Sorry... (Score:4, Funny)
A small thread from the past (Score:3, Informative)
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:Just compared MySQL 4.0.12 vs PG 8.0.3 (Score:4, Informative)
extensions (Score:3, Interesting)
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.
Re:Been using MySQL, but... (Score:3, Informative)
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.
Re:Popularity (Score:4, Insightful)
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.
Re:Popularity (Score:4, Informative)
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.
Re:Popularity (Score:3, Insightful)
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 o
Answers: (Score:5, Informative)
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!
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.
Re:Mysql is very isp friendly (Score:4, Interesting)
Complete, utter bullshit.
PostgreSQL does *NOT* need to be "manually installed" for each user any more than MySQL does.
there are a ton of php and perl scripts and tools for users to use.
This is the reason why MySQL is more popular for ISPs - because there is a bunch of PHP code that runs only on it.
Its what ISP's prefer.
No, it's what ISPs offer. And they offer it because people ask for it.
Check Out PostgreSQL 8.1 Beta (Score:4, Informative)
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.
Re:Mysql is very isp friendly (Score:3, Insightful)
However if your a small isp with little to no support staff mysql is the easiest to install and configure for average home users and small business on a server farm. That is all I am saying and why mysql is incredibly popular. Its just what the ISP's love using and including by default.
The same reason Windows and Dos became popular. Its the OS OEM's love to include.
Re:Mysql is very isp friendly (Score:4, Informative)
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 [pgadmin.org].
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.
Re:popularity (Score:4, Insightful)
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.
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.
Re:The most interesting part of the old article... (Score:3, Funny)
Re:The most interesting part of the old article... (Score:3, Funny)
Re:The most interesting part of the old article... (Score:3, Funny)
As are you. :-p
Re:The most interesting part of the old article... (Score:3, Funny)
Re:The most interesting part of the old article... (Score:3, Funny)
Re:My point of view (Score:4, Informative)
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)
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.
Re:Haw haw (Score:5, Insightful)
Yes, I do. That's why I set the length in the first place. If I wanted to truncate the data, I'd tell the DB to do that.
if you REALLY need the logic to reject things like blank inputs in web forms then you should be doing that in your application logic anyway
This is the problem with MySQL's cheerleaders - they believe that the app designer should re-invent the wheel, rather than expecting the DB to do the stuff that it's supposed to do.
Why on earth should I have to write extra code to check each input field, when I should just be able to send the results to the DB, and return the error message to the client if it fails?
just trying to relay the idea that in the context that mysql is usually used, these small quirks don't have a large impact.
The only reason that people believe that they don't have a large impact is because they don't actually understand the *reasons* for the correct behaviour. The attitude is "well, I'm a programmer, so I'll just program around the problems", rather than expecting the DB to handle it (like it's supposed to.)
Re:Haw haw (Score:4, Insightful)
That is exactly the MySQL attitude. The problem is that my data tend to be important, or I wouldn't be putting them in a RDBMS. I'd rather the database assume the usual, safe thing - data should not silently truncated. If I want to throw away my data, I'll do it explicitly.
And filling in NOT NULLS is also handy for the same reason.... if you REALLY need the logic to reject things like blank inputs in web forms then you should be doing that in your application logic anyway....
What I REALLY need is for the database to pay attention to the constraints I specify. If I didn't care if these fields were blank, I wouldn't have said "NOT NULL".
I am by no means sticking up for non-conformity... just trying to relay the idea that in the context that mysql is usually used, these small quirks don't have a large impact.
That is not the context in which mysql.com is claiming their database is usually used. They claim they have a real database.
Re:My point of view (Score:3, Interesting)
Excellent relational support? Please. Does 5.0 fix the issue where an insert of an invlalid date into a date field will instead insert '00-00-0000' into the field instead of aborting, like real databases do? And what the fuck is the 00-00-0000 default anyway? That isn't even a valid date unl
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.
Re:My point of view (Score:5, Funny)
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.
Re:My point of view (Score:3, Insightful)
To be fair, Oracle targets the really high-end apps or performance where hiring a dedicated Oracle expert(s) is worth it.
I doubt even Postgre can match the performance, reliability, and scalability of Oracle. Only IBM DB2 can come close so far. Postre may compete with Sybase and SQL-Server's level.
Thus, you are comparing apples to oranges.
Although for read-only
Re:I have a small problem... (Score:3, Informative)
Re:What does postgres mean? (Score:4, Informative)