# Gerenciar a contenção de TOAST OID no Amazon RDS para PostgreSQL TOAST (The Oversized-Attribute Storage Technique) é um recurso do PostgreSQL projetado para lidar com grandes valores de dados que excedem o tamanho normal de blocos de banco de dados de 8 KB. O PostgreSQL não permite que linhas físicas abranjam vários blocos. O tamanho do bloco atua como um limite superior no tamanho da linha. O TOAST supera essa restrição dividindo grandes valores de campo em partes menores. Ele os armazena separadamente em uma tabela TOAST dedicada vinculada à tabela principal. Para acessar mais informações, consulte a [documentação de implementação e mecanismo de armazenamento TOAST do PostgreSQL](https://www.postgresql.org/docs/current/storage-toast.html). **Topics** + [ ## Noções básicas sobre operações TOAST ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.HowWorks) + [ ## Identificar problemas de performance ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.PerformanceChallenges) + [ ## Recomendações ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Recommendations) + [ ## Monitoramento ](#Appendix.PostgreSQL.CommonDBATasks.TOAST_OID.Monitoring) ## Noções básicas sobre operações TOAST O TOAST executa a compactação e armazena grandes valores de campo fora da linha. O recurso atribui um OID (Identificador de Objeto) exclusivo a cada bloco de dados superdimensionados armazenado na tabela TOAST. A tabela principal armazena o ID do valor TOAST e o ID da relação na página para fazer referência à linha correspondente na tabela TOAST. Isso permite que o PostgreSQL localize e gerencie com eficiência esses fragmentos do TOAST. No entanto, à medida que a tabela TOAST cresce, o sistema corre o risco de esgotar os OIDs disponíveis, causando a degradação da performance e um possível tempo de inatividade devido ao esgotamento do OID. ### Identificadores de objetos no TOAST Identificador de Objeto (OID) é um identificador exclusivo em todo o sistema usado pelo PostgreSQL para fazer referência a objetos de banco de dados, como tabelas, índices e funções. Esses identificadores desempenham um papel essencial nas operações internas do PostgreSQL, permitindo que o banco de dados localize e gerencie objetos com eficiência. Para tabelas com conjuntos de dados elegíveis para toasting, o PostgreSQL atribui OIDs para identificar exclusivamente cada fragmento de dado excedente armazenado na tabela TOAST associada. O sistema associa cada fragmento a um `chunk_id`, o que ajuda o PostgreSQL a organizar e localizar esses fragmentos de forma eficiente na tabela TOAST. ## Identificar problemas de performance O gerenciamento de OID do PostgreSQL depende de um contador global de 32 bits para que ele funcione depois de gerar 4 bilhões de valores exclusivos. Embora o cluster de banco de dados compartilhe esse contador, a alocação de OID envolve duas etapas durante as operações TOAST: + **Contador global para alocação**: o contador global atribui um novo OID em todo o cluster. + **Pesquisa local de conflitos**: a tabela TOAST garante que o novo OID não entre em conflito com os OIDs existentes já usados nessa tabela específica. A degradação da performance pode ocorrer quando: + A tabela TOAST tem alta fragmentação ou uso denso de OID, causando atrasos na atribuição do OID. + O sistema frequentemente aloca e reutiliza OIDs em ambientes com alta rotatividade de dados ou tabelas amplas que usam o TOAST extensivamente. Para acessar mais informações, consulte a [documentação de alocação de OID e limites de tamanho de tabelas TOAST do PostgreSQL](https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit): Um contador global gera os OIDs e envolve cada 4 bilhões de valores, para que, periodicamente, o sistema gere novamente um valor já usado. O PostgreSQL detecta isso e tenta novamente com o próximo OID. Um INSERT lento poderá ocorrer se houver uma série muito longa de valores de OID usados sem lacunas na tabela TOAST. Esses desafios se tornam mais pronunciados à medida que o espaço do OID é preenchido, causando inserções e atualizações mais lentas. ### Identificar o problema + Declarações `INSERT` simples demoram muito mais do que o normal de maneira inconsistente e aleatória. + Atrasos ocorrem somente para declarações `INSERT` e `UPDATE` envolvendo operações TOAST. + As seguintes entradas de log aparecem nos logs do PostgreSQL quando o sistema tem dificuldade para encontrar OIDs disponíveis nas tabelas TOAST: ``` LOG: still searching for an unused OID in relation "pg_toast_20815" DETAIL: OID candidates have been checked 1000000 times, but no unused OID has been found yet. ``` + O Insights de Performance indica um alto número médio de sessões ativas (AAS) associadas a eventos de espera `LWLock:buffer_io` e `LWLock:OidGenLock`. Você pode executar a seguinte consulta SQL para identificar transações INSERT de longa duração com eventos de espera: ``` SELECT datname AS database_name, usename AS database_user, pid, now() - pg_stat_activity.xact_start AS transaction_duration, concat(wait_event_type, ':', wait_event) AS wait_event, substr(query, 1, 30) AS TRANSACTION, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.xact_start) > INTERVAL '60 seconds' AND state IN ('active', 'idle in transaction', 'idle in transaction (aborted)', 'fastpath function call', 'disabled') AND pid <> pg_backend_pid() AND lower(query) LIKE '%insert%' ORDER BY transaction_duration DESC; ``` Exemplos de resultado de consulta exibindo operações INSERT com tempos de espera estendidos: ``` database_name | database_user | pid | transaction_duration | wait_event | transaction | state ---------------+-----------------+-------+----------------------+---------------------+--------------------------------+-------- postgres | db_admin_user| 70965 | 00:10:19.484061 | LWLock:buffer_io | INSERT INTO "products" (......... | active postgres | db_admin_user| 69878 | 00:06:14.976037 | LWLock:buffer_io | INSERT INTO "products" (......... | active postgres | db_admin_user| 68937 | 00:05:13.942847 | : | INSERT INTO "products" (......... | active ``` ### Isolar o problema + **Testar a inserção pequena**: insira um registro menor que o limite `toast_tuple_target`. Lembre-se de que a compactação é aplicada antes do armazenamento TOAST. Se isso funcionar sem problemas de performance, o problema está relacionado às operações TOAST. + **Testar nova tabela**: crie uma tabela com a mesma estrutura e insira um registro maior que `toast_tuple_target`. Se isso funcionar sem problemas, o problema estará localizado na alocação de OID da tabela original. ## Recomendações As abordagens a seguir podem ajudar a resolver problemas de contenção do TOAST OID. + **Limpeza e arquivamento de dados**: analise e exclua quaisquer dados obsoletos ou desnecessários para liberar OIDs para uso futuro ou arquivar os dados. Considere as seguintes limitações: + Escalabilidade limitada, pois a limpeza futura nem sempre é possível. + Possível operação VACUUM de longa duração para remover as tuplas inativas resultantes. + **Gravar em uma nova tabela**: crie uma tabela para futuras inserções e use uma visualização `UNION ALL` para combinar dados antigos e novos para consultas. Essa visualização apresenta os dados combinados das tabelas antigas e novas, permitindo que as consultas os acessem como uma única tabela. Considere as seguintes limitações: + As atualizações na tabela antiga ainda podem causar o esgotamento de OIDs. + **Partição ou fragmento**: particione a tabela ou fragmente os dados para ter melhor escalabilidade e performance. Considere as seguintes limitações: + Maior complexidade na lógica de consulta e na manutenção, possível necessidade de alterações na aplicação para lidar corretamente com dados particionados. ## Monitoramento ### Usar tabelas do sistema Você pode usar as tabelas do sistema do PostgreSQL para monitorar o crescimento do uso de OIDs. **Atenção** Dependendo do número de OIDs na tabela TOAST, pode levar algum tempo para ser concluído. Recomendamos que você agende o monitoramento fora do horário comercial para minimizar o impacto. O bloco anônimo a seguir conta o número de OIDs distintos usados em cada tabela TOAST e exibe as informações da tabela principal: ``` DO $$ DECLARE r record; o bigint; parent_table text; parent_schema text; BEGIN SET LOCAL client_min_messages TO notice; FOR r IN SELECT c.oid, c.oid::regclass AS toast_table FROM pg_class c WHERE c.relkind = 't' AND c.relowner != 10 LOOP -- Fetch the number of distinct used OIDs (chunk IDs) from the TOAST table EXECUTE 'SELECT COUNT(DISTINCT chunk_id) FROM ' || r.toast_table INTO o; -- If there are used OIDs, find the associated parent table and its schema IF o <> 0 THEN SELECT n.nspname, c.relname INTO parent_schema, parent_table FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.reltoastrelid = r.oid; -- Raise a concise NOTICE message RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Number of used OIDs: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999'); END IF; END LOOP; END $$; ``` Exemplo de saída exibindo estatísticas de uso de OIDs pela tabela TOAST: ``` NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Number of used OIDs: 45,623,317 NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Number of used OIDs: 10,000 NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Number of used OIDs: 1,000,000 DO ``` O bloco anônimo a seguir recupera o OID máximo atribuído para cada tabela TOAST não vazia: ``` DO $$ DECLARE r record; o bigint; parent_table text; parent_schema text; BEGIN SET LOCAL client_min_messages TO notice; FOR r IN SELECT c.oid, c.oid::regclass AS toast_table FROM pg_class c WHERE c.relkind = 't' AND c.relowner != 10 LOOP -- Fetch the max(chunk_id) from the TOAST table EXECUTE 'SELECT max(chunk_id) FROM ' || r.toast_table INTO o; -- If there's at least one TOASTed chunk, find the associated parent table and its schema IF o IS NOT NULL THEN SELECT n.nspname, c.relname INTO parent_schema, parent_table FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.reltoastrelid = r.oid; -- Raise a concise NOTICE message RAISE NOTICE 'Parent schema: % | Parent table: % | Toast table: % | Max chunk_id: %', parent_schema, parent_table, r.toast_table, TO_CHAR(o, 'FM9,999,999,999,999'); END IF; END LOOP; END $$; ``` Exemplo de saída exibindo o máximo de IDs de blocos para tabelas TOAST: ``` NOTICE: Parent schema: public | Parent table: my_table | Toast table: pg_toast.pg_toast_16559 | Max chunk_id: 45,639,907 NOTICE: Parent schema: public | Parent table: my_table1 | Toast table: pg_toast.pg_toast_45639925 | Max chunk_id: 45,649,929 NOTICE: Parent schema: public | Parent table: my_table2 | Toast table: pg_toast.pg_toast_45649931 | Max chunk_id: 46,649,935 DO ``` ### Usar o Performance Insights Os eventos de espera `LWLock:buffer_io` e `LWLock:OidGenLock` aparecem no Insights de Performance durante operações que exigem a atribuição de novos identificadores de objetos (OIDs). As sessões ativas de alta média (AAS) para esses eventos geralmente apontam para contenção durante a atribuição de OIDs e o gerenciamento de recursos. Isso é particularmente comum em ambientes com alta rotatividade de dados, uso extensivo de grandes volumes de dados ou criação frequente de objetos. #### LWLock:buffer\$1io `LWLock:buffer_io` é um evento de espera que ocorre quando uma sessão do PostgreSQL aguarda a conclusão das operações de E/S em um buffer compartilhado. Isso geralmente acontece quando o banco de dados lê dados do disco para a memória ou grava páginas modificadas da memória para o disco. O evento de espera `BufferIO` garante a consistência ao impedir que vários processos acessem ou modifiquem o mesmo buffer enquanto as operações de E/S estão em andamento. Altas ocorrências desse evento de espera podem indicar gargalos no disco ou atividade excessiva de E/S na workload do banco de dados. Durante as operações TOAST: + O PostgreSQL aloca OIDs para objetos grandes e garante sua exclusividade examinando o índice da tabela TOAST. + Índices TOAST grandes podem exigir o acesso a várias páginas para verificar a exclusividade do OID. Isso gera aumento de E/S de disco, principalmente quando o grupo de buffers não consegue armazenar em cache todas as páginas necessárias. O tamanho do índice afeta diretamente o número de páginas de buffer que precisam ser acessadas durante essas operações. Mesmo que o índice não esteja inchado, seu tamanho pode aumentar a E/S do buffer, principalmente em ambientes de alta simultaneidade ou alta rotatividade. Para acessar mais informações, consulte o [Guia de solução de problemas com o evento de espera LWLock:BufferIO](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.lwlockbufferio.html). #### LWLock:OidGenLock `OidGenLock` é um evento de espera que ocorre quando uma sessão do PostgreSQL está aguardando para alocar um novo identificador de objeto (OID). Esse bloqueio garante que os OIDs sejam gerados sequencialmente e com segurança, permitindo que somente um processo gere OIDs por vez. Durante as operações TOAST: + **Alocação de OID para blocos na tabela TOAST**: o PostgreSQL atribui OIDs a blocos nas tabelas TOAST ao gerenciar grandes registros de dados. Cada OID deve ser exclusivo para evitar conflitos no catálogo do sistema. + **Alta simultaneidade**: como o acesso ao gerador de OIDs é sequencial, quando várias sessões estão criando simultaneamente objetos que exigem OIDs, pode ocorrer contenção para `OidGenLock`. Isso aumenta a probabilidade de sessões esperando a conclusão da alocação de OIDs. + **Dependência do acesso ao catálogo do sistema**: a alocação de OIDs requer atualizações nas tabelas compartilhadas do catálogo do sistema, como `pg_class` e `pg_type`. Se essas tabelas apresentarem muita atividade (devido às operações frequentes de DDL), isso poderá aumentar a contenção de bloqueio para `OidGenLock`. + **Alta demanda de alocação de OIDs**: workloads intensas envolvendo TOAST com grandes registros de dados exigem alocação constante de OIDs, aumentando a contenção. Fatores adicionais que aumentam a contenção de OIDs: + **Criação frequente de objetos**: workloads trabalho que frequentemente criam e descartam objetos, como tabelas temporárias, amplificam a contenção no contador global de OIDs. + **Bloqueio global do contador**: o contador global de OIDs é acessado em série para garantir a exclusividade, criando um único ponto de contenção em ambientes de alta simultaneidade.