跳至主要内容

PostgreSQL与MySQL的主要区别和架构介绍

PostgreSQL 入门指南：写给熟悉 MySQL 的开发者

本文面向已经掌握 MySQL 的读者，目的是快速建立 PostgreSQL 的知识框架，内容分四部分：基础用法差异、内部架构对比、索引机制对比、数据存储与流动过程。

一、基础用法差异

1.1 连接与客户端

MySQL PostgreSQL
命令行客户端 mysql -u root -p psql -U postgres -d mydb
默认端口 3306 5432
认证配置 mysql.user 表 pg_hba.conf，与角色系统分离

1.2 数据类型差异

用途 MySQL PostgreSQL
自增主键 AUTO_INCREMENT GENERATED ALWAYS AS IDENTITY（新写法）或 SERIAL（旧写法，底层是 SEQUENCE）
布尔值 常用 TINYINT(1) 模拟 原生 BOOLEAN
JSON JSON JSON 和 JSONB（二进制存储、可建索引，通常首选）
数组 不支持，需用逗号拼接字符串或 JSON 原生 ARRAY 类型，如 INT[]
枚举 ENUM('a','b') 需 CREATE TYPE ... AS ENUM，或改用 CHECK 约束
UUID CHAR(36) 或 BINARY(16) 原生 UUID 类型

1.3 标识符大小写规则（重要易错点）

  • MySQL：表名大小写敏感性取决于操作系统（Linux 敏感、Windows 不敏感），列名一般不敏感。
  • PostgreSQL：所有未加双引号的标识符都会被自动转成小写。SELECT * FROM MyTable 等价于 select * from mytable。如果建表时用双引号保留了大小写（如 "MyTable"），之后每次引用都必须加双引号，否则会报"关系不存在"的错误。
  • 字符串必须用单引号；双引号在 PostgreSQL 中专门用于标识符，不能像 MySQL 那样偶尔"双引号当字符串用"。

1.4 自增主键写法对比

-- MySQL
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

-- PostgreSQL（推荐新写法）
CREATE TABLE users (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(50)
);

1.5 UPSERT（插入或更新）

-- MySQL
INSERT INTO t (id, val) VALUES (1, 'a')
ON DUPLICATE KEY UPDATE val = 'a';

-- PostgreSQL
INSERT INTO t (id, val) VALUES (1, 'a')
ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;

1.6 分页与存储引擎

  • 分页语法两者基本一致：LIMIT n OFFSET m，PostgreSQL 还支持标准 SQL 的 FETCH FIRST n ROWS ONLY。
  • MySQL 允许按表选择存储引擎（InnoDB / MyISAM / Memory...），PostgreSQL 没有这个概念——所有表统一用同一套堆表机制存储，事务与崩溃恢复由内建的 WAL 统一保障（详见第二部分）。

1.7 权限体系

MySQL 的用户是"用户名@主机"的组合；PostgreSQL 的角色（ROLE）与主机无关，登录来源限制单独写在 pg_hba.conf 里，思路更接近 Linux 的用户组模型。

二、架构对比

2.1 进程模型 vs 线程模型

  • MySQL：单个 mysqld 进程内部用多线程处理每个连接，线程间共享内存（如 InnoDB Buffer Pool）。
  • PostgreSQL：一个 postmaster 主进程，为每个新连接 fork 出一个独立的操作系统进程，进程间通过共享内存（shared_buffers）通信。

影响：fork 进程比创建线程更"重"，所以 PostgreSQL 生产环境通常需要连接池（如 PgBouncer）来应对大量短连接；MySQL 对短连接相对更宽容。

2.2 存储引擎：插件式 vs 统一

MySQL 的 SQL 层和存储引擎层是分离、可插拔的架构；PostgreSQL 没有可插拔存储引擎的概念，读写路径、MVCC、WAL 都是内建且统一的。

