数据库优化师

-- 好的设计：外键索引、合理的约束
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_created_at ON users(created_at DESC);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(500) NOT NULL,
    content TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 外键索引，加速 JOIN
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 部分索引，优化高频查询
CREATE INDEX idx_posts_published
ON posts(published_at DESC)
WHERE status = 'published';

-- 复合索引，覆盖过滤+排序
CREATE INDEX idx_posts_status_created
ON posts(status, created_at DESC);

-- ❌ 坏：N+1 查询模式
SELECT * FROM posts WHERE user_id = 123;
-- 然后对每篇文章：
SELECT * FROM comments WHERE post_id = ?;

-- ✅ 好：单次 JOIN 查询
EXPLAIN ANALYZE
SELECT
    p.id, p.title, p.content,
    json_agg(json_build_object(
        'id', c.id,
        'content', c.content,
        'author', c.author
    )) as comments
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.user_id = 123
GROUP BY p.id;

-- 检查查询计划：
-- 关注：Seq Scan(差)、Index Scan(好)、Bitmap Heap Scan(尚可)
-- 对比：实际时间 vs 预估时间，实际行数 vs 预估行数

// ❌ 坏：应用层 N+1
const users = await db.query("SELECT * FROM users LIMIT 10");
for (const user of users) {
  user.posts = await db.query(
    "SELECT * FROM posts WHERE user_id = $1",
    [user.id]
  );
}

// ✅ 好：单次聚合查询
const usersWithPosts = await db.query(`
  SELECT
    u.id, u.email, u.name,
    COALESCE(
      json_agg(
        json_build_object('id', p.id, 'title', p.title)
      ) FILTER (WHERE p.id IS NOT NULL),
      '[]'
    ) as posts
  FROM users u
  LEFT JOIN posts p ON p.user_id = u.id
  GROUP BY u.id
  LIMIT 10
`);

-- ✅ 好：可回滚的迁移，不锁表
BEGIN;

-- 添加带默认值的列（PostgreSQL 11+ 不会重写表）
ALTER TABLE posts
ADD COLUMN view_count INTEGER NOT NULL DEFAULT 0;

-- 并发创建索引（不锁表）
COMMIT;
CREATE INDEX CONCURRENTLY idx_posts_view_count
ON posts(view_count DESC);

-- ❌ 坏：迁移期间锁表
ALTER TABLE posts ADD COLUMN view_count INTEGER;
CREATE INDEX idx_posts_view_count ON posts(view_count);

// Supabase 连接池配置
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!,
  {
    db: {
      schema: 'public',
    },
    auth: {
      persistSession: false, // 服务端
    },
  }
);

// Serverless 场景使用事务模式连接池
const pooledUrl = process.env.DATABASE_URL?.replace(
  '5432',
  '6543' // 事务模式端口
);