Re: Future In-Core Replication

On Sat, Apr 28, 2012 at 8:40 PM, Hannu Krosing <hannu(at)krosing(dot)net> wrote:

> As to what LCRs should contain, it will probably be locical equivalents
> of INSERT, UPDATE ... LIMIT 1, DELETE ... LIMIT 1, TRUNCATE and all DDL.

Yeh

> I would even go as far as propose a variant for DML-WITH-LIMIT-1 to be
> added to postgresql's SQL syntax so that the LCRs could be converted to
> SQL text for some tasks and thus should be easy to process using generic
> text-based tools.
> The DML-WITH-LIMIT-1 is required to do single logical updates on tables
> with non-unique rows.
> And as for any logical updates we will have huge performance problem
> when doing UPDATE or DELETE on large table with no indexes, but
> fortunately this problem is on slave, not master ;)

While that is possible, I would favour the do-nothing approach. By
making the default replication mode = none, we then require a PK to be
assigned before allowing replication mode = on for a table. Trying to
replicate tables without PKs is a problem that can wait basically.

> Generating and shipping the LCR-s at WAL-generation time or perhaps even
> a bit earlier will have a huge performance benefit of not doing double
> writing of captured events on the master which currently is needed for
> several reasons, the main one being the determining of which
> transactions do commit and in what order. (this cant be solved on master
> without a local event log table as we dont have commit/rollback
> triggers)
>
> If we delegate that part out of the master then this alone enables us to
> be almost as fast as WAL based replica in most cases, even when we have
> different logical structure on slaves.

Agreed

>> How the LCRs are produced and how they are applied is a subject for
>> debate and measurement. We're lucky enough to have a variety of
>> mechanisms to compare, Slony 1.0/2.0, Slony 2.2/Londiste/Bucardo and
>> its worth adding WAL translation there also. My initial thought is
>> that WAL translation has many positive aspects to it and we are
>> investigating. There are also some variants on those themes, such as
>> the one you discussed above.
>>
>> You probably won't recognise this as such, but I hope that people
>> might see that I'm hoping to build Slony 3.0, Londiste++ etc. At some
>> point, we'll all say "thats not Slony", but we'll also say (Josh
>> already did) "thats not binary replication". But it will be the
>> descendant of all.
>
> If we get efficient and flexible logical change event generation on the
> master, then I'm sure the current trigger-based logical replication
> providers will switch (for full replication) or at least add and extra
> LCR-source . It may still make sense to leave some flexibility to the
> master side, so the some decisions - possibly even complex ones - could
> be made when generating the LCR-s
>
> What I would like is to have some of it exposed to userspace via
> function which could be used by developers to push their own LCRs.

Yes, I see that one coming.

That use case is not something I'm focused on, but I do recognise
others wish to pursue that.

The bit I'm not sure about is whether we have custom handler code as well.

> As metioned above, significant part of this approach can be prototyped
> from user-level triggers as soon as we have triggers on commit and
> rollback , even though at a slightly reduced performance. That is it
> will still have the trigger overhead, but we can omit all the extra
> writing and then re-reading and event-table management on the master.

Agreed.

> Wanting to play with Streaming Logical Replication (as opposed to
> current Chunked Logical Replication) is also one of the reasons that I
> complained when the "command triggers" patch was kicked out from 9.2.

Yeh. It's clear that project needs to move forwards quickly in 9.3 if
we are to make this Just Work in the way we hope.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services