← All cheatsheets

Databases

PostgreSQL — the psql + SQL commands that come up daily

The psql and SQL commands that come up daily: connect, inspect tables, add indexes, read EXPLAIN, spot slow queries, back up.

psql session

psql postgresql://user:pass@host:port/db — connect via URL

psql -h host -U user -d db — connect with flags

\q — quit

\? — psql help

\h — SQL help (\h SELECT)

Inspect

\l / \list — list databases

\c dbname — switch database

\dt / \dt schema.* — list tables

\d table — describe table

\dn — list schemas

\du — list users / roles

Indexes + EXPLAIN

\di — list indexes

EXPLAIN ANALYZE SELECT ... — show actual execution plan + timing

EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) ... — full detail

Performance peeks

SELECT * FROM pg_stat_activity; — current connections + queries

SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; — slowest queries (needs extension)

SELECT pg_size_pretty(pg_database_size('db')); — db size

SELECT pg_size_pretty(pg_total_relation_size('table')); — table size incl. indexes

Backup + restore

pg_dump -Fc dbname > db.dump — custom-format dump

pg_restore -d newdb db.dump — restore

pg_dump -Fc dbname | gzip > db.dump.gz — compressed