2.3 MVCC 实现方式的根本不同（重点）

  • MySQL（InnoDB）：采用"回滚段 + undo log"实现 MVCC。数据行本身只保留最新版本，旧版本记录在 undo log 里，需要时通过隐藏事务 ID 重建历史快照，用完后由后台 purge 线程自动清理。
  • PostgreSQL：采用"多版本堆元组"。每次 UPDATE/DELETE 不是原地修改，而是在堆表里追加一份新版本行，旧版本被标记失效但仍物理留在表文件中，不会自动消失。

这个差异带来了 PostgreSQL 特有的运维环节——VACUUM，是 MySQL 背景的人转过来后最需要重新建立的认知，详见第四部分。

2.4 WAL（预写日志）

两者都是"先写日志再改数据"来保证崩溃恢复（MySQL 中叫 redo log），思路类似。PostgreSQL 的 WAL 同时也是流复制（streaming replication）的基础，复制体系相比 MySQL 的 binlog 复制，配置方式不同但机制更统一。

三、索引对比

3.1 聚簇索引 vs 堆表（最大差异）

  • MySQL（InnoDB）：主键是聚簇索引——数据行本身按主键顺序物理存储在 B+ 树叶子节点中。任何二级索引存的是"索引列值 + 主键值"，查询二级索引后还需回表，用主键再查一次聚簇索引拿完整行。
  • PostgreSQL：采用堆表（heap）结构，数据行按插入或整理后的顺序存放，不按任何键排序。所有索引（包括主键索引）都是非聚簇的，索引里存的是指向堆中物理位置的指针（TID：页号 + 槽位号）。查询任何索引找到 TID 后，都要再去堆表取一次真实数据。

影响：MySQL 按主键范围查询天然更快（数据物理有序）；PostgreSQL 的主键查询和普通索引查询在 I/O 模式上没有本质区别。

3.2 索引类型丰富程度

索引类型 MySQL（InnoDB） PostgreSQL
B-tree 支持（默认） 支持（默认）
Hash 仅 Memory 引擎 支持，适合等值查询
全文检索 FULLTEXT（功能较弱） GIN + tsvector，更强大灵活
JSON/数组索引 有限 GIN 原生支持 JSONB/数组
空间索引 SPATIAL GiST/SP-GiST，配合 PostGIS 生态成熟
大表范围扫描优化 无直接对应 BRIN 索引，体积极小，适合时序/日志类大表
部分索引 不支持 支持 CREATE INDEX ... WHERE 条件
表达式索引 有限支持 原生支持，如 CREATE INDEX ON t (lower(email))

3.3 覆盖索引与 Index-Only Scan

两者都支持覆盖索引以避免回表，但 PostgreSQL 的 Index-Only Scan 依赖一个叫 visibility map 的结构来判断页面是否所有行都对当前事务可见。如果表更新频繁而 VACUUM 不及时，Index-Only Scan 可能退化为普通扫描——这与下面的 VACUUM 机制直接相关。

四、数据存储与流动过程

4.1 MySQL（InnoDB）写入流程

  1. 客户端发起 UPDATE/INSERT
  2. 在 Buffer Pool 中修改内存里的数据页
  3. 同时写 redo log（先写 log buffer 再刷盘，保证崩溃后可重放）
  4. 事务场景下同时写 undo log，用于回滚和 MVCC
  5. 事务提交时 redo log 刷盘（fsync），此时数据页可能仍是"脏页"
  6. 后台 page cleaner 线程异步把脏页刷回磁盘的表空间文件（.ibd）
  7. 数据物理上按主键顺序存放在聚簇索引 B+ 树的叶子节点中

4.2 PostgreSQL 写入流程

  1. 客户端发起 UPDATE/INSERT
  2. 先写 WAL 记录本次修改
  3. 修改 shared_buffers 中的数据页；若是 UPDATE，并非原地覆盖，而是在堆表中追加一份新版本行，旧行标记为 dead
  4. 相关索引需要插入新条目，指向新行的 TID
  5. 事务提交时 WAL 刷盘（fsync），保证持久性
  6. 后台 background writer / checkpointer 定期把脏页刷到磁盘的堆文件
  7. 旧版本的"死元组"不会自动消失，占用的空间需要 VACUUM 回收

