pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19103: Canceled INSERT statement can still influence the performance of subsequent SELECT statement
4+ messages / 4 participants
[nested] [flat]
* BUG #19103: Canceled INSERT statement can still influence the performance of subsequent SELECT statement
@ 2025-11-04 12:08 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2025-11-04 12:08 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jinhui.lai@qq.com
The following bug has been logged on the website:
Bug reference: 19103
Logged by: Jinhui Lai
Email address: jinhui.lai@qq.com
PostgreSQL version: 18.0
Operating system: ubuntu 22.04
Description:
Dear PG developers:
Thanks for reading my report. Here, I find a performance issue. I have
found a performance issue where a canceled INSERT statement appears to
negatively impact the performance of subsequent SELECT queries.
Steps to Reproduce:
\timing on
CREATE TABLE t0(c0 INT8);
INSERT INTO t1 VALUES(1);
CREATE TABLE t1(c1 INT8);
INSERT INTO t1 SELECT * FROM generate_series(1, 100000000);
^CCancel request sent
ERROR: canceling statement due to user request
Time: 50685.063 ms (00:50.685)
SELECT COUNT(*) FROM t0;
count
-------
0
(1 row)
Time: 1.264 ms
SELECT COUNT(*) FROM t1;
count
-------
0
(1 row)
Time: 3466.933 ms (00:03.467)
This performance degradation could affect systems where large batch
operations are frequently started and canceled, potentially impacting
overall database responsiveness.
Thank you for investigating this issue.
Best regards,
Jinhui Lai
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19103: Canceled INSERT statement can still influence the performance of subsequent SELECT statement
@ 2025-11-04 13:26 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2025-11-04 13:26 UTC (permalink / raw)
To: jinhui.lai@qq.com <jinhui.lai@qq.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tuesday, November 4, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19103
> Logged by: Jinhui Lai
> Email address: jinhui.lai@qq.com
> PostgreSQL version: 18.0
> Operating system: ubuntu 22.04
> Description:
>
> Dear PG developers:
>
> Thanks for reading my report. Here, I find a performance issue. I have
> found a performance issue where a canceled INSERT statement appears to
> negatively impact the performance of subsequent SELECT queries.
This doesn’t feel like a bug. Processing deleted rows is expensive and
some of that work happens during selects if/when those dead rows are
encountered.
> This performance degradation could affect systems where large batch
> operations are frequently started and canceled, potentially impacting
> overall database responsiveness.
>
Temporary tables are nice for this kind of flow. Don’t touch production
tables until you know what you are going to insert is going to stick.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19103: Canceled INSERT statement can still influence the performance of subsequent SELECT statement
@ 2025-11-04 14:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-11-04 14:55 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: jinhui.lai@qq.com <jinhui.lai@qq.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, November 4, 2025, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> Thanks for reading my report. Here, I find a performance issue. I have
>> found a performance issue where a canceled INSERT statement appears to
>> negatively impact the performance of subsequent SELECT queries.
> This doesn’t feel like a bug.
It is not. The reason ROLLBACK is essentially free in Postgres
is that some of the ensuing cleanup work gets done during
subsequent table accesses. Some other DBMSes do that differently,
but that's our choice and we're not likely to revisit it.
> Temporary tables are nice for this kind of flow. Don’t touch production
> tables until you know what you are going to insert is going to stick.
Yeah. Frequently rolling back large data modifications is a
performance anti-pattern no matter what the underlying implementation
does.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19103: Canceled INSERT statement can still influence the performance of subsequent SELECT statement
@ 2025-11-07 15:51 Álvaro Herrera <alvherre@kurilemu.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Álvaro Herrera @ 2025-11-07 15:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; jinhui.lai@qq.com <jinhui.lai@qq.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On 2025-Nov-04, Tom Lane wrote:
> It is not. The reason ROLLBACK is essentially free in Postgres
> is that some of the ensuing cleanup work gets done during
> subsequent table accesses. Some other DBMSes do that differently,
> but that's our choice and we're not likely to revisit it.
At least, it's the choice we have made in heapam. It's possible to make
different choices for other table AMs -- for instance, I bet OrioleDB
has made a different choice here, because of its claim that vacuum is
not needed.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-11-07 15:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-04 12:08 BUG #19103: Canceled INSERT statement can still influence the performance of subsequent SELECT statement PG Bug reporting form <noreply@postgresql.org>
2025-11-04 13:26 ` David G. Johnston <david.g.johnston@gmail.com>
2025-11-04 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-11-07 15:51 ` Álvaro Herrera <alvherre@kurilemu.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox