PostgreSQL Cheat Sheet

PostgreSQL cheat sheet with real commands and snippets for setup, core workflows, debugging, and production-safe automation patterns. If you are working across tools, pair this with the Django Cheat Sheet and Elixir Cheat Sheet.

Setup and Connection

Goal: Connect to database shell or endpoint

# Run connection or version command
psql "postgresql://user:pass@localhost:5432/app"

# Run connection or version command
psql -d app -U postgres

# Run connection or version command
psql -c "SELECT version();"

Schema and Data Modeling

Goal: Create a table/index/schema foundation

-- Create table with JSONB and timestamp defaults
CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  kind TEXT NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Core Queries and Mutations

Goal: Run practical read/write operations

-- Filter JSONB payload data and aggregate counts
SELECT payload->>'source' AS source, COUNT(*)
FROM events
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY payload->>'source'
ORDER BY COUNT(*) DESC;

Performance and Optimization

Goal: Add indexes and inspect query plans

-- Add B-tree and GIN indexes for query acceleration
CREATE INDEX idx_events_created_at ON events (created_at DESC);
CREATE INDEX idx_events_payload_gin ON events USING GIN (payload);
EXPLAIN ANALYZE SELECT * FROM events WHERE payload @> '{"source":"web"}';

Automation and Backups

Goal: Create backup and restore routines

# Export data snapshot before risky migrations
pg_dump "$DATABASE_URL" > backup.sql || mysqldump appdb > backup.sql || true

# Compress backup artifact for storage
gzip -f backup.sql || true

# Verify backup file exists and has content
ls -lh backup.sql.gz || ls -lh backup.sql

Debugging and Troubleshooting

Goal: Inspect slow-query symptoms

# Check active connections/sessions
psql -c "SELECT now();" || mysql -e "SELECT NOW();" || true

# Inspect query plans for heavy statements
psql -c "EXPLAIN SELECT 1;" || mysql -e "EXPLAIN SELECT 1;" || true

# Search migration files for destructive statements
rg "DROP TABLE|TRUNCATE|DELETE FROM" prisma migrations sql -n

Common Gotchas

• Use transactions for multi-step PostgreSQL data changes that must stay consistent.
• Create indexes for frequent WHERE and JOIN columns, then verify with query plans.
• Back up production data before schema migrations.
• Separate read-only credentials from write credentials in app environments.
• Monitor connection pool limits to avoid timeout spikes under load.

Related Sheets

• Django Cheat Sheet — daily Django commands and production-ready examples.
• Elixir Cheat Sheet — daily Elixir commands and production-ready examples.
• Express Cheat Sheet — daily Express commands and production-ready examples.