PostgreSQL vs. MySQL comparison

prostoalex writes "Ever find yourself wondering which open source database is the best tool for the job? Well, wonder no more, and let your tax dollars do the work in the form of Fermi National Accelerator Laboratory publishing this unbiased review of MySQL vs. PostgreSQL. After reading it, however, it seems that MySQL ranks the same or better on most of the accounts." My poor sleepy eyes misread the date of posting on here; caveat that this is more then 15 months old.

Comments

Foreign Keys

[P(0)(!P(k) P(k 1))]

From TFA:

Having foreign keys [...] can all be very attractive in PostgreSql—if you need them and you will make any use of them.

Foreign keys are nice, I have to say; I implement them in mysql anyway, in spite of the fact that they're ignored for MyISAM.

Re:Foreign Keys

[mwanaheri]

Foreign keys are more than nice, they are essential. Unless, maybe you don't care about the integrity of your data or want to make the necessary checks in their application. The latter should keep their eyes down and their mouth shut if the talk is about 'speed' of any rdbms, off course.

Re:Foreign Keys

[ShieldW0lf]

This is unbiased? Give me a break.

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2 and has most of their concerns addressed in that release, whereas MySQL is still at 5.0?

MySQL is a great database, if you need clustering but not referencial integrity or ACID compliance, that is.

Re:Foreign Keys

[Anonymous Coward]

If you're not using FKs.. Do you really need either? All you need is a file store right? What's sleepy cat doing?

Re:Foreign Keys

[Anonymous Coward]

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2 and has most of their concerns addressed in that release, whereas MySQL is still at 5.0? That's because the comparison is dated Feb 2005.

Re:Foreign Keys

[Tet]

WTF is with putting up an "unbiased comparison" between Postgres 7.2 and MySQL 5.0 when Postgres is now up to 8.2

That'd be because the article was written in 2005. Unbiased? Maybe. Vague, unscientific and out of date? Definitely.

Re:Foreign Keys

[Phil John]

MySQL is a great database, if you need clustering but not referencial(sic) integrity or ACID compliance, that is.

Is that the same referential integrity and ACID compliance afforded by using INNOdb as your table type in MySQL? ;o)

Re:Foreign Keys

[Brummund]

Foreign keys aren't "nice", THEY ARE ESSENTIAL TO A RDBMS.

It is the same thinking that probably made the retards at MySQL AB make a datatype that accepts 30th February as a date. (At least did, a few years ago.) Why EVEN include a datetime datatype if it isnt capable of the SIMPLEST validations ever.

Yes, I'm fuming. Those MySQL retards has made a generation of programmers think they can do SQL when they manage to put crap into MySQL. Gahhh, I hope their puny webapps will haunt them down sometime.

(I was once searching for a simple webbased forum, and tested phpnuke. It had the following gem to display the 5 most recent articles in the database:

1. "SELECT * FROM ARTICLES ORDER BY ID DESC"
2. Retrieve all articles from the database
3. Then a for loop printing out the 5 first entries.

They basically transferred all data in the articles database everytime, just to iterate over the 5 first rows. Gahhhhhh)

Re:Foreign Keys

[JustOK]

Foreign keys are nice, I have to say;
I implement them in mysql anyway,

Couldn't finish with a rhyme on MyISAM?

Re:Foreign Keys

[Tony Hoyle]

Foreign keys don't speed anything up, they just add an extra layer of checks on your database. Your app should be checking itself anyway.

It's the subselects that get me - without them you have to jump through a lot of hoops. The sentence quoted basically translates as 'as long as you are only storing your CD collection and not doing anything serious with a database, then use mysql'.

TFA also fails to mention that mysql cannot be used in commercial development without paying $200 per client - which makes it more expensive than most other solutions (except maybe oracle, and even they have cheap licenses for some uses).

Re:Foreign Keys

[CaptainZapp]

Foreign keys are more than nice, they are essential.

Bingo!

It doesn't cease to amaze me, when the Mysql croud argues that "you don't really need those pesky integrity stuff, it just slows down the database."

Guess what guys; You're dead wrong!

Any DBA worth his salary will enforce data integrity on the lowest possible level, which means constraints (however implemented) on the object level.

Sure, you can let your coders in Bengaluru ensure that the primary key is unique instead of just applying a unique index and the same goes for referential constraints between tables. You can implement them in the application just fine until somebody overlooks some minor detail in the code and you're royally fucked!

Again! Foreign keys or triggers are not "niceties". They are essential in implementing an industry strength database; period!

Re:Foreign Keys

[bubulubugoth]

Using MyISAM databases is like using dbase with a sql wrapper, that kind of security u have.

I u want all tne neat features of a full rdbms, use InnoDB instead.

Compraing, for example Mysql MyISAM vs Postgress is a huge mistake, Mysql Innodb and MaxDB are almost a diferent RDBMS from Mysql.

Re:Foreign Keys

[Sparr0]

MySQL has no restrictions on commercial development. They have restrictions on non-GPL distribution. Just like every other GPL-ed product on the planet. Nice try.

Re:Foreign Keys

[hey!]

This kind of entanglement of logical design (foreign keys) with phsyical design (ISAM) is precisely what a database management system is supposed to do.

It's just like separation of concerns in web site design. You want to leave graphic design to artists and programming to programmers, and to be able to vary each (within limits) somewhat freely.

The same SOC principle applies to database applications.

Foreign key constraints are declarative specifications of a behavior; either you need the behavior or you don't. If you need it, then the decision to use one or the other disk format should not force you to implement it in your program.

If a browser's implementation of CSS forced you to embed font tags in HTML, anybody'd see that's a broken CSS implementation. If a RDBMS forces you to enforce foreign key limitations in application code, it's equally broken.

Re:Foreign Keys

[Tony Hoyle]

1. "SELECT * FROM ARTICLES ORDER BY ID DESC"
2. Retrieve all articles from the database
3. Then a for loop printing out the 5 first entries.

They basically transferred all data in the articles database everytime, just to iterate over the 5 first rows. Gahhhhhh)


Depends if it was cross-database or not, and whether that 'retrieve all articles' really did that or simply created the recordset/cursor.

SQL is a bit fuzzy once you get beyond simple selects... On one database you might want 'SELECT TOP 5 * ...' on another you might want 'SELECT ... LIMIT 5', or even 'SELECT ... WHERE ROWNUM6'. Or you could just use a forward only cursor - provided the DB backend doesn't try to read the whole thing into memory to implement it...

Re:Foreign Keys

[walt-sjc]

People coding shitty SQL is independent of their database of choice. MySQL is (IMHO) easier to install, configure, and use than postgres which just makes it more common to use, but MySQL is not responsible for shitty SQL in poorly written PHP apps.

Re:Foreign Keys

[Dunbal]

Couldn't finish with a rhyme on MyISAM?

      You have to think like Dr. Seuss, MyISAM I am I am I am

Re:Foreign Keys

[mwanaheri]

>Foreign keys don't speed anything up, they just add an extra layer of checks on your database. Right, they even make the dbms slower. But the dbms certainly does it faster than the application you write. So, to rely on the checks being made in the application results in a waste of speed on the application side. If I don't care about the speed in the application, why make a fuss about the speed of the rdbms? By the way: I rather have things reliable than fast. Subselects are something I also heavily use (and thus mainly stay away from MySQL) although I think it's better to use views for queries performed more than once in a while. Probably one of the main reasons for the spread of MySQL is the fact that is is frequently pre-installed.

Re:Foreign Keys

[Tony Hoyle]

Untrue.

The client library is GPL. That means you cannot create a commercial program that uses it without using the commercial licensed version. Which is $200 per client

You can't even create a library and not ship mysql - the mysql site is very clear that they consider distributing a program that *uses* mysql as being exactly the same as distributing mysql itself:

http://www.mysql.com/company/legal/licensing/comme rcial-license.html

Typical examples of MySQL distribution include: ...
        * Selling software that requires customers to install MySQL themselves on their own machines.

Specifically:

        * If you develop and distribute a commercial application and as part of utilizing your application, the end-user must download a copy of MySQL; for each derivative work, you (or, in some cases, your end-user) need a commercial license for the MySQL server and/or MySQL client libraries.

This makes mysql unusable for anything except large products. Our entire product only cost $70 for the single user version. No way in hell we're upping the price by $200 a copy.

Re:Foreign Keys

[Linker3000]

Could it be coincidence that Digg had a 'dated' article on this earlier today - I can't check as Digg is currently down, but did Hemos 'nick' this from Digg?

I'd hate to think that Digg, Fark, Slashdot, Boing Boing etc. are nothing more than a big news circle-jerk now. (well, more than they are already!)

Re:Foreign Keys

[Tony Hoyle]

An app can do its checks with full knowlege of the structure of data it's writing, and often it's just the comparison of a couple of integers anyway and have no impact on speed. You don't want to rely solely on the DB to do that... you end up having to handle a lot of nasty exception cases. Far better to avoid them first. Put foreign keys in, but definately don't make them your first line of defence.

Re:Foreign Keys

[Brummund]

A database allowing even simple datatypes to contain crap which is totally inconsistent with any calendar in use for the last 5000 years is responsible for some of the crap .

Re:Foreign Keys

[Branko]

Your app should be checking itself anyway.

Actually it shouldn't (in this context). Typically, one database will have several client applications attached to it. If data consistency is not checked at DB level, then:

• Bug in single application might compromise the data consistency of the whole system.
• You must keep all of your applications precisely synchronized.
• You are repeating the job of implementing the same consistency logic across all applications instead of implementing it only once - in database.
• Implementing these sorts of checks can be difficult to do correctly at the application level in a concurrent environment typical for a DBMS.
• Data consistency at DB level is directly supported by modeling tools, so you can plan for it and visualize it early enough to spot problems and communicate it to the other team members more easily.

Re:Foreign Keys

[Brummund]

Have you tried casting a returned column which contains a date like '1900-0-0 18:30' (contained in a datetime field) to DateTime in Java from the resultset?

Unless you cannot assume that a datetime contained in the RDBMS is in fact a valid datetime, you will end up with a huge mess.

But I guess that is why I don't work with MySQL or web programmers anymore. :=)

Re:Foreign Keys

[lysdexia]

Although the nun smacked my hams
I gleefully clutched MyISAM

That pretty much scans, I think.

Re:Foreign Keys

[Eivind Eklund]

No, it is the same referential integrity offered by

1. Declaring all your foreign keys
2. Marking all your MySQL tables as InnoDB, as otherwise (1) is silently ignored
3. Ensuring that you have specific indexes added for all your columns used as foreign key targets (or was that sources, or both?), as otherwise (1) is silently ignored.
4. Ensuring that your MySQL is compiled with InnoDB support, as otherwise (2) is silently ignored

In other words, the MySQL referential integrity is a very different referential integrity, one that you cannot trivially see from the table declarations, only by carefully investigating the surrounding conditions.

Eivind.

Re:Foreign Keys

[Smidge204]

I wans't aware that "commercial application" and "GPL licensed" were mutually exclusive. From the page you linked:

The Commercial License is an agreement with MySQL AB for organizations that do not want to release their application source code. Commercially licensed customers get a commercially supported product with assurances from MySQL. Commercially licensed users are also free from the requirement of making their own application open source.

When your application is not licensed under either the GPL-compatible Free Software License as defined by the Free Software Foundation or approved by OSI, and you intend to or you may distribute MySQL software, you must first obtain a commercial license to the MySQL product.

Emphasis mine. In other words, You don't have to pay the $200 if your project is itself compliant with the GPL or similar license scheme.

"Comply with the GPL or pay us $200 to legally use our code or libraries" is not the same as saying "You have to pay us $200 if you plan to sell software you made using our code or libraries."
=Smidge=

Re:Foreign Keys

[Anonymous Coward]

Any DBA worth his salary will enforce data integrity on the lowest possible level

Yeah... because he's a DBA. And a programmer will enforce them in code because he's a programmer. And someone who is or has both will enforce them wherever it makes the most sense to. That is NOT always the database. The maintenance costs are higher if there are constraints in the database, because it is duplication - your application still has to make the exact same constraints in order to function properly itself in all situations.

Re:Foreign Keys

[vadim_t]

Well, I sure hope you never work on anything serious.

The database's function is to provide a RELIABLE storage for your data. Part of the whole reliability thing is making sure crap can't get in, because once it's there everything goes to heck.

For instance, let's take a shopping cart. Can an order be for a negative quantity? If your app doesn't work that way (it could, using a negative amount for returns for example), and you still allow it in the DB, then all your reporting goes to heck, as SELECT SUM... now returns the wrong thing.

A proper database is set up in such a way that every piece of data in it makese sense. This means for instance not having things like orders hanging around without in the void without being linked to some client. This is something easily ensured by foreign keys. Otherwise you have an utter mess - the total of the orders in the database doesn't match the sum of the orders of all clients!

If you put your checks in the database, you have a guarantee that when somebody else codes another frontend to it (say, you had a website and now are making a special version for PDAs), if the application does the wrong thing, the database simply won't let it happen. This may cost a bit of speed, but I assure you that peace, your sanity and your ASS (if you have a boss and he's got any sense, he's not going to like it at ALL if it turns out that reports don't match reality, and that reality can't be even easily extracted) is far, far more valuable.

Re:Foreign Keys

[maxume]

Can't create a *closed source* commercial application that uses the gpl client software. You can of course sell a gpl application commercially. I totally understand that you might not want to, but you actually can, if you want to. See e.g., Sun, Redhat, TrollTech, etc.

Re:Foreign Keys

[JensenDied]

well, out of those four I only frequent /. and occasionally fark. but other sites i visit that are more focus on certain areas tend to show the same articles (or ones that reference the same incidents at the least) several hours earlier most of the time when i notice them

Re:Foreign Keys

[Trifthen]

I think the concern here was that even 15 months ago, Postgres was at least in the 8.0 tree, a vast improvement over the 7.4 tree which itself offered not insignificant advances over 7.2 and 7.3. The linked table of pros/cons for each database is dated February 2005, and browsing the postgres archives informs me that 8.0.1 was released by that point. If they waited two months, 8.0.2 would have been available. As it stands, the 8.0 tree was deprecated back in November of 2005, and the future now sits squarely on 8.2 after 8.1 experienced a short life of roughly a year.

The truth of the matter is that postgres is a rapidly fluxuating target. I remember waiting five years for mysql 5.0 to finally see the light of day, and during that time postgres has truly experienced a ridiculous flurry of revisions and tweaks sometimes requiring alterations to their core storage format. That they do this all while providing full ACID compliance is a Godsend.

I don't begrudge MySQL their success, but the roots of that database were thanks to simplicity and speed, like where SQLite is now. It's also an evolving product, but I think its lost the original focus in the mad rush of (friendly) competition all products eventually engage.

Re:Foreign Keys

[Phil John]

1. MySQL isn't the only database where you have to declare foreign keys, so that one's a non issue. I don't want a database to "guess" what my FK constraints are, I want to tell it explicitly.
2. True, but most of the tools I use to create DB schemas do this automatically now (phpMyAdmin et al)
3. It's a good idea to have indexes on foreign keys, as you'll often be joining on them. As for silently ignoring, I've never seen that - I get an error message if I try to add a foreign key with no indexes created.
4. Not so much of an issue these days, every build I've used recently from ones for Mac OS X and those for Fedora/ES/CentOS always have it compiled in. Even cheaper hosting accounts now very often have InnoDB support compiled into their MySQL installs.

Don't get me wrong, MySQL is far from perfect, but if used properly (like any tool) it can power some very cool stuff.

We always make sure that our apps work on multiple database servers as we realise our clients don't need a one-size-fits-all solution. We used to have to work around MySQL's limitations, but these days that's no longer as big an issue.

Re:Foreign Keys

[walt-sjc]

Well, true, much like car that allows you to run into a concrete wall at 120 MPH is partially responsible for the death of the driver.

When you look at the previous example you provided, PostgreSQL would have ALSO allowed the same shitty programming. It's pretty trollish to now drum up other flaws in MySQL. Yes, MySQL does have a lot of flaws. I don't think anyone is disputing that, but most of those flaws and limitations are known and can be worked around (such as doing client-side validation on dates.) If MySQL doesn't provide all the database functionality you need, don't use it, but it does work well enough for many many people and applications.

Re:Foreign Keys

[ThePiMan2003]

This only works if you are the only person touching the database. If you are working on any large scale application where multiple different programs use your database ensuring referential integrity is a must. Sure if only one application is touching your data you can forget about all the nice things that databases do, and gain a considerable speed advantage, but at that point why not just use flat files they are even faster.

Re:Foreign Keys

[Omnifarious]

I consider foreign key constraints to be like strong typing or array bounds checking. It can quite useful, especially if you have a lot of people working different bodies of code that all call eachother. But, you can get a heck of a lot done without it.

And of course (like with array bounds checking) you can shoot yourself in the foot in a major way if you make a mistake when you don't have foreign key constraints.

Re:Foreign Keys

[bberens]

I didn't think you could sell GPL software, only support. I thought with GPL it also had to be free as in beer. Am I wrong?

Re:Foreign Keys

[Anonymous Coward]

MySQL is a great database, if you need clustering but not referencial integrity or ACID compliance, that is.

...which is not a scenario which never comes up, as a developer. For websites and the like, that is far closer to the reality of the situation. (I know defending MySQL is taboo however, and I've seen others make this point more elegantly than I am willing to spend time doing at the moment..)

To be honest, my biggest personal "pet peeve"* is on that list as well: "\d" vs. "show tables". And to be fair, this is a problem that arises from SQL being a collection of so many different standards, that aren't (?) accessible as a reference card to the average database application developer. Instead, you end up using the manual for the DBMS you're using as a cheat sheet... and I find MySQL's syntax, where (presumably at least one of the two) varies from the standard, to be far more intuitive. This is a useful "feature", as a developer.

* and by pet peeve, I do not suggest that this is "as important as" things like ACID.. but it is one of the (many) things that keep MySQL in the "good enough for 90% of my projects" category.

Re:Foreign Keys

[CaptainZapp]

Sure if only one application is touching your data you can forget about all the nice things that databases do

Not even then do I believe that data integrity and - consistency should be up to the application. It's just a matter of Murphys law. Someday something will go wrong with application level consistency enforcement. I would outright refuse to take a job as a DBA if I can't implement constraints in databases which are under my responsibility. As a matter of fact I deem application level constraints an unprofessional approach to good database culture, to put it mildly.

Re:Foreign Keys

[Anonymous Coward]

Foreign keys are more than nice, they are essential.

More accurately, foreign keys are a trade-off.

For example, foreign keys force you to delete all the kids before deleting their parent. But in "laissez-faire" mode (no FK enforcement), you can delete the parent more quickly. Of course, this leaves orphans. But sometimes the orphans do no harm except take up space, and it's possible that the faster delete of the parent is preferred.

It's a classic time-space trade-off.

Re:Foreign Keys

[Crizp]

Yes. You can sell it for as much as you like. Just remember to make the source available :)

Re:Foreign Keys

[cloudmaster]

Umm, I'm pretty sure that MySQL had had subselects for several years. It's not clear if you knew that or not.

Re:Foreign Keys

[insignis]

As both a DBA and programmer, I enforce referential integrity at the database level. I cannot fathom why you'd implement it at a code level--if you don't trust your DBMS, why are you using it? The database is the only common chokehold in a multi-user, multi-application environment. To implement it at an application level, you'd have to try to somehow synchronize communication between all those apps, some of which you might not maintain, and this would only be complicated by each app instance having its own transaction open. The database is already handling those transactions, which makes enforcing referential integrity easiest to do at the database level.

Re:Foreign Keys

[Calinous]

When you create a database, you standardize on the way the content is correct. Then, you standardize the way you can add/change/remove data. For this, you can have some stored procedures.
      In order to keep the stored procedures simple, you then move some of the work, which will be done when triggers are triggered.

      Can you use the database as just a collection of tables, and enforcing the client to make the changes? Yes. But this way, you need transactions: if you sell mainboards, K7S5A, and have lots of orders - you can't get any more K7S5a, so you change the item to K7S5A+. If you have a stored procedure, you call it - change all K7S5A in orders in K7S5A+. If you don't, you change some things, start updating the waiting orders, and your computer crashes - your database will be left in a state that's not correct. When your money depend on the correct status of the database, you lose.

Re:Foreign Keys

[emurphy42]

It's worth pointing out that the parent is not being sarcastic. You can sell it for whatever price you like - but you still have to make the source available, and you can't place any restrictions on your customers selling copies for whatever price they like. In practice, this tends to drive the cost of GPLed software down to $0.

Re:Foreign Keys

[anothy]

sort of. you can sell GPL software, technically, but it's not really a sustainable business: you're required to ship the source with it, and anyone you ship it to can resell it or give it away.

Re:Foreign Keys

[Eivind Eklund]

(1) wasn't meant as a problem; it's the only reasonable step. The problem is that MySQL silently mess up in so many cases. When I have a database, I do not want it to silently mess up. This is doubly true for foreign key constraints, as it is non-obvious when these are messed up. Whether it is a good idea or not to have an index on a particular column is irrelevant: What I am protesting is SILENT IGNORE. This seems to be something that's missed by MySQL fundamentalists, who always come up with some set of random excuses addressing something else.

I also happen to believe I am better qualified than anybody else for selecting what indexes I want in a particular database I'm designing, which none of you others know the purpose of nor the update frequency of nor the join frequency of. It's a good rule of thumb; it's a lousy requirement.

Oh, and I'm perfectly aware that MySQL can power cool stuff - I have used it a ton myself (as an inherited database too expensive to replace, mostly). That doesn't mean that it doesn't suck compared to PostgreSQL (in my experience), and IMO is popular mostly because of being insecure by default (thus easy to install), being incompatible with the rest in subtly icky ways ("embrance and extend"), and due to semi-falsified benchmarks a long time ago (MySQL AB published only the benchmarks where they were best, varying what benchmarks they displayed by what database they were comparing against, giving the impression that the they were "as good or better" in all performance areas.)

Eivind.

Re:Foreign Keys

[Bilbo]

Frack! Where are all my good Mod Points when I need them???

Mod parent up.

Re:Foreign Keys

[Jahz]

It doesn't cease to amaze me, when the Mysql croud argues that "you don't really need those pesky integrity stuff, it just slows down the database."

What are you whining about? MySQL 5.0 - released October 2005 - supports foreign key contraints just fine.

Note the *one* sentence that was added by the slashdot mod stated this was 15 months out of date. Oct 2005 is 14 months ago. Next time, before you insult a large group of developers, get your facts straight.

Re:Foreign Keys

[anothy]

good clarification. but still: does this really hold water? i mean, unless they're using a modified GPL or whatever (is the GPL open-source?), they don't really get to dictate the terms. "derivative work", for example, isn't something they get to define. if i'm shipping a closed-source app that simply requires MySQL be also installed, and not something that's statically linked to their binaries, where's the "derivative work" come in?

Re:Foreign Keys

[ThePiMan2003]

Yes, but I assumed in that situation there is no DBA just people who are misusing a database.

Re:Foreign Keys

[jadavis]

The maintenance costs are higher if there are constraints in the database, because it is duplication

The maintenance costs are usually lower, and the reason for that is *when* you catch the error. If the database enforces some simple constraints, you catch the error *before* it goes into your database, and you know exactly which application tried to insert bad data. It's the best kind of error report a programmer can see.

If you do everything at the application level, any kind of bug can result in bad data being inserted. When a later point in your app finds the bad data, you have no idea where it came from. Good luck tracking that down. The part that inserted the bad data might not have even been written by you, maybe it can't be replaced by you, and maybe you don't even have the source to it. Maybe it's due to a security flaw.

