🥇Quando VACUUM não dá conta — limpamos a tabela manualmente

VACUUM só pode "limpar" da tabela no PostgreSQL aquilo que ninguém pode ver — ou seja, não há nenhuma consulta ativa que tenha começado antes de essas entradas serem alteradas.

E se houver um tipo inconveniente (carga OLAP prolongada em um banco de dados OLTP) ainda assim? Como limpar uma tabela que está mudando ativamente em meio a consultas longas e não cair em armadilhas?

Colocando as armadilhas

Primeiro, vamos definir qual é e como pode surgir o problema que queremos resolver.

Geralmente, essa situação ocorre em uma tabela relativamente pequena, mas na qual ocorrem muitas alterações. Normalmente, isso envolve ou vários contadores/agregados/classificações, nos quais há frequentes atualizações, ou uma fila de buffer para processar algum fluxo contínuo de eventos, cujos registros estão sempre sendo inseridos/excluídos.

Vamos tentar reproduzir um cenário com classificações:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- com este índice construiremos a classificação

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

Enquanto isso, outra conexão inicia uma consulta longa e demorada, coletando alguma estatística complexa, mas não afetando nossa tabela:

SELECT pg_sleep(10000);

Agora, atualizamos muitas vezes o valor de um dos contadores. Para a limpeza do experimento, faremos isso em transações separadas usando dblink, como isso aconteceria na realidade:

DO $$
DECLARE
  i integer;
  tsb timestamp;
  tse timestamp;
  d double precision;
BEGIN
  PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
  FOR i IN 1..10000 LOOP
    tsb = clock_timestamp();
    PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$);
    tse = clock_timestamp();
    IF i % 1000 = 0 THEN
      d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000;
      RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5);
    END IF;
  END LOOP;
  PERFORM dblink_disconnect();
END;
$$ LANGUAGE plpgsql;

NOTICE:  i =  1000, exectime = 0.524
NOTICE:  i =  2000, exectime = 0.739
NOTICE:  i =  3000, exectime = 1.188
NOTICE:  i =  4000, exectime = 2.508
NOTICE:  i =  5000, exectime = 1.791
NOTICE:  i =  6000, exectime = 2.658
NOTICE:  i =  7000, exectime = 2.318
NOTICE:  i =  8000, exectime = 2.572
NOTICE:  i =  9000, exectime = 2.929
NOTICE:  i = 10000, exectime = 3.808

O que aconteceu? Por que até mesmo para uma simples atualização de um único registro o tempo de execução degradou em 7 vezes — de 0.524ms para 3.808ms? E nossa classificação está se construindo cada vez mais devagar.

A culpa é do MVCC

Tudo se deve ao mecanismo MVCC, que obriga a consulta a revisar todas as versões anteriores do registro. Portanto, vamos limpar nossa tabela de versões "mortas":

VACUUM VERBOSE tbl;

INFO:  realizando vacuum em "public.tbl"
INFO:  "tbl": encontrou 0 versões de linhas removíveis, 10026 versões de linhas não removíveis em 45 de 45 páginas
DETALHE:  10000 versões de linha mortas não podem ser removidas ainda, xmin mais antigo: 597439602

Oh, e não há nada para limpar! Paralelamente a consulta em execução está nos atrapalhando — pois pode querer acessar essas versões (e se?), e elas devem estar disponíveis para ele. Portanto, mesmo o VACUUM FULL não nos ajudará.

Vamos "compactar" a tabela

Mas sabemos com certeza que essa consulta não precisa da nossa tabela. Portanto, tentaremos ainda assim recuperar o desempenho do sistema ao descartar tudo que for desnecessário da tabela — pelo menos "manualmente", uma vez que o VACUUM não está funcionando.

Para ser mais claro, vamos considerar o caso de uma tabela de buffer. Ou seja, há um grande fluxo de INSERT/DELETE, e às vezes a tabela fica completamente vazia. Mas se não estiver vazia, devemos manter o seu conteúdo atual.

#0: Оцениваем ситуацию

É claro que podemos tentar fazer algo com a tabela após cada operação, mas isso não faz muito sentido — os custos de manutenção serão claramente maiores do que a capacidade de processamento das consultas alvo.

Vamos estabelecer critérios — "já é hora de agir", se:

o VACUUM foi iniciado há bastante tempo
Esperamos uma carga alta, então que seja 60 segundos desde o [auto]VACUUM.
o tamanho físico da tabela é maior que o alvo
Vamos defini-lo como o dobro do número de páginas (blocos de 8KB) em relação ao tamanho mínimo — 1 blk na heap + 1 blk para cada um dos índices — para uma tabela potencialmente vazia. Se esperamos que sempre haja um certo volume de dados no buffer, é razoável otimizar essa fórmula.

Consulta de verificação

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- aqui seria mais correto fazer * current_setting('block_size')::bigint, mas quem muda o tamanho do bloco?..
, pg_total_relation_size(oid) size
, coalesce(extract('epoch' from (now() - greatest(
    pg_stat_get_last_vacuum_time(oid)
  , pg_stat_get_last_autovacuum_time(oid)
  ))), 1 << 30) vaclag
FROM
  pg_class cl
WHERE
  oid = $1::regclass -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

#1: Все равно VACUUM

Não podemos saber com antecedência o quanto a consulta paralela nos atrapalha — quantos registros se tornaram "obsoletos" desde o seu início. Portanto, quando decidirmos processar a tabela de alguma forma, primeiro devemos realizar VACUUM — diferentemente do VACUUM FULL, não impede que processos paralelos trabalhem com dados em leitura e gravação.

