From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Justin Pryzby <pryzby@telsasoft.com>,
        Tobias Gierke <tobias.gierke@code-sourcery.de>,
        pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Linear slow-down while inserting into a table with an ON INSERT
 trigger ?
In-reply-to: 
 <CAApHDvr-kFAmOihG1FxVZfgdV_e5TR8cU3n+QPkYwJ8XqmA4YQ@mail.gmail.com>
References: <62070f97-13e7-df73-bed1-f8e48c12bf2d@code-sourcery.de>
 <20210717044026.GA19498@telsasoft.com>
 <CAApHDvr-kFAmOihG1FxVZfgdV_e5TR8cU3n+QPkYwJ8XqmA4YQ@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Sat, 17 Jul 2021 19:02:31 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <62961.1626532389.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sat, 17 Jul 2021 10:33:09 -0400
Message-ID: <62962.1626532389@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/62962.1626532389%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Sat, 17 Jul 2021 at 16:40, Justin Pryzby <pryzby@telsasoft.com> wrote=
:
>> You could run a single UPDATE rather than 30k triggers.
>> Or switch to an INSERT on the table, with an index on it, and call
>> max(last_parent_table_change) from whatever needs to ingest it.  And pr=
une the
>> old entries and vacuum it outside the transaction.  Maybe someone else =
will
>> have a better suggestion.

> Maybe just change the UPDATE statement to:
> UPDATE data_sync SET last_parent_table_change=3DCURRENT_TIMESTAMP WHERE
> last_parent_table_change <> CURRENT_TIMESTAMP;
> That should reduce the number of actual updates to 1 per transaction.

Or, if it's impractical to make the application do that for itself,
this could be a job for suppress_redundant_updates_trigger().

https://www.postgresql.org/docs/current/functions-trigger.html

			regards, tom lane