Simon Riggs <simon at 2ndquadrant.com> writes:
> On Thu, 2007-10-25 at 19:59 -0700, David Fetter wrote:
>
>> Does Slony-I 2-to-be use INSERT ... VALUES (...), ..., (...); to pass
>> INSERTs?  It seems to me this would be quicker than a flock of
>> single-tuple INSERTs, and since we're having flag day with 8.3, we're
>> guaranteed the feature is there. :)
>
> It's not easily possible to do this because each invocation of the log
> trigger has a different set of values and they can't see each others
> values.
>
> It could be possible to do this on the apply side by saving up data from
> log table row to row until we can issue a single mega-INSERT. But if we
> were going to do that, why not initiate a COPY, which would handle the
> bulk cases even better? The difficulty would be in deciding what the
> logic should be to invoke the special case SQL construction.
>
> Another thought: I notice that Slony writes out all of the column names
> for each insert e.g. (col1, col2, col3) VALUES (x, y, x)

I put together a rough proposal a while back as to handling this more
efficiently.

The idea would be to detect consecutive updates to the same table, and
rewrite INSERTs to use multiple VALUE subclauses, as well as to detect
multiple UPDATEs involving the same SET clause, and fold together the
WHERE clauses.  DELETE has an obvious optimization too.

It adds a fair bit of complexity, and proably won't help OLTP traffic
terribly much (where it is common to intersperse tables heavily).  I
don't think it's the "big Win," though.

> It would be straightforward to remove the (col1, col2, col3) text from
> each INSERT statement since that is optional. That would reduce the
> overhead of each INSERT row and reduce the parsing time on the other
> side also.

Very unsafe.  What if the subscriber decides to consider the columns
to be in a different order?  Do we need to go back and ask for the
"reordering columns" feature that periodically pops up?

I see a much bigger win in Jan's idea to use COPY to get sl_log_n data
to the subscriber As Fast As Parsing Allows, and then use rules on
sl_log_n to generate INSERT/UPDATE/DELETE requests on the subscriber
to do the work.  That would take a lot of the load off the provider,
and COPY seems likely to be way faster than other rewritings.

-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://cbbrowne.com/info/languages.html
Do not meddle in the affairs of dragons, for you are crunchy and taste
good with ketchup.