当前位置：首页 > news >正文

[关系型数据库] PostgreSQL

news 2026/7/26 18:26:07

0 序

近两天捣鼓群晖 NAS，发现其内置数据库是 PostgreSQL 数据库（11.11 版本）。
为此，在把玩了一下该数据库后，在此简单总结一下该数据库。

1 概述：PostgreSQL

产品定位

PostgreSQL

https://www.postgresql.org/

PostgreSQL 是开源对象关系型数据库（ORDBMS），被业内叫"开源界的 Oracle"。

既能扛 OLTP 业务系统（事务、订单、用户中心等）

又能写复杂 OLAP 查询（窗口函数、CTE、递归）

还可通过扩展变成：文档库（JSONB）、空间库（PostGIS）、向量库（pgvector）

许可：类 BSD 的 PostgreSQL License，商用/改源码/闭源分发都自由。

对大数据开发者的定位："【业务系统】到【数据湖】之间的【可信数据底座】 + 【轻量数仓】 + 【AI 向量附属存储】"。

优劣点

优势

SQL 标准兼容度极高（SQL:2023 核心特性覆盖 ~170/177），窗口函数/CTE/MERGE 原生支持
真 ACID + MVCC（2001 起），高并发读写不脏读
扩展机制无敌：pgvector（AI）、PostGIS（地图）、TimescaleDB（时序）、FDW（跨源外表）
类型丰富：JSONB、数组、UUID、枚举、范围类型
云与托管成熟： RDS/Aurora PG、Cloud SQL、AlloyDB、Azure PG、Neon、Supabase

劣势

默认行存，大宽表 OLAP 性能不如 ClickHouse/Doris/StarRocks
连接数 = 进程数，高并发短连接需配 PgBouncer 连接池
调优项多（autovacuum、shared_buffers、work_mem），新手易"跑得慢怪 PG"
社区版缺原生 TDE/审计脱敏，企业级合规要靠 EDB/云厂商补

诞生背景与研发团队

起源：1986 年 UC Berkeley 由 Michael Stonebraker（图灵奖得主）主导的 POSTGRES 项目，受 DARPA/NSF 资助，初衷是超越早期 Ingres，支持抽象数据类型与复杂对象。
1994 年 Andrew Yu & Jolly Chen 加入 SQL 解释器 → Postgres95
1996 年更名 PostgreSQL 6.0，转向 SQL 标准 + 社区驱动
现维护方：PostgreSQL Global Development Group（PGDG），全球志愿者核心组 + 各云厂/EDB/2ndQuadrant 等商业公司共建，每年一个大版本，约 5 年支持周期

版本发展沿革

1989 POSTGRES 4.2 外发 → 1996 PG 6.0（定名）
8.0（2005）：原生 Win + 完善 MVCC/ACID
9.0（2010）：流复制；9.6（2016）：并行查询
10（2017）：逻辑复制 + 原生分区；
12（2019）：分区/索引优化
14~16：高并发 vacuum、并行 DML、逻辑复制增强
17（2024）/ 18（2025-09 大版本，2026-02 出 18.3）：新 wire 协议、逻辑复制与优化器再提速

特别注意：生产环境尽量 ≥ PG 14，云上直接用托管最新大版本。

竞品对比（Oracle / MySQL / PG / Doris）

维度	Oracle	MySQL	PostgreSQL	Apache Doris
定位	商业企业级 OLTP	互联网轻量 OLTP	开源全能 ORDBMS	MPP 实时数仓
协议	商业收费	双协议(社区开源)	BSD 类自由开源	Apache 2.0
SQL 标准	高但有私有语法	中等(~70%)	极高(~90%+)	MySQL 语法兼容+分析扩展
复杂查询	强	一般	强(窗口/CTE/递归)	强(列存/向量化)
扩展生态	封闭	中等	极强(pgvector等)	中等(向量/湖仓加速)
典型场景	银行核心/ERP	Web 业务/CMS	业务系统+轻数仓+AI底座	报表/日志/广告/OLAP
大数据领域的扮演角色	源系统	源系统	贴源层/维表/向量库	数仓查询引擎

DB-Engines 2025-2026 综合热度：

Oracle #1(≈1132)、MySQL #2(≈846)、SQL Server #3、PostgreSQL #4(≈650-688，分数持续上涨)、Doris 在关系型总榜外单列（MPP 细分）。

Roadmap 衍化方向（2025+）

AI 原生化：pgvector 持续增强（HNSW 索引、StreamingDiskANN、量化），PG 内核考虑向量类型一等公民
云原生存算分离：Neon 类分支、AlloyDB Omni 本地 K8s 部署
Lakehouse 外表：通过 FDW/Iceberg 外表直查数据湖（EDB Analytics Accelerator 等）
运维自动化：逻辑复制双向、增量备份更轻、AI 调优建议（如 AlloyDB AI 自然语言转 SQL）

市占率与趋势

DB-Engines 流行度：稳居全球第 4、开源关系型第 2（仅次于 MySQL），但分数增速第一梯队
Stack Overflow 2025 开发者调查：使用率 55.6% 排所有数据库第一，超 MySQL 40.5%
大数据行业：在"湖仓一体+BI 贴源层+特征表+RAG 知识库"场景渗透率快速超 MySQL

AI 与大数据领域的定位 *

PG 不是"替代 Spark/Doris"，而是扮演"带事务的轻量智能数据层"：

典型厂商与场景

