Database Cheatsheet¶
Quick reference for common PostgreSQL operations.
Connection¶
# psql
psql -h localhost -U postgres -d sartiq
psql "postgresql://user:pass@localhost:5432/sartiq"
# pgcli (better autocomplete)
pgcli -h localhost -U postgres -d sartiq
# Environment variable
export PGPASSWORD='password'
psql -h localhost -U postgres -d sartiq
Database Operations¶
-- List databases
\l
-- Connect to database
\c sartiq
-- Create database
CREATE DATABASE sartiq;
-- Drop database
DROP DATABASE sartiq;
-- Database size
SELECT pg_size_pretty(pg_database_size('sartiq'));
Table Operations¶
-- List tables
\dt
-- Describe table
\d users
\d+ users -- with more detail
-- Table size
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- Row count (fast estimate)
SELECT reltuples::bigint FROM pg_class WHERE relname = 'users';
-- Row count (exact)
SELECT COUNT(*) FROM users;
Common Queries¶
-- Select with limit
SELECT * FROM users LIMIT 10;
-- Select with condition
SELECT * FROM users WHERE status = 'active';
-- Select with ordering
SELECT * FROM users ORDER BY created_at DESC;
-- Select with join
SELECT u.name, COUNT(b.id) as bookings
FROM users u
LEFT JOIN bookings b ON b.user_id = u.id
GROUP BY u.id;
-- Insert
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- Insert returning id
INSERT INTO users (name, email) VALUES ('John', 'john@example.com') RETURNING id;
-- Update
UPDATE users SET status = 'active' WHERE id = 1;
-- Delete
DELETE FROM users WHERE id = 1;
-- Upsert
INSERT INTO users (email, name) VALUES ('john@example.com', 'John')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
Indexes¶
-- List indexes
\di
SELECT * FROM pg_indexes WHERE tablename = 'users';
-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status_email ON users(status, email);
-- Create index concurrently (no lock)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Drop index
DROP INDEX idx_users_email;
-- Partial index
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';
-- Index usage stats
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
Constraints¶
-- Add foreign key
ALTER TABLE bookings ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);
-- Add unique constraint
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
-- Add check constraint
ALTER TABLE bookings ADD CONSTRAINT ck_amount CHECK (amount > 0);
-- Drop constraint
ALTER TABLE users DROP CONSTRAINT uq_email;
JSONB¶
-- Query JSON field
SELECT * FROM users WHERE preferences->>'theme' = 'dark';
-- Query nested JSON
SELECT * FROM users WHERE preferences->'notifications'->>'email' = 'true';
-- Update JSON field
UPDATE users SET preferences = preferences || '{"theme": "light"}' WHERE id = 1;
-- Remove JSON key
UPDATE users SET preferences = preferences - 'deprecated' WHERE id = 1;
-- JSONB containment
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
Full-Text Search¶
-- Simple search
SELECT * FROM products
WHERE to_tsvector('english', name || ' ' || description) @@ to_tsquery('english', 'wireless');
-- With ranking
SELECT name, ts_rank(search_vector, query) as rank
FROM products, to_tsquery('english', 'headphones') as query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- With highlighting
SELECT name, ts_headline('english', description, to_tsquery('english', 'wireless'))
FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless');
Transactions¶
-- Basic transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Rollback
ROLLBACK;
-- Savepoint
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
SAVEPOINT sp1;
INSERT INTO users (name) VALUES ('Bob');
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
-- Set isolation level
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Locks¶
-- Select for update
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Skip locked rows
SELECT * FROM jobs WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 10;
-- Advisory lock
SELECT pg_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);
Performance¶
-- Explain query
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';
-- Slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Current queries
SELECT pid, now() - query_start as duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
-- Kill query
SELECT pg_cancel_backend(pid); -- Graceful
SELECT pg_terminate_backend(pid); -- Force
Maintenance¶
-- Update statistics
ANALYZE users;
ANALYZE VERBOSE users;
-- Vacuum (reclaim space)
VACUUM users;
VACUUM FULL users; -- Rewrites table, locks it
-- Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;
REINDEX INDEX CONCURRENTLY idx_users_email;
-- Table bloat
SELECT schemaname, relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
Backup & Restore¶
# Backup
pg_dump -h localhost -U postgres sartiq > backup.sql
pg_dump -h localhost -U postgres -Fc sartiq > backup.dump
# Restore
psql -h localhost -U postgres sartiq < backup.sql
pg_restore -h localhost -U postgres -d sartiq backup.dump
# Backup specific tables
pg_dump -h localhost -U postgres -t users -t bookings sartiq > tables.sql
Replication¶
-- Check replication status
SELECT * FROM pg_stat_replication;
-- Replication lag
SELECT client_addr,
pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes
FROM pg_stat_replication;
User Management¶
-- Create user
CREATE USER app_user WITH PASSWORD 'password';
-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE sartiq TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- Create read-only user
CREATE USER readonly_user WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE sartiq TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
Useful psql Commands¶
\l List databases
\c dbname Connect to database
\dt List tables
\d tablename Describe table
\di List indexes
\df List functions
\x Toggle expanded output
\timing Toggle query timing
\e Edit query in editor
\i file.sql Execute SQL file
\copy Import/export CSV
\q Quit
Copy Data¶
-- Export to CSV
\copy users TO '/tmp/users.csv' WITH CSV HEADER;
-- Import from CSV
\copy users FROM '/tmp/users.csv' WITH CSV HEADER;
-- Export query result
\copy (SELECT * FROM users WHERE status = 'active') TO '/tmp/active_users.csv' WITH CSV HEADER;
Date/Time¶
-- Current timestamp
SELECT NOW();
SELECT CURRENT_TIMESTAMP;
-- Date truncation
SELECT DATE_TRUNC('month', created_at) FROM bookings;
-- Date arithmetic
SELECT created_at + INTERVAL '1 day' FROM users;
SELECT created_at - INTERVAL '1 hour' FROM users;
-- Date difference
SELECT NOW() - created_at as age FROM users;
SELECT EXTRACT(EPOCH FROM (NOW() - created_at)) as seconds FROM users;
-- Generate date series
SELECT generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day');