PostgreSQL 17 & 18: La Base de Datos Open-Source Avanzada

Un buen esquema es la base del rendimiento de la base de datos. La mayor fortaleza de PostgreSQL es su sistema de tipos rico y estricto: además del diseño relacional normalizado de siempre, te da JSONB nativo, arrays, rangos, enums y dominios y tipos compuestos definidos por el usuario. Modela tus datos bien primero, y recurre a los tipos semi-estructurados solo donde la forma realmente varía por fila.

• Normaliza primero (1NF-3NF): columnas atómicas, una primary key real, sin dependencias parciales ni transitivas. Desnormaliza solo con evidencia medida. PostgreSQL impone integridad con constraints CHECK, UNIQUE, NOT NULL, EXCLUDE y FOREIGN KEY, incluyendo las diferibles
• Columnas identity sobre serial: prefiere GENERATED ALWAYS AS IDENTITY (estándar SQL) sobre el pseudo-tipo serial heredado. Para inserciones distribuidas, PostgreSQL 18 agrega una función nativa uuidv7() cuyos valores ordenados por tiempo evitan los splits aleatorios de páginas B-tree que causa UUIDv4
• Dimensiona bien los tipos: int (4 bytes) vs bigint (8 bytes); text y varchar(n) se almacenan idénticamente (no hay penalización de rendimiento por text en Postgres). Almacena siempre los instantes como timestamptz (guardado en UTC), nunca timestamp ingenuo. Usa numeric para dinero, nunca float
• JSONB para datos semi-estructurados: jsonb es un formato binario descompuesto que soporta indexado y operadores ricos. Prefiérelo sobre json (que guarda texto crudo). Úsalo para metadata, feature flags y payloads cuya forma varía -- no como excusa para evitar columnas que consultas y restringes seguido
• Arrays y rangos: las columnas array nativas (text[], int[]) evitan tablas de unión para listas simples. Los tipos rango (int4range, tstzrange) modelan intervalos, y un constraint EXCLUDE USING gist impide reservas superpuestas a nivel de base de datos -- algo que MySQL no puede expresar
• Enums y dominios: CREATE TYPE ... AS ENUM da enumeraciones compactas y ordenadas; CREATE DOMAIN adjunta constraints CHECK reutilizables a un tipo base. Los soft deletes usan deleted_at timestamptz más un índice parcial (Postgres los soporta nativamente, a diferencia de MySQL)

-- Well-designed PostgreSQL schema
CREATE TYPE user_status AS ENUM ('active','inactive','suspended');

CREATE TABLE users (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id   uuid NOT NULL DEFAULT uuidv7(),   -- time-ordered UUID (PG 18)
  email       citext NOT NULL,                  -- case-insensitive (citext ext.)
  name        text NOT NULL,
  status      user_status NOT NULL DEFAULT 'active',
  tags        text[] NOT NULL DEFAULT '{}',     -- native array
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  deleted_at  timestamptz,
  CONSTRAINT uq_users_email UNIQUE (email)
);

CREATE TABLE bookings (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users(id),
  room_id     bigint NOT NULL REFERENCES rooms(id),
  during      tstzrange NOT NULL,               -- the reserved interval
  status      text NOT NULL DEFAULT 'confirmed'
              CHECK (status IN ('confirmed','cancelled','completed')),
  created_at  timestamptz NOT NULL DEFAULT now(),
  -- No two confirmed bookings for the same room may overlap in time:
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
    WHERE (status = 'confirmed')
);

Índices B-tree

B-tree es el método de acceso por defecto y más usado de PostgreSQL. Mantiene las claves ordenadas y sirve queries de igualdad, rango, ORDER BY y MIN/MAX. A diferencia de InnoDB de MySQL, las tablas de PostgreSQL están organizadas como heap (no clusterizadas por la primary key), así que cada índice -- incluida la primary key -- es una estructura secundaria separada que apunta a las tuplas del heap.

• Regla del prefijo más a la izquierda: un índice compuesto en (a, b, c) sirve predicados sobre (a), (a, b) o (a, b, c) -- pero no (b) ni (c) solos. PostgreSQL 18 agrega el skip scan de B-tree, que puede usar columnas posteriores cuando una columna líder tiene pocos valores distintos
• El orden de columnas importa: pon las columnas de igualdad antes que las de rango, y en general la más selectiva primero. Haz coincidir la dirección de orden del índice con tu ORDER BY (puedes especificar ASC/DESC y NULLS FIRST/LAST)
• Índices cubrientes con INCLUDE: agrega columnas de payload no-clave con INCLUDE (...) para que un index-only scan devuelva todo sin ir al heap. EXPLAIN muestra Index Only Scan. Los index-only scans también dependen de que el visibility map esté fresco (mantén VACUUM sano)
• Selectividad: ratio de valores distintos sobre filas. Un boolean solo es mal candidato para B-tree -- usa un índice parcial en su lugar. Ejecuta ANALYZE para que el planner tenga estadísticas actuales; extiéndelas con CREATE STATISTICS para columnas correlacionadas
• Overhead de escritura y HOT: cada índice se mantiene en cada escritura. La optimización Heap-Only Tuple (HOT) de PostgreSQL evita el churn de índices cuando un UPDATE no toca ninguna columna indexada y la página tiene espacio libre -- otra razón para no sobre-indexar y dejar margen de fillfactor en tablas calientes

-- Composite index matching a common access pattern
-- SELECT ... FROM bookings WHERE user_id = $1 AND status = 'confirmed'
--   ORDER BY created_at DESC LIMIT 20;
CREATE INDEX idx_bookings_user_status_created
  ON bookings (user_id, status, created_at DESC);

-- Covering / index-only scan with INCLUDE (payload not part of the key)
-- SELECT id, email, name FROM users WHERE status = 'active' ORDER BY created_at DESC;
CREATE INDEX idx_users_status_created
  ON users (status, created_at DESC) INCLUDE (email, name);

-- Build without long write locks in production:
CREATE INDEX CONCURRENTLY idx_users_status_created
  ON users (status, created_at DESC) INCLUDE (email, name);

Índices GIN y GiST

