On Wednesday 11 October 2006 1:56 pm, you wrote:
> First, thanks for all the feedback.   After spending some more time
> evaluating what we would gain by using slony I'm not sure it's worth
> it.  However I thought I would get some more feedback before
> finalizing that decision.
>
> The primary reason for looking at replication was to move cpu
> intensive SELECT queries to a slave.  However, by moving away from
> schema's the report queries for all clients on the server become more
> cpu intensive instead of just the clients with large data sets.  The
> average distribution is that 95% of our clients have less then 5000
> rows in any table, and the other 5% can have hundreds of thousands.
> So by putting all the data into one schema, every report query now
> gets run against a million or more rows instead of just a few  hundred
> or thousand.  So all clients will see a drop in query performance
> instead of just the clients with large amounts of data.
>
> Chris
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general

PREFACE: I'm sending this directly to you because Slony's mailing list won't 
allow any of my posts, for some reason.

Here's my thoughts, after having used Slony for the past several years:

First, we have everything in the public schema, with our main table containing 
100 million lines with several thousand searches per day on it: each query 
runs for about 10ms, with a cost of around 9 to 10.

The logging table, which contains 1.5 million lines, and which the admins 
constantly run queries on to see what's happening: average query takes 2-3ms, 
with a cost of anywhere from 10 to 170.  

Most of the tables we have are several hundred thousand rows (unless they're 
"descriptive" tables).  To get optimal results, we had to do a lot of 
tweaking with statistics on the various tables, along with some massaging of 
the settings in postgresql.conf.   

We also have several customized scripts, all running off the main slave 
database server: this database has special indexes to make all the searches 
faster.  

My point is that you probably could merge everything together into one schema 
and benefit from the power of Slony.  I would guess it's going to take some 
time, lots of work, and a hell of a lot of patience.  In our case, the 
transition to using slony was pretty simple, as the only thing we really had 
to do was add some primary keys to some tables that lacked them. If you've 
got the time & patience, I think using Slony is definitely worth it, if for 
no other reason than to have the "hot backup" database.

-- 
Best Regards,


Dan Falconer
"Head Geek",
AvSupport, Inc. (http://www.partslogistics.com)