Encontre aqui (no espelho GitHub do repositório oficial do PostgreSQL) as fontes da vacuumloversão do banco de dados PostgreSQL que eu estava usando naquele exato momento.

Você pode imaginar o resto: eu apenas imito o que o programa está realizando, também simplesmente descrito aqui .

1. Construção de mesa temporária.

Prepare a tabela temporária de referências de objeto ou OIDs.

1.1. Tabela de lob temporária como cópia da tabela de lob completa.

=> SET search_path = pg_catalog;
[...]
=> CREATE TABLE vacuum_lo_removeme AS \
        SELECT oid AS lo FROM pg_largeobject_metadata;
[...]
=> ANALYZE vacuum_lo_removeme;
[...]
=> _

1.2. Limite a tabela de lob temporária.

Execute a consulta que retorna todas as colunas do seu banco de dados digitadas OID:

=> SELECT
     s.nspname, c.relname, a.attname FROM pg_class c, pg_attribute a
     , pg_namespace s, pg_type t
   WHERE
     a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = c.oid
     AND a.atttypid = t.oid AND c.relnamespace = s.oid 
     AND t.typname in ('oid', 'lo') AND c.relkind in ('r','m')
     AND s.nspname !~ '^pg_';

Em seguida, você deve executar esta consulta para todos os resultados obtidos pela consulta anterior (observe ${VARIABLE}que você deve substituir de acordo com sua milhagem) para remover da tabela temporária todos os OIDs de objetos realmente em uso:

=> DELETE FROM vacuum_lo_removeme WHERE \
        lo IN (SELECT ${column} FROM ${SCHEMA}.${TABLE});

No meu caso, foram apenas duas tabelas totalizando cinco colunas e, na verdade, ambas as tabelas vazias, então, naturalmente, as cinco DELETEconsultas não fizeram nada. Se você tiver um subesquema habilitado para OID maior , talvez seja necessário automatizar isso de alguma forma.

2. Desvinculação de objetos grandes.

Finalmente o programa declara um cursor que itera as locélulas restantes da tabela temporária, limpando-as com uma lo_unlinkchamada de função.

2.A. Não faça desta forma.

Eu deveria ter automatizado isso com um procedimento armazenado PLPGSQL, mas como sou péssimo nesse tipo de tarefa, acabei de emitir este forro:

$ echo 'SELECT COUNT(*) FROM vacuum_lo_removeme;' | \
        $MY_AUTOMATABLE_PSQL_MILEAGE 
  count  
---------
 1117233
(1 row)

Então este outro que itera selecionando o primeiro OID órfão na tabela temporária de OIDs órfãos, desvinculando-o e removendo-o da tabela:

