Columnas generadas de PostgreSQL para acelerar filtros en el admin

Por qué las pantallas de administración se vuelven lentas y desordenadas rápido

Las pantallas de administración suelen empezar sencillas: una tabla, un par de filtros, quizá un orden por “más recientes primero”. Luego llega el trabajo real. Soporte quiere buscar clientes por nombre, correo y teléfono. Ventas quiere ordenar por “última actividad”. Finanzas quiere “saldo vencido”. Cada petición añade condiciones, joins y cálculos extra.

La mayoría de las listas de administración se vuelven lentas por la misma razón: cada clic cambia la consulta. Filtrar y ordenar puede obligar a la base de datos a escanear muchas filas, sobre todo cuando la consulta tiene que calcular un valor por fila antes de decidir si coincide.

Un punto de inflexión común es cuando WHERE y ORDER BY se llenan de expresiones. En lugar de filtrar por una columna simple, filtras por lower(email), date_trunc('day', last_seen_at) o un CASE que agrupa varios estados en un “bucket”. Esas expresiones no solo son más lentas, también hacen el SQL más difícil de leer, más difícil de indexar y más propenso a errores.

El SQL desordenado para administración suele venir de algunos patrones repetidos:

• Un único campo de “búsqueda” que revisa varios campos con reglas distintas
• Ordenar por un valor derivado (nombre completo, puntuación de prioridad, “último evento significativo”)
• Reglas de negocio copiadas entre pantallas (activo vs inactivo, pagado vs vencido)
• Pequeños ajustes “auxiliares” (trim, lower, coalesce) esparcidos por todas partes
• El mismo valor calculado usado en la lista, los filtros y el orden

Los equipos a menudo intentan ocultarlo en la capa de la app: generadores de consultas dinámicas, joins condicionales o precomputar valores en código. Eso puede funcionar, pero divide la lógica entre la interfaz y la base de datos, lo que hace que depurar consultas lentas sea doloroso.

El objetivo es sencillo: consultas rápidas que sigan siendo legibles. Cuando un valor calculado aparece una y otra vez en pantallas de administración, las columnas generadas de PostgreSQL pueden mantener la regla en un solo lugar y permitir que la base de datos la optimice.

Columnas generadas en palabras simples

Una columna generada es una columna normal de la tabla cuyo valor se calcula a partir de otras columnas. No escribes el valor tú mismo. PostgreSQL lo rellena usando una expresión que defines.

En PostgreSQL, las columnas generadas son almacenadas. PostgreSQL calcula el valor cuando se inserta o actualiza una fila y luego lo guarda en disco como cualquier otra columna. Eso suele ser lo que quieres para pantallas de administración: lecturas rápidas y la posibilidad de indexar el valor calculado.

Esto difiere de hacer el mismo cálculo dentro de cada consulta. Si sigues escribiendo WHERE lower(email) = lower($1) o ordenando por last_name || ', ' || first_name, pagas ese coste repetidamente y tu SQL se vuelve ruidoso. Una columna generada mueve ese cálculo repetido a la definición de la tabla. Tus consultas se vuelven más simples y el resultado es coherente en todas partes.

Cuando cambian los datos de origen, PostgreSQL actualiza el valor generado automáticamente para esa fila. Tu app no tiene que acordarse de sincronizarlo.

Un modelo mental útil:

• Define la fórmula una vez.
• PostgreSQL la calcula en escrituras.
• Las consultas la leen como una columna normal.
• Al estar almacenada, puedes indexarla.

Si más adelante cambias la fórmula, necesitarás un cambio de esquema. Planifícalo como cualquier migración, porque las filas existentes se actualizarán para coincidir con la nueva expresión.

Buenas aplicaciones para campos calculados en filtros y orden

Las columnas generadas brillan cuando el valor siempre se deriva de otras columnas y lo filtras u ordenas con frecuencia. Son menos útiles para informes puntuales.

Campos de búsqueda útiles que la gente realmente usa

La búsqueda en administración rara vez es “pura”. La gente espera que el cuadro soporte texto desordenado, mayúsculas inconsistentes y espacios extra. Si guardas una "clave de búsqueda" generada ya normalizada, tu cláusula WHERE sigue siendo legible y se comporta igual en todas las pantallas.

Buenos candidatos incluyen un nombre completo combinado, texto en minúsculas y recortado para búsquedas insensibles a mayúsculas, una versión limpiada que colapsa espacios en blanco, o una etiqueta de estado derivada de varios campos.

Ejemplo: en lugar de repetir lower(trim(first_name || ' ' || last_name)) en cada consulta, genera full_name_key una vez y filtra por ella.

Claves de orden que coinciden con cómo ordenan las personas

Ordenar es donde los campos calculados suelen dar retorno más rápido, porque ordenar puede obligar a PostgreSQL a evaluar expresiones para muchas filas.