Not only that, but some constraints are nearly impossible to enforce from the application without going way overboard on locking. UNIQUE constraints are a good example.

The "duplication" argument is just not true. The constraints offer your application an API in a way: it shows the app developer the nature of the data they can get from the database, and the nature of the information you can add to your database. In an application, almost all non-static functions do some sanity checking on their inputs, and by your definition that would be "duplication". However, it might be almost impossible for the caller to know it would cause an error, and there are so many callers that, if you don't do sanity checking, it would be impossible to trace the error backwards. It's exactly the same with a database, unless your application is so simple that tracing backwards is possible (i.e. only a couple of points can modify data). How would you like it if API calls didn't return errors? After all, it's just duplicating code, since you should check the data first anyway. And comments are the ultimate in "duplication"; I'm sure you don't write any of those, right?

Errors from constraint violations are a part of normal operating procedure for databases; a database error does not necessarily indicate an application bug.

Re:Foreign Keys

[poot_rootbeer]

Foreign keys don't speed anything up, they just add an extra layer of checks on your database.

Correct. That extra layer of checks will probably actually slow things down a bit.

But foreign keys aren't about performance. They're about data integrity, which I would hope every database administrator or developer is more concerned with anyway. It doesn't matter how many requests/second your DBMS can handle if the data is fuxxored.

Your app should be checking itself anyway.

Yes, it should be catching "foreign key constraint violation" exceptions thrown by the DB interface and handling them appropriately. I hope that's what you meant.

Re:Foreign Keys

[WindBourne]

due to semi-falsified benchmarks a long time ago (MySQL AB published only the benchmarks where they were best, varying what benchmarks they displayed by what database they were comparing against, giving the impression that the they were "as good or better" in all performance areas.)

Careful there. Caveat( I am a big postgres user ). I have seen postgres do the same. In particular, one that I remember was back in pg 5, where they had it showing neck and neck with Oracle. The reason? Because it was run through the odbc connection with small queries. It pissed me off when they came back saying that it was as fast as Oracle. That is the same stunt that MS does with SQL server to claim that it is as fast as Oracle or DB2 (not even close in a real-world).

Re:Foreign Keys

[CaptainZapp]

Yes, but I assumed in that situation there is no DBA just people who are misusing a database.

One might argue that in such a situation database level integrity enforcement is even more important. :)

Re:Foreign Keys

[raynet]

You could use the ODBC/JDBC/WhatNotBC loophole like Extensis does with Portfolio Server. Their Mac OS X version only works with MySQL but they don't have to GPL their application because it uses ODBC and not the GPL'd client library (and requires users to download and install MySQL/MyODBC manually).

Re:Foreign Keys

[dubious9]

I didn't think you could sell GPL software, only support. I thought with GPL it also had to be free as in beer. Am I wrong? Yep, your wrong. It's a common misunderstanding though. You can charge 14 billionty dollars for GPL'ed software, you just have to be able to provide the source code for said software for a nominal fee.

Re:Foreign Keys

[CaptainZapp]

The truth of the matter is that postgres is a rapidly fluxuating target.

What amazes me about the speed of evolvment is the fact that they don't seem to sacrifice quality in the bargain. (yes shit happened if I recall correctly, but rarely and under quite esotheric circumstances)

Great job, Postgresql team!

Re:Foreign Keys

[dubious9]

Isn't this technically a restriction that isn't allowed by the GPL? The GPL makes the distinction of "mere aggregation" as opposed to combination.

Doesn't distributing a completely separate program that only communicates with MySql via JDBC over TCP constitute "mere aggregation"?

From the GPL FAQ:

By contrast, pipes, sockets and command-line arguments are communication mechanisms normally used between two separate programs. So when they are used for communication, the modules normally are separate programs. But if the semantics of the communication are intimate enough, exchanging complex internal data structures, that too could be a basis to consider the two parts as combined into a larger program. It doesn't seem like MySql would be able to restrict distribution in the case.

Re:Foreign Keys

[Anonymous Coward]

After deleting a row left dangling foreign keys (yes, I declared them as such), I check at the application level.

Re:Foreign Keys

[localman]

Having worked on a "serious" app or two, I'm not 100% sure I agree with the logic. I actually like to allow certain types of crap in my DB -- let me tell you why: because it provides a trail. For example, in an order processing system lets say you have to insert the order, the items, and the credit card charge. DBA 101 would say this should be a transaction so that all or nothing goes through, right? That way you've got your "clean" database.

However, I'd absolutely rather have messy data: imagine some type of glitch occurs (yes, unavoidable when working on anything with "serious" volume) and you end up performing the credit card charge but some part of the insert fails. In that case I want a partial transaction because there really was a partial transaction. It will aid in identifiying that something happened, and also in figuring out what it was.

Yes, I do log errors elsewhere. But if you've worked on anything "serious" you'll know that there's always an error case that can come up aside from what you're able to log well.

I've come accross other examples, like making inventory records that don't have a foreign key because it's better than having no record at all.

I guess it comes down to this philosophy: I use the database to record a reality. Reality can have messy stuff. I'd rather record that messiness and then identify it and work to improve the processes than to ignore the messiness and have a pure but inaccurate database.

Flame away...

Re:Foreign Keys

[localman]

That webforum example is funny and sad... but what does that have to do with MySQL?

Cheers.

Re:Foreign Keys

[thedave]

It is a little sad: digg, fark, slashdot, boing. All circle and jerk, no climax.

Re:Foreign Keys

[Anonymous Coward]

Yeah, now wake me up when

  foo INTEGER CHECK (foo in (1,2));

is working.

data integrity my 455.

Re:Foreign Keys

[Anonymous Coward]

ALTER TABLE

Ok. Let's see.

"Postgres supports ALTER TABLE to some extent. You can ADD COLUMN, RENAME COLUMN and RENAME TABLE.

"MySQL has all options in ALTER TABLE - you can ADD column, DROP it, RENAME or CHANGE its type on the fly - very good feature for busy servers, when you don't want to lock the entire database to dump it, change definition and reload it back.

LOL!!!!! MySQL LOCKS the bloody tables in ALTER TABLE!!! It rewrites them completely!! PostgreSQL does the same thing (except for ordering fields - fields always appended) but it NEVER rewrites data. When you drop a column or change type, it will just cast the query to new type or ignore the given data.

Guess which one is faster for 200,000,000 rows? Oh, and if you want to do what MySQL does, you can do that too!

ALTER TABLE foo ALTER COLUMN blah TYPE TO blows;
UPDATE foo SET blah=blah;
VACUUM;

That should do it. Now, if you have 200,000,000 rows, the second the third line could take a while. As long as MySQL does it in fact. probably not a good thing on them "busy websites"!

Re:Foreign Keys

[thedave]

Don't get to excited about accurate calendaring. Our calendar has only been in use for a few hundred years.

Very few (except special purpose) databases do ancient dates correctly.

AD alone is highly revised. BC is all over the map,

This bug report for DEC VMS is amazing in its analysis:

D I G I T A L

SPR ANSWER FORM

SPR NO. 11-60903

SYSTEM VERSION PRODUCT VERSION COMPONENT
SOFTWARE: VAX/VMS V3.2 VAX/VMS V3.2 Run-Time Library

PROBLEM:

The LIB$DAY Run-Time Library service "incorrectly" assumes the year
2000 is a leap year.

RESPONSE:

Thank you for your forward-looking SPR.

Various system services, such as SYS$ASCTIM assume that the year 2000
will be a leap year. Although one can never be sure of what will
happen at some future time, there is strong historical precedent for
presuming that the present Gregorian calendar will still be in affect
by the year 2000. Since we also hope that VMS will still be around by
then, we have chosen to adhere to these precedents.

The purpose of a calendar is to reckon time in advance, to show how
many days have to elapse until a certain event takes place in the
future, such as the harvest or the release of VMS V4. The earliest
calendars, naturally, were crude and tended to be based upon the
seasons or the lunar cycle.

The calendar of the Assyrians, for example, was based upon the phases
of the moon. They knew that a lunation (the time from one full moon
to the next) was 29 1/2 days long, so their lunar year had a duration
of 364 days. This fell short of the solar year by about 11 days.
(The exact time for the solar year is approximately 365 days, 5 hours,
48 minutes, and 46 seconds.) After 3 years, such a lunar calendar
would be off by a whole month, so the Assyrians added an extra month
from time to time to keep their calendar in synchronization with the
seasons.

The best approximation that was possible in antiquity was a 19-year
period, with 7 of these 19 years having 13 months (leap months). This
scheme was adopted as the basis for the religious calendar used by the
Jews. (The Arabs also used this calendar until Mohammed forbade
shifting from 12 months to 13 months.)

When Rome emerged as a world power, the difficulties of making a
calendar were well known, but the Romans complicated their lives
because of their superstition that even numbers were unlucky. Hence
their months were 29 or 31 days long, with the exception of February,
which had 28 days. Every second year, the Roman calendar included an
extra month called Mercedonius of 22 or 23 days to keep up with the
solar year.

Even this algorithm was very poor, so that in 45 BC, Caesar, advised
by the astronomer Sosigenes, ordered a sweeping reform. By imperial
decree, one year was made 445 days long to bring the calendar back in
step with the seasons. The new calendar, similar to the one we now
use was called the Julian calendar (named after Julius Caesar). It's
months were 30 or 31 days in length and every fourth year was made a
leap year (having 366 days). Caesar also decreed that the year would
start with the first of January, not the vernal equinox in late March.

Caesar's year was 11 1/2 minutes short of the calculations recommended
by Sosigenes and eventually the date of the vernal equinox began to
drift. Roger Bacon became alarmed and sent a note to Pope Clement IV,
who apparently was not impressed. Pope Sixtus IV later became
convinced that another reform was neede

Re:Foreign Keys

[raju1kabir]

I didn't think you could sell GPL software, only support. I thought with GPL it also had to be free as in beer. Am I wrong?

Priced a retail copy of Red Hat Enterprise Server recently? A whole lotta GPL software in there.

Re:Foreign Keys

[Anonymous Coward]

No problem, use MIT license and NEVER release the code -:)

Re:Foreign Keys

[tacocat]

When are you non-database types going to stop saying "Your app should be checking itself anyway."

This is an insanely inneficient method of execution. It's also highly presumptive.

Inneficient: If you are going to insert a record you have to first check to make sure it's not there. Then if it is there you have to change your INSERT to an UPDATE. This is dumb. Some databases do a INSERT OR UPDATE. but if they don't, it's faster to do an INSERT, handle failure, UPDATE. Alternatively -- UPDATE and INSERT on ZERO ROWS CHANGED. This means you have to run less than 2 queries on average. Your app should check method guarantees two SQL statements are executed every single time.

Dumb. Say you check for a record to exist. You get a "NO" answer. While you are preparing and executing your next INSERT, some other process or a thread inserts that same record into the databse. Now you have an error and you still don't know what to do. In short, you're in a pretty bad way.

Presumptive. In all my years of living I've never seen any company happy with the only interface to the data being through the application interface. Especially with a database on the back end. The business types, Marketing in partitular, love to screw with database information to try and identify trends, patterns, and correlations between the customer behaviour, product representation, and sales metrics. It is presumptive that the application can safely contain all of the business logic and you can assume that no one will ever come in the back end and change something -- thereby breaking all your business rules.

The other consideration is that the business logic contained in a database is going to run a heck of a lot faster on the database than anything you can dream up in your application, unless the application is written in C. Databases are generally written in C/C++. Applications are generally written in Java,Perl,Python,Ruby. None of these can compete with C. Add to that the fact that databases have been designed for years to do only one thing -- manage data. Do you seriously think you can out perform a decade of database optimization in a ruby script?

If you are going to base an application on data it would be useful to know how to capitalize on the features of a database rather than trying to repeat it. At the very least, you are less likely to introduce bugs.

Re:Foreign Keys

[Doctor Memory]

sometimes the orphans do no harm except take up space, and it's possible that the faster delete of the parent is preferred.

It's a classic time-space trade-off. I disagree. In a classic time/space trade-off, you don't run the risk of producing incorrect results (e.g., someone runs an aggregate function on the detail records, but some of those are invalid because they don't have a parent). Granted, you could argue that "well, in that case the orphans do harm, so that's not what I'm talking about", but there's no way you can guarantee that someone, somewhere, won't need to do something like that.

I think a better example in your case would be to simply add a "valid" flag (or possibly another value for a status field, if the record already has one). Set the valid flag to 'N' and reap all the invalid records and their children as part of your overnight processing. It's a lot cheaper to check WHERE PARENT.VALID = 'Y' than WHERE PARENT.ID IN (SELECT ID FROM PARENT p2). In fact, if you deleted the parents within the same transaction where you perform the check, you might not even be able to use that logic, depending on your transaction isolation level.

Defeating referential integrity is always a Bad Idea. If you really need the speed increase (say during a mass import), then it's usually better to drop the constraint completely, then add it again afterwards. Import code is pretty much the only place where you'll want to enforce constraints at the application level.

Re:Foreign Keys

[tacocat]

Additionally, databases generally can do this faster than the application code. I can say this because databases are written in C and optimized and debugged for years. Applications are rarely (relatively) written in C and have not been debugged for years when released.

This is something that actually really pisses me off about Ruby, Rails, and ActiveRecord. ActiveRecord is an insane violation of everything that a database has been built to do. It breaks consistency, violates keys, ignores so many rules... And it's beats the crap out of a database to do what a database is designed to do and can handle much faster.

This is regardless of the flame wars of Postgres vs MySQL.

Re:Foreign Keys

[partenon]

If you put your checks in the database, you have a guarantee that when somebody else codes another frontend to it (say, you had a website and now are making a special version for PDAs) That's the reason OOP is there :-) Database abstraction, DAO's and Model's (POxO/DTO/whatever) can be responsible for storing/validating data and reused in other app's (as webservices, libraries, ...). So, the database *can* be flexible, lightweight and have consistent data, based on *what* consistent means in the context. Imagine a bank app for managing accounts. You can have N account types (customer, broker, corporate, ...) Each account type have it's own set of "valid data" constraints. And even inside the same "account type", the validation can change (if an account was opened before date XX, it's permitted to do bla). You just can't do that using simple foreign keys. And if you want to ensure your data is consistent, you *will* need Stored Procedures and Triggers.

So, to me, *database* is only a place to store *data*, not *information*. Of course, for a Bank app, I validate the data on both sides (app level and database level), because it's worth. But on almost all other apps, I would have only the app level validation.

Re:Foreign Keys

[raju1kabir]

In no case does it make sense to read through the entire result set rather than just the first 5 items.

Re:Foreign Keys

[Anonymous Coward]

You use MySQL, don't you?

Re:Foreign Keys

[Anonymous Coward]

$200 has got to be full retail.

Unless I'm mistaken, Adobe Bridge (looking at MacOS version here...) is tapping MySQL for its metatagging, and I can't believe that $200 of the price of each upgrade is going for a MySQL client.

Re:Foreign Keys

[drew]

Perhaps(*), but how many of the performance comparisons that show MySQL being so much faster than everything else use InnoDB? The MySQL guys like to have their cake and eat it too in this regard. Most performance comparisons I've seen show MySQL without all of the referential integrity enabled, but whenever anyone points that out, the MySQL fans cover their ears and say "InnoDB! InnoDB! I'm not listening..."

(* - This is even assuming that I trust the MySQL developers to know enough about database integrity to implement InnoDB properly, which I don't.)

Re:Foreign Keys

[jaydonnell]

You are correct about everything except the most important part. I'm not sure your wrong, but I question it. That is that it's typical to have multiple applications attached to the same db. I would wager that the vast majority of databases in use today are used by websites that have a single major "application" connecting to them.

Re:Foreign Keys

[j_snare]

I'm not sure, but I think it might be safe to say that the parent was referring to developers who *use* MySQL.

The development team working on MySQL probably is in favor of getting the integrity constraints to work right. I doubt they could be experienced database programmers and think otherwise.

However, I have to admit that I've noticed many developers that use MySQL seem to think that integrity constraints are not useful, and often concentrate on the performance aspect more than anything, saying that the application should enforce the constraints.

For example, there are two posts here and here that give various arguments against putting integrity constraints in the database.

Re:Foreign Keys

[ShieldW0lf]

I don't agree with you regarding the use of triggers, I find they end up making optimization a lot more difficult. But I do agree regarding stored procs.

Personally, I tend to steer towards procs that are complex internally with a simple external signature rather than using triggers. I find triggers are a real pain in the ass when you're trying to figure out how to optimize a slow running query.

When I develop, I usually put all my data access functionality into stored procedures, deny access to all tables and views, then selectively grant access to the stored procs.

Makes securing your data a lot easier, prevents most sql injection attacks, avoids the whole "magic quotes" mess, makes centrally managing your data a lot easier, and keeps the code-jockeys from screwing things up when they're in a rush.

It's also a big advantage when you're changing your schema. You don't even need to touch the codebase in a lot of cases.

On top of all this, it's more efficient. You send a lot less data back and forth across the wire, which most people don't think of until things start to bog down and it's time to move your db off the webserver and onto its own box on the network. And most dbs support some level or another of precompilation, which saves even more resources.

If you can save a trip across the wire to the db by doing data validation in code, checking that that email address has an @ symbol and all that jazz, well that's good. But if you need to hit the db to do that validation, as you'd have to do when you're enforcing integrity in the middle tier, you just wasted network resources. You shouldn't have bothered.

Even if you don't need all these sorts of benefits right now, there's still value in doing things the right way. Aside from building good working habits in yourself, you're building something that has a value external to the application.

A well designed database generally has value that goes beyond the application that prompted its initial design. In the absense of the middle and client tier, it can still be utilized to generate projections and answer questions, and it's trivial to slap a new UI onto it. This is generally not true for dbs that are tightly bound to the web tier.

To throw my 2c into the Postgres vs MySQL debate, there is one thing that stands out between the two that has nothing to do with the technology.

MySQL developers have demonstrated time and again through their history that they have no problems selling the ignorant a bunch of bullshit to spur adoptation of their product. They do not concede that their product is unsuitable for some niches because of its limitations, instead, they knowingly advise new users to use poor development techniques even as they struggle to fix those limitations in their product.

I do not trust them not to lie to me, and I would not stake my reputation on their products for that reason. That's something that is most likely never going to change regardless of what any "feature set" charts say.

Re:Foreign Keys

[vadim_t]

That's the reason OOP is there :-) Database abstraction, DAO's and Model's (POxO/DTO/whatever) can be responsible for storing/validating data and reused in other app's (as webservices, libraries, ...)

None of this applies when somebody logs in with psql/enterprise manager/whatever and updates something in the database by hand. You can have all the OO and libraries you want, but it doesn't help if the new application doesn't use it. Yesterday we had code in VB6, today we have it in C#. Application is completely different. Guaranteeing that all the VB code will be exactly translated to C# is very, very hard.

On the other hand, the database remains being the same, and all the constraints it has don't care about which language, methodology or whatever is being used. VB, C#, Perl, PHP, are all automatically held to the same constraints.

  You can have N account types (customer, broker, corporate, ...) Each account type have it's own set of "valid data" constraints. And even inside the same "account type", the validation can change (if an account was opened before date XX, it's permitted to do bla). You just can't do that using simple foreign keys. And if you want to ensure your data is consistent, you *will* need Stored Procedures and Triggers.

And what's the problem with that? Use stored procedures and triggers then. Seriously, in a database of any size, forget about any attempts at compatibility with other databases. It only works on very, very trivial applications.

Just take postgres and mysql. PostgreSQL loves big transactions. The overhead for a transaction is high, but it's perfectly happy with large, long running transactions, and the more the better. PostgreSQL will be slow if you have a transaction per statement.

On the other hand, databases like mySQL want tiny transactions because the locks are really problematic. Leave a transaction uncommitted, and quickly things will grind to a halt. On the other hand, on postgresql the worst problem will be the lack of vacuum, which will gradually slow things down, but doesn't cause immediate problems.

If you make it for mySQL, without a redesign it'll suck on postgres and viceversa. If you try to make it for both, it'll be suboptimal on both.

Re:Foreign Keys

[Anonymous Coward]

"code-jockeys"

without the code-jockeys, there would be no way of accessing the data that is inside of your beautiful schema... blah, blah, blah

Re:Foreign Keys

[ShieldW0lf]

I don't deny that. I started my career as a code jockey. Doesn't change the fact that co-operation requires a realistic assessment of everyones skills.

If you do that, you can give each person dominion in their own area and everything will work.

If you don't, you end up having to double check everything with someone or other, development slows to a crawl, and mistakes are made.

It's the same as using templates to separate code from presentation so your designers don't muck up your server side scripts. Would you argue against that as well?

Re:Foreign Keys

[nuzak]

> Your app should be checking itself anyway.

No it shouldn't -- the purpose of a database is to make it happen whether the client apps care about data integrity or not. Now a good client will gracefully handle the errors that a database throws back at it, but the database is supposed to take care of the checks in the first place.

TFA reminds me of the anti-transaction FUD in old MySQL docs, which suddenly disappeared as soon as MySQL got a transactional backend. But hey, its system tables are still MyISAM, so you'd best be careful with those admin apps.

Re:Foreign Keys

[Rohan427]

MySQL is at version 5.0.27 with 5.1 in Beta now. The article is terribly old as far as features of the two DBMS's is concerned, especially references to views, transactions, and sub-queries (all of which were lacking in MySQL 4.1, but all of which are present in 5.0.27 and 5.1). In fact, your statement about MySQL is errant as well.

Using InnoDB, transactions, foreign keys with constraints, triggers, etc. in MySQL 5.x gives plenty of referential integrity. It also provides ACID compliance.

We won't even talk about comparing MySQL MaxDB (SAP/R3 Certified) and Postgres.

PGA

Re:Foreign Keys

[cortana]

Until it goes to court, it's up to those who want to try their luck to get away with going against MySQL Inc's wishes/license. :)

Re:Foreign Keys

[Branko]

You are correct about everything except the most important part. I'm not sure your wrong, but I question it. That is that it's typical to have multiple applications attached to the same db.

Good point. Al lot of "small" Web applications are actually "single application - single database". In addition to that, many are heavily read oriented with very few writes and manipulate data that is not very critical. In environment like this, having "application level" data consistency might be quick and dirty solution that actually works quite well in practice.

However, I'm under impression that many people learn these quick and dirty solutions first and never quite take the time u understand why the "slow and clean" solutions were invented in the first place.

In addition to that, I would argue that most development goes into "large" Web applications (they are fewer, but require significantly more development). Also, Web is by no means the only area where databases find their application.

Re:Foreign Keys

[Anonymous Coward]

Most decent sized development effort involves a Data Layser and a Business layer which lays out the objects and maintains business rules. Most developers would not be exposed to the data store direcly. Some companies go as far as developing a whole set of API(s) for each app they develop so the "junior" app developers never have to deal with data persistence layer.

Re:Foreign Keys

[droopy16]

At current rate most apps (even simple ones) need to be integrated with other apps. Which together brings it up one level into "several apps accessing same DB" category. No way in hell you can do that without DB being smart enough to cut off "stupid" queries. Seen it many times and learned it the hard way. But it is the reality.

Re:Foreign Keys

[sobachatina]

This discussion confuses me. Perhaps you can enlighten me.

How can you make a business selling a gpl application? Has anyone really done it?

If you have to give out your source and your customers have the right to modify, redistribute, and charge however they see fit it seems like you would only ever sell one copy.