Além disso, pode limpar imediatamente uma grande parte do que gostaríamos de remover. Assim, as consultas subsequentes nesta tabela ocorrerão no "cache quente", o que reduzirá sua duração — e, portanto, o tempo total de bloqueio de outras transações em nossa operação.

#2: Есть кто-нибудь дома?

Vamos verificar – há algo na tabela?

TABLE tbl LIMIT 1;

Se não houver nenhum registro, podemos economizar muito no processamento — simplesmente executando TRUNCATE:

Ela atua como um comando DELETE incondicional para cada tabela, mas muito mais rápido, pois na prática não escaneia as tabelas. Mais importante ainda, ela libera imediatamente espaço em disco, então não é necessário executar a operação VACUUM após.

Cabe a você decidir se deve redefinir o contador de sequência da tabela (RESTART IDENTITY).

#3: Все — по-очереди!

Como estamos operando em condições de alta concorrência, enquanto verificamos a ausência de registros na tabela, alguém pode já ter gravado algo lá. Não devemos perder essa informação, então - o que fazer? Certo, devemos garantir que ninguém consiga gravar.

Para isso, precisamos habilitar SERIALIZÁVEL-a isolação para nossa transação (sim, aqui iniciamos a transação) e bloquear a tabela "definitivamente":

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

Esse nível de bloqueio é determinado pelas operações que queremos realizar.

#4: Конфликт интересов

A intenção aqui é bloquear a tabela — e se, nesse momento, alguém estiver ativo nela, por exemplo, lendo? Ficaremos 'pendurados' aguardando a liberação do bloqueio, enquanto outros desejosos de ler terão que encarar a gente...

Para evitar que isso aconteça, 'nos sacrificamos' — se não conseguirmos obter o bloqueio em um tempo determinado (aceitavelmente breve), receberemos uma exceção do banco, mas pelo menos não impediremos muito os outros.

Para isso, configuraremos uma variável de sessão lock_timeout (para versões 9.3+) ou/e statement_timeout. O principal a lembrar é que o valor de statement_timeout se aplica apenas à próxima instrução. Ou seja, assim nesta concatenação — não fará:

SET statement_timeout = ...;LOCK TABLE ...;

Para não ter que restaurar o valor "antigo" da variável depois, usamos a forma SET LOCAL, que limita o escopo da configuração à transação atual.

Lembre-se de que statement_timeout se aplica a todas as solicitações subsequentes, para que a transação não possa se estender a valores inaceitáveis, se houver muitos dados na tabela.

#5: Копируем данные

Se a tabela não estiver totalmente vazia — os dados terão que ser regravados através de uma tabela temporária auxiliar:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Assinatura ON COMMIT DROP significa que, no momento do término da transação, a tabela temporária deixará de existir, e não será necessário excluí-la manualmente no contexto da conexão.

Como supomos que não há muitos dados "vivos", essa operação deve ser rápida.

Então, isso é tudo! Não se esqueçam de, após a conclusão da transação executar ANALYZE para normalizar as estatísticas da tabela, se necessário.

Vamos compilar o script final

Usamos o seguinte "pseudo-python":

# собираем статистику с таблицы
stat <-
  SELECT
    relpages
  , ((
      SELECT
        count(*)
      FROM
        pg_index
      WHERE
        indrelid = cl.oid
    ) + 1) << 13 size_norm
  , pg_total_relation_size(oid) size
  , coalesce(extract('epoch' from (now() - greatest(
      pg_stat_get_last_vacuum_time(oid)
    , pg_stat_get_last_autovacuum_time(oid)
    ))), 1 << 30) vaclag
  FROM
    pg_class cl
  WHERE
    oid = $1::regclass -- table_name
  LIMIT 1;

# таблица больше целевого размера и VACUUM был давно
if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60:
  -> VACUUM %table;
  try:
    -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    # пытаемся захватить монопольную блокировку с предельным временем ожидания 1s
    -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
    -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE;
    # надо убедиться в пустоте таблицы внутри транзакции с блокировкой
    row <- TABLE %table LIMIT 1;
    # если в таблице нет ни одной "живой" записи - очищаем ее полностью, в противном случае - "перевставляем" все записи через временную таблицу
    if row is None:
      -> TRUNCATE TABLE %table RESTART IDENTITY;
    else:
      # создаем временную таблицу с данными таблицы-оригинала
      -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table;
      # очищаем оригинал без сброса последовательности
      -> TRUNCATE TABLE %table;
      # вставляем все сохраненные во временной таблице данные обратно
      -> INSERT INTO %table TABLE _tmp_swap;
    -> COMMIT;
  except Exception as e:
    # если мы получили ошибку, но соединение все еще "живо" - словили таймаут
    if not isinstance(e, InterfaceError):
      -> ROLLBACK;

É possível não copiar os dados uma segunda vez?Em princípio, pode, se no oid da própria tabela não houver outras atividades vinculadas por parte da PL ou FK do lado do banco de dados:

CREATE TABLE _swap_%table(LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

Executaremos o script na tabela original e verificaremos as métricas:

VACUUM tbl;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
  LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
  CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
  TRUNCATE TABLE tbl;
  INSERT INTO tbl TABLE _tmp_swap;
COMMIT;

relpages | size_norm | size   | vaclag
-------------------------------------------
       0 |     24576 |  49152 | 32.705771

Tudo funcionou! A tabela foi reduzida em 50 vezes, e todos os UPDATEs estão rápidos novamente.

Fonte: habr.com

Quando o VACUUM falha - limpe a tabela manualmente