PostgreSQL Cheat Sheet: SQL, Indexes, EXPLAIN & psql

psql — command line client

| Command | What it does | |---|---| | psql -U postgres | Connect as postgres user | | psql -U user -d mydb -h localhost | Full connection | | psql "postgresql://user:pass@host/db" | Connection string | | \l | List all databases | | \c mydb | Connect to a database | | \dt | List tables in current database | | \dt schema.* | List tables in a schema | | \d tablename | Describe table (columns, types, indexes) | | \di | List indexes | | \dv | List views | | \df | List functions | | \du | List users/roles | | \timing | Toggle query execution time display | | \x | Toggle expanded output (great for wide tables) | | \e | Open last query in editor | | \i file.sql | Run SQL from a file | | \o file.txt | Send output to file | | \q | Quit |

Database operations

| Command | What it does | |---|---| | CREATE DATABASE mydb; | Create a database | | DROP DATABASE mydb; | Delete a database | | CREATE DATABASE mydb OWNER myuser; | Create with owner | | \c mydb | Switch to database |

Table operations

| Command | What it does | |---|---| | CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMPTZ DEFAULT NOW()); | Create table | | DROP TABLE users; | Delete table | | DROP TABLE IF EXISTS users; | Delete if exists | | TRUNCATE TABLE users; | Delete all rows (fast) | | ALTER TABLE users ADD COLUMN age INT; | Add column | | ALTER TABLE users DROP COLUMN age; | Remove column | | ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(255); | Change column type | | ALTER TABLE users RENAME COLUMN name TO full_name; | Rename column | | ALTER TABLE users RENAME TO customers; | Rename table |

CRUD

| Command | What it does | |---|---| | INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); | Insert one row | | INSERT INTO users (name, email) VALUES (...), (...) RETURNING id; | Insert multiple, return IDs | | SELECT * FROM users; | Select all | | SELECT name, email FROM users WHERE id = 1; | Select specific columns | | SELECT * FROM users ORDER BY created_at DESC LIMIT 10; | Sort + limit | | UPDATE users SET name = 'Bob' WHERE id = 1; | Update one row | | UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '90 days'; | Conditional update | | DELETE FROM users WHERE id = 1; | Delete one row | | DELETE FROM users WHERE active = false RETURNING id; | Delete and return |

Filtering

| Syntax | What it does | |---|---| | WHERE status = 'active' | Equality | | WHERE age > 18 AND age < 65 | Range | | WHERE name LIKE 'Al%' | Pattern match | | WHERE name ILIKE 'al%' | Case-insensitive LIKE | | WHERE id IN (1, 2, 3) | In a list | | WHERE id NOT IN (...) | Not in list | | WHERE email IS NULL | Null check | | WHERE email IS NOT NULL | Not null | | WHERE created_at BETWEEN '2025-01-01' AND '2026-01-01' | Date range |

Joins

| Join | What it does | |---|---| | INNER JOIN posts ON posts.user_id = users.id | Only matching rows | | LEFT JOIN posts ON posts.user_id = users.id | All users, matched posts or NULL | | RIGHT JOIN | All posts, matched users or NULL | | FULL OUTER JOIN | All rows from both, NULLs for non-matches | | CROSS JOIN | Every combination (cartesian product) |

Indexes

| Command | What it does | |---|---| | CREATE INDEX ON users(email); | Simple index | | CREATE UNIQUE INDEX ON users(email); | Unique index | | CREATE INDEX ON users(last_name, first_name); | Composite index | | CREATE INDEX CONCURRENTLY ON users(email); | Non-blocking index creation | | CREATE INDEX ON posts USING GIN(tags); | GIN index for arrays/JSONB | | CREATE INDEX ON posts USING GiST(location); | GiST for geometric/range types | | DROP INDEX index_name; | Remove index | | \di | List all indexes |

Aggregations

| Function | What it does | |---|---| | COUNT(*) | Count all rows | | COUNT(DISTINCT user_id) | Count unique values | | SUM(amount) | Sum | | AVG(score) | Average | | MIN(price) | Minimum | | MAX(price) | Maximum | | GROUP BY status | Group rows | | HAVING COUNT(*) > 5 | Filter groups (not rows) |

JSON & JSONB

| Operator | What it does | |---|---| | data->>'key' | Get text value from JSON | | data->'key' | Get JSON value (keeps type) | | data#>>'{a,b}' | Nested path as text | | data @> '{"role":"admin"}' | Contains (JSONB) | | data ? 'key' | Key exists (JSONB) | | jsonb_set(data, '{key}', '"value"') | Update a field | | data - 'key' | Remove a key (JSONB) |

