PostgreSQL: "Aufgeblähte" Tabellen und Indizes / "verschwendeter" Platz

[Bambam]

Hallo,

in PostgreSQL gibt es den Vacuum-Befehl, um freien Platz zu katalogisieren bzw. aufzuräumen. Freier Platz entsteht, wenn ein Datensatz aktualisiert oder gelöscht wird. PostgreSQL markiert dann den (alten) Platz als frei. Damit ist das Löschen und Einfügen schnell, aber die Größe der Datenbank nimmt zu. Dieses Statement zeigt auf, welche Tabellen und welche Indizes freien Platz enthalten und somit Kandidaten für Vacuum oder Reindex sind.

Code: Alles auswählen

with foo as (
  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  
), rs as (
  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 foo  
), sml as (
  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 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
)

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 sml
ORDER BY wastedbytes DESC

Beispeil des Ergebnisses:

current_database	schemaname	tabkename	tbloat	wasedbytes	iname	ibloat	wastedibytes
KIK2401	public	SALESORDERDETAIL	1,4	24765014016	I20GPN0MUD600CIS	0,0	0
KIK2401	public	SALESORDERDETAIL	1,4	24765014016	SALESORDDET_002	0,0	0
KIK2401	public	SALESORDERDETAIL	1,4	24765014016	I1001ROMNH600CIS00	0,0	0
KIK2401	public	SALESORDERDETAIL	1,4	24765014016	I000OESH6H700CIS00	0,0	0
KIK2401	public	SALESORDERDETAIL	1,4	24765014016	SALESORDDET	0,0	0
KIK2401	public	SALESORDERDETAIL	1,4	24765014016	I30GPN0MUD600CIS	0,0	0
KIK2401	public	SALESORDERDETAIL	1,4	24765014016	SALESORDERDETAIL_pkey	0,0	0
KIK2401	public	MODIFICATIONJOURNAL001	1,5	10211090432	I20GLJ5OKS000CIS	0,1	0
KIK2401	public	MODIFICATIONJOURNAL001	1,5	10211090432	I30GLJ5OKS000CIS	0,1	0
KIK2401	public	MODIFICATIONJOURNAL001	1,5	10211090432	MODIFICATIONJOURNAL001_pkey	0,1	0
KIK2401	public	PROCESSIMPORTERRORMESSAGES	1,4	2286624768	PROCESSIMPORTERRORMESSAGES_pkey	0,2	0
KIK2401	public	PROCESSIMPORTERRORMESSAGES	1,4	2286624768	I10ODV3DBK500CIS	0,2	0
KIK2401	public	PROCESSIMPORTERRORMESSAGES	1,4	2286624768	PROCESSIMPORTERRORMESSAGES_001	0,2	0
KIK2401	public	PROCESSPROTOCOLENTRY	6,5	2004492288	PROCESSPROTOCOLENTRY_005	1,2	72499200

Ein Reindex aus dem vorherigen Beispiel

Code: Alles auswählen

reindex table public."PROCESSPROTOCOLENTRY";

ergibt

current_database	schemaname	tabkename	tbloat	wasedbytes	iname	ibloat	wastedibytes
KIK2401	public	PROCESSPROTOCOLENTRY	6,5	2002132992	PROCESSPROTOCOLENTRY_005	0,0	0

Die einzige Möglichkeit, die Tabellen garantiert zu verkleinern, ist ein full vaccum. Das hat allerdings den Nachteil, das die Tabelle exclusiv gesperrt wird und somit keinerlei Parallele Verarbeitung stattfinden kann. Also Vorsicht!