Guide

Appearance

PostgreSQL 概述：为什么它是「最强大的开源关系型数据库」 ​

当你在技术选型时选 MySQL 还是 PostgreSQL？

很多人会说：「用 MySQL 吧，成熟、简单、社区活跃。」

但如果我告诉你，有一款数据库在 ACID 特性上更严格、支持更丰富的数据类型、索引机制更强大、扩展性更出色——你会不会重新考虑一下？

今天，我们来聊聊 PostgreSQL。

PostgreSQL 的「前世今生」 ​

PostgreSQL 起源于 UC Berkeley 的 POSTGRES 项目，1996 年正式对外发布。二十多年过去，它已经从一个小众的学术研究项目，成长为功能最全面的开源关系型数据库。

有意思的是，PostgreSQL 的名字曾经让很多人困惑——有人把它念成「Post-gress」，有人念成「Post-gre-sql」。实际上，正确的发音是 /ˈpōstɡres ˌkyuˈāl/，简写为「Postgres」。

「PostgreSQL」中的「SQL」不是缩写，而是表示它对 SQL 标准的坚持。所以叫它「Postgres」是完全正确的。

PostgreSQL 的核心特性 ​

1. 对 SQL 标准的极致兼容 ​

PostgreSQL 几乎实现了 SQL 标准的全部特性，包括：

  • 完整的事务支持（ACID）
  • 复杂的 JOIN 操作
  • 子查询（包括相关子查询）
  • CTEs（WITH 子句）
  • 窗口函数
  • 递归查询

如果你之前只用 MySQL，你会发现 PostgreSQL 对 SQL 标准的遵循程度令人惊叹。

2. 丰富的数据类型 ​

MySQL 的数据类型相对保守：INT、VARCHAR、DATE、TEXT...

PostgreSQL 呢？

类别PostgreSQL 支持的类型
基础类型INTEGER、BIGINT、NUMERIC、VARCHAR、TEXT、DATE、TIME、TIMESTAMP
几何类型POINT、LINE、CIRCLE、BOX、POLYGON
网络类型INET、CIDR、MACADDR
货币类型MONEY
枚举类型ENUM
范围类型INT4RANGE、INT8RANGE、DATERANGE、TSTZRANGE
JSON/JSONBJSON、JSONB
数组类型INTEGER[]、TEXT[]
XMLXML
UUIDUUID

这意味着什么？你可以用一个字段存储一个坐标点，一个 IP 地址段，甚至一个日期范围。不需要像 MySQL 那样用 VARCHAR 来凑合。

3. 强大的索引机制 ​

MySQL 主要依赖 B-Tree 索引（InnoDB 使用 B+ Tree）。

PostgreSQL 支持的索引类型：

索引类型适用场景
B-Tree默认索引，适用于等值查询和范围查询
Hash适用于等值查询，但不支持范围查询
GiST适用于几何类型、全文搜索
GIN适用于数组、JSON、全文搜索
BRIN适用于物理顺序相关的大表
Partial Index只索引满足条件的行
Expression Index基于表达式/函数结果的索引

后面会有专门的章节讲解 PostgreSQL 的索引体系。

4. MVCC 与并发控制 ​

PostgreSQL 和 MySQL（InnoDB）都使用 MVCC（多版本并发控制），但实现细节有所不同：

  • MySQL InnoDB：Read Committed 和 Repeatable Read 两种隔离级别都使用 MVCC
  • PostgreSQL：所有隔离级别（Read Committed、Repeatable Read、Serializable）都使用 MVCC

PostgreSQL 的 MVCC 实现更加「纯粹」——它通过 xmin/xmax 字段来标识版本，而不是依赖回滚段。

5. 扩展性 ​

PostgreSQL 被称为「可扩展的数据库」，这不是说说而已：

  • 支持自定义数据类型
  • 支持自定义操作符
  • 支持自定义索引类型
  • 支持自定义函数（支持多种语言：PL/pgSQL、Python、JavaScript、Rust 等）
  • 官方提供了 PostGIS（空间数据库）、pgvector（向量数据库）等扩展
