汕头市网站建设_网站建设公司_后端开发_seo优化

PostgreSQL架构与索引深度剖析

前言

你维护了一个电商网站，需要存储千万级商品数据，你选了 MySQL。但随着业务发展，产品经理提了各种需求：地理位置搜索、全文检索、JSON 文档存储、时序数据分析…于是你引入了 Redis、Elasticsearch、MongoDB、InfluxDB，原本简单的单体架构变成了复杂的分布式系统。有没有一个数据库能原生支持这些需求？有，它就是 PostgreSQL。

🏠个人主页：你的主页

文章目录

• PostgreSQL架构与索引深度剖析
  • 一、PostgreSQL是什么
  • 二、存储架构详解
  • 三、索引类型全解析
  • 四、进程架构详解
  • 五、内存架构详解
  • 六、WAL日志机制
  • 七、查询执行流程
  • 八、PostgreSQL vs MySQL 深度对比
  • 九、总结

一、PostgreSQL是什么

1.1 一句话定义

PostgreSQL（简称 PG）是一个功能最强大的开源关系型数据库，被称为"数据库天花板"。

1.2 为什么说它是天花板

PG 不仅能完全替代 MySQL，在很多场景下还能替代：

一个数据库，多种索引，无限扩展。

1.3 国产数据库的"祖师爷"

PG 完全开源可商用（BSD 协议），国内很多自研数据库都有 PG 的影子：

• 阿里云 PolarDB（兼容 PG）
• 腾讯云 TDSQL-PG
• 华为 GaussDB（基于 PG）
• 人大金仓 KingbaseES（基于 PG）
• 瀚高数据库（基于 PG）

可以说，PG 养活了国内大半数据库团队。

二、存储架构详解

2.1 数据组织方式

PG 将数据组织成类似 Excel 表的结构：

数据库（Database） └── 模式（Schema） └── 表（Table） └── 行（Tuple）

与 MySQL 的区别：

PG 多了一层 Schema，可以在同一个数据库内实现更好的逻辑隔离。

2.2 堆表文件

数据表在磁盘上以**堆表文件（Heap File）**的形式存储：

$PGDATA/base/<database_oid>/<relfilenode>

• database_oid：数据库的 OID
• relfilenode：表的文件节点号

举个例子：

-- 查看表的存储位置SELECTpg_relation_filepath('products');-- 结果：base/16384/16385

2.3 数据页结构

当堆表文件超过1GB时，会自动拆分成多个文件（16385、16385.1、16385.2…）。

每个文件由多个8KB的数据页组成：

┌─────────────────────────────────────────┐ │ Page Header │ ← 24 字节，页面元信息 ├─────────────────────────────────────────┤ │ Item Pointers │ ← 行指针数组，指向实际数据 │ (Line Pointers) │ ├─────────────────────────────────────────┤ │ │ │ Free Space │ ← 空闲空间 │ │ ├─────────────────────────────────────────┤ │ Tuples │ ← 实际行数据（从页尾向前增长） │ (Row Data) │ ├─────────────────────────────────────────┤ │ Special Space │ ← 特殊空间（索引页使用） └─────────────────────────────────────────┘

与 MySQL InnoDB 的区别：

2.4 MVCC 实现差异

PG 和 MySQL 都支持 MVCC（多版本并发控制），但实现方式完全不同。

MySQL InnoDB 的 MVCC：

原始数据行 → Undo Log（存储旧版本）

• 更新时，旧版本数据写入 Undo Log
• 通过 Undo Log 链回溯历史版本
• Undo Log 会定期清理（Purge）

PostgreSQL 的 MVCC：

原始数据行（标记为删除） + 新数据行（插入）

• 更新时，不修改原数据，而是插入一条新数据
• 原数据标记xmax（删除事务ID），新数据标记xmin（创建事务ID）
• 旧版本数据由 VACUUM 进程清理

打个比方：

• MySQL：像用橡皮擦掉旧内容，写上新内容，旧内容存在草稿纸上
• PG：像在新的一行写新内容，旧的那行划掉但还在

各自的优缺点：

