I've been thinking some more about the long-standing problem of the AFTER TRIGGER queue using too much memory, and I think that the situation can be improved by using some basic compression.
Currently each event added to the AFTER TRIGGER queue uses 10 bytes per trigger per row for INSERTs and DELETEs, and 16 for UPDATEs. The attached patch reduces this down to just 1 byte in a number of common cases. It works by optimising for the case where an event's CTID is same or close to the previous event's CTID, and similarly for the event's pointer to the shared trigger data (AfterTriggerSharedData). If there is only 1 trigger on the table, it will use 1 byte for most INSERTed rows. For UPDATEs and DELETEs, the space needed will vary according to the table's layout and traversal order. Given a not too fragmented table, and a sequential or bitmap heap scan, it should use 1-2 bytes per row. This will increase for more random traversal orders - the theoretical maximum is 7 bytes per row, but that would take a pretty pathological case. If there are multiple triggers firing for each row, then each additional trigger event will take 1 byte, provided the triggers fire unconditionally (no WHERE clauses). If the triggers fire more randomly, due to WHERE clauses, then some of the trigger events may take 2 bytes each (for up to 256 triggers on the table) or 3 bytes each if there are more triggers than that. The compression code is pretty simple, so should only use a few additional cycles. In my testing so far, it seems to make no detectable difference to performance (actually it seems to be 2-3% faster for a 50M row INSERT, presumably because the reduced memory usage leads to fewer cache misses). For comparison, saving a 1MB AfterTriggerEventChunk produced by the current code to a file and compressing it with gzip gives a compression ratio of around 4:1, and bzip2 gives around 8:1. I've not tried the pg_lzcompress code, but at first glance it is hard to imagine that it would be as good as gzip, and it is not so suited to on-the-fly compression/decompression. It still needs more testing, but the initial results are encouraging. Thoughts? Regards, Dean
after-triggers.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
