PostgreSQL 最佳实践

# 使用 Homebrew 安装
brew install postgresql@18

# 启动服务
brew services start postgresql@18

# 或手动启动
pg_ctl -D /opt/homebrew/var/postgresql@18 start

# 连接
psql -U postgres -d postgres

# 创建 PostgreSQL 仓库配置文件
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# 导入仓库签名密钥
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# 更新并安装
sudo apt update
sudo apt install -y postgresql-18

# 启动服务
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 连接（切换到 postgres 用户）
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:

-- 查看数据库
\l

-- 连接数据库
\c myapp

-- 查看表
\dt

-- 基本 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;

-- 使用适当的数据类型
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()
);

-- 添加注释
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.id IS '用户唯一标识';

-- 外键约束
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()
);

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

-- B-tree 索引（默认）
CREATE INDEX idx_users_email ON users(email);

-- 复合索引
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 部分索引
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

-- GIN 索引（JSONB）
CREATE INDEX idx_products_data ON products USING GIN(data);

-- GiST 索引（全文搜索）
CREATE INDEX idx_articles_search ON articles USING GIST(to_tsvector('english', content));

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

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

-- 使用 LIMIT 限制
SELECT * FROM orders LIMIT 100;

-- 使用 EXISTS 替代 IN
SELECT * FROM users u WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 使用 CTE（公共表表达式）
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;

-- 创建 JSONB 列
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    data JSONB
);

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

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

-- 使用 GIN 索引查询
CREATE INDEX idx_products_data ON products USING GIN(data);

-- 包含查询
SELECT * FROM products WHERE data @> '{"color": "red"}';

-- 任意键查询
SELECT * FROM products WHERE data ? 'tags';

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

-- 创建分区表
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 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');

-- 默认分区（捕获未匹配的数据）
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;
-- 执行更新
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- SKIP LOCKED（非阻塞）
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

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

-- 日期
SELECT NOW();
SELECT CURRENT_DATE;
SELECT DATE_TRUNC('month', created_at);
SELECT AGE(created_at);  -- 距今天数

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

-- 窗口函数
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;