印刻数据库

外观

PostgreSQL 存储引擎与表结构 ​

PostgreSQL的存储引擎是数据库管理系统的核心组件，负责数据的存储、检索和管理。与其他数据库系统不同，PostgreSQL采用单一存储引擎架构，但通过丰富的表类型和扩展机制提供了灵活的存储解决方案。

存储引擎概述 ​

PostgreSQL存储引擎特点 ​

PostgreSQL采用基于关系模型的存储引擎，具有以下特点：

  1. 单一存储引擎架构：PostgreSQL不像MySQL那样支持多种存储引擎，而是采用单一的存储引擎设计
  2. 基于磁盘的持久化存储：数据持久化存储在磁盘上，确保数据安全性
  3. 支持多种表类型：通过表访问方法支持不同类型的表，如堆表、索引组织表、外部表等
  4. 丰富的数据类型支持：支持基本数据类型、复杂数据类型和自定义数据类型
  5. MVCC并发控制：采用多版本并发控制机制，提高并发处理能力
  6. 支持扩展：通过扩展机制可以增强存储引擎功能

存储引擎架构 ​

PostgreSQL的存储引擎架构主要包括：

  1. 访问方法层：提供表和索引的访问接口
  2. 缓存管理层：管理共享缓冲区和WAL缓冲区
  3. 存储管理层：负责数据文件的读写操作
  4. 事务管理层：确保事务的ACID特性
  5. 并发控制层：处理多用户并发访问

表结构设计 ​

表的基本组成 ​

PostgreSQL表由以下部分组成：

  1. 表定义：包含表名、列定义、约束等元数据
  2. 数据文件：存储表的实际数据
  3. 索引：加速数据检索
  4. TOAST表：存储大字段数据
  5. 统计信息：用于查询优化

表的创建与管理 ​

创建表 ​

创建表是数据库设计的基础，PostgreSQL提供了灵活的表创建语法：

sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

修改表 ​

可以使用ALTER TABLE命令修改表结构：

sql
-- 添加列
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

-- 修改列类型
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);

-- 添加约束
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

-- 删除列
ALTER TABLE users DROP COLUMN full_name;

删除表 ​

使用DROP TABLE命令删除表：

sql
DROP TABLE IF EXISTS users;

数据类型选择 ​

选择合适的数据类型对于数据库性能和存储空间至关重要：

数值类型 ​

数据类型存储大小范围应用场景
SMALLINT2字节-32768 到 32767小整数值，如年龄、数量
INTEGER4字节-2147483648 到 2147483647常用整数值，如ID、计数
BIGINT8字节-9223372036854775808 到 9223372036854775807大整数值，如雪花ID、大计数
NUMERIC(precision, scale)可变最高131072位精度精确数值，如货币、科学计算
FLOAT44字节单精度浮点数近似数值，如坐标、测量值
FLOAT88字节双精度浮点数高精度近似数值

字符类型 ​

数据类型特点应用场景
CHAR(n)固定长度，空格填充长度固定的数据，如身份证号、手机号
VARCHAR(n)可变长度，最大n个字符长度可变的数据，如用户名、邮箱
TEXT可变长度，无限制大文本数据，如文章内容、描述

日期时间类型 ​

数据类型存储大小范围应用场景
DATE4字节4713 BC 到 5874897 AD日期值，如生日、订单日期
TIME8字节00:00:00 到 23:59:59.999999时间值，如开始时间、结束时间
TIMESTAMP8字节4713 BC 到 5874897 AD日期和时间，如创建时间、更新时间
INTERVAL16字节-178000000 年 到 178000000 年时间间隔，如持续时间

布尔类型 ​

  • BOOLEAN：存储布尔值（TRUE/FALSE）
  • 存储大小：1字节
  • 应用场景：标记状态、开关等

复杂数据类型 ​

数据类型特点应用场景
ARRAY存储同类型元素的数组标签、多值属性
JSON/JSONB存储JSON数据半结构化数据、配置信息
HSTORE存储键值对动态属性、配置数据
RANGE存储范围值时间范围、数值范围
ENUM存储枚举值状态、类型等有限选项

约束设计 ​

约束用于确保数据的完整性和一致性：

主键约束 ​

  • 唯一标识表中的每一行
  • 自动创建唯一索引
  • 不能包含NULL值
sql
-- 创建表时定义主键
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

-- 现有表添加主键
ALTER TABLE products ADD PRIMARY KEY (id);

外键约束 ​

  • 建立表之间的关系
  • 确保引用完整性
  • 支持级联操作
sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

唯一约束 ​

  • 确保列或列组合的值唯一
  • 可以包含NULL值
