Fragmentation in PostgreSQL 9.1

Question

How can I check for fragmentation in PostgreSQL 9.1?

Answer 1

Fragmentation is often called bloat in PostgreSQL. It relates to its implementation of MVCC where rows are not updated in place or directly deleted, but are copied with a different ID. Those rows are then made visible or invisible depending on the transaction looking at the data. You can start looking at the wiki Show database bloat for more information on fragmentation issues. Bearing in mind that this query will not give you exact numbers, it's a useful tool to have:

SELECT
 current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
 ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,
 CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
 iname, /*ituples::bigint, ipages::bigint, iotta,*/
 ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,
 CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
 SELECT
 schemaname, tablename, cc.reltuples, cc.relpages, bs,
 CEIL((cc.reltuples*((datahdr+ma-
 (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) 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 (
 SELECT
 ma,bs,schemaname,tablename,
 (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
 schemaname, tablename, hdr, ma, bs,
 SUM((1-null_frac)*avg_width) AS datawidth,
 MAX(null_frac) AS maxfracsum,
 hdr+(
 SELECT 1+COUNT(*)/8
 FROM pg_stats s2
 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
 ) AS nullhdr
 FROM pg_stats s, (
 SELECT
 (SELECT current_setting('block_size')::NUMERIC) AS bs,
 CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
 CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
 FROM (SELECT version() AS v) AS foo
 ) AS constants
 GROUP BY 1,2,3,4,5
 ) AS foo
 ) AS rs
 JOIN pg_class cc ON cc.relname = rs.tablename
 JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
 LEFT JOIN pg_index i ON indrelid = cc.oid
 LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;

Once you know where bloat is happening, you can take measures to clean the table. The operation is called VACUUMing and can be launched on demand. It is also usually done in the background with the autovacuum worker, so please do not disable it.

As noted in https://www.postgresql.org/support/versioning/, PostgreSQL 9.1 has been unsupported for one year, consider upgrading.

• Vérace: Link only answers are not considered the optimal solution on dba.se. Perhaps you could explain briefly what is said on the PostgreSQL bloat page? Even include the (formatted) text of the relevant query.