Large PostgreSQL databases

Dear Shridhar,

> One of my friends is evaluating postgres for large databases. This is a select 
> intensive application which is something similar to data-warehousing as far as 
> I can see.
> 
> The data is 150GB in flat files so would swell to 200GB+ with indexes.
> 
> Is anybody running that kind of site? Any url? Any performance numbers/tuning 
> tips for random selects?

I work for Bristol-Myers Squibb in their Bioinformatics department,
and I have about 300GB in PostgreSQL databases for DNA sequence
analysis.  Some of my tables are approaching 100 million rows. You
have to watch and adjust how PostgreSQL plans queries in order to get
good application performance.

> 
> I would hate to put mysql there but we are evaluating that too. I would hate if 
> postgres loses this to mysql because I didn't know few things about postgres.
> 
> Secondly would it make a difference if I host that database on say, an HP-UX 
> box? From some tests I have done for my job, single CPU HP-UX box trounces 4 
> way xeon box. Any suggestions in this directions?

We use an SGI Origin 3000 with Fibre Channel RAID. However, an SGI Origin 2000
works well too, and those systems are available cheaply on the used market.
Lots of RAM helps performance -- we run with big buffer caches.

--Bob

+-----------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
| P.O. Box 314                | URL:   http://www.congen.com/~bruc |
| Pennington, NJ 08534        |                                    |
+-----------------------------+------------------------------------+