Tema: sql
-
Búsqueda de texto completo en PostgreSQL con tsvector
PostgreSQL convierte el texto en un tsvector de lexemas normalizados usando una configuración de idioma, lo compara con tsquery, lo clasifica con ts_rank y lo indexa con GIN; gestiona la derivación de raíces (stemming) y las palabras vacías, pero no las erratas ni los sinónimos de forma nativa.
-
Almacenar datos derivados en PostgreSQL: columnas generadas frente a vistas materializadas
Una columna generada deriva un valor por fila a partir únicamente de esa fila, ya sea de forma virtual (calculada en la lectura) o almacenada (calculada en la escritura), y solo puede usar expresiones inmutables; una vista materializada almacena el resultado de cualquier consulta y está tan actualizada como su último REFRESH. Conviene usar columnas generadas almacenadas para la normalización por fila que deba indexarse, vistas materializadas para agregados costosos que puedan quedar desactualizados, y una tabla de resumen mantenida al día cuando ninguna de las dos encaja.
-
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.
-
NULL en SQL: la lógica trivalente y sus trampas
NULL significa desconocido, así que las comparaciones con NULL producen desconocido en lugar de verdadero o falso; los filtros WHERE descartan las filas con resultado desconocido, NOT IN con un NULL no encuentra nada, y las funciones de agregación omiten los NULL. Usar IS NULL, IS DISTINCT FROM y COALESCE de forma deliberada.
-
Expresiones de tabla común y consultas recursivas con WITH
WITH nombra una subconsulta para el resto de una sentencia; WITH RECURSIVE evalúa un término no recursivo y luego repite un término recursivo hasta que este no produce filas nuevas, lo que permite recorrer árboles y grafos de cualquier profundidad en una sola consulta. Las CTE de un solo uso y sin efectos secundarios se integran en la consulta externa a menos que se escriba MATERIALIZED, y las cláusulas SEARCH y CYCLE gestionan el orden y los bucles.
-
Estadísticas del planificador en PostgreSQL: objetivos de estadísticas, columnas correlacionadas y estimaciones erróneas
El planificador estima el número de filas a partir de estadísticas por columna que ANALYZE muestrea (valores más comunes, histogramas, recuentos de valores distintos) y asume columnas independientes; las estimaciones erróneas provienen de estadísticas obsoletas, columnas sesgadas con muy pocas entradas, columnas correlacionadas y expresiones. Aumentar el objetivo de estadísticas en columnas específicas, crear estadísticas extendidas para columnas correlacionadas o expresiones, volver a analizar, y comparar las filas estimadas con las reales.
-
Lectura de un plan de consulta de PostgreSQL con EXPLAIN ANALYZE
EXPLAIN muestra el árbol elegido por el planificador con costes estimados; EXPLAIN ANALYZE ejecuta la consulta y añade tiempos y recuentos de filas reales. Comparar las filas estimadas con las reales, encontrar el nodo con el mayor tiempo real y comprobar si hay recorridos secuenciales en tablas grandes y uniones mal estimadas.
-
NULL en SQL: la lógica trivaluada y sus trampas
NULL significa desconocido, por eso toda comparación con NULL no da ni verdadero ni falso, sino desconocido; WHERE descarta las filas desconocidas, NOT IN con un NULL no encuentra nada, las funciones agregadas omiten NULL, y una restricción de unicidad permite varios NULL. Usar deliberadamente IS NULL, IS DISTINCT FROM y COALESCE.
-
Los savepoints y el estado de transacción abortada en PostgreSQL
Tras cualquier error dentro de un bloque de transacción, PostgreSQL rechaza todos los comandos posteriores hasta que el bloque se revierte; un savepoint definido antes de una sentencia riesgosa permite que ROLLBACK TO SAVEPOINT descarte solo esa parte y continúe. Usar los savepoints de forma deliberada y con moderación, y hacer que los manejadores de error reviertan en lugar de reintentar en la misma conexión.
-
Paginación por cursor en lugar de offsets: páginas que permanecen estables ante los cambios
La paginación por offset obliga a la base de datos a calcular igualmente todas las filas omitidas y desplaza las páginas en cuanto se inserta o elimina algo entre medias; la paginación por cursor o por conjunto de claves (keyset) pregunta «los siguientes 20 después de la clave X», aprovecha el índice y entrega cada fila exactamente una vez. Requiere un orden de clasificación único, y su precio es renunciar a los números de página.
-
Convenciones de esquema para una base de datos PostgreSQL nueva: nombres, identificadores, marcas de tiempo y texto
Conviene decidir un puñado de convenciones antes de la primera migración: nombres en snake_case en minúsculas que nunca necesiten comillas, una única estrategia de id aplicada en todas partes, timestamptz para cada instante con created_at en todas las tablas, text en lugar de varchar(n), y NOT NULL y claves foráneas explícitas; conviene dejarlas por escrito para que toda migración posterior las siga.
-
Operaciones masivas en lugar de bucles fila por fila: viajes de ida y vuelta, transacciones y COPY
Un bucle que envía una sentencia por fila paga un viaje de ida y vuelta, un análisis sintáctico y, a menudo, un commit por cada fila; una operación masiva envía el conjunto en una sola sentencia, un único flujo o una única transacción. La propia guía de PostgreSQL recomienda hacer un solo commit, usar COPY para las cargas y crear los índices después de cargar; las bibliotecas cliente ofrecen executemany y copy por el mismo motivo.
-
Comprobaciones de calidad de datos: actualidad, volumen, nulos y unicidad como conjunto mínimo
Cuatro comprobaciones baratas detectan la mayoría de las cargas defectuosas: ¿la fuente es lo bastante reciente?, ¿llegó un número plausible de filas?, ¿están rellenas las claves y las métricas?, ¿es única la granularidad declarada? Cada comprobación se formula como una consulta que devuelve las filas erróneas, se ejecuta tras la carga y antes de publicar, y distingue entre aviso y bloqueo.
-
¿Qué tamaño de pool de conexiones en relación con los núcleos de CPU han adoptado los equipos en un servidor PostgreSQL y qué medición los llevó a cambiarlo?
Pregunta abierta: la wiki de PostgreSQL propone una fórmula para dimensionar las conexiones activas según el número de núcleos y de discos efectivos, y el valor por defecto de max_connections suele ser 100. ¿Qué tamaños de pool han acabado usando los equipos tras ajustarlos, cuánto se alejan de la fórmula y qué evidencia motivó cada cambio (esperas por bloqueos, colas en el pooler, saturación de CPU, latencia)?
-
Borrado lógico frente a tablas de archivo
Un borrado lógico conserva la fila con una marca deleted_at, de modo que cada consulta debe filtrarla y cada restricción de unicidad debe convertirse en un índice parcial; una tabla de archivo saca la fila de la tabla activa, así las consultas sobre datos vigentes siguen siendo simples y el historial queda en un solo lugar. La elección depende de quién lee los datos borrados y con qué frecuencia, y debe plasmarse en restricciones y vistas, no en cada consulta.
-
Escribir un upsert con INSERT ... ON CONFLICT
INSERT ... ON CONFLICT (columnas) DO UPDATE SET ... convierte el «insertar o actualizar» en una sola sentencia atómica basada en un índice único: se indica el destino del conflicto, se actualizan solo las columnas que deben cambiar, se usa EXCLUDED para la fila propuesta y se añade un WHERE que omite las actualizaciones sin efecto. MERGE cubre los casos con varias ramas que ON CONFLICT no puede expresar.
-
Consultas N+1: detectarlas contando y corregirlas con batching
Cargar N filas padre y acceder luego a una relación perezosa en cada una genera N+1 consultas; el coste crece con los datos y no con el código, por lo que pasa desapercibido en pruebas con fixtures pequeños. Se detecta verificando el número de consultas por petición, haciendo que las cargas perezosas no deseadas lancen una excepción, y se corrige con joins en relaciones to-one y con segundas consultas por lotes con IN en colecciones.
-
Normalización hasta la tercera forma normal y cuándo desnormalizar
Las formas normales eliminan los grupos repetitivos y los datos almacenados en más de un lugar; la tercera forma normal exige que toda columna no clave dependa de la clave y de nada más. Lo recomendable es normalizar por defecto los datos transaccionales y desnormalizar solo en columnas derivadas con nombre propio, manteniendo normalizada la fuente de verdad.
-
Restricciones declarativas en PostgreSQL: CHECK, UNIQUE y claves foráneas con ON DELETE
Las restricciones hacen que la base de datos rechace estados no válidos para cualquier escritor, no solo para la aplicación: CHECK para reglas por fila, NOT NULL para valores obligatorios, UNIQUE para la identidad y claves foráneas con una acción ON DELETE explícita. Elegir la acción referencial e indexar la columna que referencia son las dos decisiones que más se omiten.
-
Cómo encontrar las sentencias más costosas con pg_stat_statements
pg_stat_statements agrega, para todo el servidor, el número de llamadas, el tiempo de ejecución y los contadores de bloques por sentencia normalizada. Se carga mediante shared_preload_libraries; después se ordena por total_exec_time y, por separado, por calls, se compara la media con el máximo, se revisan las columnas de buffers, se analizan las sentencias principales con EXPLAIN ANALYZE y se compara el antes y el después por queryid.
Legible por máquina: JSON