Redhat makes its money off of support- if you don't want support I thought you could build their OS for free- or use Fedora which is the same thing. Trolltech sells a non-gpl version commercially- I wasn't aware that they sold their GPL version at all.

So how does one make a business on selling GPL software?

Re:Foreign Keys

[JFitzsimmons]

Yes.

Re:Foreign Keys

[vadim_t]

But this isn't really related to the discussion at hand.

At least in my case, the database enforces integrity. The business layer is there, but: It doesn't have to remain being the only one (eg, I might want to migrate to a more modern system at some point), and it's pretty thin. My business layer basically provides a friendly interface. If the database says "foreign key violation" the business layer would translate that "That user doesn't exist".

Re:Foreign Keys

[vadim_t]

However, I'd absolutely rather have messy data: imagine some type of glitch occurs (yes, unavoidable when working on anything with "serious" volume) and you end up performing the credit card charge but some part of the insert fails. In that case I want a partial transaction because there really was a partial transaction. It will aid in identifiying that something happened, and also in figuring out what it was.

So you do it in multiple transactions then. Transaction 1 inserts the order data, transaction 2 processes the payment, transaction 3 updates statistics, with each saving a note somewhere of how far it got.

This way you have both things: consistency, and the possibility to have a partially complete (but cut off at a well determined point) operations.

Yes, I do log errors elsewhere. But if you've worked on anything "serious" you'll know that there's always an error case that can come up aside from what you're able to log well.

That's easy, in my application I just log all of the requests headers and POST data. That's step 1, and will always succeed unless the database is down, as it's nice and simple.

Step 2 is processing it, in one transaction. If it fails, I can retry the operation.

I've come accross other examples, like making inventory records that don't have a foreign key because it's better than having no record at all.

Why? In a well designed database, things don't just vanish. What happens is that it returns an error to the user, who knows it wasn't saved at all, instead of being in some half-saved state the user may not be able to recover from. Then the user can retry saving it, knowing that unless the database says it's good, nothing gets written, and so there won't be 20 half-written records in the database due to the previous attempts.

Re:Foreign Keys

[lord_rob the only on]

Usually, you don't sell the software as is. Actually, you sell support. For example, installation, technical assistance and so on. This business can be profitable because your own costs are way lower than if you were doing traditional business (If you used proprietary, commercial software, you should have to pay much more royalties for licenses for example). However there are about no chances you can become a monopoly and earn large amounts of money like Microsoft, because if you practice too high prices, someone else will fork your product and replace you. That's perfectly legal of course. So I think it's a more fair business.

Re:Foreign Keys

[Ugot2BkidNme]

Actually I have to disagree with both of you I believe Foreign keys are useful in some cases but not in others. As far as the insertion of data and data control All database access should go through "Stored Procedures". No user of any application should have direct access to any table for insert or view. your stored procedure should handle all the checks you need. You can use a foreign key on top of it if you like. However if your right your procedures properly there is no need for foreign keys. No matter what application your using you have one access point the Stored Procedures. This controls what you can an cannot do and is a way better check on data integrity. Just remember to remove all access from the idiot developers to anything but your stored procedures. If they need something you don't have then you or whoever is your DBA writes it for them.

Re:Foreign Keys

[Haeleth]

i mean, unless they're using a modified GPL or whatever (is the GPL open-source?), they don't really get to dictate the terms. "derivative work", for example, isn't something they get to define.

MySQL AB's interpretation of the terms matches the FSF's interpretation, and has also been supported by courts in various parts of the world. It holds water. Nobody's found a loophole yet, and believe me, there are a hell of a lot of people who have been looking.

if i'm shipping a closed-source app that simply requires MySQL be also installed, and not something that's statically linked to their binaries, where's the "derivative work" come in?

The FSF's interpretation of their own license holds that both static and dynamic linking create a derivative-work relationship. I don't know what their legal argument is, but the advice they issue is that if a binary does not work unless a specific shared library available, it uses that library and is therefore required to respect that library's license. Note that a even dynamically linked executable does generally have specific references to parts of the shared library compiled into it, and on some platforms is even statically linked to an import library, so there is some logic in this position.

Now, if what you had in mind was an application that wasn't linked to MySQL libraries at all, but interacted with MySQL by running the mysql program with specific command-line options and parsing the output, then you would be correct: that does not create a derivative-work relationship, and even RMS himself is of the opinion that a program that works that way would not infringe on the GPL and could be licensed however you please. Of course, there would probably be something of a performance hit.

Re:Foreign Keys

[killjoe]

Here is reality.

You build a web site which lets people enter some data (let's say it's employees for now). Your web site also lets them search the web site so they can find a particular employee. You have used an ORM in developing your web site because you know it's wrong to sprinkle your code with SQL statements. You have chosen to use postgres because it's "robust".

Your customer does a search for "McFadden" but is unable to find the employee because she typed in "Mcfadden" when she entered the data. Your ORM does not support weird postgres syntax like ILIKE or *~. Oh I know all I have to do is to change to collation on that column to case insensitive english. Nope postgres does not support per column collations and it does not have any case insensitive collations.

Ah well forget it. Your ORM lets you change databases and every other database in the world supports case insensitive where clauses so you switch.

That's reality for you. Also in real life people need easy to set up, async, multi master, replication and failover.

Re:Foreign Keys

[Brummund]

A time enthusiast :-)

Check this one out as well, very nice overview and analysis. http://naggum.no/lugm-time.html

Re:Foreign Keys

[killjoe]

Personally I think putting business logic in the database is wrong. It's harder to debug, harder to version control, harder to write.

Putting some code to handle data integrity is OK but not business logic. Performance isn't everything. I would rather have an application that runs slower and is easier to maintain then the other way around.

"unless the application is written in C. Databases are generally written in C/C++. Applications are generally written in Java,Perl,Python,Ruby. None of these can compete with C."

This is nonsensical. If I write in python the python VM compiles and then runs my code. The VM is written in C. If I write in PL/PSQL then the postgres interprets my PL/PSQL line by line (it never gets compiled). Running your business logic in java should be significantly faster then writing it in PL/PSQL except when the application needs to transfer an ungodly amount of data over the network.

Re:Foreign Keys

[Talchas]

Simply, yes. However, most commercial software obviously isn't GPLed.

Re:Foreign Keys

[Crayon Kid]

If it's a modified GPL it may not be allowed to call itself GPL, but most of it is still there. Including the parts about defining "derivative work".

Re:Foreign Keys

[Crayon Kid]

Yes they can. I can take the GPL, I can slap "you have to wear pajamas at all times or else you can't do anything with this product" and it would be valid. Not GPL, not sane, but valid nonetheless.

It's up to the original developer to license his original product in any way he sees fit. He can use any existing license as a starting point. There's nothing in the GPL that says "don't use GPL as a base for modified licenses".

Re:Foreign Keys

[electroniceric]

I wouldn't call this a flame, but I do think you're off base. What you're describing sounds to me like it would be better addressed by a combination of an audit trail and application logging. Your post says you want to see all the ugly steps that happened between a clean start and a clean commit. That's absolutely a good idea to see that stuff, but it should not result in dirty data in your database at the end of a commit.

Put it this way: the dirty data is fundamentally metadata of one sort or another, and the data that drives a business process should be clean. While database referential integrity is not the only way of acheiving it, it does serve as a good last line of defense.

I don't really argue that there are times when you can't rigidly stick to these (or any) design principles. Maybe it's my faulty reading of your post, but the cases your describe sound more like you are excessively relaxing data integrity requirements to acheiving auditing and logging.

Re:Foreign Keys

[cyberon22]

This is a really interesting post. What are your thoughts on a program that could hot-swap between backend database systems, optionally choosing to interact with MySQL or Postgresql or SQLite or whatever backend database storage system was required. If the choice was left to the user in terms of implementing the database backend, would this still be considered a derivative work for sales purposes?

Not looking for legal advice, just curious because you have some interesting thoughts on things.

Re:Foreign Keys

[angulion]

As the is just one of the jobs foreign keys are there to prevent, you
A) didn't use foreign keys,
B) did not know how to use them (as in "ignore") or
C) used a database that has shoddy support of foreign key (depending on "backend" w/o returning errors) like MySQL.

Re:Foreign Keys

[angulion]

Or (at least in postgres) you can do it quick and clean with a "foreign key on delete cascade" - parent *and* kids are gone.

Re:Foreign Keys

[MarkLewis]

We investigated this issue as well, because we have a commercial application which uses JDBC and requires a database. Our application wasn't designed for MySQL, but works with it as well as with several other databases. Most of our clients already had a commercial database installed, so this was not an issue for them, but some of our clients needed a database.

So we asked our legal folks what they thought about including documentation for our MySQL support. Their opinion was that because we had not designed our application for MySQL but had instead coded to the generic JDBC interface (and since MySQL wasn't even our primary target), then as long as we didn't ship MySQL or the GPL'd driver then there was no way we could be classified as a derivative work.

So we ship the application, and allow the user to specify their own JDBC driver. We do include directions on configuring it to use MySQL.

If you go to the MySQL site, they try to convince you that hiding behind the JDBC interface like this is infringing use for which you need a commercial license. But our lawyers decided that their argument was bunk, and that it would have probably been just fine to even distribute the MySQL driver alongside our application provided we advertise the GPL and include the driver source.

Now that PostgreSQL is a couple of versions into native Windows support this has all become a non-issue anyway. All new clients who need a database are being steered that way, partially because of the friendly licensing but mostly because our developers trust it more anyway.

Re:Foreign Keys

[theonetruekeebler]

can say this because databases are written in C and optimized and debugged for years. While the database does the check more quickly than an attached application, it's not for the reason you cite. The DB can do it faster because it can do the checking internally. Not only does it not have to transfer a single row to the application, typically it won't even access the referenced table: it will simply do a hit/miss test on the appropriate index.

If the application has a network between it and the database, which it often will, network lag by itself produced horrible inefficiencies in data validation, and clever client-side caching will inevitably break referential integrity.

Re:Foreign Keys

[Anonymous Coward]

You don't need an object relational mapper to keep SQL out of your code, anyone who has ever done more than a little development has used or written a database abstraction class.

Your ORM lets you change how it generates queries too, change it to use UPPER().

Why would you want async multimaster replication with failover? Async replication is used for mostly recent backups, it makes no sense for a multimaster setup where all masters have to be in sync.

You seem to have a pretty twisted little "reality" built for yourself.

Re:Foreign Keys

[fm6]

Your statement requires one qualification: foreign keys are essential in a relational database. The fact is, most applications implemented on top of MySQL use it as a simple flat database, with no attempt (and often no need) to normalize the data. A classic example is a forum or wiki, where each post or topic is represented by a table row, and there are no relations to other tables. For that kind of non-relational database, foreign keys are not at all necessary.

Of course, once you get into more complex applications, you start using relations and normalizing your data — or at least you should. But many self-taught database "experts" out there have no idea what "relations" and "normalization" are. Or if they do, they assume MySQL supports them because it uses the same query language as relational DBMSs.

Re:Foreign Keys

[counterplex]

A foreign key has about as much impact as a unique index. I hope you don't offload unique indexes onto your application instead of letting your database handle it specially if you have multiple clients talking to a single database.

Re:Foreign Keys

[counterplex]

I think he might've meant "clients" instead of "applications". In a single web application you can have multiple clients connecting to the database. Each apache child will be a new client for the db.

Re:Foreign Keys

[Slashdot Parent]

Nice rant, but MySQL supports foreign key constraints and triggers (and several other "niceties" like advanced clustering, partitioning, and XA transactions, which PostgreSQL couldn't be bothered to implement). It also has the added bonus of not hosing 100% of your data if you forget to vacuum.

What was your rant about, again?

Re:Foreign Keys

[Procyon101]

Yes, you can get alot done without them, but then you have to ask yourself why you are using an RDBMS in the first place. Raw storage can be accomplished much faster using other methods, so the only thing you buy yourself is the SQL API, which isn't generally the easiest to work with anyway.

Re:Foreign Keys

[Anonymous Coward]

I think a better example in your case would be to simply add a "valid" flag (or possibly another value for a status field, if the record already has one).

You've simply pushed the problem to another level. Now you have to worry that someone, somewhere will forget to check if the parent is valid when they run their aggregate function on the kids.

I object to the absolute declaration of the grandparent: "thou must always use FK constraints". It reminds me of people who say that "all RDBMSs shalt be maximally normalized". That kind of absolutism might be ok for academics, but it ignores real-world performance issues. The ugly fact is that resolving performance issues sometimes requires de-normalization, dropping constraints, etc. The trick is to know what you're doing and why, and to make sure that the trade-offs you took have been documented.

Re:Foreign Keys

[jaydonnell]

it's not what i've experienced in numerous places. If it works for you that's good.

Re:Foreign Keys

[jaydonnell]

but they would use the same code base so his point wouldn't make sense in that context.

Re:Foreign Keys

[jaydonnell]

Let me expand upon this a bit. Currently I'm working on a good sized site in ruby on rails. We have what you would call other apps that we've written for the site, but all are in ruby. I'm not sure if you've used ruby on rails before, but all of our business logic/db access is done through our models. All of our serious/complex data manipulation is done in these models and our other "apps" use this same code to access the db (a single point of control). This is the benefit of good design, but that's going off on a tangent. Having the data manipulation in ruby works well for us for a few reasons

• it's easier to debug ruby than funky db language
• ruby is more powerful and elegant than funky db language
• most importantly, all of our code management processes (version control, testing, deployment, etc) are already in place and seamless for application level ruby code.

I know this isn't the case in most places, but it works great for us. I agree with all the points you made before, but I don't think that has to be the situation for all or even most people.

Re:Foreign Keys

[Jahz]

That is possible, but then it might just be a result of *who* uses MySQL. There is a large base of inexperienced users who are just not trained/experienced at all. Most people that I have met in school or on the job who has developed a DB and spoken to me about it has relied in some way on foreign key constraints. On the other hand a few of my friends have done DB work and dont even know what FK's are... I think it basically comes down to experience, and MySQL tends to have more inexperienced devs using it (wholly due to its popularity and free-ness).

Re:Foreign Keys

[dubious9]

But, as far as I can tell, MySQL does call their license GPL. From section 4:

You may not copy, modify, sublicense, or distribute the Program except as expressly provided under this License. Any attempt otherwise to copy, modify, sublicense or distribute the Program is void, and will automatically terminate your rights under this License. I'll admit I've never *really* read through the GPL to research this case, but it looks like you can't restrict redistribution and still call your license "GPL". Requiring pajamas is fine, you just can't call it GPL anymore.

As far as I can tell, you can sublicense under lesser terms, but not stronger ones.

Re:Foreign Keys

[Omnifarious]

Well, the SQL API can be very nice for certain kinds of data, and there is no storage system I know that does ACID without tons of work on your part.

I've actually been really hopeful that some filesystem would grow ACID + a few extra Unix calls to make it work. In general I hate databases because (as you say) SQL isn't a very nice API in many cases. I was looking forward to some of the extra semantics reiserfs 4 was (and maybe still is) going to include to make certain kinds of operations atomic that weren't before.

Re:Foreign Keys

[Procyon101]

I'm not too hopefull for Reiser ATM. Politics being what they are and all, and the project always seemed to me to have bitten off more than they could chew from day one. It's a pie in the sky project that if it ever pans out will be revolutionary, but I'm not holding my breath. My money would be on WinFS in the long run, with a good clone for non-windows becoming workable [reliable even] 2-5 years after.

If I had to do a non-relational ACID, clustered data solution, I'd probably tend more towards a Turmite (gambit scheme) system with parity, or something similar built on Erlang or any other comparable message passing (or publish/subscribe as it's sometimes called) architecture. I don't believe that parity is built into either system in it's current state, and that might not be a requisite since you could probably use plain redundancy, but the scalability, robustness and flexibility is way more than I think you are likely to get out of an RDBMS, especially a non-oracle, open source one. What you sacrifice is integrity (and the miserable API ;)) but if that doesn't matter to you in the first place... As a side benefit, you would get automatic local data caching, as your app would coexist with it's data, since the data store is a green-threaded process and in many usage patterns the storage site could migrate towards the use site, as well as automatically balancing the load across all cores on a machine, which is of increasing importance as the core-wars scale up, and happens automatically in a message passing architecture since all your functions become thread-services.

This is of course, just me brainstorming, so take it for what it's worth. Overall though, I'm tending toward the opinion that RDBMS's are a big square hole that we tend to shove all of our pegs into for better or worse, and that if you find you do not need a fundamental principle of the RDBMS, like foreign keys, that it's a strong indication you've got a round peg in your hand.

Re:Foreign Keys

[gumbi west]

Wait, isn't this discussion about MySQL doing exactly that?

Re:Foreign Keys

[anothy]

okay, apparently i wasn't clear. i understand the FSF's position. do you have cites on these court decisions? because, in my mind, dynamic (not static) linking doesn't create anything resembling what i've seen termed a "derivative work" relationship in other instances (including court cases). i don't particularly disbelieve that there's legal support out there, but i've just never seen it, and it doesn't match my expectations. references would be helpful.
again: is there legal reference indicating dynamic linking can be considered a derivative work relationship?

Re:Foreign Keys

[edwdig]

SQL is a bit fuzzy once you get beyond simple selects... On one database you might want 'SELECT TOP 5 * ...' on another you might want 'SELECT ... LIMIT 5', or even 'SELECT ... WHERE ROWNUM6'. Or you could just use a forward only cursor - provided the DB backend doesn't try to read the whole thing into memory to implement it...

Once you insert an ORDER BY clause into your query, the DB automatically has to process all the data before it can send you any results. Without an order by, sure, the DB can do what you said.

Re:Foreign Keys

[statusbar]

If you go to the MySQL site, they try to convince you that hiding behind the JDBC interface like this is infringing use for which you need a commercial license

This is very very important, can you post a link to the relevant spot on the mysql site for other people to reference?

--jeffk++

Re:Foreign Keys

[droopy16]

Agreed if your environment is homogenous and you can afford to either write apps using same framework or deploy apps written using the same framework.

But if you're employing "best of breed" apps you might end up with PHP+Java+Python+Perl (like our current situation). What is going to guarantee in this case that data is going to be consistant? Add to the complexity layer fact that not all of those apps running on the same platform. From what I gather our situation is far from unique and this *is* the new reality - things need to be integrated and the lowest common denominator is DB. Why not use what *real db* has to offer: stored procedures, triggers, foreign keys, views etc? All the logic in the apps all of sudden becomes simple. On top of that - if most of your queries translated into stored procedure calls portability of your app goes "up" instanteniosly - if you can provide both Oracle and Postgres support at lowest possible effort why wouldn't you do it?

Consider calling procedure "add_record(a,b,c)" instead of using 3 inserts into different tables. Better yet consider "find(x,y,z)" instead of "select a.x, b.y, c.z from a, b, c outter join d on..." which would change from DB to DB especially depending on JOIN implementation.

you can mintain cross-compatibility code on DB layer but your app is always the same (considering you've used something like PEAR::DB or DBI or JDBC to simplify/unify DB calls).

As soon as you cross boundary of one programming language you just have to consider "smarter" DB. Even staying within same programing language you can access MySQL using PHP standard functions and PEAR - which makes verification code not-so-easily portable. Why bother reinventing the wheel when it was done long time ago - DB-side logic?

Re:Foreign Keys

[Lodragandraoidh]

You are absolutely right - business logic has to be code, primarily because it is constantly changing as marketing assumptions change, new products are introduced, and old products are deprecated.

Re:Foreign Keys

[innocent_white_lamb]

You are free to sell GPL software at any price that you choose to sell it for.
 
The person who purchases it from you is also free to sell it for any price that he chooses, in exactly the same way.

Re:Foreign Keys

[jadavis]

While the database does the check more quickly than an attached application, it's not for the reason you cite.

For some things this is true, others not.

Database features like constraints and the relational model are not there for speed. They are there because:
(1) They abstract the physical data storage from the logical access of the data
(2) They allow application developers catch errors very early, rather than waiting until the database is corrupt and trying to backtrack

Re:Foreign Keys

[Serveert]

or you could use LOWER(x) LIKE '%blah%'

and regarding replication, slony is awesome, we use it in production. Sure it's not multimaster but multimaster is garbage, it's too complex. Even oracle has problems with it because it's a hard problem to solve. single master multiple slaves just works, it's simple, and there are no conflict christmas presents to deal with. Multi-master is scary.

Re:Foreign Keys

[localman]

So you do it in multiple transactions then. Transaction 1 inserts the order data, transaction 2 processes the payment, transaction 3 updates statistics, with each saving a note somewhere of how far it got.

Well sure... though at that point the "transactions" are just single insert statements, so I'm not sure what benefit you get since those are atomic for all intents and purposes anyways.

That's easy, in my application I just log all of the requests headers and POST data.

A good idea, but that doesn't tell you necessarily when something went wrong. A dangling charge record sure does. Then you can check out the POST log to see if it was something in the request that caused it.

Why? In a well designed database, things don't just vanish.

True. But lets say you have an inventory system where an item gets scanned to a location. Normally you have a table in the DB for each. But lets say the user scans an item into a non-existent location -- perhaps a barcode was created that isn't valid for some reason. You're still better off recording the bad scan. Sure you can give the user an error (and I would) but 9 times out of 10 in a high-volume distributed user base the user doesn't really care about the error. They'll shrug and walk away and you're left with no data at all.

Note the above isn't really a hypothetical (though it's simplified) -- I've had to deal with this in reality in many cases in our warehouse. Someone can always easily say "don't allow xyz" but you can't stop people from doing xyz, so you might as well record it. Then they say "well, fire someone if they do xyz", that's not always practical, but even if it is, you have to record it to know who did it.

Are there clean transactional ways to do all the things I'm talking about? Sure, to a large degree. But partially inserted records aren't "wrong" if the process was really partially complete. I get that datbases should be "clean", but there are exceptions to every rule. For me, the exception is when being "clean" means being less representative of reality.

Cheers.

Re:Foreign Keys

[Jamie Lokier]

Once you insert an ORDER BY clause into your query, the DB automatically has to process all the data before it can send you any results.

That's not true, if the ORDER BY column(s) are ordered-indexed, or if it/they are the table's primary storage ordering. Which one would hope applies in a query like "select most recent 5 articles" - if they are stored by date, and any WHERE clause is not too sparse a filter.

Think about traversing a B-tree, or any kind of tree. You don't have to read the whole tree to begin streaming out the smallest elements.

Re:Foreign Keys

[maop]

Based on the GPL FAQ you could also use a socket interface and the software would still be considered "aggregate programs" and not linked modules unless "the semantics of the communication are intimate enough, exchanging complex internal data structures, that too could be a basis to consider the two parts as combined into a larger program." MySQL is probably claiming that making various queries constitutes exchange of complex internal structures. I would disagree with this and label it as communication between separate programs if the data exchange is that of formated data as expected from any other standard SQL RDBMS implementation.

Re:Foreign Keys

[ultranova]

Good point. Al lot of "small" Web applications are actually "single application - single database". In addition to that, many are heavily read oriented with very few writes and manipulate data that is not very critical. In environment like this, having "application level" data consistency might be quick and dirty solution that actually works quite well in practice.

However, I'm under impression that many people learn these quick and dirty solutions first and never quite take the time u understand why the "slow and clean" solutions were invented in the first place.

Fast and clean solution: use "directorid INTEGER REFERENCES directors.id ON UPDATE CASCADE ON DELETE CASCADE" in the "CREATE TABLE" query.