Detailed sections

EXPLAIN ANALYZE — understanding query plans

-- See the query plan without running it
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Run it and show actual timing
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- Full output with buffers (shows cache hits)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42;

What to look for:

• Seq Scan on a large table = missing index
• Nested Loop with high rows = join might need an index
• actual time much higher than estimated time = stale statistics, run ANALYZE tablename
• Rows Removed by Filter: 99000 = index exists but not selective enough

CTEs — Common Table Expressions

-- Basic CTE (readable subquery)
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
recent_orders AS (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, COALESCE(o.order_count, 0) AS orders_last_30_days
FROM active_users u
LEFT JOIN recent_orders o ON o.user_id = u.id
ORDER BY orders_last_30_days DESC;

-- Recursive CTE — walk a tree (e.g. org chart, file system)
WITH RECURSIVE org_tree AS (
  -- base case: top-level managers
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive case: their reports
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;

Window functions

-- Rank users by order count, partitioned by country
SELECT
  name,
  country,
  order_count,
  RANK() OVER (PARTITION BY country ORDER BY order_count DESC) AS country_rank
FROM user_stats;

-- Running total
SELECT
  created_at::date AS day,
  revenue,
  SUM(revenue) OVER (ORDER BY created_at::date) AS running_total
FROM daily_revenue;

-- Previous row value
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly_revenue;

-- Row number per group
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) sub
WHERE rn = 1;  -- most recent order per user

JSONB — practical queries

-- Create table with JSONB column
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attributes JSONB
);

-- Insert
INSERT INTO products (name, attributes) VALUES
  ('Laptop', '{"brand": "Dell", "ram": 16, "tags": ["electronics", "computers"]}');

-- Query by JSONB field
SELECT * FROM products WHERE attributes->>'brand' = 'Dell';

-- Query nested
SELECT * FROM products WHERE attributes->'specs'->>'cpu' = 'M3';

-- Contains check (uses GIN index)
SELECT * FROM products WHERE attributes @> '{"brand": "Dell"}';

-- Array contains
SELECT * FROM products WHERE attributes->'tags' ? 'electronics';

-- Index for JSONB (required for @> and ? to be fast)
CREATE INDEX ON products USING GIN(attributes);

-- Update a field inside JSONB
UPDATE products
SET attributes = jsonb_set(attributes, '{ram}', '32')
WHERE id = 1;

Useful admin queries

-- Show running queries
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Kill a long-running query
SELECT pg_cancel_backend(pid);   -- graceful
SELECT pg_terminate_backend(pid); -- force kill

-- Table sizes
SELECT
  relname AS table,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Index usage (find unused indexes)
SELECT
  relname AS table,
  indexrelname AS index,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Cache hit ratio (should be > 99%)
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

-- Locks
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE relation IS NOT NULL;

Backup and restore

# Dump a database
pg_dump -U postgres mydb > mydb_backup.sql

# Dump compressed
pg_dump -U postgres -Fc mydb > mydb_backup.dump

# Restore from SQL
psql -U postgres mydb < mydb_backup.sql

# Restore from compressed dump
pg_restore -U postgres -d mydb mydb_backup.dump

# Dump specific table
pg_dump -U postgres -t users mydb > users_backup.sql

# Dump all databases
pg_dumpall -U postgres > all_databases.sql

Hands-on guides: Run PostgreSQL Locally with Docker | Debug a Slow SQL Query | SQL Cheat Sheet

Related Articles

Deepen your understanding with these curated continuations.

View All Articles

Cheatsheet 5 min read

SQL Cheat Sheet: Queries, Joins, Aggregates & CTEs

The ultimate SQL reference for SELECT syntax, JOINs, subqueries, and window functions. Master CTEs, transactions, and constraints for any relational database.

Vishnu Mar 27, 2026

HowTo 5 min read

How to Debug a Slow SQL Query in PostgreSQL

Step-by-step: find slow queries with pg_stat_statements, read EXPLAIN ANALYZE output, identify missing indexes, fix N+1 queries, and diagnose lock contention.

Vishnu Dec 31, 2025

PostgreSQL 5 min read

PostgreSQL JSONB: Query Patterns & Performance Optimization

Master PostgreSQL JSONB — operators, indexing, querying nested data, aggregation, and performance tuning. Real-world patterns for modern applications.

Rachel Apr 01, 2026