PostgreSQL Dev Config Cheatsheet

Connection Management (pg-connect)

``bash # Connect to local database psql -d mydb

# Connect to remote database psql -h host.example.com -p 5432 -U user -d mydb

# Execute single SQL and exit psql -d mydb -c "SELECT version();"

# List all databases psql -l

# Execute SQL from file psql -d mydb -f script.sql

# Password-less connection via env var export PGPASSWORD=secret psql -h localhost -U user -d mydb

# Display connection info \conninfo `

DDL — Table Structure (pg-ddl)

`sql -- Create table CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT NOW() );

-- Add column ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0;

-- Drop column ALTER TABLE users DROP COLUMN age;

-- Change column type ALTER TABLE users ALTER COLUMN name TYPE TEXT;

-- Set default value ALTER TABLE users ALTER COLUMN created_at SET DEFAULT NOW();

-- Add NOT NULL constraint ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Drop table DROP TABLE users;

-- Drop if exists DROP TABLE IF EXISTS users;

-- Rename table ALTER TABLE users RENAME TO customers;

-- Describe table structure \d+ users

-- Create index CREATE INDEX idx_users_email ON users(email);

-- Create unique index CREATE UNIQUE INDEX idx_users_username ON users(username); `

DML — Data Manipulation (pg-dml)

`sql -- Insert single row INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- Insert multiple rows INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Charlie', 'charlie@example.com');

-- Insert and return generated ID INSERT INTO users (name, email) VALUES ('Dave', 'dave@example.com') RETURNING id;

-- Update data UPDATE users SET name = 'Alice Updated' WHERE id = 1;

-- Update and return UPDATE users SET name = 'Alice New' WHERE id = 1 RETURNING *;

-- Delete data DELETE FROM users WHERE id = 5;

-- Truncate table (preserve structure) TRUNCATE TABLE users;

-- Truncate and reset auto-increment TRUNCATE TABLE users RESTART IDENTITY;

-- Delete and return DELETE FROM users WHERE id = 5 RETURNING *; `

Queries & JOINs (pg-query)

`sql -- Basic queries SELECT * FROM users; SELECT id, name, email FROM users;

-- WHERE conditions SELECT * FROM users WHERE age > 18;

-- Sorting SELECT * FROM users ORDER BY created_at DESC;

-- Pagination SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- Fuzzy search (LIKE) SELECT * FROM users WHERE name LIKE '%Ali%';

-- IN clause SELECT * FROM users WHERE id IN (1, 2, 3);

-- Range query SELECT * FROM users WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';

-- INNER JOIN SELECT u.name, o.amount FROM users u JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- Multi-table JOIN SELECT u.name, o.amount, p.product_name FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id;

-- Subquery SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); `

Aggregation & Grouping (pg-query)

`sql -- Count SELECT COUNT(*) FROM users;

-- Distinct count SELECT COUNT(DISTINCT city) FROM users;

-- Aggregate functions SELECT AVG(age), MAX(age), MIN(age), SUM(salary) FROM employees;

-- GROUP BY SELECT city, COUNT(*) as user_count FROM users GROUP BY city ORDER BY user_count DESC;

-- HAVING filter SELECT city, COUNT(*) as user_count FROM users GROUP BY city HAVING COUNT(*) > 5;

-- GROUP BY with multiple aggregates SELECT department, AVG(salary), MAX(salary), COUNT(*) FROM employees GROUP BY department;

-- DISTINCT SELECT DISTINCT city FROM users; `

Index Management (pg-index)

`sql -- Create regular index CREATE INDEX idx_users_name ON users(name);

-- Create unique index CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Create composite index CREATE INDEX idx_users_city_age ON users(city, age);

-- Create partial index CREATE INDEX idx_users_active ON users(created_at) WHERE status = 'active';

-- List all indexes on a table SELECT * FROM pg_indexes WHERE tablename = 'users';

-- Show index details \d+ idx_users_name

-- Drop index DROP INDEX idx_users_name;

-- Analyze table (update statistics) ANALYZE users;

-- Rebuild index REINDEX INDEX idx_users_email; `

Backup & Restore (pg-backup)

`bash # Backup single database (custom format) pg_dump -Fc mydb > mydb.dump

# Backup single database (plain SQL) pg_dump -Fp mydb > mydb.sql

# Compressed backup pg_dump -Fc mydb | gzip > mydb.dump.gz

# Backup specific tables pg_dump -t users -t orders mydb > tables.sql

# Schema-only backup (no data) pg_dump -s mydb > schema.sql

# Data-only backup (no schema) pg_dump -a mydb > data.sql

# Remote backup pg_dump -h remote_host -U user -d mydb > backup.sql

# Restore custom format backup pg_restore -d mydb mydb.dump

# Restore SQL format backup psql -d mydb < mydb.sql

# Parallel restore (multi-core) pg_restore -d mydb -j 4 mydb.dump

# Backup entire cluster pg_dumpall > full_cluster.sql `

Performance Tuning (pg-perf)

`sql -- View query plan (estimated) EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- View actual execution plan (with timing) EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- View slow queries (requires pg_stat_statements) SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;

-- VACUUM (reclaim storage) VACUUM users;

-- VACUUM FULL (full reclaim, exclusive lock) VACUUM FULL users;

-- Analyze table (update statistics) ANALYZE users;

-- Show autovacuum settings SHOW autovacuum;

-- View active connections SELECT pid, usename, application_name, state, query FROM pg_stat_activity WHERE state = 'active';

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

-- View database size SELECT pg_database_size('mydb');

-- View table size SELECT pg_total_relation_size('users');

-- Human-readable size SELECT pg_size_pretty(pg_database_size('mydb'));

-- Set transaction isolation level BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- ... queries ... COMMIT; `

Privilege Management (pg-perf)

``sql -- Create user CREATE USER app_user WITH PASSWORD 'secure_password';

-- Create database with owner CREATE DATABASE mydb OWNER app_user;

-- Grant all privileges GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;

-- Grant table privileges GRANT SELECT, INSERT, UPDATE ON users TO app_user;

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

-- Revoke privileges REVOKE DELETE ON users FROM app_user;

-- List users/roles \du

-- View table privileges \z users