PostgreSQL 实践

要点

PostgreSQL 是 AI 项目的推荐关系型数据库，功能全面、扩展丰富
JSONB 类型可以在关系型表里存储半结构化数据，支持 GIN 索引
pgvector 扩展支持向量存储和相似度检索，避免引入额外的向量数据库
与 Hono 集成通常使用 Drizzle 或 Prisma 作为 ORM
连接池管理是生产环境的必备配置
UUID 主键比自增 ID 更适合分布式和 API 场景

1. 安装与启动

1.1 本地开发

macOS：

brew install postgresql@16
brew services start postgresql@16

Linux（Ubuntu）：

sudo apt install postgresql
sudo systemctl start postgresql

Docker：

docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  postgres:16

1.2 创建数据库

createdb hono_app

或通过 psql：

psql -U postgres
CREATE DATABASE hono_app;

2. 与 Hono 集成

2.1 Drizzle ORM

Drizzle 是 TypeScript 优先的 ORM，类型推导好，性能高：

pnpm add drizzle-orm pg
pnpm add -D drizzle-kit @types/pg

// src/schema.ts
import { pgTable, uuid, text, timestamp, jsonb, integer } from 'drizzle-orm/pg-core'
 
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  metadata: jsonb('metadata'),
  createdAt: timestamp('created_at').defaultNow(),
})
 
export const conversations = pgTable('conversations', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  title: text('title').notNull(),
  model: text('model').notNull(),
  messageCount: integer('message_count').default(0),
  createdAt: timestamp('created_at').defaultNow(),
})

// src/lib/db.ts
import { Pool } from 'pg'
import { drizzle } from 'drizzle-orm/node-postgres'
import * as schema from '../schema'
 
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,  // 连接池大小
})
 
export const db = drizzle(pool, { schema })

// src/routes/users.ts
import { Hono } from 'hono'
import { db } from '../lib/db'
import { users } from '../schema'
import { eq } from 'drizzle-orm'
 
const app = new Hono()
 
app.get('/users', async (c) => {
  const allUsers = await db.select().from(users)
  return c.json(allUsers)
})
 
app.get('/users/:id', async (c) => {
  const id = c.req.param('id')
  const user = await db.select().from(users).where(eq(users.id, id)).get()
  if (!user) return c.json({ error: 'Not found' }, 404)
  return c.json(user)
})
 
app.post('/users', async (c) => {
  const body = await c.req.json()
  const [user] = await db.insert(users).values(body).returning()
  return c.json(user, 201)
})
 
export default app

2.2 Prisma

Prisma 是另一个流行的 TypeScript ORM，schema 定义独立于代码：

pnpm add prisma @prisma/client
pnpx prisma init

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  metadata  Json?
  createdAt DateTime @default(now())
}

pnpx prisma migrate dev --name init

// src/lib/db.ts
import { PrismaClient } from '@prisma/client'
 
export const prisma = new PrismaClient()

// src/routes/users.ts
import { Hono } from 'hono'
import { prisma } from '../lib/db'
 
const app = new Hono()
 
app.get('/users', async (c) => {
  const users = await prisma.user.findMany()
  return c.json(users)
})
 
export default app

3. JSONB 类型

PostgreSQL 的 JSONB 类型可以在关系型表里存储 JSON 数据，支持高效的查询和索引。

3.1 存储 JSON

// src/schema.ts
import { pgTable, uuid, text, jsonb } from 'drizzle-orm/pg-core'
 
export const documents = pgTable('documents', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  metadata: jsonb('metadata').$type<{
    author?: string
    tags?: string[]
    publishedAt?: string
  }>(),
})

// 插入带 metadata 的文档
await db.insert(documents).values({
  title: 'Hono Guide',
  content: '...',
  metadata: {
    author: 'Alice',
    tags: ['hono', 'typescript'],
    publishedAt: '2026-06-20',
  },
})

3.2 查询 JSON 字段

import { sql } from 'drizzle-orm'
 
// 查询 metadata.author = 'Alice'
const docs = await db
  .select()
  .from(documents)
  .where(sql`${documents.metadata}->>'author' = 'Alice'`)
 
// 查询 metadata.tags 包含 'hono'
const taggedDocs = await db
  .select()
  .from(documents)
  .where(sql`${documents.metadata}->'tags' ? 'hono'`)

3.3 GIN 索引

JSONB 字段可以建 GIN 索引，加速 JSON 查询：

// drizzle.config.ts 或迁移脚本
import { sql } from 'drizzle-orm'
 
await db.execute(sql`
  CREATE INDEX idx_documents_metadata ON documents USING GIN (metadata)
`)

GIN 索引支持 @>（包含）、?（键存在）、?|（任一键存在）、?&（所有键存在）等操作符。

4. pgvector 扩展

pgvector 让 PostgreSQL 支持向量存储和相似度检索。

4.1 安装

# macOS
brew install pgvector
 
# 在数据库里启用
psql -d hono_app -c "CREATE EXTENSION vector;"

Docker：

docker run -d \
  --name pgvector \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  pgvector/pgvector:pg16

4.2 定义向量列

// src/schema.ts
import { pgTable, uuid, text, vector } from 'drizzle-orm/pg-core'
 
export const documentEmbeddings = pgTable('document_embeddings', {
  id: uuid('id').primaryKey().defaultRandom(),
  documentId: uuid('document_id').notNull(),
  chunk: text('chunk').notNull(),
  embedding: vector('embedding', { dimensions: 1536 }).notNull(),
  createdAt: timestamp('created_at').defaultNow(),
})

