PostgreSQL（PG） 17 都来了，你还在死磕 MySQL 吗？

Hi, Boys，又见面了。

在很多人的印象里，提起开源关系型数据库，MySQL 就像是那个同班里最出名的同学，谁都知道。但其实，班里还有个“学霸”，平时可能不那么张扬，但论内功和全面性，那可是相当能打——它就是 PostgreSQL（我们亲切地称之为 PG）。

这不，PostgreSQL 17 的消息都出来了，带来了更多牛X的特性。如果你还在为 MySQL 的一些限制（比如JSON支持、复杂查询性能）而头疼，或者你只是想看看“隔壁班的学霸”到底有多强，那今天这篇，你可得坐稳了。

一、破除迷思：PG vs. MySQL，不是替代，是“升维”

很多人有个误区，觉得 PG 和 MySQL 是二选一的竞争关系。早年间，MySQL 凭借简单、快速、易于部署的特性，在 Web 1.0 和 2.0 时代抢占了先机。但随着业务越来越复杂，数据结构越来越多样，PG 的优势就体现出来了。

它不仅仅是一个数据库，更是一个“数据管理平台”。

直观的对比：

一句话总结：如果你的业务是简单的 CRUD，读多写少，MySQL 足够快也足够好。但如果你的业务涉及复杂查询、多维数据、高数据一致性要求，或者你想在数据层做更多文章，那 PostgreSQL 绝对是你的“升维”之选。

二、深入龙潭：解剖 PG17 的核心架构

想摸透一个东西，就得看它的骨架。PG 的架构设计堪称经典。

1. 进程架构：一个“餐厅”模型

你可以把 PG 的进程模型想象成一个管理有序的餐厅。

• Postmaster (总管进程)：这就是餐厅的“大堂经理”。它在数据库启动时运行，负责监听客户端的连接请求。

• Backend Process (服务员进程)：每当一个新客户（客户端连接）进来，大堂经理就会派一个专属的“服务员”（也叫 postgres 进程）来为你服务。这个服务员会处理你所有的查询、更新等操作。你和数据库的所有交互，都是和这个进程打交道。连接一断，服务员下班。

• Background Workers (后厨团队)：除了前台的服务员，后厨还有一帮人在默默工作，保证餐厅高效运转。

  • Background Writer (洗碗工)：把厨房里用过的“脏盘子”（内存里被修改过的数据页）在后台不紧不慢地刷回“碗柜”（磁盘）。

  • WAL Writer (记账员)：你点的每一道菜，服务员都会先在 WAL (Write-Ahead Log) 这个小本本上记一笔，WAL Writer 专门负责把这个账本快速写入磁盘。这样就算突然停电，账本还在，重启后就能恢复。

  • Checkpointer (盘点员)：定期对整个餐厅的资产（数据）进行一次“盘点”，确保内存和磁盘的数据状态同步到一个一致的时间点，这能大大加快宕机后的恢复速度。

  • Autovacuum Launcher (保洁员)：PG 的并发控制（MVCC）会产生一些“过期”的数据版本（后面会讲），这个保洁员就负责定期巡视，把这些垃圾清理掉。

2. 内存架构：大脑如何划分区域

• Shared Buffers (共享缓冲区)：这是 PG 大脑里最大的一块内存区域，是所有进程共享的“数据缓存区”。从磁盘读取的数据页（Data Page）都会先放在这里，后续的读写操作都直接在这里进行，大大减少了磁盘 I/O。这是 PG 性能调优的核心参数。

• WAL Buffers (日志缓冲区)：专门存放 WAL 记录的缓冲区。写操作先在这里完成，然后由 WAL Writer 批量刷到磁盘，保证了日志写入的高效性。

• work_mem (工作内存)：这是每个“服务员进程”自己私有的内存。当需要进行排序（ORDER BY）、哈希连接等复杂操作时，服务员会在这块“草稿纸”上进行计算。如果草稿纸不够大，就只能用磁盘来临时存放，性能会急剧下降。

• maintenance_work_mem (维护工作内存)：专门给 VACUUM（清理）、CREATE INDEX（创建索引）等维护操作使用的“大号草稿纸”。