sql
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

检查约束 ​

  • 确保列值满足特定条件
  • 支持复杂表达式
sql
ALTER TABLE products ADD CONSTRAINT products_price_check CHECK (price > 0);

非空约束 ​

  • 确保列不能包含NULL值
sql
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

表类型 ​

PostgreSQL支持多种表类型，通过表访问方法实现：

堆表（Heap Table） ​

堆表是PostgreSQL默认的表类型，具有以下特点：

  1. 无序存储：数据按插入顺序存储，不保证物理顺序
  2. 支持MVCC：采用多版本并发控制，每行数据包含版本信息
  3. 支持全表扫描：适合全表查询操作
  4. 适合频繁更新：更新操作只需要标记旧版本，插入新版本

索引组织表（Index-Organized Table） ​

PostgreSQL通过主键索引实现类似索引组织表的功能：

  1. 基于主键的有序存储：数据按主键顺序存储在索引中
  2. 提高主键查询性能：主键查询只需要访问索引，不需要回表
  3. 节省存储空间：不需要单独的堆表存储

外部表（External Table） ​

外部表允许PostgreSQL访问外部数据源，如文件、其他数据库等：

  1. 只读访问：默认情况下只能读取外部数据
  2. 支持多种数据源：文件系统、HDFS、其他数据库等
  3. 通过扩展实现：如file_fdw、postgres_fdw、jdbc_fdw等
sql
-- 创建外部表扩展
CREATE EXTENSION file_fdw;

-- 创建服务器
CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

-- 创建外部表
CREATE FOREIGN TABLE external_orders (
    id INTEGER,
    name VARCHAR(100),
    price NUMERIC(10, 2)
) SERVER file_server
OPTIONS (filename '/path/to/orders.csv', format 'csv', header 'true');

分区表 ​

分区表是将大表分割为多个小表的技术，提高查询和维护性能：

  1. 水平分区：将表按行分割为多个分区
  2. 支持多种分区策略：范围分区、列表分区、哈希分区、复合分区
  3. 透明访问：应用程序无需修改即可访问分区表
  4. 提高查询性能：只扫描相关分区
  5. 便于维护：可以单独维护分区
