Message-ID: <4F4DFBDF.5050901@thl.fi>
Date: Wed, 29 Feb 2012 12:20:15 +0200
From: =?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?= <anssi.kaariainen@thl.fi>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Large insert and delete batches
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

Hello all,

I am trying to help the Django project by investigating if there should=20
be some default batch size limits for insert and delete queries. This is=20
realted to a couple of tickets which deal with SQLite's inability to=20
deal with more than 1000 parameters in a single query. That backend=20
needs a limit anyways. It might be possible to implement default limits=20
for other backends at the same time if that seems necessary.

If I am not mistaken, there are no practical hard limits. So, the=20
question is if performance is expected to collapse at some point.

Little can be assumed about the schema or the environment. The inserts=20
and deletes are going to be done in one transaction. Foreign keys are=20
indexed and they are DEFERRABLE INITIALLY DEFERRED by default.=20
PostgreSQL version can be anything from 8.2 on.

The queries will be of form:
     insert into some_table(col1, col2) values (val1, val2), (val3,=20
val4), ...;
and
     delete from some_table where PK in (list_of_pk_values);

So, is there some common wisdom about the batch sizes? Or is it better=20
to do the inserts and deletes in just one batch? I think the case for=20
performance problems needs to be strong before default limits are=20
considered for PostgreSQL.

The tickets in question are:
https://code.djangoproject.com/ticket/17788 and=20
https://code.djangoproject.com/ticket/16426

  - Anssi K=E4=E4ri=E4inen