Раздувание таблицы на Postgres

У меня есть небольшое (~ 200 ГБ) хранилище данных, работающее на Postgres 9.5.15 в экземпляре AWS RDS.

Для надежности я вставляю новые данные в аналитическую схему (результат ELT) следующим образом:

  1. вставить новый фрагмент
  2. удалить старый фрагмент с помощью команды удаления
  3. вакуум

Я знаю, что Postgres soft удаляет кортежи, когда вы выполняете команды удаления или обновления. Это не проблема, связанная с размерами таблиц. Проблема в том, что мертвые кортежи не удаляются ни при явной очистке по (3), ни при обычной автоочистке. Итак, если конвейер выполняется много раз, я получаю ОГРОМНОЕ раздувание таблицы, которое сильно влияет на производительность таблицы, не говоря уже о дополнительной трате памяти.

Более того, когда я начал расследование, я обнаружил, что даже системные таблицы имеют эту проблему:

schemaname | relname | n_live_tup | n_dead_tup | ratio%
pg_catalog | pg_attribute | 46081 | 8339587 | 18097
pg_catalog | pg_depend | 27375 | 2490507 | 9097
pg_catalog | pg_statistic20094 | 1208474 | 6013

Это может ухудшить общую производительность экземпляра настолько, что я даже представить себе не могу. Когда я пытаюсь сделать VACUUM FULL VERBOSE pg_catalog.pg_attribute, это дает мне это:

"pg_attribute": found 0 removable, 8387117 nonremovable row versions in 152494 pages

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

Моя единственная гипотеза заключается в том, что вакуум требует, чтобы все мертвые кортежи помещались в память, которая ограничена настройкой maintenance_work_mem (127 МБ для нашего примера), и нам нужно увеличить это значение, но сначала мне нужно стороннее мнение.


