Running lots of postgres commands in parallel

Postgres is great, however one limitation is that you can only run one command at a time in the shell. Sometimes however when you are doing administrative functions over multiple tables, for example (re)creating indexes or vacuuming and you have a nice powerful box, you can run many of these commands in parallel for easy speedup. Here’s an easy way to run lots of commands in parallel.

Firstly, create a text file with one command per line. For example

vacuum full a;
vacuum full b;
vacuum full c;
vacuum full d;

Then, ensure that you have your .pgpass file set up correctly so that you can just run psql [database] [user] without being prompted for a password.

Finally, run the following command:

xargs -d "\n" -n 1 -P 20 psql database_name username -c < list_of_commands.txt

-P 20 specifies the number of jobs to run in parallel so change this to what your server can cope with.

Leave a Reply

Your email address will not be published. Required fields are marked *