X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 47133DAA80 for ; Thu, 17 Nov 2005 05:46:12 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08350-10 for ; Thu, 17 Nov 2005 09:46:13 +0000 (GMT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by svr1.postgresql.org (Postfix) with ESMTP id 4D3D3DA5D5 for ; Thu, 17 Nov 2005 05:46:09 -0400 (AST) Received: from smtp-out5.blueyonder.co.uk (smtp-out5.blueyonder.co.uk [195.188.213.8]) by svr2.postgresql.org (Postfix) with ESMTP id C56B5F0B1E for ; Thu, 17 Nov 2005 09:46:10 +0000 (GMT) Received: from Eve ([82.43.185.102]) by smtp-out5.blueyonder.co.uk with Microsoft SMTPSVC(5.0.2195.6713); Thu, 17 Nov 2005 09:47:01 +0000 From: "Andy Ballingall" To: Subject: idea for a geographically distributed database: how best to implement? Date: Thu, 17 Nov 2005 09:46:22 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0000_01C5EB5B.C50FB760" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 thread-index: AcXrW8TkUX3mut7+QaS71Wsjud0Gig== Message-ID: X-OriginalArrivalTime: 17 Nov 2005 09:47:01.0335 (UTC) FILETIME=[DBDFB670:01C5EB5B] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200511/151 X-Sequence-Number: 23402 This is a multi-part message in MIME format. ------=_NextPart_000_0000_01C5EB5B.C50FB760 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 'pgsql-sql@postgresql.org' ------=_NextPart_000_0000_01C5EB5B.C50FB760 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

 

'pgsql-sql@postgresql.org'

------=_NextPart_000_0000_01C5EB5B.C50FB760--