Subject: Re: Linear slow-down while inserting into a table with an ON INSERT
 trigger ?
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: pgsql-performance@postgresql.org
References: <62070f97-13e7-df73-bed1-f8e48c12bf2d@code-sourcery.de>
 <20210717044026.GA19498@telsasoft.com>
From: Tobias Gierke <tobias.gierke@code-sourcery.de>
Message-ID: <b5b020e9-ad28-a309-12e6-5f6d01218b86@code-sourcery.de>
Date: Sun, 18 Jul 2021 09:36:33 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.12.0
MIME-Version: 1.0
In-Reply-To: <20210717044026.GA19498@telsasoft.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/b5b020e9-ad28-a309-12e6-5f6d01218b86%40code-sourcery.de>
Precedence: bulk

Thank you for the detailed explanation ! Just one more question: I've 
did an experiment and reduced the fillfactor on the table updated by the 
trigger to 50%, hoping  the HOT feature would kick in and each 
subsequent INSERT would clean up the "HOT chain" of the previous INSERT 
... but execution times did not change at all compared to 100% 
fillfactor, why is this ? Does the HOT feature only work if a different 
backend accesses the table concurrently ?

Thanks,
Tobias

> On Fri, Jul 16, 2021 at 11:27:24PM +0200, Tobias Gierke wrote:
>> CREATE OR REPLACE FUNCTION parent_table_changed() RETURNS trigger LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>>      UPDATE data_sync SET last_parent_table_change=CURRENT_TIMESTAMP;
>>      RETURN NEW;
>> END;
>> $function$
>>
>> I'm trying to insert 30k rows (inside a single transaction) into the parent
> The problem is because you're doing 30k updates of data_sync within a txn.
> Ideally it starts with 1 tuple in 1 page but every row updated requires
> scanning the previous N rows, which haven't been vacuumed (and cannot).
> Update is essentially delete+insert, and the table will grow with each update
> until the txn ends and it's vacuumed.
>
>          pages: 176 removed, 1 remain, 0 skipped due to pins, 0 skipped frozen
>          tuples: 40000 removed, 1 remain, 0 are dead but not yet removable, oldest xmin: 2027
>
> 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 prune the
> old entries and vacuum it outside the transaction.  Maybe someone else will
> have a better suggestion.
>