MySQL 5.0 Now Available for Production Use

chicagoan writes "MySQL AB today announced the general availability of MySQL 5.0, the most significant product upgrade in the company's ten-year history. The major new version delivers advanced SQL standard-compliant features such as stored procedures, triggers, views & new pluggable storage engines. Over 30 enterprise platform and tool vendors have also expressed enthusiastic support for the new release of the world's most popular open source database."

Comments

stored procs and triggers, finally

[cerelib]

I have always been amazed thy MySQL has been able to gain the popularity it has without features like stored procs and triggers.

Re:stored procs and triggers, finally

[SpaceLifeForm]

And if the database is not properly protected with constraints, you can screw up the database. And if the schema has to change, we have to hunt down your code and make changes there.

The best way to manage a database is to only allow applications to modify the database via stored procedures. You'll have far fewer problems that way.

Re:stored procs and triggers, finally

[User 956]

Almost every database out there impliments an ISO or similar SQL standard as it's base (SQL-92 in most cases). They then build on top of that by adding their own features, while still supporting the common SQL syntax. It's not about being a barebones implimentation of a standard, it's about supporting the standard as your base.

PostgreSQL supports SQL-92, while adding it's own extra features (which describes most other databases like Oracle and MS SQL too), including the support of the "LIMIT" statement. MySQL doesn't support any standard base, instead existing as an arbitrary mish mash of standard and propritary SQL. It wasn't until the current version, 4, that MySQL even bothered to add support for UNION.

With every other database you can start working safe in the knowledge that while having it's own extensions, you're working with a normal "SQL" database. MySQL, while posing as SQL, has little if anything in common (in particular see threads about optimization - getting fast code in MySQL means learning an entirely new system filled with quirks and vomit inducing workarounds to solve language faults)

Re:stored procs and triggers, finally

[temojen]

And if its in your code, the query executes using the resources of the machine running your code, as opposed to the resources of that (usually) bigged database server.

This is so wrong it made my head explode. All queries are executed in the server. Stored procedures are compiled and optimized once (per connection, and most sites use connection pooling).

Re:stored procs and triggers, finally

[JoeD]

With hardcoded SQL, you run the risk of SQL injection exploits, unless you're very careful to escape each and every user-written field that makes its way into a SQL statement.

Re:stored procs and triggers, finally

[commanderfoxtrot]

You should always use bound placeholders in SQL. Then you don't get SQL injection exploits.

i.e. define a parameter then execute: SELECT x FROM y WHERE p = '?'

It is also faster as the DB can use an already prepared query plan.

This is the ONLY way to write decent SQL applications?

Re:stored procs and triggers, finally

[msuzio]

That would be what prepared statements are for. No need to use stored procs just for that.

Re:stored procs and triggers, finally

[xelah]

This is so wrong it made my head explode. All queries are executed in the server. Stored procedures are compiled and optimized once (per connection, and most sites use connection pooling).

The OP is presumably referring to work which stored procedures do which isn't part of a query (like running business logic, chopping text about, or god knows what else). Performance wise it /might/ be better to do this on your database machine if it drastically reduces the amount of data sent across a network. Otherwise, IMHO, it's not such a good idea otherwise as you're pushing work into a part of your system which probably can't be spread across machines so easily. (Though you may have other reasons for using SPs than performance, of course.)

SPs aren't the only way of compiling and optimizing a query once. For instance, caching Perl DBI statements will, for databases which support it, result in the same thing. With Postgresql, for example, DBI will send 'PREPARE blah AS ' before the first execution and then use 'EXECUTE ' afterwards. Unlike SPs these disappear when you drop your connection (and so remain inside the client code and not in the DB).

Re:stored procs and triggers, finally

[xelah]

That's an excellent argument for having a layer between applications and the data. Stored procedures are certainly a way to achieve this, but they aren't the only way to achieve it. Is a bunch of, say, Java stored procedures all that different to, say, a Java server which exposes application domain methods via CORBA or J2EE (or whatever), is the only way for the rest of your system to get at the database and contains all of the queries all that different? Not really - and the second method has some advantages (like allowing you to run many copies across many computers). IMHO you really do have to think about your system architecture and it's requirements before making a decision like 'everything goes through SPs'.

