Databases — psql cheat sheet

Meta-commands

Command	Does
\l	List databases
\c <db>	Connect to another database
\dt / \dt+	List tables (with sizes)
\d <table>	Describe a table
\dn	List schemas
\du	List roles
\x auto	Expanded output for wide rows
\timing on	Show query times
\i file.sql	Run a file
\copy t TO 'out.csv' CSV HEADER	Export to CSV on your machine
\q	Quit

Useful queries

-- What's running now
SELECT pid, state, now() - query_start AS age, left(query, 80)
FROM pg_stat_activity WHERE datname = current_database() ORDER BY age DESC;

-- Biggest tables
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;

-- Slowest queries (pg_stat_statements is enabled on every database)
SELECT calls, round(mean_exec_time) AS ms, left(query, 80)
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20;

Safe habits

• Use the read-only port for exploring.
• Wrap manual changes in a transaction: BEGIN; … ; ROLLBACK; until you're sure, then COMMIT;.
• Schema changes belong in your app's migrations, not ad-hoc psql.