三、索引类型全解析

PG 之所以全能，核心在于它支持多种索引类型，且架构上支持扩展自定义索引。

3.1 B-Tree 索引（默认）

这是最常用的索引类型，适用于等值查询和范围查询。

CREATEINDEXidx_product_priceONproducts(price);-- 等值查询SELECT*FROMproductsWHEREprice=99;-- 范围查询SELECT*FROMproductsWHEREpriceBETWEEN50AND100;

PG 的 B-Link Tree vs MySQL 的 B+Tree：

MySQL 使用标准 B+Tree：

[30|60] ← 非叶子节点 / | \ [10|20] [40|50] [70|80] ← 叶子节点（双向链表）

PG 使用 B-Link Tree（Lehman-Yao 算法）：

[30|60] → ← 非叶子节点也有右指针 / | \ [10|20]→[40|50]→[70|80] ← 叶子节点（单向链表）

B-Link Tree 的优势：

• 非叶子节点有右指针，页分裂时不需要锁整棵树
• 并发性能更好，特别是高并发写入场景

3.2 GIN 索引（全文搜索）

GIN（Generalized Inverted Index，通用倒排索引）适用于全文搜索和数组查询。

原理：将文本分词，建立"词元 → 文档ID"的映射。

文档1: "PostgreSQL 是最强数据库" 文档2: "MySQL 也是数据库" 文档3: "PostgreSQL 支持全文搜索" 分词后建立倒排索引： ┌──────────────┬─────────────┐ │ 词元 │ 文档ID │ ├──────────────┼─────────────┤ │ PostgreSQL │ 1, 3 │ │ MySQL │ 2 │ │ 数据库 │ 1, 2 │ │ 全文搜索 │ 3 │ └──────────────┴─────────────┘

使用示例：

-- 创建全文搜索索引CREATEINDEXidx_product_name_ginONproductsUSINGgin(to_tsvector('chinese',name));-- 全文搜索SELECT*FROMproductsWHEREto_tsvector('chinese',name)@@ to_tsquery('chinese','蓝牙 & 耳机');

GIN + JSONB：替代 MongoDB

-- 创建 JSONB 列的 GIN 索引CREATEINDEXidx_product_attrsONproductsUSINGgin(attributes jsonb_path_ops);-- 查询 JSON 中包含特定键值对的记录SELECT*FROMproductsWHEREattributes @>'{"color": "blue", "size": "L"}';

JSONB 是 PG 优化后的二进制 JSON 格式，配合 GIN 索引，百万级文档也能毫秒级查询。

3.3 GiST 索引（地理信息）

GiST（Generalized Search Tree，通用搜索树）是一个索引框架，支持多维数据。

R-Tree 原理：

一维数据用线段表示范围，二维数据用矩形表示范围。

┌─────────────────────────┐ │ 根节点矩形 │ └───────────┬─────────────┘ ┌─────┴─────┐ ↓ ↓ ┌─────────┐ ┌─────────┐ │ 矩形A │ │ 矩形B │ └────┬────┘ └────┬────┘ ↓ ↓ [点1,点2] [点3,点4]

查询"附近 1 公里的店铺"时：

1. 计算查询点周围 1 公里的矩形范围
2. 从根节点开始，找与查询矩形相交的子矩形
3. 递归向下，最终找到叶子节点的坐标点

使用示例：

-- 启用 PostGIS 扩展CREATEEXTENSION postgis;-- 创建地理位置列和索引ALTERTABLEstoresADDCOLUMNlocationgeometry(Point,4326);CREATEINDEXidx_store_locationONstoresUSINGgist(location);-- 查询上海市中心 1 公里内的店铺SELECTname,ST_Distance(location,ST_SetSRID(ST_MakePoint(121.4737,31.2304),4326))asdistanceFROMstoresWHEREST_DWithin(location,ST_SetSRID(ST_MakePoint(121.4737,31.2304),4326),1000-- 1000米)ORDERBYdistance;

3.4 BRIN 索引（时序数据）

BRIN（Block Range Index，块范围索引）适用于按时间或 ID 顺序插入的大数据表。

