深入解析Postgres内部：数据库集群、数据库与表

# 深入阅读 PostgreSQL 内部结构：数据库集群、数据库和表 — Burak Sen 来源：https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables 我正在深入研究 Postgres 内部原理，同时觉得将笔记写下来有助于保持学习节奏，并内化所读内容。感谢 Hironobu Suzuki 提供这份优秀参考以及他在 Postgres 方面的工作。以下为源链接： https://www.interdb.jp/pg/index.html?ref=buraksen.dev ### 数据库集群的逻辑结构 [https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#logical-structure-of-database-cluster](https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#logical-structure-of-database-cluster) 在 PostgreSQL 中，数据库集群（database cluster）并非指数据库服务器的集合（SQL 标准中称为“目录集群”）。它指的是由单个 PostgreSQL 实例管理的一组数据库。从字典角度来看，这很可能是正确的定义，但通常当你听到“数据库集群”时，会认为它是由多个数据库节点/实例组成的单一系统。 数据库则是表、索引、视图等数据库对象的集合。在 PostgreSQL 中，数据库本身也是数据库对象，由 `Oid`（无符号整型对象标识符）表示。 ```sql SELECT oid, datname FROM pg_database ORDER BY oid; ``` ``` oid | datname -----+---------- 1 | template1 4 | template4 5 | postgres (3 rows) ``` 内置对象（此查询中的数据库）具有硬编码的低值。其他用户创建的表/对象从 16384 开始分配 OID（OID 1-16383 被保留或由初始化对象使用）。 - 数据库集群 · 一个 PostgreSQL 实例 - template1 oid=1 - template4 oid=4 - postgres oid=5 - shop oid=16384 - pg_database 列出每个数据库（每个集群一个共享目录） - ‘shop’ 内的对象 - orders 表 oid=16386 - orders_pkey 索引 oid=16395 - 视图、序列、……更多对象 oid=… - 每个对象由其自身的 OID 标识 这些对象及其关系存储在系统目录（system catalogs）中，它们只是 PostgreSQL 中的普通表。常见示例： | 表 | 描述 | | ------------ | ------------------------------------------------------------ | | pg_class | 表及其他类似表对象（视图、索引、TOAST 表等） | | pg_database | 存储可用数据库的相关信息 | | pg_index | 索引信息 | | … | … | 关于目录的一些小知识： - `pg_database` 在集群的所有数据库间共享（每个集群一个表），而大多数系统目录是**每个数据库**单独创建的。 - `pg_class` 也存储索引，但还有另一个名为 `pg_index` 的目录。原因在于 `pg_class` 用于通用关系信息，而 `pg_index` 及其他相关目录拥有各自的定制模式。这有助于分离关注点，避免将来出现像 `merchants2` 表那样的命名混乱（虽然有些夸张，但还记得那个著名的 `merchants2` 表吗？[ugliest-beautiful-codebase](https://jimmyhmiller.com/ugliest-beautiful-codebase?ref=buraksen.dev)）。 正如上面所说，这些只是普通表，你可以对它们执行查询（风险自负！）。许多内置对象（如类型、函数、运算符）都存储在这些表中，用户定义的对象也以同样方式添加。当向目录表中添加新行时，这些 OID 会自动创建。例如，注册一个扩展（如 pgvector）时，pgvector 会带着自动创建的 OID 被添加到 `pg_extension` 表中。 对于用户定义的表，这种行为的历史变迁如下： - `PG <= 8.0`：所有表行都带有 OID。 - `8.1 <= PG < 12`：自动生成 OID 成为可选特性。用户需要使用 `CREATE TABLE foo (...) WITH OIDS;` 创建表，或启用 GUC 参数 `default_with_oids`。 - `PG >= 12`：该特性被完全移除。 ### 数据库集群的物理结构 [https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#physical-structure-of-db-cluster](https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#physical-structure-of-db-cluster) 一个 Postgres 集群将所有内容存储在数据目录中。该目录的路径由 `PGDATA` 环境变量设置。常见的默认位置有 `/var/lib/pgsql/data` 和 `/var/lib/postgresql/<version>/main`。`initdb` 负责设置并创建此目录，Postgres 安装程序会自动完成。当调用 `brew install postgresql@18` 时，[email protected]（[链接](https://github.com/Homebrew/homebrew-core/blob/839d8de5e4b1c0df647dcec57dbe09abf0135a9e/Formula/p/postgresql%4018.rb#L150?ref=buraksen.dev)）会在 post-install 方法中运行以下行： ```ruby system bin/"initdb", "--locale=en_US.UTF-8", "-E", "UTF-8", postgresql_datadir unless pg_version_exists? ``` 其他安装方法（EDB for Windows、apt/deb 等）也实现了类似逻辑。 在 `$PGDATA` 内部有许多子目录： ``` $PGDATA/ ├── base/ # 每个数据库一个子目录 │ └── {OID}/ # 表 & 索引作为文件（relfilenode） ├── global/ # 集群范围内的目录（如 pg_class） ├── pg_wal/ # WAL 段文件 ├── pg_xact/ # 事务提交状态（clog） ├── pg_tblspc/ # 指向外部表空间的符号链接 ├── PG_VERSION # 主版本号 ├── postgresql.conf # 主服务器配置 └── ... # 15+ 个更多 ``` 完整列表见 [PostgreSQL 文档](https://www.postgresql.org/docs/current/storage-file-layout.html?ref=buraksen.dev)。子目录的变化似乎很少。[此处的表格](https://www.interdb.jp/pg/pgsql01/02.html?ref=buraksen.dev)显示了 PG9 和 PG10 中的一些命名变化及新增项。我还查看了 Postgres 源码，发现 `current_logfiles` 子目录是在 PG10 版本中添加的。它在 [19dc233](https://github.com/postgres/postgres/commit/19dc233c32f2900e57b8da4f41c0f662ab42e080?ref=buraksen.dev) 提交中引入，从 PG10 开始包含： ```bash git tag --contains 19dc233c32f | grep -E '^REL' | sort -V | head ``` ``` REL_10_0 REL_10_1 REL_10_2 ... ``` #### 数据库子目录布局 [https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#database-subdirectory-layout](https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#database-subdirectory-layout) 如上所述，每个数据库在 `base` 目录下都有自己的子目录，以该数据库的 OID 命名（`/base/{OID}`）。如果表或索引的大小小于 1GB，它们会以单个文件的形式存储在数据库子目录下。与 OID 类似，物理文件由 `relfilenode` 标识，该信息存储在表/索引对应的 `pg_class` 行中。 现在让我们进一步探索这些布局。我使用的是 Mac，已通过 `brew install postgresql@18` 安装了 PostgreSQL 18 并作为服务运行。然后通过 `psql -d postgres` 连接。 ``` data_directory ------------------------ /opt/homebrew/var/postgresql@18 (1 row) ``` 如上所述，数据目录路径为 `/opt/homebrew/var/postgresql@{VERSION}`。现在创建 `shop` 数据库并探索它。 ```sql CREATE DATABASE shop; SELECT oid, datname FROM pg_database WHERE datname = 'shop'; ``` ``` CREATE DATABASE oid | datname -------+--------- 16384 | shop (1 row) ``` 磁盘上现在出现了一个以该 OID 命名的新目录： ```bash ls /opt/homebrew/var/postgresql@18/base/ ``` 一个意外是 `shop` 目录并非空目录。原因是 `CREATE DATABASE` 命令通过复制现有数据库来创建新数据库，而 `template1` 是默认源数据库。关于模板的更多细节请参考[官方文档](https://www.postgresql.org/docs/current/manage-ag-templatedbs.html?ref=buraksen.dev)。 ```bash ls /opt/homebrew/var/postgresql@18/base/16384 | head ``` ``` 112 113 1247 1247_fsm 1247_vm 1249 1249_fsm 1249_vm 1255 1255_fsm ``` 创建一个基础表： ```sql \c shop CREATE TABLE orders ( id bigserial PRIMARY KEY, customer_id bigint NOT NULL, total_cents bigint NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); ``` 现在，该表及其主键索引将拥有各自的 OID。通过查询 pg_class 查看它们： ```sql SELECT oid, relname, relfilenode, relkind FROM pg_class WHERE relname IN ('orders', 'orders_pkey'); ``` ``` oid | relname | relfilenode | relkind -------+-------------+-------------+--------- 16386 | orders | 16386 | r 16395 | orders_pkey | 16395 | i (2 rows) ``` 如您所见，relfilenode（物理位置标识符）和 OID 在此处相同。我们可以使用内置函数 `pg_relation_filepath` 来计算路径： ```sql SELECT pg_relation_filepath('orders'), pg_relation_filepath('orders_pkey'); ``` ``` pg_relation_filepath | pg_relation_filepath ----------------------+----------------------- base/16384/16386 | base/16384/16395 (1 row) ``` - 数据库 OID=16384 - relfilenode · pg_class=16386 - 堆文件在磁盘上：base/16384/16386 - 文件名由 relfilenode 命名，而非 OID（仅在刚 CREATE 后它们相等） 我们的表和索引文件存储在 `base/{database_oid}/{table|index_relfilenode}` 中。 插入一些行并触发 `VACUUM`： ```sql INSERT INTO orders (customer_id, total_cents) SELECT (random() * 1000)::bigint, (random() * 100000)::bigint FROM generate_series(1, 1000); VACUUM orders; ``` `VACUUM` 主要用于回收存储。细节可查阅[文档](https://www.postgresql.org/docs/current/sql-vacuum.html?ref=buraksen.dev)，我将在后续文章中深入探讨。结果文件如下： ```bash ls -l /opt/homebrew/var/postgresql@18/base/16384/{16386*,16395} ``` ``` -rw-------@ 1 burak admin 65536 Jun 15 10:43 .../base/16384/16386 -rw-------@ 1 burak admin 24576 Jun 15 10:43 .../base/16384/16386_fsm -rw-------@ 1 burak admin 8192 Jun 15 10:43 .../base/16384/16386_vm -rw-------@ 1 burak admin 40960 Jun 15 10:43 .../base/16384/16395 ``` 对于我们的表，`16386` 是主关系数据；`16386_fsm` 和 `16386_vm` 是其辅助文件。它们被称为辅助文件（forks），分别用于跟踪表页面中的空闲空间（FSM）和每个页面的可见性状态（VM）。 现在，为了演示 `OID == relfilenode` 并不总是成立，我们执行一下 `VACUUM FULL`，它会执行以下操作（简化描述）： - 获取表的排他锁 - 将行复制到新文件（新的 relfilenode） - 切换指针指向新的 relfilenode 并提交 ```sql VACUUM FULL orders; SELECT oid, relname, relfilenode FROM pg_class WHERE relname IN ('orders', 'orders_pkey'); SELECT pg_relation_filepath('orders'); ``` ``` VACUUM oid | relname | relfilenode -------+-------------+------------- 16386 | orders | 16397 16395 | orders_pkey | 16400 (2 rows) pg_relation_filepath ---------------------- base/16384/16397 (1 row) ``` 由 `VACUUM` 引起的重写更新了 relfilenodes，在最新状态下它们与 OID 不再相等。 - 在 VACUUM FULL 之前 - OID（身份）=16386 - relfilenode=16386 - 路径=base/16384/16386 - 文件=16386 - VACUUM FULL → 重写为新文件 - 之后 - OID（身份）=16386 - relfilenode=16397 - 路径=base/16384/16397 - 文件=16397 - oid 是表的永久身份 · relfilenode 命名文件，并在重写时改变 #### 表空间 [https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#tablespaces](https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#tablespaces) Postgres 也支持表空间（tablespaces）（[官方文档](https://www.postgresql.org/docs/current/manage-ag-tablespaces.html?ref=buraksen.dev)）。通过表空间，用户可以指定其他目录来存储/使用数据库文件。它允许： - 扩展 Postgres 初始配置所在的分区/卷。 - 将频繁访问的索引放在快速磁盘上，冷数据表放在慢速磁盘上。 我们之前讨论过 `base/` 和 `global/` 目录。`pg_default` 是普通数据库对象的默认表空间，路径为 `$PGDATA/base`；而 `pg_global` 是用于集群范围对象的表空间。 创建一个目录 `mkdir -p /Users/burak/pg-tblspc-extra`，并将其添加为表空间。 ```sql CREATE TABLESPACE extra_space LOCATION '/Users/burak/pg-tblspc-extra'; SELECT oid, spcname FROM pg_tablespace WHERE spcname = 'extra_space'; ``` ``` CREATE TABLESPACE oid | spcname -------+------------ 16401 | extra_space (1 row) ``` 正如所料，我们的表空间也有一个 OID，即 `16401`。Postgres 不会直接访问此表空间的原始路径，而是在文件路径之间创建一个符号链接。 ```bash ls -la /opt/homebrew/var/postgresql@18/pg_tblspc/ ``` ``` lrwx------@ 1 burak admin 28 Jun 20 15:56 16401 -> /Users/burak/pg-tblspc-extra ``` 创建表空间时，Postgres 会执行 `symlink("/Users/burak/pg-tblspc-extra","$PGDATA/pg_tblspc/16401")`。 Postgres 如何知道它正在搜索的文件位于 `base/`、`global/` 还是 `pg_tblspc/` 中的符号链接里呢？内部有一个映射关系： 根据表空间 OID 解析关系的目录路径： - 如果 spcOid == GLOBALTABLESPACE_OID → `global/` - 如果 spcOid == DEFAULTTABLESPACE_OID → `base/{database_oid}/` - 否则（命名表空间）→ `pg_tblspc/{spcOid}/PG_{major_version}_{catalog_version}/{database_oid}/` 例如：`pg_tblspc/16401/PG_18_202506291/16384/16402` 其中 spcOid 是表空间的 OID。你可能注意到了 `PG_xx_yyy`，即 `PG_{主版本}_{目录版本}`。在我的本地环境中，它是： ```bash ls -la /Users/burak/pg-tblspc-extra/ ``` ``` drwx------@ 2 burak staff 64 Jun 20 15:56 PG_18_202506291 ``` 在此表空间中创建一张带索引的表，并检查它们的路径： ```sql \c shop CREATE TABLE archived_orders (LIKE orders INCLUDING ALL) TABLESPACE extra_space; SELECT oid, relname, reltablespace, relfilenode FROM pg_class WHERE relname IN ('archived_orders', 'archived_orders_pkey'); ``` ``` CREATE TABLE oid | relname | reltablespace | relfilenode -------+--------------------+---------------+------------- 16402 | archived_orders | 16401 | 16402 16411 | archived_orders_pkey | 0 | 16411 (2 rows) ``` 我们的表空间 OID 是 `16401`，表对应的目录行反映了这一点，但索引的 `reltablespace` 为 0，表示使用数据库的默认表空间。默认情况下，索引使用数据库的默认表空间，需要使用 `USING INDEX TABLESPACE` 来覆盖。 ``` List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description -------------+-------+-------------------------+-------------------+---------+--------+------------- extra_space | burak | /Users/burak/pg-tblspc-extra | | | 96 bytes | pg_default | burak | | | | 30 MB | pg_global | burak | | | | 548 kB | (3 rows) ``` ### 堆表结构 [https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#heap-table-structure](https://www.buraksen.dev/articles/internals-of-postgresql-db-cluster-and-tables#heap-table-structure) 前面我们讨论了数据的层级结构以及数据在磁盘上的实际位置。现在，本节将探索它们是如何存储的，而非存储位置。 堆表（heap table）是一个由页面（pages）组成的无序集合，页面中包含元组（tuples，约等于行的物理表示），这些元组以随机顺序存储。 表文件： - 第 0 页：8192 [字节] - 第 1 页：8192 [字节] - … - 第 N 页：8192 [字节] 页面（块）编号：pd_lsn, pd_checksum, pd_flags, pd_lower, pd_upper, pd_special, pd_pagesize_version, pd_prune_xid 页面的组成部分： 1. 页头（Headers）：包含关于页面的通用信息，Postgres 在每次访问时都会查看（空闲空间指针、校验和、标志等）。 2. 行指针（Line Pointers）：指向元组。它们类似于元组的索引，对元组的任何访问都通过行指针进行。行指针从 1 编号到 N，并顺序递增。 3. 元组（Tuples）：实际存储数据的地方。元组也从 1 编号到 N，但它们从页面末尾开始存储。元组也有头部、空位图（null bitmap）和用户数据。 现在让我们检查它们是如何工作的，以及在一个 Postgres 集群中实际值是什么。为此，我将使用一个名为 pageinspect 的内置 Postgres 扩展（[官方文档](https://www.postgresql.org/docs/current/pageinspect.html?ref=buraksen.dev)）。它提供了用于底层检查页面内容的函数。 让我们创建一个数据库、一张表，并安装该扩展。 ```sql CREATE DATABASE notes_lab; CREATE EXTENSION pageinspect; CREATE TABLE notes ( id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text NOT NULL ); INSERT INTO notes (note) VALUES ('small row'), ('second row'), ('third row'); ``` 首先，按照上一节的方法检查表数据的位置： ```sql SELECT oid, relname, relfilenode, relkind FROM pg_class WHERE relname IN ('notes', 'notes_pkey') ORDER BY relname; ``` ``` oid | relname | relfilenode | relkind -------+-------------+-------------+--------- 16461 | notes | 16461 | r 16468 | notes_pkey | 16468 | i (2 rows) ``` 注意，relkind `r` 表示普通表。