On Fri, Oct 26, 2007 at 10:04:31AM -0400, Christopher Browne wrote:
> 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.

You mean piling together the PKs and doing a DELETE .. WHERE (pk,
fields) = ANY(...) ?

> 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.

That's a very interesting idea, and kinda orthogonal to the
INSERT/UPDATE/DELETE speedups above.  How big a change would it be?

Cheers,
D
-- 
David Fetter <david at fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter at gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate