从MySQL到PostgreSQL：那些让我决定"迁库"的硬核功能

-- 订单表，items存储商品列表（JSON数组）
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    items JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- GIN索引加速JSONB查询
CREATE INDEX idx_orders_items ON orders USING GIN (items);

-- 查询购买过商品ID为1001的订单
SELECT * FROM orders 
WHERE items @> '[{"product_id": 1001}]'::jsonb;

-- 查询订单总金额超过200的订单（数值提取）
SELECT * FROM orders 
WHERE (items -> 'total')::numeric > 200;

-- 提取每个订单的商品名称列表
SELECT id, jsonb_path_query_array(items, '$.items[*].name') AS product_names
FROM orders;

-- 部分索引：只索引未完成的订单，索引体积减少80%
CREATE INDEX idx_orders_pending ON orders (created_at, priority) 
WHERE status IN ('pending', 'processing');

-- 查询直接走这个小型索引
SELECT * FROM orders 
WHERE status IN ('pending', 'processing') 
ORDER BY priority DESC, created_at 
LIMIT 50;

-- 按日期（忽略时间）建索引
CREATE INDEX idx_orders_date ON orders (DATE(created_at));

-- 走索引
SELECT COUNT(*) FROM orders WHERE DATE(created_at) = '2025-07-30';

CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    payload JSONB,
    status VARCHAR(20) DEFAULT 'pending',
    scheduled_at TIMESTAMP DEFAULT NOW(),
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    retry_count INT DEFAULT 0
);

CREATE INDEX idx_tasks_pending ON tasks (scheduled_at) 
WHERE status = 'pending';

-- 原子抢占：高并发下多个Worker同时执行，每个只抢到一条
WITH picked AS (
    SELECT id FROM tasks
    WHERE status = 'pending' AND scheduled_at <= NOW()
    ORDER BY scheduled_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE tasks SET 
    status = 'running', 
    started_at = NOW()
FROM picked 
WHERE tasks.id = picked.id
RETURNING tasks.*;

-- 触发器：任务创建时发送NOTIFY
CREATE OR REPLACE FUNCTION notify_task_created()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('task_created', NEW.id::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_task_created
AFTER INSERT ON tasks
FOR EACH ROW
EXECUTE FUNCTION notify_task_created();