Re:stored procs and triggers, finally

[Anonymous Coward]

C'mon, how can you say that ?

One of the challenges of MySQL 5 was precisely to get closer to the SQL:2003 standard. And it did.
Consider the MySQL stored procedures for example : their syntax is probably one of the most respectful of the norm today. And that effort was also made for all the other new funcionality of MySQL 5.

Now since you're talking about the past flaws of MySQL, you shouldn't confuse the absence of a functionality with the proprietary implementation of that functionality.
It's true that until 2 years ago or so MySQL didn't support UNION but when it did it was in a standard-compliant way. But as far as I know MySQL has never had such a proprietary approach as the one Oracle had to outer join syntax for years for instance.

Concerning the LIMIT statement, it is proprietary syntax because there is no equivalent for it in the SQL standard ! By the way you won't find two RDBMS that implement it the same way...

So don't tell us MySQL is one of the less standard-respectful databases because it's just not true. It might not be the most SQL standard-compliant because it lacks standard functionality but what is implemented is fairly normative.
And don't come arguing that MySQL should implement "all of the standard or none of it" because you know pretty well it is not possible for a young RDBMS like this...

Re:stored procs and triggers, finally

[einhverfr]

PostgreSQL has another option as well. Views and rules. When I use stored procs for these, I always wrap them in views in order to keep the interfaces clean and portable. YOu can essentially define custom select/insert/update/delete using these tools in ways that are more flexible than triggers though triggers have an edge in other areas.

I usually think of app structure this way (this is a flexible guide, not a hard map):

User Interface
Application Logic
Data Access
Data Presentation (views/rules, can include multi-app business logic)
Data Maintenance (triggers)
Data Storage (base tables)

Re:stored procs and triggers, finally

[Jearil]

what if '?' = '1; delete employees;'

Then it would probably return:

Empty Set (0.00 sec)

as I doubt any record in field p will actually equal the string '1; delete employees;'

Now what you're probably thinkog of is setting that ? in '?' to be something like:

1'; delete employees;

attempting to escape out of the select prematurely with a well placed ' after the 1. However, using prepaired statements (which is what I believe the GP was speaking of) runs the statement through a parser to set escape characters into the query strings so something like that can't happen. The example above would turn into something like:

1\'; delete employees;

which would still yeild a fun result of Empty Set (0.00 sec). You don't actually need a stored procedure for safe queries with user input, just a language that has prepared statements (or heck write you're own version of it if your language of choice doesn't have it, it's not that difficult of a thing to make).

Innovation

[chrysalis]

Wow, triggers and stored procedures. MySQL really does innovation.

Re:Innovation

[choas]

So there I go, looking up stored procedures on Wikipedia and it gives me this:

Wikipedia has a problem

Sorry! This site is experiencing technical difficulties.

Try waiting a few minutes and reloading.

