翻译：小次郎
2016/11月2日，Scott Mead 原文链接
Postgresql的存储管理器的任务是满足ACID兼容的复杂的工作。 拥有一套有据可查的一系列算法，我不会在这里赘述了。 像任何存储系统或数据库， Postgresql的表就可以开始采取自由空间（因为它有时也被称为,膨胀)。 虽然使用的自由空间是不是一件坏事，也有一些情况下它可以变得笨拙。
在一个高级别：
- 在一个UPDATE或DELETE Postgres的，该行不会被删除。它被标记为重新使用“自由空间”。@H_502_10@
- 如果有适合的自由空间（或者你有没有新行）没有入境行，也被标记为空闲的“死行”，现在占用的磁盘空间。@H_502_10@
有时候我们看到的是，只有几千行总计不到1 GB的表,我已经看到了使用的磁盘空间100GB。 这是由一个老版本的石英调度使用的队列。 该表是高容量，纯INSERT和DELETE。 从来没有为死空间被发现并重新使用的任何机会。
因此， 我们已经确定的交通模式具有高容量UPDATE和DELETE可导致膨胀， 但是，实际上，我怎么监督呢？
有真的在这里，虽然两所学校：
##难道膨胀一定不好？
在继续寻找膨胀之前，让我们理解其含意。 如果你已经花了很多时间与数据库管理员， 你可能听说过他们讨论的自由空间。 自由空间是已被分配的磁盘空间，并且可用于使用。 这种类型的可用空间是非常有效的使用与分配新的块。 我们的目标不是消除所有膨胀。我们的目标是消除过度膨胀。 这里的查询将要或者明确发现或估计的关系的可用空间的量。 我不建议去上对所有建成自由空间的一个堂吉诃德式的探索。只是消除多余的膨胀。
深层扫描
第一个策略将是确定膨胀的最精确的方法。 您可以看到到底有多少膨胀是在表中。没有与此战略的几个问题：
换句话说：这将需要很长的时间，最有可能影响系统性能。
我今天不打算深入了解此选项，如果你有兴趣，看看在pgstattuple等模块。 （注：pgstattuple等也有一个pgstattuple_approx）中的最新版本的功能。 虽然它确实接近膨胀（使深扫描稍快），但它仍然扫描的关系。
##系统元数据(catalog)
Postgres的收集有关表和索引的统计信息， 以便有效地对它们进行查询（这是通过“分析”，另一天的讨论完成）。 一些良好的放置查询可以使用这些统计信息来估计表中的膨胀的量。 由于我们使用的是元数据，不需要深度扫描。
缺点是，我们不会有100％准确的统计数据，这只是一个估计值。
我喜欢一个战略，让我快速生成潜在问题的进一步挖掘之前的列表。 从本质上讲，我开始估计再钻，在寻找更多的细节。
很高兴，我们拥有两个很受欢迎的表膨胀估计查询。
最为著名的是作为check_postgres.pl(Nagios 中的Postgresql插件)的一部分。 就个人而言，我还发现他有一个难点是他的结果上手稍稍难一点。
我目前最喜欢的查询语句是另外一个，提供了潜在膨胀问题，结果可读性好 链接:
/* WARNING: executed with a non-superuser role,* the query inspect only tables you are granted to read. * This query is compatible with Postgresql 9.0 and more */ SELECT current_database(),schemaname,tblname,bs*tblpages AS real_size,(tblpages-est_tblpages)*bs AS extra_size,CASE WHEN tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages)/tblpages::float ELSE 0 END AS extra_ratio,fillfactor,(tblpages-est_tblpages_ff)*bs AS bloat_size,CASE WHEN tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float ELSE 0 END AS bloat_ratio,is_na --,(pst).free_percent + (pst).dead_tuple_percent AS real_frag FROM ( SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,tblpages,bs,tblid,heappages,toastpages,stattuple.pgstattuple(tblid) AS pst FROM ( SELECT ( 4 + tpl_hdr_size + tpl_data_size + (2*ma) - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END ) AS tpl_size,bs - page_hdr AS size_per_block,(heappages + toastpages) AS tblpages,reltuples,toasttuples,page_hdr,is_na FROM ( SELECT tbl.oid AS tblid,ns.nspname AS schemaname,tbl.relname AS tblname,tbl.reltuples,tbl.relpages AS heappages,coalesce(toast.relpages,0) AS toastpages,coalesce(toast.reltuples,0) AS toasttuples,coalesce(substring( array_to_string(tbl.reloptions,' ') FROM '%fillfactor=#"__#"%' FOR '#')::smallint,100) AS fillfactor,current_setting('block_size')::numeric AS bs,CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,24 AS page_hdr,23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,sum( (1-coalesce(s.null_frac,0)) * coalesce(s.avg_width,1024) ) AS tpl_data_size,bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na FROM pg_attribute AS att JOIN pg_class AS tbl ON att.attrelid = tbl.oid JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace JOIN pg_stats AS s ON s.schemaname=ns.nspname AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid WHERE att.attnum > 0 AND NOT att.attisdropped AND tbl.relkind = 'r' GROUP BY 1,2,3,4,5,6,7,8,9,10,tbl.relhasoids ORDER BY 2,3 ) AS s ) AS s2 ) AS s3; -- WHERE NOT is_na -- AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1
正如我所说，这些查询的复杂性是很高的。让我们来看看在输出（如图片，因为文本换行）
current_database | schemaname | tblname | real_size | extra_size | extra_ratio | fillfactor | bloat_size | bloat_ratio | is_na ------------------+------------+------------------------+------------+------------+-------------------+------------+--------------+-------------------+------- postgres | public | pgbench_accounts | 1411891200 | 39084032 | 2.76820423556716 | 10 | -12316139520 | 0 | f postgres | public | pgbench_branches | 1105920 | 1097728 | 99.2592592592593 | 10 | 1064960 | 96.2962962962963 | f postgres | public | pgbench_history | 16867328 | 73728 | 0.437105390966489 | 100 | 73728 | 0.437105390966489 | f postgres | public | pgbench_tellers | 21422080 | 21372928 | 99.7705544933078 | 10 | 20979712 | 97.9349904397706 | f postgres | public | t_stats | 32768 | 0 | 0 | 100 | 0 | 0 | f postgres | snapshots | snap | 65536 | 0 | 0 | 100 | 0 | 0 | f postgres | snapshots | snap_databases | 2424832 | 327680 | 13.5135135135135 | 100 | 327680 | 13.5135135135135 | t postgres | snapshots | snap_indexes | 9330688 | 327680 | 3.51185250219491 | 100 | 327680 | 3.51185250219491 | t postgres | snapshots | snap_pg_locks | 5980160 | 483328 | 8.08219178082192 | 100 | 483328 | 8.08219178082192 | f postgres | snapshots | snap_settings | 24576 | 0 | 0 | 100 | 0 | 0 | f postgres | snapshots | snap_stat_activity | 1449984 | 65536 | 4.51977401129944 | 100 | 65536 | 4.51977401129944 | t postgres | snapshots | snap_statio_all_tables | 29868032 | 974848 | 3.26385079539221 | 100 | 974848 | 3.26385079539221 | t postgres | snapshots | snap_user_tables | 5472256 | 270336 | 4.94011976047904 | 100 | 270336 | 4.94011976047904 | t
我们来到这里是一个非常不错的概述， 显示我们(以字节为单位所有尺寸)的关系的real_size，有多少是额外的，什么额外的比例。最后一列，is_na（不适用）是重要的。事实上，如果你正在使用的“名称”的数据类型此列是真实的。 “名称”数据类型抛出了臃肿的估计，可以给你的无效数据。
由于这些都是估计，我们必须把它们和一粒盐。 你可能想，如果你不熟悉的工作量已经把它从pg_stat_user_tables和比较，以你的使用率。
回收空间，这是另一天的话题，但是，看看VACUUM FULL(需要全表锁） pg_repack(使用最少的锁定回收空间扩展).
现在您对臃肿的一些信息。在这一点上，你可以用pgstattuple等模块， 或者开始更积极的真空瞄准一些嫌疑/或移动压实。
除此(之前提到的)之外查询，还有一个Nagios中流行的check_postgres.pl脚本：
SELECT
current_database() AS db,tablename,reltuples::bigint AS tups,relpages::bigint AS pages,otta,ROUND(CASE
WHEN otta=0
OR sml.relpages=0
OR sml.relpages=otta THEN
0.0
ELSE sml.relpages/otta::numeric END,1) AS tbloat,CASE
WHEN relpages < otta THEN
0
ELSE relpages::bigint - otta
END AS wastedpages,CASE
WHEN relpages < otta THEN
0
ELSE bs*(sml.relpages-otta)::bigint
END AS wastedbytes,CASE
WHEN relpages < otta THEN
'0 bytes'::text
ELSE (bs*(relpages-otta))::bigint || ' bytes'
END AS wastedsize,iname,ituples::bigint AS itups,ipages::bigint AS ipages,iotta,ROUND(CASE
WHEN iotta=0
OR ipages=0
OR ipages=iotta THEN
0.0
ELSE ipages/iotta::numeric END,1) AS ibloat,CASE
WHEN ipages < iotta THEN
0
ELSE ipages::bigint - iotta
END AS wastedipages,CASE
WHEN ipages < iotta THEN
0
ELSE bs*(ipages-iotta)
END AS wastedibytes,CASE
WHEN ipages < iotta THEN
'0 bytes'
ELSE (bs*(ipages-iotta))::bigint || ' bytes'
END AS wastedisize,CASE
WHEN relpages < otta THEN
CASE
WHEN ipages < iotta THEN
0
ELSE bs*(ipages-iotta::bigint)
END ELSE
CASE
WHEN ipages < iotta THEN
bs*(relpages-otta::bigint)
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint)
END END AS totalwastedbytes
FROM
(SELECT nn.nspname AS schemaname,cc.relname AS tablename,COALESCE(cc.reltuples,0) AS reltuples,COALESCE(cc.relpages,0) AS relpages,COALESCE(bs,0) AS bs,COALESCE(CEIL((cc.reltuples*((datahdr+ma- (CASE
WHEN datahdr%ma=0 THEN
ma
ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,COALESCE(c2.relname,'?') AS iname,COALESCE(c2.reltuples,0) AS ituples,COALESCE(c2.relpages,0) AS ipages,COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation,assumes ALL cols
FROM pg_class cc
JOIN pg_namespace nn
ON cc.relnamespace = nn.oid
AND nn.nspname <> 'information_schema'
LEFT JOIN
(SELECT ma,foo.nspname,foo.relname,(datawidth+(hdr+ma-(case
WHEN hdr%ma=0 THEN
ma
ELSE hdr%ma END)))::numeric AS datahdr,(maxfracsum*(nullhdr+ma-(case
WHEN nullhdr%ma=0 THEN
ma
ELSE nullhdr%ma END))) AS nullhdr2
FROM
(SELECT ns.nspname,tbl.relname,hdr,ma,SUM((1-coalesce(null_frac,0))*coalesce(avg_width,2048)) AS datawidth,MAX(coalesce(null_frac,0)) AS maxfracsum,hdr+
(SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0
AND s2.schemaname = ns.nspname
AND s2.tablename = tbl.relname ) AS nullhdr
FROM pg_attribute att
JOIN pg_class tbl
ON att.attrelid = tbl.oid
JOIN pg_namespace ns
ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats s
ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname
AND s.inherited=false
AND s.attname=att.attname,(SELECT
(SELECT current_setting('block_size')::numeric) AS bs,CASE
WHEN SUBSTRING(SPLIT_PART(v,' ',2)
FROM '#"[0-9]+.[0-9]+#"%' for '#') IN ('8.0','8.1','8.2') THEN
27
ELSE 23
END AS hdr,CASE
WHEN v ~ 'mingw32'
OR v ~ '64-bit' THEN
8
ELSE 4
END AS ma
FROM
(SELECT version() AS v) AS foo ) AS constants
WHERE att.attnum > 0
AND tbl.relkind='r'
GROUP BY 1,5 ) AS foo ) AS rs
ON cc.relname = rs.relname
AND nn.nspname = rs.nspname
LEFT JOIN pg_index i
ON indrelid = cc.oid
LEFT JOIN pg_class c2
ON c2.oid = i.indexrelid ) AS sml;
生活因使用Postgresql 而精彩！