sql
-- 创建分区表
CREATE TABLE sales (
    id SERIAL,
    product_id INTEGER NOT NULL,
    sale_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

临时表 ​

临时表用于存储会话期间的临时数据：

  1. 会话隔离：只对创建会话可见
  2. 自动清理：会话结束或事务提交后自动删除（取决于创建方式）
  3. 存储在临时表空间：默认存储在pg_temp表空间
sql
-- 创建事务级临时表
CREATE TEMP TABLE temp_users (
    id INTEGER,
    name VARCHAR(100)
);

-- 创建会话级临时表
CREATE TEMP TABLE session_users (
    id INTEGER,
    name VARCHAR(100)
) ON COMMIT PRESERVE ROWS;

未日志表（Unlogged Table） ​

未日志表不写入WAL日志，提高写入性能，但数据安全性降低：

  1. 不写入WAL日志：写入性能比普通表高
  2. 数据安全性低：崩溃后数据会丢失
  3. 不支持复制：不能用于复制环境
  4. 适合临时数据：如缓存、临时结果
sql
CREATE UNLOGGED TABLE cache_data (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT,
    expires_at TIMESTAMP
);

表访问方法 ​

PostgreSQL通过表访问方法（Table Access Method）实现不同类型的表：

内置表访问方法 ​

  1. heap：默认的堆表访问方法
  2. btree：B树索引访问方法
  3. hash：哈希索引访问方法
  4. gist：通用搜索树索引访问方法
  5. spgist：空间分区通用搜索树索引访问方法
  6. gin：倒排索引访问方法
  7. brin：块范围索引访问方法

自定义表访问方法 ​

PostgreSQL支持通过扩展实现自定义表访问方法：

  • TimescaleDB：时序数据访问方法
  • Citus：分布式表访问方法
  • PostGIS：空间数据访问方法

存储优化 ​

表结构优化 ​

  1. 合理设计表结构：

    • 避免过度规范化
    • 合理选择数据类型
    • 适当添加约束

  2. 优化列顺序：

    • 频繁访问的列放在前面
    • 固定长度列放在前面
    • NULL值较多的列放在后面

  3. 避免过度设计：

    • 只添加必要的列
    • 避免使用过于复杂的数据类型
    • 合理使用约束

数据压缩 ​

PostgreSQL支持多种数据压缩方式：

  1. TOAST压缩：

    • 自动压缩大字段数据
    • 支持四种压缩策略：PLAIN、EXTENDED、EXTERNAL、MAIN
    • 默认使用EXTENDED策略（先压缩，再存储到TOAST表）

  2. 表压缩：

    • PostgreSQL 14+支持表级压缩
    • 使用COMPRESSION选项指定压缩算法
    • 支持zstd、pglz等压缩算法
sql
-- 创建压缩表
CREATE TABLE compressed_table (
    id SERIAL PRIMARY KEY,
    data TEXT
) WITH (COMPRESSION = 'zstd');
  1. 外部表压缩：
    • 外部数据可以使用压缩格式
    • 如CSV.GZ、Parquet等压缩格式

分区优化 ​

  1. 选择合适的分区策略：

    • 时间序列数据：使用范围分区
    • 离散值数据：使用列表分区
    • 均匀分布数据：使用哈希分区

  2. 合理设置分区大小：

    • 分区大小建议在1GB到10GB之间
    • 避免过多小分区

  3. 使用分区修剪：

    • 确保查询条件包含分区键
    • 优化查询计划，只扫描相关分区

索引优化 ​

  1. 选择合适的索引类型：

    • B树索引：适合范围查询和排序
    • 哈希索引：适合等值查询
    • GIN索引：适合数组和JSONB数据
    • BRIN索引：适合大表的范围查询

  2. 合理创建索引：

    • 只为频繁查询的列创建索引
    • 避免过多索引影响写入性能
    • 考虑复合索引，覆盖常用查询

  3. 定期维护索引：

    • 重建碎片化的索引
    • 分析索引使用情况
    • 删除无用的索引

TOAST机制 ​

TOAST（The Oversized-Attribute Storage Technique）是PostgreSQL用于存储大字段的机制：

TOAST工作原理 ​

  1. 当行数据大小超过页大小的25%时，大字段会被TOAST处理
  2. TOAST处理包括压缩和/或存储到TOAST表
  3. TOAST表是与主表关联的辅助表，存储大字段数据
  4. 主表中只存储TOAST指针，指向TOAST表中的实际数据

TOAST存储策略 ​

PostgreSQL支持四种TOAST存储策略：

策略描述应用场景
PLAIN不使用TOAST，大字段直接存储小型字段，不需要压缩
EXTENDED先压缩，再存储到TOAST表（默认）大文本、JSON数据等
EXTERNAL不压缩，直接存储到TOAST表已经压缩的数据，如图片、视频
MAIN只压缩，不存储到TOAST表中等大小字段，适合压缩

TOAST优化 ​

  1. 选择合适的TOAST策略：

    • 对于大文本数据，使用EXTENDED策略
    • 对于已经压缩的数据，使用EXTERNAL策略
    • 对于中等大小字段，使用MAIN策略

  2. 监控TOAST使用情况：

    • 使用pg_total_relation_size()查看表和TOAST表的总大小
    • 使用pg_stat_user_tables查看TOAST相关统计信息

案例分析：大表性能优化 ​

背景：某电商平台的订单表数据量超过10亿行，查询性能下降明显。

分析：

  1. 全表扫描时间过长
  2. 索引大小过大，维护成本高
  3. 写入性能下降
  4. 备份和恢复时间过长

优化措施：

  1. 实施分区表：

    • 按订单日期进行范围分区，每个月一个分区
    • 将历史数据分区存储在低成本存储上
    • 查询时只扫描相关分区，提高查询性能

  2. 优化索引：

    • 保留常用查询的索引
    • 删除无用的索引
    • 为分区表创建本地索引

  3. 使用压缩：

    • 对历史数据分区启用zstd压缩
    • 减少存储空间占用，提高I/O性能

  4. 数据归档：

    • 将超过2年的历史数据归档到外部表
    • 减少主表数据量，提高查询性能

  5. 优化查询：

    • 确保查询条件包含分区键
    • 使用合适的索引
    • 避免全表扫描

结果：

  • 查询性能提高了10倍
  • 写入性能提高了5倍
  • 存储空间减少了60%
  • 备份和恢复时间缩短了80%

总结 ​

PostgreSQL的存储引擎虽然采用单一架构，但通过丰富的表类型、数据类型和扩展机制提供了灵活的存储解决方案。理解PostgreSQL的存储引擎和表结构设计对于DBA进行数据库设计、性能优化和维护至关重要。

在实际生产环境中，DBA需要根据业务需求和系统资源情况，合理设计表结构，选择合适的表类型和存储策略，优化数据存储，确保数据库系统的高性能和可靠性。

PostgreSQL的存储引擎设计充分考虑了数据的安全性、可靠性和性能，通过不断的技术创新和扩展机制，能够满足从简单应用到复杂企业级系统的各种存储需求。