I quite often get asked to have a look at what’s happening in a PostgreSQL database. So I'll execute something like “SELECT * FROM pg_stat_activity;” once every minute or so to get a quick feel of what’s going on. This will display a list of the current connections to the database along with various other bits of useful information that I use to get an idea of what’s going on in there.
Then I discovered \watch.
With \watch, I can issue the same query appended like this: “SELECT * FROM pg_stat_activity; \watch”, to simply repeat the associated SQL statement once every two seconds. Now I can sit back and watch what’s happening without having to keep reissuing the command manually.
This leaves me more time to look at other things without having to keep bringing the terminal to the foreground, reissue the SQL command, note the values, and then go back to another task.
Every two seconds may be good for this query, or it may be overkill for other queries. No problem. That's why \watch has a delay value you can add like “\watch 60”, which repeats the SQL command every 60 seconds.
If you are interested in learning more about \watch and how it may improve your PostgreSQL database, please contact us directly. Fujitsu provides 24/7 Australian-based PostgreSQL support and services, DBA and developer training, and our own enhanced version of PostgreSQL - FUJITSU Enterprise Postgres.