From: Tom Lane <tgl@sss.pgh.pa.us>
To: aditya desai <admad123@gmail.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Any way to speed up INSERT INTO
Date: Fri, 04 Mar 2022 13:42:39 -0500
Message-ID: <3967140.1646419359@sss.pgh.pa.us> (raw)
In-Reply-To: <CAN0SRDHkd=3j7_xu2G_mg0ku0uwdN+DDGxEn8gzO=9hb=UFtcw@mail.gmail.com>
References: <CAN0SRDHkd=3j7_xu2G_mg0ku0uwdN+DDGxEn8gzO=9hb=UFtcw@mail.gmail.com>
aditya desai <admad123@gmail.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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, admad123@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Any way to speed up INSERT INTO
In-Reply-To: <3967140.1646419359@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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