From: codeWarrior <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: idea for a geographically distributed database: how best to implement?
Date: Thu, 17 Nov 2005 08:00:43 -0800
Message-ID: <dli9a4$1eg5$1@news.hub.org> (raw)
References: <ECOWS04Mp8nkfZyufzT00010fe4@smtp-out4.blueyonder.co.uk>
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 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
>
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com
Subject: Re: idea for a geographically distributed database: how best to implement?
In-Reply-To: <dli9a4$1eg5$1@news.hub.org>
* 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