PostgreSQL, its community, license, and process model. (was:Re: MySQL Version 4)

[Lamar Owen]

On Tuesday 16 March 2004 03:14 pm, Wade Chandler wrote:
> I like PostgreSQL as far as it's simplicity and things go.  It's nice,
> and there are some good front ends for it.  The one complaint I have
> with Postgres is that it forks.

> MySQL and Firebird use threads and Postgres forks.  Forking is ok,
> unless you have many database connections.  The more connections the
> more processes.  I noticed while profiling an application that every
> connection alone was taking over 1MB of memory.  This based on the
> process per connection gripe I have.

You need to check and see how much of that is shared memory.  As far as 
forking performance goes, well, there isn't that much difference under Linux 
between thread creation time and forking time.  If connection pools are used, 
it's a non-issue.  

I use AOLserver for all of my database-backed websites (I maintain the 
nspostgres driver that AOLserver uses, in fact), and it has the best 
connection pooling support of anything I have tried.  AOLserver, 
incidentally, IS multithreaded, which makes quite a bit of sense from a 
webserver standpoint.  But threads are not the cure-all for performance, 
since the highest performing webservers are not multithreaded (at least, the 
last time I looked that was the case; but I reserve the right to be wrong).  
AOLserver is sweet due to the tight integration between webserver and 
database and the integrated tcl API that is available for database work.  The 
fact that OpenACS (my CMS/community/weblog/toolkit of choice) runs on 
AOLserver, and the fact that dotLRN (educational institution oriented 
intranet software) runs on top of OpenACS, also influences my decision to 
stick with AOLserver for intranet dynamic websites (my public website, 
unfortunately, is outsourced, otherwise it would be on OpenACS).  OpenACS 
supports either Oracle or PostgreSQL, but will never support MySQL (but it 
could support Firebird, since Firebird is a real ACID RDBMS, unlike MySQL).

> So, Postgres, sure I like it, but as far as a major DBMS goes, I think
> it is limited by it's memory usage.  That's just my opinion on the
> matter.

You need to check the details more thoroughly before forming such an opinion.

> However, it is a fact that it forks (forking takes more time 
> and more resources than threading).  One benefit in forking is the same
> reason Apache forks( memory leaks can be minimized).  However, I think
> if a DBMS has that bad of a memory leak....I won't use it.

The other benefit of forking is process memory isolation, which is desirable 
in an RDBMS that takes the I in ACID seriously.  For those elements that must 
be shared, SysV IPC shared memory is used by PostgreSQL, for locks and so 
forth.  Threaded applications must go the other way around, since all process 
memory is share and you must invert your logic and wrap things that need 
isolation in mutexes or the like.  These sorts of isolation issues might even 
cause a threaded PostgreSQL backend to be poorer performing than the current 
shmem implementation due to overlocking for isolation's sake.  Having said 
that, there has been an effort to make PostgreSQL multithreaded, primarily 
for the Win32 native port, where threads are a big win since Win32 doesn't 
have a lightweight fork.  It has proven very difficult to do this, and the 
performance wins have not been as great as expected in initial testing.

> I like to advocate Firebird as much as possible.  It runs on many
> platforms and seems to be pretty scalable as far as connections and
> usage goes, and it has a very flexible license as well.

And I like to advocate PostgreSQL as much as possible. :-) AFAIK, there is no 
more fexible license than the BSD one, which is the license PostgreSQL uses.  
The only thing more flexible is public domain (which is not a license), and 
that doesn't have any indemnification possibilities.

The fact that PostgreSQL's community is the model of a meritocratic oligarchy 
(core steering committee plus a large hackers community) with no central 
authority of any kind makes it more desirable from my standpoint than a 
johnny-come-lately open source project which is just a reincarnated piece of 
commercial software.  Good quality commercial software, yes, but still closed 
source commercial software.  

I do know the firebird community is changing and growing and becoming more 
open every day; but I also know the maturity level of the PostgreSQL 
community is the highest I have seen in any open source community, probably 
in consequence to it having been around nearly a decade now (the Postgres 
developers at Berkeley handed the then Postgres 4.2 code over to a pair of 
grad students, who SQL-ified it and released Postgres95 in, well, 1995, and 
then afterward the community was formed and renamed it to PostgreSQL since 
1995 had passed: the first message in the archives is from January1, 1997, 
but that's not the first post, just the first archived post).   I started 
using PostgreSQL in 1997 myself, with version 6.1, and put it in production 
on a Red Hat 5.0 system with PostgreSQL 6.2.1 and AOLserver 2.2.1 (which was 
not open source at the time, and even today the AOLserver community is not as 
vibrant as the much older PostgreSQL community due, I believe, to its 
commercial roots). There are of course exceptions, but for the most part the 
PostgreSQL lists have very few flames and very high signal to noise.

There is choice for high performance open source ACID-compliant enterprise 
databases: PostgreSQL and Firebird both qualify.  Choice is good.

It remains to be seen how open MaxDB will indeed be; the SAP/DB base has never 
been really open in the community sense of the word (then again, MySQL isn't 
very open either with MySQL AB basically calling all the shots, but base 
MySQL is not enterprise-quality either (see the MySQL gotchas page for some 
of the reasons why: http://sql-info.de/mysql/gotchas.html)).  If MaxDB 
becomes really open, then there will be three really open enterprise level 
RDBMS's for use to choose from.  Of course, there may be others that I am not 
aware of; I reserve the right to be wrong.

I personally believe in PostgreSQL so much I joined the community and have 
been the community RPM maintainer since summer of 1999.  When you install the 
Fedora Core PostgreSQL RPM's, you are using work that my hands have touched.  
Likewise, when you get a bug in the packaging, it's likely that it is my 
fault. :-)
-- 
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC  28772
(828)862-5554
www.pari.edu