PostgreSQL Guide

# macOS (Homebrew)
brew install postgresql@18
brew services start postgresql@18

# Ubuntu / Debian
sudo apt install -y postgresql postgresql-contrib
sudo systemctl start postgresql

# Windows — download the installer from:
# https://www.postgresql.org/download/windows/

# Verify installation
psql --version
# PostgreSQL 18.x

-- Connect as superuser
psql -U postgres

-- Create a database
CREATE DATABASE myapp_db;

-- Create a user with password
CREATE USER myapp_user WITH ENCRYPTED PASSWORD 'securepassword';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- Connect to the database
\c myapp_db

CREATE TABLE products (
  id        SERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  tags      TEXT[],
  scores    INTEGER[]
);

INSERT INTO products (name, tags, scores) VALUES
  ('Widget A', ARRAY['electronics', 'gadget'], ARRAY[90, 85, 92]);

-- Query with array operators
SELECT * FROM products
WHERE 'electronics' = ANY(tags);

-- Unnest array into rows
SELECT name, unnest(tags) AS tag FROM products;

-- UUIDv7 embeds millisecond timestamp in high bits (PG 18+)
CREATE TABLE events (
  id         UUID PRIMARY KEY DEFAULT uuidv7(),
  event_type TEXT NOT NULL,
  payload    JSONB
);
-- UUIDs sort chronologically — excellent B-tree locality
SELECT uuidv7();
SELECT uuid_extract_timestamp(uuidv7());
-- UUIDv4 comparison (random, bad B-tree locality)
SELECT gen_random_uuid();

-- VIRTUAL (PG18 default): computed on read, no extra storage
CREATE TABLE products (
  price      NUMERIC(10,2),
  tax_rate   NUMERIC(4,4) DEFAULT 0.18,
  price_incl NUMERIC(10,2) GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL
);
-- STORED: persisted to disk, can be directly indexed
CREATE TABLE contacts (
  first_name TEXT, last_name TEXT,
  full_name  TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);
CREATE INDEX ON contacts(full_name);

UPDATE products SET price = price * 1.10
WHERE category = 'electronics'
RETURNING OLD.price AS old_price, NEW.price AS new_price, NEW.id;

DELETE FROM orders WHERE status = 'cancelled' RETURNING OLD.*;

MERGE INTO inventory i USING shipment s ON i.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = i.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
RETURNING merge_action(), NEW.sku, NEW.qty;

CREATE INDEX idx_dept_salary ON employees(department, salary);
-- PG 18 skip scan: uses index even WITHOUT condition on 'department'
SELECT * FROM employees WHERE salary > 100000;
SET enable_skipscan = on;   -- default in PG 18
SET enable_skipscan = off;  -- disable for testing

SELECT first_name, last_name, email,
  EXTRACT(YEAR FROM created_at) AS signup_year
FROM users
WHERE active = TRUE AND created_at >= '2023-01-01'
ORDER BY last_name ASC, first_name ASC
LIMIT 20 OFFSET 0;

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

-- LEFT JOIN
SELECT u.email, COUNT(o.id) AS order_count
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.email;

-- FULL OUTER JOIN with COALESCE
SELECT COALESCE(a.name, b.name) AS name,
  a.value AS left_val, b.value AS right_val
FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.id;

-- Recursive CTE
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10
) SELECT * FROM seq;

-- Multi-step CTE pipeline
WITH
  active_users AS (SELECT id, email FROM users WHERE active = TRUE),
  top_spenders AS (
    SELECT user_id, SUM(amount) AS total FROM payments
    GROUP BY user_id HAVING SUM(amount) > 1000)
SELECT au.email, ts.total FROM active_users au
JOIN top_spenders ts ON au.id = ts.user_id ORDER BY ts.total DESC;

SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary) AS prev_salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank
FROM employees;

SELECT
  abs(-42),  ceil(4.2),  floor(4.9),
  round(4.567, 2),  sqrt(144),  power(2, 10),
  mod(17, 5),  random(),  trunc(4.789),
  log(100),  ln(2.71828);

CREATE TABLE events (
  id      BIGSERIAL PRIMARY KEY,
  payload JSONB NOT NULL
);
INSERT INTO events (payload) VALUES
  ('{"type":"click","user":{"id":42,"name":"Alice"},"ts":1700000000}');

-- Operators: -> returns JSON, ->> returns text
SELECT
  payload ->> 'type' AS event_type,
  payload -> 'user' ->> 'name' AS user_name,
  (payload ->> 'ts')::BIGINT AS timestamp
FROM events;

-- Containment operator @>
SELECT * FROM events
WHERE payload @> '{"user":{"id":42}}';

-- GIN index for fast JSONB querying
CREATE INDEX idx_events_payload ON events USING gin(payload);