PostgreSQL = 关系数据库 + 存储过程语言 + 扩展框架

PostgreSQL vs MySQL：选谁？ ​

这是一个经典的「MySQL vs PostgreSQL」对比：

维度MySQLPostgreSQL
定位Web 应用首选，简单易用企业级应用首选，功能全面
SQL 标准部分实现几乎完全实现
事务支持支持
MVCCInnoDB 支持原生支持
索引类型主要 B-Tree多种类型
数据类型基础类型为主丰富多样
扩展性有限强大
性能（简单查询）较快略慢
性能（复杂查询）一般优秀
主从复制原生支持原生支持
分区表支持支持
并行查询有限完整支持
社区生态成熟庞大活跃专业

选 MySQL 的场景：

  • 简单的 CRUD 应用
  • 需要高并发简单查询
  • 团队对 MySQL 更熟悉
  • 生态工具更丰富（如 MySQL Router、MySQL Shell）

选 PostgreSQL 的场景：

  • 需要复杂查询（多表 JOIN、子查询、窗口函数）
  • 需要丰富的数据类型（JSON、数组、GIS）
  • 需要高级特性（CTEs、递归查询）
  • 需要高扩展性（自定义类型、函数、索引）
  • 数据一致性要求极高

PostgreSQL 的适用场景 ​

1. 数据分析场景 ​

PostgreSQL 的窗口函数、CTEs、递归查询，加上强大的 SQL 兼容性，让它成为数据分析的利器。

sql
-- 使用窗口函数计算累计销售额
SELECT 
    month,
    sales,
    SUM(sales) OVER (ORDER BY month) as cumulative_sales,
    AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3m
FROM monthly_sales;

2. JSON 数据处理 ​

MySQL 5.7 开始支持 JSON 类型，但 PostgreSQL 的 JSONB 在性能和功能上都更胜一筹。

sql
-- PostgreSQL 的 JSONB 可以建索引
CREATE INDEX idx_user_preferences ON users USING GIN (preferences);

-- 查询 JSON 字段中的嵌套属性
SELECT * FROM users 
WHERE preferences @> '{"theme": "dark"}';

3. 地理信息系统（GIS） ​

PostgreSQL + PostGIS 是地理信息系统的标准解决方案，比 MySQL 的空间扩展强大得多。

sql
-- 查找半径 10 公里内的所有门店
SELECT * FROM stores 
WHERE ST_DWithin(
    location::geography, 
    ST_MakePoint(116.4, 39.9)::geography, 
    10000
);

4. 复杂业务逻辑 ​

需要大量存储过程、触发器、自定义函数的业务，PostgreSQL 的 PL/pgSQL 让这一切变得自然。

面试中的 PostgreSQL ​

面试官问 PostgreSQL，通常想考察什么？

  1. 为什么选 PostgreSQL 而不是 MySQL？

    • 考察对数据库选型的理解
    • 需要结合具体业务场景分析

  2. PostgreSQL 的 MVCC 是怎么实现的？

    • 考察对并发控制的理解深度
    • xmin/xmax 机制是重点

  3. PostgreSQL 支持哪些索引类型？

    • 考察知识面的广度
    • 需要能说清楚各类型的适用场景

  4. PostgreSQL 的 VACUUM 机制是干什么的？

    • 考察对 PostgreSQL 独特机制的理解
    • 这是 MySQL 没有的特性

总结 ​

PostgreSQL 是一款「全才型」数据库：

  • SQL 标准支持最全面
  • 数据类型最丰富
  • 索引机制最强大
  • 扩展性最好
  • MVCC 实现更纯粹
  • VACUUM 机制独树一帜

如果你的团队有足够的技术能力，PostgreSQL 能让你做很多事情。

下一节，我们来看看 PostgreSQL 和 MySQL 的具体差异，让你在技术选型时更有底气。

基于 VitePress 构建

Copyright © 2025-2026 Guide