Postgres Pro
Downloads
Home   >   mailing lists

RE: [EXTERNAL] Re: Inserts and bad performance - Mailing list pgsql-general

From Godfrin, Philippe E
Subject RE: [EXTERNAL] Re: Inserts and bad performance
Date
Msg-id SA0PR15MB39338BE786CAFAF3AE3A2FD882659@SA0PR15MB3933.namprd15.prod.outlook.com
Whole thread Raw
In response to Re: Inserts and bad performance  (Gavin Roy <gavinr@aweber.com>)
List pgsql-general
Tree view

Hi Gavin – thanks I hadn’t realized that about psychopg. I’m on the earlier version, so I can’t use what you recommended at this point. But I did use copy_expert.

 

Interestingly enough the performance of the copy statement is only slightly better than the insert, as I was running inserts with 5000 values clauses. In the end, the current config couldn’t keep up with the WAL creation, so I turned all that off. But still no perf gains. I also turned off fsync and set the kernel settings to 10% and 98% for dirty pages…

 

I wonder if there’s a better load product than COPY???? But I’d still like to know what separates COPY from bulk inserts…

pf

 

From: Gavin Roy <gavinr@aweber.com>
Sent: Wednesday, November 24, 2021 1:50 PM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>
Cc: pgsql-general@lists.postgresql.org
Subject: [EXTERNAL] Re: Inserts and bad performance

 

 

 

 

On Wed, Nov 24, 2021 at 2:15 PM Godfrin, Philippe E <Philippe.Godfrin@nov.com> wrote:

Greetings

I am inserting a large number of rows, 5,10, 15 million. The python code commits every 5000 inserts. The table has partitioned children.

 

On the Python client side, if you're using psycopg, you should consider using using COPY instead of INSERT if you're not:

 

 

And if using psycopg2, execute_batch might be of value:

 

 

Regards,

 

Gavin

 

 

pgsql-general by date:

Previous
From: Ron
Date:
Subject: Re: Match 2 words and more
Next
From: "Godfrin, Philippe E"
Date:
Subject: RE: [EXTERNAL] Re: Inserts and bad performance