AWS：RDS/Aurora PG + pgvector → 电商推荐、RAG 客服
Google Cloud AlloyDB：pgvector + Gemini + 语义重排 → 专利检索、商品推荐、自然语言转 SQL
Azure PG：azure_ai 扩展直连 Azure OpenAI/ML → 情感分析、PII 脱敏、RAG
EDB Postgres AI：Iceberg/Delta 外表 + 向量 + AI Agent → 企业知识库、湖仓查询加速
Neon / Supabase：Serverless PG 给 LLM 应用存会话/用户/向量/定时任务
国内：腾讯云/阿里云 RDS PG 跑标签维表、特征快照、Doris/Spark 的结果回写层

大数据流水线里的位置

业务库(MySQL/Oracle) ─CDC(Flink/Debezium)→ PG(贴源/维表/质量校验)│├─ 推 Doris/ClickHouse(明细数仓)├─ 推 Spark(离线宽表)└─ pgvector 存 Embedding → RAG/向量检索

2 原理架构篇

核心概念

PostgreSQL 作为一款功能强大的开源对象-关系型数据库（ORDBMS），其核心概念可以从逻辑结构、存储机制、并发控制、扩展性几个层面来理解。下面按“由表及里”的方式梳理最关键的概念。

一、逻辑结构：从实例到行

1. 实例（Instance / Cluster）

一个 PostgreSQL 实例 = 一个数据目录（$PGDATA）
一个实例可以管理多个数据库
同一实例内，所有数据库共享：
- 后台进程（postmaster、checkpointer、walwriter 等）
- 内存结构（shared buffers、WAL buffers）
- 配置文件（postgresql.conf、pg_hba.conf）

注意：PostgreSQL 的 “cluster” ≠ 分布式集群，而是指一个数据库实例。

2. 数据库（Database）

一个实例下的逻辑隔离单元
不同数据库之间：
- 不能直接跨库查询（除非用 dblink / postgres_fdw）
- 各自拥有独立的系统表、对象命名空间
常见用途：按业务/租户建库

3. Schema（模式）

数据库内部的二级命名空间
一个数据库中可以有多个 schema
用于逻辑分组、权限隔离、避免命名冲突

database└── schema├── table├── view├── function└── sequence

示例：

CREATE SCHEMA finance;
CREATE TABLE finance.orders (...);

4. 表（Table）、行、列、数据类型

PostgreSQL 是行存关系型数据库
表由行（tuple）和列组成
支持丰富的数据类型：
- 基础类型：int, text, boolean, timestamp
- 集合类型：ARRAY, JSONB, HSTORE
- 复合类型：
  - point (x, y)
  - line 直线
  - lseg 线段
  - box 矩形
  - path 闭合/开放路径
  - polygon 多边形
  - circle 圆
- 自定义类型：CREATE TYPE

二、物理存储：数据是如何落盘的

5. Relation & Page（堆表与页）

表、索引在内部统称为 relation
数据文件按 8KB page 组织
- PostgreSQL 把磁盘上的数据文件，切成固定 8KB 大小的“块”（Page），所有表、索引的读写，都以 Page 为单位进行。
  - Page 是“最小读写单元” | 即： 磁盘 I/O ←→ Page ←→ Buffer Pool
    - 不是按“行”读磁盘
    - 不是按“字节”读磁盘
    - 而是一次读 / 写一个完整的 8KB Page
  - 为什么是 8KB？
    - 接近操作系统页大小（通常 4KB）
    - 减少随机 I/O
    - 平衡 CPU Cache / 磁盘吞吐
    - 编译期可改（--with-blocksize），但极少人动
  - Page 和表的关系：一个表 = N 个 Page；一个 Page = 多个 Tuple（行）；行不能跨 Page 存储
    - 一行数据不能跨 Page，但1行数据超 8KB 时走 TOAST 或行外存储（Out-of-Line Storage）
      - TOAST = The Oversized-Attribute Storage Technique
  - 一个 8KB Page 内部包含：
    - Header（元数据）
    - Tuple（行数据）
    - Free Space（空闲空间）
    - Special（索引专用）

┌───────────────┐
│ Page Header   │
├───────────────┤
│ Tuple 1       │
│ Tuple 2       │
│ ...           │
├───────────────┤
│ Free Space    │
├───────────────┤
│ Special Space │
└───────────────┘

每个 page 中存放多个 tuple（行）

简化结构：

datafile└── page (8KB)├── header├── tuple1├── tuple2└── free space

如果一行数据超过了8KB，会怎么存储呢？
- 一行数据不能跨 Page，超 8KB 时走 TOAST 或行外存储（Out-of-Line Storage）。
- 1️⃣ 普通列（短数据）
  - 整行必须塞进 一个 8KB Page
  - 行头 + 所有列 ≤ Page 可用空间（约 8KB - header - 对齐）
- 2️⃣ 大字段（text / bytea / jsonb / array 等） : 当某列“太大”时，触发 TOAST（The Oversized-Attribute Storage Technique）：
  - 主表行里：只存一个指针（十几字节）
  - 真实数据：
    - 压缩后放 TOAST 表（独立文件）
    - 或进一步切片存到多个 TOAST Page
    - 或直接使用“行外存储”（不压缩）

6. OID & Filenode

PostgreSQL 内部大量使用 OID（Object ID）
表、索引、函数等都有 OID
表对应的物理文件名通常是其 relfilenode

SELECT oid, relname, relfilenode
FROM pg_class
WHERE relname = 'my_table';

大表会被拆分成多个 1GB 的文件（如 12345, 12345.1, 12345.2）

7. TOAST（超长字段存储）

单行不能超过约 2KB（受 page 限制）
超过阈值的字段（如 text, bytea）会进入 TOAST 表
自动压缩 + 外存，对用户透明

三、事务与并发控制（非常核心）

8. 事务（Transaction）

