Most developers I work with use psql at the level of "connect, run a few queries, exit." That is fine, but psql has features that turn it into a serious productivity tool. A handful of meta-commands and shortcuts cover most of the value.
Here are the ones I reach for daily.
Connection: a tidy ~/.psqlrc
The single biggest productivity boost: a configured ~/.psqlrc.
-- ~/.psqlrc
\set QUIET 1
\set ON_ERROR_STOP on
\set HISTSIZE 5000
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\timing on
\x auto
\pset null '~'
\pset linestyle unicode
\pset border 2
\set QUIET 0
\set PROMPT1 '%[%033[1;33m%]%n%[%033[0m%]@%[%033[1;31m%]%/%[%033[0m%]%R%# '
What this does:
\timing on: every query shows execution time.\x auto: wide rows automatically display in expanded form (key:value per line).\pset null '~': NULL values display as~instead of empty (easier to spot).ON_ERROR_STOP: in scripts, abort on first error.HISTCONTROL ignoredups: deduplicate history.
Drop this file in your home directory; psql picks it up automatically.
\watch for live monitoring
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
\watch 5
Reruns the previous query every 5 seconds. Ctrl-C to stop. Useful for watching active queries during an incident, monitoring replication lag, or waiting for a long-running operation to finish.
Combine with conditional output:
SELECT now(), count(*) AS connections
FROM pg_stat_activity
WHERE state = 'active';
\watch 1
You get a per-second log of active connection count.
\copy for data movement
Server-side COPY requires file system access on the database server. \copy does it client-side, which works from any psql session:
\copy (SELECT * FROM orders WHERE created_at > '2025-01-01') TO 'recent_orders.csv' CSV HEADER
\copy orders FROM 'orders_to_import.csv' CSV HEADER
Much faster than INSERTs for bulk data. The client streams the data over the connection.
\e to edit the previous query
Typing a long query and hitting an error means starting over. Or it would, if \e did not exist:
psql=> SELECT * FROM orders WERE id = 1;
ERROR: syntax error...
psql=> \e
This opens the previous query in your $EDITOR. Fix the typo, save, the corrected query runs.
For longer queries, \ef function_name edits a function in your editor and replays it.
\d and friends
\d is the meta-command for description. Variations:
\d— list all relations.\d table_name— describe table structure.\d+ table_name— same but with size info.\dt— list tables only.\di— list indexes.\df— list functions.\dn— list schemas.\du— list roles.\dx— list extensions.
The + variant adds size info, comments, etc. The S variant includes system objects (\dS).
\? and \h
\? lists all psql meta-commands. \h SQL_COMMAND shows syntax help for SQL commands.
psql=> \h CREATE INDEX
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON ...
Faster than searching docs for syntax reminders.
\g and \gx for the previous query
\g re-executes the previous query. Useful after editing in your editor or after \watch.
\gx is \g with expanded display (\x). For when you accidentally ran a wide query and got the table-with-too-many-columns view.
\pset format csv for CSV output
For exporting query results without going through \copy:
psql=> \pset format csv
psql=> \o results.csv
psql=> SELECT * FROM orders WHERE created_at > '2025-01-01';
psql=> \o
psql=> \pset format aligned
\o filename redirects output to a file. \o (no argument) restores normal output.
EXPLAIN with shortcut aliases
In ~/.psqlrc:
\set ea 'EXPLAIN (ANALYZE, BUFFERS, VERBOSE)'
Now you can write:
:ea SELECT * FROM orders WHERE id = 1;
psql expands :ea to EXPLAIN (ANALYZE, BUFFERS, VERBOSE). Saves typing the same option set repeatedly.
Useful queries as functions in psqlrc
\set activity 'SELECT pid, now() - xact_start AS duration, state, query FROM pg_stat_activity WHERE state != \'idle\' ORDER BY xact_start;'
Now :activity runs the long-running-queries query without remembering the syntax.
Other useful ones to define:
\set locks 'SELECT * FROM pg_locks JOIN pg_stat_activity USING (pid) WHERE NOT granted;'
\set blocking 'SELECT b.pid AS blocked, a.pid AS blocking, b.query AS blocked_query FROM pg_stat_activity a, pg_stat_activity b WHERE a.pid = ANY(pg_blocking_pids(b.pid));'
\set replication 'SELECT * FROM pg_stat_replication;'
\set bloat 'SELECT relname, n_dead_tup, round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct FROM pg_stat_user_tables WHERE n_live_tup > 1000 ORDER BY n_dead_tup DESC LIMIT 20;'
During an incident, :activity is much faster to type than the full query.
\set AUTOCOMMIT off for safety
For write-heavy or DDL sessions:
\set AUTOCOMMIT off
Now every statement is implicitly inside a transaction that you must explicitly COMMIT or ROLLBACK. Slows you down a little but prevents "oops, I accidentally dropped the wrong table."
The keystroke that has saved me most
Ctrl-R for reverse history search. Like in bash. Type a few characters and psql shows the most recent matching query from history. Hit Ctrl-R again to cycle further back.
For frequently-used queries, this is faster than retyping or even using aliases.
What I have in my ~/.psqlrc
All of the above plus:
- Connection prompt that shows database and user (in case I am connected to multiple).
default_statistics_target = 1000for sessions where I am tuning statistics.- A few aliases for common diagnostic queries.
The whole file is maybe 50 lines. Pays for itself in the first hour of use.
Why this matters
psql is the lowest-friction interface to Postgres. The faster you can navigate it, the faster you diagnose problems, prototype queries, and run operations. The investment in tuning psql to your habits is small; the daily savings are real.