Colunas geradas do PostgreSQL para filtros administrativos mais rápidos

Por que telas administrativas ficam lentas e bagunçadas rápido

Telas administrativas geralmente começam simples: uma tabela, alguns filtros, talvez uma ordenação por “mais recentes”. Aí vem o trabalho real. Suporte quer busca que encontre clientes por nome, email e telefone. Vendas quer ordenação por “última atividade”. Financeiro quer “saldo vencido”. Cada pedido adiciona condições, joins e cálculos extras.

A maioria das listas administrativas fica lenta pelo mesmo motivo: cada clique muda a consulta. Filtrar e ordenar pode forçar o banco a escanear muitas linhas, especialmente quando a consulta precisa calcular um valor para cada linha antes de decidir o que bate.

Um ponto comum de virada é quando WHERE e ORDER BY ficam cheios de expressões. Em vez de filtrar por uma coluna simples, você filtra por lower(email), date_trunc('day', last_seen_at) ou um CASE que mapeia vários status para um único “bucket”. Essas expressões não são só mais lentas. Elas tornam o SQL mais difícil de ler, de indexar e fáceis de errar.

SQL administrativo bagunçado costuma vir de alguns padrões que se repetem:

• Um campo de “busca” que verifica vários campos com regras diferentes
• Ordenação por um valor derivado (nome completo, score de prioridade, “último evento relevante”)
• Regras de negócio copiadas entre telas (ativo vs inativo, pago vs vencido)
• Pequenos ajustes auxiliares (trim, lower, coalesce) espalhados por todo lado
• O mesmo valor calculado usado na lista, nos filtros e na ordenação

Times costumam tentar esconder isso na camada da aplicação: builders de consulta dinâmicos, joins condicionais ou pré-cálculo em código. Isso pode funcionar, mas divide a lógica entre UI e banco, o que torna debugar consultas lentas bem doloroso.

O objetivo é simples: consultas rápidas que continuem legíveis. Quando um valor calculado aparece repetidas vezes nas telas administrativas, colunas geradas do PostgreSQL podem manter a regra em um lugar só e ainda deixar o banco otimizar isso.

Colunas geradas em linguagem simples

Uma coluna gerada é uma coluna normal na tabela cujo valor é calculado a partir de outras colunas. Você não escreve o valor manualmente. O PostgreSQL preenche usando uma expressão que você define.

No PostgreSQL, colunas geradas são armazenadas. O banco calcula o valor na inserção ou atualização de uma linha e então salva no disco como qualquer outra coluna. Isso é geralmente o que você quer para telas administrativas: leituras rápidas e a possibilidade de indexar o valor calculado.

Isso difere de fazer o mesmo cálculo dentro de cada consulta. Se você fica escrevendo WHERE lower(email) = lower($1) ou ordenando por last_name || ', ' || first_name, paga o custo repetidas vezes e seu SQL fica ruidoso. Uma coluna gerada desloca esse cálculo repetido para a definição da tabela. Suas consultas ficam mais simples e o resultado é consistente em todo lugar.

Quando os dados de origem mudam, o PostgreSQL atualiza automaticamente o valor gerado para aquela linha. Sua aplicação não precisa lembrar de mantê-lo em sincronia.

Um modelo mental útil:

• Defina a fórmula uma vez.
• PostgreSQL calcula na escrita.
• Consultas leem como uma coluna normal.
• Por ser armazenada, você pode indexá-la.

Se depois você mudar a fórmula, será necessária uma alteração de esquema. Planeje como qualquer migração, porque linhas existentes serão atualizadas para combinar com a nova expressão.

Bons usos para campos computados em filtros e ordenações

Colunas geradas brilham quando o valor é sempre derivado de outras colunas e você filtra ou ordena por ele com frequência. Elas são menos úteis para relatórios pontuais.

Campos de busca amigáveis que as pessoas realmente usam

Busca administrativa raramente é “pura”. Usuários esperam que a caixa lide com texto bagunçado, caixa inconsistênte e espaços extras. Se você armazenar uma “chave de busca” gerada já normalizada, seu WHERE fica legível e se comporta igual entre telas.

Bons candidatos incluem um nome completo combinado, texto em lowercase e trimado para busca case-insensitive, uma versão limpa que colapsa espaços, ou um rótulo de status derivado de múltiplos campos.

Exemplo: em vez de repetir lower(trim(first_name || ' ' || last_name)) em cada consulta, gere full_name_key uma vez e filtre por ela.

Chaves de ordenação que batem com como humanos ordenam

Ordenação é onde campos computados costumam compensar mais rápido, porque ordenar pode forçar o PostgreSQL a avaliar expressões para muitas linhas.

Chaves comuns de ordenação incluem um rank numérico (plano mapeado para 1, 2, 3), um único timestamp de “última atividade” (como o máximo de dois timestamps) ou um código preenchido que ordena corretamente como texto.

Quando a chave de ordenação é uma coluna simples e indexada, ORDER BY fica muito mais barato.

Flags derivadas para filtros rápidos

