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

Is there any of this that is not already in the 8.2 docs?
http://developer.postgresql.org/pgdocs/postgres/high-availability.html

---------------------------------------------------------------------------

Timasmith wrote:
> "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.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +