Hi John -- > The clear choice from these responses is Postgres because of > its internal > strength over MySql
I've used both MySQL and PgSQL. I've also used Oracle, Sybase, DB2, MS SQL Server, and Informix. I've also been developing web apps for quite a long time, so I feel my opinions carry *some* weight. That being said, my preference is still MySQL. First off, if I want/need Oracle functionality, I'll just use Oracle. When you compare PgSQL to Oracle on "internal strength", it's a no-brainer. Nobody is going to argue that PgSQL is better than Oracle. In fact, PgSQL isn't better than any of the other so-called "ACID" databases I mention above. And some of those databases are free (or very nearly so) for small sites. The biggest criticism that I've heard leveled at MySQL is that "...it's not ACID. It's more like a file system with a SQL interface." After 10 years developing web/database apps, I've discovered that 80% of the time, that is exactly what you need. Web applications are software, but they are very notably different from client-server software. One of the most significant differences is that the web is stateless. This means that some advanced features, such as transactions and cursors are not generally applicable. If I was writing a client-server application I would have a stateful connection, through which I could use a cursor to step through results, or hold a transaction open. That is not the case on the web. On the web, when a page is rendered, the application stack is effectively terminated. That's not to say you *couldn't* devise some scheme to simulate statefulness. You simply would not *want* to. On the web, every request has to contain all the information needed to fully articulate a transaction. That means, you need a FAST database with the tools necessary to get the job done. That, in a nutshell, is MySQL. Features such as "offset/limit" (which were practically invented by MySQL, which are not standard SQL, which don't exist in Oracle, and only exist in PgSQL because they were so damned useful) are a classic example of why MySQL is the most popular database in the whole world for web applications. It is the right tool for the job. Same with the "auto increment" columns. A feature which didn't exist in Oracle-like databases, but was a practical solution which made life that much more easy. There are dozens of other examples like these (such as the MySQL interactive shell, which beats the pants off of sqlplus, or mysqldump which annihilates pg_dump). The theme here is that MySQL was created to be three things: 1. Simple 2. Reliable 3. Fast Let's not forget that the "P" in Perl stands for "Practical". PgSQL was created as an academic exercise: "Can we write our own Oracle?" If I wanted to be "academically correct", I'd be programming in Java. I don't, and I'm not. And, BTW: Nearly all those advanced, "academically correct" features which people point to when pimping PgSQL (row-level locking, stored procs, transactions, triggers, ref. integrity checking, clustering, etc.) are available for MySQL right now, or are slated to be available in the next release. However, PgSQL is still slow, hard to use, and of questionable reliability. > Also, Postgres is much more Oracle-like in syntax than > MySql, so if we should ever graduate to big-daddy-Oracle, we > can do that > easier. I'd like to shoot this idea down pretty quick: The idea that you might "graduate" to Oracle. I'd like to point to an article in InfoWorld just two weeks ago: http://weblog.infoworld.com/article/05/11/17/47FEmainmigrate_1.html The article is about migrating from a mainframe computer. I direct your attention to page two: "Sabre Pushes the Limits. Sabre Holdings -- parent of the Travelocity online consumer booking service and the Sabre travel reservations and ticketing system, which handles about 40 percent of worldwide travel reservations -- is in the midst of one of the largest mainframe migrations. Todd Richmond, the company's vice president of enterprise architecture, says Sabre has the world's third-largest implementation of IBM TPF (Transaction Processing Facility) mainframes. In an effort that began almost six years ago, however, Sabre has migrated most of its domestic booking services to four-way, Intel (Profile, Products, Articles) Itanium-based HP (Profile, Products, Articles) NonStop servers and a cluster of HP Integrity Itanium-based servers running 64-bit Red Hat (Profile, Products, Articles) Linux and the MySQL database." The article goes on to describe what they're doing with MySQL: "...Sabre will continue to use NonStop servers for database transactions because they are able to process the 14,000 transactions per second more reliably across large data sets typical of Sabre's environment" So, there you have it: 14,000 transactions per SECOND, handling 40% of the worldwide travel reservations... And all running on MySQL. So, it is possible to run a HUGE enterprise using MySQL -- no graduation necessary: Just good application architecture. Warmest regards, -Jesse- -- Jesse Erlbaum The Erlbaum Group [EMAIL PROTECTED] Phone: 212-684-6161 Fax: 212-684-6226
