Forum OpenACS Q&A: Response to Current state of Postgres v. Oracle

Collapse
Posted by Don Baccus on
I'll take a shot:

1. PG is proving to be extremely scalable.  Tim Perdue's planning to
move SourceForge from MySQL to Postgres 7.1 based on performance under
load alone.  It kicked MySQL's butt when he tested with a simulated
load of several users, and throughput stayed steady on his target
server (a dual-processor linux machine with 256MB or 512MB RAM) up to
the 50 or so simultaneous user range, doing a mix of select, insert
and update queries.  MySQL crashed under his stress testing, PG just
chugged away.

That represents excellent scalability.

Note that insert/update performance of Postgres 7.1 will be very much
higher than in earlier versions, for reasons I won't go into here.

2. Stability improves with each release, this much is clear.  PG 7.0
stability seems very good to me, PG 7.1 should be even better.
However, the PG team still finds and fixes potentially nasty,
database-corrupting bugs.  They're becoming exceedingly rare and I
*think* with PG 7.0.3 we're out of the woods, but could be wrong.

I've run into nasty and disturbing Oracle bugs while working on client
sites (or, since I'm a Postgres fan, "encouraging and spirit-lifting
bugs", perhaps?).  But none that threaten corruption of the database -
Oracle's more mature in that respect.

On the other hand, I've been running birdnotes.net for over a year
now, and though it gets little traffic it has accumulated thousands of
census reports containing nearly a million rows of per-species data,
with no problem whatsoever.  The server stays up for months at a time.
Tcl 8x, with its regexp memory leaks, is a bigger threat to stability
than Postgres judging from my experience.

3. Pg_dump backups in version 7.0 require hand-massaging at times
before you can reload them, due to misordering of the definitions of
some elements leading to illegal forward references.  Other than
that, they're fine for databases whose dumps don't exceed the
compressed 2GB limit of a Linux file.  The problem should be fixed in
PG 7.1.

aD and I believe most others who use ACS Classic knock down AOLserver
nightly and do a database export rather than depend on the fancy
backup-and-restore tools available for Oracle.  So this supposed
advantage to Oracle is unused by the average ACS Classic admin.  The
same approach could be taken on a Postgres site if one doesn't trust
pg_dump for some reason: take down AOLserver, then Postgres, dump the
database directory to a backup device, bring everything back up.

Still, backup and recovery is a weakness in PG at the moment, one that
requires a bit of a stone-age mentality to cope with.

OK, now for the record, Michael has asked me this question privately,
and for his particular customer I've recommended they go with Oracle,
based in part on the his timeframe.  His needs would be better suited
by PG 7.1, but that's not due for general release until just about the
time Michael needs to fund work on the customer site.  ACS 4.0's a
potential issue, too.

For other some other scenarios I would heartily recommend PG 7.0, and
if PG 7.1 proves to be a solid as I expect, in six months I'll be
recommending PG 7.1 for just about any project I'm asked about,
particularly after OpenACS 4.0 is released (and ACS 4.0 Classic
matures, for that matter).