Re: postgresql roadmap for horizontal scalability? - Mailing list pgsql-hackers

"Mike Rylander" wrote:
> On 1 Dec 2006 09:43:57 -0800, Timasmith <timasmith@hotmail.com> wrote:
> > Hi,
> >
> > This database has exceeded all expectations and perhaps I am getting
> > ahead of myself but is there anything in the roadmap to match Oracles
> > ability to cluster databases across multiple servers for concurrent
> > access?
> >
>
> My preferred solution is Slony-1 + pgPool (more below).
>
> > The reason I ask is for the architecture of an open source database
> > project I am working on.  It is tempting to ignore caching altogether
> > and push the onus on to the database to perform more database queries.
> > This is makes life easier and allows the focus to be on the business
> > logic.
> >
> > However if Postgresql is the datababase and the only means to scale is
> > to buy a bigger single server then that could become a problem of cost
> > to the community.  It would be cheaper to scale across multiple smaller
> > servers (I think).
>
> It surely is, depending on your sync speed requirements.
>
> >
> > The application I am working on is OLTP with say 90% reads to writes
> > ratio.
> >
>
> That's very similar to the read/write ratio of my app.  Here's what we do:
>
> We've set up
>   1 master read/write DB
>   2 (soon to be 3) slave read-only DBs
>   cascading pgPool setup in "external replication" mode
>
> We spread the load over all machines using the built in pgPool
> weighting algorithm, giving the master machine about 1/6 of the read
> only traffic (it also gets all in-transaction traffic per the pgPool
> config) and spread the rest of the load over the other machines.
> Behind the cluster, taking care of keeping everyone in sync, is a
> simple Slony-1 setup.  It's fairly simple to get going once you wrap
> your head around it, and we've yet to see a single problem that we
> didn't cause by not reading the documentation.
>
> One thing to watch out for, though, is the replication lag.  We see
> anywhere from 0.5 to 3 seconds of lag on our setup, and we expect to
> start seeing a little more when we add the third slave machine.  The
> one sure way to counteract this is to always go to the master whenever
> you're building a page that changes any data.  Those should be few and
> far between, realistically, but will cause more than the expected load
> that the pgPool config would suggest.  Giving the vast majority of the
> read-only load to the slaves has mitigated the load issue for us, and
> I expect would do the same for you.
>
> Hope that helps. :)
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> >                http://archives.postgresql.org
> >
>
>
> --
> Mike Rylander
> mrylander@gmail.com
> GPLS -- PINES Development
> Database Developer
> http://open-ils.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

That is awesome.  I am not worried about the time to replicate - this
is supposed to be static, very infrequently changing data (like form
controls, security etc.)  so there is no expectation changes are real
time.

I am envisioning a powerful,.highly available central server and then a
slew of cheap servers.

It is not so much that I would not using caching at all but it does
open the door to focus on 'fat client caching' only - that reduces
introducing synchronized code (that may introduce errors) on the
application server.