postgresql roadmap for horizontal scalability?

Timasmith,

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?

There are several efforts to do *better than* RAC, which is actually not very
effective (RAC scales very poorly, and not at all for some applications).
These include pgPool, Skytools, pgCluster, Replicator, Postgres-R and
Sequoia.

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.

Yes, except even on a clustered system proper use of caching will *always* at
least double database peformance. There's simply no way to make an ACID
RDBMS anywhere near as fast as a simple thing like a cache.

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).

Yes, it would. However, scalable OLTP clustering is a problem not yet solved
by anyone in the industry. It may not be solvable at all.

The application I am working on is OLTP with say 90% reads to writes
ratio.

Hmmm ... that makes it more like web than OLTP. You may be able to use a
query replication system like pgPool or Sequoia.

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

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

"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.

Timasmith wrote:

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.

Be very sure that this is covered in the specifications or you may end
up with PHB types asking you why the changes aren't propagated in real
time.

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

If you intend to use the slony based approach above, then I suggest
that your cheap servers need to be reliable. In the event of a single
node's failure, cruft will begin accumulating on all the nodes until
that node is either dropped or brought back online and catches back up.

Drew

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. +

Am Freitag, 1. Dezember 2006 18:43 schrieb Timasmith:

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?

I have got some ideas on how to get there, but it would likely be a multi-year
effort to complete. In the meantime, there are other solutions available
which may work better or worse than what Oracle has.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Fri, 2006-12-01 at 09:43 -0800, Timasmith wrote:

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 experience of Oracle clustering is that database design is incredibly
difficult and requires very careful segregation of transactions to make
it work well. RAC is much better than Oracle Parallel Server (OPS) which
had such a (deservedly) bad reputation they had to change the name. Some
applications work well with it, others work much worse than a
make-one-large-SMP approach, but its fairly hard to be certain which it
will be for any particular app.

So, my take on clustering is that it will be a huge drain on development
resources to get it to work reasonably well (Oracle took 10 years and 3
major versions, note) and that other explicit data
duplication/replication techniques are almost as effective. They are
available now.

I think its clustering is do-able and will come, but not for a while
yet, methinks.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com