Reindex multiple indexes in postgresql in one query

Question

I can reindex indexes one by one like this:

database=# REINDEX INDEX CONCURRENTLY indexname1;
...
database=# REINDEX INDEX CONCURRENTLY indexname2;
...

How could I run reindex of multiple indexes in one query?

Something like this:

database=# REINDEX INDEX CONCURRENTLY indexname1 indexname2;
ERROR:  syntax error at or near "indexname2"
LINE 1: REINDEX INDEX CONCURRENTLY indexname1 indexname2;
                                                    ^

Or this:

database=# REINDEX INDEX CONCURRENTLY indexname_*
ERROR:  syntax error at or near "*"
LINE 1: REINDEX INDEX CONCURRENTLY indexname_*
                                             ^

Answer 1

As per the documentation, REINDEX cannot be executed inside a transaction block when used with a partitioned table, schema, or database. This means that you cannot use a single transaction to REINDEX multiple items concurrently.

You can execute multiple REINDEX CONCURRENTLY commands in a single script, through a python interface with PostgreSQL with psycopg2.

import psycopg2

index_list = ['indexname1', 'indexname2']

conn = psycopg2.connect(database="your_db_name", user="your_username", password="your_password", host="your_host", port="your_port")

cur = conn.cursor()

for index in index_list:
    cur.execute(f"REINDEX INDEX CONCURRENTLY {index};")

conn.commit()

cur.close()
conn.close()

Answer 2

Finally I found this solution, although it is certainly not suitable in every case:

since my end goal was to perform REINDEX INDEX CONCURRENTLY indexname on all indexes in the database, the sum of all REINDEX INDEX ... comes down to running a single REINDEX DATABASE CONCURRENTLY dbname query.

Warning: use this only if you understand how many indexes there are in your database, what their size is, how long such REINDEX DATABASE might take to complete and how much additional load it might create on the server.