Re: Merging Data from Multiple DB

You could also say you can have 2^N databases and 2^(63-N) records in
each database, and use a BIGSERIAL with the N higher bits pointing to the
DB number, and the 63-N lower bits being the actual serial... faster than
strings for indexing, and you init the serial to start at the first value
for this database, and to stop at the last value for this database... just
another way to generate GUID's...

On Mon, 03 Jan 2005 07:20:54 -0800, Joe Conway <mail(at)joeconway(dot)com> wrote:

> anon permutation wrote:
>> For performance reasons, each branch must has its own database and a
>> centralized transactional system is not an option.
>> I was considering just centralizing primary keys generation, but that
>> seems very slow too.
>> Segmenting primary keys among the branches is doable, but it is too
>> much of a maintainence nightmare.
>> What do you suggest?
>
> We have a similar application. What we did is this:
>
> 1. Each database instance is assigned a unique identifier, stored in a 1
> row, 1 column table (with a trigger to ensure it stays that way).
>
> 2. Write a function that can take two integers, convert them to text,
> and concatenate them. In our case we convert to hex and concatenate with
> a delimiter character.
>
> 3. Write another function, called something like 'nextrowid', that takes
> a sequence name as its argument. Use the sequence name to get the next
> value from the sequence, lookup the local unique identifier from the
> table defined in #1, and pass them both to the function defined in #2.
>
> 4. Use nextrowid('seq_name') to generate your primary keys.
>
> HTH,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>