印刻数据库

外观

PostgreSQL 存储结构与文件布局 ​

物理文件布局 ​

PostgreSQL 数据库集群的物理文件存储在一个指定的目录中，称为数据目录（Data Directory）。数据目录的结构如下：

data_directory/
├── PG_VERSION              # 数据库版本信息文件
├── base/                   # 存储各个数据库的数据文件
│   ├── 1/                  # template1 数据库
│   ├── 13186/              # template0 数据库
│   └── 16384/              # 用户创建的数据库
├── global/                 # 存储集群级别的数据
├── pg_commit_ts/           # 事务提交时间戳
├── pg_dynshmem/            # 动态共享内存段
├── pg_hba.conf             # 客户端认证配置文件
├── pg_ident.conf           # 身份映射配置文件
├── pg_logical/             # 逻辑复制相关数据
├── pg_multixact/           # 多事务ID
├── pg_notify/              # LISTEN/NOTIFY 消息
├── pg_replslot/            # 复制槽信息
├── pg_serial/              # 序列生成器状态
├── pg_snapshots/           # 快照信息
├── pg_stat/                # 统计信息
├── pg_stat_tmp/            # 临时统计信息
├── pg_subtrans/            # 子事务信息
├── pg_tblspc/              # 表空间链接
├── pg_twophase/            # 两阶段提交信息
├── pg_wal/                 # WAL（Write-Ahead Logging）文件
├── pg_xact/                # 事务ID提交状态
├── postgresql.auto.conf    # 自动生成的配置文件
├── postgresql.conf         # 主配置文件
└── postmaster.opts         # Postmaster 启动选项

核心文件类型 ​

1. 数据文件 ​

  • 存储位置：base/{database_oid}/{relfilenode}
  • 文件命名：以表或索引的 relfilenode 命名，例如 16385
  • 文件大小：默认每个文件最大1GB，超过后会创建扩展文件，例如 16385.1、16385.2 等
  • 存储内容：存储表和索引的实际数据

2. WAL文件 ​

  • 存储位置：pg_wal/ 目录
  • 文件命名：使用16进制数字命名，例如 000000010000000000000001
  • 文件大小：默认16MB，可通过 wal_segment_size 参数调整
  • 存储内容：记录所有数据库修改操作，用于故障恢复和复制

3. 控制文件 ​

  • 存储位置：global/pg_control
  • 文件大小：固定大小，包含数据库集群的关键元数据
  • 存储内容：
    • 数据库集群标识符
    • 检查点信息
    • 最新的事务ID和WAL位置
    • 集群状态信息
    • 配置参数的默认值

4. 配置文件 ​

  • postgresql.conf：主配置文件，包含数据库服务器的各种配置参数
  • pg_hba.conf：客户端认证配置文件，控制哪些客户端可以连接到数据库
  • pg_ident.conf：身份映射配置文件，用于将操作系统用户映射到数据库用户
  • postgresql.auto.conf：自动生成的配置文件，用于存储通过 ALTER SYSTEM 命令修改的参数

5. 其他重要文件 ​

  • PG_VERSION：存储数据库版本号的文件，例如 14 表示PostgreSQL 14
  • postmaster.opts：存储Postmaster进程的启动选项
  • postmaster.pid：存储Postmaster进程的PID和其他信息

表空间文件布局 ​

表空间允许将数据库对象存储在数据目录之外的位置，提供了更灵活的存储管理：

  • 表空间链接文件：存储在 pg_tblspc/ 目录中，文件名是表空间的OID，文件内容是表空间的实际路径
  • 表空间数据文件：存储在表空间的实际路径中，结构与 base/ 目录类似

数据文件结构 ​

1. 块结构 ​

PostgreSQL 将数据文件划分为固定大小的块（Block），默认块大小为8KB，可通过编译时选项调整。每个块包含以下部分：

  • 块头：包含块的元数据，如块类型、块号、空闲空间指针等
  • 空闲空间：块中未使用的空间
  • 元组：存储实际的数据行
  • 行指针：指向元组的指针数组

2. 元组结构 ​

元组是表中的一行数据，包含以下部分：

  • 元组头：包含元组的元数据，如事务ID、命令ID、元组长度等
  • 空值位图：指示哪些字段为空值
  • 字段数据：存储各个字段的实际数据

3. 页面类型 ​

PostgreSQL 支持多种页面类型，包括：

  • 普通数据页面：存储表数据
  • 索引页面：存储索引数据
  • 空闲页面：未使用的页面
  • 分支页面：B-tree索引的分支节点
  • 叶子页面：B-tree索引的叶子节点

WAL文件结构 ​

1. WAL段文件 ​

WAL文件以段（Segment）为单位，每个段文件默认大小为16MB。段文件的命名格式为 XXXXXXXXXXXXXXXX，其中前8位是时间线ID，后16位是日志位置。

2. WAL记录 ​

每个WAL段文件包含多个WAL记录，每个记录包含以下信息：

  • 记录长度：WAL记录的总长度
  • 记录类型：指示WAL记录的类型，如插入、更新、删除等
  • 事务ID：关联的事务ID
  • 页面引用：指向被修改的页面
  • 修改数据：实际的修改内容

3. WAL记录类型 ​

常见的WAL记录类型包括：

  • HeapTupleInsert：插入数据行
  • HeapTupleUpdate：更新数据行
  • HeapTupleDelete：删除数据行
  • IndexInsert：插入索引项
  • IndexDelete：删除索引项
  • Checkpoint：检查点记录
  • Begin：事务开始记录
  • Commit：事务提交记录
  • Abort：事务中止记录

