Re: What's size of your PostgreSQL Database?

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