PostgreSQL vs MySQL：实际项目中的选择

最近两个项目分别用了 PostgreSQL 和 MySQL，对比一下感受。

功能对比

功能	PostgreSQL	MySQL
JSON 支持	原生 JSONB，支持索引	JSON 类型，索引较弱
全文搜索	内置，中文需要分词插件	内置，一般
地理位置类型	PostGIS 扩展，很强	基本支持
窗口函数	完整支持	8.0 后支持
并发控制	MVCC，无锁读	MVCC，有差距
事务隔离	支持全部级别	默认 REPEATABLE READ

PostgreSQL 用得爽的地方

JSONB

存 JSON 不用序列化，直接当字段用：

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  attrs JSONB
);

-- 查询 JSON 内字段
SELECT * FROM products WHERE attrs->>'color' = 'red';

-- 给 JSON 字段建索引
CREATE INDEX idx_attrs_color ON products ((attrs->>'color'));

我们有个商品属性系统，用 JSONB 存动态属性，省了建一堆字段。

数组类型

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  tags TEXT[]
);

-- 查询包含某标签的文章
SELECT * FROM posts WHERE 'vue' = ANY(tags);

-- 数组聚合
SELECT array_agg(name) FROM users WHERE team_id = 1;

CTE (Common Table Expression)

复杂查询用 CTE 写起来很清晰：

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS total
  FROM orders
  GROUP BY month
),
ranked AS (
  SELECT
    month,
    total,
    LAG(total) OVER (ORDER BY month) AS prev_total
  FROM monthly_sales
)
SELECT
  month,
  total,
  (total - prev_total) / prev_total * 100 AS growth_rate
FROM ranked;

数据库监控面板

MySQL 用得爽的地方

简单项目上手快

# 安装
brew install mysql

# 创建数据库
CREATE DATABASE myapp;

# 搞定

PostgreSQL 的配置项多，新手容易晕。

生态成熟

ORM、连接池、监控工具，MySQL 都有一堆选择。云服务商对 MySQL 的支持也更完善。

读写分离

MySQL 主从复制配置简单，很多云数据库一键开启。PostgreSQL 也可以，但配置起来麻烦点。

存储过程

虽然现在不推荐用存储过程，但有些老项目确实依赖这个。MySQL 的存储过程语法更接近直觉。

踩过的坑

PostgreSQL

1. 连接数限制

默认最大连接数 100，不够用。改 max_connections 要重启。

后来用了 PgBouncer 做连接池：

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

2. VACUUM

MVCC 的代价是更新会产生死元组，需要 VACUUM 清理。虽然有 autovacuum，但高写入场景还是需要调优。

MySQL

1. InnoDB 锁

大批量更新容易锁表，得用分批处理：

def batch_update():
    while True:
        rows = db.execute("SELECT id FROM items WHERE status = 'pending' LIMIT 1000")
        if not rows:
            break
        ids = [r.id for r in rows]
        db.execute(f"UPDATE items SET status = 'done' WHERE id IN ({','.join(ids)})")

2. 字符集

一定要显式设置 utf8mb4，默认的 utf8 是阉割版：

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

数据库迁移

选型建议

场景	推荐
简单 CRUD、快速开发	MySQL
复杂查询、数据分析	PostgreSQL
大量 JSON 数据	PostgreSQL
地理位置应用	PostgreSQL + PostGIS
传统企业应用	MySQL
高并发读写分离	MySQL

迁移成本

从 MySQL 迁移到 PostgreSQL：

DDL 语法有差异
自增主键写法不同
部分函数名不一样

建议用 pgloader 或 AWS DMS，比自己写脚本省事。

总结

没有绝对的优劣，看需求选。

我现在默认选 PostgreSQL，因为 JSON 支持好、扩展性强。但如果是小项目、团队熟悉 MySQL，没必要换。

数据库选型只是第一步，更重要的是用好它。索引设计、查询优化、监控告警，这些才是关键。