[wellylug] Storing Mail in an SQL database ? - Scaling of Postgresql

[Wood Brent]

> Well, I'm not convinced. See this fairly comprehensive comparison:
> 
> http://www.phpbuilder.com/columns/tim20000705.php3?page=1

Unfortunately, despite being a generally good review, both RDBMS systems are a
few versions up on those reviewed. MySQL is looking at transacations & spatial
datatypes, & PG has increased performance with each of 3 or versions since
then.
Also note that Firebird (the OS implementation of Borland's Interbase) has
entered the fray, with supposedly impressive performance specs.  

> 
> I've tried both and settled on PostgreSQL. It feels a lot more "grown
> up" than MySQL which I thought was fairly limited. I'm no database
> guru, but it's nice to have the security of transactions if you want
> then (though I've not often actually used them). I've never had a
> database corruption with either database, and I'd like a few more
> performance type tools with PostgreSQL, but I've never noticed it
> being slow.

I use PG for spatial data mgmnt & analysis. 50,000-100,000 records per table
are pretty standard, with some of 40 odd attrs & 2m-3m rows. I have built the
appropriate indexes & vacuum analysed the PG tables, but even on a dual 2.4Ghz
Xeon box with 2.5Gb memory under RH9, performance isn't blinding. I haven't
compared it with others tho, but it is a bit slower than I expected, but no
other issues. I figure a dual Opteron with 64 bit PG on 64 bit RH would help
performance a bit, but I'm not likely to get it :-)

For any half competent db implementation, there should be foreign keys, & there
should also be subqueries in the SQL (I use them frequently). The last version
of MySQL I looked at had neither, nor spatial datatype support, so I only had
one choice. 

Both are very reputable, MySQL meets most basic needs, but you may outgrow it.
PG is more complex, & you are not likely to outgrow it. Firebird I haven't
looked at (yet).

If you are looking at a commercial option, have a look at Empress, small &
fast, pretty tweakable, Linux versions available for some years, & it was rated
the no 1 Linux RDBMS a few years back in an RDBMS Advisor mag's review. I've
been using & occasionally administering it for about 15 yrs now.

I'm planning to have a play with Rekall sometime soon, now a free version is
available, it claims to be an Access like app builder for PG & MySQL as well as
DBx.


Cheers,

  Brent Wood