Más allá de B-tree, PostgreSQL trae varios métodos de acceso especializados. GIN (Generalized Inverted Index) y GiST (Generalized Search Tree) son los dos que más vas a usar: GIN indexa los elementos dentro de valores compuestos (claves JSONB, elementos de array, lexemas de texto completo, trigramas), mientras GiST indexa datos geométricos, de rango y de vecino más cercano. Aquí es donde Postgres se adelanta decididamente a MySQL.inside composite values (JSONB keys, array elements, full-text lexemes, trigrams), while GiST indexes geometric, range, and nearest-neighbor data. This is where Postgres pulls decisively ahead of MySQL.

• GIN para JSONB / arrays / FTS: un índice GIN acelera contención (@>), existencia de clave (?), solapamiento de arrays (&&) y texto completo (@@). Usa la clase de operadores jsonb_path_ops para un índice de solo-contención más chico y rápido. Combínalo con pg_trgm para que LIKE '%term%' y la búsqueda difusa usen índice
• GiST para rangos y geometría: GiST potencia el solapamiento de rangos (&&), los constraints EXCLUDE, el vecino más cercano (ORDER BY point <-> target) y las queries espaciales de PostGIS. SP-GiST sirve para estructuras no balanceadas como quadtrees y prefijos de IP
• Índices hash reales: PostgreSQL también tiene un índice genuino USING hash -- a prueba de fallos y registrado en WAL desde PG 10 -- para búsquedas de solo igualdad sobre claves grandes no ordenables. Es de nicho; un B-tree suele ser el mejor default

-- GIN on a JSONB column for containment / key queries
CREATE INDEX idx_users_metadata ON users USING gin (metadata jsonb_path_ops);
-- Uses the index:  WHERE metadata @> '{"plan":"pro"}'

-- GIN on an array column
CREATE INDEX idx_users_tags ON users USING gin (tags);
-- Uses the index:  WHERE tags && ARRAY['vip','beta']   -- overlap

-- Trigram GIN makes substring / fuzzy search index-backed
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
-- Uses the index:  WHERE name ILIKE '%nobile%'

-- GiST for nearest-neighbor on geometry / vectors of points
CREATE INDEX idx_places_geom ON places USING gist (location);
-- ORDER BY location <-> ST_MakePoint(-74.07, 4.65) LIMIT 10;

Índices Parciales, de Expresión y BRIN

PostgreSQL soporta nativamente índices parciales (una cláusula WHERE en el índice), índices de expresión (indexar el resultado de una función) y BRIN (Block Range INdex) para tablas enormes y naturalmente ordenadas. Son características de primera clase -- MySQL solo puede aproximarlas con columnas generadas.

• Índices parciales: CREATE INDEX ... WHERE deleted_at IS NULL indexa solo filas vivas, haciendo el índice más chico, rápido y barato de mantener. Perfecto para soft deletes, o para una cola de "jobs pendientes" donde solo una fracción mínima de filas importa
• Índices de expresión: indexa un valor computado como lower(email) o (metadata->>'plan'). La query debe usar exactamente la misma expresión para coincidir. Esto reemplaza los índices funcionales de MySQL y es más flexible
• BRIN para tablas append-only: BRIN guarda solo el min/max por rango de bloques, así que un índice sobre una tabla de series temporales de mil millones de filas puede ocupar unos pocos megabytes. Brilla cuando el orden físico de las filas correlaciona con la columna (p. ej. un created_at monótonamente creciente). Diminuto, pero solo útil en datos bien correlacionados

-- Partial index: only index rows that are not soft-deleted
CREATE INDEX idx_users_active ON users (created_at DESC)
  WHERE deleted_at IS NULL;

-- Partial index for a work queue (indexes ~0.1% of the table)
CREATE INDEX idx_jobs_pending ON jobs (run_after)
  WHERE status = 'pending';

-- Expression index for case-insensitive lookups
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- Uses the index:  WHERE lower(email) = lower($1)

-- BRIN on a massive, time-ordered append-only table (tiny footprint)
CREATE INDEX idx_events_created_brin ON events
  USING brin (created_at) WITH (pages_per_range = 32);

Errores Comunes de Indexado

• Funciones sobre columnas indexadas: WHERE date_trunc('year', created_at) = '2026-01-01' no puede usar un B-tree simple. Reescribe como rango: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01', o construye un índice de expresión que coincida
• Casts implícitos: comparar una columna text con un literal entero, o un timestamptz con un string pelado, puede anular un índice. Haz coincidir los tipos y castea explícitamente
• LIKE con comodín al inicio: WHERE name LIKE '%smith' no puede usar un B-tree normal. Un índice GIN pg_trgm lo arregla; LIKE 'smith%' simple funciona con un B-tree usando text_pattern_ops
• OR entre columnas: WHERE a = 1 OR b = 2 a menudo no puede usar un solo índice compuesto. PostgreSQL puede combinar dos índices separados con un BitmapOr, o reescribe la query como un UNION
• Índices redundantes y sin uso: cada índice suma costo de escritura y bloat. Encuentra el peso muerto con pg_stat_user_indexes (idx_scan = 0) y revisa el tamaño con pg_relation_size(). Elimina lo que el planner nunca elige

EXPLAIN ANALYZE y pg_stat_statements

EXPLAIN es la herramienta más importante para el rendimiento de PostgreSQL. El EXPLAIN simple muestra el plan elegido por el planner y sus estimaciones de costo; EXPLAIN (ANALYZE, BUFFERS) realmente ejecuta la query y reporta tiempos reales, conteos reales de filas y cuántas páginas vinieron de caché versus disco.

• Postgres como única fuente de verdad: primario más réplicas de lectura streaming, WAL archivado para recuperación point-in-time, y pgBackRest para backups comprimidos y verificados. La replicación lógica manejó un upgrade de versión mayor con downtime mínimo
• Connection pooling: puse PgBouncer en modo transaction delante del primario. Miles de conexiones de cliente colapsaron sobre un pool de servidor pequeño, y los incidentes de "too many connections" bajaron a cero
• Optimización de queries: usé pg_stat_statements y EXPLAIN (ANALYZE, BUFFERS) para encontrar las peores queries, agregué índices cubrientes y parciales, y corregí patrones N+1 con lateral joins -- recortando drásticamente la latencia P95
• Disciplina de VACUUM: ajusté autovacuum por tabla en las tablas calientes y alerté sobre el ratio de tuplas muertas y la edad de transaction-ID. Esto eliminó el lento deterioro de latencia por bloat que se había confundido con "la base de datos envejeciendo"
• pgvector para RAG: construye la recuperación para funciones de IA directamente en Postgres con pgvector e índices HNSW -- embeddings, filtros de metadata y joins de negocio en una sola query, sin una base de datos vectorial separada que operar