postgresql soft-delete database-administration
person AlexYes    schedule 23.12.2019    source источник
comment
Обычные операторы DML не повлияют на pg_attribute или pg_depend. Вы очень часто создаете и удаляете (временные) таблицы? Обычно это одна из причин раздувания системных каталогов. И если vacuum full ничего не удаляет, это означает, что у вас есть хотя бы одна открытая транзакция, которая использовала таблицы, которым принадлежали атрибуты.   -  person a_horse_with_no_name    schedule 23.12.2019
comment
Я делаю это как часть конвейеров ELT, например, для одной вставки таблицы требуется 3 промежуточных таблицы перед основной вставкой, и это делается каждые 3 часа. Что касается транзакций, я проверяю pg_stat_activity, указателей на эти таблицы нет.   -  person AlexYes    schedule 23.12.2019
comment
pg_attribute: найдено 0 съемных, 8387117 неудаляемых версий строк на 152494 страницах. Неудаляемые включают как живые, так и недавно умершие. Что, если вы просто сделаете select count(*) from pg_attribute?   -  person jjanes    schedule 23.12.2019
comment
Выдали ли ваши подробные очистки такие строки, как DETAIL: 900000 версий мертвых строк, которые еще не могут быть удалены.   -  person jjanes    schedule 23.12.2019
comment
Вы можете временно увеличить maintenance_work_mem для одной команды, а затем вернуться к значению по умолчанию, см. SET LOCAL, хотя не уверен, что это поможет.   -  person Christophe Roussy    schedule 23.12.2019
comment
pg_stat_activity не будет содержать указателей на эти таблицы, но будет отображать незафиксированные транзакции (соединения, которые простаивают в транзакции). Вы постоянно удаляете и заново создаете промежуточные таблицы? Если да, то почему?   -  person a_horse_with_no_name    schedule 23.12.2019
comment
@jjanes дает мне 45978, что очень близко к количеству живых кортежей в приведенном выше примере.   -  person AlexYes    schedule 24.12.2019
comment
@jjanes это только что дало мне то, что я указал выше: ... 8387117 nonremovable row versions   -  person AlexYes    schedule 24.12.2019
comment
@a_horse_with_no_name под указателями я имею в виду имя таблицы в тексте запроса, чтобы было ясно, что запрос, который я вижу в представлении активности, касается таблицы. Также я пробовал некоторые запросы на основе pg_locks для выявления возможных блокировок и взаимоблокировок в любых таблицах, и они ничего не вернули.   -  person AlexYes    schedule 24.12.2019
comment
@a_horse_with_no_name, что касается промежуточных таблиц, как я уже сказал, это среда DWH, поэтому для преобразования данных существует сложная бизнес-логика. Вместо того, чтобы писать 1000-строчный запрос с примерно 15 операторами/подзапросами WITH, я разделил его на более мелкие фрагменты, которые будут отвечать за отдельные уровни логики, а затем соединил, и результат был вставлен в целевую таблицу, которая полностью описывает бизнес-объект ( как клиент или контракт).   -  person AlexYes    schedule 24.12.2019
comment
Даже в версии 9.5 я всегда получаю строку DETAIL, сообщающую, что версии мертвых строк nnn еще не могут быть удалены.   -  person jjanes    schedule 24.12.2019
comment
чтобы было ясно, что запрос, который я вижу в представлении активности, касается таблицы. Открытый снимок предотвратит очистку кортежей, даже если транзакция никогда не касалась этой конкретной таблицы.   -  person jjanes    schedule 24.12.2019
comment
Я понимаю концепцию промежуточных таблиц. Но вы так и не ответили на вопрос, постоянно ли вы их сбрасываете и создаете или создаете только один раз (или у вас есть соединения, которые простаивают в транзакциях).   -  person a_horse_with_no_name    schedule 24.12.2019
comment
@a_hourse_with_no_name да, я удаляю и воссоздаю их или использую временные таблицы. Просто чтобы не загромождать пространство имен. Было бы разумнее усекать после каждого выполнения?   -  person AlexYes    schedule 24.12.2019
comment
@a_horse_with_no_name, и у меня нет соединений, которые простаивают в транзакции. Либо бездействует, либо активен.   -  person AlexYes    schedule 24.12.2019
comment
@AlexYes У вас есть новости по этому поводу? Я думаю, что столкнулся с точно такой же проблемой.   -  person Chris Sekas    schedule 18.01.2021
comment
@ChrisSekas Я придумал переупаковывать столы по расписанию в конце - добавил ответ здесь   -  person AlexYes    schedule 18.01.2021

Ответы (1)


arrow_upward
1
arrow_downward

В итоге я написал свои собственные функции для переупаковки данных и запуска их по расписанию:

-- repack an individual table
CREATE OR REPLACE FUNCTION admin.repack_table(text)
RETURNS text
AS $$
DECLARE SQL text;
BEGIN

    SELECT
     'CREATE TEMP TABLE t1 (LIKE '||$1||');'||chr(10)||
     'INSERT INTO t1 SELECT * FROM '||$1||';'||chr(10)||
     'TRUNCATE TABLE '||$1||';'||chr(10)||
     'INSERT INTO '||$1||' SELECT * FROM t1;'||chr(10)||
     'DROP TABLE t1;'||chr(10)||
     'ANALYZE '||$1||';'
    INTO SQL;
    EXECUTE SQL;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;

-- repack all tables in certain schema (with an optional threshold for N of dead tuples)
CREATE OR REPLACE FUNCTION admin.repack_schema(text,int default 5000)
RETURNS table (table_name text)
AS $$
DECLARE SQL text;
BEGIN
RETURN QUERY (
    with
     schema as (select $1)
    select admin.repack_table(t.table_schema||'.'||t.table_name)
    from information_schema.tables t
    where t.table_schema=(select * from schema)
    and t.table_name in (
        select relname
        from pg_stat_all_tables
        where schemaname=(select * from schema)
        and n_dead_tup>$2
        and n_live_tup<1000000 -- avoid repacking too large tables
    )
);
END;
$$ LANGUAGE plpgsql;
person AlexYes    schedule 18.01.2021