RES: Any way to speed up INSERT INTO

De: Andres Freund<mailto:andres(at)anarazel(dot)de>
Enviado:sexta-feira, 4 de março de 2022 15:52
Para: pgsql-performance(at)lists(dot)postgresql(dot)org<mailto:pgsql-performance(at)lists(dot)postgresql(dot)org>; Tom Lane<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>; aditya desai<mailto:admad123(at)gmail(dot)com>
Cc:Pgsql Performance<mailto:pgsql-performance(at)lists(dot)postgresql(dot)org>
Assunto: Re: Any way to speed up INSERT INTO

Hi,

On March 4, 2022 10:42:39 AM PST, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>aditya desai <admad123(at)gmail(dot)com> writes:
>> One of the service layer app is inserting Millions of records in a table
>> but one row at a time. Although COPY is the fastest way to import a file in
>> a table. Application has a requirement of processing a row and inserting it
>> into a table. Is there any way this INSERT can be tuned by increasing
>> parameters? It is taking almost 10 hours for just 2.2 million rows in a
>> table. Table does not have any indexes or triggers.
>
>Using a prepared statement for the INSERT would help a little bit.
>What would help more, if you don't expect any insertion failures,
>is to group multiple inserts per transaction (ie put BEGIN ... COMMIT
>around each batch of 100 or 1000 or so insertions). There's not
>going to be any magic bullet that lets you get away without changing
>the app, though.
>
>It's quite possible that network round trip costs are a big chunk of your
>problem, in which case physically grouping multiple rows into each INSERT
>command (... or COPY ...) is the only way to fix it. But I'd start with
>trying to reduce the transaction commit overhead.

Pipelining could also help.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Sorry for disturbing – I had similar problem with storing logs for e-commerce service mesh producing millions of records per day; to not loose anything, I do record every log records in Apache ActiveMQ Artemis, and then another microservice collects data from MQ and store in PostgreSQL. Since we have logs in waves, ActiveMQ Artemis reduces the “impedance” between systems.
Just my 2c.

Regards,

ER.