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?

Comments

Get off it ScuttleMonkey

[Anonymous Coward]

SCO bought a freakin license to include a copy of MySQL that's not GPL. It's not like SCO bought the company.

Re:Get off it ScuttleMonkey

[Saeed al-Sahaf]

No, it's like MySQL _sold_ them something.

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?

Another question

[Eightyford]

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?

Re:Another question

[Jerry]

PostgreSQL compares very well to Oracle.

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

[Dogtanian]

Oracle has the 'non benchmarking' clause that prevents you from doing that.

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

[ron_ivi]

This page is the best document I've seen comparing each of the majordatabases (Oracle, Postgresql, DB2, MySQL, SQL Server) not directly against each other, but against the SQL Standard. In cases where at least one of the databases differs from the standard, this guy's article shows both the SQL called for by the standard, and how each of the implementations may either follow or deviate from the standard.

Re:Another question

[Stinking Pig]

SQL Server gets a lot of flack on /., I'd be interested to know why? I've worked a fair amount with it, Oracle 8 and 9, Postgres, and a little bit of MySQL. I've also done extensive benchmark testing of SQL/Oracle/Postgres handling the same load on the same hardware (shh, don't tell Oracle :).

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

[dotgain]

Get over yourself. If a DBA fails to "recongnize their lack of skills", maybe they're doing just fine with the management tools. If his skills are so lacking he'll screw up his own database eventually.

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:Another question

[NickFortune]

Compares very well to Oracle? In what metric?

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

[grassbeetle]

No. He got it right the first time. Why on earth would you want your RDBMS vendor cramming their lousy procedural programming language down your throat? For the privilege of burning cycles for your application code on CPUs that you've paid your database vendor upwards of $10k per core for licenses? Or is it because your control-freak DBAs like the app code right up close to the data where they can micro-manage it. The only folks with a worse appreciation of programming languages and application design than sysadms are DBAs.

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

[einhverfr]

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.

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

[Aceticon]

I've worked for several years both creating programs inside the database and on a server layer outside it (and also just about every other layer).

I have to agree with grassbeetle above.

Software architecture-wise:
- You can't make a scalable architecture if you put everything in one single place (in this case the database).
- You will be hard-pressed to create a failure tolerant architecture if you stuff everything in a single point of failure.
- Databases are NOT application servers. They are designed with data storage and retrieval in mind, not reliable execution of complex business logic. Amongst other things databases do not make available in an easy and/or reliable way some of the standard application server functionality.
- All external components of the application (for example UIs) have to connect to the database. You're now stuck to using the connection protocols from the chosen database. This might cause all sort of problems with security, firewalls, use of asychronous messaging, availability of adaptors in the platform you are deploying your applications to, etc...
- Spliting your application accross several servers or in a multi-tiered geographical distribution is much harder.
- All coders have to have a good knowledge on how to work with the specific database you are using.
- Programing inside databases is not standartized. Different databases and indeed different versions of the same database have sometimes different versions of the same language or different libraries available. The language/libraries have not been so throughly used/tested/examined by a big user comunity (while for example standard C/Java/etc libraries have been thouroughly debugged in billions of man-hours of use). This means more library bugs and a lack of third party tools for software design and development inside the database.
- Facilities such as version control, source control, etc are either not available or difficult to use in a reliable manner.
- Availability of compatible 3rd party libraries or application modules is very, very restricted by comparison to NOT having your server side logic all inside the database.
- Forget about moving databases in the future. Also, simple migrating to a newer version of the database can be a nightmare.

Software design-wise, the design of the software will be strongly constrained by the internal structure of the database:
- Information flows will mostly have to be database-like information flows
- A true object oriented structure is pretty much impossible. At the most you can do weakly connected islands with an objecte oriented structure. If the database language you have to use is procedural forget about OO design.
- Server-side initiated connections to outside entities, thread control, ditributed transactions and other more advanced functionalities are pretty much impossible.
- Usage/integration with 3rd party libraries or application modules is very hard or even impossible.

Software programming-wise, and from my experience (mostly Oracle):
- The language sucks.
- The application libraries (not the DBA ones) suck big time.

Simply put, a software architect that puts all server-side logic inside the database is with this single choice removing almost all his other architecture options and creating/fortifying vendor lock-in of the application to the database itself and 3rd party tools and also of the development team itself by means of the knowledge experience they have/will gain with said database and said 3rd party tools.

Such a person should IMHO either be demoted to a place were he/she can't cause any damage or fired outright.

Helllooo?? Editors??

[wfberg]

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.

Also

[TheRaven64]

I have been wondering, which is better, vi or emacs?

Re:Also

[cnettel]

Regarding potential for being a RDBMS, I would vote for emacs. I'll leave it to you to decide if that's a good thing...

Re:Popularity

[mellon]

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.

Spatial databases

[MostlyHarmless]

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.

Re:popularity

[Bloater]

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.

PostgreSQL is supreme A LOT

[michalf]

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

Re:PostgreSQL is supreme A LOT

[pHDNgell]

Can you be more specific? How often does slashdot go down?

Slashdot has a subtle ``down'' state where they only serve static pages. It causes neat things to break like the RSS feed that I get for my home page (any request returns a static page).

Wikimedia Foundation also runs on a small cluster of MySQL servers

Perhaps you don't remember their recent outtage that took the entire thing off the internet for a day or two while they had to completely rebuild their database from backups. All of the mySQL apologists were quick to point out that databases should be expected to be all corrupt and stuff when they lose power. Users of real databases were amazed that anyone would think that.

