Re: What's size of your PostgreSQL Database?

On Sat, 2008-08-16 at 11:42 +0800, Amber 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:
> 1. What's size of your database?
> 2. What Operating System are you using?
> 3. What level is your RAID array?
> 4. How many cores and memory does your server have?
> 5. What about your performance of join operations?
> 6. What about your performance of load operations?
> 7. How many concurrent readers of your database, and what's the average transfer rate, suppose all readers are doing one table scaning.
> 8. Single instance or a cluster, what cluster software are you using if you have a cluster?
>
> Thank you in advance!

1. 2.5-3TB, several others that are of fractional sisize.

...

5. They do pretty well, actually. Our aggregate fact tables regularly
join to metadata tables and we have an average query return time of
10-30s. We do make some usage of denormalized mviews for
chained/hierarchical metadata tables.

6. Load/copy operations are extremely performant. We pretty well
constantly have 10+ concurrent load operations going with 2-3
aggregation processes.

7. About 50, but I'm not sure what the transfer rate is.

8. We have a master and a replica. We have plans to move to a
cluster/grid Soon(TM). It's not an emergency and Postgres can easily
handle and scale to a 3TB database on reasonable hardware (<$30k).

A few notes: our database really can be broken into a very typical ETL
database: medium/high input (write) volume with low latency access
required. I can provide a developer's view of what is necessary to keep
a database of this size running, but I'm under no illusion that it's
actually a "large" database.

I'd go into more details, but I'd hate to be rambling. If anyone's
actually interested about any specific parts, feel free to ask. :)

Also, if you feel that we're doing "something wrong", feel free to
comment there too. :)

-Mark