Tema: postgresql
-
Gestión de extensiones de PostgreSQL: instalación, versionado, actualización y volcado
Una extensión empaqueta objetos SQL y, a menudo, una biblioteca compartida bajo un único nombre, con un archivo de control y scripts versionados; CREATE EXTENSION la instala por base de datos, ALTER EXTENSION UPDATE aplica los scripts de actualización de quien la desarrolla, y pg_dump solo emite la línea CREATE EXTENSION. Es necesario mantener sincronizados los archivos instalados, la versión del catálogo y la biblioteca cargada, especialmente entre actualizaciones de paquetes y con pg_upgrade.
-
Diseñar una tabla de series temporales de solo inserción en PostgreSQL
Almacenar las mediciones en una tabla particionada por rango de tiempo con timestamptz, una clave compuesta de serie y tiempo, índices ajustados al patrón de consulta (B-tree por serie, BRIN para recorridos de tiempo sobre toda la tabla) y una retención implementada separando y eliminando particiones en lugar de usar DELETE; estas decisiones se derivan de que las filas llegan en orden temporal y se eliminan en bloques temporales completos.
-
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.
-
VACUUM, autovacuum y la hinchazón de las tablas
El control de concurrencia multiversión (MVCC) de PostgreSQL deja versiones de filas muertas tras las actualizaciones y las eliminaciones; VACUUM las recupera y mantiene las estadísticas y la protección contra el desbordamiento del identificador de transacción. El autovacuum debe permanecer activado y ajustarse para las tablas con mucha actividad.
-
Bases de datos efímeras en contenedores para pruebas de integración
Iniciar el motor de base de datos real en un contenedor desechable por cada ejecución de pruebas, mantener sus datos en memoria, aplicar las migraciones una sola vez a una base de datos plantilla y copiarla por cada archivo de pruebas; así las pruebas ejercitan el planificador real, las restricciones y el comportamiento de aislamiento en lugar de un sustituto en memoria.
-
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.
-
Niveles de aislamiento de transacciones en la práctica
Read committed, repeatable read y serializable intercambian concurrencia por coherencia; saber qué anomalías permite cada nivel determina cuándo añadir bloqueos explícitos o reintentos.
-
Cambios en la base de datos sin interrupción: expandir y contraer
Modificar un esquema en tres pasos desplegables por separado: expandir (crear una columna o tabla nueva y conservar la antigua), migrar (escribir por duplicado y rellenar en lotes pequeños), contraer (eliminar lo antiguo en cuanto todo el código use lo nuevo); evitar bloqueos largos no reescribiendo ninguna tabla en una sola sentencia, y limitando toda espera con lock_timeout.
-
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.
-
Columnas JSONB: para qué sirven y cuándo es mejor una columna
jsonb almacena JSON analizado en una forma binaria que puede indexarse con GIN y consultarse con operadores de contención y de ruta; conviene a atributos dispersos, definidos externamente o realmente variables. Los datos con una forma fija, los datos que necesitan restricciones, claves foráneas o actualizaciones por campo, y los documentos grandes que cambian con frecuencia, pertenecen a columnas ordinarias.
-
La agrupación de conexiones de base de datos y sus límites
Cada conexión de PostgreSQL es un proceso con un costo de memoria; las aplicaciones deben mantener un grupo (pool) pequeño, dimensionado según la concurrencia real, establecer tiempos de espera de adquisición, y no permitir nunca que los manejadores de solicitudes abran conexiones ad hoc.
-
Reclamos de PostgreSQL SKIP LOCKED medidos con cuatro consumidores concurrentes de una cola
Cuatro transacciones concurrentes reclamaron 25 trabajos sintéticos cada una en PostgreSQL 16.15. Los 100 ID devueltos eran únicos y no quedó ningún trabajo sin reclamar; esto verifica una fase de reclamo acotada, no un procesamiento exactamente una vez ni la sustitución de un bróker.
-
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.
-
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.
-
Las migraciones de esquema ejecutadas con un lock_timeout corto y reintento automático causan menos incidentes en el momento del despliegue que las migraciones sin él
Hipótesis: dado que la mayoría de las formas de ALTER TABLE toman un bloqueo ACCESS EXCLUSIVE que se pone en cola detrás de cualquier transacción larga mientras bloquea toda consulta posterior, las migraciones ejecutadas con un lock_timeout de unos pocos segundos y un bucle de reintento acotado producen interrupciones más cortas y menos frecuentes en el momento del despliegue que las mismas migraciones ejecutadas con la espera ilimitada por defecto, al costo de que algunas migraciones necesiten una reejecución manual.
-
Las políticas de seguridad a nivel de fila reducen las fugas de datos entre arrendatarios en comparación con el filtrado del lado de la aplicación
Hipótesis: los servicios multiarrendatario que imponen el aislamiento entre arrendatarios con políticas de seguridad a nivel de fila de PostgreSQL (un ajuste de arrendatario por conexión que verifica la base de datos) presentan menos errores de exposición entre arrendatarios que los servicios que añaden un predicado tenant_id a cada consulta, porque una consulta que olvida el predicado de arrendatario sigue viendo solo las filas del arrendatario actual, y una solicitud que nunca establece el arrendatario no obtiene nada o recibe un error, en lugar de las filas de todos los arrendatarios.
-
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.
-
Replicación lógica en PostgreSQL: publicaciones, suscripciones y en qué se diferencia de la replicación en streaming
La replicación en streaming (física) envía el write-ahead log a un standby idéntico byte a byte, de la misma versión mayor y arquitectura; la replicación lógica publica los cambios de fila de tablas seleccionadas hacia un suscriptor que puede ejecutar una versión mayor distinta y que puede mantener sus propios datos. La replicación lógica necesita una identidad de réplica, no transporta ni DDL ni los valores de las secuencias, y su slot retiene WAL en el publicador mientras el suscriptor está retrasado.
-
Réplicas de lectura y lag de replicación: cómo se ven las lecturas obsoletas y cómo acotarlas
Una réplica por streaming aplica el registro del primario con cierto retraso, por lo que una lectura justo después de una escritura puede no verla. Las lecturas se enrutan según cuánta obsolescencia tolere cada solicitante, los modos de replicación síncrona se usan solo donde su latencia es aceptable, y hay que entender que reproducir el registro puede cancelar consultas largas en la réplica.
-
Sesiones de larga duración e inactivas en transacción en PostgreSQL: qué bloquean y cómo acotarlas
Una transacción abierta retiene sus bloqueos y fija el horizonte xmin, de modo que VACUUM no puede eliminar las filas borradas después de que empezó, y el DDL queda encolado detrás de ella; el peor caso es una sesión inactiva en transacción porque un cliente nunca hizo commit. Se localizan en pg_stat_activity por xact_start y state, y se acotan por rol con idle_in_transaction_session_timeout, transaction_timeout y statement_timeout.
Legible por máquina: JSON