-- The workhorse: run it, count real rows, show cache behavior
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT b.id, b.created_at, u.name, u.email
FROM bookings b
JOIN users u ON u.id = b.user_id
WHERE b.room_id = 42
  AND b.status = 'confirmed'
  AND b.created_at >= '2026-06-01'
ORDER BY b.created_at DESC
LIMIT 20;

-- Ideal shape of the output:
--  Limit  (actual time=0.05..0.09 rows=20 ...)
--    ->  Index Scan using idx_bookings_room_status_created on bookings b
--          Index Cond: (room_id = 42 AND status = 'confirmed')
--          Buffers: shared hit=24
--    ->  Index Scan using users_pkey on users u  (rows=1)

-- The supporting index:
CREATE INDEX idx_bookings_room_status_created
  ON bookings (room_id, status, created_at DESC);

-- Rank the worst offenders across the whole server:
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Patrones de Optimización

• Paginación por keyset: evita el OFFSET 10000 profundo (Postgres igual lee y descarta esas filas). Pagina por la última clave de orden vista: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20
• COUNT es caro: un COUNT(*) exacto debe escanear las filas coincidentes por la visibilidad MVCC. Para dashboards usa reltuples de pg_class como estimación rápida, o mantén contadores de rollup
• Escrituras en batch: INSERT multi-fila, o COPY para cargas masivas (a menudo 10-50x más rápido). Usa INSERT ... ON CONFLICT DO UPDATE para upserts y MERGE (PG 15+, con RETURNING en PG 17) para merges complejos
• Mata el N+1 con lateral joins: trae un padre más sus top-N hijos en un solo viaje con LEFT JOIN LATERAL (... LIMIT n) ON true, o agrega los hijos en JSON con jsonb_agg
• Selecciona solo lo que necesitas: SELECT * anula los index-only scans y trae columnas TOAST anchas que quizás no uses. Lista las columnas explícitamente y deja que los índices INCLUDE cubran las lecturas calientes
• Ajusta las perillas que importan: configura work_mem por sesión para sorts/hashes grandes, effective_cache_size para que el planner conozca tu RAM, y random_page_cost = 1.1 en SSD/NVMe para que los index scans se costeen bien

Cada conexión de PostgreSQL es un proceso del sistema operativo aparte con su propia memoria (aproximadamente 5-10 MB más work_mem). Unos pocos miles de conexiones directas agotan el servidor mucho antes de saturar la CPU. Como un backend de Postgres es más pesado que un hilo de MySQL, el pooling externo con PgBouncer no es opcional a escala -- es la arquitectura estándar, y es lo que corre por debajo del pooling gestionado en RDS Proxy y Supabase.

• Dimensiona bien el pool: un punto de partida común es max_connections alrededor de 4 x núcleos de CPU para la ruta de escritura -- más procesos suman contención, no throughput. PgBouncer multiplexa miles de conexiones de cliente sobre ese pequeño pool de servidor
• Modos de pooling: el modo transaction es el punto óptimo para apps web -- una conexión de servidor vuelve al pool al terminar cada transacción. El modo session fija una conexión durante toda la sesión del cliente (necesario para estado de sesión); el modo statement es el más agresivo
• Cuidados del modo transaction: no puedes confiar en que el estado de sesión (SET, advisory locks, prepared statements sin nombre, LISTEN/NOTIFY) sobreviva entre transacciones. PgBouncer 1.21+ soporta prepared statements a nivel de protocolo en modo transaction vía max_prepared_statements
• Timeouts y límites: ajusta default_pool_size, max_client_conn, server_idle_timeout y query_wait_timeout para que las ráfagas hagan cola brevemente en vez de abrumar a Postgres o fallar de una
• Pool del lado app también: tu driver (node-postgres pg.Pool, HikariCP, SQLAlchemy) mantiene su propio pool chico apuntando a PgBouncer, no directamente a Postgres. Mantén statement_timeout e idle_in_transaction_session_timeout configurados como redes de seguridad
• Separación lectura/escritura: apunta las lecturas a una réplica física y las escrituras al primario. Cada uno suele tener su propio pool de PgBouncer, multiplicando la capacidad

# pgbouncer.ini -- transaction pooling for a typical web workload
[databases]
myapp = host=10.0.0.10 port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 20          # server conns per user/db pair
max_client_conn = 5000          # client conns PgBouncer will accept
max_prepared_statements = 200   # protocol-level prepared stmts (1.21+)
server_idle_timeout = 300
query_wait_timeout = 30

// Node.js: app pool connects to PgBouncer (:6432), not Postgres directly
import { Pool } from 'pg';

export const db = new Pool({
  host: process.env.PGBOUNCER_HOST,   // PgBouncer, port 6432
  port: 6432,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: 'myapp',
  max: 10,                            // small: PgBouncer does the real fan-out
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
  // Safety nets applied per backend session:
  options: '-c statement_timeout=15000 -c idle_in_transaction_session_timeout=10000'
});

PostgreSQL ofrece dos mecanismos de replicación complementarios construidos sobre el Write-Ahead Log (WAL). La replicación física (streaming) envía el WAL crudo byte por byte para producir réplicas binarias idénticas -- la base de la alta disponibilidad y el escalado de lecturas. La replicación lógica decodifica el WAL en eventos de cambio a nivel de fila, permitiéndote replicar tablas seleccionadas entre versiones mayores o hacia otros sistemas.