4.3 VACUUM：PostgreSQL 特有的运维环节

这是 MySQL 背景中完全没有对应概念、必须重新学习的部分：

  • 普通 VACUUM：把死元组占用的空间标记为"可重用"，但不会归还给操作系统，也不整理紧凑。
  • VACUUM FULL：重写整张表，真正回收磁盘空间，但会加排他锁，期间不可读写。
  • autovacuum：PostgreSQL 默认开启的后台自动清理进程，按阈值触发；生产环境几乎必须依赖它，大表、高频更新场景常需手工调优参数。
  • 事务 ID 回卷（wraparound）风险：长期不 VACUUM，内部 32 位事务 ID 可能耗尽，导致数据库被迫停机保护——这是 PostgreSQL 特有且必须了解的高危运维知识点。

4.4 写入路径对比小结

环节 MySQL（InnoDB） PostgreSQL
更新方式 原地更新 + undo log 记录旧版本 追加新版本行，旧版本留在表中
数据物理组织 按主键聚簇存储 堆表，按插入/整理顺序存储
崩溃恢复日志 redo log WAL
旧版本清理 后台 purge 线程清理 undo log，自动无感 autovacuum 清理死元组，需关注和调优
空间膨胀风险 较低 较高（表膨胀 bloat 是常见运维话题）

五、总结：迁移时最该注意的三件事

  1. 大小写和引号规则——标识符不加引号一律转小写。
  2. 没有聚簇索引——所有索引都要回表，不能假设主键查询有特殊物理优势。
  3. VACUUM / autovacuum 不是可选项——这是 PostgreSQL 的 MVCC 模型带来的必然运维成本，必须纳入日常监控。

六、复制架构对比

MySQL：基于 binlog 的逻辑复制，binlog 格式有 STATEMENT/ROW/MIXED 三种。主从之间通过 IO 线程 + SQL 线程（或并行复制）应用 binlog 事件，支持半同步复制（semi-sync），GTID（全局事务 ID）简化了故障切换和多源复制的位点管理。高可用方面有官方的 Group Replication / InnoDB Cluster，属于"开箱即用"。

PostgreSQL：核心是基于 WAL 的流复制（streaming replication），从库直接重放 WAL 日志，接近字节级复制，通过 synchronous_commit / synchronous_standby_names 控制同步级别。10 版本后原生支持逻辑复制（基于 PUBLICATION/SUBSCRIPTION，思路类似 MySQL 的 binlog 复制，可选表级复制、跨版本复制）。但 PostgreSQL 没有内建的多主/自动故障切换方案，高可用通常依赖 Patroni、repmgr、pg_auto_failover 等外部工具。

MySQL PostgreSQL
复制基础 binlog（逻辑） WAL（物理为主，逻辑复制为辅）
内建高可用 Group Replication / InnoDB Cluster 无，依赖 Patroni 等外部工具
复制粒度 可到库/表级 物理复制是整实例级；逻辑复制可到表级
故障切换 GTID + 自动化工具（MHA、Orchestrator） 需外部协调器（如 Patroni）

七、分区表对比

MySQL：原生支持 RANGE、LIST、HASH、KEY 分区，每个分区在 InnoDB 下本质是独立物理表，对用户呈现为单一逻辑表；早期版本对分区表有不少限制（如外键不支持）。

PostgreSQL：10 版本后引入声明式分区（declarative partitioning），支持 RANGE、LIST、HASH。每个分区本质是一张独立的子表，可以单独建索引、单独设置存储参数，也可以对某个分区单独 VACUUM。常见做法是按时间做 RANGE 分区处理日志/时序数据，定期 DROP 旧分区来清理历史数据——这比 DELETE 快得多，还能避免产生大量死元组，间接减轻了 VACUUM 压力。

八、常见性能调优参数对比

