PostgreSQL — Commands & Recipes¶

Connection & Basics¶

# Connect
psql -h localhost -U postgres -d mydb

# Create database
createdb mydb

# Import SQL
psql -d mydb -f schema.sql

# Dump
pg_dump mydb > backup.sql
pg_dump -Fc mydb > backup.custom  # compressed

Essential Queries¶

-- Database size
SELECT pg_size_pretty(pg_database_size('mydb'));

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;

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

-- Kill long query
SELECT pg_terminate_backend(pid);

-- Index usage
SELECT relname, idx_scan, seq_scan,
  ROUND(100.0 * idx_scan / NULLIF(idx_scan + seq_scan, 0), 1) AS idx_pct
FROM pg_stat_user_tables ORDER BY seq_scan DESC;

PostgreSQL 18 Features¶

-- UUIDv7 (time-ordered, no index bloat)
CREATE TABLE events (
  id uuid DEFAULT uuidv7() PRIMARY KEY,
  data jsonb NOT NULL,
  created_at timestamptz DEFAULT now()
);

-- Virtual generated columns (computed, not stored)
ALTER TABLE products ADD COLUMN price_with_tax numeric
  GENERATED ALWAYS AS (price * 1.1) VIRTUAL;

# postgresql.conf — enable async I/O (v18)
io_method = 'io_uring'    # or 'worker' or 'sync'

Replication¶

# Set up streaming replication (primary)
# postgresql.conf
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5

# On replica
pg_basebackup -h primary -D /var/lib/postgresql/18/main -R -P

Performance Tuning¶

# Essential postgresql.conf settings
shared_buffers = '4GB'           # 25% of RAM
effective_cache_size = '12GB'    # 75% of RAM
work_mem = '64MB'
maintenance_work_mem = '1GB'
max_wal_size = '4GB'
random_page_cost = 1.1           # SSD
effective_io_concurrency = 200   # SSD/NVMe

Sources¶

PostgreSQL docs