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
1 2 3 4 |
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:
1 |
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.