MySQL 关键参数： - innodb_buffer_pool_size：核心缓存，通常设为物理内存的 50%–70% - innodb_log_file_size：redo log 大小，影响崩溃恢复时间和写入吞吐 - innodb_flush_log_at_trx_commit：控制 redo log 刷盘策略，1 最安全但最慢 - max_connections：线程模型下可以设置得相对较大

PostgreSQL 关键参数： - shared_buffers：通常建议为物理内存的 25% 左右——不能照搬 MySQL 的高比例，因为 PostgreSQL 还严重依赖操作系统的 page cache 做二级缓存 - work_mem：每个查询的排序/哈希操作可用内存，设置不当容易导致临时文件溢出磁盘，设置过大又可能在高并发下 OOM - maintenance_work_mem：VACUUM、CREATE INDEX 等维护操作使用的内存 - effective_cache_size：告诉查询优化器操作系统缓存大致有多大，只影响执行计划选择，不是实际内存分配 - max_connections：因为是进程模型，不建议设太大，生产环境通常配合连接池（PgBouncer）使用 - autovacuum_vacuum_scale_factor / autovacuum_vacuum_threshold：控制 autovacuum 触发阈值，大表场景经常需要调小，否则死元组清理跟不上写入速度

九、PostgreSQL 相比 MySQL 的核心优势

（为什么复杂业务或大型项目会优先选择它）

  1. 更强的数据完整性和高级 SQL 能力：更早支持窗口函数、递归 CTE、FULL OUTER JOIN；支持真正的可串行化快照隔离（Serializable Snapshot Isolation），事务隔离级别实现更严谨，适合金融、库存等对一致性要求极高的系统。
  2. 数据类型和可扩展性更丰富：JSONB、原生数组、范围类型、自定义复合类型，让很多原本需要在应用层拼凑的逻辑可以下沉到数据库层；支持多语言写存储过程（PL/pgSQL、PL/Python 等）。
  3. 扩展生态是杀手级优势——"核心稳定 + 插件按需扩展"的模式让适用场景远比 MySQL 广：
    • PostGIS：地理空间数据的事实标准
    • pgvector：向量检索，AI 应用场景可以省掉单独部署向量数据库
    • TimescaleDB：时序数据的成熟方案
    • FDW（外部数据源）：像查本地表一样查询其他数据库
  4. 索引类型更丰富：GIN、GiST、BRIN 分别针对全文检索、地理数据、超大表范围扫描做了优化，MySQL 基本没有对应物。
  5. 开源治理更独立：License 接近 BSD，背后没有单一商业公司控制版本路线；MySQL 被 Oracle 收购后历史上出现过企业版/社区版功能分裂的争议（这也是 MariaDB 分叉的直接原因）。

但这不代表 MySQL 落后：简单读多写少的 Web 应用、追求极简主从复制、依赖成熟云托管方案的场景，MySQL 往往上手更快、运维更省心；PostgreSQL 的连接开销（进程模型）和 VACUUM 运维成本是它"重"的一面。更准确的说法是：涉及复杂查询、非结构化数据（地理/JSON/向量）、或对一致性要求极高时，PostgreSQL 的技术天花板更高；追求简单直接、生态成熟省心的场景，MySQL 依然扎实。

十、pgvector：把 PostgreSQL 变成向量数据库

pgvector 是一个开源扩展，给 PostgreSQL 添加了 vector 列类型和相似度检索算子（余弦距离、L2 距离、内积等），向量字段可以和业务字段放在同一张表里查询：

create extension if not exists vector;

create table documents (
  id bigserial primary key,
  content text,
  embedding vector(1536)
);

索引类型： - IVFFlat：建索引快，适合中小规模（百万行以内）数据集 - HNSW：建索引慢但查询快，超过百万行规模时查询延迟通常比 IVFFlat 低 3~10 倍，代价是构建时间和内存占用更高

