PostgreSQL

¿Qué es PostgreSQL?

PostgreSQL es una base de datos relacional de código abierto con fama de hacer las cosas básicas correctamente. Almacena los datos en tablas, aplica reglas mediante constraints, envuelve los cambios en transacciones reales y expone todo esto a través de SQL estándar. Se ha desarrollado continuamente desde la década de 1980 y está gobernada por una amplia comunidad en lugar de una sola empresa.

Esa longevidad se refleja en los detalles. Postgres tiene el sistema de tipos más rico de cualquier base de datos convencional, un mecanismo de extensiones que permite a terceros añadir capacidades completamente nuevas y un planificador de consultas que maneja desde una búsqueda puntual simple hasta una consulta analítica con window functions. Es la base de datos relacional predeterminada para nuevas aplicaciones en la mayoría de las empresas y está disponible como servicio gestionado en casi cualquier plataforma.

Si vas a elegir una sola base de datos para aprender a fondo, esta es la que más recompensa el tiempo invertido.

Por qué los equipos eligen Postgres

Tres propiedades explican la mayor parte de su popularidad.

Es correcto por defecto. Las restricciones se aplican en el motor, no en el código de la aplicación. Un CHECK no puede ser omitido por un servicio con errores, un FOREIGN KEY no puede ser ignorado por un proceso batch, y un índice UNIQUE no puede sufrir condiciones de carrera entre dos solicitudes concurrentes. La integridad de los datos se convierte en una propiedad del esquema.

Es extensible. En lugar de integrar cada funcionalidad en el núcleo, Postgres expone hooks para nuevos tipos, operadores, métodos de indexación y lenguajes procedimentales. Así es como PostGIS, pgvector, TimescaleDB y docenas de otros proyectos existen como extensiones en lugar de forks.

Habla SQL estándar. Las habilidades y las consultas son transferibles entre bases de datos, ORMs y herramientas. No tienes que aprender un dialecto propietario para empezar.

El modelo relacional, en breve

Una base de datos relacional almacena los datos en tablas compuestas por filas y columnas. Cada tabla tiene una primary key que identifica de forma única una fila, y las relaciones se expresan mediante foreign keys que apuntan a otras tablas. El objetivo es almacenar cada dato una sola vez y permitir que los joins lo reensamblen.

Consideremos los usuarios y los pedidos. En lugar de repetir el email de un cliente en cada pedido, almacenas el email una sola vez en users y haces referencia al usuario desde orders mediante user_id. Esto es la normalización, y evita la clásica anomalía en la que se actualiza una fila mientras que otras mil siguen conservando el valor antiguo.

La normalización no es una religión. La tercera forma normal es el estándar sensato, y la desnormalización deliberada —un order_count en caché, una vista materializada— es una decisión de rendimiento que tomas más adelante, basándote en mediciones reales.

Tipos de datos que realmente valen la pena

Postgres ofrece más tipos de los que la mayoría de los proyectos necesitan, pero hay un puñado que son fundamentales:

• text — cadena de longitud variable sin límite arbitrario. Prefiérelo sobre varchar(n) a menos que el límite sea una regla de negocio real.
• integer y bigint — números enteros. Usa bigint para cualquier cosa que pueda crecer sin límite, como los IDs de una secuencia.
• numeric(p, s) — aritmética decimal exacta. Este es el tipo correcto para dinero, no real ni double precision.
• timestamptz — una marca de tiempo almacenada en UTC con conciencia de la zona horaria. Prefiérelo siempre sobre timestamp para los datos de la aplicación.
• uuid — un identificador de 128 bits, útil cuando los clientes generan IDs o cuando no quieres filtrar el conteo de registros.
• jsonb — JSON binario que puedes indexar y consultar.
• Arrays y tipos compuestos — text[], integer[] y tipos de fila personalizados, útiles para etiquetas y listas ordenadas pequeñas.

Vale la pena interiorizar el ejemplo del dinero: 0.1 + 0.2 no es 0.3 en punto flotante binario. Almacena los montos como unidades menores enteras (total_cents) o como numeric, nunca como float.

Creación de tablas y restricciones

Un esquema es un contrato. Cada restricción que declaras es una clase de error que evitas enviar a producción.

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text NOT NULL CHECK (length(name) > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now(),
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb
);