$ for i in {1..1117000}; do \
        export oid=$(echo 'SELECT * FROM vacuum_lo_removeme LIMIT 1' | \
        $MY_AUTOMATABLE_PSQL_MILEAGE | grep -v 'lo\|\-\-\-\-\|row\|^$' | \
        sed s/\ //g) && \
        echo "SELECT lo_unlink($oid); \
              DELETE FROM vacuum_lo_removeme WHERE lo = $oid" | \
              $MY_AUTOMATABLE_PSQL_MILEAGE; \
        done
 lo_unlink 
-----------
         1
(1 row)

DELETE 1
 lo_unlink 
-----------
         1
(1 row)

DELETE 1
 lo_unlink 
-----------
         1
(1 row)

DELETE 1
 lo_unlink 
-----------
         1
(1 row)

DELETE 1
ERROR:  must be owner of large object 18448
DELETE 1
ERROR:  must be owner of large object 18449
DELETE 1
ERROR:  must be owner of large object 18450
DELETE 1
ERROR:  must be owner of large object 18451
[...]
 lo_unlink 
-----------
         1
(1 row)

DELETE 1
[...]

Eu sabia que estava subotimizado pra caramba, mas deixei que removesse lentamente esses registros órfãos. Quando não é DBA, esses one-liners podem ser mais fáceis de forjar do que trabalhar algum PLPGSQL idiomático significativo.

Mas isso era muito lento para deixá-lo assim.

2.B. Faça isso melhor do que 2.A (embora ainda não seja uma bala de prata).

Você poderá acelerar a desvinculação de objetos grandes com algo simples, como:

=> CREATE OR REPLACE FUNCTION unlink_orphan_los() RETURNS VOID AS $$
DECLARE
  iterator integer := 0;
  largeoid OID;
  myportal CURSOR FOR SELECT lo FROM vacuum_lo_removeme;
BEGIN
  OPEN myportal;
  LOOP
    FETCH myportal INTO largeoid;
    EXIT WHEN NOT FOUND;
    PERFORM lo_unlink(largeoid);
    DELETE FROM vacuum_lo_removeme WHERE lo = largeoid;
    iterator := iterator + 1;
    RAISE NOTICE '(%) removed lo %?', iterator, largeoid;
    IF iterator = 100 THEN EXIT; END IF;
  END LOOP;
END;$$LANGUAGE plpgsql;

_{OBSERVAÇÃO não é necessário desvincular objetos grandes 100por vez, nem mesmo um número específico xdeles por vez, mas desvincular 100por vez é a base mais segura aplicável à configuração de memória padrão de todos os tamanhos de instância da AWS. Se você usar um número excessivamente grande para isso, corre o risco de falha de função devido à memória atribuída insuficientemente; como você pode fazer dependerá da quantidade de objetos a serem desvinculados e seu tamanho, do tipo e tamanho da instância e do grau de configuração manual aplicada.}

O que é um pouco fácil de forjar para uma pessoa que não é DBA, chamando-o com algo como

$ for i in {0..$WHATEVER}; do echo 'SELECT unlink_orphan_los()' | \
        $YOUR_AUTOMATABLE_PSQL_MILEAGE

Onde ${WHATEVER}é uma constante construída dependendo do tamanho da tabela dos objetos grandes temporários e do número de bloqueios por transação que sua configuração está permitindo (estou usando os padrões do RDS, mas iterando de bashacho que nem preciso saber qual é o maior número de lo_unlinks que o RDBMS está permitindo com current max_locks_per_transaction.

3. VACUUMgrandes tabelas de objetos.

Sugerido por este tópico na lista de discussão postgres , entendi que deveria VACUUM pg_largeobjectdepois de desvincular os objetos.

Não tenho certeza de qual deles é o conjunto mínimo necessário, ou o tempo adequado para sua execução, mas alguns deles podem ajudar, e nenhum deve causar nenhum dano: executei VACUUM ANALYZE VERBOSE pg_largeobject_metadata; VACUUM ANALYZE VERBOSE pg_largeobject; VACUUM FULL ANALYZE VERBOSE pg_largeobject_metadata; VACUUM FULL ANALYZE VERBOSE pg_largeobject;várias vezes enquanto a micro instância estava desvinculando os objetos ( levou muito tempo, infelizmente), primeiro quando cerca de 1/4 dos objetos já foram desvinculados e algum pouco armazenamento foi devolvido ao sistema operacional, em segundo lugar quando cerca de 1/3 dos objetos já foram desvinculados e outro pequeno armazenamento foi devolvido ao SO, em terceiro lugar, quando cerca de 3/5 objetos já foram desvinculados, a instância sofreu um retorno de armazenamento maciço ao SO:

Esse retorno maciço de armazenamento era o que eu estava procurando. Depois de executar a consulta para as maiores tabelas encontradas na página inicial oficial , com menos de 3/4 do total de objetos desvinculados, a tabela de objetos foi reduzida para menos de 3GiB, longe dos 20GiB iniciais e ainda mais inchados.

_{OBSERVAÇÃO automatizar a iteração rápida VACUUM ANALYZE em tabelas tem os mesmos efeitos de longo prazo (de limpar a tabela do excesso de armazenamento em disco) que executar um único VACUUM FULL, mas sem adquirir um bloqueio exclusivo.}

Todos os créditos para https://dba.stackexchange.com/a/174664/83878 mas consegui simplificar a solução no meu caso. Há apenas um esquema no meu caso. Além disso, a primeira instrução search_path = pg_catalognão funciona no meu caso porque não tenho permissões para modificar o catálogo do sistema. Eu também poderia simplificar drasticamente o procedimento de desvinculação com apenas uma única instrução.

1. Criar tabela tmp vacuum_lo_removeme:

CRIAR TABELA SE NÃO EXISTE vacuo_lo_removeme AS SELECT oid AS lo FROM pg_catalog.pg_largeobject_metadata

Nota: Isso é criado no esquema padrão agora.

2. Encontre todos os nomes de tabelas e colunas onde um OID é usado:

SELECT c.relname as tablename, a.attname as columnname FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t WHERE a.attnum > 0 AND NOT a.attisdropped AND a.attrelid = c.oid AND a.atttypid = t.oid AND c.relnamespace = s.oid AND t.typname in ('oid', 'lo') AND c.relkind in ('r','m') AND s.nspname !~ '^pg_' AND relname != 'vacuum_lo_removeme'

3. Itere sobre todas as tabelas e remova os objetos grandes ainda usados ​​da tabela tmp:

DELETE FROM vacuum_lo_removeme WHERE lo IN (SELECT ${columnname} FROM ${tablename}

Exemplo: estou usando Java aqui ( rsé o ResultSet da declaração anterior):

            while (rs.next()) {
                String tablename = rs.getString("tablename");
                String columnname = rs.getString("columnname");
                // remove OID which must be kept
                String removeUsedOids = String.format("DELETE FROM vacuum_lo_removeme WHERE lo IN (SELECT \"%s\" FROM \"%s\")",
                        columnname, tablename);
                stmt = connection.createStatement();
                stmt.executeUpdate(removeUsedOids);
            }

4. Remover OAs órfãos

SELECT count(lo_unlink(lo)) from vacuum_lo_removeme where lo in (select loid from pg_catalog.pg_largeobject)

5. Soltar banco de dados temporário

DROP table vacuum_lo_removeme