(Can't contact the database server: Lost connection to MySQL server during query (10.0.0.101))

Well at least I now know you're not a troll and it DOES gave something to do with MySQL ;)

Re:Innovation

[b0r1s]

Postgres was free ('as in beer') and free ('as in a real license'), and gave away these features long ago.

Besides, for 'freedom', the BSD license used by Postgres beats the GPL hands-down.

Re:Innovation

[Jamesday]

10.0.0.101 is Adler. Its uptime is currently 2017391 seconds (23 days). Adler's uptime is that short because it had a hardware repair. It was probably overload - several DB servers are dead right now and Monday is the busiest day for the site. So far the site is consistently filling to capacity all the hardware which is ordered and that shows no sign of stopping. It's now at 4500 pages per second, 400 megabits/s. For scale, the biggest Slashdotting the site saw was about 650 pages per second.

Averages over 23 days for this one server: 1620 selects per second, 10 inserts and 3 replaces per second. That is: 140 million selects per day average. Peak rates are about double average rates, typically in the 3000-5000 qps range.

I'm one of the roots at Wikipedia. Figures from SHOW STATUS just before typing this reply.

Re:Innovation

[Jamesday]

Adler is a dual Opteron with 16GB of RAM and 6 15,000 RPM SCSI drives. We have two like that, one with 8GB and 15K SCSI, several with 4GB and slower drives.

Suda, a dual Opteron 4GB box with 6 10K SCSI drives has 8 day uptime and 580 qps average but it's probably been out of normal load quite a bit of that time for various chores.

Holbach, a dual Opteron 4GB box with 6 10K SCSI drives has 28 day uptime and 616 qps average.

Ariel, a dual Opteron 8GB box with 6 15K SCSI drives has 8 day uptime after repairs and 1280qps.

Samuel, the current master, is another 32GB dual Opteron with 6 15K SCSI drives, has 83 day uptime (that is, no crashes or deliberate MySQL server shutdowns for 83 days). Only 367 qps and I think it was not in service for quite a while before it was made master - think we put it into service earlier than planned because of the hardware problem on Adler. It's typically running nearer 2000-4000qps now it is in service.

khaldun and bacon, both 4GB dual Opterons, one with 10KK SATA and the other with 7200RPM SATA are both down. All are running Fedora core.

Add up those query averages and it comes to 385 million per day cross 5 servers. We might pass the billion select per day mark this year; hard to predict.

Some people will say that MySQL is incapable of doing serious work, even though just 5 main database servers are powering a top 100 site delivering 1 in every 1,000 web pages viewed in Alexa.com's sample. Others will say they use MySQL because it gets the job done. Including me. :)

Well done MySQL AB

[iBod]

It's not the fanciest, or the fastest, but it's ubiquitous and free!

I for one have found it invaluable on many projects where a full-featured, high-capacity RDBMS would have been more trouble and expense than it was worth.

Props to MySQL!

Well this is neat

[lewp]

No matter if you're a MySQL supporter or someone who thinks that everyone should use a "real" RDBMS, having all these new features available to MySQL developers is a good thing. There's quite a few apps, I'm sure, that don't use these features in databases where they're available simply because they're aiming for the lowest common denominator that was MySQL's feature set.

Anyway, not trying to start an argument about the relative merits of any particular RDBMS, but this is a good thing all the way around. I look forward to taking it for a spin.

Gotchas

[Ed Avis]

It would be cool if someone knowledgeable could check the old MySQL Gotchas list and see how many have been fixed in 5.0. My hope is, nearly all of them.

Re:what the?

[b0r1s]

'General' implies usability in production systems. What you really want to read it as is this is the first non-beta release.

We tested many of our sites (including my personal favorite, vobbo, a site for video blogs) and found some very significant speed improvements, especially in some of the math functions (SIN, COS, etc).

Re:Almost caught up to MSSQL!

[Anonymous Coward]

With all due respect, SQL2K has been one of the most stable databases I've ever worked with. Sybase was a close second, Oracle was fine once you got it installed. Say what you will about their consumer products, but MS can make some damn fine products *when it wants to*.

Woohoo! MySQL is finally ready!

[philovivero]

I've been waiting for years for stored procedures, triggers, and... ah. Wait a minute. No, actually, I've been running multi-terabyte millions-of-transactions-per-hour database clusters with MySQL for about two years now.

Well. Anyway. Now all the little shops that have been making excuses about why not to use MySQL can now start using it.

(In fairness, actually, yes, the MySQL gotcha's page scares me, too)

Re:Generic Web-Frontends for MySQL

[FireFury03]

This is slightly off-topic, but I was wondering if anyone is aware of any generic web-frontends for MySQL?

How about http://www.phpmyadmin.net/?

phpMyAdmin

[smelroy]

I'm not sure about reporting specifically, but phpMyAdmin is the way to go for a generic MySQL front end.

It's good, but there's better...

[User 956]

It is still lacking compared to other free databases such as PostgreSQL and Firebird, but version 5 is a real improvement. (as mentioned, now you have things like triggers, stored procedures, views and sub-queries.) If you use strict mode integrity checking will work reasonably.

What I'm currently miss the most in the new version is that it can't handle domains and the ability add check constraints as you create tables is somewhat lacking. So, even if MySQL have done a tremendous job improving their product I would still go for PostgreSQL, or Firbird any day both for technical and legal reasons. Both Postgresql and Firebird also seam to be better at internationalization.

The fact that Oracle just bought the company that supplies the default MySQL storage engine doesn't spell good for the future. Even though MySQL could continue to use InnoDB in the future under the GPL licence it is in Oracles power to raise the licence fees for commercial use. That would mean less incomes to MySQL AB and that could hurt their ability to develop the product further. However, afaik Oracle have not said anything about raising the prices other than that the licence deal with MySQL is going to be renegotiated in '06. To me that sounds a bit ominous.

does that mean they fixed the gotchas?

[RelliK]

Any word on when they are planning to fix this? With this careless disregard for data integrity, it's hard for me to take MySQL seriously.

Re:does that mean they fixed the gotchas?

[GabboFlabbo]

Well, straight from the email:

Implementing ANSI SQL standard ways of using existing MySQL features means there will be fewer unpleasant surprises ("gotchas") for those migrating to MySQL from other database systems:

- Strict Mode: MySQL 5.0 adds a mode that complies with standard SQL in a number of areas in which earlier versions did not; we now do strict data type checking and issue errors for all invalid dates, numbers and strings as expected

Same question I asked when it went beta:

[jbellis]

Do you get these features in all table types, or do you have to use the (much slower) InnoDB tables, as with transactions?

Re:no x.0 for me...

[sethadam1]

I never get the .0 release of anything ... And I've been wanting to try out Postgres anyway...

What's the purpose of posting something like this? I am not trolling, I'm just curious why everytime there's a MySQL thread, someone has to chime in about why they won't use it and why Postgres is better. What is the motivation to visit a thread solely to post something negative. Seriously, what is wrong with internet culture?

So I'm not completely off topic, I read the feature list, and this thing looks fantastic. Views, triggers, sp's, a new data type, BIT, for storing Booleans, which MSSQL has and is AWESOME. You may not want to try it, but some of us are excited to get our hands in it and have been waiting for the first "blessed" release!

Why MySQL is popular

[einhverfr]

I stopped using MySQL as my primary RDBMS in 2000 (I still use it when apps require it, but I almost never program for it.

When I started using PostgreSQL 6.5, I noticed that it was *far* harder to use than MySQL. It had a *huge* learning curve and was missing obvious functionality such as alter table drop column. But it provided better data integrity checking than MySQL. So for the next two years, I would prototype databases in MySQL before moving them over to PostgreSQL.

MySQL was good enough for simple CMS type tasks and extremely user friendly at a critical time in the market. PostgreSQL, designed for enterprise apps from the beginning, placed technological soundness ahead of ease of use. However, over the last five years, PostgreSQL has actually become the simpler RDBMS to use and program for. No questions of "I misspelled InnoDB and now it created a MyISAM table instead" or such.

Unfortunately, it seems that by the time PostgreSQL became easy to use, MySQL already had cornered the low-end market. However, I would say that aside from light-weight CMS tasks, PostgreSQL is still far and away the better application for a number of reasons:

1) ACID compliance is pervasive throughout the engine. Creating operations outside a transaction, while possible, requires an untrusted programming language (like C, PL/PerlU, PL/PythonU, etc).

2) Date's Central Rule is designed into the RDBMS and cannot be circumvented by the application (which is not the case in MySQL 5.0 as strict mode can be disabled by an application).

3) PostgreSQL, while not perfectly standards-compliant, is far more standards-compliant than MySQL. This allows for much more portable code to be written for PostgreSQL than MySQL.

