PostgreSQL: el gigante open-source

Guía completa de PostgreSQL: instalación, tipos de datos exclusivos, JSONB, full-text search, y rendimiento con EXPLAIN ANALYZE.

Introducción

PostgreSQL (conocido simplemente como Postgres) es el sistema gestor de bases de datos relacional open-source más avanzado del mundo. Nacido en la Universidad de California en Berkeley en 1986 como sucesor del proyecto Ingres, Postgres ha evolucionado durante casi cuatro décadas hasta convertirse en un motor de bases de datos con características de clase empresarial.

Entre sus capacidades más destacadas se encuentran: control de concurrencia MVCC (Multi-Version Concurrency Control), point-in-time recovery, tablespaces, replicación nativa síncrona y asíncrona, y un extenso ecosistema de extensiones como PostGIS (bases de datos geoespaciales) y TimescaleDB (series temporales). Es la base de datos detrás de proyectos como Instagram, Apple iCloud, y Reddit.

Instalación

PostgreSQL está disponible en todos los sistemas operativos modernos. Estas son las formas más comunes de instalarlo:

macOS (Homebrew)

brew install postgresql@16
brew services start postgresql@16

Docker

Ideal para desarrollo y entornos aislados:

docker run --name postgres \
  -e POSTGRES_PASSWORD=root \
  -p 5432:5432 \
  -d postgres:16

Verificar la instalación

psql --version
# psql (PostgreSQL) 16.2

Primeros comandos psql

Conéctate con la herramienta de línea de comandos psql:

psql -U postgres

CREATE DATABASE mi_proyecto;
\c mi_proyecto  -- conectar a la base de datos

CREATE USER dev WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE mi_proyecto TO dev;

\dt  -- listar tablas
\d nombre_tabla  -- describir tabla
\du -- listar usuarios
\l  -- listar bases de datos
\?  -- ayuda de comandos psql

Tipos de datos exclusivos de PostgreSQL

PostgreSQL ofrece tipos de datos que no encontrarás en otros SGBD relacionales. Aquí los más importantes:

• SERIAL — auto-increment nativo (equivalentes: SMALLSERIAL, BIGSERIAL). En MySQL se usa AUTO_INCREMENT.
• UUID — identificadores universales. Requiere la extensión uuid-ossp o pgcrypto.
• JSONB — JSON binario, indexable con GIN. Permite consultas eficientes sobre campos internos del JSON.
• ARRAY — soporte nativo de arrays multidimensionales: TEXT[], INT[][], etc.
• ENUM — tipos enumerados creados con CREATE TYPE.
• CITEXT — texto case-insensitive (requiere extensión citext).
• TSVECTOR / TSQUERY — búsqueda de texto completo nativa, con soporte para stemming y ranking.
• INTERVAL — intervalos de tiempo (ej: INTERVAL '3 days').
• INET / CIDR / MACADDR — tipos de red.
• Geométricos — point, line, circle, polygon, etc.

CREATE TABLE avanzada

PostgreSQL permite crear tablas con funcionalidades que van mucho más allá del estándar SQL:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "citext";

CREATE TABLE usuarios (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  nombre CITEXT NOT NULL,
  email CITEXT UNIQUE NOT NULL,
  roles TEXT[] DEFAULT '{}',
  metadata JSONB DEFAULT '{}',
  creado_en TIMESTAMPTZ DEFAULT NOW()
);

-- Índices avanzados
CREATE INDEX idx_usuarios_email ON usuarios (email);
CREATE INDEX idx_usuarios_roles ON usuarios USING GIN (roles);
CREATE INDEX idx_usuarios_metadata ON usuarios USING GIN (metadata);

uuid_generate_v4() genera UUIDs aleatorios. CITEXT permite búsquedas case-insensitive sin necesidad de LOWER(). Los índices GIN sobreJSONB y TEXT[] habilitan consultas eficientes sobre datos semiestructurados.

JSONB en PostgreSQL

JSONB es una de las características estrella de PostgreSQL. A diferencia de almacenar JSON como texto, JSONB almacena los datos en formato binario descompuesto, lo que permite indexación y consultas eficientes sobre campos internos:

