PostgreSQL Best Practices

# Install via Homebrew
brew install postgresql@18

# Start service
brew services start postgresql@18

# Or start manually
pg_ctl -D /opt/homebrew/var/postgresql@18 start

# Connect
psql -U postgres -d postgres

# Create PostgreSQL repository config file
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import repository signing key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Update and install
sudo apt update
sudo apt install -y postgresql-18

# Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Connect (switch to postgres user)
sudo -u postgres psql

services:
  postgres:
    image: postgres:18-alpine
    container_name: postgres
    restart: unless-stopped
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: password123
      POSTGRES_DB: myapp
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d myapp"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:

-- View databases
\l

-- Connect to database
\c myapp

-- View tables
\dt

-- Basic CRUD
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
SELECT * FROM users WHERE name = 'Alice';
UPDATE users SET age = 30 WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- Use appropriate data types
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    age INTEGER CHECK (age >= 0),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Add comments
COMMENT ON TABLE users IS 'User table';
COMMENT ON COLUMN users.id IS 'Unique user identifier';

-- Foreign key constraint
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    total DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'completed';

-- B-tree index (default)
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

-- GIN index (JSONB)
CREATE INDEX idx_products_data ON products USING GIN(data);

-- GiST index (full-text search)
CREATE INDEX idx_articles_search ON articles USING GIST(to_tsvector('english', content));

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Avoid SELECT *
SELECT u.id, u.name FROM users u WHERE u.id = 1;

-- Use LIMIT
SELECT * FROM orders LIMIT 100;

-- Use EXISTS instead of IN
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Use CTE (Common Table Expression)
WITH recent_orders AS (
    SELECT user_id, SUM(total) as total
    FROM orders
    WHERE created_at > '2024-01-01'
    GROUP BY user_id
)
SELECT u.name, r.total
FROM users u
JOIN recent_orders r ON u.id = r.user_id;

-- Create JSONB column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    data JSONB
);

-- Insert
INSERT INTO products (name, data) VALUES (
    'Widget',
    '{"color": "red", "size": "large", "tags": ["new", "sale"]}'
);

-- Query JSONB
SELECT * FROM products WHERE data->>'color' = 'red';

-- Use GIN index for queries
CREATE INDEX idx_products_data ON products USING GIN(data);

-- Containment query
SELECT * FROM products WHERE data @> '{"color": "red"}';

-- Any key query
SELECT * FROM products WHERE data ? 'tags';

-- JSONB iteration
SELECT id, jsonb_array_elements_text(data->'tags') as tag
FROM products;

-- Create partitioned table
CREATE TABLE orders (
    id BIGSERIAL,
    user_id BIGINT NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Default partition (captures unmatched data)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE products (
    id SERIAL,
    name VARCHAR(255),
    category VARCHAR(50),
    PRIMARY KEY (id, category)
) PARTITION BY LIST (category);

CREATE TABLE products_electronics PARTITION OF products
    FOR VALUES IN ('electronics', 'gadgets');

CREATE TABLE products_clothing PARTITION OF products
    FOR VALUES IN ('clothing', 'shoes');

-- SELECT FOR UPDATE
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Perform update
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- SKIP LOCKED (non-blocking)
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

-- String
SELECT CONCAT(first_name, ' ', last_name) FROM users;
SELECT UPPER(email);
SELECT LOWER(email);
SELECT TRIM(name);

-- Date
SELECT NOW();
SELECT CURRENT_DATE;
SELECT DATE_TRUNC('month', created_at);
SELECT AGE(created_at);  -- Days since

-- Aggregation
SELECT COUNT(*), SUM(total), AVG(total), MIN(total), MAX(total)
FROM orders;

-- Window functions
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
    SUM(salary) OVER (PARTITION BY department) as dept_total
FROM employees;