Обсуждение: Large insert and delete batches

Hello all,

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

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

Little can be assumed about the schema or the environment. The inserts
and deletes are going to be done in one transaction. Foreign keys are
indexed and they are DEFERRABLE INITIALLY DEFERRED by default.
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,
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
to do the inserts and deletes in just one batch? I think the case for
performance problems needs to be strong before default limits are
considered for PostgreSQL.

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

  - Anssi Kääriäinen

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

I did a little test about this. My test was to see if there is any interesting difference
in performance between doing queries in small batches vs doing them in one go.

The test setup is simple: one table with an integer primary key containing a million rows.
The queries are "select * from the_table where id = ANY(ARRAY[list_of_numbers])"
and the similar delete, too.

For any sane amount of numbers in the list, the result is that doing the queries in smaller
batches might be a little faster, but nothing conclusive found. However, once you go into
millions of items in the list, the query will OOM my Postgres server. With million items
in the list the process uses around 700MB of memory, 2 million items is 1.4GB, and beyond
that it is an OOM condition. The problem seems to be the array which takes all the memory.
So, you can OOM the server by doing "SELECT ARRAY[large_enough_list_of_numbers]".

Conclusion: as long as you are not doing anything really stupid it seems that there isn't any important
performance reasons to split the bulk queries into smaller batches.

For inserts the conclusion is similar. A lot of memory is used if you go to the millions of items range,
but otherwise it seems it doesn't matter if you do many smaller batches versus one larger batch.

 - Anssi

On Thu, Mar 1, 2012 at 21:06, Kääriäinen Anssi <anssi.kaariainen@thl.fi> wrote:
> The queries are "select * from the_table where id = ANY(ARRAY[list_of_numbers])"
> and the similar delete, too.

> [...] However, once you go into
> millions of items in the list, the query will OOM my Postgres server.

The problem with IN() and ARRAY[] is that the whole list of numbers
has to be parsed by the SQL syntax parser, which has significant
memory and CPU overhead (it has to accept arbitrary expressions in the
list). But there's a shortcut around the parser: you can pass in the
list as an array literal string, e.g:
select * from the_table where id = ANY('{1,2,3,4,5}')

The SQL parser considers the value one long string and passes it to
the array input function, which is a much simpler routine. This should
scale up much better.

Even better if you could pass in the array as a query parameter, so
the SQL parser doesn't even see the long string -- but I think you
have to jump through some hoops to do that in psycopg2.

Regards,
Marti

On 03/01/2012 10:51 PM, Marti Raudsepp wrote:
> The problem with IN() and ARRAY[] is that the whole list of numbers
> has to be parsed by the SQL syntax parser, which has significant
> memory and CPU overhead (it has to accept arbitrary expressions in the
> list). But there's a shortcut around the parser: you can pass in the
> list as an array literal string, e.g:
> select * from the_table where id = ANY('{1,2,3,4,5}')
OK, that explains the memory usage.
> The SQL parser considers the value one long string and passes it to
> the array input function, which is a much simpler routine. This should
> scale up much better.
>
> Even better if you could pass in the array as a query parameter, so
> the SQL parser doesn't even see the long string -- but I think you
> have to jump through some hoops to do that in psycopg2.
Luckily there is no need to do any tricks. The question I was trying to
seek answer for was should there be some default batch size for inserts
and deletes in Django, and the answer seems clear: the problems appear
only when the batch sizes are enormous, so there doesn't seem to be a
reason to have default limits. Actually, the batch sizes are so large
that it is likely the Python process will OOM before you can trigger
problems in the DB.

  - Anssi