Extensiones esenciales de PostgreSQL

PostgreSQL incorpora un mecanismo de extensiones que permite añadir tipos de datos, funciones, operadores e incluso lenguajes procedurales sin tocar el motor. Algunas extensiones forman parte de contrib y vienen con la instalación estándar; otras se distribuyen vía PGXN o paquetes del sistema. Esta lección recorre las más útiles en proyectos profesionales.

Habilitar y consultar extensiones

La sintaxis es simple. Cada extensión se instala una vez por base de datos:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT extname, extversion FROM pg_extension ORDER BY extname;

Algunas extensiones requieren añadirse a shared_preload_libraries y reiniciar el servidor (ejemplo pg_stat_statements). Otras se cargan dinámicamente con CREATE EXTENSION y ya funcionan.

UUIDs: gen_random_uuid frente a uuid-ossp

Históricamente la generación de UUIDs requería uuid-ossp con funciones como uuid_generate_v4(). Desde PostgreSQL 13 la función gen_random_uuid() está disponible nativamente sin extensión, gracias a la integración de pgcrypto:

CREATE TABLE pedidos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cliente_id BIGINT NOT NULL,
    total NUMERIC(12,2) NOT NULL,
    creado_en TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO pedidos (cliente_id, total) VALUES (1, 99.99);

gen_random_uuid() produce UUIDs versión 4 (random). Si necesitas otros tipos como UUID v1 (basado en timestamp y MAC) o v5 (basado en hash de namespace + nombre), uuid-ossp sigue siendo la opción.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SELECT
    uuid_generate_v1() AS v1_basado_timestamp,
    uuid_generate_v4() AS v4_random,
    uuid_generate_v5(uuid_ns_url(), 'https://certidevs.com') AS v5_namespace;

Para nuevos proyectos, prefiere gen_random_uuid() y solo añade uuid-ossp si necesitas v1 o v5 explícitamente.

pgcrypto para hashing y cifrado

pgcrypto aporta funciones criptográficas robustas para hashing de contraseñas y cifrado simétrico de columnas:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Hash de password con salt automatico (bcrypt)
SELECT crypt('password123', gen_salt('bf', 12)) AS hash;

-- Verificacion
SELECT crypt('password123', '$2a$12$...stored_hash...') = '$2a$12$...stored_hash...' AS valido;

-- Cifrado simetrico de un campo
INSERT INTO secretos (datos)
VALUES (pgp_sym_encrypt('informacion sensible', 'clave_maestra'));

SELECT pgp_sym_decrypt(datos, 'clave_maestra') FROM secretos;

Para autenticación de aplicación es preferible delegar el hashing al lenguaje de aplicación (Argon2 en Python, bcrypt en Node, etc.) por temas de gestión de la clave maestra. pgcrypto brilla en cifrado de columnas con datos sensibles.

citext: texto case-insensitive sin LOWER

La extensión citext define un tipo de texto que ignora mayúsculas y minúsculas en comparaciones:

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE usuarios (
    id BIGSERIAL PRIMARY KEY,
    email CITEXT UNIQUE NOT NULL
);

INSERT INTO usuarios (email) VALUES ('Alice@Example.com');

-- Esto encuentra el registro sin necesidad de LOWER
SELECT * FROM usuarios WHERE email = 'alice@example.com';

La alternativa sin extensión es una columna generada con LOWER y un índice único sobre ella, vista en la lección de columnas generadas. citext es más conciso pero introduce una dependencia adicional.

ltree: jerarquías como rutas

ltree define un tipo para representar rutas jerárquicas estilo ciencia.matematicas.algebra. Permite consultas eficientes sobre ancestros, descendientes y patrones de ruta sin recurrir a CTE recursivas:

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TABLE categorias (
    id SERIAL PRIMARY KEY,
    nombre TEXT NOT NULL,
    ruta LTREE NOT NULL
);

CREATE INDEX idx_categorias_ruta_gist ON categorias USING GIST (ruta);

INSERT INTO categorias (nombre, ruta) VALUES
('Ciencia',         'ciencia'),
('Matematicas',     'ciencia.matematicas'),
('Algebra',         'ciencia.matematicas.algebra'),
('Calculo',         'ciencia.matematicas.calculo'),
('Fisica',          'ciencia.fisica');

Operadores específicos:

| Operador | Significado | Ejemplo | |----------|-------------|---------| | <@ | descendiente de | ruta <@ 'ciencia.matematicas' | | @> | ancestro de | 'ciencia.matematicas' @> ruta | | ~ | match con lquery | ruta ~ 'ciencia.*.algebra' | | ? | match con array de lquery | ruta ? '{ciencia.*}' |

-- Todas las descendientes de matematicas
SELECT * FROM categorias WHERE ruta <@ 'ciencia.matematicas';

-- Profundidad
SELECT nombre, nlevel(ruta) AS profundidad FROM categorias;

ltree es mucho más rápido que CTEs recursivas para árboles estables que se modifican poco. Para jerarquías muy dinámicas, las CTE recursivas siguen siendo más flexibles.

pg_trgm: búsqueda fuzzy y LIKE acelerado

