Re: [HACKERS] database replication

>
> Interesting idea. I can see how this might sync up two databases
> somehow. For true replication, however, I would always want every
> replicated database to be, at the very least, internally consistent
> (i.e., referential integrity), even if it was a little behind on
> processing transactions. In this method, its not clear how
> consistency is every achieved/guaranteed at any point in time if the
> input stream of changes is continuous. If the input stream ceased,
> then I can see how this approach might eventually catch up and totally
> resync everything, but it looks *very* computationally expensive.
>

What's the typical unit of work for the database? Are we talking about
update transactions which span the entire DB? Or are we talking about
updating maybe 1% or less of the database everyday? I'd think it would be
more towards the latter than the former. So, yes, this process would be
computationally expensive but how many records would actually have to be
sent back and forth?

> But I might have missed something. How would internal consistency be
> maintained?
>

Updates that occur at site A will be moved to site B and vice versa.
Consistency would be maintained. The only problem that I can see right off
the bat would be what if site A and site B made changes to a row and then
site C was brought into the picture? Which one wins?

Someone *has* to win when it comes to this type of thing. You really
DON'T want to start merging row changes...

>
> My two cents is that, while I can see this kind of database syncing as
> valuable, this is not the kind of "replication" I had in mind. This
> may already possible by simply copying the database. What replication
> means to me is a live, continuously streaming sequence of updates from
> one database to another where the replicated database is always
> internally consistent, available for read-only queries, and never "too
> far" out of sync with the source/primary database.
>

Sounds like you're talking about distributed transactions to me. That's
an entirely different subject all-together. What you describe can be done
by copying a database...but as you say, this would only work in a read-only
situation.

Damond