PostgreSQL

-- Store JSON
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);

-- Query JSONB
SELECT payload->>'name' FROM events;       -- Text
SELECT payload->'address'->>'city' FROM events;
SELECT * FROM events WHERE payload->>'type' = 'click';

-- JSONB containment
SELECT * FROM events WHERE payload @> '{"type": "click"}';

-- JSONB array
SELECT * FROM events WHERE payload->'tags' ? 'urgent';

-- Index for JSONB queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);
CREATE INDEX idx_events_type ON events ((payload->>'type'));

-- CTE
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
)
SELECT month, revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_revenue;

-- Recursive CTE (tree traversal)
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON t.id = c.parent_id
)
SELECT * FROM tree ORDER BY depth;

-- B-tree (default — equality, range, sort)
CREATE INDEX idx_users_email ON users (email);

-- Partial index (smaller, faster for filtered queries)
CREATE INDEX idx_active_users ON users (created_at)
WHERE status = 'active';

-- Composite index
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- GIN for JSONB / arrays / full-text
CREATE INDEX idx_docs_fts ON docs USING GIN (to_tsvector('english', body));

-- Explain query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- Find missing indexes (slow queries)
SELECT * FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;

# Connect
psql -h localhost -p 5432 -U postgres -d mydb
psql "postgresql://user:pass@host:5432/mydb"

# Inside psql
\l                  -- list databases
\c mydb             -- connect to database
\dt                 -- list tables
\dt schema.*        -- list tables in schema
\d users            -- describe table (columns, indexes, constraints)
\d+ users           -- verbose description
\di                 -- list indexes
\dv                 -- list views
\df                 -- list functions
\dn                 -- list schemas
\du                 -- list roles/users

\x                  -- toggle expanded output (useful for wide rows)
\timing             -- show query execution time
\e                  -- open query in $EDITOR
\i /path/to/file.sql  -- execute SQL file
\o /path/out.txt    -- write output to file
\q                  -- quit

# Execute a single command
psql -U postgres -d mydb -c "SELECT count(*) FROM users;"

# Execute a SQL file
psql -U postgres -d mydb -f /path/to/migration.sql

# Pipe SQL
echo "SELECT now();" | psql -U postgres -d mydb

# Suppress headers/footers (for scripting)
psql -U postgres -d mydb -t -A -c "SELECT id FROM users WHERE active;"
# -t  = tuples only (no header/footer)
# -A  = unaligned output
# -F, = comma-separated (combine with -A)

# Save query result to CSV
psql -U postgres -d mydb -c "\COPY users TO '/tmp/users.csv' CSV HEADER;"

-- Create a login role
CREATE USER app_user WITH PASSWORD 'secret';

-- Create a role (no login — for grouping privileges)
CREATE ROLE readonly;

-- Grant role membership
GRANT readonly TO app_user;

-- Alter password
ALTER USER app_user WITH PASSWORD 'newpass';

-- Make superuser / remove superuser
ALTER USER app_user SUPERUSER;
ALTER USER app_user NOSUPERUSER;

-- List roles
\du
SELECT rolname, rolsuper, rolcanlogin FROM pg_roles;

-- Drop user (must own no objects)
DROP USER app_user;

-- Grant table privileges
GRANT SELECT, INSERT, UPDATE ON TABLE users TO app_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_user;

-- Grant on future tables (important!)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO app_user;

-- Grant sequence (needed for INSERT with SERIAL/BIGSERIAL)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Read-only role pattern
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

-- Revoke
REVOKE INSERT ON TABLE users FROM app_user;

-- Show grants
\dp users

# Dump single database (plain SQL)
pg_dump -U postgres mydb > mydb.sql

# Dump in custom format (compressed, supports parallel restore)
pg_dump -U postgres -Fc mydb > mydb.dump

# Dump specific tables
pg_dump -U postgres -t users -t orders mydb > subset.sql

# Dump schema only (no data)
pg_dump -U postgres --schema-only mydb > schema.sql

# Dump data only
pg_dump -U postgres --data-only mydb > data.sql

# Restore plain SQL dump
psql -U postgres -d mydb < mydb.sql

# Restore custom format (faster, parallel)
pg_restore -U postgres -d mydb -j 4 mydb.dump
# -j 4 = 4 parallel workers

# Restore to a new database
createdb -U postgres mydb_new
pg_restore -U postgres -d mydb_new mydb.dump

# Dump all databases + global objects (roles, tablespaces)
pg_dumpall -U postgres > full_backup.sql

# Dump only global objects (roles)
pg_dumpall -U postgres --globals-only > globals.sql

# Restore full dump
psql -U postgres < full_backup.sql

# Scheduled backup with cron
# crontab -e
0 2 * * * pg_dump -U postgres -Fc mydb > /backups/mydb_$(date +\%F).dump
# Keep last 7 days
0 3 * * * find /backups -name '*.dump' -mtime +7 -delete

# Test backup integrity
pg_restore --list mydb.dump | head -20

-- Reclaim dead row space (autovacuum does this automatically)
VACUUM users;

-- Vacuum + update planner statistics
VACUUM ANALYZE users;

-- Full vacuum: rewrites table, reclaims disk space (locks table!)
VACUUM FULL users;

-- Update query planner statistics only
ANALYZE users;

-- Reindex (fix bloated / corrupted indexes)
REINDEX TABLE users;
REINDEX INDEX idx_users_email;

-- Non-blocking reindex (Postgres 12+)
REINDEX TABLE CONCURRENTLY users;

-- Check table bloat
SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- All active connections
SELECT pid, usename, application_name, client_addr, state,
       wait_event_type, wait_event,
       now() - query_start AS duration,
       query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- Kill a specific connection
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;

-- Kill all idle connections older than 10 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > INTERVAL '10 minutes';

-- Connection count by database
SELECT datname, count(*) AS connections
FROM pg_stat_activity
GROUP BY datname
ORDER BY connections DESC;

-- Find blocking queries
SELECT
  blocked.pid          AS blocked_pid,
  blocked.query        AS blocked_query,
  blocking.pid         AS blocking_pid,
  blocking.query       AS blocking_query,
  now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- Active locks
SELECT relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation IS NOT NULL
ORDER BY relation;

-- Database size
SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- Table sizes
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;