idea for a geographically distributed database: how best to implement?
От | Andy Ballingall |
---|---|
Тема | idea for a geographically distributed database: how best to implement? |
Дата | |
Msg-id | ECOWS05MEyBjB7mRBmc0001088c@smtp-out5.blueyonder.co.uk обсуждение исходный текст |
Список | pgsql-sql |
<div class="Section1"><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew"">Hello,</span></font><p class="MsoNormal" style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New""> </span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew"">I’ve got a database for a website which is a variant of the ‘show stuffnear to me’ sort of thing.</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal" style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">Ratherthan host this database on a single server, I have a scheme in mind to break the database up geographically sothat each one can run comfortably on a small server, but I’m not sure about the best way of implementing it.</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">Here’s the scheme:</span></font><p class="MsoNormal"style="text-autospace:none"><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">--------------------------------</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New"size="2"><span style="font-size:10.0pt;font-family:"Courier New"">Imagine that the country is split into an array ofsquare cells.</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew"">Each cell contains a database that stores information about people wholive in the area covered by the cell.</span></font><p class="MsoNormal" style="text-autospace:none"><font face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal" style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">There’sone problem with this scheme. What happens if you live near the edge of a cell?</span></font><p class="MsoNormal"style="text-autospace:none"><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">My solution is that any inserted datawhich lies near to the edge of cell A is *also* inserted in the database of the relevant neighbouring cell – let’s saycell B.</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">Thus, if someone lives in cell B, butclose to the border with cell A, they’ll see the data that is geographically close to them, even if it lies in cell A.</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">--------------------------------</span></font><pclass="MsoNormal" style="text-autospace:none"><font face="Courier New"size="2"><span style="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal" style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">Isthis a common pattern?</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal" style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">I could,of course, simply find every insert, update and delete in the application and alter the code to explicitly update allthe relevant databases, but is there a more elegant way of simply saying: “Do this transaction on both Database A andDatabase B” monotonically?</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal" style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">I’vehad a look at some replication solutions, but they all seem to involve replicating an entire database. The advantageof my scheme is that if I can distribute my application over large numbers of small servers, I’ll end up with morebangs for the buck, and it’ll be much easier to manage growth by managing the number of servers, and number of cellshosted on each server.</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New""> </span></font><p class="MsoNormal" style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"">Thanksfor any suggestions!</span></font><p class="MsoNormal" style="text-autospace:none"><font face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">Andy Ballingall</span></font><p class="MsoNormal" style="text-autospace:none"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New""> </span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">'pgsql-sql@postgresql.org'</span></font></div>
В списке pgsql-sql по дате отправления: