On Friday 21 January 2005 21:28, Paul wrote:
On Fri, 2005-01-21 at 15:39 -0600, Benjamin J. Weiss wrote:
I'm beginning to wonder whether PostgresSQL wouldn't be the better solution...
I've thought it is ... the only reason to prefer MySQL in the past has been better read performance. By every other metric I've felt that PostgresSQL is a "better" database and have wondered why everybody was using MySQL for everything. The only reason I can think of is the "network effect" which is one of the reasons Windows is popular (everybody knows somebody that knows it).
While I am somewhat biased (google me and find out, or just read the PostgreSQL RPM changelog), I have watched PostgreSQL improve to the point that there remain very few compelling reasons to use MySQL.
IMO, they are: 1.) If your cache is big enough, the mysql backend can fit in the cache (but that's not the most used part of a database server; the kernel is, and it's likely to be in cache more);
2.) Existing applications that rely on MySQL nonstandard extensions or can't easily be modified to use some other backend (PostgreSQL's SQL standards compliance is closer, so if things are coded in an ANSI SQL compliant way it should be a low-level driver type change only, with the SQL itself not requiring much modification);
3.) You need high single-user read performance (PostgreSQL's multiuser read/write mix has higher performance on identical hardware under most situations, and where the advanced features of the backend can be used, performance can be dramatically increased by simply using the backend's features instead of having to issue two or more SQL queries);
4.) You just simply prefer MySQL (which is OK, you are certainly free to choose the wrong tool for the job if you want to).
PostgreSQL, being BSD licensed, doesn't and won't have the licensing gotcha that caused Red Hat to significantly delay release of MySQL 4.x RPMs. This gotcha is simply due to MySQL AB wanting to get more commercial licenses; their whole dual license approach is quite scary, and their insistence on them retaining copyright over additions and patches means that they could take future versions to a commercial license only. PostgreSQL's code has multiple copyrights and each developer would have to agree to a license change. OTOH, commercial versions can be produced and released easily due to the BSD license; Pervasive Postgres for instance, or SRA Powergres, or Command Prompt's Mammoth PostgreSQL, each of which is value-added and each developer of which contributes to the larger community.
PostgreSQL commercial support is available from several different companies, including database heavyweight Pervasive (the guys behind mainframe Btrieve), PostgreSQL Inc, Command Prompt Inc, Software Research Associates (SRA), amongst others. PostgreSQL is not owned by a single company (unlike MySQL) and has an extensive track record spanning nearly 20 years.
Without extensive evaluation of the latest and greatest of each database you cannot categorically say that you just use the best tool for the job; you need to determine the best tool empricially, and increasingly MySQL is no longer the best tool for many jobs that it once was.
Also, MySQL's data integrity is not that great; see www.sql-info.de for some comparisons, gotchas, and explanations of the difference in the design philosophies of the two most popular open source databases. Firebird is coming on strong to be number three, though. The biggest problem I see with MySQL is the 'quick and dirty' attitude; that is, don't throw errors or warnings since they take time, just do what the client said even if it violates data integrity.
PostgreSQL has won the Linux Journal Editors Choice awards for I think three years running for best database. MySQL has won the Readers choice for about the same time.
MySQL's biggest competition on the low end is shaping up to be sqlite, which is extremely fast and lightweight. PostgreSQL's competition is Oracle; as each release occurs, PostgreSQL gains more enterprise class features and make it competitive in the space that DB2, Oracle, and Sybase play.
And with version 8.0 Windows is natively supported (and works great on Win2k3 at least, which I have tested).
PostgreSQL is the backend behind Affilias' .info and .org domain whois and registration databases.
PostgreSQL is so flexible that industrial strength replication can be made as a third party module; one of which is the Slony 1 replication engine which can replicate between differing versions of the backend. (Incidentally, the Slony name means 'elephants'; 'slon' is the singular, and the elephant is the PostgreSQL mascot....). By not forcing a particular integrated replication scheme, users can choose the right replication tool for the job, whether they need master slave, connection pooling, HA, or soon even multi-master.
With the WxWindows-based multiplatform pgAdmin GUI, the phppgadmin Web gui, and easy to install RPMs, DEB's, and other packages, PostgreSQL is now as easy or even easier to administer than MySQL. The only sticky point is version upgrades, but even there PostgreSQL is as easy as Oracle or DB2.
Not only does PostgreSQL have stored procedures, but you have choice in language in which to write them. You can write them in pl/pgsql (almost like Oracle's pl/sql), tcl, perl, python, even R and bash. And you can develop new languages yourself, or write them in C for that matter.
You can write and integrate brand new datatypes and operators, even, that can be easily used from straight SQL queries; the PostGIS module does this for Geographical Information Systems objects, for instance.
PostgreSQL is extremely fexible, fast, reliable, meets ACID requirements, is well supported both by the community and by companies, and is incredibly extensible. There really is no better open source choice for 99.9% of applications. And the mindshare is coming......