On Tue, 2008-08-19 at 02:28 -0400, David Wilson wrote: > On Fri, Aug 15, 2008 at 11:42 PM, Amber <guxiaobo1982@xxxxxxxxxxx> wrote: > > Dear all: > > We are currently considering using PostgreSQL to host a read only warehouse, > we would like to get some experiences, best practices and performance metrics from the > user community, following is the question list: I didn't realise the initial questions from this and since I'm lazy to look for the original mail, I'll put in my 2 cents worth. DB is a DSS type store instead of OLTP type. Heavily denormalised data. Master is a celeron 1.7Ghz, 768MB ram, 2x500GB 7200rpm IDE RAID1(data)+ 1 spare, 1x80GB (system). Slave is a celeron 1.7Ghz, 1.5GB RAM, 3x160GB 7200rpm IDE RAID1(data), 1x160GB system Max columns ~120 DB size is ~200+GB ~600+M (denormalised) rows in ~60+ tables (partitioned and otherwise) vacuum is done nightly in addition to turning on autovacuum. I'm both IO and CPU constrainted. :-) Denormalisation/ETL process is done on the master and only the final product is shipped to the slave for read-only via slony. I've got close to 8 indexes on each table (for bitmap scanning) Due to the denormalisation, gettin to the data is very snappy even based on such a "small" server. (adding ram to the slave saw drastic performance improvement over the initial 512MB) Currently looking for an FOSS implementation of a Slice and Dice kind of drilldown for reporting purposes. Tried a variety including pentaho, but never been able to get it set-up.
