psql commands, SQL, indexing, and backup essentials.
A reference for working in PostgreSQL day to day — the `psql` meta-commands for navigating a database, the SQL you write most, and the indexing, permission, and backup commands that are easy to forget.
For query tuning, the key habit is `EXPLAIN ANALYZE`: it shows the actual plan and timings so you can tell whether an index is being used before you add another one.
Run inside the psql shell — they start with a backslash.
\l | List databases. |
\c dbname | Connect to a database. |
\dt | List tables in the current schema. |
\d tablename | Describe a table (columns, indexes, constraints). |
\du | List roles/users. |
\timing | Toggle query execution timing. |
\x | Toggle expanded (row-per-line) output. |
CREATE TABLE t (id serial PRIMARY KEY, ...); | Create a table with an auto-increment key. |
ALTER TABLE t ADD COLUMN c text; | Add a column. |
ALTER TABLE t ALTER COLUMN c SET NOT NULL; | Add a NOT NULL constraint. |
DROP TABLE t; | Delete a table and its data. |
TRUNCATE t RESTART IDENTITY; | Empty a table fast and reset sequences. |
SELECT * FROM t WHERE c = $1 LIMIT 20; | Filtered, bounded read (always bound large reads). |
INSERT INTO t (a, b) VALUES ($1, $2) RETURNING id; | Insert and get the generated id back. |
UPDATE t SET a = $1 WHERE id = $2; | Update matching rows. |
INSERT ... ON CONFLICT (id) DO UPDATE SET ... | Upsert — insert or update on key clash. |
SELECT a, count(*) FROM t GROUP BY a; | Aggregate with grouping. |
CREATE INDEX ON t (col); | B-tree index for equality/range lookups. |
CREATE UNIQUE INDEX ON t (col); | Enforce uniqueness and speed lookups. |
CREATE INDEX CONCURRENTLY ... | Build an index without locking writes. |
EXPLAIN ANALYZE SELECT ...; | Show the real plan and timings. |
VACUUM ANALYZE t; | Reclaim space and refresh planner stats. |
CREATE ROLE app LOGIN PASSWORD '...'; | Create a login role. |
GRANT SELECT, INSERT ON t TO app; | Grant table privileges. |
pg_dump dbname > dump.sql | Back up a database to a SQL file. |
pg_dump -Fc dbname > db.dump | Custom-format dump (compressed, parallel restore). |
pg_restore -d dbname db.dump | Restore a custom-format dump. |
Run `EXPLAIN ANALYZE` before the query. If the plan shows an “Index Scan” or “Index Only Scan” on the relevant index, it is being used; a “Seq Scan” on a large table usually means the index is missing, not selective enough, or the query prevents its use (e.g. a function on the column).
Plain `VACUUM` reclaims dead-row space for reuse and updates statistics without locking the table for reads/writes. `VACUUM FULL` rewrites the whole table to return space to the OS but takes an exclusive lock, so it is a maintenance-window operation.