当前位置：首页 > article >正文

PostgreSQL的表碎片

article 2024/9/30 2:11:14

PostgreSQL的表碎片

在 PostgreSQL 中，表碎片化可能会影响数据库性能和存储效率。碎片化通常是由于频繁的插入、更新和删除操作引起的。以下是关于 PostgreSQL 表碎片化的详细信息，包括如何识别和处理表碎片化。

什么是表碎片化？

表碎片化是指表数据在文件系统中的不连续存储，导致读取和写入操作的效率降低。常见原因包括：

频繁的插入和删除：导致数据块中出现“空洞”。
更新操作：由于 PostgreSQL 的 MVCC（多版本并发控制）机制，更新操作会生成新的行版本，原来的空间会被标记为可重用但是不立即回收。

如何检测表碎片化？

表碎片化可以通过分析表和索引的膨胀比例来检测。pg_stat_user_tables 和 pg_relation_size 是两个常用的系统表和函数。

使用 `pgstattuple` 拓展

pgstattuple 拓展可以详细报告表和索引的使用情况，包括死元组和空闲空间。你需要先安装这个扩展：

CREATE EXTENSION pgstattuple;

然后运行如下查询：

SELECT * FROM pgstattuple('your_table_name');

该查询将返回如下信息：

table_len: 表的总大小。
tuple_count: 活跃元组数。
tuple_len: 活跃元组的总大小。
dead_tuple_count: 死元组数（可以视为碎片）。
dead_tuple_len: 死元组的总大小。

输出：

white=# select count(*) from yewu1.t1;
  count  
---------
 1000000
(1 row)

white=# SELECT * FROM pgstattuple('yewu1.t1');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
  51642368 |     1000000 |  40888896 |         79.18 |                0 |              0 |                  0 |      20928 |         0.04
(1 row)

white=# delete from yewu1.t1;
DELETE 1000000
white=# commit;
WARNING:  there is no transaction in progress
COMMIT
white=# SELECT * FROM pgstattuple('yewu1.t1');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
  51642368 |           0 |         0 |             0 |          1000000 |       40888896 |              79.18 |      20928 |         0.04
(1 row)

使用 `pg_stat_user_tables` 和 `pg_relation_size`

SELECT schemaname,
    relname AS table_name,
    n_dead_tup AS dead_tuples,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_stat_user_tables where relname='t1'
ORDER BY
    n_dead_tup DESC;

这个查询显示了每个表的死元组计数和表的总大小。死元组计数较高的表可能存在严重的碎片化问题。

如何处理表碎片化？

一般通过以下两种方式处理表碎片化：

VACUUM:
- VACUUM: 回收死元组空间，但不会重组表。常用于日常维护。
- VACUUM FULL: 清理并重组表，但会锁表，适用于严重碎片化的情况。

-- 回收死元组空间
VACUUM your_table_name;

-- 清理并重组表
VACUUM FULL your_table_name;

REINDEX:
- 重新创建索引，适用于索引碎片化。

REINDEX INDEX your_index_name;

-- 或者重新创建整个表的所有索引
REINDEX TABLE your_table_name;

自动维护

PostgreSQL 提供了自动维护工具：autovacuum。autovacuum 会自动清理和优化表，以减少手动维护的需要。你可以通过配置 postgresql.conf 文件中的相关参数来调整其行为：

autovacuum: 是否启用自动清理（默认启用）。
autovacuum_vacuum_threshold 和 autovacuum_analyze_threshold: 起始清理和分析的死元组数。
autovacuum_vacuum_scale_factor 和 autovacuum_analyze_scale_factor: 起始清理和分析的表大小比例。

这些配置可以通过 SQL 修改：

ALTER SYSTEM SET autovacuum = 'on';
ALTER SYSTEM SET autovacuum_vacuum_threshold = 50;
ALTER SYSTEM SET autovacuum_analyze_threshold = 50;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.2;
ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1;