Re: temp table on commit delete rows performance issue

Hi Floris,

> On Jul 16, 2024, at 19:47, Floris Van Nee <florisvannee(at)Optiver(dot)com> wrote:
>
> Hi hackers,
>
> I'm looking for some input on an issue I've observed. A common pattern
> I've seen is using temporary tables to put data in before updating the
> real tables. Something roughly like:
>
> On session start:
> CREATE TEMP TABLE temp_t1 (...) ON COMMIT DELETE ROWS;
>
> On update:
> BEGIN;
> COPY temp_t1 FROM STDIN (FORMAT BINARY);
> INSERT INTO t1 (SELECT * FROM temp_t1 ...) ON CONFLICT DO UPDATE SET ...;
> -- potentially some other operations on temp table to put data into real table t1
> COMMIT;
>
> This pattern starts to break down under certain exceptional circumstances of
> high concurrency. The "ON COMMIT DELETE ROWS" does a truncate that is
> fairly expensive and doesn't work well in high-concurrency scenarios. It's
> especially noticeable under following circumstances:
> - high max_connections setting
> - high number of temp tables per session
> - concurrent writers at fairly short intervals
> Impact is on both TPS on primary as well as that the WAL replay process
> on replica becomes completely overloaded (100% cpu even though not
> a lot of WAL is being generated)
>
> A very simple pgbench example that showcases degradation (taken
> with max_connections=2000 to clearly show it).

I also encountered the similar performance issue with temporary tables
andprovided a patch to optimize the truncate performance during commit
in [1].

Additionally, is it possible to lower the lock level held during truncate for
temporary tables?

[1] https://www.postgresql.org/message-id/flat/tencent_924E990F0493010E2C8404A5D677C70C9707%40qq.com

Best Regards,
Fei Changhong