遵循 ACID
使用 BEGIN / COMMIT / ROLLBACK
支持：
- 保存点：SAVEPOINT
- 子事务
- 两阶段提交（XA）

9. MVCC（多版本并发控制）

这是 PostgreSQL 最重要的特性之一。

写不阻塞读，读不阻塞写
每次 UPDATE / DELETE 实际是：
- 标记旧行为“已删除”
- 插入新版本行
通过 xmin / xmax 判断行的可见性

关键优势：

几乎不需要读锁
避免大量锁竞争

代价：

产生“死元组”（dead tuples）
需要 VACUUM 清理

10. VACUUM & Autovacuum

[英译]

vacuum n.真空、真空吸尘器、空间、空虚、空白

VACUUM：回收死元组、更新统计信息
- 死元组（Dead Tuple）= 被 MVCC 标记为“已删除 / 过期”，但还没被清理掉的旧版本行。
VACUUM ANALYZE：同时更新优化器统计信息
autovacuum：后台自动进程，生产环境必须开启

四、索引机制

11. 索引类型（PostgreSQL 一大亮点）

B-Tree：默认，适合等值、范围查询
Hash：等值查询（较局限）
GiST / SP-GiST：通用搜索树，地理、全文检索
GIN：倒排索引，适合 JSONB, ARRAY, 全文检索
BRIN：块范围索引，适合时序/日志数据

示例：

CREATE INDEX idx_tags ON articles USING GIN (tags);

五、SQL 与对象模型

12. SQL 标准 + 扩展

完整支持 SQL:2016 核心特性
支持：
- CTE（WITH）
- 窗口函数（OVER / PARTITION BY）
- 递归查询
- UPSERT（INSERT ... ON CONFLICT）

13. 对象-关系特性

支持继承

CREATE TABLE parent (id int);
CREATE TABLE child () INHERITS (parent);

支持 自定义类型、操作符、聚合函数
接近面向对象建模能力

六、可靠性与高可用

14. WAL（Write-Ahead Logging）

所有修改先写 WAL，再改内存
崩溃恢复依赖 WAL
支持：
- 时间点恢复（PITR）
- 物理复制（流复制）

PG数据库的真实存储模型：
- 堆表（Heap）：行存，8KB Page，无序插入，MVCC 产生死元组
- 索引：B-Tree / GIN / GiST 等，各自独立文件
- WAL：只是堆表和索引页修改的“旁路日志”，先写 WAL 再改内存页，后台 checkpointer 再把脏页刷回堆文件

即：PG 是 “Heap + B-Tree + WAL”，不是 “LSM + SSTable + WAL”。

LSM 树里通常也用 WAL 保护 Memtable，但 LSM 本身替代的是 PG 的堆+B树，不是 WAL。

特别注意
- PostgreSQL 的 WAL ≠ LSM 树，两者是不同层面的东西。
  - WAL（Write-Ahead Log）：是一种日志协议/恢复机制，核心是“改数据前先顺序追加写日志”，用于崩溃恢复、复制、PITR。它本身只是一个 append-only 的日志记录流（16MB 段文件，按 LSN 顺序），不是一种索引/存储数据结构。
  - LSM 树（Log-Structured Merge Tree）：是一种存储引擎/数据组织方式，用于把随机写转顺序写（RocksDB、Cassandra、TiDB 等）
    - 典型结构是 内存 Memtable → 刷盘 SSTable → 多层 Compaction 合并

15. 物理复制 & 逻辑复制

物理复制：基于 WAL，主备完全一致
逻辑复制：基于逻辑解码，可按表/行级同步
常见架构：
- 一主多备
- 级联复制
- 读写分离

七、权限与安全

16. 角色体系（Role）

PostgreSQL 没有用户/角色之分
LOGIN 权限的角色 ≈ 用户

CREATE ROLE readonly NOLOGIN;
GRANT CONNECT ON DATABASE app TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

17. 认证与加密

认证方式：pg_hba.conf
- trust, password, md5, scram-sha-256
SSL 连接
行级安全（RLS）

八、扩展生态（PostgreSQL 的灵魂）

18. Extension 机制

插件式扩展，热加载
著名扩展：
- PostGIS：地理空间
- pg_stat_statements：SQL 统计
- pgcrypto：加密
- TimescaleDB：时序数据
- Citus：分布式

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

九、核心概念速查

层级	核心概念
实例	Cluster / Instance
物理	实例 --> TableSpace （均以文件目录做物理隔离）
逻辑	Database（逻辑隔离） → Schema（逻辑隔离） → Table
存储	Page / Tuple / TOAST
并发	MVCC / XID / VACUUM
索引	B-Tree / GIN / GiST / BRIN
事务	ACID / SAVEPOINT
高可用	WAL / 物理复制 / 逻辑复制
安全	Role / GRANT / RLS
扩展	Extension

graph TDA[ PostgreSQL 实例（Cluster） ] --> B[数据库（Database） ]A --> C[全局系统表（pg_global） ]B --> D[Schema]D --> E[Table]D --> F[Index]E --> G[TOAST 表]F --> H[索引文件]A --> I[表空间（Tablespace）]I --> J[pg_default]I --> K[pg_global]I --> L[自定义表空间]J --> BK --> CE --> M[8KB Page]M --> N[Tuple （行）]O[WAL 日志] --> AP[MVCC] --> AQ[VACUUM] --> Astyle A fill:#ffe4c4,stroke:#333style B fill:#add8e6,stroke:#333style D fill:#90ee90,stroke:#333style E fill:#ffb6c1,stroke:#333style F fill:#ffb6c1,stroke:#333style I fill:#dda0dd,stroke:#333

架构设计与运行原理

