Back to All Cheatsheet Libraries cheatsheets

PostgreSQL

psql and SQL command reference, roles and core concepts, and a slow-query diagnosis workflow using EXPLAIN ANALYZE.

Total Commands: 0

Category	Command	Description
Connect	`psql -U user -h host -d dbname`	Opens an interactive session against a database.
psql Meta	`\l`	Lists all databases on the server.
psql Meta	`\dt`	Lists tables in the current schema.
psql Meta	`\d tablename`	Describes a table's columns, types, indexes, and constraints.
psql Meta	`\c dbname`	Switches the current session to a different database.
Query	`SELECT * FROM users WHERE active = true;`	Basic filtered read.
Query	`SELECT * FROM orders o JOIN users u ON o.user_id = u.id;`	Joins two tables on a matching key column.
JSON	`SELECT data->>'name' FROM events;`	Extracts a JSON field as text from a jsonb column — native JSON querying is one of Postgres's standout features.
Index	`CREATE INDEX idx_email ON users(email);`	Adds a B-tree index to speed up lookups on that column.
Diagnose	`EXPLAIN ANALYZE SELECT ...;`	Shows the actual execution plan and real timing — more informative than plain EXPLAIN's estimate-only output.
Maintenance	`VACUUM ANALYZE;`	Reclaims dead-row space and refreshes planner statistics — Postgres's MVCC model needs this run regularly.
Backup	`pg_dump dbname > backup.sql`	Exports a database to a plain-SQL (or custom-format with -Fc) dump file.
Restore	`psql dbname < backup.sql`	Restores a plain-SQL dump into a database.

Roles & Privileges

Statement	Purpose
`CREATE ROLE app WITH LOGIN PASSWORD 'pw';`	Creates a role that can log in — Postgres unifies "users" and "groups" under one role concept.
`GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO app;`	Grants specific privileges across every table in a schema.
`\du`	psql meta-command listing roles and their attributes.
`ALTER ROLE app WITH PASSWORD 'newpw';`	Changes a role's password.

Core Concepts

Schema

Namespace within a database

MVCC

Multi-version concurrencyWhy VACUUM exists

Extensions

CREATE EXTENSION postgis

Replication

Streaming, logical

EXPLAIN ANALYZE, not just EXPLAIN

ANALYZE actually runs the query and reports real timing per step — a plan can look reasonable and still be slow in practice, or vice versa.

Check autovacuum is keeping up

A table with heavy churn and stale planner statistics can silently degrade — pg_stat_user_tables shows last autovacuum/analyze time per table.

Index the right columns, including partial/expression indexes

Postgres supports indexing an expression or a filtered subset of rows (WHERE active) — often a better fit than a plain full-column index.

Enable pg_stat_statements for ongoing visibility

Tracks every query's total time and call count across the whole database — turns "something feels slow" into a ranked list of actual offenders.

Quick Tips

jsonb over json for anything queried

jsonb stores a parsed binary form and supports indexing (GIN) — plain json is just text with validation, re-parsed on every read.

Long-idle transactions block VACUUM

An open transaction (even an idle one left by a forgotten BEGIN) prevents Postgres from reclaiming dead rows it needs to keep visible to it — a common cause of table bloat.

Custom-format dumps (-Fc) enable selective restore

pg_restore against a -Fc dump can restore just one table or run in parallel — a plain SQL dump can only be replayed sequentially in full.