pg_trgm divide cadenas en trigramas (secuencias de tres caracteres) y permite búsquedas por similitud y aceleración de patrones LIKE '%texto%' que normalmente harían sequential scan:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_productos_nombre_trgm
    ON productos USING GIN (nombre gin_trgm_ops);

-- LIKE con comodines en ambos lados, ahora indexado
SELECT * FROM productos WHERE nombre ILIKE '%lapt%';

-- Similitud
SELECT nombre, similarity(nombre, 'laptop dell') AS sim
FROM productos
WHERE nombre % 'laptop dell'
ORDER BY sim DESC
LIMIT 10;

El operador % activa la búsqueda por similitud: devuelve resultados con similitud por encima del umbral configurable (pg_trgm.similarity_threshold, por defecto 0.3).

Para una búsqueda de texto en español que tolere errores tipográficos en nombres y direcciones, pg_trgm es la opción más simple. Para búsqueda multidocumento por relevancia, full-text search es superior.

unaccent: ignorar tildes en búsquedas

Buscar "garcia" debería encontrar "García". La extensión unaccent elimina diacríticos:

CREATE EXTENSION IF NOT EXISTS unaccent;

SELECT unaccent('Catalá García');
-- Catala Garcia

-- Indice funcional para busquedas sin tildes
CREATE INDEX idx_clientes_nombre_unaccent
    ON clientes (LOWER(unaccent(nombre)));

SELECT * FROM clientes
WHERE LOWER(unaccent(nombre)) LIKE LOWER(unaccent('%garcia%'));

Combinada con pg_trgm en un índice GIN funcional, permite búsquedas tolerantes a tildes y errores tipográficos en una sola consulta indexada.

pg_stat_statements: telemetría de queries

pg_stat_statements registra estadísticas agregadas de todas las queries ejecutadas: número de ejecuciones, tiempo total, tiempo por ejecución, filas retornadas, lecturas de bloques, etc. Es la herramienta principal para identificar queries lentas y queries más frecuentes.

-- Requiere shared_preload_libraries = 'pg_stat_statements' en postgresql.conf
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries por tiempo total acumulado
SELECT
    substring(query, 1, 80) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Las queries se almacenan normalizadas (sin valores literales), de modo que SELECT * FROM productos WHERE id = 1 y SELECT * FROM productos WHERE id = 2 cuentan como la misma. Esto agrupa la actividad real.

hstore: legado pero todavía usado

hstore fue el predecesor de JSONB para almacenar pares clave-valor. Sigue siendo válido cuando los valores son siempre strings y no necesitas anidación:

CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE eventos (
    id SERIAL PRIMARY KEY,
    metadatos HSTORE
);

INSERT INTO eventos (metadatos) VALUES
('"ciudad" => "Madrid", "tipo" => "concierto"');

SELECT metadatos -> 'ciudad' FROM eventos;

Para proyectos nuevos casi siempre es preferible JSONB: ofrece tipos numéricos, booleanos, anidación y tooling más rico. hstore se mantiene por compatibilidad.

cube y earthdistance: geoespacial básico

Cuando necesitas distancia geoespacial sin instalar PostGIS, las extensiones cube y earthdistance ofrecen una solución ligera:

CREATE EXTENSION IF NOT EXISTS cube;
CREATE EXTENSION IF NOT EXISTS earthdistance;

-- Distancia en metros entre dos coordenadas
SELECT earth_distance(
    ll_to_earth(40.4168, -3.7038),     -- Madrid
    ll_to_earth(41.3879, 2.16992)      -- Barcelona
) AS metros;
-- ~504000

-- Busqueda de puntos cercanos con indice GiST
CREATE INDEX idx_tiendas_geoloc
    ON tiendas USING GIST (ll_to_earth(latitud, longitud));

SELECT nombre
FROM tiendas
WHERE earth_box(ll_to_earth(40.4168, -3.7038), 5000)
      @> ll_to_earth(latitud, longitud)
  AND earth_distance(
      ll_to_earth(latitud, longitud),
      ll_to_earth(40.4168, -3.7038)
  ) < 5000;

Para casos avanzados (polígonos, transformaciones de proyección, datos catastrales) PostGIS es obligatorio. Para "tiendas más cercanas a un punto", earthdistance basta.

flowchart LR
    A[Caso de uso] --> B{Que necesitas?}
    B -->|Hash de password| C[pgcrypto]
    B -->|Texto case-insensitive| D[citext]
    B -->|Jerarquia estable| E[ltree]
    B -->|LIKE acelerado| F[pg_trgm]
    B -->|Sin tildes| G[unaccent]
    B -->|Telemetria queries| H[pg_stat_statements]
    B -->|Distancia simple| I[earthdistance]
    B -->|Embeddings IA| J[pgvector]

Las extensiones convierten a PostgreSQL en una base de datos universal. La regla práctica es activar solo las que realmente usas, mantener un script idempotente con CREATE EXTENSION IF NOT EXISTS por cada base de datos, y revisar pg_extension periódicamente para detectar extensiones obsoletas o no usadas.