Client-Server + 多进程：每连接一个 backend 进程，共享内存放 buffer/shared catalog
存储：表 → Heap 文件；行存；MVCC 靠行头 xmin/xmax 标记版本，旧版本由 autovacuum 回收
WAL（Write Ahead Log）：先写日志再改页，保证崩溃恢复；流复制读 WAL 同步备库
查询链路：SQL → 解析 → 重写 → 优化器（基于成本） → 执行器（支持并行 scan/join/agg）
扩展挂载点：自定义类型/函数/索引访问方法/FDW 外表/后台 worker，pgvector 就是挂 GIN/HNSW 索引实现的

Z FAQ for PostgreSQL

Q: 对象-关系型数据库/ORDBMS，与关系型数据库的异同？并以PostgreSQL、MYSQL类比举例说明?

从概念、核心差异、典型特性、以及 PostgreSQL / MySQL 的实际表现四个层面，系统讲清 ORDBMS（对象-关系型数据库） 与 RDBMS（关系型数据库） 的异同。

一、概念层面的异同

1. 共同点（为什么都叫“关系型”）

都以表（Table）、行（Row）、列（Column）为核心模型
都支持 SQL
都强调 ACID 事务
都依赖 关系代数（选择、投影、连接等）

👉 所以，ORDBMS 本质上仍然是关系型数据库，只是在其基础上扩展了面向对象能力。

2. 核心区别

RDBMS：一切都是二维表 + 标量值

ORDBMS：表 + 对象类型 + 继承 + 复杂结构

维度	RDBMS	ORDBMS
数据类型	标量（int、varchar…）	标量 + 复合类型 + 自定义类型
数据建模	扁平、范式化	支持嵌套、聚合、继承
行为封装	存储过程	方法绑定到类型
SQL 标准	SQL92 / SQL99	SQL:1999 对象扩展
典型代表	MySQL（传统）	PostgreSQL

二、ORDBMS 的核心特性（对比 RDBMS）

1. 自定义复合类型（Composite Types）

RDBMS（MYSQL）：

-- 地址拆成多个字段
CREATE TABLE users (id int,city varchar(50),street varchar(100)
);

ORDBMS（PostgreSQL）：

CREATE TYPE address AS (city varchar(50),street varchar(100)
);CREATE TABLE users (id int,addr address
);

✅ 优势：

更符合现实世界建模
减少字段爆炸
语义更清晰

2. 表继承（Inheritance）

这是 ORDBMS 最具代表性的特征之一。

PostgreSQL 示例：

CREATE TABLE animals (id serial,name text
);CREATE TABLE dogs (bark_volume int
) INHERITS (animals);

dogs 自动拥有 id、name
查询父表可看到所有子类数据

👉 MySQL 完全不支持表继承。

3. 数组与集合类型

RDBMS：

-- 标签通常拆表
tags: tag_id, user_id, tag_name

ORDBMS（PostgreSQL）：

CREATE TABLE users (id int,tags text[]
);

✅ 适合半结构化、弱关联数据

4. 方法与操作符重载 *

ORDBMS 允许将“行为”绑定到类型上。

-- 建表测试用（可选）
CREATE TABLE t_geo (-- type = circle（复合类型）,作为 PostgreSQL 内置几何类型之一; PG 原生支持这些几何类型： point, line, lseg, box, path, polygon, circle-- circle 的属性字段: center :: point (圆心) , radius :: float8 (半径)-- circle 的常用方法: 算面积 area(circle) :: float8 , 求直径 diameter(circle) ::float8 , 算半径 radius(circle) :: float8 , 求圆心 center(circle) :: point-- SELECT (c).center, (c).radius , center(c), area(c), diameter(c) FROM ( SELECT '((0,0),5)'::circle ) t(c);-- SELECT '(0,0)'::point , '((0,0),5)'::circle , radius(circle '((0,0),5)') , area(circle '((0,0),5)');   -- radius = PG 的内置函数; area = PG 其实也自带 area(circle)c circle
);INSERT INTO t_geo(c) VALUES ( circle '((0,0),5)' );-- 创建面积函数
CREATE OR REPLACE FUNCTION circle_area(circle)
RETURNS float8
LANGUAGE SQL
AS $$SELECT pi() * ($1).radius * ($1).radius; -- $1 是 SQL 函数的位置参数引用，表示函数的第一个输入参数 : 即 circle
$$;-- 查询验证
SELECT circle_area(c) FROM t_geo;-- 直接调用
SELECT circle_area(circle '((0,0),5)');

👉 更接近面向对象语言（Java / C++）的设计方式。

5. 面向对象的“多态”查询

结合继承 + 类型判断，可实现类似 OOP 的多态：

SELECT *, tableoid::regclass
FROM animals;

三、PostgreSQL vs MySQL：经典对照

特性	PostgreSQL（典型 ORDBMS）	MySQL（典型 RDBMS）
自定义类型	✅ 支持	❌ 不支持
表继承	✅ 支持	❌ 不支持
数组类型	✅ 原生支持	❌ 不支持
JSON	✅ JSONB（索引、操作符）	✅ JSON（功能较弱）
多态查询	✅ 支持	❌ 不支持
面向对象建模	✅ 强	❌ 无
生态定位	OLTP + 分析 + 扩展	轻量 OLTP

👉 PostgreSQL = “最像 ORDBMS 的开源数据库”

👉 MySQL = “纯粹、简洁的关系型数据库”

四、什么时候该用 ORDBMS？

✅ 适合 ORDBMS（PostgreSQL）的场景

领域模型复杂（GIS、金融、医疗）
需要嵌套结构、数组、枚举
希望数据库层贴近业务对象
规则引擎、配置系统、元数据管理

✅ 适合传统 RDBMS（MySQL）的场景