Slow and dirty solution: every time you're about to insert something into the table containing the "directorid" column, first do "SELECT id FROM directors WHERE id = x", check that you got a row, and pray no one will remove or change that row after you've inserted the new row.

Why on Earth would anyone take the dirty solution, even if the data is not critical, is beyond me. This is one of the situations where doing the right thing is much less work than doing the dirty hack.

Re:Foreign Keys

[JBird]

After deleting a row left dangling foreign keys (yes, I declared them as such), I check at the application level. Your DB doesn't allow you to cascade deletes of the foreign keys? Hmmm, this is an essential capability for me in any DB apps that I write. Removes a whole host of referential integrity issues.

Re:Foreign Keys

[ultranova]

A classic example is a forum or wiki, where each post or topic is represented by a table row, and there are no relations to other tables.

Except that a post may well reference other posts. I'm not sure if it's possible to have foreign keys that check against another column in the same table, thought. But posters, and their personal preferences, should be separated to another table, as shoudl articles.

Re:Foreign Keys

[edwdig]

That's not true, if the ORDER BY column(s) are ordered-indexed, or if it/they are the table's primary storage ordering. Which one would hope applies in a query like "select most recent 5 articles" - if they are stored by date, and any WHERE clause is not too sparse a filter.

Well, I was thinking in more general terms. PostgreSQL tends not to use indexes as often as you would expect it to, so I didn't really think about it too much. You are right, it would use an index here.

Re:Foreign Keys

[theonetruekeebler]

Of course constraints aren't there for speed. They're there for data integrity. However, handling integrity checks within the database will be significantly faster (and more reliable) than handling them through an application interfacing to the database.

Re:Foreign Keys

[Anonymous Coward]

Next time, before you insult a large group of developers, get your facts straight. Next time, before you absolve a group of developers from wrong-doing, perhaps you would do well to read up a bit on what they actually said regarding these issues in the past? Hmm?

Re:Foreign Keys

[ultranova]

Once you insert an ORDER BY clause into your query, the DB automatically has to process all the data before it can send you any results. Without an order by, sure, the DB can do what you said.

No. Using an ORDER BY and LIMIT together allows the database to use an index (assuming the column in question has one) to retrieve only the first n rows from the ordered set. This is true for at least PostgreSQL, dunno about MySQL.

Re:Foreign Keys

[meiao]

Anyway, you can sell GPLed programs. You just need to provide the source code.
And anyone who buys it, may redistribute freely.

Re:Foreign Keys

[Branko]

Why on Earth would anyone take the dirty solution...

My guess:

• They want some special behavior (such as user-friendly error message) to happen when constraint is violated.
• They don't plan for it in advance, so implementing it in code is "closer" than doing ALTER TABLE.
• They simply don't know ON UPDATE etc... syntax.

BTW, I used "quick" as a reference to implementation speed, not execution speed.

Re:Foreign Keys

[fm6]

Well, I was thinking of a very simple forum: each post is represented by a row that contains just the index field, a text field that contains the post content, and a couple other fields that contain metadata, such as the date of the post. I suppose a typical forum database would also have a field for things like parent and child posts. Now, these could be foreign keys (yes, that's allowed; table often have relations with themselves) but for a simple application that doesn't make sense. You'd access the rows one at a time, and cross references would be handled by your procedural code, not your SQL.

Except I've never actually written this kind of software, so I'm talking through my third orifice. Anyway, this is just an example. The key point is that MySQL works perfectly well (and is faster) when all you need is a flat database. Where many MySQL enthusiasts go wrong is assuming that MySQL can do anything an RDBMS can do, just because it implements SQL.

Re:Foreign Keys

[jaydonnell]

I can't remember if you were the OP or not, but I agree with everything that's been said. I was just pointing out that I think there are a good number of people in my situation too :)

I have a couple of questions for you if you have the time. What did you mean by "best of breed" apps? I'm guessing that you meant apps that aren't made in house. If this is the case how do stored procs help since third part apps wouldn't be coded to use the same stored procs?

Re:Foreign Keys

[vadim_t]

A good idea, but that doesn't tell you necessarily when something went wrong. A dangling charge record sure does.

Baad. A dangling record says something is wrong, but not necessarily what, and you can't always determine it. Example:

table orders(order_id, client_id, ...)
table orders_rows(row_id, order_id, product_id, amount, ...)

Now, what happens when you save an order with 5 lines in it? First you need to create a row in "orders", then insert 5 rows into "orders_rows".

You have several modes of failure:

1. Nothing gets done at all: equivalent result both ways
2. Only row in orders got inserted: This could be a bug in the application that allowed an empty row to be saved, or the insertion into orders_rows failed. If saving the whole order in one transaction, you now know that the order was actually empty. If you don't use transactions, then you don't know which is it
3. Several rows inserted into order_rows. If you use transactions, you know everything went well. If you don't, you can't know whether something failed. Depending on your logic, maybe everything after the failed row is missing, or perhaps only the bad row is missing... in any case this is hard to debug.

Then you can check out the POST log to see if it was something in the request that caused it.

My system is:
1. Insert POST record
2. begin transaction
3. process operation
4. Write log entry referencing POST record
5. commit

Then it's trivial to determine where something went wrong: If there's a POST record, but no log entry is associated with it, then it never got processed. Database errors are of course recorded in the database log.

An additional benefit of this system that I can pick any request I want and try to resubmit it as many times as necessary, while performing a rollback on step 5. That allows for very easy debugging, as the starting state for each attempt is always the same.

True. But lets say you have an inventory system where an item gets scanned to a location. Normally you have a table in the DB for each. But lets say the user scans an item into a non-existent location -- perhaps a barcode was created that isn't valid for some reason. You're still better off recording the bad scan. Sure you can give the user an error (and I would) but 9 times out of 10 in a high-volume distributed user base the user doesn't really care about the error. They'll shrug and walk away and you're left with no data at all.

You log the error and don't record anything. The user is going to be paid minimum wage for that sort of job. You can easily fire the moron for not reporting the problem, and get somebody else. That person isn't very critical to your business. A few years later they'll probably be off to greener pastures. However, long after they're gone, you'll find things are being ordered and you don't know where they are.

Your database will be there for a long, long time. Ensuring the business itself runs smoothly is a lot more important than pleasing a minimum wage data entry employee. When the order must ship, and nobody can figure out where the heck are those 50000 widgets the company promised, nobody is going to thank you for the convenience.

Note the above isn't really a hypothetical (though it's simplified) -- I've had to deal with this in reality in many cases in our warehouse. Someone can always easily say "don't allow xyz" but you can't stop people from doing xyz, so you might as well record it.

Bullshit. Of course you can stop them, you make the program refuse to accept it.

Then they say "well, fire someone if they do xyz", that's not always practical, but even if it is, you have to record it to know who did it.

That's easy enough, I work on a similar app

Re:Foreign Keys

[Procyon101]

Not to say that I wouldn't ever dump a plain blob of data into a SQL server for convienience, just saying that in pure CS terms, it's not a "correct" solution, just one in which the round peg happens to fit in the square hole nicely.

Re:Foreign Keys

[tacocat]

If you think it's nonsensical try doing benchmarks between C code and Python. C is faster. You can't argue with the facts. Can you cite examples of code that have the same performance for Python as C?

All that aside. I absolutely agree that the business logic should not be in the database code. However, there are certain core elements of the business logic that could and should be located in the more controlled database environment. Examples of this wouold be things that are not likely to be changed by the business rules would be: Account Numbers, Part Numbers and things like that.

The new architectural model is to encapsulate the business logic into a highly adaptive language like Python, Perl, Ruby and use the business logic element to act as a glue layer between the interface (HTTP/Web/Soap/Rest) the data store (database) and the operating system (Linux/BSD). This gives you a highly robust set of application foundations that you interface with and figure out how to run your business via the business logic or middle ware (if that's still applicable).

Re:Foreign Keys

[tacocat]

it's a balancing act between the logic you put into the database and the logic you put into the application code.

The stuff that you won't be changing is something you put into the database logic, but only where it makes sense because you need to have the integrity of a relational model. There are some errors that you can protect yourself against by placing it into the database. For example, non-null password fields and username fields would be good.

Re:Foreign Keys

[Anonymous Coward]

My favorite part is that it's unbiased because the author said it is. It must be true! Now that's credibility!

Re:Foreign Keys

[MarkLewis]

Well, I had previously found a more specific FAQ about the client libraries back when I was helping research the licensing stuff. I can't find it now, but this excerpt from the commercial license page seems applicable (from http://www.mysql.com/company/legal/licensing/comme rcial-license.html).

"If you develop and distribute a commercial application and as part of utilizing your application, the end-user must download a copy of MySQL; for each derivative work, you (or, in some cases, your end-user) need a commercial license for the MySQL server and/or MySQL client libraries."

"If you include one or more of the MySQL drivers in your non-GPL application (so that your application can run with MySQL), you need a commercial license for the driver(s) in question. The MySQL drivers currently include an ODBC driver, a JDBC driver and the C language library."

Re:Foreign Keys

[Anonymous Coward]

They understand scalability. The postgreSQL team chases ACID/scalability/features, in that order. The MySQL team chases performance and buzz word compliance at the cost of ACID and features. Which do you want to store your data in. I constantly see small developers create things for games and they pick MySQL because they don't know any better. They lose their data two to three times per year and don't think anything of it.

Who do you want caring for your data?

Re:Foreign Keys

[adah]

they don't really get to dictate the terms. "derivative work", for example, isn't something they get to define.

It is their product and their licence, so they have the right to interpret their licence. It is unlike the case of the Linux kernel, where FSF has no right to interpret the licence for Linus and other kernel developers.

Re:Foreign Keys

[jadavis]

However, handling integrity checks within the database will be significantly faster (and more reliable)

As I said in my post, for some constraints that's true, for some not.

For example, let's say you have an application that stores historic data about classes. It finds a class from the source, and adds the name, time, and teacher of the class in one table, and the students that took the class in another table referencing the first table. The application can submit entire classes in one transaction, easily ensuring that every student entered will match to an existing class. However, if you do a foreign key check, you will have to examine each student entered and search the class table to make sure there's an entry. This is because the database isn't smart enough to prove that because you're submitting the entire set of changes in one transaction, and all the students you enter match with the class you enter, the constraint can't be violated.

I'm not saying it's a good idea, but in some situations, if you constrain the data within the application it can be more efficient. When it's more efficient to do in the database, that's a bonus. I try to avoid the speed argument because intelligent developers can make a lot of valid arguments when it comes to performance. But from an engineering standpoint, the central constraint-checking location is more robust.

You also have to consider that CPU time on an application server is often much cheaper than CPU time on a database server.

Re:Foreign Keys

[anothy]

unless they explicitly define the terms, no. "derivative work" has a standing legal definition, the interpretation and application of which is up to the courts, not the copyright holder. if the license explicitly defines the term to mean some particular thing, that's another story, but (as far as i'm aware) the GPL does not do this clearly enough to answer the question here (thus my pending request for citations to the supposed legal precedent). commentary on a license on a web site has no legal standing, even from the copyright holder.

again, i wouldn't be shocked to find out the ancestor is correct here, but i remain skeptical until someone can point me at (a) a section of the license which resolves the issue unambiguously or (b) a court decision that has the same clarifying effect.

Re:Foreign Keys

[Anonymous Coward]

Foreign Keys are just that, keys. An indexed key speeds up SELECT & INSERT. It hurts UPDATE & INSERT a little bit to have this. Who inserts data and never looks at it?

The foreign part guarantees that a large search table matches keys in a short FK table. The short table holds human-readble names for each integer FK.

So, users get a pull-down menu of short table options, but the database holds integer keys in the large search table. So many website application databases don't do this, and things get ugly.

Re:Foreign Keys

[47F0]

Wow. Just wow. Setting aside, for an instant, the whole DB issue, there is something fundamental that is being flat missed here.

In any proper development environment, robustness and exception management should occur at the lowest level possible. Always. A great app, written against a shaky O/S, running against bogus BIOS calls will never be a great app. Ever. Someone was sleeping during design 101 if they miss this fundamental design concept.

This is the only efficient, and only reasonable development strategy. You can't honestly expect to code "create socket - Did my socket really get created? - try socket out - did that really work?" and think that you've got something solid - no, you've just got a pile of inefficient code on top of a bad socket call that should have reliably returned a status and/or failed gracefully to start with.

The arguments I see against a solid DB design ensuring integrity boil down to one thing, and one thing only, "Gee, we only code in Ruby and that DB stuff is sooooo weird".

Please show me one instance, just one where it's ok to create a product shipment record against an order record that never got created. Explain to me just why a properly designed database should even allow such things from an application, then how it's any more efficient to check for the same potential pitfalls in a dozen pieces of application than it is to get it right at the lowest level to start with.

Re:Foreign Keys

[vadim_t]

I think you replied to the wrong post

Re:Foreign Keys

[droopy16]

ok, say you're using PHP application for your forums (let's assume it's phpBB). Now let's assume you're using content management system for your site (let's say it's Plone). Now your task is to make every published item in Plone to appear as a new topic in phpBB and vice-versa - every topic in phpBB to appear in Plone (things get better when you remove stuff from either one ;) ). Obviously two different application stacks, nothing in common but DB. If you slipped on Plone end and missed some entries in DB needed by phpBB - you're done (if your DB didn't check for data integrity etc.). With postgres things get even better since you can write stored procedures in many languages (including python) so you can write trigger for phpBB DB so that it creates item in Plone on topic creation. Variations are endless but everything is tied to idea that whenever something happens to DB I can: control event, generate event, etc.

This was rather hypothetical example (however pretty close to what we're doing from time to time), there are way more complex examples but it'll take me much longer to describe those.

So as you see - it's insane to try to write your own phpBB from scratch and same goes for Plone. Now your customer for each specific task chose each of those apps separately but realized - they want integration. Without "smart DB" in the background I'd be in trouble trying to explain that from time to time they have to go and clean up stuff on both ends.

Re:Foreign Keys

[Anonymous Coward]

I'm not sure what 'models' are in rails, but the whole thing sounds a bit like how SAP does it. You make changes by calling supplied functions, rather than coding your own update statements.

No Digg

[AKAImBatman]

1. There's no such thing as unbiased. Especially on a page that gives a fairly abstract review.

2. This article is 2 years old. Everything in its comparisons is out of date.

Re:No Digg

[TracerRX]

Hehheh... Beavis He said "Digg" on slashdot

Re:No Digg

[suso]

2. This article is 2 years old. Everything in its comparisons is out of date.

Its a government website and so it was written in Government Time (GVT). That means that February 2005 is actually about May of 2009. So clearly the author of the page can't be trusted.

Re:No Digg

[TrappedByMyself]

Funny you should mention that. Guess what article appeared on Digg very recently...

Re:No Digg

[Anonymous Coward]

If you knew the people who wrote the page (I do), you would have treated TFA as a joke when it turned up in a google search, and left laughing.. regardless of the date. ;)

They aren't people you want to be making advice for what software to be using. In fact, any software or software related suggestions you see come out of Fermilab you probably want to avoid.

One of the least professional IT departments I have ever had to deal with. Monkeys banging on keyboards would be an apt description.

(anonymous because I still need the job they give me)

Re:No Digg

[electroniceric]

Just to continue on your good points, especially troubling is the fact that this article compares the then-unreleased MySQL 5 to the Postgres 7.x series. Nearly all the drawbacks to Postgres that this article highlights have been addressed in the 8.x series.

We run Postgres for our main business application and the main limitations are of two forms:
1) Depth of community
The Postgres community is great - very responsive and knowledgeable, but its size is a limitation in a number of ways. The ODBC driver is a bit of stepchild to the main project, and some key functions like dblink that address missing features like cross-database selects are relegated to /contrib, and rely on their individual authors for nearly all maintenance. This means that as a user you are more likely to bump up against the bleeding edge earlier than in communities where these outside-the-core projects are more supported.

For the same reason a key subset of its documentation is very sparse. Documentation for the core system is thorough, clear and concise, but anything in contrib or any projects like the ODBC or .NET drivers are much less like to have the same quality of documentaton. Postgres' extremely powerful GIST indexes are unparalled as a feature, but you need a background in theoretical CompSci to figure them out, thanks to limited documentation (note to aspire database index geeks - I would gladly buy a book on GIST aimed at proficient DBAs who are not giants of theoretical CS). Likewise its procedural languages: thanks to its architecture and open codebase, Postgres offers more server-side languages than any other database that I know of, but few of them have more than basic documentation, let alone the stacks of books you'd find with other procedural languages.

2) Postgres is very close to being a true enterprise contender (unlike MySQL, which is evolving that direction but distinctly further off), but lacks some key features like XML handling, a more comprehensible approach to result sets (anyone who's dealt with rowtypes and casting resultsets can attest to the steep learning curve), and a userbase that has put the product through the wringer. Now that some corporate heads are getting interested (e.g. Sun, Red Hat, EnterpriseDB) hopefully some of these shortcomings will be addressed in short order.

Don't let this outdated, apples to oranges comparison fool you: Postgres is a very solid and usable database.

Re:No Digg

[archen]

And as far as that goes I'd prefer two biased views that continually counterpoint each other in a reasonable manner. When you see these "unbiased" reviews that typically means that the person in question hasn't used both extensively enough to have a very good understanding of either. The real guts comes out when you have people use this stuff in the trenches in real life scenarios. Usually this will cause people to gravitate to one or the other for some reason or another. These experiences are the guts of a good comparison.

Re:No Digg

[gmack]

Don't let this outdated, apples to oranges comparison fool you: Postgres is a very solid and usable database.

Does it have clustering yet? How about load ballancing? Those were the main issues that stopped our deployment of postgres.

Re:No Digg

[leonmergen]

Does it have clustering yet? How about load ballancing? Those were the main issues that stopped our deployment of postgres.

Yep, it does.

Re:No Digg

[Anonymous Coward]

Did you read that page? Most of those features are supplied by 3rd party tools, not built into the database itself. I remember an article a while back about PostgreSQL replication, and all those tools had some serious shortcomings in them.

Re:No Digg

[Danny Rathjens]

especially troubling is the fact that this article compares the then-unreleased MySQL 5 to the Postgres 7.x series. Nearly all the drawbacks to Postgres that this article highlights have been addressed in the 8.x series.

I find this statement quite amusing in light of all the past comparisons by postgresql fanboys that compared newer versions of postgresql to old versions of mysql that made mysql look bad because it didn't have particular features yet, e.g. stored procedures or triggers.

Re:No Digg

[Danny Rathjens]

I realized that a lot of this type of confusion likely stems from the fact that the two projects have different versioning philosophies and development cycles.
mysql made 5.0.0 in 2003, made 5.0.2, 5.0.3, etc. and declared the 5.0.15 version stable for release in october 2005.
postgresql made 8.0beta1 in july 2004, made 8.0beta2, etc. 8.0rc1, 8.0rc2 etc. and released 8.0 in january 2005.

Summary?

[Anonymous Coward]

MySQL as good? You mean besides the weird MySQL-proprietary SQL stuff and the data integrity?

Re:Summary?

[Architect_sasyr]

As opposed to TSQL or any of the other sorts? Or maybe the proprietry stuff in Oracle... which would be the first SQL...

Re:Summary?

[Anonymous Coward]

AFAIK the first SQL is that of IBM's.

Old news

[daffmeister]

From the site:

"Last modified: February 15, 2005."

Re:Old news

[suv4x4]

Next article on Slashdot: 486 SX vs 486 DX

Re:Old news

[cloudmaster]

To paraphrase a comment from farther up, "yeah, floating point is nice. I implement it in my application on the SX anyway."

Re:Old news

[tacocat]

Too bad it's old.

One of the biggest problems I've seen with these comparisons is that MySQL and Postgres seem to have a wildly different release cycle and when one is "up" the other is "down" on it's lifecycle.

I do like Postgres 8.1 a lot. It's kickin'. I also like Postgres in general because it's got more options on how to do stuff with it's RULES, FUNCTIONS and all those goodies.

Re:Old news

[blafasel]

and vi vs. emacs, i hope.

This is outdated and incomplete

[Tetard]

MySQL does not have tablespaces, only recently support for views, subselects, transactions was added and triggers and stored procedures are still considered alpha. No bitmap indexes... This is by far not the best comparison I've ever seen.

Re:This is outdated and incomplete

[Anonymous Coward]

triggers and stored procedures are both available in a stable release - 5.0

Re:This is outdated and incomplete

[Anonymous Coward]

Wow, more MySQL lies on Slashdot. It's amazing the amount of misinformation here about MySQL.

> MySQL does not have tablespaces,

Yet another lie. Here's what I'm using on one of my systems about six months ago to create a tablespace under MySQL:

CREATE TABLESPACE ts_1
    ADD DATAFILE 'data_1.dat'
    USE LOGFILE GROUP lg_1
    INITIAL_SIZE 32G
    ENGINE NDB;

And to move a table into that tablespace:

ALTER TABLE user TABLESPACE ts_1 STORAGE DISK ENGINE NDB;

The documentation:

http://dev.mysql.com/doc/refman/5.1/en/mysql-clust er-disk-data.html

Again, why is there so much absolute BS posted here about MySQL?

stability

[oliverthered]

Having foreign keys, views, subselects, and transactions can all be very attractive in PostgreSql -
if you need them and you will make any use of them. If you don't need them or won't use them, then
you're probably better off with MySQL and its superior performance.

PostgreSql is more stable than MySQL, (and has better performance when saturated), shouldn't you take that into consideration?

Re:stability

[TheRaven64]

According to TFA, 'MySQL does very good job even on the busiest sites,' while for PostgreSQL 'Random disconnects, core dumps and memory leaks are usual.' This flies in the face of my own experience and testing results I have seen. Under heavy load, PostgreSQL has a habit of slowing to a crawl, while MySQL just dies. How many web pages have you seen where the entire text was a PHP error saying it was unable to connect to the MySQL server?

Re:stability

[scribblej]

This is what blows my mind, too. I've been using postgresql 7.x (yeah, I'm a Debian user) for years and I have never, ever seen it crash, disconnect, dump core, or leak memory. Ever.

I've never seen MySQL do those things, either, to be fair, but I don't use it as much, and I *did* have it destroy a bunch of my data once when a machine was rebooted without being properly shut down.

The comment that Postgresql is unstable, even in the older version numbers, seems to indicate some kind of separation from reality. I didn't see anything in the article about their testing methods. I wonder, given some of the other complaints, if they tested them on *WINDOWS* despite the lousy support for Windows, and the outright recommendation that you don't try it, from the devs.

Seriously. Anyone else with me on that? Even outdated as this review is, it STILL doesn't make sense... unless you think maybe they did it on Windows. Then it makes more sense. Postgresql on Windows used to suck, and for good reason. It wasn't supported, either, and for good reason.

Re:stability

[Blimey85]

Just because you see that error doesn't mean that MySQL has "died". If the number of people hammering your site exceeds the number of connections you have allowed, that message will come up. Granted by that point the server is probably crawling but maybe not. You may be doing a dump from a particular table causing it to remain locked while the dump is in progress. All queries to that table will stack waiting for it to get unlocked and that can cause your connections to max which would in turn cause that particular error message. Not the only caue of course but the main one I've come across.

Re:stability

[rycamor]

It flies in the face of my experience, too. Even with the older 7.x series, if I accidentally ran a stupid query, (for example, a join on multiple tables, forgetting the WHERE clause, resulting ridiculous multiplication of returned rows) PostgreSQL would literally run that query for 24 hours without dying, until I finally killed the query or re-started the server.