Las decisiones importantes aquí son deliberadas. NOT NULL descarta toda una familia de errores por falta de datos. UNIQUE en email impone el invariante en la base de datos, que es el único lugar donde una condición de carrera no puede anularlo. La CHECK en status documenta e impone la máquina de estados. ON DELETE CASCADE decide qué sucede cuando un usuario desaparece, en lugar de dejar registros huérfanos.

Es preferible añadir las restricciones en la misma migración que crea la tabla. Añadir una NOT NULL o una clave foránea más tarde implica tener que limpiar primero cualquier fila inválida que se haya acumulado mientras la regla no existía.

Consultas con joins

La mayoría de las consultas reales combinan tablas. Un join empareja filas de dos relaciones basándose en una condición, y la elección del tipo de join decide qué sucede con las filas que no tienen coincidencia.

SELECT u.email,
       o.id,
       o.total_cents
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

JOIN (o INNER JOIN) mantiene únicamente los pares que coinciden. LEFT JOIN mantiene todas las filas de la izquierda y rellena la derecha con NULL cuando no hay coincidencia; es la forma estándar de preguntar “qué usuarios no tienen pedidos”:

SELECT u.email, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
HAVING count(o.id) = 0;

Los agregados como count, sum, avg, min y max colapsan grupos de filas en una sola. Cada columna en el SELECT que no esté dentro de un agregado debe aparecer en el GROUP BY. La cláusula WHERE filtra las filas antes de agrupar; HAVING filtra los grupos después. Confundirlas es una fuente común de resultados confusos.

Índices y EXPLAIN ANALYZE

Un índice es una estructura ordenada que permite al planificador encontrar filas sin tener que escanear toda la tabla. El valor predeterminado es un B-tree, que sirve para predicados de igualdad y rango, y soporta ORDER BY directamente.

CREATE INDEX orders_user_created_idx
  ON orders (user_id, created_at DESC);

Un índice compuesto como este cubre consultas que filtran por user_id y ordenan por created_at. El orden de las columnas es fundamental: la columna situada más a la izquierda debe aparecer en la consulta para que el índice sea útil al filtrar. Esta es la regla del prefijo izquierdo (leftmost-prefix rule), y es la razón por la cual el diseño de índices comienza basándose en las consultas y no en las columnas.

Otros tipos de índices cubren diferentes estructuras:

• GIN — índices invertidos para jsonb, arrays y búsqueda de texto completo (tsvector).
• GiST — geometría, rangos y búsqueda del vecino más cercano, utilizado intensamente por PostGIS.
• BRIN — índices diminutos sobre datos ordenados naturalmente, como tablas de timestamps de solo inserción (append-only).
• Hash — búsquedas únicamente de igualdad; rara vez son necesarios ya que el B-tree se encarga de ellas.

Nunca adivines si se está utilizando un índice. Pregúntale al planificador:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

La salida muestra el árbol del plan, el coste estimado y —con ANALYZE— las filas y el tiempo reales.

