Re: Triggers and scalability in high transaction tables.

On Thu, Feb 26, 2015 at 3:54 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
> I want to write a trigger which runs semi-complicated code after each
> insert. I have done some reading and from what I can gather this could
> cause problems because after insert triggers "don't spill to the disk" and
> can cause queue problems. Many people suggest LISTEN NOTIFY but that's not
> going to help me because my daemons could be offline and I would lose
> records.
>
> I have two questions.
>
> There are some hints out there that it could be possible to do asynchronous
> triggers based on dblink but I haven't seen any documentation or examples of
> this. Is there a writeup someplace about this?
>
> Secondly I had the idea of "partitioning" the trigger processing by
> partitioning the table and then putting a trigger on each child table. This
> way theoretically I could be running the triggers in parallel. Is my
> presumption correct here? If I only have one table the trigger calls get
> queued up one at a time but if I partition my table into N tables I am
> running N triggers simultaneously?

I would strongly advise you not to put complex processing in triggers
if at all possible. Instead have the insert operation write a record
into another table which forms a queue of work to do. That queue can
then be walked by another process which accumulates the work and takes
appropriate action (and, since you are out of the context of the
operation at hand, can be threaded etc).

merlin