PGSqlPhriday #006: One Thing You Wish You Knew While Learning PostgreSQL: psql commands

For this month’s #PGSQLPhriday, Grant Fritchey asks: What is the one thing you wish you knew while you learn PostgreSQL.

My preferred client for PostgreSQL is psql, and while it is very powerful I like a few features a most:

• \timing
• \watch

Both are internal commands in psql, \timing is there for a very long time, but got improved at some point. \watch came later, but is also there for a couple years now.

\timing

With timing you get a client-side timer how long it takes to run a SQL command. This time includes the planning time, but also the time it takes to transfer back the result - so it might be slightly skewed if you run a very complicated query, or retrieve a very large result set. But in general it gives a very good idea for how long it takes to run a certain query:

1
2
3
4
5
6
7
8
9

postgres=# \c pgsqlphriday
You are now connected to database "pgsqlphriday" as user "ads".
pgsqlphriday=# CREATE TABLE pgsqlphriday (id SERIAL, data FLOAT8);
CREATE TABLE
pgsqlphriday=# \timing
Timing is on.
pgsqlphriday=# INSERT INTO pgsqlphriday (data) SELECT random() FROM generate_series(1, 10000000);
INSERT 0 10000000
Time: 9626,326 ms (00:09,626)

That is neat. If you us \timing multiple times, it toggles on and off.

1
2
3
4
5
6

pgsqlphriday=# \timing
Timing is off.
pgsqlphriday=# \timing
Timing is on.
pgsqlphriday=# \timing
Timing is off.

You can also use on and off to activate or deactivate the timing:

1
2
3
4
5
6

pgsqlphriday=# \timing on
Timing is on.
pgsqlphriday=# \timing on
Timing is on.
pgsqlphriday=# \timing off
Timing is off.

\watch

The other psql feature I use often is \watch. This repeats the last query every so often:

1
2
3
4

pgsqlphriday=# SELECT * FROM pg_locks WHERE NOT granted; \watch 5
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------+---------+----------
(0 rows)

When operating on live databases - as we all sometimes have to do - I usually have another terminal open and run above query. The terminal sits on another screen, and it tells me almost instantly if there are blocked queries (the granted flag being false). If by accident I run a query which blocks a production query, those queries show up in the terminal. The query is repeated by psql every 5 seconds, I just have to keep an eye on it.

Summary

psql has many other useful features and you should explore them. Check the manpage and scroll to “Metacommands”, it’s a long list. Maybe start with \gexec or \copy, or become familiar with all the \d variants. But \watch and \timing are the ones I use really often in daily work.