# PosgreSQL 9.0 High Performance中文版瑕疵

Measuing index bloat:

Assuming you have a version of PostgreSQL before 9.0,
where VACUUM FULL will bloat indexes, you can easily get
this table into the situation where its data pages can be
cleaned up but not its indexs. Just sparsely deleting some of
the rows in the table, so that no index pages can be
reclaimed, then issue VACUUM FULL:

DELETE FROM pgbench_accounts
WHERE aid % 2 = 0;
VACUUM FULL;

The table will be named just "accounts"on PostgreSQL versions before
8.4. Now running the index ratio query shows a very different propotion:

relname | accounts_pkey
index_ration | 0.27
index_size | 43 MB
table_size | 155 MB

Now the idex is 27% of the size of the table--clearly quite bloated compared with the
original, compact representation. While the exact threshold where the ratio is so far
off that an index is obviously bloated varies depending on the structure of the table
and its index, if you take a periodic snapshot of this data it's possible to see if
bloat growth is increasing or not.

Assuming you have a version of PostgreSQL before 9.0,where VACUUM FULL will bloat indexes, you can easily get this table into the situation where its data pages can be cleaned up but not its indexs. Just sparsely deleting some ofthe rows in the table, so that no index pages can be reclaimed, then issue VACUUM FULL:

DELETE FROM pgbench_accounts
WHERE aid % 2 = 0;
VACUUM FULL;

假设用户所使用的PostgreSQL版本是9.0以前的版本，这些版本中的VACUUM FULL操作会引起索引膨胀，所创建的表可以很容易清理数据页面而不是清理其索引。该操作只是删除表中的某些行，所以没有索引页面可以进行回收，这就是VACUUM FULl引起的问题。

假定你有一个9.0版之前的PostgreSQL,那么在此版本中VACUUM FULL会导致索引膨胀：你可以很容易地创造这样一种场景：表的数据页被清除，但索引页却没有得到清除----如果你稀疏地删除表的一些行(因此也保留一些行)，这样一来就没有相应的索引页可以被回收；然后你再运行 VACUUM FULL来 看看：

DELETE FROM pgbench_accounts WHERE aid % 2 = 0;
VACUUM FULL;

The table will be named just "accounts"on PostgreSQL versions before
8.4. Now running the index ratio query shows a very different propotion:

relname | accounts_pkey
index_ration | 0.27
index_size | 43 MB
table_size | 155 MB

Now the idex is 27% of the size of the table--clearly quite bloated compared with the
original, compact representation. While the exact threshold where the ratio is so far
off that an index is obviously bloated varies depending on the structure of the table
and its index, if you take a periodic snapshot of this data it's possible to see if
bloat growth is increasing or not.

在PostgreSQL8.4之前的版本中，该表的名称为 "accounts"。现在运行索引比例查询得到的结果会有不同的比例。
relname | accounts_pkey
index_ration | 0.27
index_size | 43 MB
table_size | 155 MB
现在索引的大小紧凑地表示为表的27%。显然相比于原始的大小它膨胀得很厉害。而实际上比例阀值一直关闭，索引膨胀很大程度上与表和索引的结构有关，如果用户执行数据的定期快照，可以看到膨胀是否增长。

(顺便提一句)在PostgreS8.4版以前，上述的表名称为普通的"accounts"。现在运行前述的索引比率查询会显示一个非常不同的比例值：

relname | accounts_pkey
index_ration | 0.27
index_size | 43 MB
table_size | 155 MB

posted @ 2013-08-16 17:40  健哥的数据花园  阅读(885)  评论(0编辑  收藏  举报