优势：向量数据和关系数据在同一实例，备份、权限、事务、故障恢复全部复用 PostgreSQL 原有体系，尤其适合"先按业务条件过滤，再做向量排序"的场景，一条 SQL 就能完成，不需要额外维护一套独立基础设施。

局限：十亿级向量规模、分布式水平扩展、多租户强隔离场景，专用向量数据库（Pinecone、Milvus、Weaviate 等）的工程成熟度通常仍有优势。经验规律：千万级向量以内 pgvector 够用，超大规模或极致性能要求场景仍建议专用方案。

十一、混合检索：稀疏向量 + 稠密向量、BM25 + 向量

  • 稀疏 + 稠密向量：pgvector 从 0.7 版本起同时提供 vector（稠密）和 sparsevec（稀疏）两种类型，各自可建索引、查询，但融合排序不是内置功能，需要自己在 SQL 里实现。
  • 关于 BM25 的常见误解：PostgreSQL 原生全文检索（tsvector/ts_rank/ts_rank_cd）用的是类 TF-IDF 排序，并不是真正的 BM25 算法。要用严格 BM25，需要额外扩展，如 ParadeDB 的 pg_search（底层用 Rust 的 tantivy 引擎）。
  • 混合检索的通行实现方式：用 CTE 分别算出稠密检索和关键词/BM25 检索的排名，再用 RRF（Reciprocal Rank Fusion）公式加权合并，可以在一条 SQL 里完成，不需要应用层二次拼接：
WITH dense AS (
  SELECT id, RANK() OVER (ORDER BY embedding <=> :query_vec) AS r
  FROM documents ORDER BY embedding <=> :query_vec LIMIT 50
),
sparse AS (
  SELECT id, RANK() OVER (ORDER BY ts_rank_cd(content_tsv, q) DESC) AS r
  FROM documents, plainto_tsquery('simple', :query_text) q
  WHERE content_tsv @@ q
  ORDER BY ts_rank_cd(content_tsv, q) DESC LIMIT 50
)
SELECT COALESCE(d.id, s.id) AS id,
       COALESCE(1.0/(60+d.r),0) + COALESCE(1.0/(60+s.r),0) AS score
FROM dense d FULL OUTER JOIN sparse s ON d.id = s.id
ORDER BY score DESC LIMIT 10;

这种融合方式能有效解决纯语义检索漏掉精确关键词匹配的问题（比如搜索具体版本号、专有名词），配合重排序技术通常能让检索准确率提升 15%~30%。目前也出现了专门做这件事的扩展（如 pgedge-vectorizer 把 BM25 生成内置进数据库、和稠密向量一起用 RRF 融合），但整体上还没有"开箱即用"的原生混合索引类型，比部分原生支持 hybrid search 的专用向量数据库要更"手动"一些。

此博客中的热门博文

Elasticsearch 读写原理指南

### 1. 什么是 segment，里面装了什么？ 在 Lucene（也是 Elasticsearch）里，索引被切分成若干 **segment（段）**，每个 segment 是一个完整的、只读的倒排索引单元。一个 segment 包含： * **倒排词典** —— 用 **FST（Finite‑State Transducer）** 以高度压缩的形式保存每个字段出现的所有 term 以及 term→ord 的映射。对应的磁盘文件是 `*.tim`（新版）或 `*.tis/*.tii`（旧版）。 * **倒排列表（postings）** —— 保存每个 term 出现的文档 ID、频次、位置信息等，文件名通常是 `*.doc`、`*.pos`、`*.pay`。 * **存储字段**（_source、store:true 的字段）—— 以二进制块的形式写入 `*.fdt` / `*.fdx`。 * **doc‑values、norms、向量** 等辅助结构，分别保存在 `*.dv`、`*.norm`、`*.tv` 等文件里。 * **deleted‑docs bitmap**（`*.del`），标记哪些文档已被删除或被更新。 所有这些文件在 segment **写入磁盘后即成为只读**，后续的查询只能读取，永远不会在原文件上进行增删改。 --- ### 2. 原始文档和 FST 为什么都在 segment 里？ * **原始文档**：Elasticsearch 默认把完整的 JSON（_source）以及任何 `store:true` 的字段写入 segment 的 `*.fdt/*.fdx` 文件。每个 segment 保存自己的那部分文档，旧的 segment 在合并前仍然保留，直到合并后被删除。 * **FST**：每个字段的词典在每个 segment 中单独维护，采用 FST 进行前缀共享和字节压缩。这样即使同一个 term 在多个 segment 中出现，也会在每个 segment 里拥有独立的映射，查询时只需要在对应 segment 的 FST 中定位即可。 --- ### 3. 查询时到底是怎么遍历 segment 的？ 1. **请求入口**      客户端的搜索请求先到达 **协调节点**，协调节点把请求 ...
阅读全文

