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:
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.
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:
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.
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:
pgsqlphriday=# \timing on Timing is on. pgsqlphriday=# \timing on Timing is on. pgsqlphriday=# \timing off Timing is off.
The other psql feature I use often is \watch. This repeats the last query every so often:
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.
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.
Display comments as Linear | Threaded