Re: [Q] Cluster design for geographically separated dbs

On Sat, Mar 7, 2009 at 2:03 PM, V S P <toreason(at)fastmail(dot)fm> wrote:

> And wanted to ask what would be the main challenges I am facing with --
> from the experience of the users on this list.
>
> Especially I am not sure how to for example manage 'overlapping unique
> IDs' data.

I'm not expert on a lot of what you're doing, but the standard trick
here is to partition your bigserials.

The max value for the underlying sequence is 9223372036854775807 which
should give you plenty of space to work in. So, When creating your
bigserials, you can then alter the sequence underneath them to use a
different range on each machine.

smarlowe=# create table a1 (id bigserial, info text);
NOTICE: CREATE TABLE will create implicit sequence "a1_id_seq" for
serial column "a1.id"
smarlowe=# create table a2 (id bigserial, info text);
NOTICE: CREATE TABLE will create implicit sequence "a2_id_seq" for
serial column "a2.id"
smarlowe=# alter sequence a1_id_seq minvalue maxvalue 19999999999
start 10000000000;
ALTER SEQUENCE
smarlowe=# alter sequence a2_id_seq minvalue 20000000000 maxvalue
29999999999 start 20000000000;
ALTER SEQUENCE

Now those two sequences can't run into each other, and if you move a
record from one machine to another it won't bump into what's already
there. Partitioning by 10billion gives you 922337203 possible
partitions, so if you need bigger but fewer partitions there's plenty
of wiggle room to play with.