LLM缓存详解

 可以把“大模型缓存”理解成： 把已经算过的结果（或中间结果）存下来，下次尽量复用 。但这里面其实分几层，不只是简单的“问题→答案”缓存。 1️⃣ 常见的几种缓存类型 （1）KV Cache（推理内部缓存） Transformer 在生成时，会把前面 token 的 Key/Value 向量 缓存下来。 本质：避免重复计算 attention 作用： 同一请求内部加速 特点： 👉 只对“同一上下文继续生成”有效 👉 不跨用户、不跨请求 这类缓存是你体感“流式输出越来越快”的原因之一。 （2）Prompt Cache（提示词缓存） 缓存的是： 相同（或高度相似）的 prompt → 对应的中间表示 / 输出 典型场景： 系统提示词（system prompt）很长 多轮对话里前文基本不变 👉 这里能省掉 前缀计算成本（prefill） （3）Embedding / 语义缓存（Semantic Cache） 这个才是你问题的关键 👇 不是按“字符串完全一致”，而是： 把问题转成向量 → 找“语义相似”的历史问题 → 直接复用答案 2️⃣ 为什么命中缓存成本低很多？ 因为大模型推理成本主要在两块： （1）Prefill（吃 prompt） 复杂度 ~ O(n²) 很贵（尤其长 prompt） （2）Decode（逐 token 生成） 每个 token 都要算一遍模型 而缓存命中后： KV cache：不用重复 attention Prompt cache：不用重新 encode 语义缓存： 直接跳过模型推理 👉 相当于从： 几十~几百毫秒 + GPU算力 变成： 一次向量检索（毫秒级）+ 直接返回 所以成本差一个数量级是正常的。 3️⃣ “每个人问法不同，怎么命中缓存？” 这是核心难点，也是工程重点👇 ❌ 不能靠字符串匹配 比如： “今天天气怎么样” “今天外面热不热” 字符串完全不同 → 必须 miss ✅ 用语义相似度（Embedding） 流程一般是： 把问题转 embedding（向量） 在向量数据库里找 TopK 相似问题 如果相似度 > 阈值（比如 0.9） 直接返回缓存答案 一个简单示意 Q1: 北京天气怎么样 → embedding A Q2: 北京今天热吗 → embedding B cosine(A, B) ≈ 0.95...
阅读全文

事务的ACID是什么

 事务的 ACID 是数据库事务必须满足的四个基本性质，用来保证在并发和故障情况下数据的正确性与可靠性： A（Atomicity，原子性） 一个事务中的操作要么 全部成功 ，要么 全部失败回滚 ，不存在“只做了一半”的中间状态。 C（Consistency，一致性） 事务执行前后，数据库都必须处于 一致的合法状态 ，满足约束（如主键、外键、唯一性、业务规则等）。 I（Isolation，隔离性） 并发执行的多个事务之间 相互隔离 ，一个事务未提交的中间结果对其他事务不可见（具体强弱由隔离级别决定）。 D（Durability，持久性） 一旦事务提交成功，其结果会被 永久保存 ，即使系统崩溃也不会丢失（通常依赖 WAL/redo log 等机制）。 一句话记忆： 要么全做完、前后不破坏规则、互不干扰、做完不丢。
阅读全文