CRUD 为主
简单表结构
高并发 Web 业务
团队熟悉度 & 运维成本优先

五、举例：用 Java / Python ORM（如 Hibernate / SQLAlchemy）对比 ORDBMS 建模

本案例旨在说明：

ORM 在“假装面向对象”，而 ORDBMS 在“真正面向对象”。

下面用 同一业务模型，分别用 Hibernate（Java） 和 SQLAlchemy（Python），对比它们在 传统 RDBMS（MySQL） 与 ORDBMS（PostgreSQL） 下的建模差异。

1、统一业务场景：员工–岗位模型

业务规则

员工分为：普通员工、经理
员工有地址（城市 + 街道）
员工有多个标签
经理有额外属性：bonus_rate（奖金比例）

2、在 RDBMS（MySQL）中的“妥协式”建模

1️⃣ Java + Hibernate（JPA）

实体类

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
public class Employee {@Idprivate Long id;private String name;@Embeddedprivate Address address;@ElementCollectionprivate List<String> tags;
}@Entity
public class Manager extends Employee {private Double bonusRate;
}

实际生成的表（MySQL）

employee
---------
id
name
address_city
address_streetmanager
---------
id
bonus_rate

✅ ORM 帮你“拼回对象”

❌ 数据库里仍是扁平表 + 外键

2️⃣ Python + SQLAlchemy

class Employee(Base):__tablename__ = 'employee'id = Column(Integer, primary_key=True)name = Column(String)type = Column(String)  # polymorphic_identitycity = Column(String)street = Column(String)tags = relationship("Tag")class Manager(Employee):__tablename__ = 'manager'id = Column(Integer, ForeignKey('employee.id'), primary_key=True)bonus_rate = Column(Float)

👉 本质仍是：

JOIN
映射表
应用层组装对象

🔑 RDBMS + ORM 的本质

数据库不懂“对象”，ORM 只是翻译官

3、在 ORDBMS（PostgreSQL）中的“原生对象建模”

1️⃣ PostgreSQL 原生对象定义

复合类型

CREATE TYPE address AS (city text,street text
);

表继承

CREATE TABLE employees (id serial PRIMARY KEY,name text,addr address,tags text[]
);CREATE TABLE managers (bonus_rate numeric
) INHERITS (employees);

✅ 数据库本身就理解：

继承
复合结构
集合属性

2️⃣ Java + Hibernate（PostgreSQL）

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public class Employee {@Idprivate Long id;private String name;@Type(type = "com.vladmihalcea.hibernate.type.array.StringArrayType")@Column(columnDefinition = "text[]")private String[] tags;@Type(type = "com.vladmihalcea.hibernate.type.basic.PostgreSQLHStoreType")private Address addr; // 映射为 PG 复合类型
}

👉 Hibernate 不再“模拟”对象，而是直接映射数据库原生能力

3️⃣ Python + SQLAlchemy（PostgreSQL）

from sqlalchemy.dialects.postgresql import ARRAY, CompositeTypeAddress = CompositeType('address',[Column('city', String),Column('street', String)]
)class Employee(Base):__tablename__ = 'employees'id = Column(Integer, primary_key=True)name = Column(String)addr = Column(Address)tags = Column(ARRAY(String))class Manager(Employee):__tablename__ = 'managers'id = Column(Integer, ForeignKey('employees.id'), primary_key=True)bonus_rate = Column(Float)

✅ SQLAlchemy 对 PostgreSQL 的支持非常“对象友好”

4、关键差异对比（ORM 视角）

维度	MySQL + ORM	PostgreSQL + ORM
继承实现	JOIN / SINGLE_TABLE	表继承（DB 原生）
复杂结构	拆表 / Embeddable	复合类型
集合属性	关联表	数组 / 多值列
ORM 复杂度	高（大量映射逻辑）	低（接近领域模型）
查询语义	多表 JOIN	单表 + 多态扫描
性能	JOIN 成本高	更紧凑、更少 JOIN

5、一个非常有代表性的查询对比

需求：查询所有员工（含经理）

MySQL + ORM（隐式）

SELECT *
FROM employee e
LEFT JOIN manager m ON e.id = m.id;

PostgreSQL（原生）

SELECT * FROM employees;

✅ 自动包含 managers 的数据

✅ 数据库理解“is-a”关系

6、ORM 在两种数据库中的角色变化

在 MySQL 中

ORM = 对象模拟器

负责继承
负责组合
负责集合
负责多态

在 PostgreSQL 中

ORM = 对象映射器

数据库已经懂对象
ORM 只做“桥接”
更接近 领域驱动设计（DDD）

7、总结

MySQL + ORM：把对象“压扁”进表

PostgreSQL + ORM：让数据库“长成”对象

8、延伸思考（很重要）

问题	结论
ORM 能替代 ORDBMS 吗？	❌ 不能，只是掩盖差异
为什么很多项目不用 PG？	运维成本 + 团队认知
微服务时代还重要吗？	✅ 领域模型越复杂，价值越大
适合 DDD 吗？	✅ PostgreSQL 是天然土壤

六、举例：用真实业务案例（如电商商品模型）对比 PG vs MySQL

业务背景

商品有多种类型（普通商品、图书、数码），且属性差异巨大。

MySQL：典型的“妥协式”设计

┌──────────────┐
│   products   │  ← 宽表 / EAV
├──────────────┤
│ id           │
│ title        │
│ price        │
│ author       │  ← NULL（如果不是书）
│ isbn         │
│ brand        │  ← NULL（如果不是数码）
│ warranty     │
│ attr_key     │  ← EAV 模式才有
│ attr_value   │
└──────────────┘▲│ 1:N
┌──────────────┐
│ product_attrs│  ← 可选（EAV）
└──────────────┘