4) PostgreSQL is much more extensible than MySQL. You can add language handlers to allow you to create stored procs in whatever languages you want. PostgreSQL currnetly ships with PL/PGSQL, PL/Perl, PL/Python, PL/TCL. Other languages, such as PL/PHP, PL/Java (or PL/J), PL/SH, and PL/R are available as addons. I believe there is an attempt to make Mono available for stored procedures. Also you can add new data types without too much difficulty.

5) PostgreSQL has better Business Intelligence capabilities than MySQL. Capabilities include table partitioning and more. Parallel queries (across nodes) are under development in a spinoff project called Bizgres.

Re:Why MySQL is popular

[lukej]

I think I am pretty agnostic about the whole Postgres/Mysql love affair. But I do find amusement in the 'personalities' of those supporting both sides.

Point #3;I always like the standards = portable argument. Reality check:

a> if somebody writes a huge DB app, standards compliant or not, their going to stick with their base DB
b> if it is a small DB app, then it's trivial to rewrite if you do want to migrate DBs

With all my Postgres and Mysql based stuff, I've never rewritten one for the other. Often times I've upgrade the DB or it's host machine. But, with both Postgres Mysql, performance for my (granted, small) DBs is fine with both, the DBs are free, and installing is simple.

I just don't think DB migration takes place often, even if you can find two compliant DBs!

