On Tue, 2008-08-19 at 02:28 -0400, David Wilson wrote:
> On Fri, Aug 15, 2008 at 11:42 PM, Amber <guxiaobo1982@hotmail.com> 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.