特点——MySQL：典型的“泛化妥协”模型
- 只有一张（或两张）物理表
- 靠 NULL 或关联表表达差异
- DB 不理解“什么是图书”

方案 1：宽表（冗余严重）

CREATE TABLE products (id BIGINT,title VARCHAR(255),price DECIMAL(10,2),-- 图书专用author VARCHAR(100),isbn VARCHAR(20),-- 数码专用brand VARCHAR(50),warranty_months INT
);

❌ 大量 NULL 字段，无法约束“图书必须有 ISBN”。

方案 2：EAV 模型（性能灾难）

CREATE TABLE product_attrs (product_id BIGINT,attr_key VARCHAR(50),attr_value TEXT
);

❌ 无法做类型约束，查询必 JOIN，索引失效。

ORM 层（Java / Python）

必须用 Single Table / Joined 继承策略
复杂查询需手写 SQL
业务规则被迫写在应用层

PostgreSQL：原生“对象化”设计

┌──────────────┐
│  products    │  ← 抽象父类
├──────────────┤
│ id           │
│ title        │
│ price        │
│ specs(JSONB) │
└──────┬───────┘│ INHERITS
┌──────┴────────────────┐
▼                       ▼
┌───────────┐         ┌──────────────┐
│  books    │         │ electronics  │
├───────────┤         ├──────────────┤
│ isbn      │         │ brand        │
│ author    │         │ warranty     │
└───────────┘         └──────────────┘

特点——PostgreSQL：真正的“泛化–特化”模型
- 表之间有 IS-A 关系
- 子类字段强约束
- DB 原生理解“图书是一种商品”

1. 基础类型 + 继承

-- 公共属性
CREATE TABLE products (id SERIAL PRIMARY KEY,title VARCHAR(255),price NUMERIC(10,2)
);-- 图书（继承商品）
CREATE TABLE books (isbn CHAR(13) NOT NULL,author VARCHAR(100)
) INHERITS (products);-- 数码（继承商品）
CREATE TABLE electronics (brand VARCHAR(50),warranty_months INT
) INHERITS (products);

2. 复杂属性用 JSONB

ALTER TABLE products ADD COLUMN specs JSONB;-- 支持索引
CREATE INDEX idx_specs ON products USING GIN (specs);

3. 查询示例

-- 查所有商品（自动包含子类）
SELECT * FROM products;-- 查图书特有的字段
SELECT title, author FROM books;

核心差异对照表

维度	MySQL	PostgreSQL
建模范式	表驱动（扁平化）	对象驱动（层次化）
扩展性	改表结构 / EAV	新增子表即可
数据约束	弱（NULL 泛滥）	强（NOT NULL 作用于子类）
复杂查询	多表 JOIN	单表扫描 + 多态
JSON 能力	仅存储 / 简单提取	索引 + 路径查询 + 函数
ORM 负担	重（大量映射配置）	轻（接近领域模型）

总结

MySQL：为了适应表结构，牺牲了业务的“对象感”

PostgreSQL：为了适应业务，强化了数据库的“对象感”

实战建议

SKU 结构简单、迭代快：选 MySQL（省心）
商品类目多、属性差异大、搜索复杂：选 PostgreSQL（省钱，省代码）

七、举例： “RDBMS → ORDBMS → NoSQL”演进关系图 *

RDBMS → ORDBMS → NoSQL 的演进关系与分化逻辑（不是单纯时间线，而是「能力扩展」与「取舍」）。

graph LRR[RDBMS 关系型数据库 表/行/列/SQL/ACID]O[ORDBMS 对象-关系型数据库 +复合类型/继承/数组/方法]N[NoSQL 非关系型数据库 文档/KV/列族/图]R -->|扩展建模能力| OR -->|放弃强schema/SQL 追求扩展性与灵活模型| NO -->|仍保留SQL与事务 增强对象语义| PG[(PostgreSQL)]R -->|纯关系模型 简单高效| MY[(MySQL)]N --> DOC[(MongoDB)]N --> KV[(Redis)]N --> COL[(Cassandra)]N --> G[(Neo4j)]style R fill:#e1f5fe,stroke:#0288d1style O fill:#f3e5f5,stroke:#8e24aastyle N fill:#fff3e0,stroke:#f57c00

RDBMS → ORDBMS
- 不改关系本质，向内增强建模能力（面向对象）
RDBMS → NoSQL
- *向外放弃部分关系约束**，换扩展性 / 灵活数据模型
ORDBMS ≠ 中间态 （它和 NoSQL 是两条不同进化树：）
- ORDBMS：关系 + 对象
- NoSQL：反关系 / 弱关系

八、总结

ORDBMS = RDBMS + 面向对象建模能力

PostgreSQL 把“对象”放进数据库，MySQL 把“对象”留在应用层。

Q: pg数据库中，表、索引的存储实现？是以独立的文件存放吗？

是的，表和索引在 PostgreSQL 中本质上就是操作系统文件，但“一个表 ≠ 一个文件”这么简单。

表、索引在物理上以文件形式存储在表空间目录中，但会根据大小拆分为多个【文件】，TOAST 数据另有独立文件。

存储位置在哪？

路径规则：

$PGDATA/└── base/           # 默认表空间 pg_default└── <db_oid>/├── <relfilenode>├── <relfilenode>.1├── <relfilenode>.2└── <relfilenode>_fsm

<db_oid>：数据库的 OID（pg_database.oid）
<relfilenode>：表/索引的文件名（来自 pg_class.relfilenode）

表和索引是不是独立文件？

是的，每个表、每个索引都有自己的文件集合