I used to like MySQL

[caluml]

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.
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. :%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.
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.

Re:I used to like MySQL

[croddy]

mysql> select count(*) from users where id not in (select id from users where id%2 != 0);
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.01 sec)

mysql>

Re:My point of view

[einhe1t]

Parent is comparing non-current versions, and making up false "cons" for mysql, out of thin air...

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 ./ has been running on mysql + innodb for years. It also support clustering "out of the box".

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)

Not exactly ...

[khasim]

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.

Re:Not exactly ...

[Quarters]

Press releases have no bearing whatsoever on the level of business relationships. Press releases happen if someone (or some-company) has enough money (~$300US) to do a wire release and has something to say. They're just an advertising medium. Nothing more, nothing less.

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.

Re:My point of view

[scrutty]

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.

Heavy

[HadenT]

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.

Mysql is very isp friendly

[Billly Gates]

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.

Haw haw

[Safety Cap]

Parent is comparing non-current versions, and making up false "cons" for mysql, out of thin air...

5.0 has views, triggers, stored procedures etc, and it's still amazingly fast.

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

[schon]

Truncating varchars is handy for webforms... you don't want to reject the data..

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.)

Sco Partners With MySQL AB

[burnin1965]

Get your facts straight coward:

"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_ 948.html

burnin

Message bearer

[burnin1965]

True, but then again it also depends on who is bearing the news as well.

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_ 948.html

burnin

One thing to consider - collations and Unicode sup

[melted]

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.

The scoreboard

[ttfkam]

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.

Re:MySQL vs. Oracle

[Just Some Guy]

MySQL supports all of the Oracle features you need to build and operate an enterprise software system.

As of today, MySQL 4.1 is the current release. 5.0, the current development snapshot, is the first to support stored procedures. Since the choice today is between a tested system and stored procedures, it most certainly does not "support all the Oracle procedures [I] need to build and operate an enterprice software system".

Next year? Maybe. Right now? No way, according to mysql.com.

Re:MySQL vs. Oracle

[oGMo]

MySQL supports all of the Oracle features you need to build and operate an enterprise software system.

HAHA. Right. Tablespaces? Failover? High availability? Row-level locking? Stored procedures? Triggers? Multimaster replication? SQL conformity? I could go on, and on...

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).

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.

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!"

MySQL performs pretty much the same as Oracle out of the box (and I think it is easier to tune).

Yes, and anyone who's using a db tuned out of the box isn't doing significant work.

[snip whining about blobs]

Blah, blah, Oracle is hard. Get a DBA and a real developer. This is what they're paid for.

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.

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 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.

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

[ttfkam]

1. MySQL supports all of the Oracle features you need to build and operate an enterprise software system.

Ummm... no. MySQL does not have user-defined data types, object-relational extensions, full support for the CHECK constraint (a big one IMHO), views in a stable release, updatable views, rules, stored procedures in a stable release, synonyms, support for more than one autoincrement column per table, automatic conversion of code pages between client and server, nested transactions, complete trigger support, access privilege grouping, access to multiple databases in one session, multi-master replication, gateways to other DBMSs, XML data and transformation tools, and better tools for recovery from failures.

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.

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).

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.

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.

See my answer to number 2.

4. MySQL performs pretty much the same as Oracle out of the box (and I think it is easier to tune).

Only in environments that MySQL can handle. Oracle can handle scenarios where MySQL cannot run at all let alone run fast.

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.)

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.

SCO issued a press release.

[falconwolf]

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

Falcon

Just compared MySQL 4.0.12 vs PG 8.0.3

[adturner]

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.

Re:It really doesn't matter.

[tzanger]

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.

MySQL vs. PostgreSQL -- Real World

[philovivero]

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.

Comparison of MySQL, PostgreSQL, Oracle, MS SQL...

[einhverfr]

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.

Re:Forget Interbase/Firebird

[Unordained]

I had mod points, and as a Firebird user, I was going to mod up the grandparent -- Firebird is very often ignored despite a host of positive features.

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 ... heck, why bother? Just develop a good RDBMS with a good API, and let others fight it out? (That's an open question.)

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 ... And then there's reliability ... ugh. MySQL just wasn't designed with data integrity in mind, while Pg and Fb were. "Foreign key constraints can be

SP's are only the beginning

[einhverfr]

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.

PostgreSQL SCO "relationship"

[tangledweb]

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?

Re:My point of view

[tangledweb]

So this is what passes for "Score 5: Informative" now?

Invent an imaginary version of postgres to compare to a real version of MySQL, then spout some fictional cons. In that case:

I'm using both, but mainly Postgres. From what I can tell:

Postgres 7.841
Pros:
- Supports african dialects such as Kaliharinese
- Adds extra features when it detects that the user is a Womble
- Compatible with IP/feline
Cons:
- Runs slowly if you try to quieten your hard drives with banana peels

MySQL 4.841
Pros:
- Written entirely by Ooompa Loompas
- Discourages the use of Perl
Cons:
- Supports animal testing. Drips of MySQL are places in the eyes of penguins to check for irritation.
- Shows signs of money contamination, which brings hippies out in a rash
- Does not support transactions.

Re:Slashdot uptime

[jamie]

The 500s you see are almost always due to load on the webheads (rendering pages takes a lot of CPU) and occasionally to planned restarts (we toast a few hundred connections every time we upgrade the code, basically because we're too lazy to gracefully integrate restarts with the LB proxy). Sometimes due to a DDoS or network outages.

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.

Answers:

[einhverfr]

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!