Das Thema "Anschwellen" von Tabellen und Indizes aufgrund der Implementierung von MVCC ist ein wunder Punkt für PostgreSQL-Benutzer und -Administratoren.
Ich habe es einmal in dem Artikel "DBA: Wenn VACUUM fehlschlägt - Wir löschen die Tabelle manuell" angesprochen , nachdem ich anhand konkreter Beispiele analysiert hatte, wie dramatisch sich die Auswirkung auf die Abfrageleistung auf die Abfrageleistung auswirken kann, wenn die Transaktion nicht rechtzeitig ausgeführt oder aufgrund von verschwendet wurde gleichzeitige Transaktionen VACUUM
.
Neben dem Effekt auf die Geschwindigkeit gibt es aber auch den Effekt auf den besetzten Platz. Sie werden wahrscheinlich sehr überrascht sein, wenn eine Tabelle mit einem einzelnen "Live" -Datensatz nach einem erfolgreichen Durchgang autovacuum
weiterhin Gigabyte Speicherplatz auf teuren SSDs belegt.
Heute werden wir ein wenig über die Struktur der Datenspeicherung in Dateien recherchieren und uns mit dem pg_catalog
Schema befassen , das die PostgreSQL-Datenbank beschreibt, um zu verstehen, wie Tabellen identifiziert werden können, die eindeutig einen verdächtig großen Speicherplatz belegen .
, PostgreSQL 12, - MVCC.
CREATE TABLE bloat AS SELECT 0 i;
CREATE INDEX ON bloat(i);
-- "" pg_temp,
CREATE OR REPLACE PROCEDURE pg_temp.upd() AS $$
UPDATE bloat SET i = i + 1;
$$ LANGUAGE sql; -- , plainSQL-,
DO $$
DECLARE
i integer;
ts timestamp;
BEGIN
FOR i IN 1 .. 1 << 14 LOOP
ts := clock_timestamp();
CALL pg_temp.upd();
RAISE NOTICE '% : %', i, clock_timestamp() - ts;
END LOOP;
END;
$$ LANGUAGE plpgsql;
, UPDATE
. ?
TABLE bloat;
i
-----
16384
?
SELECT pg_relation_size('bloat');
-- 598016
... ! , , HOT-update. VACUUM
- !
VACUUM bloat;
-- ... !
SELECT pg_relation_size('bloat');
-- 598016
, , PostgreSQL, :
- (
pg_class.relfilenode
)
, 1GB
() 8KB
"" VACUUM
(VACUUM FULL
) - , , , "" .
"" , "" - VACUUM
.
pg_catalog
pg_catalog - , - , , , , , ... , , Joel Jacobson. , :
pg_namespace - (schema)
pg_class - - , , matview, ...
pg_depend - , -
-
-
"" ( ):
(
pg_depend
) (pg_namespace
)public
(pg_catalog
information_schema
)
(
pg_class.relpages
) (pg_class.reltuples
)
" " (
pg_statistic
) (pg_attribute
) , NULL- (pg_statistic.stanullfrac
) (pg_statistic.stawidth
) (pg_attribute.attalign
)
-
PostgreSQL, 8KB. , , block_size:
SELECT current_setting('block_size')::integer;
-- 8192
, , 27 , :
" " NULL'
NULL- . NULL- , HeapTupleHeaderData
23 "" .
8 NULL, 1 , 24 . 9, ... 2 , 25, 64-bit - 32 .
"" NULL .
, NULL , pg_statistic.stanullfrac
NULL- .
, , NULL, . -, , , sum/avg/min/max/count
.
! "", "SQL HowTo: 1000 ":
!
, , :
CREATE TABLE nobloat AS
SELECT 0 i;
CREATE INDEX ON nobloat(i);
-- objects-in-scheme
WITH dep AS (
-- https://postgrespro.ru/docs/postgresql/12/catalog-pg-depend
SELECT
objid
FROM
pg_depend
WHERE
(
refclassid
, refobjid
, classid
) = (
'pg_namespace'::regclass
, (
-- https://postgrespro.ru/docs/postgresql/12/catalog-pg-namespace
SELECT
oid
FROM
pg_namespace
WHERE
nspname = 'public' -- schema
LIMIT 1
)
, 'pg_class'::regclass
)
)
-- objects
, cl AS (
-- https://postgrespro.ru/docs/postgresql/12/catalog-pg-class
SELECT
oid
, relname
, relpages
, reltuples
FROM
pg_class cl
WHERE
oid = ANY(ARRAY(TABLE dep)::oid[]) AND
relkind IN ('r', 'm', 'p') -- relation | matview | partition
)
SELECT
T.*
, cl.*
, CASE
WHEN ratio >= 1 THEN (ratio - 1) * def.PAGESIZE
END::bigint repack_effect_size
FROM
(
-- https://postgrespro.ru/docs/postgresql/12/storage-page-layout
SELECT
current_setting('block_size')::integer PAGESIZE
, CASE
WHEN version() ~ 'mingw32' OR version() ~ '64-bit' THEN 8
ELSE 4
END MAXALIGN
) def
, cl
, LATERAL (
WITH cols AS (
SELECT
*
, (sz + szq - 1) / szq * szq sza -- aligned size
FROM
(
-- https://postgrespro.ru/docs/postgresql/12/catalog-pg-attribute
-- https://postgrespro.ru/docs/postgresql/12/catalog-pg-statistic
SELECT
attname
, attnum
, coalesce(st.stanullfrac, 0) nfr
, CASE
WHEN attlen = -1 THEN
CASE
WHEN attstorage IN ('p', 'm') OR stawidth < 2048 THEN -- toast_tuple_target
stawidth
ELSE 8 -- len + toast_id
END
ELSE attlen
END sz
, CASE attalign
WHEN 'c' THEN 1
WHEN 's' THEN 2
WHEN 'i' THEN 4
WHEN 'd' THEN 8
END szq
FROM
pg_attribute at
LEFT JOIN
pg_statistic st
ON (st.starelid, st.staattnum) = (at.attrelid, at.attnum)
WHERE
at.attrelid = cl.oid AND
NOT at.attisdropped AND -- without dropped columns
at.attnum > 0 -- without system columns (tableoid, ctid, ...)
ORDER BY
attnum
) T
)
SELECT
CASE
WHEN reltuples = 0 AND relpages = 0 THEN 1
WHEN reltuples = 0 AND relpages > 0 THEN NULL
ELSE
relpages /
ceil( -- need pages
reltuples /
(
(PAGESIZE - 24) / -- PageHeaderData
ceil(szt::double precision / reltuples)::bigint -- avg tuple size
) -- tuples-per-page
)
END ratio
FROM
(
SELECT
reltuples * 4 + -- ItemIdData
(
(reltuples - fnnt) * hdr_tuple_w_nulls + -- hdr, tuples w/nulls
fnnt * hdr_tuple_wo_nulls -- hdr, tuples wo/nulls
) +
sztc szt -- size of tuples
FROM
(
SELECT
sztc
, fnnt
-- aligned headers
, ceil(hdr_cols_sys::double precision / MAXALIGN)::integer * MAXALIGN hdr_tuple_wo_nulls
, ceil((hdr_cols_sys + hdr_cols_null)::double precision / MAXALIGN)::integer * MAXALIGN hdr_tuple_w_nulls
FROM
(
SELECT
-- https://doxygen.postgresql.org/htup__details_8h_source.html#l00121
-- 4 : t_xmin
-- 4 : t_xmax
-- 4 : t_cid | t_xvac
-- 6 : t_ctid
-- 2 : t_infomask2
-- 2 : t_infomask
-- 1 : t_hoff
23 hdr_cols_sys
, ceil(count(*)::double precision / 8)::integer hdr_cols_null
, sum(ceil(sza * reltuples * (1 - nfr))) sztc -- size of tuples cols
, trunc(exp(sum(ln(CASE WHEN nfr < 1 THEN 1 - nfr ELSE 1 END))) * reltuples) fnnt -- full-not-null-tuples
FROM
cols
) T
) T
) T
) T;
?
ratio | oid | relname | relpages | reltuples | repack_effect_size
-------------------------------------------------------------------
73 | 41333 | bloat | 73 | 15 | 589824
1 | 41337 | nobloat | 1 | 1 | 0
, PostgreSQL , bloat
15 , 1, - ANALYZE
. , ratio
1.
- , "" :