三、神兵利器：PG 的高级 SQL 与索引艺术

PG 的强大，很大程度上体现在它强大的 SQL 能力和索引策略上。

1. 不只是 SELECT *：玩转高级 SQL

• 窗口函数 (Window Functions)：想在不 GROUP BY 的情况下，计算分组排名、移动平均值？窗口函数是你的超能力。

  -- 查询每个部门工资最高的前三名员工
  SELECT name, department, salary
  FROM (
    SELECT
      name,
      department,
      salary,
      ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) as rn
    FROM employees
  ) tmp
  WHERE rn <= 3;
  
  

• 公用表表达式 (CTE - Common Table Expressions)：让你的复杂 SQL 像写文章一样清晰。特别是递归查询，可以轻松处理树状结构数据。

  -- 查询某个员工及其下属所有员工
  WITH RECURSIVE subordinates AS (
    SELECT employee_id, name, manager_id FROM employees WHERE employee_id = 101
    UNION
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON s.employee_id = e.manager_id
  )
  SELECT * FROM subordinates;
  
  

• 无敌的 JSONB 操作：这绝对是 PG 的王牌。MySQL 的 JSON 类型只是个文本，而 PG 的 JSONB 是二进制格式，不仅存取效率高，更有强大的函数和操作符支持，并且可以被高效地索引。

  -- 假设有个 products 表，其中有个 attributes 字段是 JSONB 类型
  -- {"color": "red", "tags": ["new", "sale"], "stock": {"warehouse_a": 100}}
  
  -- 查询所有红色的商品
  SELECT name FROM products WHERE attributes ->> 'color' = 'red';
  
  -- 查询所有包含 'sale' 标签的商品
  SELECT name FROM products WHERE attributes -> 'tags' @> '["sale"]';
  
  -- 查询 A 仓库库存大于 50 的商品
  SELECT name FROM products WHERE (attributes -> 'stock' ->> 'warehouse_a')::int > 50;
  
  

2. 索引的艺术：对症下药

PG 提供了多种索引类型，让你能为不同的查询场景“量身定制”最高效的索引。

• B-Tree：万金油，默认索引。适用于各种比较操作（=, >, <, BETWEEN, IN）。

• GIN (广义倒排索引)：专门为“包含”操作而生。是 JSONB、数组、全文检索等场景下的不二之选。

  -- 为 JSONB 字段的 'tags' 创建 GIN 索引
  CREATE INDEX idx_products_tags ON products USING GIN ((attributes -> 'tags'));
  
  

  有了这个索引，上面那个查询 sale 标签的语句，速度会提升几个数量级。

• GiST (广义搜索树)：处理更复杂的数据类型，比如地理位置数据（二维查询），实现“查找我附近 1 公里内的餐厅”这类需求。

• 部分索引 (Partial Indexes)：只对表中的一部分数据建立索引。当你的查询条件很固定时，这能极大地节省索引空间，并提高查询和更新效率。

  -- 只为未处理的订单创建索引
  CREATE INDEX idx_orders_unprocessed ON orders (order_date) WHERE status = 'pending';
  
  

四、并发之魂：深入理解事务与 MVCC

为什么 PG 在高并发读写下表现如此稳健？秘诀就是 MVCC (多版本并发控制)。

简单来说，当你要修改一行数据时，PG 不会直接在老数据上改（或者加锁），而是创建一个新版本的数据行，并把旧版本标记为“过期”。

• 读操作：读请求只会看到在它启动之前就已经提交的、最新的数据版本，完全不会被正在进行的写操作阻塞。

• 写操作：写操作也一样，它只和自己打交道，不会阻塞别人。

“读者不阻塞写者，写者不阻塞读者”，这就是 MVCC 的精髓。

但这也带来了新的问题：会产生很多“过期”的数据行（死元组），占用磁盘空间。这就是前面提到的 Autovacuum “保洁员”需要登场的原因，它负责回收这些空间。所以，理解并正确配置 VACUUM 对维持 PG 的健康至关重要。

五、终极实战：Spring Boot 3 + PostgreSQL 17

理论说了这么多，上手写代码才是王道。我们用 Spring Boot 3 来连接 PG17，并玩一下 JSONB 类型。