Usuários administrativos adoram checkboxes como “Vencido” ou “Alto valor”. Isso funciona bem como colunas geradas quando a lógica é estável e baseada apenas nos dados da linha.

Por exemplo, se uma lista de clientes precisa de “Tem mensagens não lidas” e “Está vencido”, um booleano gerado has_unread (a partir de unread_count > 0) e is_overdue (a partir de due_date < now() e paid_at is null) permitem que filtros da UI sejam condições simples.

Escolhendo entre colunas geradas, índices e outras opções

Telas administrativas precisam de três coisas: filtros rápidos, ordenação rápida e SQL que você consiga ler meses depois. A decisão real é onde a computação deve ficar: na tabela, no índice, numa view ou no código da aplicação.

Colunas geradas são uma boa opção quando você quer que o valor se comporte como uma coluna real: fácil referência, visível em selects e difícil de esquecer ao adicionar novos filtros. Elas também se combinam naturalmente com índices normais.

Índices de expressão podem ser mais rápidos de adicionar porque você não altera a tabela. Se você só se importa com velocidade e não liga para SQL menos legível, um índice de expressão costuma bastar. A desvantagem é a legibilidade, e você depende do planner casar exatamente sua expressão.

Views ajudam quando você quer uma “forma” de dados compartilhada, especialmente se a lista administrativa junta muitas tabelas. Mas views complexas podem esconder trabalho caro e adicionar um segundo lugar para depurar.

Triggers podem manter uma coluna normal em sincronia, mas adicionam partes móveis. Podem tornar atualizações em massa mais lentas e fáceis de esquecer em troubleshooting.

Às vezes a melhor opção é uma coluna simples preenchida pela aplicação. Se usuários podem editá-la, ou se a fórmula muda frequentemente por decisões de negócio (e não apenas por dados da linha), manter explícito é mais claro.

Uma forma rápida de escolher:

• Quer queries legíveis e uma fórmula estável baseada só em dados da linha? Use coluna gerada.
• Quer velocidade para um filtro específico e não liga para SQL ruidoso? Use índice de expressão.
• Precisa de uma forma com joins reutilizada em muitos lugares? Considere uma view.
• Precisa de lógica entre tabelas ou efeitos colaterais? Prefira lógica na aplicação primeiro, triggers por último.

Passo a passo: adicionar uma coluna gerada e usá-la em uma consulta

Comece com uma lista administrativa lenta que você sente na UI. Anote os filtros e a ordenação que a tela usa mais. Melhore essa única consulta primeiro.

Escolha um campo calculado que remova trabalho repetido e nomeie-o claramente em snake_case para que outros adivinhem o que guarda sem reler a expressão.

1) Adicionar a coluna gerada (STORED)

ALTER TABLE customers
ADD COLUMN full_name_key text
GENERATED ALWAYS AS (
  lower(concat_ws(' ', last_name, first_name))
) STORED;

Valide em linhas reais antes de adicionar índices:

SELECT id, first_name, last_name, full_name_key
FROM customers
ORDER BY id DESC
LIMIT 5;

Se o resultado estiver errado, corrija a expressão agora. STORED significa que o PostgreSQL manterá atualizado em cada insert e update.

2) Adicione o índice que corresponde à sua tela administrativa

Se sua tela filtra por status e ordena por nome, indexe esse padrão:

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) Atualize a consulta administrativa para usar a nova coluna

Antes, você talvez tivesse um ORDER BY bagunçado. Depois, fica óbvio:

SELECT id, status, first_name, last_name
FROM customers
WHERE status = 'active'
ORDER BY full_name_key ASC
LIMIT 50 OFFSET 0;

Use colunas geradas nas partes que as pessoas filtram e ordenam todo dia, não em telas raras.

Padrões de indexação que correspondem a telas administrativas reais

Telas administrativas repetem alguns comportamentos: filtrar por um pequeno conjunto de campos, ordenar por uma coluna e paginar. A melhor configuração raramente é “indexe tudo”. É “indexe o shape exato das consultas mais comuns”.

Uma regra prática: coloque as colunas de filtro mais comuns primeiro e a coluna de ordenação mais comum por último. Se você é multi-tenant, workspace_id (ou similar) frequentemente vem na frente: (workspace_id, status, created_at).

Busca por texto é outro problema. Muitas caixas de busca viram ILIKE '%term%', que é difícil de acelerar com índices btree básicos. Um padrão útil é buscar em uma coluna auxiliar normalizada em vez do texto bruto (minúsculas, trim, talvez concatenado). Se a UI puder usar busca por prefixo (term%), um índice btree nessa coluna normalizada pode ajudar. Se precisar de contains (%term%), considere ajustar a UI para tabelas grandes (por exemplo, “email começa com”) ou limitar a busca a um subconjunto menor.

Também verifique seletividade antes de criar índices. Se 95% das linhas compartilham o mesmo valor (como status = 'active'), indexar apenas essa coluna não ajudará muito. Combine com uma coluna mais seletiva ou use um índice parcial para o caso minoritário.

Exemplo realista: uma lista de clientes que permanece rápida

