PostgreSQL
Modelo mental completo de PostgreSQL
Modelo mental completo de PostgreSQL que conecta diseño relacional, SQL, integridad, MVCC, índices, planner, almacenamiento, seguridad y operación.
- Última actualización
- Actualizada
- Nivel
- Profundización
PostgreSQL
Modelo mental completo de PostgreSQL que conecta diseño relacional, SQL, integridad, MVCC, índices, planner, almacenamiento, seguridad y operación.
Comprender PostgreSQL significa seguir el recorrido completo del dato: desde lo que representa en el dominio hasta cómo se consulta, bloquea, almacena, replica, protege y recupera después de un fallo.
dominio y requisitos
↓
modelo relacional
↓
tipos, claves y constraints
↓
queries y transformaciones
↓
transacciones y concurrencia
↓
planner, estadísticas e índices
↓
MVCC, vacuum, pages y WAL
↓
roles, red, TLS y seguridad
↓
backups, replicación, HA y DR
↓
observabilidad, upgrades y operaciónPostgreSQL no empieza en SELECT. Empieza en decidir qué información existe, qué significa y qué estados deben ser imposibles.
Antes de crear schema, responde:
Ejemplo:
order
→ intención comercial confirmable
order_item
→ producto, cantidad y precio capturado
inventory
→ disponibilidad actual por branch y product
inventory_movement
→ historial inmutable de cambiosNo todas estas preguntas se resuelven con la misma tabla ni con JSONB.
Cada tabla expresa un predicado:
orders
→ “existe una orden con estas propiedades”
order_items
→ “esta orden contiene este producto con esta cantidad y precio”La granularidad debe estar clara:
una fila por order
una fila por item de order
una fila por branch-productGran parte de los errores de joins y agregaciones proviene de perder esta unidad.
Distingue:
Una sequence genera valores únicos concurrentes, pero no garantiza continuidad ni orden de commit.
Ejemplo:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL UNIQUE,
UNIQUE (business_id, order_number)Cada clave tiene un propósito diferente.
El tipo reduce estados inválidos:
numeric → dinero exacto
boolean → verdadero/falso
uuid → identidad
inet → red
tstzrange → intervalo temporal
jsonb → estructura variableNo guardes datos tipados como text solo por comodidad.
Para tiempo:
timestamptz → instante global
timestamp → hora local sin zona
date → fecha civilLa zona de negocio debe ser explícita.
NULL significa ausencia o desconocimiento, no cadena vacía ni cero.
Pregunta por cada columna nullable:
SQL usa lógica de tres valores:
TRUE
FALSE
UNKNOWNWHERE conserva únicamente TRUE.
La aplicación mejora UX; la base protege integridad.
Herramientas:
NOT NULL.CHECK.UNIQUE.Ejemplo:
CHECK (quantity > 0),
UNIQUE (business_id, sku),
FOREIGN KEY (business_id, customer_id)
REFERENCES customers (business_id, id)Una validación previa en backend no sustituye una constraint bajo concurrencia.
Normaliza para evitar anomalías de actualización, pero conserva snapshots cuando el valor histórico no debe cambiar.
Ejemplo:
products.current_price
→ dato actual
order_items.unit_price
→ snapshot del precio vendidoUn reporte histórico no debe depender del nombre o precio actual si el negocio exige evidencia original.
Un SELECT construye una relación:
FROM → fuentes
WHERE → filas
GROUP BY → granularidad
SELECT → columnas
ORDER BY → secuencia
LIMIT → cantidadSin ORDER BY, no existe orden garantizado.
Sin desempate único, la paginación es inestable.
Antes de unir, estima:
0, 1 o N coincidencias por filaDos relaciones one-to-many producen multiplicación:
3 items × 2 payments = 6 filasPreagrega cada lado si el resultado necesita una fila por order.
EXISTS expresa presencia sin multiplicar la fila externa.
GROUP BY
→ colapsa filas
→ una fila por grupo
window function
→ conserva filas
→ añade cálculo por partición/frameLa métrica correcta comienza por una definición de negocio:
Evita read-modify-write en la aplicación cuando la condición puede expresarse en SQL:
UPDATE inventory
SET reserved_quantity = reserved_quantity + $1
WHERE branch_id = $2
AND product_id = $3
AND quantity - reserved_quantity >= $1
RETURNING reserved_quantity;Cero filas es un resultado de negocio, no necesariamente error técnico.
RETURNING conecta el estado real de la base con la aplicación.
BEGIN
→ cambios locales coordinados
→ verificar resultados
→ COMMIT o ROLLBACKLa transacción pertenece a una conexión.
Debe ser corta y no esperar HTTP, email, pagos o humanos.
Para side effects externos:
transacción local + outbox
→ commit
→ worker idempotenteUn UPDATE normalmente crea una nueva tuple.
Cada snapshot decide qué versión es visible.
lectores normales no bloquean writers
writers sobre la misma fila sí compitenConsecuencias:
Snapshot por statement. Correcto para gran parte del CRUD bien expresado.
Snapshot estable; útil para reportes, pero puede permitir write skew.
Detecta historias no serializables y aborta con 40001.
Necesita retries completos.
El nivel no reemplaza constraints, locks ni buen modelado.
Usa locks cuando una decisión necesita un recurso estable:
SELECT ... FOR UPDATE;Opciones:
NOWAIT para fallo rápido.SKIP LOCKED para workers.Prevén deadlocks adquiriendo recursos en orden consistente.
Un índice es una copia ordenada/especializada que acelera lecturas y encarece writes.
Pregunta:
Familias:
Un sequential scan puede ser el plan correcto.
El planner no ejecuta todas las alternativas; estima costes y cardinalidades.
statistics
→ selectivity
→ estimated rows
→ scan/join/order strategyLa comparación más valiosa en EXPLAIN ANALYZE suele ser:
estimated rows vs actual rowsUna mala estimación temprana contamina el resto del plan.
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)
SELECT ...;Lee de adentro hacia afuera y revisa:
EXPLAIN ANALYZE ejecuta DML; úsalo con extremo cuidado.
relation files
→ pages
→ line pointers
→ tuplesEl heap no está ordenado por PK.
TOAST mueve/comprime valores grandes.
Visibility map ayuda a index-only scans.
Free space map guía reutilización.
Shared buffers y OS cache trabajan juntos.
Vacuum:
Vacuum normal no suele reducir el archivo; deja espacio reutilizable.
VACUUM FULL reescribe y bloquea fuertemente.
cambio
→ WAL durable
→ COMMIT
→ data pages pueden persistirse despuésWAL permite crash recovery, archiving y physical replication.
No es backup.
Checkpoints limitan el punto de recuperación, pero demasiado frecuentes aumentan I/O y full-page images.
JSONB es apropiado para estructura variable, no para evitar relaciones.
Promueve una key a columna cuando participa en:
Distingue SQL NULL, JSON null y key ausente.
Particionar divide una tabla lógica por key y bounds.
Aporta:
No aporta automáticamente velocidad.
La query debe filtrar la partition key y la operación debe mantener particiones futuras.
Particionamiento no es sharding.
red
→ TLS
→ autenticación
→ role
→ object privileges
→ RLS
→ queries seguras
→ logs/backups protegidosEl runtime no debe ser owner ni superuser.
Usa parámetros para values y whitelist para estructura dinámica.
RLS protege filas, pero no sustituye toda autorización.
Cada conexión suele corresponder a un backend process.
Demasiadas conexiones reducen throughput.
Define presupuesto global:
instancias × pool size + jobs + administración + margenUna transacción usa el mismo client.
Transaction pooling limita session state, prepared statements, temp tables y LISTEN.
En rolling deploy conviven versiones de aplicación.
Patrón expand-contract:
añadir
→ escribir compatible
→ backfill
→ cambiar readers
→ dejar de usar
→ eliminar despuésEvalúa locks, rewrites, WAL, replica lag y rollback.
Una migration aplicada no debe editarse retroactivamente.
Capacidades distintas:
Un backup no está verificado hasta restaurarlo.
Define:
RPO → pérdida máxima aceptable
RTO → tiempo máximo de recuperaciónPhysical replication reproduce WAL del cluster.
Logical replication publica cambios de tablas.
HA necesita:
Failover puede producir pérdida según la durabilidad configurada.
Empieza por el síntoma:
latencia
→ pool, locks, CPU, I/O, query, replication o clienteInstrumentos:
pg_stat_activity.pg_locks.pg_stat_statements.No optimices sin baseline.
Una major version cambia más que binarios:
Estrategias: pg_upgrade, dump/restore, logical migration o proveedor.
El rollback cambia después del primer write en destino.
Una order pending puede confirmarse si todos sus items tienen stock.
lock/condición de estado
→ reservar todos los items
→ marcar order confirmed
→ insertar outbox
→ commitDos confirmaciones no deben reservar dos veces. Usa estado condicional, row locks u operación idempotente.
(branch_id, product_id) en inventory.(order_id) en items.Este caso muestra que una feature atraviesa todo PostgreSQL.
1. definir síntoma y periodo
2. identificar query + parámetros + application_name
3. separar espera de ejecución
4. revisar locks/pool/I/O
5. obtener plan seguro
6. comparar estimates y actual
7. formular una hipótesis
8. cambiar una variable
9. medir y observar regresionesNo empieces creando un índice al azar.
1–16 y 24:
17–23, 35–51 y 55–62:
25–34, 39–41, 45, 49, 52–59 y 63–67:
No avances solo por número. Cada nivel debe poder explicarse y demostrarse.
public y search_path controlados.Puedes afirmar que comprendes PostgreSQL cuando puedes:
Cada solución debe justificar semántica, integridad, cardinalidad, concurrencia, rendimiento, seguridad, evolución y recuperación. SQL válido por sí solo no demuestra comprensión.
PostgreSQL es un sistema integrado:
significado
→ integridad
→ concurrencia
→ acceso
→ almacenamiento
→ durabilidad
→ seguridad
→ recuperaciónCuando puedes seguir ese recorrido y justificar cada decisión, dejas de “usar una base de datos” y empiezas a diseñar y operar un sistema de datos.