1. 环境准备

pom.xml 添加依赖:

<dependencies>
    <!-- Spring Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- PostgreSQL Driver -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!-- 为了方便处理 JSONB，引入 hypersistence-utils 库 -->
    <dependency>
        <groupId>io.hypersistence</groupId>
        <artifactId>hypersistence-utils-hibernate-63</artifactId>
        <version>3.8.1</version> <!-- 请使用最新版本 -->
    </dependency>
</dependencies>

application.properties 配置:

# PostgreSQL Datasource
spring.datasource.url=jdbc:postgresql://localhost:5432/your_db
spring.datasource.username=your_user
spring.datasource.password=your_password
spring.datasource.driver-class-name=org.postgresql.Driver

# JPA/Hibernate Settings
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
# 指定 JSONB 类型处理器
spring.jpa.properties.hibernate.type.json_format_mapper=io.hypersistence.utils.hibernate.type.json.JacksonJsonFormatMapper

2. 代码实现

定义实体类 Product.java:

import io.hypersistence.utils.hibernate.type.json.JsonType;
import jakarta.persistence.*;
import org.hibernate.annotations.Type;

import java.util.Map;

@Entity
@Table(name = "products")
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    // 使用 @Type 注解将 Map<String, Object> 映射到 jsonb 类型
    @Type(JsonType.class)
    @Column(columnDefinition = "jsonb")
    private Map<String, Object> attributes;

    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public Map<String, Object> getAttributes() { return attributes; }
    public void setAttributes(Map<String, Object> attributes) { this.attributes = attributes; }
}

创建 ProductRepository.java:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    /**
     * 使用原生 SQL 查询 JSONB 字段
     * 查询所有包含指定 tag 的产品
     * 注意：这里的 ?1 是 JPQL 的占位符，但在 nativeQuery 中，我们用 :tagName 这种命名参数更清晰
     * @param tag 要搜索的标签
     * @return 产品列表
     */
    @Query(
        value = "SELECT * FROM products WHERE attributes -> 'tags' @> CAST(CAST(:tag AS text) as jsonb)",
        nativeQuery = true
    )
    List<Product> findByTag(@Param("tag") String tag);

    /**
     * 查询指定属性key的值等于value的商品
     * @param key 属性键
     * @param value 属性值
     * @return 产品列表
     */
    @Query(
        value = "SELECT * FROM products WHERE attributes ->> :key = :value",
        nativeQuery = true
    )
    List<Product> findByAttribute(@Param("key") String key, @Param("value") String value);
}

创建 ProductController.java:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/api/products")
public class ProductController {

    @Autowired
    private ProductRepository productRepository;

    @PostMapping
    public Product createProduct(@RequestBody Product product) {
        return productRepository.save(product);
    }

    @GetMapping("/search/tag")
    public List<Product> searchByTag(@RequestParam String tag) {
        // 为了适配SQL查询，需要将 "sale" 变成 '["sale"]'
        return productRepository.findByTag(String.format("[\"%s\"]", tag));
    }

    @GetMapping("/search/attribute")
    public List<Product> searchByAttribute(@RequestParam String key, @RequestParam String value) {
        return productRepository.findByAttribute(key, value);
    }
}

现在，你可以启动你的 Spring Boot 应用，然后通过 Postman 或浏览器来测试接口了。比如，POST 一个新的产品数据到 /api/products，再通过 GET /api/products/search/tag?tag=sale 来体验强大的 JSONB 查询。

结论：是时候抱抱 PostgreSQL 了

聊了这么多，相信你对 PostgreSQL 已经有了一个全新的、立体的认识。

它不是一个“更好”的 MySQL，而是一个在设计哲学上就有所不同的、功能更全面的数据平台。它用强大的功能、严格的一致性和极高的扩展性，为你处理复杂业务数据提供了无限可能。

随着云原生和微服务的深入，对数据处理能力的要求只会越来越高。PostgreSQL 凭借其与生俱来的优势，无疑会在这个时代扮演越来越重要的角色。

SO，别犹豫了。在你的下一个项目中，不妨给 PostgreSQL 一个机会。