• Replicación streaming: un standby recibe WAL continuamente del primario y lo reproduce, manteniéndose binariamente idéntico. Los standbys son "hot standbys" de solo lectura que pueden servir queries de lectura mientras reproducen
• Async vs síncrona: la asíncrona (por defecto) no espera al standby -- rápida, pero un crash puede perder las últimas transacciones. synchronous_commit = on con synchronous_standby_names espera a que un standby persista el WAL, cambiando un poco de latencia por cero pérdida de datos en el failover
• Replication slots: un slot garantiza que el primario retenga el WAL hasta que una réplica lo haya consumido, previniendo errores de "requested WAL segment already removed". Protege contra el crecimiento ilimitado del WAL con max_slot_wal_keep_size
• Replicación lógica: CREATE PUBLICATION en el origen y CREATE SUBSCRIPTION en el destino replican tablas elegidas a nivel de fila. Funciona entre versiones mayores (la base de los upgrades con downtime casi nulo) y puede filtrar filas y columnas
• Monitoreo del lag: consulta pg_stat_replication en el primario y comparas pg_current_wal_lsn() con el replay_lsn de cada réplica. Para consistencia read-after-write, enruta la lectura crítica al primario o usa synchronous_commit
• Herramientas de failover: Postgres no tiene gestor de cluster incorporado, así que la HA en producción usa Patroni (con etcd/Consul) o repmgr para promover un standby automáticamente. PostgreSQL 17 agregó pg_createsubscriber para convertir rápidamente un standby físico en un suscriptor lógico
• Failover slots (PG 17): los replication slots lógicos ahora pueden sincronizarse a los standbys, así que un suscriptor sigue funcionando después de que el publicador hace failover -- antes era un vacío operativo difícil
• CDC y el ecosistema: la decodificación lógica es cómo Debezium, y herramientas como pglogical, transmiten cambios de Postgres a Kafka, data warehouses e índices de búsqueda -- change-data-capture sin escrituras dobles

• Backups lógicos (pg_dump): exporta una base de datos como SQL o un archivo comprimido custom/directory. Portable entre versiones y arquitecturas; usa el formato directory con -j para dump/restore en paralelo. pg_dumpall además captura los globals (roles, tablespaces)
• Base backups físicos (pg_basebackup): una copia a nivel de bytes de todo el cluster, tomada online sin bloquear escrituras. Es la base tanto de los standbys como de la recuperación point-in-time
• Recuperación point-in-time (PITR): un base backup más el WAL archivado te deja restaurar a cualquier momento -- p. ej. un segundo antes de un DELETE malo. Configura archive_mode = on y un archive_command/archive_library para enviar el WAL a almacenamiento durable
• Backups incrementales (PG 17): pg_basebackup --incremental copia solo los bloques cambiados desde un backup previo, y pg_combinebackup reconstruye un backup completo desde la cadena -- backups mucho más chicos y rápidos para clusters grandes
• Usa pgBackRest en producción: agrega backups full/diferenciales/incrementales paralelos, comprimidos y cifrados, políticas de retención y chequeos de integridad por encima de las herramientas crudas. WAL-G y Barman son alternativas sólidas
• Prueba las restauraciones: un backup no probado no es un backup. Automatiza una restauración semanal en una instancia descartable y corre pg_verifybackup más algunas queries de humo. Guarda copias fuera de la región para recuperación ante desastres

# Logical: parallel directory-format dump + parallel restore
pg_dump -h db -U app -d myapp -F d -j 4 -f /backup/myapp_$(date +%F)
pg_restore -h db2 -U app -d myapp_restored -F d -j 4 /backup/myapp_2026-07-01

# Physical base backup (streams WAL so it is self-consistent)
pg_basebackup -h db -U repl -D /backup/base -F tar -z -X stream -c fast

# Incremental base backup (PG 17), then reconstruct a full copy
pg_basebackup -h db -U repl -D /backup/incr \
  --incremental=/backup/base/backup_manifest
pg_combinebackup /backup/base /backup/incr -o /backup/full

# Recommended: pgBackRest full backup + verify
pgbackrest --stanza=myapp --type=full backup
pgbackrest --stanza=myapp verify

El particionamiento declarativo (nativo desde PostgreSQL 10 y mejorado constantemente) divide una tabla lógica en particiones físicas hijas. Las queries pegan al padre; el planner hace partition pruning para tocar solo las hijas relevantes. La mayor ventaja es la gestión del ciclo de vida de los datos: eliminar o desasociar una partición vieja es una operación de metadatos instantánea, no un DELETE lento que genera bloat.

• Particionamiento RANGE: asigna filas por un valor que cae en un rango -- la elección clásica para series temporales. Particiona por mes o día, y retira datos viejos con DROP/DETACH PARTITION en vez de borrados masivos
• Particionamiento LIST: asigna filas por valores discretos (región, tenant, estado). Genial para aislamiento multi-tenant o residencia de datos por país
• Particionamiento HASH: distribuye filas uniformemente entre N particiones por un hash de la clave. Úsalo para paralelizar el I/O cuando no hay un límite natural de rango o lista
• Sub-particionamiento: las particiones pueden a su vez particionarse (p. ej. RANGE por mes, luego HASH por tenant) para datos multi-tenant de series temporales muy grandes
• Pruning y partition-wise joins: incluye la clave de partición en el WHERE para que el planner haga pruning en tiempo de plan y de ejecución. Habilita enable_partitionwise_join y enable_partitionwise_aggregate para queries analíticas grandes
• Reglas y automatización: una clave UNIQUE/PRIMARY debe incluir la clave de partición. Los índices creados en el padre se propagan a todas las particiones. Las foreign keys que referencian una tabla particionada están soportadas en versiones modernas. Automatiza la creación de particiones con pg_partman (y TimescaleDB para series temporales)

-- RANGE partition by month, ideal for event/booking logs
CREATE TABLE booking_logs (
  id          bigint GENERATED ALWAYS AS IDENTITY,
  user_id     bigint NOT NULL,
  action      text NOT NULL,
  created_at  timestamptz NOT NULL,
  PRIMARY KEY (id, created_at)          -- must include the partition key
) PARTITION BY RANGE (created_at);

-- Index on the parent cascades to every partition
CREATE INDEX ON booking_logs (user_id, created_at);

-- Monthly partitions
CREATE TABLE booking_logs_2026_06 PARTITION OF booking_logs
  FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE booking_logs_2026_07 PARTITION OF booking_logs
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

-- Retire old data instantly (metadata-only, no row-by-row DELETE)
ALTER TABLE booking_logs DETACH PARTITION booking_logs_2026_06;
DROP TABLE booking_logs_2026_06;

-- Confirm pruning: only the July partition is scanned
EXPLAIN SELECT * FROM booking_logs
WHERE created_at >= '2026-07-01' AND created_at < '2026-08-01';