Claves de orden comunes incluyen un rango numérico (plan mapeado a 1, 2, 3), una sola marca de tiempo de “última actividad” (como el máximo de dos timestamps) o un código rellenado que ordena correctamente como texto.

Cuando la clave de orden es una columna simple indexada, ORDER BY resulta mucho más barato.

Flags derivados para filtros rápidos

A los usuarios de administración les encantan casillas como “Vencido” o “Alto valor”. Estas funcionan bien como columnas generadas cuando la lógica es estable y basada solo en los datos de la fila.

Por ejemplo, si una lista de clientes necesita “Tiene mensajes sin leer” y “Está vencido”, una columna has_unread booleana (de unread_count > 0) y is_overdue (de due_date < now() y paid_at is null) permite que los filtros del UI se mapeen a condiciones simples.

Elegir entre columnas generadas, índices y otras opciones

Las pantallas de administración necesitan tres cosas: filtrado rápido, ordenación rápida y SQL que puedas seguir leyendo meses después. La decisión real es dónde debe vivir el cálculo: en la tabla, en un índice, en una vista o en el código de la app.

Columnas generadas encajan bien cuando quieres que el valor se comporte como una columna real: fácil de referenciar, visible en selects y difícil de olvidar al añadir nuevos filtros. También se llevan bien con índices normales.

Índices de expresión pueden ser más rápidos de añadir porque no cambias la definición de la tabla. Si te importa sobre todo la velocidad y no te molesta un SQL menos limpio, un índice de expresión suele ser suficiente. La desventaja es la legibilidad, y dependes de que el planner identifique exactamente tu expresión.

Vistas ayudan cuando quieres una “forma” compartida de datos, especialmente si tu lista de administración une muchas tablas. Pero las vistas complejas pueden ocultar trabajo costoso y añadir un segundo lugar que depurar.

Triggers pueden mantener una columna normal sincronizada, pero añaden piezas móviles. Pueden hacer que las actualizaciones masivas sean más lentas y son fáciles de pasar por alto al depurar.

A veces la mejor opción es una columna normal llenada por la app. Si los usuarios pueden editarla, o si la fórmula cambia con frecuencia por decisiones de negocio (no solo por datos de la fila), mantenerla explícita es más claro.

Una forma rápida de elegir:

• ¿Quieres consultas legibles y una fórmula estable basada solo en datos de fila? Usa una columna generada.
• ¿Quieres velocidad para un filtro específico y no te importa SQL ruidoso? Usa un índice de expresión.
• ¿Necesitas una forma unida tipo informe reutilizada en muchos sitios? Considera una vista.
• ¿Necesitas lógica entre tablas o efectos secundarios? Prefiere la lógica en la app primero, triggers como último recurso.

Paso a paso: añadir una columna generada y usarla en una consulta

Empieza con una consulta lenta de la lista de administración que notes en la UI. Anota los filtros y el orden que usa la pantalla con más frecuencia. Mejora esa consulta primero.

Elige un campo calculado que elimine trabajo repetido y nómbralo claramente en snake_case para que otros puedan adivinar qué contiene sin releer la expresión.

1) Añadir la columna generada (STORED)

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

Valida con filas reales antes de añadir índices:

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

Si la salida es incorrecta, corrige la expresión ahora. STORED significa que PostgreSQL la mantendrá actualizada en cada inserción y actualización.

2) Añadir el índice que coincide con tu pantalla de administración

Si tu pantalla filtra por estado y ordena por nombre, indexa ese patrón:

CREATE INDEX customers_status_full_name_key_idx
ON customers (status, full_name_key);

3) Actualizar la consulta de administración para usar la nueva columna

Antes, podrías tener un ORDER BY desordenado. Después, es evidente:

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

Usa columnas generadas para las partes que la gente filtra y ordena todos los días, no para pantallas raras.

Patrones de indexación que coinciden con pantallas reales

Las pantallas de administración repiten algunos comportamientos: filtrar por un puñado de campos, ordenar por una columna y paginar. La mejor configuración rara vez es “indexarlo todo”. Es “indexar la forma exacta de las consultas más comunes”.

Una regla práctica: pon las columnas de filtro más comunes primero y la columna de orden más común al final. Si eres multi-tenant, workspace_id (o similar) suele ir primero: (workspace_id, status, created_at).

La búsqueda de texto es otro problema. Muchos cuadros de búsqueda acaban como ILIKE '%term%', que es difícil de acelerar con índices btree básicos. Un patrón útil es buscar en una columna auxiliar normalizada en lugar de en el texto crudo (minúsculas, recortado, quizá concatenado). Si tu UI puede usar búsqueda por prefijo (term%), un índice btree en esa columna normalizada puede ayudar. Si necesita búsqueda por contenido (%term%), considera ajustar el comportamiento del UI para tablas grandes (por ejemplo, “email empieza con”), o limitar la búsqueda a un subconjunto más pequeño.

