Освобождение свободного места после delete в PostgreSQL

4 minute read
code
,

img

Суть проблемы

У вас есть таблицы, либо ряд таблиц, строки которых нужно очистить и единственный способ, которым вы можете это сделать - это операция DELETE.

delete from schema.table where condition;

Конечная цель изменения данных - это особождение места (дискового пространства), но на деле, после удаления данных - свободного места больше не становится.

Частично эту проблему решают команды vacuum и vacuum full, но у первых двух есть свои проблемы, подробности есть в документации.

Если кратко - vacuum освободит место если были удалены недавно записанные данные, условно “верхние” в дата файле.
А vacuum full - корректно освободит место, но для этого ему потребуется столько же места на диске, сколько уже занимает эта таблица, так как под капотом - это простая переливка данных из таблицу в таблицу.

Решение

Если у вас много места на диске - просто сделайте vacuum full schema.table.
Исправлять ситуацию будем с помощью утилиты pgcompacttable, ссылка github.

Ставим зависимости:

  • on Debian-based Linux OS with apt-get install libdbi-perl libdbd-pg-perl
  • RedHat/Centos with yum install perl-Time-HiRes perl-DBI perl-DBD-Pg

Переходим в интерактивный режим psql:

sudo -u postgres psql

\c your_database
create extension if not exists pgstattuple;

grant execute on function pgstattuple(text) to <db user>;
grant execute on function pgstattuple(regclass) to <db user>;
grant execute on function pgstattuple_approx to <db user>;

-- Если вы забыли пароль от админской уз postgres
alter user postgres with password 'your new admin password';
\q

Далее забираем себе локально perl скрипт.

git clone https://github.com/dataegret/pgcompacttable.git

И запускаем с необходимыми параметрами, полный список можно посмотреть через:

perl pgcompacttable --man

Команда:

perl pgcompacttable \
--dbname <database_name> \
-n <schema_name> \
-h localhost \
-p 5432 \
-U <user> \
-W <password> \
-t <table_name> \
-f

Полезное видео на тему хранения данных в Postgresql

Какие таблицы проблемные?

Запускаем SQL запрос для проверки данных в таблицах - смотрим на значения в 5 столбце (wastedbytes)

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,
  pg_size_pretty(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
--where iname like '%ccnew%'
ORDER BY (CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT end) DESC;

Пояснение проблемы

In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control (MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.

When ever you delete a row, you essentially mark it as inactive for your snapshot. Other snapshots started by prior transactions can still see it. This means it’s still on disk.

Rows aren’t stored in their own file on disk. In order to delete that row, even when you want, you have to essentially rewrite the files stored on disk without that row. DELETE does not do this and under normal circumstances that’s perfectly fine because dead rows that aren’t visible to a transaction can be reclaimed internally by a simply VACUUM. After being marked for reuse new data can be stored in the heap.

So essentially, only operations that internally cause the table’s heap to be rewritten reclaim space. Those operations are:

  • CLUSTER
  • VACUUM FULL
  • TRUNCATE
  • ALTER TABLE (forms that rewrite the table)

All of these operations require an ACCESS EXCLUSIVE lock. And, TRUNCATE is such a sledge hammer it even violates MVCC.

Подписывайтесь!

Большое спасибо всем за внимание! Мой телеграм канал Telegram_logo artydev & Co — подписывайтесь!