PostgreSQL implementa transacciones ACID con Control de Concurrencia Multiversión (MVCC). En vez de sobrescribir filas en el lugar, un UPDATE escribe una nueva versión de fila (tupla) y marca la vieja como muerta; cada transacción ve un snapshot consistente basado en IDs de transacción (xmin/xmax). La ventaja: los lectores nunca bloquean a los escritores ni viceversa. El costo: las tuplas muertas se acumulan y deben ser recuperadas por VACUUM (sección siguiente).

• Cómo funciona MVCC: la visibilidad se decide comparando el xmin/xmax de una tupla contra el snapshot de la transacción. No hay un segmento de rollback/undo separado como en otros motores -- las versiones viejas viven en la propia tabla hasta que VACUUM las elimina
• READ COMMITTED (por defecto): cada sentencia ve un snapshot fresco de datos committed. Sin dirty reads, pero dos lecturas en la misma transacción pueden diferir (non-repeatable reads). Correcto para la gran mayoría del trabajo OLTP
• REPEATABLE READ: toda la transacción ve un solo snapshot tomado en su primera sentencia. En PostgreSQL esto también previene phantom reads. Las escrituras concurrentes en conflicto lanzan un fallo de serialización (SQLSTATE 40001) que reintentas
• SERIALIZABLE: usa Serializable Snapshot Isolation (SSI) -- serializabilidad real sin locks de lectura pesados. Rastrea dependencias lectura/escritura y aborta una transacción de un ciclo peligroso. Tienes que estar listo para reintentar ante 40001
• Locking explícito: SELECT ... FOR UPDATE bloquea filas para un read-modify-write; agrega SKIP LOCKED para construir colas de jobs concurrentes, o NOWAIT para fallar rápido. Los advisory locks dan mutexes a nivel de app desacoplados de las filas
• Deadlocks y reintentos: PostgreSQL detecta deadlocks y aborta una transacción (SQLSTATE 40P01). Tanto los deadlocks como los fallos de serialización son normales bajo contención -- envuelve las transacciones en un loop de reintento (unos pocos intentos con backoff exponencial)

-- Inspect and set isolation
SHOW transaction_isolation;
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- Prevent overbooking with a locking read
BEGIN;
SELECT capacity, current_bookings
  FROM rooms WHERE id = 42
  FOR UPDATE;                         -- row locked until COMMIT/ROLLBACK

UPDATE rooms SET current_bookings = current_bookings + 1 WHERE id = 42;
INSERT INTO bookings (user_id, room_id, status)
  VALUES (7, 42, 'confirmed');
COMMIT;

-- Concurrent worker queue: each worker grabs distinct rows
SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY run_after
  FOR UPDATE SKIP LOCKED
  LIMIT 10;

-- Retry pattern (pseudocode) for 40001 / 40P01:
-- for attempt in range(5):
--   try: run_txn(); break
--   except (SerializationFailure, Deadlock): sleep(0.05 * 2**attempt)

Como MVCC deja tuplas muertas en cada UPDATE y DELETE, PostgreSQL debe recuperar ese espacio y mantener las estadísticas frescas. VACUUM hace la recuperación; el daemon autovacuum lo ejecuta automáticamente. Entenderlo y ajustarlo es la habilidad operativa más importante para una base de datos Postgres sana -- descuidarlo es la causa número uno de bloat descontrolado de tablas y caídas de rendimiento.

• Qué hace VACUUM: marca las tuplas muertas como reutilizables, actualiza el visibility map (que habilita los index-only scans) y avanza el horizonte de transaction-ID "congelado". El VACUUM simple corre online; VACUUM FULL reescribe la tabla y toma un lock exclusivo -- evítalo en tablas vivas
• ANALYZE: refresca las estadísticas del planner. Autovacuum también lo corre, pero después de cargas grandes de datos o migraciones ejecuta ANALYZE manualmente para que el planner no decida sobre números viejos
• Ajusta autovacuum por tabla: los defaults son conservadores para tablas grandes. Baja autovacuum_vacuum_scale_factor (p. ej. 0.02) en tablas calientes para que la limpieza se dispare antes, y sube autovacuum_vacuum_cost_limit para que aguante el ritmo. Configúralos por tabla con ALTER TABLE ... SET (...)
• Previene el wraparound de XID: un autovacuum de prevención de wraparound (anti-wraparound) es obligatorio y no se puede saltar. Vigila datfrozenxid/age(); una transacción abierta mucho tiempo o un replication slot atascado que retiene el horizonte puede empujarte a modo de solo lectura de emergencia. PostgreSQL 17 reescribió la memoria de VACUUM (TID store) para que use mucha menos RAM y rara vez necesite varias pasadas
• Mide el bloat: monitorea pg_stat_user_tables (n_dead_tup, last_autovacuum) y usa pgstattuple para cifras precisas. Recupera bloat online con pg_repack o VACUUM (FULL, ...) en una ventana de mantenimiento; deja margen de fillfactor en tablas con muchas actualizaciones para habilitar HOT updates

-- Manual maintenance (parallel index vacuuming available on big tables)
VACUUM (ANALYZE, VERBOSE) bookings;

-- Tune autovacuum for a hot, high-churn table
ALTER TABLE bookings SET (
  autovacuum_vacuum_scale_factor = 0.02,   -- vacuum after 2% dead tuples
  autovacuum_vacuum_cost_limit   = 2000,   -- let it do more work per round
  fillfactor                     = 90      -- leave room for HOT updates
);

-- Find the tables that need attention
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Watch transaction-ID age to stay clear of wraparound
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

JSONB

JSONB es el tipo JSON binario e indexable de PostgreSQL -- una de las características que permite que una sola instancia de Postgres sirva cargas relacionales y documentales. Valida al escribir, guarda una forma binaria descompuesta para acceso rápido a claves, soporta operadores potentes y puede indexarse con GIN. Úsalo cuando la forma de una fila realmente varía; mantén como columnas reales los campos que consultas y restringes seguido.

• jsonb vs json: jsonb se parsea una vez a binario (acceso rápido, soporta indexado, deduplica claves); json guarda el texto de entrada exacto y lo reparsea en cada lectura. Usa jsonb para casi todo
• Acceder valores: -> retorna jsonb, ->> retorna texto, #>/#>> toman un array de ruta, y el operador de ruta SQL/JSON @? / jsonb_path_query ejecuta expresiones JSONPath
• Contención e indexado: el operador de contención @> más un índice GIN (jsonb_path_ops) hace que metadata @> '{"plan":"pro"}' use índice. Indexa una sola clave caliente con un índice de expresión en su lugar
• Construir y transformar: jsonb_build_object, jsonb_agg, jsonb_set, jsonb_path_query, y (PG 17) SQL/JSON JSON_TABLE para expandir JSON en filas relacionales -- más constructores estándar como JSON_OBJECT/JSON_QUERY

