A little PostgreSQL advocacy (was:Re: [Centos] centos4/rhes4 - mysql4?)

[Lamar Owen]

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......
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu