Amit,
> - We have lot of foreign keys between the tables
Do you need these keys to be enforced? Last I checked, MySQL was still
having trouble with foriegn keys.
> - Most of the DB usage is Selects. We would have some inserts but that
> would be like a nightly or a monthly process
So transaction integrity is not a real concern? This sounds like a data
warehouse; wanna try Bizgres? (www.bizgres.org)
> Our only concern with going with postgres is speed. I haven't done a speed
> test yet so I can't speak. But the major concern is that the selects and
> inserts are going to be much much slower on postgres than on mysql. I dont
> know how true this is. I know this is a postgres forum so everyone will say
> postgres is better but I am just looking for some help and advise I guess
Well, the relative speed depends on what you're doing. You want slow, try a
transaction rollback on a large InnoDB table ;-) PostgreSQL/Bizgres will
also be implementing bitmapped indexes and table partitioning very soon, so
we're liable to pull way ahead of MySQL on very large databases.
> I am not trying to start a mysql vs postgres war so please dont
> misunderstand me .... I tried to look around for mysql vs postgres
> articles, but most of them said mysql is better in speed.
Also I'll bet most of those articles were based on either website use or
single-threaded simple-sql tests. Not a read data warehousing situatiion.
It's been my personal experience that MySQL does not scale well beyond about
75GB without extensive support from MySQL AB. PostgreSQL more easily scales
up to 200GB, and to as much as 1TB with tuning expertise.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