-- JSONB column with a GIN index for containment queries
CREATE TABLE users (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email    text NOT NULL UNIQUE,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX idx_users_metadata ON users USING gin (metadata jsonb_path_ops);

INSERT INTO users (email, metadata) VALUES
('[email protected]',
 '{"plan":"pro","features":["analytics","exports"],"trial_ends":"2026-08-01"}');

-- Containment query (uses the GIN index)
SELECT email FROM users WHERE metadata @> '{"plan":"pro"}';

-- Extract a scalar as text
SELECT email, metadata->>'plan' AS plan FROM users;

-- Update a nested key immutably
UPDATE users SET metadata = jsonb_set(metadata, '{plan}', '"enterprise"')
WHERE id = 1;

-- Expand a JSON array into rows with JSON_TABLE (PG 17)
SELECT u.email, f.feature
FROM users u,
     JSON_TABLE(u.metadata, '$.features[*]'
       COLUMNS (feature text PATH '$')) AS f;

Funciones de Ventana

Las funciones de ventana calculan sobre un conjunto de filas relacionadas con la fila actual sin colapsarlas como hace GROUP BY. PostgreSQL tiene desde hace años una implementación completa y conforme al estándar -- incluyendo cláusulas de frame completas (ROWS/RANGE/GROUPS) y ventanas con nombre -- lo que la hace excelente para rankings, totales acumulados y analíticas comparativas.

• ROW_NUMBER(): un número secuencial único por partición. La forma limpia de hacer top-N por grupo, deduplicación y paginación estable
• RANK() / DENSE_RANK(): ranking con empates -- RANK deja huecos después de empates, DENSE_RANK no. PERCENT_RANK y NTILE segmentan la distribución
• LAG() / LEAD(): lee valores de filas anteriores/siguientes para deltas período-sobre-período y detección de huecos
• Agregados como ventanas: SUM() OVER (...), AVG() OVER (...) y una cláusula de frame dan totales acumulados y promedios móviles sin self-joins

-- Top 3 most active users per room (by booking count)
SELECT name, room_name, booking_count
FROM (
  SELECT u.name, r.name AS room_name,
         COUNT(b.id) AS booking_count,
         ROW_NUMBER() OVER (PARTITION BY r.id
                            ORDER BY COUNT(b.id) DESC) AS rn
  FROM bookings b
  JOIN users u ON u.id = b.user_id
  JOIN rooms r ON r.id = b.room_id
  WHERE b.created_at >= '2026-01-01'
  GROUP BY u.id, u.name, r.id, r.name
) ranked
WHERE rn <= 3;

-- Month-over-month growth with LAG over a monthly aggregate
SELECT month,
       bookings,
       LAG(bookings) OVER w AS prev_month,
       round((bookings - LAG(bookings) OVER w) * 100.0
             / NULLIF(LAG(bookings) OVER w, 0), 1) AS growth_pct
FROM (
  SELECT date_trunc('month', created_at) AS month, COUNT(*) AS bookings
  FROM bookings GROUP BY 1
) m
WINDOW w AS (ORDER BY month)
ORDER BY month;

Common Table Expressions (CTEs)

Los CTEs (la cláusula WITH) definen conjuntos de resultados temporales con nombre para una sola query. Hacen legible la lógica compleja, habilitan recursión para datos jerárquicos y -- algo crucial en Postgres -- soportan CTEs que modifican datos ejecutando INSERT/UPDATE/DELETE con RETURNING dentro de una sola sentencia atómica.

• CTEs no recursivos: bloques con nombre que aplanan subqueries anidadas. Desde PostgreSQL 12 el planner los inlinea por defecto cuando se referencian una vez y no tienen efectos secundarios -- agrega MATERIALIZED para forzar un cómputo único, o NOT MATERIALIZED para forzar el inlining
• CTEs recursivos: WITH RECURSIVE recorre árboles y grafos (organigramas, árboles de categorías, listas de materiales). Agrega una guarda de profundidad o UNION (no UNION ALL) para detener ciclos; PG 14+ soporta las cláusulas CYCLE y SEARCH
• CTEs que modifican datos: encadena escrituras en una sentencia -- p. ej. mover filas a un archivo y borrarlas atómicamente -- usando RETURNING para pasar resultados entre pasos

-- Readable multi-step reporting; force materialization once
WITH monthly AS MATERIALIZED (
  SELECT date_trunc('month', created_at) AS month, room_id, COUNT(*) AS total
  FROM bookings
  WHERE status = 'completed'
  GROUP BY 1, room_id
),
ranked AS (
  SELECT month, room_id, total,
         RANK() OVER (PARTITION BY month ORDER BY total DESC) AS rnk
  FROM monthly
)
SELECT month, room_id, total FROM ranked WHERE rnk <= 5;

-- Recursive CTE: category tree with indentation
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT repeat('  ', depth) || name AS tree_view
FROM category_tree ORDER BY depth, name;

-- Data-modifying CTE: archive and delete atomically
WITH moved AS (
  DELETE FROM bookings
  WHERE status = 'completed' AND created_at < '2025-01-01'
  RETURNING *
)
INSERT INTO bookings_archive SELECT * FROM moved;

Almacenar y Consultar Vectores

pgvector es la extensión que convierte a PostgreSQL en una base de datos vectorial de producción -- la columna vertebral de la búsqueda semántica y la Generación Aumentada por Recuperación (RAG). En vez de acoplar un sistema separado como Pinecone o Milvus, almacenas los embeddings junto a tus datos relacionales y combinas la búsqueda por similitud con filtros SQL, joins y transacciones normales en una sola query. A 2026 la versión actual es pgvector 0.8.x.

• Tipos: vector(n) almacena floats de precisión simple; halfvec(n) usa floats de 2 bytes para reducir a la mitad el almacenamiento y el tamaño del índice (e indexar hasta 4000 dims); bit(n) soporta binario/Hamming y sparsevec maneja vectores dispersos
• Operadores de distancia: <=> coseno, <-> L2/Euclídea, <#> producto interno negativo, <+> L1/taxicab. Haz coincidir el operador con cómo se entrenó tu modelo de embeddings (coseno para la mayoría de modelos de texto)
• Genera embeddings en tu pipeline: computa vectores con el modelo que elijas (p. ej. uno de OpenAI, Cohere, o un modelo local), luego insértalos. Las dimensiones deben coincidir con la columna: 1536 o 3072 para modelos comunes de OpenAI, 768/1024 para muchos modelos abiertos

-- Enable the extension and create an embeddings table
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text NOT NULL,
  content    text NOT NULL,
  source     text NOT NULL,
  embedding  vector(1536) NOT NULL,      -- match your model's dimension
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Insert a chunk plus its precomputed embedding
INSERT INTO documents (title, content, source, embedding)
VALUES ('PgBouncer basics', 'Transaction pooling ...', 'guide',
        '[0.0123, -0.0456, 0.0789, ...]');   -- 1536 floats from your model

-- k-NN semantic search by cosine distance
SELECT id, title, 1 - (embedding <=> $1) AS cosine_similarity
FROM documents
ORDER BY embedding <=> $1               -- $1 = the query embedding
LIMIT 10;

Indexado HNSW y Patrones de RAG

• Índices HNSW: la opción por defecto -- un índice de grafo con excelente recall/latencia y sin paso de entrenamiento. Ajusta la construcción con m y ef_construction; ajusta el recall en query con SET hnsw.ef_search. IVFFlat es más chico y rápido de construir pero debe construirse después de cargar datos y necesita un buen valor de lists
• Búsqueda filtrada e híbrida: combina la distancia vectorial con filtros WHERE normales (tenant, fecha, idioma). Los iterative index scans de pgvector 0.8 mantienen alto el recall incluso con filtros selectivos. Para búsqueda híbrida, mezcla la distancia semántica con puntajes de texto completo ts_rank o de trigramas -- todo en una sola sentencia SQL
• Escalado: usa halfvec para recortar la memoria del índice, cuantiza o reduce dimensiones cuando tu modelo lo permita, y considera la extensión pgvectorscale (StreamingDiskANN) para corpus muy grandes que exceden la RAM

-- HNSW index for cosine distance (pick the op class that matches your operator)
CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Raise recall at query time (per-session)
SET hnsw.ef_search = 100;

-- RAG retrieval: filter by tenant/recency, THEN rank by similarity
SELECT id, title, content
FROM documents
WHERE source = 'guide'
  AND created_at >= now() - interval '365 days'
ORDER BY embedding <=> $1
LIMIT 8;

-- Hybrid search: blend semantic similarity with keyword relevance
SELECT id, title,
       0.6 * (1 - (embedding <=> $1))
     + 0.4 * ts_rank(to_tsvector('english', content),
                     plainto_tsquery('english', $2)) AS score
FROM documents
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', $2)
ORDER BY score DESC
LIMIT 10;

El sistema de extensiones de PostgreSQL es su superpoder: un solo CREATE EXTENSION agrega nuevos tipos, métodos de índice, funciones e incluso background workers sin forkear el motor. Por eso una sola instancia de Postgres puede cubrir cargas relacionales, geoespaciales, de series temporales, vectoriales y de texto completo que de otro modo necesitarían varias bases de datos especializadas.

• PostGIS: la extensión geoespacial de referencia. Agrega tipos geometry/geography, miles de funciones espaciales (ST_DWithin, ST_Intersects, ST_Distance) e índices espaciales GiST/SP-GiST. Potencia nativamente las queries de "encontrar todo dentro de N km" -- la serie actual es PostGIS 3.5
• TimescaleDB: convierte a Postgres en una base de datos de series temporales vía hypertables (auto-particionamiento transparente por tiempo), compresión columnar nativa, agregados continuos (rollups mantenidos incrementalmente) y políticas de retención -- ideal para métricas, IoT y eventos
• pgvector y compañía: búsqueda vectorial (cubierta arriba), más pgvectorscale para ANN a escala de miles de millones y pg_search/ParadeDB para texto completo BM25 -- los bloques de construcción de funciones de IA dentro de tu base de datos principal
• Esenciales incluidas: pg_stat_statements (telemetría de queries), pg_trgm (búsqueda difusa/de substring), citext (texto insensible a mayúsculas), uuid-ossp, hstore, postgres_fdw (consultar bases de datos remotas) y pgcrypto vienen con el core
• Add-ons operativos: pg_partman automatiza el ciclo de vida de las particiones, pg_cron corre jobs SQL programados dentro de la base de datos, y pg_repack elimina bloat online sin locks largos
• Salvedad de disponibilidad: algunas extensiones deben precargarse vía shared_preload_libraries y un reinicio, y las plataformas gestionadas (RDS, Cloud SQL, Azure) permiten solo una lista curada -- verifica el soporte antes de diseñar en torno a una. Las extensiones neutrales al proveedor te mantienen portable

-- PostGIS: geospatial "what's nearby" query
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE gyms (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text NOT NULL,
  geom     geography(Point, 4326) NOT NULL   -- lon/lat on the WGS84 sphere
);
CREATE INDEX idx_gyms_geom ON gyms USING gist (geom);

-- All gyms within 5 km of a point, nearest first
SELECT name, ST_Distance(geom, ST_MakePoint(-74.07, 4.65)::geography) AS meters
FROM gyms
WHERE ST_DWithin(geom, ST_MakePoint(-74.07, 4.65)::geography, 5000)
ORDER BY meters
LIMIT 20;

-- TimescaleDB: hypertable + continuous aggregate for metrics
CREATE EXTENSION IF NOT EXISTS timescaledb;

CREATE TABLE metrics (
  ts       timestamptz NOT NULL,
  device   text NOT NULL,
  value    double precision NOT NULL
);
SELECT create_hypertable('metrics', 'ts');   -- auto time-partitioning

-- Incrementally-maintained hourly rollup
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', ts) AS bucket,
       device,
       avg(value) AS avg_value,
       max(value) AS max_value
FROM metrics
GROUP BY bucket, device;

-- Compress and retain
ALTER TABLE metrics SET (timescaledb.compress, timescaledb.compress_segmentby = 'device');
SELECT add_retention_policy('metrics', INTERVAL '180 days');

Corro PostgreSQL en producción en sistemas personales y de clientes -- desde un primario gestionado con réplicas de lectura hasta instancias autohospedadas que potencian funciones de IA. El tema recurrente: Postgres escala más de lo que la gente espera cuando respetas MVCC, mantienes autovacuum sano y haces pooling de conexiones como corresponde.

• Postgres como única fuente de verdad: primario más réplicas de lectura streaming, WAL archivado para recuperación point-in-time, y pgBackRest para backups comprimidos y verificados. La replicación lógica manejó un upgrade de versión mayor con downtime mínimo
• Connection pooling: puse PgBouncer en modo transaction delante del primario. Miles de conexiones de cliente colapsaron sobre un pool de servidor pequeño, y los incidentes de "too many connections" bajaron a cero
• Optimización de queries: usé pg_stat_statements y EXPLAIN (ANALYZE, BUFFERS) para encontrar las peores queries, agregué índices cubrientes y parciales, y corregí patrones N+1 con lateral joins -- recortando drásticamente la latencia P95
• Disciplina de VACUUM: ajusté autovacuum por tabla en las tablas calientes y alerté sobre el ratio de tuplas muertas y la edad de transaction-ID. Esto eliminó el lento deterioro de latencia por bloat que se había confundido con "la base de datos envejeciendo"
• pgvector para RAG: construye la recuperación para funciones de IA directamente en Postgres con pgvector e índices HNSW -- embeddings, filtros de metadata y joins de negocio en una sola query, sin una base de datos vectorial separada que operar

Modelo de Versiones y Releases

PostgreSQL lanza una versión mayor por año (típicamente a fines de septiembre) y soporta cada una durante cinco años. A mediados de 2026, PostgreSQL 18 (lanzado en septiembre de 2025) es el release estable actual y PostgreSQL 17 (septiembre de 2024) es la mayor previa ampliamente desplegada. No hay un "LTS" separado -- toda versión mayor es apta para producción y recibe releases menores trimestrales con correcciones de bugs y seguridad.

• Elige una mayor reciente: corre PostgreSQL 17 o 18 para despliegues nuevos. Ambas reciben releases menores regulares; aplica siempre el último menor (p. ej. 18.x) pronto, ya que los menores son solo parches y seguros de adoptar
• Ventana de soporte de cinco años: cada mayor se soporta unos cinco años desde su lanzamiento. Planifica un upgrade mayor antes de que tu versión llegue al fin de vida para seguir recibiendo parches de seguridad
• Rutas de upgrade: usa pg_upgrade para upgrades mayores in-place rápidos, o replicación lógica (pg_createsubscriber en PG 17 lo facilita) para upgrades con downtime casi nulo. Prueba siempre la compatibilidad de extensiones primero

PostgreSQL vs MySQL

Ambas son bases de datos relacionales maduras, ACID y open-source, y para CRUD simple cualquiera sirve. PostgreSQL se adelanta cuando necesitas tipos ricos y extensibilidad: JSONB nativo con indexado GIN, arrays, rangos con constraints de exclusión, un ecosistema profundo de extensiones (pgvector, PostGIS, TimescaleDB), índices parciales/de expresión reales, y múltiples métodos de índice (GIN/GiST/BRIN). El InnoDB de MySQL clusteriza la tabla por la primary key (lookups de PK rápidos, lecturas más baratas sin índices secundarios) y tiene una enorme presencia en hosting gestionado. El resumen honesto: elige PostgreSQL cuando importan la corrección, las queries complejas y las funciones de IA/analítica; MySQL es un default razonable para apps web sencillas con muchas lecturas ya invertidas en su ecosistema.

-- A one-statement capability that showcases Postgres's type system:
-- prevent overlapping room reservations at the DB level (MySQL cannot express this)
CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
  WHERE (status = 'confirmed');

-- Concurrency model differs too:
--  * PostgreSQL default isolation = READ COMMITTED; MVCC keeps old row versions
--    in the heap (reclaimed by VACUUM).
--  * MySQL/InnoDB default isolation = REPEATABLE READ; old versions live in the
--    undo log. Postgres SERIALIZABLE uses SSI (no read locks).

Novedades de PostgreSQL 17 (Septiembre 2024)

PostgreSQL 17 se enfocó en escala operativa y SQL/JSON. Agregó base backups incrementales nativos (pg_basebackup --incremental con pg_combinebackup), un gestor de memoria de VACUUM reescrito (un TID store que usa mucha menos RAM), I/O en streaming para scans secuenciales más rápidos, y mejoras mayores en replicación lógica incluyendo failover slots y pg_createsubscriber. En el lado SQL trajo la función estándar JSON_TABLE, más capacidades de MERGE (incluyendo RETURNING y WHEN NOT MATCHED BY SOURCE), y COPY ... ON_ERROR ignore para cargas masivas resilientes.

-- PG 17: MERGE with RETURNING and match-source awareness
MERGE INTO inventory AS t
USING shipment AS s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET qty = 0
RETURNING merge_action(), t.sku, t.qty;

-- PG 17: tolerate bad rows during a bulk COPY instead of aborting
COPY events FROM '/data/events.csv' WITH (FORMAT csv, HEADER, ON_ERROR ignore);

PostgreSQL 18 (Septiembre 2025)

PostgreSQL 18 es la versión mayor estable actual en 2026. Su característica estrella es un nuevo subsistema de I/O asíncrono (AIO) que puede acelerar sustancialmente las lecturas (scans secuenciales, bitmap heap scans y VACUUM) en almacenamiento en la nube. También agrega generación nativa de UUIDv7 (uuidv7()) para claves ordenadas por tiempo, columnas generadas virtuales como nuevo default, skip scan de B-tree para índices multicolumna, constraints temporales PRIMARY KEY/UNIQUE ... WITHOUT OVERLAPS, acceso con RETURNING tanto a las filas OLD como NEW en DML, y autenticación basada en OAuth.

La historia práctica del upgrade sigue mejorando: pg_upgrade ahora preserva las estadísticas del planner, así que un upgrade mayor ya no arranca con un optimizador ciego y lento mientras re-ejecutas ANALYZE. Combinado con el subsistema AIO y los skip scans, PostgreSQL 18 es un salto de rendimiento significativo para cargas limitadas por I/O y analíticas -- y, como siempre, el vasto ecosistema de extensiones (pgvector, PostGIS, TimescaleDB) sigue de cerca cada nueva mayor.