PostgreSQL 与 MySQL 怎么选

互联网团队里 MySQL 用得最多，但 PostgreSQL 一直在追赶并在很多场景反超。两者没有绝对优劣，关键看业务特征：MySQL 在简单读多写少、运维生态成熟、云厂商默认支持的场景下最省心；PostgreSQL 在复杂查询、半结构化数据、地理空间、严格事务与扩展能力上更强。

简单判断：业务以标准 CRUD 和报表为主，MySQL 足够；需要存储 JSON、做全文检索、地理位置计算、复杂分析查询，或对数据完整性要求极高，PostgreSQL 是更合适的选择。

本文核心知识点
  • 丰富类型数组、范围、JSONB、几何类型开箱即用。
  • 索引全家桶B 树之外还有 GIN、GIST、BRIN、哈希。
  • JSONB二进制存 JSON，可建索引、可按路径查询。
  • 扩展生态pg_trgm 模糊检索、PostGIS 地理空间。
PostgreSQL 配图：蓝色渐变背景中央发光的大象轮廓，左侧一棵圆点节点的索引树，右侧半透明文档卡片内嵌嵌套树形结构
大象轮廓代表 PostgreSQL，圆点树代表索引体系，文档卡片代表 JSONB——三者构成这篇的主线。

数据类型与特性概览

PostgreSQL 的数据类型比 MySQL 丰富得多：除了常用的数值、字符串、时间，还有数组类型（一列直接存一组值）、范围类型（表示一个区间，如时间范围、数值范围）、JSON/JSONB、几何类型（点、线、多边形）。这些类型让很多原本要拆表的设计可以优雅地存在一张表里。

-- 一列直接存标签数组
CREATE TABLE article (
  id bigserial PRIMARY KEY,
  title text,
  tags text[]                  -- 直接是字符串数组
);
INSERT INTO article (title, tags)
VALUES ('PostgreSQL 实战', ARRAY['数据库','JSONB','索引']);

索引体系：B 树、GIN 与 BRIN

默认的 B 树索引适合等值与范围查询，和 MySQL 一致。PostgreSQL 的特色在于多种专用索引：GIN 索引适合数组、JSONB、全文检索这类"一个值包含多个元素"的场景，建索引体积大但检索极快；BRIN 索引适合超大表中物理顺序与取值顺序一致的列（如按时间递增的日志表），索引体积极小；GIST 适合几何与范围类型。

-- 为 JSONB 建 GIN 索引，支持按键路径快速查询
CREATE INDEX idx_article_attr ON article USING GIN (attr jsonb_path_ops);

-- 超大日志表用 BRIN，索引极小
CREATE INDEX idx_log_time ON access_log USING BRIN (log_time);

选索引类型的原则：普通等值范围用 B 树；数组、JSONB、全文用 GIN；按时间递增的超大表用 BRIN。

JSONB：半结构化数据怎么存怎么查

JSONB 把 JSON 以二进制形式存储，去掉了多余空格和重复键，可以建索引、可以按路径查询、可以更新某个字段，比纯 text 存 JSON 强得多。适合字段不固定、需要灵活扩展的业务，比如商品属性、用户画像、配置项。

-- 存一个半结构化的商品属性
INSERT INTO product (id, attr)
VALUES (1, '{"color": "蓝", "weight": 300, "tags": ["棉","春季"]}'::jsonb);

-- 按键路径查询：找所有蓝色商品
SELECT * FROM product WHERE attr ->> 'color' = '蓝';

-- 数组包含查询：找带"春季"标签的商品
SELECT * FROM product WHERE attr -> 'tags' ? '春季';

注意 JSONB 不是万能：需要频繁按某字段聚合统计的，还是抽成独立列更高效；JSONB 适合"灵活多变、偶尔查询"的属性，而不是核心结构化数据。

事务与 MVCC 的特点

PostgreSQL 的 MVCC（多版本并发控制）实现是：更新不覆盖旧行，而是写新版本，旧版本留给需要读旧数据的事务，由 vacuum 进程回收。这让读不阻塞写、写不阻塞读，但代价是表会"膨胀"，需要定期 vacuum 清理死元组。

它的事务隔离级别支持到完整的 SQL 标准，包括可序列化（Serializable），这在对数据一致性要求极高的金融、账务场景是加分项。运维上要关注 autovacuum 的频率，大表更新频繁时及时调参，否则膨胀会拖慢查询。

常用扩展：pg_trgm 与 PostGIS

PostgreSQL 的扩展生态是它最大的杀手锏之一。pg_trgm 提供三元组模糊匹配，可以对任意文本列做"包含即查"的模糊搜索，比 like '%关键词%' 快几个数量级，适合站内搜索、名称联想。PostGIS 则把数据库变成地理信息系统，支持点线面的距离、包含、相交计算，适合外卖、打车、门店附近这类位置服务。

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_city_name ON city USING GIN (name gin_trgm_ops);
-- 之后 name LIKE '%上海%' 也能走索引