原理：不为每行数据建索引，而是为每批数据页记录范围摘要。

┌─────────────────────────────────────────────────┐ │ 页范围 │ 最小时间 │ 最大时间 │ ├────────┼───────────────────┼───────────────────┤ │ 1-128 │ 2025-01-01 00:00 │ 2025-01-15 23:59 │ │ 129-256│ 2025-01-16 00:00 │ 2025-01-31 23:59 │ │ 257-384│ 2025-02-01 00:00 │ 2025-02-15 23:59 │ └────────┴───────────────────┴───────────────────┘

查询2025-02-10的数据时，直接定位到 257-384 页范围，跳过其他页。

使用示例：

-- 创建 BRIN 索引CREATEINDEXidx_logs_created_atONlogsUSINGbrin(created_at);-- 查询某天的日志SELECT*FROMlogsWHEREcreated_atBETWEEN'2025-02-10'AND'2025-02-11';

BRIN vs B-Tree 对比：

打个比方：

• B-Tree 像书的详细目录，精确到每一节
• BRIN 像书的章节目录，只告诉你大概在哪个章节

3.5 Hash 索引

适用于等值查询，不支持范围查询。

CREATEINDEXidx_product_skuONproductsUSINGhash(sku);-- 只支持等值查询SELECT*FROMproductsWHEREsku='SKU-12345';

注意：PG 10 之前 Hash 索引不记录 WAL，崩溃后需要重建。PG 10+ 已修复。

3.6 索引类型总结

四、进程架构详解

4.1 多进程 vs 多线程

MySQL：多线程架构

mysqld 进程 ├── 连接线程1 ├── 连接线程2 ├── 连接线程3 └── 后台线程（IO、Purge等）

PostgreSQL：多进程架构

Postmaster（主进程） ├── Backend Process 1（后端进程） ├── Backend Process 2 ├── Backend Process 3 └── Background Workers（后台进程）

各自的优缺点：

4.2 Postmaster 主进程

Postmaster 是 PG 的"指挥中心"：

• 监听客户端连接请求
• 为每个新连接 Fork 出后端进程
• 监控所有子进程的健康状态
• 管理共享内存

客户端连接请求 ↓ Postmaster 接收 ↓ Fork 后端进程 ↓ 后端进程处理请求

4.3 Backend Process 后端进程

每个客户端连接对应一个后端进程（也叫 Postgres 进程）。

# 查看 PG 进程psaux|greppostgres postgres1234postgres: user db127.0.0.1(54321)idle postgres1235postgres: user db127.0.0.1(54322)SELECT postgres1236postgres: user db127.0.0.1(54323)UPDATE waiting

问题：如果应用有 500 个连接，就要创建 500 个进程，内存消耗巨大。

解决方案：使用连接池

• PgBouncer：轻量级连接池，推荐
• Pgpool-II：功能更丰富，支持负载均衡

应用（500连接） → PgBouncer（连接池） → PostgreSQL（50进程）

4.4 Background Workers 后台进程

PG 有多个后台进程负责维护工作：

VACUUM 是什么？

前面说过，PG 更新数据时不删除旧行，而是标记为"死元组"。VACUUM 就是清理这些死元组的进程。

-- 手动执行 VACUUMVACUUM products;-- VACUUM FULL（重建表，会锁表）VACUUMFULLproducts;-- 查看表的死元组数量SELECTrelname,n_dead_tupFROMpg_stat_user_tables;

与 MySQL 的对比：

五、内存架构详解

5.1 共享内存区域

所有后端进程共享的内存区域：

┌─────────────────────────────────────────────────────┐ │ Shared Memory │ ├─────────────────────────────────────────────────────┤ │ ┌─────────────────────────────────────────────┐ │ │ │ Shared Buffer Pool │ │ ← 缓存数据页 │ │ (shared_buffers) │ │ │ └─────────────────────────────────────────────┘ │ │ ┌─────────────────────────────────────────────┐ │ │ │ WAL Buffer │ │ ← WAL 日志缓冲 │ │ (wal_buffers) │ │ │ └─────────────────────────────────────────────┘ │ │ ┌─────────────────────────────────────────────┐ │ │ │ CLOG Buffer │ │ ← 事务状态缓存 │ └─────────────────────────────────────────────┘ │ │ ┌─────────────────────────────────────────────┐ │ │ │ Lock Space / Other │ │ ← 锁信息等 │ └─────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────┘

