如何在 PostgreSQL 中实现数据的压缩存储以节省空间？

PostgreSQL 是一款功能强大的关系型数据库管理系统，在处理大量数据时，有效节省存储空间是一项重要的优化任务。以下将详细探讨在 PostgreSQL 中如何实现数据的压缩存储以节省空间，并提供相应的解决方案和示例代码。

一、使用压缩表

PostgreSQL 从版本 12 开始支持创建压缩表（COMPRESSED TABLES）。压缩表可以显著减少存储空间的使用，特别是对于包含重复值较多或数据类型较大的列。

创建压缩表

要创建一个压缩表，只需在 CREATE TABLE 语句中指定 USING COMPRESSION 选项。

CREATE TABLE compressed_table (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    data TEXT
) USING COMPRESSION;

压缩表的特性

1. 压缩是在页面级别进行的，这意味着同一页面上的相似数据会被压缩。
2. 压缩会增加 CPU 开销，用于解压缩和压缩数据。
3. 适合于数据读取较多而写入相对较少的场景。

示例：比较压缩表和普通表的空间使用

-- 创建普通表
CREATE TABLE normal_table (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    data TEXT
);

-- 插入大量测试数据到普通表和压缩表
-- （此处省略插入数据的具体代码）

-- 查看表的大小
SELECT pg_size_pretty(pg_table_size('normal_table')) AS normal_table_size,
       pg_size_pretty(pg_table_size('compressed_table')) AS compressed_table_size;

二、使用压缩索引

对于经常用于查询和连接的列，可以考虑创建压缩索引（COMPRESSED INDEXES）。这可以在一定程度上减少索引所占用的空间。

创建压缩索引

CREATE INDEX compressed_index ON table_name (column_name) WITH (compress);

示例

假设我们有一个 orders 表，其中 order_date 列经常用于查询和范围搜索。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2)
);

-- 创建压缩索引
CREATE INDEX compressed_order_date_index ON orders (order_date) WITH (compress);

-- 插入大量测试数据

-- 查看索引的大小
SELECT pg_size_pretty(pg_relation_size('compressed_order_date_index')) AS compressed_index_size;

-- 与未压缩的索引大小进行比较

三、选择合适的数据类型

选择合适的数据类型可以有效地减少数据存储空间。

示例：整数类型的选择

如果一个列的值范围在 -32768 到 32767 之间，应该使用 SMALLINT 而不是 INT 。

CREATE TABLE example_table (
    small_value SMALLINT,
    int_value INT
);

文本数据类型的选择

对于较短的文本数据，使用 VARCHAR(n) 而不是 TEXT 。

CREATE TABLE text_type_example (
    short_text VARCHAR(50),
    long_text TEXT
);

四、压缩列值

可以通过函数将列的值进行压缩后存储，并在读取时解压缩。

示例：使用 pgrypto 扩展压缩文本数据

首先确保已经安装了 pgcrypto 扩展。

CREATE EXTENSION pgcrypto;

CREATE TABLE compressed_data (
    id INT PRIMARY KEY,
    compressed_text BYTEA
);

-- 压缩并存储数据
INSERT INTO compressed_data (id, compressed_text)
VALUES (1, compress('This is a long text to be compressed'));

-- 读取并解压缩数据
SELECT uncompress(compressed_text) FROM compressed_data WHERE id = 1;

五、分区表和子分区

通过分区表和子分区，可以将数据表按照一定的规则拆分成多个较小的部分，便于管理和优化存储。

示例：按日期分区

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2021 PARTITION OF sales
    FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');

CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');

-- 插入数据

分区和子分区的优点

1. 可以删除或归档特定分区的数据，而不影响整个表。
2. 对于特定分区的查询可以更高效，因为只需要扫描相关分区。

六、清理和优化表

定期清理不再使用的数据和对表进行 VACUUM 操作，可以回收存储空间。

VACUUM 操作

VACUUM FULL table_name;

示例

假设一张表 old_data 中存在大量已删除的行：

-- 执行 VACUUM FULL 操作
VACUUM FULL old_data;

-- 查看表的大小变化
SELECT pg_size_pretty(pg_table_size('old_data')) AFTER_VACUUM;

注意事项

1. VACUUM FULL 操作会阻塞表上的其他操作，应在合适的时间进行。
2. 对于非常大的表，可能需要考虑使用 pg_repack 等工具。

七、数据归档

对于历史数据或很少访问的数据，可以将其归档到单独的数据库或表中，以释放主表的存储空间。

示例：将旧数据归档到另一张表

CREATE TABLE archived_data LIKE main_data_table;

-- 将旧数据插入到归档表
INSERT INTO archived_data SELECT * FROM main_data_table WHERE create_date < '2021-01-01';

-- 从主表中删除已归档的数据
DELETE FROM main_data_table WHERE create_date < '2021-01-01';

通过上述多种方法的运用，可以在 PostgreSQL 中实现有效的数据压缩存储，节省存储空间并提高数据库的性能。但在实际应用中，需要根据具体的业务需求、数据特点和性能要求，选择合适的方法或组合使用这些方法。

希望以上内容对您有所帮助。

🎉相关推荐

• 🍅关注博主🎗️ 带你畅游技术世界，不错过每一次成长机会！
• 📢学习做技术博主创收
• 📚领书：PostgreSQL 入门到精通.pdf
• 📙PostgreSQL 中文手册
• 📘PostgreSQL 技术专栏