What is the maximum in list size in PostgreSQL?

Yesterday, while being at a customer, an interesting question popped up: What is the maximum of in list values in PostgreSQL? I couldn’t answer although I never read somewhere that there is a limit. The following is for fun only and I am not saying that creating huge in lists is a good idea. Lets go.

The version I tested is PostgreSQL 10:

postgres=# select version(), now();
                                                  version                                                   |              now              
------------------------------------------------------------------------------------------------------------+-------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit | 2018-03-21 18:29:50.269369+01
(1 row)

postgres=# create table t1 ( a varchar(10));
CREATE TABLE

We will use a very simple bash script to build the in list and execute the result in PostgreSQL:

postgres@pgbox:/home/postgres/ [PG10] cat t.sh 
#!/bin/bash
count=$1

statement='select * from t1 where a in ('

for (( i=1; i<=$count; i++ ))
do  
    if [ "${i}" -lt "${count}" ]; then
        statement="${statement} '${i}',"
    elif [ "${i}" == "${count}" ]; then
        statement="${statement} '${i}');"
    fi
done

psql -c "${statement}" postgres

Lets start with 100:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 100
 a 
---
(0 rows)

Time: 0.983 ms

1000:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 1000
 a 
---
(0 rows)

Time: 1.525 ms

10000:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 10000
 a 
---
(0 rows)

Time: 11.549 ms

… takes a much longer (because of the bash script which fully occupies my virtual core):

./t.sh: line 15: /u01/app/postgres/product/10/db_0/bin/psql: Argument list too long

So there at least is a limit with psql. Lets try by creating a sql script and execute that instead:

postgres@pgbox:/home/postgres/ [PG10] cat t.sh 
#!/bin/bash
count=$1

echo 'select * from t1 where a in (' > ttt.sql

for (( i=1; i<=$count; i++ ))
do  
    if [ "${i}" -lt "${count}" ]; then
        echo  "'${i}'," >> ttt.sql
    elif [ "${i}" == "${count}" ]; then
        echo "'${i}');" >> ttt.sql
    fi
done

psql -f ttt.sql postgres

This way of stringing together the statement is much more efficient than building the list by concatenating everything into one variable. Does it still work?

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 100000
 a 
---
(0 rows)

Time: 155.986 ms

Not a problem, one more:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 1000000
 a 
---
(0 rows)

Time: 14211.766 ms (00:14.212)

Still works. So now we could say: lets stop, who in the world will pass one million values into an in list. On the other hand, lets have fun and double:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 2000000
 a 
---
(0 rows)

Time: 3993.091 ms (00:03.993)

One more:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 3000000
psql:ttt.sql:3000001: ERROR:  out of memory
DETAIL:  Failed on request of size 524288.
Time: 3026.925 ms (00:03.027)

Ok, now I am hitting some limits, but probably not those of PostgreSQL. I’ll test further when I have more time for that 🙂