5.2 Shared Buffer Pool

这是 PG 最重要的内存区域，用于缓存数据页和索引页。

-- 查看 shared_buffers 配置SHOWshared_buffers;-- 推荐设置为物理内存的 25%-- postgresql.confshared_buffers=4GB

工作流程：

后端进程查询数据 ↓ 检查 Shared Buffer Pool ├── 命中 → 直接返回 └── 未命中 → 从磁盘读取 → 放入 Buffer Pool → 返回

与 MySQL Buffer Pool 的区别：

5.3 进程私有内存

每个后端进程还有自己的私有内存：

-- 查看 work_memSHOWwork_mem;-- 默认 4MB-- 大查询可以临时调大SETwork_mem='256MB';SELECT*FROMbig_tableORDERBYcolumn1;

注意：work_mem是每个操作的内存，一个查询可能有多个排序操作，总内存 = work_mem × 操作数 × 连接数。

六、WAL日志机制

6.1 为什么需要 WAL

数据在 Shared Buffer 中更新后，如果还没写入磁盘就崩溃了，数据就丢了。

解决方案：Write-Ahead Logging（预写日志）

• 先写日志，再写数据
• 崩溃后通过日志恢复数据

6.2 WAL 工作流程

1. 事务开始 2. 修改 Shared Buffer 中的数据页 3. 将修改操作写入 WAL Buffer 4. 事务提交时，WAL Buffer 刷入磁盘（WAL 文件） 5. 后台进程异步将脏页刷入磁盘 6. 崩溃恢复时，重放 WAL 日志

为什么先写 WAL 而不是直接写数据页？

• WAL 是顺序写，数据页是随机写
• 顺序写磁盘性能是随机写的几十倍

6.3 WAL 文件结构

$PGDATA/pg_wal/ ├── 000000010000000000000001 ├── 000000010000000000000002 ├── 000000010000000000000003 └── ...

每个 WAL 文件默认16MB。

6.4 与 MySQL Redo Log 的对比

PITR（Point-In-Time Recovery）：

PG 可以通过 WAL 归档实现任意时间点恢复：

# 恢复到指定时间点recovery_target_time='2025-02-15 14:30:00'

七、查询执行流程

7.1 完整流程

┌─────────────────────────────────────────────────────────────┐ │ Backend Process │ ├─────────────────────────────────────────────────────────────┤ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────────┐ │ │ │ Parser │ → │ Analyzer│ → │ Planner │ → │ Executor │ │ │ │ (解析器) │ │ (分析器) │ │ (优化器) │ │ (执行器) │ │ │ └─────────┘ └─────────┘ └─────────┘ └──────┬──────┘ │ │ ↓ │ │ ┌─────────────────┐│ │ │ Access Methods ││ │ │ (访问方法) ││ │ └────────┬────────┘│ └───────────────────────────────────────────────────┼─────────┘ ↓ ┌──────────────────────────────────┐ │ Shared Memory │ │ ┌────────────┐ ┌────────────┐ │ │ │Buffer Pool │ │ WAL Buffer │ │ │ └────────────┘ └────────────┘ │ └──────────────────────────────────┘ ↓ ┌──────────────────────────────────┐ │ Disk │ │ ┌────────────┐ ┌────────────┐ │ │ │ Data Files │ │ WAL Files │ │ │ └────────────┘ └────────────┘ │ └──────────────────────────────────┘

7.2 各模块职责

Parser（解析器）：

• 检查 SQL 语法是否正确
• 生成解析树（Parse Tree）

Analyzer（分析器）：

• 检查表、列是否存在
• 检查权限
• 生成查询树（Query Tree）

Planner/Optimizer（优化器）：