Limit  (cost=0.43..12.94 rows=10 width=48)
       (actual time=0.031..0.079 rows=10 loops=1)
  ->  Index Scan Backward using orders_user_created_idx on orders
        (cost=0.43..521.10 rows=417 width=48)
        (actual time=0.029..0.070 rows=10 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.184 ms
Execution Time: 0.108 ms

Léelo de adentro hacia afuera. Aquí, el Index Scan con Index Cond: (user_id = 42) confirma que el índice compuesto está cumpliendo su función. Busca un Seq Scan en una tabla grande donde esperabas un Index Scan, y busca una brecha considerable entre las filas estimadas y las reales, lo que generalmente indica estadísticas obsoletas. Ejecuta ANALYZE orders; para actualizarlas, y EXPLAIN (ANALYZE, BUFFERS) para ver cuántos datos se leyeron desde la caché frente al disco.

Transacciones y MVCC

Una transacción agrupa sentencias para que todas surtan efecto o ninguna lo haga. Postgres implementa las transacciones mediante MVCC: en lugar de bloquear filas para los lectores, mantiene múltiples versiones y asigna una instantánea (snapshot) a cada transacción.

BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 5000 WHERE id = 1;
UPDATE accounts SET balance_cents = balance_cents + 5000 WHERE id = 2;
COMMIT;

Si algo falla, ROLLBACK deshace la transacción completa. El código de la aplicación debe envolver las escrituras de varios pasos en una transacción y estar preparado para reintentar la operación en caso de fallos de serialización cuando se utilicen los niveles de aislamiento más estrictos.

Los niveles de aislamiento deciden qué puede observar una transacción:

• Read Committed (por defecto) — cada sentencia ve una instantánea actualizada; es adecuado para la mayoría de las cargas de trabajo.
• Repeatable Read — toda la transacción ve una única instantánea; es útil cuando se leen las mismas filas repetidamente.
• Serializable — las transacciones se comportan como si se ejecutaran una tras otra; es la garantía más fuerte y la que tiene más probabilidades de requerir reintentos.

MVCC tiene un coste: las filas actualizadas y eliminadas dejan atrás tuplas muertas. Autovacuum las recupera en segundo plano. Las transacciones de larga duración retrasan la limpieza e inflan las tablas, por lo que es recomendable mantener las transacciones cortas y monitorizar el vacuum en tablas con mucha rotación de datos.

Connection pooling con PgBouncer

Postgres gestiona cada conexión de cliente mediante un proceso independiente del sistema operativo. Esto es robusto, pero significa que las conexiones son más pesadas que en MySQL, y unos pocos cientos de clientes inactivos pueden consumir una cantidad considerable de memoria. El límite práctico es max_connections, y superarlo produce errores en lugar de una degradación gradual.

La solución es un connection pooler. PgBouncer se sitúa entre la aplicación y Postgres, mantiene un pool pequeño de conexiones reales y multiplexa múltiples conexiones de clientes sobre ellas. Ofrece tres modos:

• Session pooling — se mantiene una conexión al servidor durante toda la sesión del cliente.
• Transaction pooling — se mantiene una conexión al servidor solo durante una transacción; es la opción más común para aplicaciones web.
• Statement pooling — se mantiene una conexión para una sola sentencia; es el modo más agresivo y restrictivo.

El transaction pooling rompe las funcionalidades con alcance de sesión, como SET, los advisory locks mantenidos entre sentencias y LISTEN. Utiliza SET LOCAL dentro de las transacciones y verifica que tu ORM se comporte correctamente en modo de transacción.

Independientemente de lo que elijas, implementa también un pool en el lado de la aplicación. Un pool por proceso con un máximo razonable, sumado a un pooler delante, es la arquitectura estándar. Nunca abras una conexión nueva por cada solicitud.

JSONB: cuándo usarlo

jsonb almacena JSON en un formato binario descompuesto que admite indexación y un conjunto rico de operadores. Es genuinamente útil, pero también es fácil abusar de él.

SELECT id, metadata->>'coupon' AS coupon
FROM orders
WHERE metadata @> '{"channel": "mobile"}';

El operador de contención @> puede utilizar un índice GIN:

CREATE INDEX orders_metadata_idx ON orders USING gin (metadata);

Usa JSONB para datos cuya estructura sea realmente variable: payloads de webhooks, respuestas de terceros, atributos definidos por el usuario o feature flags. No lo uses para evitar escribir una migración. Los campos que filtres, unas (join), restrinjas o agregues deben ser columnas con tipos reales. La prueba es sencilla: si te encuentras convirtiendo metadata->>'price' a un número en la mayoría de las consultas, debería haber sido un integer.

Un camino intermedio es el híbrido: campos estables como columnas y todo lo opcional en una bolsa attributes de JSONB. Esto te brinda restricciones e índices donde importan y flexibilidad donde no.

Extensiones que amplían las posibilidades

CREATE EXTENSION instala un módulo empaquetado en una base de datos. Hay algunas que vale la pena conocer por su nombre:

• pg_stat_statements — registra el texto normalizado de las consultas junto con los tiempos y E/S; es lo primero que se debe habilitar al investigar el rendimiento.
• PostGIS — tipos y funciones geográficas; la razón por la cual muchos equipos eligen Postgres para datos de ubicación.
• pgvector — columnas de vectores e índices de vecinos más cercanos aproximados para embeddings y búsqueda semántica.
• pgcrypto — funciones criptográficas como gen_random_uuid() en versiones antiguas.
• pg_trgm — índices de trigramas para LIKE rápido y coincidencia difusa (fuzzy matching).

Habilita solo lo que utilices y recuerda que los proveedores gestionados a menudo requieren una configuración o una solicitud de soporte antes de que se pueda instalar una extensión.

Roles y permisos

Postgres separa los roles (que pueden iniciar sesión o poseer objetos) de los privileges (lo que un rol puede hacer). Otorga el privilegio mínimo necesario para que funcione.

CREATE ROLE app_user LOGIN PASSWORD 'secret';

GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

La línea ALTER DEFAULT PRIVILEGES es importante porque se aplica a las tablas creadas posteriormente, no solo a las existentes. Evita conectarte desde la aplicación como el propietario de la base de datos o como un superusuario; una aplicación comprometida no debería poder DROP TABLE. Para las migraciones, utiliza un rol independiente con derechos DDL.

Backups y recuperación

Existen dos tipos de backups. Los backups lógicos utilizan pg_dump para generar un script portable o un archivo de una base de datos, y pg_dumpall para capturar roles y globales. Son sencillos y tolerantes a las versiones, pero más lentos de restaurar a gran escala.

pg_dump --format=custom --file=shop.dump shop
pg_restore --dbname=shop_restore shop.dump

Los backups físicos copian el directorio de datos y el write-ahead log. pg_basebackup junto con el archivado continuo de WAL permite la recuperación en un punto específico en el tiempo (point-in-time recovery), lo que te permite restaurar la base de datos a un momento exacto antes de una migración fallida. Este es el enfoque que utilizan los proveedores gestionados.

Independientemente de cuál elijas, la regla es la misma: automatízalo, almacénalo fuera del host principal y restáuralo regularmente en una base de datos de prueba. Un backup que nunca has restaurado es una esperanza, no un plan.

Búsqueda de texto completo sin servicios adicionales

Postgres tiene búsqueda de texto completo integrada, lo cual suele ser suficiente para evitar la ejecución de un clúster de búsqueda independiente. Funciona convirtiendo el texto en un tsvector de lexemas y las consultas en un tsquery, para luego compararlos.

ALTER TABLE posts
  ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED;

CREATE INDEX posts_search_idx ON posts USING gin (search);

SELECT id, title
FROM posts
WHERE search @@ plainto_tsquery('english', 'connection pooling')
ORDER BY ts_rank(search, plainto_tsquery('english', 'connection pooling')) DESC
LIMIT 20;

Una columna generada mantiene el vector sincronizado automáticamente, y el índice GIN hace que la coincidencia del @@ sea rápida. plainto_tsquery convierte de forma segura la entrada del usuario en una consulta, mientras que ts_rank ordena por relevancia. Puedes añadir ts_headline para resaltar las coincidencias en los resultados.

Recurre a un motor de búsqueda dedicado únicamente cuando necesites faceting, tolerancia a errores tipográficos (fuzzy search) a gran escala o ajuste de relevancia entre documentos. Para la mayoría de las aplicaciones, la versión integrada elimina una pieza móvil completa de la arquitectura.

Vistas y vistas materializadas

Una vista es una consulta almacenada que se comporta como una tabla. No almacena datos; es una forma nombrada de encapsular una estructura común y mantener los permisos consistentes.

CREATE VIEW paid_orders AS
SELECT id, user_id, total_cents, created_at
FROM orders
WHERE status = 'paid';

SELECT * FROM paid_orders WHERE created_at >= now() - interval '7 days';

Una vista materializada sí almacena el resultado, lo que hace que las agregaciones costosas sean económicas de leer, a cambio de que los datos puedan quedar obsoletos.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       sum(total_cents)             AS revenue_cents
FROM orders
WHERE status = 'paid'
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CONCURRENTLY se actualiza sin bloquear a los lectores, pero requiere un índice único en la vista. Programa las actualizaciones según tu tolerancia a los datos obsoletos y recuerda que una vista materializada es una caché: siempre puede reconstruirse a partir de las tablas base.

Cambiar esquemas de forma segura

En una base de datos en producción, el DDL adquiere bloqueos. El objetivo es evitar bloqueos ACCESS EXCLUSIVE prolongados que impidan las lecturas y escrituras mientras se reescribe una tabla.

ALTER TABLE orders ADD COLUMN channel text;
ALTER TABLE orders ALTER COLUMN channel SET DEFAULT 'web';

CREATE INDEX CONCURRENTLY orders_channel_idx ON orders (channel);

Añadir una columna nullable sin valor predeterminado es instantáneo en las versiones modernas de Postgres, y establecer un valor predeterminado solo afecta a los metadatos. CREATE INDEX CONCURRENTLY construye el índice sin mantener un bloqueo de escritura, aunque no puede ejecutarse dentro de una transacción y puede fallar, dejando un índice inválido que debe eliminarse y reintentarse. Añadir una restricción NOT NULL a una tabla grande debe hacerse por pasos: añadir una CHECK que se valide por separado y, posteriormente, convertirla.

Versiona cada cambio como una migración para que todos los entornos alcancen el mismo esquema en el mismo orden. Nunca edites una tabla de producción manualmente; olvidarás lo que hiciste.

Carga masiva con COPY

Insertar filas una a una es la forma más lenta de cargar datos. Postgres cuenta con COPY, que transmite los datos en un solo comando y puede ser un orden de magnitud más rápido que un bucle de INSERTs.

COPY orders (user_id, status, total_cents, created_at)
FROM '/data/orders.csv'
WITH (FORMAT csv, HEADER true);

COPY (SELECT id, email FROM users WHERE created_at > now() - interval '30 days')
TO '/tmp/recent-users.csv'
WITH (FORMAT csv, HEADER true);

COPY se ejecuta en el servidor, por lo que el archivo debe ser legible por el proceso de la base de datos; utiliza \copy en psql para leer desde el cliente. Para el código de la aplicación, la API copy del driver transmite las filas sin necesidad de preparar un archivo. Envuelve la carga en una transacción cuando necesites que sea una operación atómica (todo o nada), y elimina o reconstruye los índices después de cargas muy grandes, ya que mantener los índices durante una inserción masiva es el costo principal.

Las inserciones por lotes (batch inserts) son un punto medio más sencillo cuando COPY no es práctico:

INSERT INTO orders (user_id, status, total_cents)
VALUES (1, 'paid', 1999),
       (2, 'paid', 4599),
       (3, 'pending', 999);

Mejores prácticas

• Elige text, bigint, numeric, timestamptz y jsonb deliberadamente en lugar de usar varchar y floats por defecto.
• Declara NOT NULL, UNIQUE, CHECK y foreign keys en la misma migración que crea la tabla.
• Diseña los índices basándote en consultas reales y confírmalos con EXPLAIN ANALYZE.
• Mantén las transacciones cortas y elige el nivel de aislamiento más débil que siga siendo correcto.
• Coloca un pooler como PgBouncer frente a la base de datos y utiliza pooling también en la aplicación.
• Usa JSONB para datos genuinamente variables, no como una forma de saltarte el diseño del esquema.
• Otorga el privilegio mínimo a los roles de la aplicación y mantén el DDL en un rol de migración.
• Habilita pg_stat_statements, monitorea el autovacuum y restaura un backup de forma programada.
• Versiona cada cambio de esquema como una migración para que los entornos se mantengan sincronizados.

Errores comunes

• Almacenar dinero en double precision y perder centavos debido al redondeo.
• Usar timestamp en lugar de timestamptz y almacenar horas locales por accidente.
• Olvidar WHERE en un UPDATE o DELETE y afectar cada fila de la tabla.
• Crear un índice por columna en lugar de índices compuestos que coincidan con la consulta.
• Abrir una nueva conexión por solicitud y agotar max_connections.
• Mantener una transacción abierta durante una llamada HTTP o una interacción del usuario.
• Tratar NULL como si fuera igual a NULL; las comparaciones requieren IS NULL y IS NOT NULL.
• Asumir que se está utilizando un índice sin verificar el plan de ejecución de la consulta.
• Ejecutar consultas de la aplicación como propietario de la base de datos o superusuario.

Próximos pasos

Postgres premia la profundidad, y el siguiente paso natural es dominar el lenguaje que utiliza: lee la guía de SQL para perfeccionar los joins, las CTE y las window functions. Si estás evaluando alguna alternativa, MySQL cubre la otra base de datos relacional de código abierto dominante, MongoDB explica el modelo de documentos y Redis muestra la capa de caché que suele situarse delante de un almacenamiento relacional.