Gerenciar objetos grandes com o módulo lo
O módulo lo (extensão) é para usuários de banco de dados e desenvolvedores que trabalham com bancos de dados PostgreSQL por meio de drivers JDBC ou ODBC. Tanto no caso do JDBC quanto no caso do ODBC, é esperado que o banco de dados processe a exclusão de objetos grandes quando as referências a eles mudam. No entanto, o PostgreSQL não funciona dessa maneira. No PostgreSQL, não se espera que um objeto seja excluído quando sua referência é alterada. O resultado é que os objetos permanecem no disco, sem referência. A extensão lo inclui uma função usada para acionar alterações de referência para excluir objetos, se necessário.
dica
Para determinar se seu banco de dados pode se beneficiar da extensão lo, use o utilitário vacuumlo para verificar se há objetos grandes órfãos. Para obter contagens de objetos grandes órfãos sem realizar nenhuma ação, execute o utilitário com a opção -n (no-op). Para saber como, consulte vacuumlo utility a seguir.
O módulo lo está disponível para Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 e versões secundárias superiores.
Para instalar o módulo (extensão), você precisa de privilégios rds_superuser. Instalar a extensão lo adiciona o seguinte ao seu banco de dados:
lo: é um tipo de dados de objeto grande (lo) que você pode usar para objetos grandes binários (BLOBs) e outros objetos grandes. O tipo de dadosloé um domínio do tipo de dadosoid. Em outras palavras, é um identificador de objeto com restrições opcionais. Para saber mais, consulte Identificadores de objetona documentação do PostgreSQL. Em termos simples, você pode usar o tipo de dados lopara distinguir suas colunas de banco de dados que contêm referências de objetos grandes de outros identificadores de objeto (OIDs).-
lo_manage: é uma função que você pode usar em gatilhos em colunas de tabela que contêm referências a objetos grandes. Sempre que você excluir ou modificar um valor que faça referência a um objeto grande, o gatilho desvincula o objeto (lo_unlink) de sua referência. Use o gatilho em uma coluna somente se a coluna for a única referência de banco de dados ao objeto grande.
Para obter mais informações sobre o módulo de objetos grandes, consulte lo
Instalar a extensão lo
Antes de instalar a extensão lo, verifique se você tem privilégios rds_superuser.
Como instalar a extensão lo
Use o
psqlpara conectar-se à instância de banco de dados primária do cluster de banco de dados Aurora PostgreSQL.psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --passwordInsira sua senha quando for solicitado. O cliente
psqlconecta-se e exibe o banco de dados de conexão administrativa padrão,postgres=>, como o prompt.Instale a extensão da forma a seguir.
postgres=>CREATE EXTENSION lo;CREATE EXTENSION
Agora é possível usar o tipo de dados lo para definir colunas em suas tabelas. Por exemplo, você pode criar uma tabela (images) que contém dados de imagem raster. Você pode usar o tipo de dados lo para uma coluna raster, conforme mostrado no exemplo a seguir, que cria uma tabela.
postgres=>CREATE TABLE images (image_name text, raster lo);
Usar a função de gatilho lo_manage para excluir objetos
É possível usar a função lo_manage em um gatilho em uma lo ou outras colunas de objetos grandes para limpar (e evitar objetos órfãos) quando a lo é atualizada ou excluída.
Como configurar gatilhos em colunas que fazem referência a objetos grandes
Execute um destes procedimentos:
-
Crie um gatilho BEFORE UPDATE OR DELETE em cada coluna para conter referências exclusivas a objetos grandes, usando o nome da coluna como argumento.
postgres=>CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images FOR EACH ROW EXECUTE FUNCTION lo_manage(raster); -
Aplique um gatilho somente quando a coluna estiver sendo atualizada.
postgres=>CREATE TRIGGER t_raster BEFORE UPDATE OF images FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
-
A função de gatilho lo_manage funciona apenas no contexto de inserção ou exclusão de dados de colunas, dependendo de como você define o gatilho. Isso não tem efeito quando você executa uma operação DROP ou TRUNCATEem um banco de dados. Isso significa que é necessário excluir colunas de objeto de qualquer tabela antes de soltá-las, para evitar a criação de objetos órfãos.
Por exemplo, suponha que você queira descartar o banco de dados que contém a tabela images. Exclua a coluna da maneira a seguir.
postgres=>DELETE FROM images COLUMN raster
Supondo que a função lo_manage seja definida nessa coluna para lidar com exclusões, agora você pode descartar a tabela com segurança.
Remoção de objetos grandes órfãos usando o vacuumlo
O utilitário vacuumlo identifica e remove objetos grandes órfãos dos bancos de dados. Esse utilitário está disponível desde o PostgreSQL 9.1.24. Se os usuários do banco de dados trabalham rotineiramente com objetos grandes, recomendamos executar o vacuumlo ocasionalmente para limpar objetos grandes órfãos.
Antes de instalar a extensão lo, você pode usar o vacuumlo para avaliar se o cluster de banco de dados do Aurora PostgreSQL pode se beneficiar. Para isso, execute vacuumlo com a opção -n (no-op) para mostrar o que seria removido, conforme mostrado no seguinte:
$vacuumlo -v -n -hyour-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgresdocs-lab-spatial-dbPassword:*****Connected to database "docs-lab-spatial-db" Test run: no large objects will be removed! Would remove 0 large objects from database "docs-lab-spatial-db".
Como mostra a saída, objetos grandes órfãos não são um problema para esse banco de dados específico.
Para obter mais informações sobre esse utilitário, consulte vacuumlo
Como funciona o vacuumlo
O comando vacuumlo remove objetos grandes órfãos (LOs) do seu banco de dados PostgreSQL sem afetar ou entrar em conflito com suas tabelas de usuário.
O comando funciona assim:
-
vacuumlocomeça criando uma tabela temporária contendo todos os IDs de objeto (OIDs) dos objetos grandes em seu banco de dados. -
Depois, o
vacuumloexamina todas as colunas do banco de dados que usam os tipos de dadosoidoulo. Se ovacuumloencontrar um OID correspondente nessas colunas, ele removerá o OID da tabela temporária. Ovacuumloverifica somente colunas com os nomes específicosoidoulo, não domínios com base nesses tipos. -
As entradas restantes na tabela temporária representam LOs órfãos, que o
vacuumloremove com segurança.
Aprimorar a performance do vacuumlo
Você pode melhorar o desempenho do vacuumlo aumentando o tamanho do lote usando a opção -l. Isso permite que o vacuumlo processe mais LOs de uma só vez.
Se o sistema tiver memória suficiente e você puder acomodar a tabela temporária completamente na memória, aumentar a configuração temp_buffers no nível do banco de dados poderá melhorar o desempenho. Isso permite que a tabela resida inteiramente na memória, o que pode melhorar o desempenho geral.
A consulta a seguir estima o tamanho da tabela temporária:
SELECT pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size FROM pg_largeobject_metadata;
Considerações para objetos grandes
A seguir, você encontrará algumas considerações importantes a serem observadas ao trabalhar com objetos grandes:
-
Vacuumloé a única solução, pois atualmente não há outro método para remover LOs órfãos. -
Ferramentas como pglogical, replicação lógica nativa e AWS DMS que usam tecnologias de replicação não oferecem suporte à replicação de objetos grandes.
-
Ao projetar seu esquema de banco de dados, evite usar objetos grandes sempre que possível e considere usar tipos de dados alternativos, como
bytea. -
Execute o
vacuumloregularmente, pelo menos semanalmente, para evitar problemas com LOs órfãos. -
Use um gatilho com a função
lo_manageem tabelas que armazenam objetos grandes para ajudar a evitar a criação de LOs órfãos.