存储参数配置 ​

1. 块大小 ​

  • 参数：编译时选项 --with-blocksize
  • 默认值：8KB
  • 调整建议：根据硬件特性和工作负载调整，一般不建议修改

2. WAL段大小 ​

  • 参数：wal_segment_size
  • 默认值：16MB
  • 调整建议：对于高写入负载的系统，可以考虑增大WAL段大小

3. 表空间 ​

  • 创建表空间：CREATE TABLESPACE tablespace_name LOCATION 'directory_path';
  • 使用表空间：CREATE TABLE table_name (...) TABLESPACE tablespace_name;

4. 填充因子 ​

  • 参数：fillfactor
  • 默认值：100（表），90（索引）
  • 调整建议：对于频繁更新的表，可以降低填充因子，预留空间用于更新操作

存储管理 ​

1. 数据文件维护 ​

  • 查看数据文件大小：SELECT pg_size_pretty(pg_relation_size('table_name'));
  • 查看表空间大小：SELECT pg_size_pretty(pg_tablespace_size('tablespace_name'));
  • 查看数据库大小：SELECT pg_size_pretty(pg_database_size('database_name'));

2. WAL文件管理 ​

  • 查看WAL使用情况：SELECT * FROM pg_stat_wal;
  • 查看WAL归档状态：SELECT * FROM pg_stat_archiver;
  • 手动切换WAL段：SELECT pg_switch_wal();

3. 表空间管理 ​

  • 查看所有表空间：SELECT * FROM pg_tablespaces;
  • 查看表使用的表空间：SELECT table_name, tablespace_name FROM information_schema.tables WHERE table_schema = 'public';
  • 修改表的表空间：ALTER TABLE table_name SET TABLESPACE new_tablespace_name;

性能优化建议 ​

1. 存储硬件选择 ​

  • 磁盘类型：优先选择SSD，提供更高的I/O性能
  • RAID配置：根据可用性和性能需求选择合适的RAID级别
  • 存储控制器：选择高性能的存储控制器，支持缓存和电池备份

2. 文件系统优化 ​

  • 文件系统选择：优先选择EXT4、XFS等高性能文件系统
  • 挂载选项：使用适当的挂载选项，如 noatime、nodiratime 等
  • 文件系统块大小：与PostgreSQL的块大小保持一致

3. 表空间优化 ​

  • 分离热点数据：将频繁访问的数据存储在高性能存储上
  • 分离WAL文件：将WAL文件存储在独立的存储设备上，减少I/O竞争
  • 分离索引：将索引存储在独立的存储设备上，提高查询性能

4. 填充因子优化 ​

  • 频繁更新的表：降低填充因子，预留空间用于更新操作
  • 静态表：使用默认填充因子，提高存储空间利用率
  • 索引：根据索引更新频率调整填充因子

常见问题（FAQ） ​

Q1: PostgreSQL 的默认块大小是多少？可以修改吗？ ​

A1: PostgreSQL 的默认块大小是8KB。块大小是在编译时通过 --with-blocksize 选项指定的，一旦编译完成，就无法修改。修改块大小需要重新编译PostgreSQL。

Q2: WAL文件的默认大小是多少？如何调整？ ​

A2: WAL文件的默认大小是16MB。可以通过 wal_segment_size 参数调整WAL文件的大小，但这个参数只能在初始化数据库集群时设置，无法在运行时修改。

Q3: 如何查看数据库的大小？ ​

A3: 可以使用以下命令查看数据库的大小：

sql
SELECT pg_size_pretty(pg_database_size('database_name'));

Q4: 什么是表空间？如何使用表空间？ ​

A4: 表空间是PostgreSQL提供的一种机制，允许将数据库对象存储在数据目录之外的位置。使用表空间可以实现：

  • 将不同的数据库对象存储在不同的存储设备上
  • 优化存储性能
  • 更好地管理存储空间

创建表空间的命令：

sql
CREATE TABLESPACE tablespace_name LOCATION 'directory_path';

使用表空间的命令：

sql
CREATE TABLE table_name (...) TABLESPACE tablespace_name;

Q5: 如何优化PostgreSQL的存储性能？ ​

A5: 可以通过以下方式优化PostgreSQL的存储性能：

  • 选择高性能的存储硬件，如SSD
  • 使用合适的RAID配置
  • 选择高性能的文件系统
  • 使用适当的文件系统挂载选项
  • 合理使用表空间，分离热点数据、WAL文件和索引
  • 根据工作负载调整填充因子
  • 定期维护数据库，如VACUUM和ANALYZE操作

Q6: PostgreSQL的数据文件可以直接备份吗？ ​

A6: 不建议直接备份数据文件，因为这样可能会导致数据不一致。PostgreSQL提供了专门的备份工具，如 pg_dump（逻辑备份）和 pg_basebackup（物理备份），这些工具可以保证备份的一致性。

Q7: 如何查看表的大小？ ​

A7: 可以使用以下命令查看表的大小：

sql
SELECT pg_size_pretty(pg_relation_size('table_name'));  -- 表数据大小
SELECT pg_size_pretty(pg_total_relation_size('table_name'));  -- 表数据+索引大小

Q8: 什么是填充因子？如何调整？ ​

A8: 填充因子是一个百分比，用于控制在插入数据时，每个数据块预留的空闲空间大小。默认情况下，表的填充因子是100，索引的填充因子是90。可以通过以下命令调整填充因子：

sql
CREATE TABLE table_name (...) WITH (fillfactor = 70);  -- 创建表时设置
ALTER TABLE table_name SET (fillfactor = 70);  -- 修改现有表