Tema: postgresql
-
Gerenciamento de extensões do PostgreSQL: instalar, versionar, atualizar e exportar
Uma extensão agrupa objetos SQL e, muitas vezes, uma biblioteca compartilhada sob um único nome, com um arquivo de controle e scripts versionados. CREATE EXTENSION a instala por banco de dados, ALTER EXTENSION UPDATE aplica os scripts de atualização do autor e o pg_dump emite apenas a linha CREATE EXTENSION. Mantenha alinhados os arquivos instalados, a versão no catálogo e a biblioteca carregada, sobretudo em atualizações de pacote e no pg_upgrade.
-
Como projetar uma tabela de séries temporais somente de inserção no PostgreSQL
Armazene medições em uma tabela particionada por intervalo de tempo, com timestamptz, chave composta de série e tempo, índices adequados ao padrão de consulta (B-tree por série, BRIN para varreduras de tempo na tabela inteira) e retenção feita por detach e drop de partições, em vez de DELETE; as escolhas decorrem de as linhas chegarem em ordem de tempo e saírem em fatias inteiras de tempo.
-
Armazenar dados derivados no PostgreSQL: colunas geradas ou views materializadas
Uma coluna gerada deriva um valor por linha apenas a partir dessa mesma linha, podendo ser virtual (calculada na leitura) ou armazenada (calculada na escrita), e aceita só expressões imutáveis; uma view materializada guarda o resultado de qualquer consulta e só está tão atualizada quanto o último REFRESH. Use colunas geradas armazenadas para normalização por linha que deva ser indexada, views materializadas para agregações caras que podem ficar defasadas e uma tabela de resumo mantida quando nenhuma das duas serve.
-
VACUUM, autovacuum e bloat de tabelas
O MVCC do PostgreSQL deixa versões mortas de linhas após updates e deletes; o VACUUM as recupera, mantém as estatísticas e protege contra o wraparound de IDs de transação. O autovacuum deve permanecer ativo e ser ajustado para tabelas com muito movimento.
-
Bancos de dados efêmeros em contêineres para testes de integração
Suba o motor de banco de dados real em um contêiner descartável a cada execução de testes, mantenha os dados em memória, aplique as migrações uma só vez em um banco-modelo e copie-o para cada arquivo de teste. Assim, os testes exercitam o planejador, as constraints e o comportamento de isolamento reais, em vez de um substituto em memória.
-
Upsert, row limits and identifier quoting across PostgreSQL, MySQL, SQLite and SQL Server
PostgreSQL's ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, SQLite's PostgreSQL-style ON CONFLICT and SQL Server's MERGE are three families of upsert syntax, LIMIT/OFFSET has at least three shapes, and a quoted identifier means double quotes, backticks or square brackets depending on the engine.
-
Níveis de isolamento de transação na prática
Read committed, repeatable read e serializable trocam concorrência por consistência; saber quais anomalias cada nível permite define quando adicionar locks explícitos ou tentativas repetidas (retries).
-
Alterações de banco de dados sem indisponibilidade: Expand e Contract
Como alterar um esquema em três etapas que podem ser entregues separadamente: expandir (criar a nova coluna ou tabela e manter a antiga), migrar (gravar nos dois lugares e preencher os dados antigos em lotes pequenos) e contrair (remover o antigo quando todo o código já usa o novo). Para evitar bloqueios longos, nenhuma tabela é reescrita em um único comando e o lock_timeout limita qualquer espera.
-
Common table expressions e consultas recursivas com WITH
WITH dá um nome a uma subconsulta para o resto da instrução; WITH RECURSIVE avalia um termo não recursivo e repete o termo recursivo até que não surjam novas linhas, percorrendo árvores e grafos de qualquer profundidade numa só consulta. CTEs de uso único e sem efeitos colaterais são incorporadas na consulta externa, a menos que se escreva MATERIALIZED; as cláusulas SEARCH e CYCLE tratam da ordenação e dos ciclos.
-
Colunas JSONB: para que servem e quando é melhor usar uma coluna comum
O jsonb guarda JSON já interpretado em formato binário, indexável com GIN e consultável com operadores de contenção e de caminho; serve para atributos esparsos, definidos externamente ou realmente variáveis. Dados com estrutura fixa, que exigem restrições, chaves estrangeiras ou atualizações por campo, e documentos grandes alterados com frequência pertencem a colunas comuns.
-
Pool de conexões com o banco de dados e seus limites
Cada conexão PostgreSQL é um processo com custo de memória; a aplicação deve manter um pool pequeno, dimensionado pela concorrência real, definir timeouts de aquisição e nunca deixar os handlers de requisição abrirem conexões avulsas.
-
Medição de reservas com SKIP LOCKED no PostgreSQL usando quatro consumidores de fila concorrentes
Quatro transações concorrentes reservaram, cada uma, 25 jobs sintéticos no PostgreSQL 16.15. Os 100 IDs retornados eram únicos e nenhum job ficou sem reserva; isso verifica uma única fase de reserva limitada, não o processamento exactly-once nem a substituição de um broker.
-
Estatísticas do planejador no PostgreSQL: statistics target, colunas correlacionadas e estimativas erradas
O planejador estima o número de linhas a partir de estatísticas por coluna amostradas pelo ANALYZE (valores mais comuns, histogramas, contagem de valores distintos) e assume que as colunas são independentes; estimativas erradas vêm de estatísticas desatualizadas, colunas assimétricas com poucas entradas, colunas correlacionadas e expressões. Aumente o statistics target em colunas específicas, crie estatísticas estendidas para colunas correlacionadas ou expressões, execute o ANALYZE de novo e compare as linhas estimadas com as reais.
-
NULL em SQL: lógica de três valores e suas armadilhas
NULL significa desconhecido, por isso toda comparação com NULL não resulta em verdadeiro nem falso, mas em desconhecido. O WHERE descarta linhas desconhecidas, NOT IN com um NULL não encontra nada, agregações ignoram NULL e uma restrição unique admite vários NULL. Use IS NULL, IS DISTINCT FROM e COALESCE de forma deliberada.
-
Migrações de esquema com lock_timeout curto e nova tentativa automática causam menos incidentes no deploy do que migrações sem esse mecanismo
Hipótese: como a maioria das formas de ALTER TABLE exige um lock ACCESS EXCLUSIVE que fica na fila atrás de qualquer transação longa e bloqueia todas as consultas seguintes, migrações executadas com lock_timeout de poucos segundos e um laço de nova tentativa limitado geram menos interrupções, e mais curtas, durante o deploy do que as mesmas migrações com a espera ilimitada padrão. O custo é que algumas migrações precisarão ser reexecutadas manualmente.
-
Políticas de segurança em nível de linha reduzem vazamentos entre tenants em comparação com a filtragem na aplicação
Hipótese: serviços multi-tenant que isolam tenants com políticas de row-level security do PostgreSQL (uma configuração de tenant por conexão, verificada pelo banco) têm menos bugs de exposição entre tenants do que serviços que adicionam um predicado tenant_id a cada consulta, pois uma consulta sem esse predicado continua vendo apenas as linhas do tenant atual, e uma requisição que não define o tenant não recebe nada ou recebe um erro, em vez das linhas de todos os tenants.
-
Savepoints e o estado de transação abortada no PostgreSQL
Depois de qualquer erro dentro de um bloco de transação, o PostgreSQL rejeita todos os comandos seguintes até que o bloco seja revertido; um savepoint criado antes de uma instrução arriscada permite usar ROLLBACK TO SAVEPOINT para descartar só essa parte e continuar. Use savepoints de forma deliberada e moderada, e faça os tratadores de erro reverterem a transação em vez de tentarem de novo na mesma conexão.
-
Replicação lógica no PostgreSQL: publicações, assinaturas e diferenças em relação à replicação por streaming
A replicação por streaming (física) envia o write-ahead log para um standby idêntico byte a byte, da mesma versão principal e arquitetura. A replicação lógica publica alterações de linhas de tabelas selecionadas para um assinante que pode usar outra versão principal e ter dados próprios. Ela exige uma replica identity, não transporta DDL nem valores de sequências, e seu slot retém WAL no publicador enquanto o assinante estiver atrasado.
-
Réplicas de leitura e atraso de replicação: como se manifestam as leituras desatualizadas e como limitá-las
Uma réplica em streaming aplica o log do primário com algum atraso, e por isso uma leitura logo após uma escrita pode não enxergar essa escrita. Direcione as leituras conforme a defasagem que cada chamador tolera, use modos de replicação síncrona apenas onde a latência for aceitável e lembre que a reaplicação do log pode cancelar consultas longas na réplica.
-
Sessões longas e idle in transaction no PostgreSQL: o que bloqueiam e como limitá-las
Uma transação aberta mantém seus locks e fixa o horizonte de xmin, de modo que o VACUUM não remove linhas excluídas depois do início dela e o DDL fica na fila atrás dela; o pior caso é uma sessão idle in transaction porque o cliente nunca deu commit. Localize-as em pg_stat_activity por xact_start e state, e limite-as por role com idle_in_transaction_session_timeout, transaction_timeout e statement_timeout.
Legível por máquina: JSON