SELECT relname, relfilenode
FROM pg_class
WHERE relname IN ('orders', 'orders_pkey');

结果类似：

orders        | 16384
orders_pkey   | 16385

👉 表 orders 和索引 orders_pkey 是完全不同的文件。

示例

synofoto=# SELECT relname, relfilenode FROM pg_class WHERE relname IN ('address', 'activity');relname  | relfilenode 
----------+-------------activity |       18361address  |       17396
(2 rows)

文件拆分规则（非常重要）

1️⃣ 单文件最大 1GB

超过 1GB，自动拆分：

16384
16384.1
16384.2

防止文件系统对大文件的限制问题

2️⃣ 辅助文件（自动维护）

后缀	作用
`_fsm`	Free Space Map（空闲空间映射）
`_vm`	Visibility Map（可见性映射，MVCC 优化）
`_init`	未日志表的初始化文件

通常不需要手动管它们。

TOAST：大字段的独立文件

[英译] toast : n.烤面包、土司、干杯

当某列太大（如 text, bytea）：

主表文件中只存一个 TOAST pointer
真实数据存在 TOAST 表中（独立文件）

16384          -- 主表
16385          -- toast table
16386          -- toast index

查询：

SELECT reltoastrelid::regclass
FROM pg_class
WHERE relname = 'orders';

示例

synofoto=# SELECT reltoastrelid::regclass FROM pg_class WHERE relname = 'address';reltoastrelid      
-------------------------pg_toast.pg_toast_17396
(1 row)

索引的存储

索引 = 独立文件
不同索引类型（B-Tree / GIN / BRIN）内部结构不同
索引文件同样遵循 1GB 拆分规则

逻辑 vs 物理对照表

逻辑对象	物理表现
Database	目录（db_oid）
Table	文件集合（relfilenode）
Index	独立文件集合
TOAST	独立表 + 独立索引
Schema	❌ 无物理文件（仅逻辑命名空间）

总结

PostgreSQL 中，表和索引以文件形式存储，每个对象有独立的 relfilenode 文件，超过 1GB 自动拆分，大字段通过 TOAST 表独立存储，Schema 不参与物理存储。

Q: 创建数据库时指定的【模板数据库】，有什么作用？

在 PostgreSQL 中，模板数据库（Template Database）的本质作用是：

作为“克隆源”，用来快速创建新数据库。

当你执行 CREATE DATABASE xxx; 时，PostgreSQL 并不是从零建库，而是复制一个已有数据库的结构和内容，这个被复制的库，就是模板数据库。

最核心的SQL语句

CREATE DATABASE new_db;

等价于（默认情况下）：

CREATE DATABASE new_db TEMPLATE template1;

👉 template1 是默认模板

PostgreSQL 自带哪几个模板库？

初始化实例后，通常会有两个“特殊”数据库：

1️⃣ `template1`（最重要）

默认模板
所有 CREATE DATABASE 不带 TEMPLATE 时都基于它
你可以改它（加表、加扩展、改参数）

2️⃣ `template0`（系统保留）

最干净的空库
字符集/排序规则固定
不允许连接，也不建议改
用途：
- 创建不同编码的数据库
- 从“完全干净”的状态建库

查看：

synofoto=# SELECT datname, datistemplate, datallowconn FROM pg_database;datname   | datistemplate | datallowconn 
-------------+---------------+--------------postgres    | f             | ttemplate1   | t             | ttemplate0   | t             | fautoupdate  | f             | tsynoindex   | f             | tmediaserver | f             | tong         | f             | tsynoffice   | f             | tnotestation | f             | tdownload    | f             | tsynofoto    | f             | tsynodrive   | f             | t
(12 rows)

模板数据库是怎么工作的？

创建新库的真实过程

指定一个模板库（默认 template1）
PostgreSQL 在文件系统层复制模板库的目录
复制系统表、对象、扩展、配置
对新库做少量初始化（如设置 owner）

⚠️ 注意：

不是逻辑导出/导入
是“物理级拷贝”（效率高）
新库和模板库在创建那一刻完全一致

模板数据库能干什么？（实战价值）

✅ 场景 1：统一新建库的基线

你可以在 template1 里提前放好：

常用 schema（public, audit, logs）
基础表（migrations, dict_*）
扩展（pgcrypto, uuid-ossp）
默认权限
搜索路径（search_path）

之后：

CREATE DATABASE order_service;

新库自动带这些东西。

✅ 场景 2：多租户 SaaS 建库

-- 先做好 tenant_template
UPDATE pg_database
SET datistemplate = true
WHERE datname = 'tenant_template';CREATE DATABASE tenant_a TEMPLATE tenant_template;
CREATE DATABASE tenant_b TEMPLATE tenant_template;

每个租户一个库，结构完全一致。

✅ 场景 3：避免编码问题（用 template0）

CREATE DATABASE mydbTEMPLATE template0ENCODING 'UTF8'LC_COLLATE 'C'LC_CTYPE 'C';

template1 如果已经被改成某种编码，可能无法创建另一种编码的库。

模板库的特殊属性

一个数据库是不是模板库，由这两个字段决定：

字段	含义
`datistemplate`	是否可作为模板
`datallowconn`	是否允许普通连接

系统判断逻辑：

datistemplate = true 才能被 TEMPLATE= 使用
多数模板库会设为 datallowconn = false（防误连）
把普通库变成模板：

UPDATE pg_database
SET datistemplate = true
WHERE datname = 'my_template';

重要限制（容易踩坑）

❌ 有活跃连接时不能当模板

ERROR: source database "template1" is being accessed by other users

解决：

断开连接
或改用 template0

❌ 不能基于自己克隆自己

❌ 模板库不是“继承关系”

