pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: codeWarrior <gpatnude@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: idea for a geographically distributed database: how best
Date: Thu, 17 Nov 2005 19:36:17 +0200 (EET)
Message-ID: <Pine.LNX.4.44.0511171917200.8800-100000@matrix.gatewaynet.com> (raw)
In-Reply-To: <dli9a4$1eg5$1@news.hub.org>

O codeWarrior Ýãñáøå óôéò Nov 17, 2005 :

> Sounds like a mis-guided approach to me...
> 
> You might want to consider using latitude and longitude or zip codes or 
> taking more of a traditional "GIS" approach rather than duplicating data 
> across redundant databases.
> 
> Another issue is that you end up having to query every database to find 
> proximity... Suppose you have 500 "cells" ? You now have to invoke some sort 
> of RPC mechanism on 499 other RDBMS, manage 500 user names and passwords, 
> retrieve potentially 500 recordsets, merge the data, etc...
> 
> Your problems will continue to get more and more complex... You are better 
> off with a single RDBMS and a single schema...

Andy, i agree with what codeWarrior says.
But if you are interested in replication, dbmirror is very elegant
(altho not as trendy) simple, and highly customizable replication 
solution.

I have heavily modified dbmirror to even support
Asynchronous Conditional row grained 
Foreign key dependency Traversal oriented 
Lazy Replication!!!
(which ofcourse nobody wants :)

> 
> 
> 
> 
> "Andy Ballingall" <andy@areyoulocal.co.uk> wrote in message 
> news:ECOWS04Mp8nkfZyufzT00010fe4@smtp-out4.blueyonder.co.uk...
> > Hello,
> >
> > I've got a database for a website which is a variant of the 'show stuff 
> > near
> > to me' sort of thing.
> >
> > Rather than host this database on a single server, I have a scheme in mind
> > to break the database up geographically so that each one can run 
> > comfortably
> > on a small server, but I'm not sure about the best way of implementing it.
> >
> > Here's the scheme:
> >
> > --------------------------------
> > Imagine that the country is split into an array of square cells.
> > Each cell contains a database that stores information about people who 
> > live
> > in the area covered by the cell.
> >
> > There's one problem with this scheme. What happens if you live near the 
> > edge
> > of a cell?
> >
> > My solution is that any inserted data which lies near to the edge of cell 
> > A
> > is *also* inserted in the database of the relevant neighbouring cell - 
> > let's
> > say cell B.
> >
> > Thus, if someone lives in cell B, but close to the border with cell A,
> > they'll see the data that is geographically close to
> > them, even if it lies in cell A.
> >
> > --------------------------------
> >
> > Is this a common pattern?
> >
> > I could, of course, simply find every insert, update and delete in the
> > application and alter the code to explicitly update all the relevant
> > databases, but is there a more elegant way of simply saying: "Do this
> > transaction on both Database A and Database B" monotonically?
> >
> > I've had a look at some replication solutions, but they all seem to 
> > involve
> > replicating an entire database. The advantage of my scheme is that if I 
> > can
> > distribute my application over large numbers of small servers, I'll end up
> > with more bangs for the buck, and it'll be much easier to manage growth by
> > managing the number of servers, and number of cells hosted on each server.
> >
> > Thanks for any suggestions!
> > Andy Ballingall
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >               http://www.postgresql.org/docs/faq
> > 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
> 

-- 
-Achilleus




view thread (10+ messages)  latest in thread

Message-ID: <Pine.LNX.4.44.0511171917200.8800-100000@matrix.gatewaynet.com>
Permalink:  ../Pine.LNX.4.44.0511171917200.8800-100000@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.44.0511171917200.8800-100000@matrix.gatewaynet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: achill@matrix.gatewaynet.com, gpatnude@hotmail.com
  Subject: Re: idea for a geographically distributed database: how best
  In-Reply-To: <Pine.LNX.4.44.0511171917200.8800-100000@matrix.gatewaynet.com>
  References: <dli9a4$1eg5$1@news.hub.org> <Pine.LNX.4.44.0511171917200.8800-100000@matrix.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox