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

发布时间：2024-11-06 12:55

文章目录一、使用压缩表创建压缩表压缩表的特性示例：比较压缩表和普通表的空间使用二、使用压缩索引创建压缩索引示例三、选择合适的数据类型示例：整数类型的选择文本数据类型的选择四、压缩列值示例：使用 `pgrypto` 扩展压缩文本数据五、分区表和子分区示例：按日期分区分区和子分区的优点六、清理和优化表`VACUUM` 操作示例注意事项七、数据归档示例：将旧数据归档到另一张表

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; 12345

压缩表的特性

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

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

-- 创建普通表 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; 12345678910111213

二、使用压缩索引

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

创建压缩索引

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

示例

假设我们有一个 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; -- 与未压缩的索引大小进行比较

12345678910111213141516

三、选择合适的数据类型

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

示例：整数类型的选择

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

CREATE TABLE example_table ( small_value SMALLINT, int_value INT ); 1234

文本数据类型的选择

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

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

四、压缩列值

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

示例：使用 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; 12345678910111213

五、分区表和子分区

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

示例：按日期分区

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'); -- 插入数据 12345678910111213

分区和子分区的优点

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

六、清理和优化表

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

VACUUM 操作

VACUUM FULL table_name; 1

示例

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

-- 执行 VACUUM FULL 操作 VACUUM FULL old_data; -- 查看表的大小变化 SELECT pg_size_pretty(pg_table_size('old_data')) AFTER_VACUUM; 12345

注意事项

VACUUM FULL 操作会阻塞表上的其他操作，应在合适的时间进行。对于非常大的表，可能需要考虑使用 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'; 1234567

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

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

美丽的分割线