Come to think of it, I haven't had PostgreSQL ever die on me, once. Even when my server crashed and rebooted, PostgreSQL would just happily pick up where it left off and get back to work.

Meanwhile, on more than one Linux server I had the pleasure to maintain, MySQL was notorious for randomly dying.

Something tells me that the person who wrote this report simply read the MySQL mailling lists, and compiled his report from the discussions, without any real testing, much less considering that a checklist is meaningless unless you also evaluate how WELL each DBMS implements said feature.

Re:stability

[Anonymous Coward]

It was possible for PostgreSQL ( 8.0 - 8.0.5 ] to corrupt its tables by allocating an already used page. But this could only happen if two queries for data were allocating (insert,update) a page at the same time. It affected one of my clients. Where he was doing an import from another database (app sends a hell of a lot of INSERTs) and a pg_dump | psql copy of another database from another server.

But I had MySQL corrupt stuff ALL the time (in comparison). ALL the time meaning more than a handful of times in a yearly period. In one release, you could do it by a simple transaction that actually wasn't which caused data corruption (app thought a transaction is a transaction - rollback should not fail!)

Re:stability

[localman]

Any DB misconfigured is going to die under load. MySQL can be configured to be extremely stable -- we've been running the fastest & most reliable retail site online for the past year now with MySQL as the DB.

I've got nothing against PostgreSQL -- just never used it. I'm sure it's a fine piece of software, but please don't spread falsehoods about MySQL just because people don't know how to configure it. That would be like me claiming PostgreSQL sucks because I couldn't get it working easily. It's all about knowing what you're doing in any case.

Specifically, most of the errors you're seeing are because they've got it configured to use more memory than their 32 bit arcitecture supports. It's fairly easy to misconfiure so that in a high traffic situation the MySQL process will use over 2GB and then the OS shuts it down. The options are to go 64 bit or to configure it to use less memory for performance or limit the connections -- just like with Apache's MaxClients option.

Cheers.

Re:stability

[slamb]

According to TFA, 'MySQL does very good job even on the busiest sites,' while for PostgreSQL 'Random disconnects, core dumps and memory leaks are usual.' This flies in the face of my own experience and testing results I have seen.

Agreed; that statement is a big fat lie.

Under heavy load, PostgreSQL has a habit of slowing to a crawl, while MySQL just dies. How many web pages have you seen where the entire text was a PHP error saying it was unable to connect to the MySQL server

As much as I hate MySQL, I can't blame it for this one. I'd say that under heavy load, PostgreSQL keeps on going fine and MySQL slows to a crawl, but both still operate.

PostgreSQL will have the same problem when misconfigured in the same way. It's pretty simple to avoid: add up the maximum sizes of all pools that connect to the database. Make the databases's maximum allowed connections at least that number + however many administrative connections you might open. If it's a huge number, you'll probably want to reduce the pool sizes for performance. (And reduce it a lot further for MySQL than for PostgreSQL, because MySQL does not scale. It does not perform under high concurrency, and additional processors don't help.)

If it's possible you'll be accepting many connections quickly under heavy load, up the listen queue size to that number as well. (Not sure if this is tunable in the config file - I've only heard of this problem in practice on a MS SQL server with something like 20 nodes that synchronized with NTP and started doing database stuff in a crontab. I think they just did listen(fd, 5) so that didn't work out.)

Re:stability

[Ugot2BkidNme]

I have to agree with you I find this argument pretty bad. I have used DB2, Oracle, SQL Server 2000, Firebird, MySql and Postgre in Production Environments over the last few years. I have to say they all have good points and bad points. And since I do bother to take the time to set things up properly I have to report that I have never had one of these servers crash in a production environment. I have had slow-downs and other problems that are RDBMS specific but I have to say they are all decent to great products. I am not going to give my personal opinion about which one is better because that would be pretty stupid since they all have advantages and disadvantages.

I will say this however Most of the crap people experience with MySql is user configured error. Much like windows it is made easy to use not easy to use right. I have a windows server that runs very stable sure I have to reboot it every month or two for security patches but it never crashes and once a month is acceptable maintenance for me but I do know what I am doing with my server. Likewise I have Linux servers running perfectly even have a NetBSD Server running flawlessly. Does that make any of them better then the others? No, of course it doesn't but would your average windows user know how to set up a BSD or Linux box properly? More then likely not.

Most my experience helping users of MySql in particular are a group of people who don;t know how to use JOIN's let alone how to configure their MySql properly. They use some of the most horribly ugly PHP code I have ever seen. However, being a DBA turned developer I have to say most developers don't know how to set up and properly use an RDBMS. is this MySql's Fault because the users don't know what they are doing? If you mess up when building something in the real world because you didn't bother to read the manual does it make that product suck?

Overall I have to say This article was absolute crap the comparison were not valid even at the time of writing this. If you want to pick a RDBMS pick one that fits your needs. Cost? Required Throughput? Languages? Character Sets? Your Level of Skill? Answer these then you should be able to easily find the right RDBMS for you.

Re:stability

[Anonymous Coward]

Any DB misconfigured is going to die under load. MySQL can be configured to be extremely stable -- we've been running the fastest & most reliable retail site online for the past year now with MySQL as the DB. Your site is the fastest due mostly to page weight. What's that have to do with the database? Sure, if your database was slower, it would keep your speed down more. However, the measure used is more affected by network latency and page weight than by page parse time (which is what the database would affect).

Compare Amazon and Target. The two literally use the same databases for products, etc. (you can buy things in the Target catalog from Amazon). However, by that article's measure, Target is noticeably slower than Amazon.

Congratulations, great work, but simply not relevant to the current discussion.

Re:stability

[edwdig]

This is what blows my mind, too. I've been using postgresql 7.x (yeah, I'm a Debian user) for years and I have never, ever seen it crash, disconnect, dump core, or leak memory. Ever.

Same here. At one point I was coding a C plugin for PostgreSQL (wanted to access data from a sql server box in pg queries), and even then I never saw it crash. If my code had a bug in it, the worst that happened was that one client connection died while everything else was ok.

For those who don't know, PostgreSQL forks off a seperate process for each connection, which makes it hard for an error in one query to effect another one.

Re:stability

[localman]

I think my post was pretty relevant since the "current discussion" was about stability. That's why I included the reliability metric. I included the speed metric as another positive which is, despite your saying otherwise, at least partly due to the good database performance. In any case the two taken together do much to rebuff the original poster's claim that MySQL dies under heavy load.

Cheers.

Re:stability

[oliverthered]

Try overloading MYSql for youself, it dies.

Re:stability

[Blimey85]

I never said it doesn't I was merely explaining a possible different cause and one that I've come across more often than MySQL actually crashing.

Re:stability

[ThePhilips]

I've got nothing against PostgreSQL -- just never used it.

Let me share bit of my experience. Grab any commercial RDBM developer/user and put him to PostgreSQL and MySQL.

In my experience, Pgsql would slowly crunch any query one would throw at it. On other side MySQL would DoS your server by trying to actually run query it cannot optimize (e.g. many ORs or IN in SELECT's WHERE). And it cannot optimize hell a lot of types of queries. RTFA in fact describes MySQL performance that way: "Supports both left and right outer joins using both ANSI and ODBC syntax." Notice that no exceptions about Pgsql queries are made: plain SQL92/99 referred. DoSing server - especially if it is hosted is last one wants while developing application for clients.

Happened to me - as developer - many times. Actually all the time. Though, normally clients end up running dumb queries and are pretty happy that they can actually filter their few thousands of records. Nothing extraordinary in real life (95% of applications) is needed.

End result is good stable performance of MySQL on real applications vs. ease of development with Pgsql. YMMV.

Old and wrong

[ldapboy]

postgresql has a native Win32 version, complete with an installer, service support and does not depend on cygwin.

Re:Old and wrong

[Anonymous Coward]

Yeah but In 2005 it didn't!

You can't say it is "old" and "wrong" when it is wrong because it is old.

Re:Old and wrong

[Prof.Phreak]

...and does not depend on cygwin.

Then why does it complain if it finds cygwin in the PATH?

Re:Old and wrong

[Anonymous Coward]

Because it doesn't-depend-on-it to such an extent that it actually complains if it finds it!

Seriously, it un-depends the fuck out of cygwin :)

I don't think so

[ajaf]

PostgreSQL seems to be a more "professional" database. Speed isn't everything, check on the article things like DATA INTEGRITY, SPECIAL server-side FEATURES, LOCKING and CONCURRENCY SUPPORT or LARGE OBJECTS.
I choose PostgreSQL for my applications.

Re:I don't think so

[TheRaven64]

Speed is a lot, but from what I've seen about the only thing MySQL beats PostgreSQL on in terms of speed is simple SELECTs (and then only if you aren't doing many INSERTs, since MySQL has very coarse-grained locking). If that's all you are doing, you would probably be better off with SQLite or even flat files.

Unbiased ?

[UncleH]

Just take a look at the description per item. I couldn't possibly call this unbiased in any way.

Out of date

[Anonymous Coward]

Look at the bottom of the page "Last modified: February 15, 2005."

I didn't even get to the second item and found information that isn't valid. PostgreSQL installs and runs as service on Windows out of the box (not that I recommend running anything on Windows if you can help it).

Out of date?

[zootm]

Last modified: February 15, 2005.

Surely, given the speed that these suites tend to be developed, this comparison is tragically out of date by now? There were a few pieces of comparison which gave the impression that it needed to be updated ("Expect PostgreSQL 8.x to continue this trend."), and I'm fairly sure that both systems have advanced considerably in most, if not all, of the criteria specified.

Re:Out of date?

[zootm]

Well, it appears that just about everyone else managed to type that comment quicker than me. Curses.

Re:Out of date?

[Tim C]

So, would it be fair to say that your comment is out of date..?

Re:Out of date?

[zootm]

I think more "belated"...

biased

[dheera]

who said everything done by the government needs to be unbiased? this isn't the court or the elections or something, this is them deciding on a piece of software to use. for a little crap decision like this they should pick whatever makes them most comfortable and crank out the data everyone wants.

i think mysql is better because of the name... "post-greeeeee-SQL" sounds awful.

Re:biased

[kdemetter]

actually , this is how you pronounce it : http://www.postgresql.org/files/postgresql.mp3

Re:biased

[Anonymous Coward]

i think mysql is better because of the name... "post-greeeeee-SQL" sounds awful.
Well theres your problem...only one e in postgres.

Adding all those extra e's is bound to make the name sound funny

Re:biased

[iangreen]

> "i think mysql is better because of the name... "post-greeeeee-SQL" sounds awful.

you HAVE to be kidding me.

the fact that it's called "MY"sql and it uses "my.conf" scare the bejesus out of me ever considering even using it for an enterprise solution.

Not similar to my experience

[seebs]

I have been involved with a smallish ("hundreds") installation of Movable Type using a mysql backend.

One comment spammer can completely annihilate it.

One developer I talked to once did some testing. On one simultaneous connection, mysql was way faster. By five or so, they were close. At ten, PostgreSQL was definitely winning. At a hundred, he was simply unable to get a single MySQL server to complete the test successfully, let alone do it quickly.

The impression I get is that PostgreSQL uses more robust algorithms, with higher constant costs and lower quadratic costs. In any event, never had any problems.

As noted elsewhere, these comparisons are quite old...

But in any event, in my own experience, mysql is a lot easier to blow up by overloading than postgres is, at least if you have a lot of writes going on. For pure-lookup functions, it might do better -- but a lot of modern database apps are pretty compulsive about saving at least something every time someone touches them. (For instance, modern vBulletin saves last visits, threads seen, and so on; all of that adds up to a huge load on the database server.)

Re:Not similar to my experience

[Rich0]

I've been wanting to switch to postgres for a long time, but do you know what the main thing is that is holding me back?

THERE IS NO ODBC FOR LINUX (or equivalent).

Why should apps that use a db be linked against libraries that are db-specific? Why not make everything modular. Then developers can stick to ANSI SQL and let the user pick whatever database they want (mysql, posgres, oracle, sql-server, access, whatever).

Right now all of my apps support mysql, and a few support postgres. So either I run two database servers and maintain/upgrade/understand both of them, or I just run mysql and hope that the app devs are good since the DB won't protect them if they want to break their referential integrity.

Mysql has come a long way, though, and it concerns me less than I used to. Before I had to cringe whenever I went to their website and was treated to articles that amounted to "well, transactions are overrated anyway - they slow everything down". Well, sure, but what exactly is the point of using a database in the first place?

Re:Not similar to my experience

[walt-sjc]

We use both PostgreSQL and MySQL for a large web-based application that does a reasonable mix of reads / writes (sessions / profiles are in MySQL so it gets MANY MANY writes.) Neither MySQL nor PostgreSQL has problems handling many many connections. Our load frequently hits around 1000 connections on Postgres and 4000 on MySQL on individual database servers (we replicate too.)

Obviously you need to tune your environment (there are a plethora of options including table types which can impact things a LOT) to the load, so if you are running into problems at 100 connections, something is wrong.

Re:Not similar to my experience

[hey!]

Somewhere in here, there's a tortoise and hare analogy trying to get out.

It seems to me that if you step back from the details, there is a fundamental difference in style between the two systems that could be summarized thus:

Postgres: emphasizes completeness, correctness, and conformance.

MySQL: emphasizes immediate practicality.

One style is not intrinsically better than the other. Given time their results may begin to converge, which I think is starting to happen. However, I am not surprised that many people are starting to give Postgres a second look after having dismissed several years ago. The Postgres strategy is a long term one. Early adopters of Postgres were a minority with a particular interest in the relational model and for whom conformance was a relatively high priority. Pragmatists who wanted to cherry pick a few of the model's most important advantages were drawn to MySQL.

Re:Not similar to my experience

[Tony Hoyle]

THERE IS NO ODBC FOR LINUX (or equivalent).

WTF? You *do* know Microsoft didn't invent ODBC???

There are at least 2 different ones for Linux that I can think of. Every commercial Unix either has ODBC or has it available as an addon.

Re:Not similar to my experience

[seebs]

Totally agreed. There are a number of apps (WordPress is one of the most obvious) that are permanently tied to MySQL.

Many, of course, make use of the extra "feature" MySQL has. Remember, "embrace and extend" is only evil when Microsoft does it; when it's a product you can download free, being completely trapped in a system that someone else controls the development of is GREAT!

Re:Not similar to my experience

[Wdomburg]

For all it's faults, MySQL does scale with a largely read-only data set. We currently have twenty-eight production servers running and about twenty development and testing machines. On the busiest servers we're pushing somewhere in the neighborhood of 4000 queries a second sustained.

Write performance can certainly be an issue, but it depends largely on the application and the table backend. For example, if you can avoid doing deletes on a MyISAM table INSERTs get appended, allowing concurrent reads.

I've not looked at how MT uses the database, but blog software would imply large variable length writes. Definitely not an ideal application for MySQL.

Re:Not similar to my experience

[AKAImBatman]

THERE IS NO ODBC FOR LINUX (or equivalent).

What the hell? If that were true, then what is this? And this? Not to mention this.

Are you making a reference to Windows-specific APIs or something? Because I don't understand how you can shout that ODBC doesn't exist for Linux/Unix?

Re:Not similar to my experience

[consumer]

The MySQL scaling problems you're describing are due to using MyISAM tables for an OLTP application. Don't do that. They're fine for read-mostly applications, but the locking is not efficient enough for more. Use InnoDB tables for OLTP apps. The InnoDB tables use an multi-version concurrency control approach just like Postgres does.

Re:Not similar to my experience

[vadim_t]

This is probably due to two things:

1. MySQL's locking is VASTLY inferior to MVCC
2. Many of MySQL's users are complete morons

Locking: MVCC allows really good concurrency, to the point where SELECT * FROM table is a perfectly good backup strategy. This kills row locking databases like mysql because this results in locking the whole table, as rows are overwritten in place, and you can't allow them changing under you while reading the table.

2. MANY MySQL users have no idea of what a decent database should be like, and to boot use it in completely braindead ways. Many of those applications are coded with no regard for the underlying DB (locking issues) and general DB practices (normalization, not requesting more than you need).

For example, somebody else here posted about an application that does a "SELECT * FROM table" then only uses the 5 first rows. Let's compare what happens:

MySQL: Whole table locked while reading, absolutely lousy concurrency as no concurrency is possible on that table.

PostgreSQL: Not very good performance due to reading more than necessary, but no concurrency problems at all.

My guess is that the test you mention had something of the sort. MySQL got bogged down with locks and ground to a halt, while postgres continued chugging along just fine.

Re:Not similar to my experience

[thedave]

If you can't do it with 'SELECT *', or 'UPDATE *' or 'DELETE *' I don't want it.

It's all or nothing for me, baby!

I only wish I could 'INSERT *' that would make the data entry work so much easier.

Re:Not similar to my experience

[Anonymous Coward]

> MySQL: emphasizes immediate practicality.

I find it eminently practical to be able to trust the database with my data so my application doesn't have to check it.

Otherwise, I may as well use Berkeley DB. Come to think, that was MySQL's second backend. Maybe MySQL should bundle a Postgres table backend.

Re:Not similar to my experience

[Rich0]

Ok, I'll have to beg forgiveness about not knowing about these projects. Then again, it might be because I don't know of a single application that supports them.

Hmm - checking my gentoo portage repository about the only things that do are programming libraries and languages.

Once programmers start using unix odbc then perhaps I'll consider using it as well. Is there some reason nobody is? Just about everything on windows uses it (but not everything - this issue is by no means restricted to linux and we have an app that only runs on one specific (old) version of Oracle because the coders thought it wise to use all kinds of proprietary extensions).

ANSI SQL is your friend...

Re:Not similar to my experience

[AKAImBatman]

Once programmers start using unix odbc then perhaps I'll consider using it as well. Is there some reason nobody is?

Probably because everyone is using JDBC instead. ;)

Re:Not similar to my experience

[kimvette]

http://www.unixodbc.org/

Both SuSE and Ubuntu offer ODBC. I don't know about centos as I've never needed ODBC on it, but I'm sure it's there, and if not, being open source, it can be downloaded and compiled.

Re:Not similar to my experience

[RAMMS EIN]

``Once programmers start using unix odbc then perhaps I'll consider using it as well. Is there some reason nobody is?''

Many programming languages come with some database-independent SQL API: Perl and Ruby have DBI, Common Lisp has CL-SQL, PHP has something I forget the name of (dbx?), Java has JDBC, etc. Between all that, I guess there just isn't a need for ODBC. Which was also my first thought when I read your original comment.

Re:Not similar to my experience

[Anonymous Coward]

I use unixODBC + Postgres ODBC driver under linux -- when I run run Windows programs under WINE.

Re:Not similar to my experience

[edwdig]

For example, somebody else here posted about an application that does a "SELECT * FROM table" then only uses the 5 first rows. Let's compare what happens:

MySQL: Whole table locked while reading, absolutely lousy concurrency as no concurrency is possible on that table.

PostgreSQL: Not very good performance due to reading more than necessary, but no concurrency problems at all.

Just a few details on that...

If you do "SELECT * FROM table", read 5 rows, and close the connection, it won't be too bad. PostgreSQL will start returning results as they are read, and will stop when you close the connection.

If you do "SELECT * FROM table LIMIT 5", PostgreSQL will choose the a query plan that gets the first results fastest. This plan may take longer to return the entire result set, but it will get you data faster. It will do this any time the limit keyword is present. It will obviously stop processing as soon as it hits 5 results.

If you do "SELECT * FROM table ORDER BY field LIMIT 5", PostgreSQL will retrieve the entire data set, sort it, then return the first 5 results. An index won't help on this query, as indexes do not store the visibility information of a row (as in, is a particular row active, deleted, or not yet written from the viewpoint of the current transaction).

Re:Not similar to my experience

[WuphonsReach]

I'll agree with that. From what I've seen over the years, the PostgreSQL folks are pretty big on "never lose data" which is what correctness and attention to detail gets you. It may not have been the fastest database to use, but I don't have to worry that it's doing something incorrect under the covers for the sake of speed.

