RE: [EXTERNAL] Re: Inserts and bad performance

Excellent idea David, silly me, I didn't think of that. For the other questions:
>How many partitions?
14
>How many rows do they have when performance is slowing considerably?
Not sure, maybe on the low millions
>Does this table get many updates or is it insert only?
insert
>What version of PostgreSQL?
13
>Are the inserts randomly distributed among the partitions or targeting one or a few partitions?
Sequentially one partition at a time, so each set of runs is inserting across each part.
>Are you able to capture an example and run it in a transaction with explain (analyze, buffers, verbose) and then rollback?
Yes, I'm looking into that
pg

-----Original Message-----
From: David Rowley <dgrowleyml(at)gmail(dot)com>
Sent: Wednesday, November 24, 2021 7:13 PM
To: Godfrin, Philippe E <Philippe(dot)Godfrin(at)nov(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: [EXTERNAL] Re: Inserts and bad performance

On Thu, 25 Nov 2021 at 08:59, Godfrin, Philippe E <Philippe(dot)Godfrin(at)nov(dot)com> wrote:
> Hi Tom. Good point about the index paging out of the buffer. I did that and no change. I do have the shared buffers at 40GB, so there’s a good bit there, but I also did all those things on the page you referred, except for using copy. At this point the data has not been scrubbed, so I’m trapping data errors and duplicates. I am curios though, as sidebar, why copy is considered faster than inserts. I was unable to get COPY faster than around 25K inserts a second (pretty fast anyway). Frankly, initially I was running 3 concurrent insert jobs and getting 90K ins/sec ! but after a certain number of records, the speed just dropped off.

EXPLAIN (ANALYZE, BUFFERS) works with INSERTs. You just need to be aware that using ANALYZE will perform the actual insert too. So you might want to use BEGIN; and ROLLBACK; if it's not data that you want to keep.

SET track_io_timing = on; might help you too.

David