改了 template1，已有库不会变
只影响“以后创建的库”

和“系统表 / 初始库”的区别

概念	作用
`postgres`	默认管理员连接库，不是模板
`template1`	默认建库模板
`template0`	干净模板（编码兼容用）
`pg_catalog`	系统表 schema，不是数据库

总结

模板数据库 = PostgreSQL 创建新库时的“快照源”，默认是 template1，用来统一结构、扩展和基线配置。

Q: 创建数据库时指定的【表空间】起什么作用？内置的 pg_default / pg_global 表空间的区别？

表空间作用

表空间 = 数据文件在操作系统里的物理存储位置。

用来把数据库对象分散到不同磁盘，做 IO 隔离、扩容、性能优化。

CREATE TABLESPACE fast_ssd LOCATION '/ssd/pgdata';
CREATE TABLE t1 TABLESPACE fast_ssd;

层级关系：表空间 --> 数据库 --> Schema --> 表

graph TDTS[(表空间 Tablespace 物理目录: /ssd/pgdata)]TS --> DB1[(数据库 database_a)]TS --> DB2[(数据库 database_b)]DB1 --> SCH1[Schema public]DB1 --> SCH2[Schema finance]SCH1 --> TBL1[(表 table_x INDEX idx_x)]SCH2 --> TBL2[(表 table_y)]DB2 --> SCH3[Schema public]SCH3 --> TBL3[(表 table_z)]PGDEF[(pg_default 表空间)] -.-> DB1PGDEF -.-> DB2PGGLOB[(pg_global 表空间)] -.-> GLOB[(全局系统表 pg_database / pg_authid)]

表空间/TableSpace：操作系统目录，可挂多个数据库

数据库/Database：数据库，属于某个实例，逻辑隔离

模式/Schema：库内命名空间，逻辑隔离

表/Table、索引/Index：最终对象，落在某个表空间的某个文件里

物理隔离的最小单位是：表空间（Tablespace）
逻辑隔离的最小单位是：Schema
- 同库不同 Schema 的表，默认都在同一个表空间里
  - 如： CREATE SCHEMA finance; CREATE TABLE finance.orders (...); -- 默认落在 pg_default
- 可以跨 Schema 连表查询；但在同一会话（Connection / Session）中，原生PG数据库下，无法跨 Database 连表查询。
  - 如： SELECT * FROM public.users u JOIN finance.orders o ON u.id = o.user_id;
  - 不能跨库连表查询的原因： Database 是 PostgreSQL 的最高逻辑边界，一个连接只能 attach 到一个 Database。
    - 一个 Connection = 一个 Database
- 采取逻辑隔离的： Database / Schema

层级	隔离类型	说明
Tablespace	物理隔离	对应操作系统目录，可以放在不同磁盘
Database	强逻辑隔离	不同库之间无法直接访问（除非 FDW）
Schema	弱逻辑隔离	只是命名空间前缀（`schema.table`），共用同一个库的资源
Table	无隔离	只是 Schema 下的一个对象

pg_default vs pg_global

表空间	作用	特点
pg_default	普通对象的默认存储	用户表、索引、自己建的库都在这里
pg_global	集群级系统对象存储	存 `pg_database`、`pg_authid` 等跨库共享的系统表

关键区别
- pg_default：每个数据库私有
- pg_global：整个 PostgreSQL 实例（cluster）唯一，所有库共享
- 两者都不能删除
- 只有 pg_global 存的是“全局系统表”

建库时，建议使用 pg_defualt 还是 pg_global 表空间？

结论：永远不要用 pg_global，99% 情况用 pg_default。

对比

表空间	能否在建库时指定	建议	原因
pg_default	✅ 可以（默认）	✅ 强烈推荐	专门存放用户数据库
pg_global	✅ 技术上可行	❌ 严禁使用	只存集群级系统表，污染会导致实例异常

原因

pg_global 是给 PostgreSQL 内核用的，不是给用户库用的。

pg_global 只存：
- pg_database
- pg_authid
- 其他跨库系统表
若把业务库建进去：
- 破坏系统结构
- 备份/恢复风险
- 官方文档明确不推荐

正确姿势

-- 什么都不写，默认就是 pg_default ✅
CREATE DATABASE app_db;-- 显式写，也推荐 ✅
CREATE DATABASE app_db TABLESPACE pg_default;

只有这两种场景才动表空间：

性能/磁盘规划：新建表空间放到 SSD
冷热分离：历史数据放 HDD

小结

pg_default 管“业务数据”，pg_global 管“集群元数据”。
建库默认用 pg_default，pg_global 碰都别碰。

Q: PG数据库的「生产环境建库最佳实践」?

权限最小化：建库用专用运维账号，业务账号仅授权CONNECT+对应schema权限，禁用superuser跑业务。
参数模板化：预置shared_buffers、work_mem等核心参数模板，按实例规格固化，避免现场随意改。
建库规范：

CREATE DATABASE显式指定OWNER，ENCODING='UTF8'，LC_COLLATE/LC_CTYPE='en_US.UTF-8'（避免中文排序坑）。

业务schema单独创建，禁止业务对象放public。

表空间分离：索引、大表用单独的表空间，数据/日志/WAL分盘挂载，避免IO争抢。
扩展白名单：仅安装必要extension（如pg_stat_statements），禁止随意CREATE EXTENSION。
连接限制：ALTER ROLE xxx CONNECTION LIMIT N，防连接风暴；配合pgbouncer做连接池。
基线配置：开启log_checkpoints/log_connections等审计日志，部署自动 vacuum/analyze，预设WAL归档。
建完校验：\l+、\dt+、pg_tablespace检查，跑一轮基础监控采集验证。