CREATE TABLE orders (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status     TEXT NOT NULL DEFAULT 'pending',
  total      NUMERIC(12,2) CHECK (total >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE orders ADD COLUMN notes TEXT;
ALTER TABLE orders RENAME COLUMN notes TO memo;
ALTER TABLE orders DROP COLUMN memo;
DROP TABLE customer_orders CASCADE;
TRUNCATE TABLE orders RESTART IDENTITY CASCADE;

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY doc_owner ON documents
  USING (owner_id = current_user_id());
CREATE POLICY admin_all ON documents
  TO admin_role USING (TRUE);

INSERT INTO products (name, price) VALUES
  ('Widget A', 9.99), ('Widget B', 14.99);
INSERT INTO users (email) VALUES ('alice@example.com') RETURNING id, created_at;

UPDATE products SET price = price * 1.10 WHERE category = 'electronics';
DELETE FROM sessions WHERE expires_at < now();

-- UPSERT
INSERT INTO product_counts (product_id, count) VALUES (42, 1)
ON CONFLICT (product_id) DO UPDATE SET
  count = product_counts.count + EXCLUDED.count;

-- MERGE (PG 15+)
MERGE INTO customer_accounts ca
USING new_transactions nt ON ca.account_id = nt.account_id
WHEN MATCHED THEN UPDATE SET balance = ca.balance + nt.amount
WHEN NOT MATCHED THEN INSERT (account_id, balance) VALUES (nt.account_id, nt.amount);

-- COPY bulk load
COPY products (name, price) FROM '/tmp/products.csv'
  WITH (FORMAT csv, HEADER true);

CREATE OR REPLACE FUNCTION calc_tax(price NUMERIC, rate NUMERIC DEFAULT 0.18)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * (1 + rate);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Set-returning function
CREATE OR REPLACE FUNCTION active_orders(uid INT)
RETURNS TABLE(order_id INT, total NUMERIC, status TEXT) AS $$
BEGIN
  RETURN QUERY SELECT id, total, status FROM orders
    WHERE user_id = uid AND status != 'cancelled';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE transfer_funds(
  from_id INT, to_id INT, amount NUMERIC
) LANGUAGE plpgsql AS $$
DECLARE bal NUMERIC;
BEGIN
  SELECT balance INTO bal FROM accounts WHERE id = from_id FOR UPDATE;
  IF bal < amount THEN
    RAISE EXCEPTION 'Insufficient funds';
  END IF;
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  COMMIT;
END;
$$;
CALL transfer_funds(101, 202, 500.00);

-- Auto-set updated_at
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_updated_at
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- Audit log trigger
CREATE OR REPLACE FUNCTION audit_changes() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log(table_name, operation, old_data, new_data)
  VALUES(TG_TABLE_NAME, TG_OP,
    CASE WHEN TG_OP != 'INSERT' THEN row_to_json(OLD) END,
    CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW) END);
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Conditional trigger
CREATE TRIGGER trg_price_change
  AFTER UPDATE OF price ON products
  FOR EACH ROW WHEN (OLD.price IS DISTINCT FROM NEW.price)
  EXECUTE FUNCTION notify_price_change();

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Skip locked rows (queue pattern)
SELECT * FROM job_queue WHERE status = 'pending'
ORDER BY created_at LIMIT 1 FOR UPDATE SKIP LOCKED;

-- Advisory locks
SELECT pg_advisory_lock(12345);
SELECT pg_try_advisory_lock(12345);
SELECT pg_advisory_unlock(12345);

-- Enable streaming replication
wal_level = replica
max_wal_senders = 10
hot_standby = on

-- Clone primary to standby data directory
pg_basebackup -h primary_ip -D /var/lib/postgresql/data -U replicator -P -R

CREATE PUBLICATION my_pub FOR TABLE orders, products;

CREATE SUBSCRIPTION my_sub 
  CONNECTION 'host=primary_ip dbname=mydb user=replicator' 
  PUBLICATION my_pub;

-- Check replication lag and status
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_stat_subscription;

wal_level = replica       # Required for archiving/PITR
archive_mode = on         # Enables WAL archiving
archive_command = 'cp %p /mnt/server/archive/%f'

restore_command = 'cp /mnt/server/archive/%f %p'
recovery_target_time = '2023-10-27 12:00:00'
recovery_target_action = 'promote'

# Backup a single database
pg_dump mydb > backup.sql

# Backup the entire cluster (roles, globals)
pg_dumpall > full_cluster.sql

# Create a physical base backup
pg_basebackup -h localhost -D /path/to/backup -Ft -z -P

-- Standard vacuum: reclaims space for reuse (doesn't lock table)
VACUUM users;

-- Full vacuum: shrinks file on disk (requires ACCESS EXCLUSIVE lock)
VACUUM FULL users;

-- Vacuum with Analyze
VACUUM ANALYZE users;

autovacuum = on
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2 -- trigger at 20% bloat
autovacuum_max_workers = 3

ANALYZE orders;
-- Check when statistics were last gathered
SELECT relname, last_analyze, last_autoanalyze 
FROM pg_stat_user_tables;

CREATE ROLE read_only;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
CREATE USER analyst WITH PASSWORD 'pass';
GRANT read_only TO analyst;

# Custom compressed format (recommended)
pg_dump -U postgres -Fc mydb > mydb.dump
# Parallel dump for large databases
pg_dump -U postgres -Fd -j 4 -f ./backup_dir mydb
# Restore
pg_restore -U postgres -d mydb mydb.dump