Re: Compressing the AFTER TRIGGER queue

On 1 August 2011 21:02, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> I would prefer an approach where we store the events in a buffer,
> which gets added to the main event queue when it fills/block number
> changes/etc. That way we can apply intelligence to the actual
> compression format used, yet retain all required info in the queued
> event. We might also be able to ensure the event queue is ordered more
> naturally by block number, to ensure we have reasonable readahead
> while re-traversing the event queue - its not just the size of the
> event queue that is the problem here.
>

I tried a similar approach before
(http://archives.postgresql.org/pgsql-hackers/2009-10/msg00464.php)
but one of the problems was that, for user-defined triggers at least,
it was thought that the order of firing for the triggers needed to
match the row update order, which is difficult to achieve with a
bitmap.

I also worry about the runtime cost of anything too complicated. What
I was aiming for with this patch was a more modest compression that
wouldn't add too many additional cycles (or complexity) to the
existing code (I need to do more testing to confirm that that is
indeed the case).

The biggest recent complaint I found in this area was this thread:
http://archives.postgresql.org/pgsql-bugs/2010-12/msg00002.php, which
was a one billion row insert. Personally, if I ever insert that much
data I always drop and re-create all the constraints and triggers, but
it would be nice if we could achieve that sort of size on reasonably
modern hardware.

> I'm also nervous of compression formats that rely too heavily on
> earlier data, so any corner case bug in one of the formats will screw
> the whole data structure. I'd prefer a more modular approach that is
> easier to test in regression tests.
>

Nearly all compression formats rely on earlier data. In this case each
event only depends on the previous event, so it just needs testing
with various classes of difference between successive rows, perhaps
with some C test code to make up CTIDs testing the corner cases.

> The cases we have problems with are the bulk cases, so we should be
> specifically optimising for that. For example, if we are running a
> COPY/INSERT SELECT or a seqscan DELETE then we will queue a trigger
> for every tuple in a block.

Agreed. That's what I tried to target with my code too - so it
achieves its best compression with sequential data.

The best compression and flexibility in
> that case is to store a bitmap since that will average out at about 1
> bit per row, with variable length bitmaps. Which is about 8 times
> better compression ratio than originally suggested, without any loss
> of metadata.

Yeah that's probably possible in specific cases, but I'm still not
sure how to make it meet the full requirements of the after trigger
queue.

Regards,
Dean

>
> We should also optimise the HOT UPDATE case.
>
> --
>  Simon Riggs                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>