Re:Almost caught up to MSSQL!

[TheKubrix]

I hope someone mods the parent down, because thats just stupid/ignorant.

I've been running MS SQL 2000 for about 4 years now and it has NEVER crashed. Nor has it corrupted any data or any other such destruction.

I notice that its people that either have _NO_ database experience tend to bash MSSQL, and they don't even know why. Your comment is a case in point.

Quite a news day

[dtfinch]

NASA drops the whole "shuttle" idea. Andy releases a new version of Minix. DrDOS steals from FreeDOS. And MySQL becomes a real database server.

Now with SAP...

[MosesJones]

The biggest thing here isn't the stored procs et al... its that SAP, you know the worlds biggest enterprise software vendor... will CERTIFY its application on MySQL (when using the old SAPdb stuff). This means that organisations that spend MILLIONS on SAP systems can get support if they run it on OSS.

That is the big deal, not functionality its about the support. MySQL might be the poor relation to Postgres in terms of functionality, but MySQL has a MUCH big best friend who can open doors where functionality doesn't count.

This is a real moment IMO, as a well known OSS database has a massive seal of approval from one of the most famous for reliability vendors in the market.

Next time your boss says that OSS can't do a DB, tell him that SAP disagrees.

Unofficial slashdot MySQL thread checklist

[jonfelder]

1. I love MySQL!
2. Who cares? Postgres is and always has been better.
3. I used to use MySQL, but now I don't.
4. I used to not use MySQL, but now I do.
5. If you use MySQL you are stupid.
6. If you do not use MySQL you are stupid.
7. Only Nazis and CowboyNeal use MySQL.
8. Did anyone say goatse.cx?

Some thoughts

[ngunton]

I've been using MySQL for about six years now, and it's been working very well for me. I utilize it on my crazyguyonabike.com, a bicycle tour journals website. It has about 750 journals on there, with over 60,000 pictures. I use replication to back up the database remotely, and all in all it works very well. I honestly can't understand the level of hatred towards the tool that emanates from many of the posts here.

I have to say that I cringe every time I see a MySQL story on slashdot these days, because it just seems like there is a legion of PostgreSQL zealots just waiting for any chance to denigrate MySQL. It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc. Even when MySQL AB adds a feature or does fix some perceived failing, then the detractors simply ignore this and move on to some other apparent showstopper. For example, it's not enough that MySQL has transactional capabilities - no, now they simply moan that it's not the default (MyISAM still is).

We seem to have people who have what can only be described as a religious mindset when it comes to these issues. "Religious" in the sense that their minds are closed, and no matter what new facts come to light, they will simple twist everything around to match with their existing worldview. So, in these people's minds, MySQL AB adding features is not a positive thing, it's rather a sign of how wrong Monty was in the past to suggest that most people really don't need transactions for everything. Well, at what point exactly do we have "proof" that I don't really need transactions for my website? Is six years of 24/7 use enough? If not, then how long exactly?