• 选择使用哪个索引
• 选择 Join 方式（Nested Loop、Hash Join、Merge Join）
• 生成执行计划（Plan Tree）

Executor（执行器）：

• 按执行计划调用 Access Methods
• 返回结果

7.3 查看执行计划

EXPLAINANALYZESELECT*FROMproductsWHEREprice>100;QUERYPLAN------------------------------------------------------------------------------------------------------------IndexScanusingidx_product_priceonproducts(cost=0.29..8.31rows=1width=100)(actualtime=0.015..0.016rows=1loops=1)IndexCond:(price>100)PlanningTime:0.080ms ExecutionTime:0.030ms

7.4 Access Methods（访问方法）

Access Methods 是 PG 扩展性的核心，它定义了如何访问不同类型的数据。

Access Methods ├── Heap AM（堆表访问） ├── Index AM（索引访问） │ ├── B-Tree │ ├── GIN │ ├── GiST │ ├── BRIN │ └── Hash └── 自定义 AM（插件扩展）

通过开放 Access Methods 接口，用户可以扩展支持：

• 向量搜索（pgvector）
• 图数据库（Apache AGE）
• 列式存储（Citus）

八、PostgreSQL vs MySQL 深度对比

8.1 架构对比

8.2 功能对比

8.3 SQL 标准兼容性

PostgreSQL 对 SQL 标准的支持更完整：

-- PostgreSQL 支持的高级特性-- 1. 数组操作SELECTARRAY[1,2,3]||ARRAY[4,5];-- {1,2,3,4,5}-- 2. JSONB 操作SELECT'{"name": "test"}'::jsonb->'name';-- "test"SELECT'{"name": "test"}'::jsonb->>'name';-- test（文本）-- 3. 范围类型SELECT'[2025-01-01, 2025-12-31]'::daterange @>'2025-06-15'::date;-- true-- 4. 递归 CTEWITHRECURSIVE subordinatesAS(SELECTid,name,manager_idFROMemployeesWHEREid=1UNIONALLSELECTe.id,e.name,e.manager_idFROMemployees eJOINsubordinates sONe.manager_id=s.id)SELECT*FROMsubordinates;-- 5. 窗口函数SELECTname,salary,RANK()OVER(ORDERBYsalaryDESC)asrank,SUM(salary)OVER()astotalFROMemployees;

8.4 性能对比

读性能：

写性能：

总结：

• 简单 CRUD：MySQL 略快
• 复杂查询：PostgreSQL 更强
• 特殊数据类型：PostgreSQL 完胜

8.5 运维对比

8.6 如何选择

选 MySQL 的场景：

• 团队熟悉 MySQL
• 简单的 CRUD 业务
• 对运维复杂度敏感
• 需要大量社区资源和教程

选 PostgreSQL 的场景：

• 需要复杂查询和分析
• 需要 JSON 文档存储
• 需要地理信息处理
• 需要全文搜索
• 需要时序数据存储
• 追求 SQL 标准兼容
• 需要高度可扩展性

一句话总结：

• MySQL：简单、够用、生态好
• PostgreSQL：强大、全能、天花板

九、总结

9.1 核心架构回顾

9.2 索引能力总结

9.3 与 MySQL 的核心差异

最后一句话：PostgreSQL 是数据库领域的"全干工程师"，一个数据库顶多个中间件。如果你的业务需求复杂多变，PG 绝对是值得投资学习的选择。

热门专栏推荐

• Agent小册
• Java基础合集
• Python基础合集
• Go基础合集
• 大数据合集
• 前端小册
• 数据库合集
• Redis 合集
• Spring 全家桶
• 微服务全家桶
• 数据结构与算法合集
• 设计模式小册
• 消息队列合集

等等等还有许多优秀的合集在主页等着大家的光顾，感谢大家的支持

文章到这里就结束了，如果有什么疑问的地方请指出，诸佬们一起来评论区一起讨论😊
希望能和诸佬们一起努力，今后我们一起观看感谢您的阅读🙏
如果帮助到您不妨3连支持一下，创造不易您们的支持是我的动力🌟