INSERT INTO usuarios (nombre, email, metadata) VALUES
  ('Ana', 'ana@email.com',
   '{"theme": "dark", "notifications": true, "lang": "es"}'),
  ('Luis', 'luis@email.com',
   '{"theme": "light", "notifications": false, "lang": "en"}');

-- Consultar campos internos
SELECT nombre, metadata->>'theme' AS tema
FROM usuarios
WHERE metadata @> '{"notifications": true}';

Operadores clave para JSONB:

• -> — accede como JSON (mantiene tipo)
• ->> — accede como texto
• @> — contiene (el operador más potente para filtrar documentos JSONB)
• ?| — existe alguna de las claves
• ?& — existen todas las claves

Gracias a los índices GIN, estas consultas sobre JSONB son igual de rápidas que las consultas sobre columnas tradicionales, incluso en tablas con millones de registros.

Full-text search nativo

PostgreSQL incluye un potente motor de búsqueda de texto completo sin necesidad de Elasticsearch ni motores externos:

CREATE TABLE articulos (
  id SERIAL PRIMARY KEY,
  titulo TEXT NOT NULL,
  contenido TEXT NOT NULL,
  busqueda TSVECTOR
    GENERATED ALWAYS AS (
      to_tsvector('spanish', titulo || ' ' || contenido)
    ) STORED
);

CREATE INDEX idx_busqueda ON articulos USING GIN (busqueda);

-- Buscar artículos que contengan "postgresql" Y "tutorial"
SELECT titulo
FROM articulos
WHERE busqueda @@ to_tsquery('spanish', 'postgresql & tutorial');

Características destacadas del full-text search en PostgreSQL:

• Stemming — reduce palabras a su raíz ("corriendo", "corrí", "correr" → "corr").
• Ranking — ordena resultados por relevancia con ts_rank().
• Diccionarios por idioma — soporta español, inglés, francés, alemán, etc.
• Highlighting — extrae fragmentos relevantes con ts_headline().

EXPLAIN ANALYZE y rendimiento

PostgreSQL ofrece herramientas de análisis de rendimiento de nivel profesional. El comando EXPLAIN ANALYZE ejecuta la consulta y muestra el plan de ejecución real:

EXPLAIN ANALYZE
SELECT * FROM usuarios WHERE email = 'ana@email.com';

--                                        QUERY PLAN
-- ─────────────────────────────────────────────────────────────────────
--  Index Scan using idx_usuarios_email on usuarios
--    (cost=0.28..8.29 rows=1 width=140)
--    (actual time=0.032..0.033 rows=1 loops=1)
--    Index Cond: ((email)::text = 'ana@email.com'::text)
--  Planning Time: 0.087 ms
--  Execution Time: 0.048 ms

Conceptos clave que debes conocer:

• Seq Scan — escaneo secuencial de toda la tabla. Ocurre cuando no hay índice disponible o cuando PostgreSQL decide que es más barato leer toda la tabla.
• Index Scan — usa un índice para localizar filas específicas. Mucho más rápido para consultas selectivas.
• Bitmap Index Scan — combina múltiples índices y luego accede a las filas. PostgreSQL lo usa cuando varios índices pueden ser relevantes.

VACUUM y mantenimiento

PostgreSQL usa MVCC, lo que significa que las filas actualizadas o eliminadas no se borran físicamente de inmediato. El comando VACUUM recupera ese espacio:

-- Recuperar espacio y actualizar estadísticas
VACUUM ANALYZE usuarios;

-- Ver estadísticas de una tabla
SELECT schemaname, tablename, n_live_tup, n_dead_tup,
       last_vacuum, last_analyze
FROM pg_stat_user_tables
WHERE tablename = 'usuarios';

En PostgreSQL moderno (9.6+), autovacuum está habilitado por defecto y maneja la mayoría de los casos automáticamente. Sin embargo, para tablas con alta tasa de escritura, puede ser necesario ajustar su configuración.

Ejercicios prácticos

Pon a prueba lo aprendido. Intenta resolver cada ejercicio antes de consultar la solución.