Yes, I've had problems, of course I have. You will with any tool, PostgreSQL included. No matter the fact that PG has had transactions from day 1, people still got corrupted tables occasionally. But at the end of the day, the results are the same - do you still have your data? Is it intact and internally consistent? I can answer yes to that. I don't mind having some logic in my application to delete some records when some other records get deleted. It works really well, and while in theory it could cause data inconsistency, in practice this has never happened. Even if it did, a quick perl script would be sufficient to clean things up - I'm doing that kind of thing all the time anyway, as the database evolves and I need to shift stuff around or change table structures. It's no big deal, really! Some will say No, this is a Horrible Solution and you should put business logic into stored procedures... I say, get a life. That's *your* solution, it's not everybody's. You're simply moving your complexity around, you'll never really get rid of it. Some people are more comfortable with their complexity in stored procedures, I'm perfectly comfortable with it in my Perl application. So what, does it work for you? If so, then who cares.

There *are* some things in MySQL that disturb me, but I don't know if they are common to other DBMS solutions out there. One of the big ones for me currently is that the query optimizer only uses one index in queries. I know you can have multi-column indexes, but I still see this being a problem for some of my more complex queries. Does PostgreSQL do this better? Informed opinions please, rather than fanboy noise.

Also, speed. I hear lots of anecdotal tales about how much faster PostgreSQL is these days, especially under load from multiple connections. I'd like to hear from anybody who has actually made a transition from MySQL to PostgreSQL for a high-load Web application. Can PostgreSQL really replace MySQL now? Or is this another case of wishful thinking?

Thanks,

-Neil

Current results of the MySQL Gotchas

[Anonymous Coward]

Results of tests against MySQL 5.0.16-nightly-20051017-log (I downloaded and installed this latest snapshot today)

1.1. NULL, or when NULL IS NOT NULL
        The behavior was not changed, but it's of no importance anyway.
1.2. AUTO_INCREMENT
    The behavior was not changed, and I must admit that all that sounds scary. On the other hand we're using a LOT of mysql where I work and never run into a single problem caused by this particular problem.
1.3. ENUM
        Behavior unchanged - This isn't a real problem at all...
1.4. Case sensitivity in CHAR / VARCHAR fields
        Weird behavior which might degrade performance, or help you - depends on what you are doing. But I don't agree with the author's suggested solution redefining the table string as binary since you can simply force a binary comparison on the select, so who really cares about this?
1.5. VARCHAR limited to 255 characters
        This restriction was lifted. Current limit is: 2147483647
1.6. VARCHAR's trailing blank allergy <= fixed ^^
1.7. DEFAULT NOW()
        This deficit only affected mysql versions below 4.1 - And I can tell you it didn't reappear in 5.0 ;-)
1.8. INSERT INTO ... SELECT ...
        Like 1.7 this was only true for versions prior to 4.0.13... Nothing to see here
1.9. Comments beginning with --
        So ok... comments introduced with -- don't work. As a web developer I never came across having to comment sql inline XD
1.10. UNION and literal values
        This bug was fixed. Although I ran into a character set problem on this one since the table and mysql defaults were set different and unions are supposed to have the same character set - or maybe I'm just too tired to understand what just happened...
1.11. Division by zero
        This behaviour is still intact 1 divided by 0 results in NULL ... Doesn't really bother me.
1.12. 'concatenation' || 'or'
        This "fault" results from not running mysql in ansi mode which makes it overload the || operator diminishing its usefulness.
1.13. What goes in - isn't (always) what comes out
        Holy shit, a variable range overflwos! If anyone really falls for this - go take a beginner's programming lesson...
1.14. February 31st
        The behaviour has changed. But since date (as is datetime) is basically a string, I don't really like the kind of checking mysql is now performing O_o I must look into that further since you still can insert some "malformed" dates, but only some of them get changed. What's wrong with that?!?