The only thing holding us back from using PGSQL prior to v8 was lack of good Win32 support. (While we're moving towards Solaris/Linux, it will still be a few years before we retire all of our Win32 boxes.)

The other reason I prefer PostgreSQL over commercial offerings is that it's one less set of licenses that I need to track. (Yay!)

Re:Not similar to my experience

[Anonymous Coward]

Not really. This is a function of the type of database activity. The fact that you don't know that explains why you consider MySQL to be worthy of comparison.

And it sucks anyway.

[khasim]

TFA lists "SQL standard COMPLIANCE" as a category and says about MySQL:

MySQL uses SQL92 as its foundation. Runs on countless platforms. ...

And what is the next category?

PLATFORMS

Which says:

There are binary distribution for most of the supported plataforms.

Why is "Runs on countless platforms" an item under "SQL standard COMPLIANCE" when "PLATFORMS" is also a category?

It looks as if the "reviewer" was trying to bulk up MySQL's characteristics. Even when it was totally inappropriate. A checklist of features would be more accurate in this case. With some evaluation of how well those features are implemented.

I want to see more databases - Firebird, Derby

[Anonymous Coward]

Glad to see the comparison, but I would really like to see is a comparison that includes the new 2.0 release of Firebird. Their new release is impressive, but I dont know how the features pan out with MySQL or Postgres. Including Derby would also be nice.

The most important factor to me in any comparison is the licensing agreement. I like a very open agreement and the MySQL license requires you to release the source code to your product in some cases, or you have to purchase a license from them.

Re:I want to see more databases - Firebird, Derby

[thedave]

You can't compare a database to a browser, silly.

Remember this is 2005!

MySQL is ridiculously easy to configure

[MikeRT]

You have to give the MySQL guys credit for the fact that it is an incredibly easy product when it comes to configuring it for your needs. For me, out of college, going to Oracle was a culture shock because the process of configuring Oracle was so convoluted and drawn out for simple stuff. I know that Oracle and PostgreSQL can be much more powerful than MySQL, but there is something to be said for how easy it is for a developer to install MySQL and just start working with it.

Re:MySQL is ridiculously easy to configure

[Schraegstrichpunkt]

You have to give the Notepad guys credit for the fact that it is an incredibly easy product when it comes to configuring it for your needs. For me, out of college, going to Vim was a culture shock because the process of learning Vim was so convoluted and drawn out for simple stuff. I know that Vim and Emacs can be much more powerful than Notepad, but there is something to be said for how easy it is for a developer to install Notepad and just start working with it.

Re:MySQL is ridiculously easy to configure

[Tony Hoyle]

Oracle Express on Windows is dead simple. Click 'setup'. It creates the default tablespaces for you and there's a nice web frontend for creating tables etc.

Perfectly fine for learning it. You can start on the real configuration later.

Re:MySQL is ridiculously easy to configure

[MP3Chuck]

Part of me sees the point you're making, but another part of me say "Yea, and ... what?" Does Notepad, embarrassingly simple though it may be, not still have appropriate uses?

Re:MySQL is ridiculously easy to configure

[MrNemesis]

Maybe your notepad.exe is a different one from mine, but I can imagine very few bona fide developers (apart from the very newest) using notepad, since IMHO it's unusably bad for all but the simplest of stuff. No syntax highlighting and annoying cursor placement issues being two of the most obvious problems. There are a million and one text editors superior to notepad available for win32 that are just as esy to use fr base functionality.

OTOT, no-one would think of comparing vi/emacs/whatever to notepad. A more valid desription would be, say, kedit and notepad.

Re:MySQL is ridiculously easy to configure

[value_added]

Part of me sees the point you're making, but another part of me say "Yea, and ... what?" Does Notepad, embarrassingly simple though it may be, not still have appropriate uses?

Short answer: No.

Longer answer: None at all.

Re:MySQL is ridiculously easy to configure

[multipartmixed]

Dude, his post is 100% on the money.

The problem is that you apparently missed his point entirely.

Re:MySQL is ridiculously easy to configure

[jalefkowit]

I don't think your example cuts the way you intend it to.

1. For a lot of tasks, Notepad is all the editor you need (where "editor" is defined as a non-WYSIWYG text processor). For making a quick change to a config file, Notepad works just as well as Vim does.
2. In Notepad, you open the program and start typing. Very intuitive for new/untrained users. In Vim, you can't start typing until you figure out how to turn on Insert mode. Very un-intuitive.
3. Notepad on Windows is like Vim on *nix: it's the only editor that is always guaranteed to be there.

So: Notepad isn't as powerful as Vim/Emacs, but that doesn't mean it's not useful -- just that its utility is mostly for basic, low-end tasks where overall power is less important than a quick time-to-completion.

And come to think of it, you could say the same thing about MySQL vis a vis PostgreSQL -- though the MySQL developers have done a better job at scaling up their product than Microsoft has with Notepad.

Re:MySQL is ridiculously easy to configure

[walt-sjc]

Nice troll. Comparing notepad to emacs is not the same as MySQL to PostgreSQL. Not even close.

I think the point that the OP is trying to make is that PostgreSQL can be more difficult to install and use for no damn reason. As someone who uses both MySQL and PostgreSQL, the Postgres guys could (should) take some of the simplicity ideas of MySQL and incorporate them into PostgreSQL which would be a Good Thing. Most of the things that make MySQL easier to install / configure / use have NOTHING AT ALL to do with the actual back-end features of the database.

Re:MySQL is ridiculously easy to configure

[MrNemesis]

That'll teach me not to read the parent post properly... damn thing was marked "informative" when I read it. Good job I made a fool of myself instead of moderating ;)

Re:MySQL is ridiculously easy to configure

[Schraegstrichpunkt]

It sounds like my example cut exactly the way I intended it to.

Re:MySQL is ridiculously easy to configure

[suggsjc]

It isn't about being intuitive its about using the best tool (for the job).

If you are going to be making changes to ANY config file and you find Vim too cumbersome then you probably shouldn't be making that change in the first place. In fact, you said it yourself..."Very intuitive for new/untrained users"...do you really want them making changes to config files?

Funny how even on a thread about MySQL vs PostgreSQL (which has enough fanboyism) we still get off on Vim/Emacs/Notepad rants.

Re:MySQL is ridiculously easy to configure

[jalefkowit]

It isn't about being intuitive its about using the best tool (for the job).

That was my point -- that the best tool does not always map to the tool with the most features.

Re:MySQL is ridiculously easy to configure

[ThJ]

I use it for taking notes. :P

Re:MySQL is ridiculously easy to configure

[lysdexia]

As a rather dense guy who tends to get a little huffy when I'm forced to read anything other than "QUICKSTART", I must agree.

At least on OpenBSD, PostGreSQL is more fiddly and difficult to install than MySQL (usually the handy comments that the OpenBSD folks add at the end of the pkg_add process give you enough to go on. Theo and the others tend to do most of the thinking ahead for you. More time for me to rock back and forth staring at last months regex &c ...)

In the *very limited* scope of my testing before the descision was made for me, the two were about the same for our needs, except that massive amounts of UPDATEs were noticeably slower on MySQL - but pulling my head out of my sphincter and using stored procedures for the big updates solved most of that. In fact I suspect that much of the mahooha spouted about both of these projects is coming from the same folks responsible for some of the truly clowny queries in some very popular projects I downloaded when I was learning how to be a merely mediocre accessor of SQL services. :-)

A poster above asserts that many projects would be better off using flat files. I must agree. In fact, one could argue that MySQL's ease of installation is (through no fault of the fine folks at MySQL AB) responsible for many misused resources. I only started using SQL in the past couple of years. Most of my projects were simply reading data into web pages with only two (100k =-) updates per daily cycle, simply using BerkeleyDB (python's anydbm module treats it as a dictionary type, making it ridiculously easy to use) was a much better choice. I've never pestered my provider for an overhead comparison vis modpython vs modpython+MySQL, but I'm betting there's an appreciable advantage with the flat db (or just a text file) for low-to-medium traffic site (did I just sin against relativity here?).

Re:MySQL is ridiculously easy to configure

[Brummund]

Back in the old days, we had a programming seminar at work. Rather bored in the evening, and not sufficiently drunk, we spent 15 minutes using MFC, and had made an app we called Visual Notepad++. It supported CTRL+S, CTRL+P, CTRL+O, AND files larger than 64 kilobytes. Microsoft finally caught up with NT 4.0, I think. :-)

Re:MySQL is ridiculously easy to configure

[daBass]

Yes and no. Postgres is probably 10% harder to start using, but when you have taken the time to read a little (and I mean a little) you will find that it is actually much easier and predictable in its configuration.

In fact, compared to Postgres, I find maintaining MySQL horrbly un-intuitive.

For instance, you actually create users with passwords first in Postgres, which you then GRANT things to - as opposed to MySQL's way of doing it all on one go. Obviously, the Postgres way takes an extra step, but makes much more sense as you reuse that same user over and over again in other grants without ending up with resetting passwords.

Re:MySQL is ridiculously easy to configure

[newt0311]

thats what emacs in latex mode with flyspell enabled is for.

Re:MySQL is ridiculously easy to configure

[drew]

You shouldn't group Oracle and PostgreSQL together like that. In my experience, PostgreSQL is about as easy as MySQL to set up (actually easier, in my opinion). On the other hand, I would rather have major dental work done without anesthetic than set up one more Oracle database ever again. Personally, I think Oracle goes out of their way to make it as difficult as possible to prop up the market for $80K/yr DBA's.

Re:MySQL is ridiculously easy to configure

[mcrbids]

...there is something to be said for how easy it is for a developer to install MySQL and just start working with it.

And, since this is a Mysql v. Postgresql comparison, here's what it takes (no kidding!) to get PostgreSQL up and running on RedHat ES or CentOS:

# yum -y install postgresql-server;

# service postgresql start;

Yep! That's it!

Re:MySQL is ridiculously easy to configure

[Anonymous Coward]

"You have to give the MySQL guys credit for the fact that it is an incredibly easy product when it comes to configuring it for your needs. blablablah ..."

How is MySQL easier to install and work with than PostgreSQL?

Re:MySQL is ridiculously easy to configure

[Slashdot Parent]

If notepad meets your needs, then great. Personally, it doesn't meet mine.

Same goes for MySQL vs. Postgresql. The environment that I'm working in right now needs partitioning, advanced clustering, and XA transactions, so Postgresql doesn't meet our needs. Postgres is actually more difficult to configure, too.

I guess I didn't have a point, then. The more feature-rich database is also easier to administer. Who knew?

Re:MySQL is ridiculously easy to configure

[waveclaw]

Part of me sees the point you're making, but another part of me say "Yea, and ... what?" Does Notepad, embarrassingly simple though it may be, not still have appropriate uses?

Short answer: No.

Longer answer: None at all.

Oh, come on, like you never copied an Engineer's instruction out of a Word document and had to paste it into notepad to clean out all the autotext special characters and 'extra' special formatting information? I'm certainly happy that my option hyphens aren't converted to em-dashes and my quotes turned into something totally differen't. I know a guy that even resorted to using the Windows Run dialog to do this. Notepad is great for stripping junk out of a text cut'n'paste from or to Microsoft Office Apps. It's almost like a working clipboard.

<ObTopic>
Now you probably wouldn't catch me passing my data through MySQL before sending it to a database. Oracle and Postgresql have good integrity checking but automated input validation can only do so much.

I kid! After all MySQL is way faster than PostgreSQL or Oracle Community for the 3 people that read my 'blog. In a month.

Really, does anyone have a good link for converting MySQL apps to use PostgreSQL or Oracle? Just automatic conversion of the SQL db creation scripts would be nice.
</ObTopic>

Re:MySQL is ridiculously easy to configure

[deanoaz]

Yes,

I use Notepad every once in a while to strip all the formatting off some piece of text I found in a formatted document or on the web. That's probably about all I ever use it for anymore, but it is a use.

Re:MySQL is ridiculously easy to configure

[Furry Ice]

Two-phase commit and XA JDBC support was added to PostgreSQL in 8.1. PostgreSQL has pretty good clustering. MySQL's requires your entire dataset to fit into RAM, making it not such a hot solution for many uses.

Re:MySQL is ridiculously easy to configure

[ocelotbob]

That was changed over a year ago.

Re:MySQL is ridiculously easy to configure

[Anonymous Coward]

"New/untrained in vim" is not the same as "clueless newbie mucking around on a server", you elitist asshole.

I love vim, but I know plenty of people with the intelligence to edit a damn text file who don't know the first thing about vi.

Happy 2007

[Alworx]

The article is dated Feb 15, 2005.

Since then things have changed a lot, especially regarding Win32 service support, ALTER TABLE features, stability and security.

I suggest we scrap this post altogether

There are several problems

[kahei]

1 -- This article is years old.

2 -- This article is posted solely to stir up (repetitive) discussion.

3 -- This article pretends that MySQL is a real database, even though in order to do so it has to make gigantic leaps like considering data integrity to be not really all that important in a database.

4 -- This article trolled me.

I'd rather

[Klaidas]

I'd rather have a new (not THAT old) comparison between Oracle and MySQL

Re:I'd rather

[Anonymous Coward]

I'd rather have a new (not THAT old) comparison between Oracle and MySQL

Why? That's a bit like comparing some ricer's honda to an indy car. Oracle is in a different league.

Re:I'd rather

[Angvaw]

Oh yeah? Well [contrives test query using insufficient hardware for Oracle, ignores transactions/logging/concurrency controls/scalability/advanced query options/etc.] my tests indicate that MySQL is 1057 times faster than Oracle! QED

Re:I'd rather

[Anonymous Coward]

Not exactly.

MySQL is the ricer's honda that sometimes kills the driver by itself (mangles your data/bad data integrity)
PostgreSQL is an Indy car.
MSSQL is a F1 car.
Oracle is a dragster.

Comnparing MySQL to the big 3 (MSSQL/DB2/Oracle) is a waste of time. MySQL just doesn't compare at all.

Outdated and Silly

[ClayDowling]

It's been a long time since any of their PostgreSQL statements were true. It's a very happy native windows service with a nice installer, and the administrative interface is very easy to use. Let's try posting current reviews of software, rather than reruns from a year or two ago.

I use PostgreSQL

[Anonymous Coward]

Main reasons:

1. Very strict about data integrity. Entering "sdf" into a date field will throw an error in PostgreSQL, while MySQL (even in strict mode) will corrupt it to "0000-00-00" (v5 does throw an error, but it still enters the corrupt data).
2. Mature support for foreign keys. If you do not understand the concept, get the fuck away from a database.
3. Mature support for stored procedures.
4. Mature support for views.
5. Mature support for triggers.

Re:I use PostgreSQL

[jellomizer]