También verifica la selectividad antes de añadir índices. Si el 95% de las filas comparten el mismo valor (como status = 'active'), indexar esa columna sola no ayudará mucho. Combínala con una columna más selectiva o usa un índice parcial para el caso minoritario.

Ejemplo realista: una lista de clientes que se mantiene rápida

Imagina una página típica de administración de clientes: un cuadro de búsqueda, algunos filtros (inactivos, rango de saldo) y una columna “Última visita” ordenable. Con el tiempo se convierte en SQL difícil de leer: LOWER(), TRIM(), COALESCE(), matemáticas de fechas y bloques CASE repetidos en pantallas.

Una forma de mantenerlo rápido y legible es empujar esas expresiones repetidas a columnas generadas.

Tabla y columnas generadas

Supongamos una tabla customers con name, email, last_seen y balance. Añade tres campos calculados:

• search_key: un texto normalizado para búsquedas simples
• is_inactive: un booleano que puedes filtrar sin repetir la lógica de fechas
• balance_bucket: una etiqueta para segmentación 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;

Ahora la consulta de administración se lee como la UI.

Filtro legible + orden

“Clientes inactivos, primero por actividad más reciente” se convierte en:

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

Y una búsqueda básica se convierte en:

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;

La verdadera ganancia es la consistencia. Los mismos campos alimentan varias pantallas sin reescribir la lógica:

• El cuadro de búsqueda de clientes usa search_key
• La pestaña “Clientes inactivos” usa is_inactive
• Los chips de filtro de saldo usan balance_bucket

Errores comunes y trampas

Las columnas generadas pueden parecer una victoria fácil: poner la matemática en la tabla y mantener las consultas limpias. Solo ayudan cuando coinciden con cómo la pantalla filtra y ordena, y cuando añades el índice correcto.

Los errores más comunes:

• Suponer que mejora el rendimiento sin indexar. Un valor calculado todavía necesita un índice para filtrar u ordenar rápido a escala.
• Meter demasiada lógica en un solo campo. Si una columna generada se convierte en un mini programa, la gente deja de confiar en ella. Mantenla corta y nómbrala claramente.
• Usar funciones no inmutables. PostgreSQL requiere que la expresión para una columna generada almacenada sea inmutable. Funciones como now() y random() rompen expectativas y a menudo no están permitidas.
• Ignorar el coste en escrituras. Inserciones y actualizaciones deben mantener el valor calculado. Las lecturas más rápidas no valen la pena si las importaciones e integraciones se vuelven demasiado lentas.
• Crear casi-duplicados. Estandariza uno o dos patrones (como una única clave normalizada) en lugar de acumular cinco columnas similares.

Si tu lista de administración hace búsquedas por contenido (ILIKE '%ann%'), una columna generada por sí sola no lo solucionará. Puede que necesites otro enfoque de búsqueda. Pero para las consultas diarias de “filtrar y ordenar”, las columnas generadas junto con el índice adecuado suelen hacer que el rendimiento sea mucho más predecible.

Lista de comprobación rápida antes de lanzar

Antes de desplegar cambios en una lista de administración, verifica que el valor calculado, la consulta y el índice estén alineados.

• La fórmula es estable y fácil de explicar en una frase.
• Tu consulta realmente usa la columna generada en WHERE y/o ORDER BY.
• El índice coincide con el uso real, no con una prueba puntual.
• Comparaste resultados con la lógica anterior en casos límite (NULLs, cadenas vacías, espacios raros, mayúsculas mezcladas).
• Probaste el rendimiento de escritura si la tabla es muy activa (importaciones, actualizaciones en background, integraciones).

Siguientes pasos: aplica esto a tus pantallas de administración

Elige un punto de partida pequeño y de alto impacto: las 2-3 pantallas de administración que se abren todo el día (órdenes, clientes, tickets). Anota qué se siente lento (un filtro por rango de fechas, ordenar por “última actividad”, buscar por nombre combinado, filtrar por una etiqueta de estado). Luego estandariza un conjunto corto de campos calculados que puedas reutilizar en varias pantallas.

Un plan de despliegue fácil de medir y revertir:

• Añade la(s) columna(s) generada(s) con nombres claros.
• Ejecuta lado a lado la versión antigua y la nueva brevemente si reemplazas lógica existente.
• Añade el índice que coincide con el filtro y orden principales de la pantalla.
• Cambia la consulta de la pantalla para usar la nueva columna.
• Mide antes y después (tiempo de consulta y filas escaneadas), luego elimina la solución antigua.

Si estás construyendo herramientas internas de administración en AppMaster (appmaster.io), estos campos calculados encajan bien en un modelo de datos compartido: la base de datos lleva la regla y tus filtros de UI pueden apuntar a un nombre de campo sencillo en lugar de repetir expresiones en cada pantalla.