1.15. Space between function name and parenthesis
        Although the behavior changed, the author won't be happy with what he sees because it still doesn't behave like his dbms of choice... But if we're honest - this is no bug!

Now, some things got fixed, some things just changed and most of these don't even matter. All in all 5.0 is a nice release and in my opinion MySQL is still very likable and for me as sys admin quite comfortable.
What bothers me most at the moment is 1.14. - because that might have some effect on real world situations. Maybe someone else wants too look into this further so I can read about it tomorrow?

Q: using older JDBC connector (LGPL)?

[MarkWatson]

I have a question: if I use the older JDBC connector (from June 2002) before the connector project was absorbed by MySQL and became GPLed, is it OK to use MySQL on a leased server with a Java web application that is not GPLed?

That is, if my web application links with the old LGPLed connector which uses a socket connection to the GPLed MySQL server, then that is fine license-wise, right?

This is a question for all the 'Slashdot lawyers' :-)

Seriously, from reading the licenses, I believe that the scenario that I mentioned using the older LGPLed JDBC connector is OK, while using the newer GPLed JVBC connector(s) is not.

Also: I believe that this is not an issue with Ruby since the client MySQL connector is not GPLed.

Some more thoughts

[Just Some Guy]

It's the same littany every time - PostgreSQL is so much better, have they fixed the "Gotchas" yet, etc etc.

I also cringe whenever a MySQL story comes out because it seems like the conversation devolves into two opposing opinions:

1. Database administrators who understand DB theory, have managed terrabyte servers, and know what a real database looks like. This group hates MySQL.
2. People who used MySQL to implement a tiny pet project successfully. This group loves MySQL.

People in the latter group don't understand why anyone would dislike it - after all, their home-written blog software renders DB-backed pages in less than five seconds.

People in the former group can't imagine why anyone would put up with its many, many shortcomings when other faster, more capable, more Free databases are widely available. They don't understand why some people wouldn't want to use the best tool for the job when there's no legitimate reason in the world not to.

One of the big ones for me currently is that the query optimizer only uses one index in queries. I know you can have multi-column indexes, but I still see this being a problem for some of my more complex queries. Does PostgreSQL do this better?

I'm migrating my companies data from an old FoxPro setup to PostgreSQL. I don't have the option of normalizing the data (it would break too much legacy code, although I might look into making backward-compatible views sometime down the road), but selective indexing on columns (and functions on columns!) made 20-table joins work astoundingly well. Only one index per query? That would be completely and utterly unusable here. Yeah, PostgreSQL does that better.

Re:Almost caught up to MSSQL!

[orabidoo]

well, I did do a large project with MSSQL, and while it didn't crash or fail spectacularly the way other Microsoft products tend to do, it did have a few issues with locking.

specifically, it had an overly complicated strategy of automatically escalating types of locks (row-level, page-level, table-level, etc), the end result of which was that you never quite knew what was going to happen. I did have a rather fun bunch of hours tracking down transaction deadlocks that should not really have ocurred with a better engine.

the result of it all was that it made me realize how much better MVCC databases (which are able to hold more than one version of a record at a time, and show each client the appropriate version of the universe) are than the ones based on simple locking and exclusive access. on a non-MVCC database, an open transaction which has modified a row will freeze any other client that attempts to read it! imagine how happy your users are when all their front-ends stop working just because one user's computer crashed at the wrong moment.

AFAIK, all the major open source transactional db engines are MVCC: PostgreSQL, MySQL+InnoDB and Firebird are (dunno about SapDB, Ingres and the various Java engines).

in the proprietary world, Oracle does MVCC, but Sybase and DB2 don't. apparently the next version of MSSQL will have some sort of MVCC support too.

btw, all this talk of database independence ("it's all SQL dialects anyway") is an oversimplification in the real world. MVCC or not is actually a big deal in how a database application is engineered. as soon as you want to do anything sightlycomplicated in your transactions, and maintain integrity in the face of multiple clients, you have to think hard about locking, and start using things like "SELECT ... FOR UPDATE". at that point, the code you write will depend heavily on whether your database is MVCC or not.