Mature Stored Procedures and views are the most important for my line of work. (Unfortunatly I use these features mostly in Microsoft SQL, as per-Client request) I follow (and I know others don't nessarly agree with me on this, and they have good points too) the concept that the Database server should handle as much data logic as possible. Using Views and Stored Procedures are realy very useful.

1. It reduces the chances of SQL injections hacks. By Passing information as parameters and not using an othe languge to build.
So. (*This is a bad Table Design but used to proove my point)

Code Snippit from a stored procedure

set @x='Data'', Password=''MyPassword'
    update Table set Message=Message+@x where UserName='administrator'

vs. Say in Psuto code

$x='Data'', Password=''MyPassword';
    $query = 'Update Table set Message=Message+'''.$x.''' where UserName='admistrator'

2. Network Bandwith. It normally takes less network bandwith to send a preformatted table with just the information. Then a bunch of commands and bunch of tables to make the final choice.

3. Deployment. After you checked out the code works on your test database. Just replace the stored procedure with the new one and it is off and running on all the apps that use it without any down time.

4. Other apps that need the same data you can recall the stored procedure vs. Coping the code from every app. (Assuming each app is in a different language and you cant make a libaray)

There are arguments about this stuff bogging down the Database server. Which is true because the database server is doing more. But today the cost of getting a server that runs twice as fast is usually cheaper then paying a programer to optimize the program to run 20% faster.

I cannot believe...

[Anonymous Coward]

I can't believe they even let this post survive. It's old, and it simply isn't fair for either platform's updates and new features. Just delete this crap...

Doesn't mean a thing and this is why ......

[Stumbles]

February 15, 2005

What about clustering?

[cecchet]

Clustering and High-Availability aspects are not mentioned at all.

MySQL speed will really depend on the database engine you use (MyISAM or InnoDB do not perform the same!). PostgreSQL performance is pretty much consistent across platforms.

On the HA side, PostgreSQL has maybe less options: Slony/I (http://gborg.postgresql.org/project/slony1/) for master/slave or Sequoia (http://sequoia.continuent.org/) for multi-master.
MySQL offers MySQL replication (http://dev.mysql.com/doc/refman/5.0/en/replicatio n.html) for master/slave, MySQL cluster (http://dev.mysql.com/doc/refman/5.0/en/mysql-clus ter.html) for those who want to switch to a new storage engine (NDB) or Sequoia (URL:http://sequoia.continuent.org/) for multi-master with transparent failover.

Interested to death

[tttonyyy]

I know slashdot is for nerds (and I happen to use mysql databases myself), but honestly - an old article comparing databases? Must... keep... eyelids.. open...

Re: PostgreSQL is easy too

[ajaf]

You don't need to setup anything to run it for the first time, only if you want to play with performance, you can start to modify parameters as memory, max connections, etc. PostgreSQL is easy and powerful, just give it a try.

Crap!

[CaptainZapp]

MySQL runs as a native Windows application (a service on NT/Win2000/WinXP), while PostgreSQL is run under the cygwin emulation.

I call pure, unadulterated crap on this one.

One of the major new features in Postgresql 8 was native Windows support. It runs just fine as a service.

This comparision is either very old news, incompetence in action, or, um! strongly biased.

Re:Crap!

[AutopsyReport]

It is too bad PostgreSQL didn't have Windows support in prior versions. My company (and the clients we contract for) develops applications in the Windows environment and moves them to Production usually running *nix. The fact that PostgreSQL didn't have native Windows support several years ago led to the decision of choosing MySQL as the database engine for a major application. I would have loved to use PostgreSQL, but the lack of Windows support made it an easy decision to pick MySQL. It is good to see that PG has come around, but I can definetely understand why MySQL is a favourite for simplicity (installation and support of environments).

Re:Crap!

[CaptainZapp]

It is too bad PostgreSQL didn't have Windows support in prior versions.

Alas it's understandable looking at the history and roots of Postgresql that it lacked native Windows support until version 8 I agree with your sentiments.

I'm definitely not a Windows fanboi, but I agree that implementing native support for projects like Postgresql or Apache (which has it since version 2) is paramount if you want to see your product succeed.

Just not providing a version for the widest available platform because uhh, you know it's Microsoft and uhh therefore it sucks! is immature and stupid.

I like the fact, though, that at least with some software Windows is not the primary platform :)

LAMP

[Anonymous Coward]

Out of LAMP, the only component that doesn't suck goats is Apache. Linux is a total mess (2.6 is unstable to the point of being useless), MySQL can barely be called a database (absolutely key functionality missing, error reporting extremely weak) and PHP is a security joke (unless you add in Suhosin).

The low entry bar to PHP coding and the fact that most of the bargain bin webhosts offer a poorly configured MySQL install as part of a $1/mnth plan only serves to perpetuate this horrid combination of software. What is worse is the way in which it has seeped into corporate applications. I hate you all.

Re:LAMP

[Anonymous Coward]

Actually, the "P" was originally Perl, although PHP has now come along and tried to retconn themselves in. At least some places have the decency to at least say "Perl, PHP, or Python". Too bad Ruby wasn't named "Pruby"...

Why we moved from MySQL to PG

[punker]

This almost seems like the same comparisons we've been hearing for years.
1) Postgresql is more full featured than MySQL
2) MySQL is faster in a read-mostly environment
That's pretty much the same as the anecdotal arguments have been for years.

          In my job, we moved from mysql to postgres several years ago (around PG 7.0). At the time, we needed to make the move for performance reasons. We are in a read-write system, and MySQL's locking was killing us (this was before InnoDB was well established). The features are better too, as our developers were used to having data integrity features, server side programming, and all of the SQL92 constructs available. We also learned a bit about PG performance, which I'll share.

1) Run EXPLAIN ANALYZE on everything. Postgresql is touchier about query performance than MySQL was. This just needs to be a habit if you're using PG. (You really should do performance analysis no matter your DB. It's just a good practice). The biggest gain will be making sure you're using index scans rather than sequential scans.

2) Use persistent connections. Everyone likes to point out the forking issue with PG vs. MySQL's threaded. PG's connection handling is slow, there's no doubt about it. But there's an easy answer. Just limit how often you connect. If you can keep a connection pool, and just reuse those connections, you'll save this big hit.

3) Full vacuum and reindex regularly. We've found the docs to be a bit off on this. It indicates that you should run these occasionally. If you're in a read-write system, a full vacuum on a regular basis is very important. It really doesn't take that long if you do it regularly. Also, we've had trouble with indexes getting unbalanced (we see 50->90% tuple turnover daily). This has gotten better, but it doesn't hurt to let your maintenance scripts make things ideal for you. So, we run a full vacuum and reindex of our tables nightly through cron.

4) Get your shared memory right. PG's shared buffers is probably the most important config attribute. It controls how much of your DB is memory resident vs disk resident. Avoiding disk hits is a big deal for any DB, so get this right. If you can fit your whole DB in memory, then do it. If not, make sure your primary tables will fit. The more you use the shared memory, and the less you have to page data in/out, the better your overall performance will be.

Most DB systems seem to be read-mostly, so I can understand the performance comparisons focusing on that. In our read-write system though, the locking was the biggest issue and it tilted the performance comparison toward PG.

Re:Why we moved from MySQL to PG

[giuntag]

Dude, I thought this was a guide on optimizing ORACLE performance!
Its sums up neatly all the advice I keep giving to all the noob contractors that develop on sqlserver and then kinda start having nightmares when they are told we will install on oracle in production (and their app gets suddenly 5x slower...)

Re:Why we moved from MySQL to PG

[tcopeland]

> So, we run a full vacuum and reindex
> of our tables nightly through cron.

I've found that just enabling autovacuum seems to keep things in order. And you can tweak it for individual tables if you're so inclined.

Re:Why we moved from MySQL to PG

[lysdexia]

2) Use persistent connections. Everyone likes to point out the forking issue with PG vs. MySQL's threaded. PG's connection handling is slow, there's no doubt about it. But there's an easy answer. Just limit how often you connect. If you can keep a connection pool, and just reuse those connections, you'll save this big hit. True that. On an earlier project I ended up going with the psycopg module for python which allows you to keep a number of connections open. It worked like a charm.

Of course, MySQL is effectively two products...

[itsdapead]

MySQL/MyISAM is the one with the massive legacy code base, the one that your open-source blogging software uses and probably the one that your web host supports. It beautifully hits the "sweet spot" for data-driven web sites with infrequent and simple updates, where trading integrity for "read only" performance is sensible. It does not even purport to compete with PostgreSQL on features - but it does offer fulltext searches, again

MySQL/InnoDB is the one that offers transactions, foreign keys etc. (ISTR it doesn't do fulltext indexes, though) - this is the "version" that bears comparison with PostgreSQL. I wonder how its user base compares?

(OK - you can mix InnoDB and MyISAM tables in a single database, but you can't use InnoDB if your web host hasn't installed it - heck, one provider I use is still on MySQL V3.23)

Flamewars have tended to pit PostgreSQL against a mythical database with the performance of MyISAM and the features of InnoDB...

As for the GUI software, the MySQL GUI Admin/query browser stuff is shinier than PgAdmin3 - but the MacOS version of the former is a complete crashfest! Neither of them steps up to the plate of providing a FOSS equivalent of (the good bits) of MS Access.

Re:Of course, MySQL is effectively two products...

[LizardKing]

As for the GUI software, the MySQL GUI Admin/query browser stuff is shinier than PgAdmin3 - but the MacOS version of the former is a complete crashfest! Neither of them steps up to the plate of providing a FOSS equivalent of (the good bits) of MS Access.

If you're talking about the MySQL GUI clients that are written in C++, then I strongly suggest you take a look at the source code. About eighteen months ago I tried to port the new clients to NetBSD as the older client wouldn't work with MySQL 4.1 (MySQL AB seem to rewrite their GUI clients from scratch every couple of years). Never have I seen such poor C++ coding in a fairly high profile project. I gave up trying to port them in the end (too many Linuxisms) and wrote a simple client in Java.

Re:Of course, MySQL is effectively two products...

[kpharmer]

Maybe there's four products? If you also include:
    - MySQL Cluster - a cluster (like Oracle RAC). Very fast, very cool. Except...all the data is kept in memory - so it is extremely limited in application. Also a separate codebase - so don't expect your applications to easily move to this platform. Also as far as speed goes, you can probably achieve most of the same results by just putting 12-24 gbytes or whatever of memory on your non-clustered database - and making sure the database uses it.
    - Non-Free - the MySQL license is very complex. Many fans celebrate how the vendor is vibrant and healthy due to its revenue stream, but that the product is free. In reality the product is only free in very limited circumstances - and I often see it being used in situations in which the vendor should be paid. Of course, you need to consult with an appropriate lawyer if you really want to know whether or not you need to pay...

Your US at Work

[Doc Ruby]

" let your tax dollars do the work in the form of Fermi National Accelerator Laboratory"

I don''t begrudge the world the science (and other investigations) that Americans pay for. But that summary should read "let US tax dollars do the work".

I wonder how many of Slashdot's foreign readers who usually rail against US-centric language in posts here will complain about how they get a free ride on this research.

15 months old...

[Smoking]

Seeing the current rate of development of both MySQL and PostgreSQL
this can only be a bad joke...

Ok, here is another outdated test

[xyvimur]

Ok, this is yet another outdated report comparing three mainstream RDBMS'es - MySQL, PostgreSQL and ORACLE. It was done for yet another physical experiment - for choosing the proper system for storing data about the construction process of one of the LHC detectors - ALICE.
And this report is at least professional, which cannot be said about the one mentioned in the article.
http://dcdbappl1.cern.ch:8080/dcdb/archive/ttraczy k/db_compare/db_compare.html

Re:Ok, here is another outdated test

[Serveert]

Although that looks pretty and professional, content-wise, it's also lacking:

Oracle8: Recovery from soft failures is automatic and transparent. Recovery from media failures requires backup copy. With redo logs it is possible to recover all commited transactions or to recover to specific time point.
Postgres: No. There is no transaction log, so the only recovery method is to restore database from backup.

Postgres can to point in time recovery and it keeps a transaction log that you can apply.

then this tidbit about replication

Postgres: No.

Someone should have given me that memo, I use slony in production to replicate postgres. Oh it's an 'add on' so it doesn't count. I'm sure that logic does well at corporate meetings, which is why I no longer do those and have moved on. :)

Re:Ok, here is another outdated test

[xyvimur]

Although that looks pretty and professional, content-wise, it's also lacking

I doubt that there will ever be a complete, generic and non-biased comparison of RDBMSes. In some areas it is obvious that one should use big commercial system, in some SQLite is sufficient, while in most of the situations some research is needed.

more recent benchmarks

[darekana]

http://tweakers.net/reviews/657

They compare PostgreSQL 8.2 vs MySQL 4.1.20 and MySQL 5.1.20a.

Re:more recent benchmarks

[tcopeland]

> They compare PostgreSQL 8.2 vs MySQL 4.1.20 and MySQL 5.1.20a.

Mod parent up and all that. We're using PostgreSQL 8.2 for a small (18 million records) database and are pleased as punch with it. And here, too.

Re:more recent benchmarks

[lysdexia]

Apparently PostGreSQL enjoys an extra processor!

Of course, the article is mainly a review of a couple of dual Intel Xeon 5160 systems, so I'm unsure what the pretty graphs mean. :-)

Re:more recent benchmarks

[daBass]

That is a great review; an actual real-world scenario buy guys who seem to know their database, not some spotty teenager who thinks a database benchmark is seeing how fast you can load 1 million records and then see how long a "select *" on that table takes...

As the article shows, every time they double the number of cores, Postgres gains 75% in performance - like any good application should do. At 4 cores, it is already twice as fast as MySQL under reasonable concurrency; I'd like to see this test on a 8-core server - my guess is MySQL wouldn't be much faster than it is now and Postgres would perform at least 3 times better than MySQL.

Oh, and Postgres doesn't think 0000-00-00 is a valid date, which is nice too.

Re:more recent benchmarks

[Anonymous Coward]

Yep.

Some shortcuts to graphs:

Performance vs. concurrency
Performance vs. CPU count

Apparently MySQL clustering doesn't help so much either.

I can't help thinking that MySQL is popular for much the same reasons that Windows or Visual Basic are popular: simplicity. However, solid scaleable applications require that you to take off the training wheels.

Re:more recent benchmarks

[RAMMS EIN]

``I can't help thinking that MySQL is popular for much the same reasons that Windows or Visual Basic are popular: simplicity.''

Except that Windows and Visual Basic aren't simple. My explanation: hype. MySQL got pushed with the whole wonder of Linux and open source. There's even a word, LAMP, which means Linux, Apache, MySQL, PHP (or Perl, or Python, sometimes). PostgreSQL just isn't as widely known as MySQL, and, as we say in the Netherlands, unknown makes unloved.

Also, of course, there is lock-in. MySQL has historically lacked several important features, offered some non-standard ones (AUTO_INCREMENT), and this caused lots of code to be written in a way that works on MySQL, but not on other databases. Of course, you don't want to move your running, tested application to be moved to an incompatible database...

Re:more recent benchmarks

[Anonymous Coward]

A more recent review of the quad core xeon: http://tweakers.net/reviews/661
Linux 2.6.18 vs 2.6.15: http://tweakers.net/reviews/657/2

Re:more recent benchmarks

[Ben Hutchings]

Nah, LAMP is "Linux, Apache, Most of our scripting languages start with P, and PostgreSQL" (according to Jeff Waugh).

Or are they bots?

[Frosty Piss]

More nice "editing" from Slashdot "editors". Or are they bots?

a more up-to-date comparison

[brentlaminack]

I did a presentation at the Atlanta Unix Users' Group this month that is a more up-to-date comparison. It's available in Open Office format. You can also get to it from my home page. I did a similar talk almost four years ago. My conclusion is that MySQL has closed the feature gap with PostgreSQL in recent years. I still give PostgreSQL the edge in features, and MySQL the edge in out-of-the-box untuned performance. I also discuss replication and clustering.

Oracle vd PgSQL

[muyuubyou]

Why MySQL? it's not in the same league at all. If you are in the market for Oracle, you definitely are not in the market for mySQL.

Oracle vs PostgreSQL would make a lot more sense. 8 series of course.

MySQL replication/clustering needs work too...

[jonathan_lampe]

I've worked with MySQL replication for about three years and I'd have to say that feature isn't quite done. Some of the most annoying features:
    - Manual cleanup of binary change files is sometimes required.
    - Databases in "slave" mode can still be updated by local applications; you can't configure them to only log changes from the "master" database.
    - Automated master-slave role-switching requires you to write some code.
    - The whole "master/slave" terminology. I know it's meant as just computer-speak and the MySQL team is European, but I work in America; it can be offensive in some situations.

MySQL clustering is also only available for a few of the OSs regular MySQL supports; it's not a universal option for all platforms.

Re:MySQL replication/clustering needs work too...

[ThJ]

I never thought of master/slave in that way before. Probably because I'm Norwegian. We hardly know anything about American history. English is a school/TV/computer language here.

Re:MySQL replication/clustering needs work too...

[raju1kabir]

I never thought of master/slave in that way before. Probably because I'm Norwegian. We hardly know anything about American history. English is a school/TV/computer language here.

Don't worry, you can safely jonathan_lampe's childish offense at the "master" and "slave" terminology. It's in common use in the United States in a wide variety of fields and is not considered offensive by anyone other than off-the-scale radical nutcases, whose own existence is far more offensive than any use of the words in question.

Perhaps we can encourage jonathan_lampe to first worry about "male" and "female" connectors, "white" and "black" hat hackers, "lame" duck presidents, and flame "retard"ants. All of the parties who are no doubt grievously wronged by these terms (females, blacks, and the physically and mentally impaired) are still around in large numbers, whereas slavery was abolished almost 150 years ago.

Re:MySQL replication/clustering needs work too...

[Anonymous Coward]

FYI: negro slaves are incapable of independent thought and therefore need a "master" to tell them what to do.

Re:MySQL replication/clustering needs work too...

[victoria_r]

> Databases in "slave" mode can still be updated by local applications; you can't configure them to only log changes from the "master" database.

No, if read_only mode is ON only updates from replication thread and users with SUPER privilege are accepted.

Use "than," not "then," in the blurb at the top!

[Anonymous Coward]

I hate it when people misspell/misuse these words.

Old news

[Anonymous Coward]

Old article and old information. It's almost 2007. That "head-to-head" was done in early 2005. Pre-MySQL5.

What about 386 dx vs 486 sx?

[Anonymous Coward]

Thats the real comparison. I had a 386 dx at 33 mhz where as my unenlightened friend had a 486 sx at 25 mhz. On win 3.11 most benchmarks ( that I wrote) preferred the higher mhz of the 386. While were at it we should also compare the 486 dx at 75 mHZ vs a pentium I at 66 mhz.

Here's a recent (Nov. 2006) Performance Review

[drake]

Here's a comprehensive performance review between PostgreSQL and MySQL. It compares both DB's under load as well as comparing Intel/AMD chips. http://tweakers.net/reviews/657/6

License anyone ?

[Anonymous Coward]

MySQL is not free for commercial use. This is often forgotten/unknown (I think many use it commercially without paying really because they don't know).

Also is quite a difference to take into account.

This story comes from

[aleKsei]

This story comes from the it's-been-slow-at-slashdot-lately-lets-stir-things -up-a-bit Dept.

Postgres For Larger Datasets

[Hornsby]

I had to make a decision recently between Mysql and Postgresql for a database composed of many tables with greater than 50,000,000 rows. While going through the decision making process, I loaded a sample table with 50,000,000 rows to do some benchmarks. The first thing I had to do to run my tests was index the table. I started with Mysql using a InnoDB table type. I had both database servers relatively tuned to the hardware they were running on. I ran the create index with MySQL and detached my screen session. I came back several hours later to find MySQL was doing something along the lines of INDEX via REPAIR SORT. After some reading, I learned that this takes an order of magnitude longer than building an index the "normal" way and is caused by the index becoming corrupted during the creation. Okay... so, I restarted this process several times and encountered the same problem. This is clean data mind you that has already been exported from an existing SQL server. I duplicated my install on a second server and had the same problems. Very annoyed with MySQL, I gave Postgres a try. It worked on the first time in less than 25 minutes without issue. Since then I've been using it on 250,000,000 row datasets without issue. It's always reliable, and as long as you remember to use CURSORS for huge SELECT statement, it's painless to work with.

Disapponting start

[smchris]

The first screen that it says MySQL supports ODBC and doesn't mention that PostgreSQL does as well -- so why should I read further? Either sloppy, ignorant, or biased writing.

There were a couple comparisons a couple years ago. It was my understanding that PostgreSQL did better with large data sets in a P vs. M match. In getting hammered with connections, another test between MySQL, PostgreSQL, DB2, Oracle, and SQLServer, if I remember, Microsoft's offering started to crap out along a power curve at maybe just 200(?) hits and the others degraded pretty equally along a straight line.

My client/server experience started with some Oracle classes and managing a department server. I must say I am _much_ more comfortable with PostgreSQL and find MySQL a little alien no matter how popular it is. Just my 2 cents.

Whoa... this was actually helpful!

[Ingolfke]

Wow... it's been months since I found anything really useful on /. The article was good, if out of date, because it gave categories of comparison. The comments for the most part were also very helpful because they point out what has changed and bring a bit more real world experience to the debate.

Thanks for the early Christmas present :)

Re:Whoa... this was actually helpful!

[bgalbrecht]

How can you find a trivial, severely out of date comparison useful?

Re:Whoa... this was actually helpful!

[Ingolfke]

RTFP idiot. The categories of comparison were useful and the comments on /. were helpful in providing updated information.

Re:Whoa... this was actually helpful!

[Hornsby]

This was a comparison I conducted on my own using the latest stable releases of MySQL and PostgreSQL. I posted them to refute the article, which IMO was very flawed.

Re:Whoa... this was actually helpful!

[bgalbrecht]

The original web page was superficial, with no supporting documentation, and the commentary is not really any different from any other recent mysql vs. postgresql flamefest (er, discussion) on slashdot. About the only useful thing in the whole thread are the couple of links to recent comparisons, and since I haven't actually checked any of them out, I don't know if they're as poorly researched as the original article. YMMV.

Great GUI Admin Tool

[LittleBigPile]

There is a very good GUI-based admin tool for both PostgreSQL and, recently, MySQL. Its PG Lightning Admin. Unfortunately, it only runs on Windows. But the price can't be beat and is well worth it - especially now that they are running a Holiday Sale.

Unbiased?

[evrybodygonsurfin]

From the comparison table:

• Postgres: Lacks binary distribution for all the supported plataforms.[sic]
• MySQL: There are binary distribution for most of the supported plataforms.

These statements convey the same information but that the author has presented them in different lights suggests to me a premeditated bias in favour of MySQL.

Re:Unbiased?

[Anonymous Coward]

Follows standards, like UNIX follows POSIX.

Uhm...yeah.

http://www.postgresql.org/docs/8.0/static/sql-synt ax.html - see 4.1.2.2, 4.1.5, etc. Sure, they follow standards when they feel like it.

Also, let's not forget the infamous "case-sensitivity in our table names is not a bug, it's a feature!"

Well, if you can argue that, then "missing foreign key constraints is a feature, not a bug!"

Re:Unbiased?

[00lmz]

Those are additions to the standard (extra features for those who need them). Dollar quoting is useful because PostgreSQL treats stored procedure code as strings. Without dollar quoting, you would have to play delimiter games or escape every single quote inside the stored procedure to create stored procedures. And about the C comments, doesn't MySQL have those too?

About case-sensitivity in table names... I thought that was only about the case folding? (PostgreSQL stores unquoted table names in lowercase, the SQL standard specifies uppercase), see 4.1.1

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)

From the MySQL 5.0 manual:

InnoDB tables support checking of foreign key constraints. See Section 14.2, "The InnoDB Storage Engine". Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. For the precise syntax, see Section 14.2.6.4, "FOREIGN KEY Constraints".

For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.9.5.5, "Foreign Keys".

Oh look, ignoring constraints without a warning! You said:

Well, if you can argue that, then "missing foreign key constraints is a feature, not a bug!"

The bad things about MySQL (to me at least) aren't about the nonportable extra features (they can be convenient, but makes it harder to switch databases), but about the features that it lacks (like, you know, proper support for constraints...)

Re:Unbiased?

[treeves]

Do you mean that: "Lacks binary distribution for all" = "There are binary distribution for most"?

If "Lacks binary distribution for all" = "There are binary distribution for none"

Do you still think they're the same?

The statement regarding PostgreSQL should be written more clearly. Given the choice between assuming incompetence (writing an easily misinterpreted sentence) and malice (bias), one should usually charitably assume incompetence.

Wow is that inaccurate...

[Anonymous Coward]

From TFA: "To choose between the two databases, you need to understand whether you need the transaction
support of Postgres or the large-text-area support in MySQL.", HUH? Postgresql text can support up over 1G of text per field. Also the article says that BLOBS are "special" in PG and MySQL they are "are just fields in the table", again in accurate at best. PG does have the concept of BLOBS and they are special, but this is really just legacy, most people use bytea which is a binary data type that "are just fields in the table" like in MySQL, this isn't new, it's been around a long time.

PostgreSQL vs. Oracle?

[Bilbo]

OK, I don't think there is much debate that Oracle is a "better" database than PostgreSQL when it comes to really BIG databases, where you have to regularly deal with multi-million line tables, or huge numbers of transactions. I'm no database guru, but I've used PostgreSQL a lot, and my experience is that it really starts to bog down on large datasets, where Oracle just sneezes at them.

However, for many (most?) cases, Oracle is also HUGE OVERKILL!

Does anyone know of a reasonably un-emotional, but still somewhat accurate comparison between PostgreSQL and Oracle? When is it appropriate to use one vs. the other?

Re:PostgreSQL vs. Oracle?

[Angvaw]

I've used Oracle for about 3 years and Postgres for about 1. Both are good databases. I may write what you're asking for eventually, but don't hold your breath. :) Here are a few thoughts off the top of my head that might help.

In a way-oversimplified nutshell, Postgres seems sorta-kinda modelled after Oracle. But many times I look into a feature that I've used on Oracle and find it doesn't exist in Postgres or it's not up to my expectations. The Postgres feature might be a little half-assed. One example is the multi-version concurrency control. If you update a row in Oracle, the undo tablespace contains a snapshot of the old version. All other concurrent users are directed to the undo tablespace until the transaction completes. They get the old version of the uncommitted row from the undo tablespace. Postgres on the other hand makes a new row in the table for your update, and the old one is marked as a "dead tuple" whose data can be retrieved by other transactions.

Might not seem like a major difference, but you'll have to "vacuum" your Postgres database periodically to clean up your dead tuples. Otherwise, table scans have to scan over all the dead tuples in addition to the "real" data. Some DBAs simply run a vacuum job every night, or use the auto-vacuum daemon. It's not always so simple however. Imagine you have to update data every morning and get it done ASAP. Well, now that you've updated your 500,000 rows, you have to vacuum the table to keep queries running at a decent speed. When I was still a Postgres newbie, I would just vacuum whenever I remembered. One time I forgot about it for a few days, when I finally ran the vacuum it took 17 hours to complete - granted it was a one disk server. It's just another thing to worry about.

Once I looked for a replication solution for Postgres. Some were incomplete. Slony-I - the most popular Postgres replication solution - seemed like the way to go. But upon delving into it, I found it imposed limitations because it sort of "corrupts" the Postgres data dictionary. I believe this corruption broke triggers on all replicated tables (don't quote me on that). Also Slony-I does not support multi-master replication at all.

Sometimes a feature is just great. Rarely is the Postgres feature better, except in terms of user-friendliness. For instance the psql command line tool blows sqlplus out of the water IMO. (Wow, paginated results, tab completion!)

Oracle has a wealth of OLAP/business intelligence features that are lacking in Postgres. There is a flavor of Postgres called Bizgres that is a bit better in this area, though still lacking in comparison. Bizgres is more cutting edge and doesn't seem to have nearly as strong a community as Postgres. Go to http://www.bizgres.com/bb/ and you'll see there are very few posts.

Oracle has an expert named Tom - asktom.oracle.com - who is a genius and answers all my questions. Postgres has an expert, also named Tom, who is a genius and answers all my questions on the newsgroup.

When would I use one over the other? Personally I would use Oracle if there were no budgetary constraints, but if Postgres definitely met all my requirements, I'd go with that. (Yes yes, I am Captain Obvious.) If you need Oracle's enterprise-level features then Postgres is out of the question. There is however EnterpriseDB, a commercial, Postgres-based enterprise-level database. Several months ago Sony "snubbed" Oracle and went with EnterpriseDB for its MMORPGs. I have not looked into EnterpriseDB myself so I can't comment further.

Well I'm rambling and not getting work done. Hope that helps just a little bit.

Re:PostgreSQL vs. Oracle?

[nuzak]

I don't get how Oracle gets around the need for vacuuming with the "undo" tablespace. It still has to keep multiple row versions, and it still has to update the versions of affected rows once the transaction completes. Does it immediately evict the old versions to the "undo" space during a transaction and direct other clients to it? That would seem to be a high up-front cost that could be batched more efficiently with a vacuum.

I'm not disputing that Oracle cleans up stale rows faster than Postgres ... I'm just curious how it actually works behind the scenes as opposed to the interface it presents to the clients.

The Sony story is really quite interesting -- I had no idea that there were major MMORPG's running on postgres! (though a highly modified version no doubt)

Re:PostgreSQL vs. Oracle?

[Angvaw]

"Does it immediately evict the old versions to the "undo" space during a transaction and direct other clients to it?"

The data is updated in place, but a the old row is copied to the undo tablespace. There can be quite a lot of I/O against the undo tablespace. It should be on a fast RAID, or perhaps a separate disk. According to that Tom expert I mentioned, a typical configuration is to put it on a RAID 0+1 ("rollback" is kind of the old name):

o raid 0+1 for rollback. It get written to lots. It is important to have protected. We cannot multiplex them so let the OS do it. Use this for datafiles you believe will be HEAVILY written. Bear in mind, we buffer writes to datafiles, they happen in the background so the poor write performance of raid 5 is usually OK except for the heavily written files (such as rollback).

source

It is true that something like vacuuming needs to be done under the hood - really it's just that the undo data needs to be purged so the undo tablespace does not grow indefinitely. I don't believe old undo data gets much "in the way" because it is not scanned like a table. (I'd have to bring out the docs to give you more detail.) You can set an UNDO_RETENTION parameter which is the number of seconds to retain the undo info for each transaction. You may want to keep this data around for a long time, even after your transaction is finished, to allow for flashback querying (basically "select * from mytable as of 2 days ago" or "flashback table mytable to last week").

What's most important IMO is that updates don't create clutter in the table itself. In Oracle, if you update 1000 rows, all other users are directed to undo if they query the same 1000 rows - for the length of your transaction only. Keep in mind that the undo data will likely be found in memory, in the buffer cache. It needs to be on disk too, though, in case of an instance failure - when you start up the database instance it will rollback all in-progress transactions.

In Postgres, if you update those 1000 rows all other users are directed to the dead tuples - again these blocks are likely in the buffer cache. I don't believe there is any performance difference here. BUT, when your transaction finishes and the dead tuples are still in the way, that's when the Postgres way becomes a problem. Now imagine 1000 rows are updated by a different user every few minutes. It piles up. A regular VACUUM will reclaim the space, but your table may still be fragmented. There is another command, VACUUM FULL, which will compact the table to a minimum number of blocks, however this is a much slower operation and requires an exclusive lock on the table, i.e. you don't want to VACUUM FULL during active hours.

"The Sony story is really quite interesting -- I had no idea that there were major MMORPG's running on postgres! (though a highly modified version no doubt):"

Agreed - we're talking Everquest II and Star Wars Galaxies! I've been meaning to check out EnterpriseDB but haven't had the time...

Re:PostgreSQL vs. Oracle?

[Bilbo]

Re: the "dead tuples" and the need to vacuum, this is exactly what I'm seeing in my application performance tests! The database gets slower and slower, until I run a vacuum, at which point it suddenly speeds up again (sometimes by a factor of 10 or more). Granted, I'm pounding the database just as hard as I can, so the loads are unrealistic, but it still points to a (in my case) significant bottleneck.

Thanks!

MySQL short on features

[Frater 219]

Does the Internet's favorite DBMS have an IP address datatype yet?

How about MAC address? CIDR block?

"An IP address is just a 32-bit unsigned int, duh. Any DBMS can store those."

Wrong. A datatype isn't just about storage, but also about operations. In PostgreSQL, when you do a SELECT across a table with IP addresses in it, you get them formatted and displayed as IP addresses, not as opaque ints. Likewise with CIDR blocks, like "192.168.42.0/23". There's also a comparison operator for asking whether an IP address is within a CIDR block.

If you're implementing a network registration system or an incident logging system, how much of your time do you want to waste staring at opaque ints like 3232246272 rather than IP addresses like 192.168.42.0 when you're trying to debug it?

MySQL is a bimbo, a fratboy: it's easy, but so shallow! The amount of time you save in one-time setup, you will lose many times over in all the little annoyances and deficiencies of a DBMS that was originally designed by folks who didn't really believe in DBMSes. Over time they've slowly been shamed into including many of the features they used to despise: transactions, relational integrity checks, and so on. But there's still so much missing ... not just essential integrity features, but little fiddly bits like good datatype support, the kinds of things that make your life easier (as a programmer or as a DBA) in the long run.

Re:MySQL short on features

[poot_rootbeer]

In PostgreSQL, when you do a SELECT across a table with IP addresses in it, you get them formatted and displayed as IP addresses

Formatting should be a task of the presentation tier, not the RDBMS.

That doesn't negate your point that having a native IP address datatype make actual application logic (like testing whether an address falls within a given range) much cleaner and easier to implement. The more native datatypes a database supports, the better; if I want to work with a boolean value, it's far better to have a bool datatype than to "fake" it with a char or int.

Re:MySQL short on features

[Ant P.]

http://dev.mysql.com/doc/refman/5.0/en/miscellaneo us-functions.html INET_ATON/NTOA convert between ints and IPv4 strings. You can use it in a view if you're really that lazy, and I guess you are since you didn't do your fact-checking to begin with. As for CIDR blocks, they're just a bitmask on a u32 int. You do at least know what a bitmask is, don't you...?

Re:MySQL short on features

[counterplex]

I've always thought the IP data types were a bit frivolous but the one thing that always gets me is that in MySQL you cannot have a table with two timestamp fields with both default to now() (or equivalent). Oh and timestamptz from PostgreSQL ... life saver. Oh and an example of a table w/ two timestamps that need a value of now(): I usually have two fields labelled creation and last_update on tables that require them. Aids in debugging and can be useful info to provide to users. I typically have them both set to now() and use a trigger to alter last_udpate just before updates. Doesn't work in MySQL though. Don't even get me started on the lack of timestamptz.

Re:MySQL short on features

[WuphonsReach]

Interesting... I hadn't realized there were such things.

One question. How does the PostgreSQL IP4 (and other) data types map to more traditional types in more limited databases? What does the field type appear as if I attach via ODBC from MS Access?

"Free" Oracle

[Bilbo]

> TFA also fails to mention that mysql cannot be used in commercial development without paying $200 per client - which makes it more expensive than most other solutions (except maybe oracle, and even they have cheap licenses for some uses).

Oracle has an "eXpress Edition", which they say is free to use, even in commercial deployments. (Make sure you read the license yourself, as I'm not even close to being a lawyer!) I've used it for some development work, and it seems to work nicely. It's a bit crippled when it comes to the usual fine-tuning options, but most normal Oracle tools will talk to it. The one gotcha that I overlooked at first is that it has a cap on overall user data size of something like 4 or 5Gig, so you have to think about how much data you're going to be handling. (In other words, it's not something to replace a full-blown enterprise Oracle installation, but it's great for smaller applications.)

So, in this case, it is possible that MySQL would end up being more expensive than Oracle!

Oh, and of course, it's all proprietary software, so if you want a full FOSS stack, you're not going to find it there.

Re:"Free" Oracle

[Ryan Amos]

4 gb is not a very large database at all. :) It's pretty much for small shop developers to try Oracle before considering dropping six to seven figures on it. Many younger developers don't see that mySQL and Postgres just don't have the features of a mature commercial product like Oracle, and this seems like a way to introduce them in a manner similar to mySQL and Postgres without sacrificing Oracle's bread and butter of million dollar database licenses.

For the record, IBM has a free version of DB2 with similar limitations (I don't believe there's a cap on data size, but it rather caps it at 1 CPU and 2 GB RAM or something similar.)

IIRC...

[Junta]

I think the pentium 60 mhz was considered about the same as the 100 mhz 486s that came out at the end of the 486 days.. except for dividing floating point numbers of course..

I only recently decommisioned a P60 box that was router in favor of a much more efficient WRT box. Too bad that 286 in the closet I can't possibly conceive a use for anymore...

Awww, yeah, back in the day

[Kozz]

I remember the labs on campus, and I did my best to get seated in front of one of the very few 486 DX/4 100's. Those machines were rockets compared to the others. Little did anyone else know. Even when the lab got some P60, I think I preferred the 486 DX/4 100.

Lame-ass editing allowed this...

[BarnabyWilde]

...antique article to appear.

Um, biased

[Jack9]

I am an admitted MySQL fan, however this "unbiased review" is dripping with subjective statements favoring MySQL.
When you have PostgreSQL criticisms IN MySQL feature sets, it's clearly biased.

I'd like to see...

[spurioustruth]

How about an article on that neat analysis framework Fermi (and others) use for the terabytes of data they generate and have to sift through?

http://root.cern.ch/ (large scale repository for data analysis)

And how would you use Postgres for something like that anyway? Maybe something like:

http://www.greenplum.com/ (biggy-sized Postgres based data warehousing)

Unbiased?

[Anonymous Coward]

Hey, MySQL fanboy. You don't know what you are talking about.

MySQL doesn't have the same capabilities and flexibilities of stored procedures and triggers as PostgreSQL has. But then again, MySQL only added it recently.

PostgreSQL is a better database in the classical sense of a database. MySQL has better support for cross-database (same server) selects, inserts, etc.. Better, more fine grained permissions.

If one were to compare databases to OSes, PostgreSQL is the UNIX of the databases. Follows standards, like UNIX follows POSIX. MySQL is like Windows of the databases. It doesn't follow standards. Features are added as "one sees fit". MySQL is a more adhoc database.

Which one is better? Use the one that fits your workload!! The comparisons are as useless as comparing if Windows is better than UNIX.

The only comparison is that PostgreSQL is BSD license so you can use it in a commercial app. MySQL has a GPL license so you CANNOT use it without paying MySQL tax for development of your apps (non-GPL apps)!! This makes MySQL more expensive to develop for than Oracle, MSSQL or DB2 all of which are free for development. Therefore, for a commercial app developer, the cost of supporting databases is,

PostgreSQL + Oracle + MSSQL + DB2 < MySQL

Sad but true. As for free usage, MySQL cost is the same (free) as the following databases,

PostgreSQL, Oracle Express, MSSQL Express, DB2 Express

The "Express" editions are limited to about a GB ram and 4 GB data, or something like that. So these will work just fine for websites and other small stuff.

Unbiased?

[General Lee's Peking]

Not only does the language seem to show a bias towards MySQL, there is no depth to this. For example, SQL isn't all that huge---they lack a listing of specific differences of SQL with the standard for each database system. If you're looking for something to help you decide which database you're going to use, this article is useless.

This Was a Hit Piece - Better Data Here...

[skeeterbug]

i was using pgsql 8.x back in 2005 - so it existed. they just cherry picked versions to get the result they wanted. postgresql.org linked to a new study where both current versions (at the time, and it was recent - pgsql is 8.2 now) of mysql and postgresql were put throught the paces... http://www.postgresql.org/about/news.691

Re:This Was a Hit Piece - Better Data Here...

[ocelotbob]

There are a lot of questions to be asked of the database, such as what the schema was, what the database type was, etc. Plus, they were using the old mysql set of commands in php 4, instead of the mysqli commandset in php 5, which would have also greatly increased their mysql performance. So that benchmark is just as invalid as the article's. I guess what it boils down to is YMMV.

Kebreros

[KidSock]

One of the biggest issues in my mind is MySQLs lack of Kerberos support. With PostgreSQL you can authenticate web clients using Kebreros, export the KRB5CCNAME environment variable, call pgsql_connect and voila it just works. Single Sign On (SSO) is a critical requirement on big IntrAnets now. MySQL will never be anything but the local Linux guy's pet project if it doesn't get around to supporting GSSAPI.

Re:Kebreros

[vidarh]

Except that most of the time there won't be a direct correspondence between accounts in your database system and accounts in your web applications. It's something I for one have never, ever needed or wanted for any of the systems I've worked on.

Re:Kebreros

[quantum bit]

If you have Win2000/XP clients with the ODBC driver, it's even possible to have it use the Kerberos ticket the user got at login to authenticate with, a la integrated authenication with MSSQL.

Great for migrating access DBs into a real database server when they hit the point where they really start suffering.

Re:Foreign Keys - Aw HELL

[thedave]

For those that feel a little like cursing, I did a quick search for heck, and replaced them with HELL

I particularly enjoy this for cHELL.

Well, I sure hope you never work on anything serious.

The database's function is to provide a RELIABLE storage for your data. Part of the whole reliability thing is making sure crap can't get in, because once it's there everything goes to HELL.

For instance, let's take a shopping cart. Can an order be for a negative quantity? If your app doesn't work that way (it could, using a negative amount for returns for example), and you still allow it in the DB, then all your reporting goes to HELL, as SELECT SUM... now returns the wrong thing.

A proper database is set up in such a way that every piece of data in it makese sense. This means for instance not having things like orders hanging around without in the void without being linked to some client. This is something easily ensured by foreign keys. Otherwise you have an utter mess - the total of the orders in the database doesn't match the sum of the orders of all clients!

If you put your cHELLs in the database, you have a guarantee that when somebody else codes another frontend to it (say, you had a website and now are making a special version for PDAs), if the application does the wrong thing, the database simply won't let it happen. This may cost a bit of speed, but I assure you that peace, your sanity and your ASS (if you have a boss and he's got any sense, he's not going to like it at ALL if it turns out that reports don't match reality, and that reality can't be even easily extracted) is far, far more valuable.

Hey! Is it feature chosing time yet?

[CaptainZapp]

I totally agree that Postgresql is very close to (if not very much in) the enterprise realm. May I suggest table partitioning on various levels? Oh yeah! Better replication would be nice too.

Else then that I have my dream come true enterprise database with the added bones of a size, which is 5% from a Sybase installation. Let alone Oracle :)

Re:Hey! Is it feature chosing time yet?

[Slashdot Parent]

In addition to the points you mentioned, Postgres will also have to support XA transactions if it is ever going to catch up to MySQL.

Oh well, maybe someday.

An unbiased report...

[mcguyver]

...this is not. A cursory read is all that's necessary to notice the author does not have enough postgresql experience to compare it to mysql.

Re:An unbiased report...

[Shados]

Does not have enough experience with generic RDBMS features, you mean. This whole thing reads like someone who's used to MySQL as a full featured standard database(plus it seems to be old, as this looks like its mostly comparing Postgres 7 and MySql 4).

Features like stored procedures were dumped in a generic category, when they make or break RDBMS single handedly depending on their features, in business environments. (I personally prefer dynamic sql, but seems like most architects do not, so well...).

I'm not particularly interested in either of these databases as they do not meet my requirements, but this comparison is totally rediculous.

I love this part...

[naChoZ]

MysSQL has a much larger user base than PostgreSQL, therefore the code is more tested and has historically been more stable than PostgreSQL and more used in production environments.

"Claiming that your RDBMS is the best in the world because more people use it is like saying McDonalds makes the best food in the world."

Sorry, just an old joke that deserved retreading... ;)

Old News? Ha, these are current packages

[HighOrbit]

In Debian, they call this "stable". See - packages.debian.org/stable/misc/postgresql

oh, wait...nevermind.

Foreign keys are an enterprise feature

[alienmole]

I suspect you're thinking of relatively simple cases where you have a single application working against a database. Which of course is the standard MySQL scenario.

Where foreign keys and the other referential integrity features really shine is in true enterprise scenarios, when you may have hundreds or thousands of applications, written in multiple languages, working against the same shared database(s).

In that scenario, the only viable way to duplicate the functionality of foreign keys at the application level is to have a middle layer which all other applications are required to go through. Realistically, that middle layer has to be implemented as a server, serving requests for object/record creation, update and delete over the network. Implementing it as a library to be linked into applications doesn't work well, because there are multiple applications accessing the database, and integrity enforcement needs to be centrally coordinated.

Implementing a middleware data server for an application isn't all that difficult, but integrating it into applications can be. Most application development environments know how to talk to databases, but don't automatically know how to talk to your application-specific, language-independent, data server. So now you're writing a client library for each app dev platform used in the enterprise, and dealing with things like integrating your custom interface with data-bound controls in the user interface. BTW, this is where people start resorting to e.g. SOAP, and projects start going off the rails (no pun intended, Ruby fans).

Luckily, as it turns out, there are already standardized, widely-available, well-supported systems that implement a centralized data serving service which enforces referential integrity. They're called databases. And foreign keys are an essential part of the service they provide.

Re:Foreign keys are an enterprise feature

[gumbi west]

And is there a problem with the current mysql implementation of foreign keys?

Re:Foreign keys are an enterprise feature

[alienmole]

I was responding to the claim that foreign key integrity should be handled by the application rather than the database. MySQL's support for foreign keys doesn't affect my point.

Re:Foreign keys are an enterprise feature

[gumbi west]

Unless you want to be on topic for the article. It was a serious question, you seem to be a foreign key lover, are the non-ansi compliant things in MySQL deal breakers or common? I don't know.

Re:Foreign keys are an enterprise feature

[alienmole]

When I use MySQL, it's in the same sort of applications everyone else uses it - web sites and other fairly straightforward applications. I don't know of anyone using MySQL as a shared enterprise database. That market is pretty locked up by the likes of Oracle and IBM DB2, and to a lesser extent, Sybase and Microsoft SQL Server.

You'll often hear people talk about this or that specific technical feature that MySQL doesn't support or doesn't handle well, but beyond that kind of thing I think the deal breaker is just that no-one else is using it in those environments, and there's no reason to be the first person to bet the operations of a multi-billion dollar company on it (other than certain dot-coms).

Read this post about Oracle at Amazon in the early days:
http://glinden.blogspot.com/2006/03/early-amazon-o racle-down.html

That's a situation that no admin team wants to find themselves in. It happened at Amazon because they were using the database in unusal ways. The way to avoid that is by using what every other company like you uses (Amazon didn't have that luxury at the time). MySQL hasn't been battle-tested in the sort of environments I'm talking about, and it lacks a whole range of features designed for systems where any downtime at all costs significant dollars. It also lacks an entire ecosystem of tools and personnel aimed at that market. Over time, I imagine MySQL might enter that space, but it'll need to acquire a lot of stuff along the way.

It's a living

[wsanders]

I have earned a good living writing fairly stupid Perl scripts to fix refential integrity in MySQL databases that foreign keys would have prevented easily. (Why is it always the SYSTEM ADMINISTRATOR who has to fix all that crap?) Please do not endanger my career!

Yeah any coder can work around this - and bats will fly out of my nose on Christmas Day!

Re:It's a living

[Anonymous Coward]

"Yeah any coder can work around this "

where are the developers working that code like this? jeez, i am far from an expert, but there are some basic best practices that everyone who is working for company larger than a ma and pa shop should know....

i am going to open my own consulting firm, "We fix your bugz"

Object Oriented and OS security integration

[pseudorand]

This review seemed to overlook two important factors:

1) PostgreSQL has what at first appears to be a very cool "Oject Oriented" feature where one table can inherit from another. Once you spend some time investigate this feature, you find out that referential integrity and derived tables are mutually exclusive. (i.e. if you have a Produce table with a foreign key referencing the Fruit table, but Fruit has two children (Apples and Oranges), the foreign key constraint will prevent you from ever inserting anything into Produce that references a row in Apples or Oranges). This really pisses me off because save that SNAFU, OO would be a really useful feature in Postgre.

2) PostgreSQL support OS account integration. I don't have to have a completely separate set of user account for my database. Even though MySQL gives me fine-grained security, to use it I have to implement and maintain a whole new set of user acconts, which makes it easier to just have the anonymous "ABCApp" account that my app uses to accese the database. I know this is how 99% of database driven apps out there work, but it doesn't change the fact that it's just plain wrong.

In the end, I still use MySQL because I just find that it has better documentation and community support.

Maybe your a noob?

[Anonymous Coward]

I use mysql with billion row tables , all indexed just fine, instant return on select queries.. Tired of these noobs not knowing what they are doing..

Re:Maybe your a noob?

[Shados]

I use mysql with billion row tables , all indexed just fine, instant return on select queries.. Tired of these noobs not knowing what they are doing..

Hmm, I wasn't aware special training was required to create a 50 million record table and index it. A "noob" who "doesn't know what they are doing", can do it perfectly fine on just about every other RDBMS.

Care to explain to me what your diagnostic might be for the parent's problem, Mr. Anonymous Coward?

Re:Maybe your a noob?

[Anonymous Coward]

yes, the diagnostic would be to create the index, BEFORE you load the data. You do not create an index on a field after all that data has been inserted else it has to create it on each row. Simple thought out design would fix his problem.

Re:Maybe your a noob?

[Shados]

yes, the diagnostic would be to create the index, BEFORE you load the data.

You're kidding, right? You've never had to use a DBMS in business environments or something? Needs and requirement changes, and you sooner or later have to add indexes on terrabytes of data. No way around it, unless you're doing a stupid personal web site. Thus why the parent was testing how quickly it would be.

And most serious RDBMS don't have any issue with it. Maybe in Fairy Land you can make a database design and stick with it: in the real world thats a dream and little more.

Re:Maybe your a noob?

[Anonymous Coward]

1. Create copy of table structure with a .sql dump without the table data.

2. Make index changes on fields inside the copied table - without the data.

3. select from original table into new table with new index data (very quick):

INSERT INTO new_table_with_index(fields)

SELECT fields,

FROM old_table

WHERE ..

And there is your 'quick changes' in your 'business enviornment'.. Also, I doubt you can code anything being you cannot even provide an example of these type of 'fairyland quick changes'

Re:Maybe your a noob?

[Kent Recal]

Hey, no wonder you are posting as AC, given you have obviously never worked on a worthwhile dataset.

It is common practice to do bulk imports on a non-indexed table and create the index afterwards.
It's done that way for the simple reason that bulk indexing is *much* faster than having the db
do its index lookup/update magic for each insert.

Sure, there's no difference when you're dealing a few thousand rows.
Nonetheless your statement shows that you've never tried it on a few million rows...

Re:Maybe your a noob?

[Anonymous Coward]

It's done that way for the simple reason that bulk indexing is *much* faster than having the db
do its index lookup/update magic for each insert.


INSERT INTO new_table_with_index(fields) SELECT fields, FROM old_table WHERE (if u want to use where)

In the SQL language you can insert a whole table with 1 select statement as I listed before. This is NOT indexing for each insert, nor is it inserting each row 1 by 1. IT IS DOING IT IN BULK. under 5 minutes for 100 million rows on a dual xeon.
 
  Yes it is commong practice for noobs to run an index on a 100 million row table. I've seen it many times. And theres no point in arguing with you on my regular slashdot account..

Re:Maybe your a noob?

[Shados]

Yes it is commong practice for noobs to run an index on a 100 million row table

Here's the catch: on just about any mainstream RDBMS except.... MySQL (from what I hear, as by the previous parent, etc. Haven't tried it myself) and Pervasive SQL, adding an index on a table that already contains data isn't an issue. Obviously it isn't: if there's a manual workaround, its even easier to have it built in the RDBMS.

So watching you argue how "noob" it is to do it, is quite the chuckle. And that doesn't count into the fact all the issues there are when you transfer data the way you suggest, when you have thousands of objects that depend on the original, on a live system.

So again, I'll restate my original point: It works everywhere else(Since I refuse to acknowledge Pervasive SQL's existance beyond naming it), and it works just fine, fast, etc . Why shouldn't we expect MySQL to do it too?

I don't understand the logic behind expecting a standard feature to work efficiently, when it already works in so many other implementation: its not like its a physical limitation or anything.

Re:Maybe your a noob?

[Shados]

I don't understand the logic behind expecting a standard feature to work efficiently(...)

Wow, its late and I can't type anymore it seems. What I meant is, I don't understand why you think its noobish to expect a feature to work efficiently in MySQL, when it works fine everywhere else, and is amazingly useful in a dynamic production environment.

Re:Maybe your a noob?

[Hornsby]

Actually, on any DB it's better to create the index after the fact if possible for a simple reason. The most common index is a B-tree, and creating it after the fact leaves you with a perfectly balanced tree. Creating it while loading data requires a lookup for every row, which takes much longer, and it also results in an unbalanced tree, so your queries will not be as efficient. In my initial attempt using MySQL, I actually did create the index ahead of time, but the time required to load the data was much too long. I researched this issue quite a bit, and found this article, which echoed the sentiments of many, indicating that it's much more efficient to create the index after the fact.

http://www.devshed.com/c/a/MySQL/MySQL-Optimizatio n-part-1/6/

This seemed like it would work but lead me to the previously described problem. I would also like to add that the company I work for does use MySQL in certain instances with tables over 100 million rows; however, these databases are maintained by a third-party company specializing in the application, and even they wrestle with corruption quite frequently. I've used MySQL quite a bit in the past, and I'm not saying it's impossible to use with large amounts of data. I'm saying it's a PAIN, and out of the box Postgres is much easier to work with and much easier to maintain. Anyway, this is just my experience, so take it FWIW. BTW, it's pretty obvious that you're trolling at this point, so I'm only responding for the benefit of those who might actually be interested in doing this for a living in the "real world".

Re:Maybe your a noob?

[Shados]

Simply out of curiosity...I don't know if you'll ever read this, but oh well :)

You mention that if you build the index on the fly, it leaves you with an unbalanced tree. But last I checked, b-trees are always balanced no matter what.

So was that really what you meant?

Re:Maybe your a noob?

[quantum bit]

But last I checked, b-trees are always balanced no matter what.

No, b-trees are not always balanced. In fact, there's a whole field of research dedicated to optimal methods for balancing them.

Certain types of binary trees, such as red-black trees, are always balanced, but they pay a price in terms of higher overhead on inserts.

Hogwash

[msobkow]

Any senior application developer, architect, or DBA knows the importance of having the database provide as much data protection as possible. That's why thousands of engineers were involved in the creation of the ANSI92 specs.

caveat that this is more then 15 months old.

Which implies that the results likely DO NOT include all the work sponsored by an overseas company (NEC?)

Re:Use "than," not "then," in the blurb at the top

[Anonymous Coward]

Those people would probably tell you it is a mute point :P

mysql won't be any good until...

[timmarhy]

... insert into mydate values('99/99/9999') fails with an error stating it's invalid. at the moment, it allows that shit as a valid date. mysql is NOT a good db.

Re:mysql won't be any good until...

[arjenlentz]

mysql> insert into dat values ('9999-99-99');
ERROR 1292 (22007): Incorrect date value: '9999-99-99' for column 'd' at row 1

Some corrections

[yacc143]

I've sent the email below to the address mentioned in the trailer of the page:

Hi!

Some minor points:

Large objects:

your page suggests that PostgreSQL does not support large object the
way mysql does. In reality it has been supporting large rows for years
now. It has the TEXT datatype and the BYTEA datatypes to store texts
and binary columns without limits. (Well, both mysql and postgresql do
have huge limits on rowsize)

SQL conformance:

http://www.postgresql.org/docs/8.0/interactive/fea tures.html shows the
details of the SQL conformance for PostgreSQL 8.0, and is (c) 2005,
guess this was available when you researched your comparision.

It especially does not do a subset of SQL 92/99, it's rather compliant
to SQL 2003. Certainly more so than say mysql, which has been slowly
adding SQL support, as you say yourself; notice that Mysql AB in their
sales documentation don't claim any SQL conformance,

PostgreSQL does allow selecting data over different databases via
dblink, but it's a kludge. OTOH, mysql optimization papers still claim
denormalisation as a solution to the fact that MySQL5 only has Nested
Loop as an executation strategy for joins. Furthermore, subselects are
always materialized.

Speed:

I question your blanket statement that MySQL is very fast on complex
queries. As noted above, it's very sensitive to the way a query is
formulated, and has basically only the most primitive execution
strategies for joins and subselects. Complex queries DO involve
usually both of these constructs.
You also mention the problem of forking backends for startup speed
problems. I'd like to point out that the classical solution to that is
to use a connection pool either in your app (as e.g. PHP provides), or
use pgpool.

Stability:

Anecdotal evidence. Fact is that mysql crashed data this month in the
office, and I've had deployed Postgres95 about a decade ago, and never
lost data. Fact is, that MySQL is offering many options where the
application can decide to have an unstable/unsafe DB setup (by
choosing e.g. MyISAM storage), while the same "performance
optimizations" for PostgreSQL are only available via the server
config. OTOH, PostgreSQL has only just now become stable enough on
64bit platforms to consider productive usage.

Special Serverside features:

You seem to miss the wealth of features that PostgreSQL provides. E.g.
it is one of the most flexible DBs to support updateable/insertable
views. Addtionally, it supports vertical table partitioning, etc.

National Language support:

PG can set the language per database and cluster. Actually, it can
only set the encoding while creating the database:

createdb -E UTF8 testdb
createdb -E SQL_ASCII testdb

Hopefully you can update your comparision, don't hesitate to ask if
you have any questions.

yacc

Re:Use "than," not "then," in the blurb at the top

[NotZed]

Or did you mean a moot one? :)

What a joke

[Thomas Charron]

The person who WROTE this article obviously doesn't know the meaning behind the jargon he uses.

Not to mention, he didnt know the meaning like. Over 2 years ago.

Derivative or not

[maop]

They could try it and IANAL but IMO they would easily win as long as the client software is not closely coupled with MySQL by using queries that do not rely on:

• Communication of internal MySQL data (but only standard SQL compliant data formats).
• Stored procedures that are not GPL.

I never have read such a biased review....

[mw]

1. "MySQL runs as a native Windows application (a service on NT/Win2000/WinXP), while PostgreSQL is run under the cygwin emulation."
Get your facts. If this review ist really 15 months old, the 8.x series was available more than 6 months, and it's running native on Windows.

2. under section "SQL standard COMPLIANCE": MySQL uses SQL92 as its foundation. Runs on countless platforms.
Wow. It's completly new to me that the number of platforms is related to SQL standard compliance. Beside this, standard conformance is far better in postgresql, there are numerous comparision tables available.

3. "STABILITY": mentioning 6.x releases clearly states how clueless the authors of the review are. Last update for 6.5 was in early 1999.

4. "LARGE OBJECTS": "In Postgres, Large Objects are very special beasties.". Historically seen - right. But why not use the bytea datatype as available in Postgresql for several years now?

5. "ALTER TABLE": 15 month ago postgresql of course had full support for all those functions.

Biased, clueless and just stupid review.