Imagine uma página típica de clientes: uma caixa de busca, alguns filtros (inativo, faixa de saldo) e uma coluna “Última visualização” ordenável. Com o tempo vira SQL difícil de ler: LOWER(), TRIM(), COALESCE(), cálculos de data e blocos CASE repetidos entre telas.

Uma forma de manter rápido e legível é empurrar essas expressões repetidas para colunas geradas.

Tabela e colunas geradas

Suponha uma tabela customers com name, email, last_seen e balance. Adicione três campos calculados:

• search_key: um texto normalizado para buscas simples
• is_inactive: um booleano para filtrar sem repetir lógica de data
• balance_bucket: um rótulo para segmentação rápida

ALTER TABLE customers
  ADD COLUMN search_key text
    GENERATED ALWAYS AS (
      lower(trim(coalesce(name, ''))) || ' ' || lower(trim(coalesce(email, '')))
    ) STORED,
  ADD COLUMN is_inactive boolean
    GENERATED ALWAYS AS (
      last_seen IS NULL OR last_seen < (now() - interval '90 days')
    ) STORED,
  ADD COLUMN balance_bucket text
    GENERATED ALWAYS AS (
      CASE
        WHEN balance < 0 THEN 'negative'
        WHEN balance < 100 THEN '0-99'
        WHEN balance < 500 THEN '100-499'
        ELSE '500+'
      END
    ) STORED;

Agora a consulta administrativa lê como a UI.

Filtro legível + ordenação

“Clientes inativos, mais recente primeiro” vira:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE is_inactive = true
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

E uma busca básica vira:

SELECT id, name, email, last_seen, balance
FROM customers
WHERE search_key LIKE '%' || lower(trim($1)) || '%'
ORDER BY last_seen DESC NULLS LAST
LIMIT 50;

O ganho real é consistência. Os mesmos campos alimentam várias telas sem reescrever lógica:

• A caixa de busca usa search_key
• A aba “Clientes inativos” usa is_inactive
• Os chips de filtro de saldo usam balance_bucket

Erros comuns e armadilhas

Colunas geradas podem parecer uma vitória simples: coloque a matemática na tabela e mantenha consultas limpas. Elas só ajudam quando combinam com como a tela filtra e ordena, e quando você adiciona o índice certo.

Erros mais comuns:

• Assumir que acelera sem indexação. Um valor calculado ainda precisa de índice para filtragem ou ordenação rápidas em escala.
• Juntar lógica demais em um campo. Se uma coluna gerada vira um mini programa, as pessoas deixam de confiar. Mantenha-a curta e nomeie claramente.
• Usar funções não imutáveis. PostgreSQL exige que a expressão para coluna gerada armazenada seja imutável. Coisas como now() e random() quebram expectativas e muitas vezes não são permitidas.
• Ignorar custo de escrita. Inserts e updates precisam manter o valor calculado. Leituras mais rápidas não valem se imports e integrações ficarem lentos demais.
• Criar quase-duplicatas. Padronize um ou dois padrões (como uma chave normalizada) em vez de acumular cinco colunas semelhantes.

Se sua lista faz buscas por contains (ILIKE '%ann%'), uma coluna gerada sozinha não resolverá. Talvez seja preciso outra abordagem de busca. Mas para as consultas do dia a dia de “filtrar e ordenar”, colunas geradas mais o índice certo normalmente tornam o desempenho muito mais previsível.

Checklist rápido antes de enviar

Antes de aplicar mudanças em uma lista administrativa, verifique se o valor calculado, a consulta e o índice batem.

• A fórmula é estável e fácil de explicar em uma frase.
• Sua consulta realmente usa a coluna gerada em WHERE e/ou ORDER BY.
• O índice corresponde ao uso real, não a um teste pontual.
• Você comparou resultados com a lógica antiga em casos extremos (NULLs, strings vazias, espaços estranhos, caixa mista).
• Testou o desempenho de escrita se a tabela é movimentada (imports, atualizações em background, integrações).

Próximos passos: aplique isso nas suas telas administrativas

Escolha um ponto de partida pequeno e de alto impacto: as 2–3 telas que as pessoas abrem o dia todo (orders, customers, tickets). Anote o que parece lento (filtro por data, ordenação por “última atividade”, busca por nome combinado, filtro por um rótulo de status). Depois padronize um conjunto curto de campos calculados reutilizáveis.

Um plano de rollout fácil de medir e reverter:

• Adicione a(s) coluna(s) gerada(s) com nomes claros.
• Rode lado a lado velho e novo brevemente se estiver substituindo lógica existente.
• Adicione o índice que corresponde ao filtro/ordenção principal.
• Troque a consulta da tela para usar a nova coluna.
• Meça antes e depois (tempo da consulta e linhas escaneadas) e então remova o workaround antigo.

Se você constrói ferramentas internas administrativas no AppMaster (appmaster.io), esses campos calculados se encaixam bem num modelo de dados compartilhado: o banco carrega a regra, e suas UIs podem apontar para um nome de campo direto em vez de repetir expressões entre telas.