4.3 插入向量

import { openai } from './lib/openai'
 
// 生成 embedding
const response = await openai.embeddings.create({
  model: 'text-embedding-ada-002',
  input: 'Hello world',
})
 
const embedding = response.data[0].embedding  // number[]
 
// 插入数据库
await db.insert(documentEmbeddings).values({
  documentId: 'doc-1',
  chunk: 'Hello world',
  embedding,
})

4.4 相似度查询

import { sql } from 'drizzle-orm'
 
const queryEmbedding = [0.1, 0.2, ...]  // 1536 维
 
// 余弦相似度
const results = await db
  .select({
    id: documentEmbeddings.id,
    chunk: documentEmbeddings.chunk,
    similarity: sql`1 - (${documentEmbeddings.embedding} <=> ${queryEmbedding})`,
  })
  .from(documentEmbeddings)
  .orderBy(sql`${documentEmbeddings.embedding} <=> ${queryEmbedding}`)
  .limit(5)

<=> 是余弦距离操作符。1 - distance 得到相似度。

4.5 向量索引

数据量大时，需要建向量索引加速查询：

-- IVFFlat 索引（适合中等规模）
CREATE INDEX idx_embeddings_ivfflat
  ON document_embeddings
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);
 
-- HNSW 索引（适合大规模，查询更快，构建更慢）
CREATE INDEX idx_embeddings_hnsw
  ON document_embeddings
  USING hnsw (embedding vector_cosine_ops);

IVFFlat 需要至少有 lists 数量的行才能建索引。HNSW 没有这个限制。

5. UUID 主键

AI 项目的 API 通常使用 UUID 作为主键，而不是自增 ID：

不可猜测：自增 ID 容易被枚举（/users/1、/users/2）
分布式友好：多个服务可以同时生成 UUID，不会冲突
API 安全：URL 里暴露自增 ID 会泄露业务量信息

// Drizzle: UUID 主键
import { pgTable, uuid } from 'drizzle-orm/pg-core'
 
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  // ...
})

UUID v4 是随机的，不适合做索引的聚簇键（会导致页分裂）。如果数据量大，可以考虑 UUID v7（带时间戳，可排序）。

6. 连接池

生产环境必须使用连接池，避免每个请求都创建新连接：

// src/lib/db.ts
import { Pool } from 'pg'
 
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,           // 最大连接数
  idleTimeoutMillis: 30000,  // 空闲连接超时（30 秒）
  connectionTimeoutMillis: 2000,  // 获取连接超时（2 秒）
})
 
export const db = drizzle(pool, { schema })

连接池大小需要根据服务器配置和数据库配置调整：

计算公式：连接数 = CPU 核心数 × 2 + 磁盘数
一般范围：10-50 个连接
过多连接：数据库上下文切换开销大，性能下降
过少连接：请求排队等待连接，响应变慢

7. 事务

import { db } from './lib/db'
import { users, conversations } from './schema'
 
// Drizzle 事务
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({
    email: '[email protected]',
    name: 'Alice',
  }).returning()
 
  await tx.insert(conversations).values({
    userId: user.id,
    title: 'New Conversation',
    model: 'gpt-4',
  })
})

事务内的操作要么全部成功，要么全部回滚。

8. 迁移管理

Drizzle Kit 管理数据库迁移：

# 生成迁移
pnpm drizzle-kit generate
 
# 应用迁移
pnpm drizzle-kit migrate
 
# 推送 schema 变更（开发环境）
pnpm drizzle-kit push

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'
 
export default defineConfig({
  schema: './src/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
})

迁移文件存放在 drizzle/ 目录，可以纳入版本控制。

9. 性能优化

9.1 索引

为频繁查询的字段建索引：

import { index } from 'drizzle-orm/pg-core'
 
export const conversations = pgTable('conversations', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => ({
  userIdIdx: index('idx_conversations_user_id').on(table.userId),
  createdAtIdx: index('idx_conversations_created_at').on(table.createdAt),
}))

9.2 查询分析

用 EXPLAIN ANALYZE 分析查询性能：

EXPLAIN ANALYZE
SELECT * FROM conversations
WHERE user_id = 'user-1'
ORDER BY created_at DESC
LIMIT 10;

9.3 N+1 问题

Drizzle 支持 leftJoin 和 relation 查询，避免 N+1：

// ❌ N+1 问题
const conversations = await db.select().from(conversations)
for (const conv of conversations) {
  const messages = await db.select().from(messages).where(eq(messages.conversationId, conv.id))
}
 
// ✅ 一次查询
const results = await db
  .select()
  .from(conversations)
  .leftJoin(messages, eq(conversations.id, messages.conversationId))

总结

PostgreSQL 是 AI 项目的推荐关系型数据库，JSONB 和 pgvector 让它能同时处理业务数据和向量数据。

这一节涉及到的几个实践：

与 Hono 集成：Drizzle 或 Prisma 作为 ORM，连接池管理
JSONB 类型：存储半结构化数据，GIN 索引加速查询
pgvector：向量存储和相似度检索，避免引入额外向量数据库
UUID 主键：API 场景更安全、分布式友好
连接池：生产环境必备，避免连接耗尽
迁移管理：Drizzle Kit 管理 schema 变更
性能优化：索引、查询分析、避免 N+1

下一篇看 MySQL 实践——与 PostgreSQL 的差异，以及适用场景。