wshobson/diseño-de-tablas-postgresql

¿Qué hace diseño-de-tablas-postgresql?

Utiliza esta habilidad al diseñar o revisar un esquema específico de PostgreSQL. Cubre mejores prácticas, tipos de datos, indexación, restricciones, patrones de rendimiento y características avanzadas.

Mejores Prácticas

• Usa tipos de datos adecuados: Prefiere integer sobre varchar para IDs numéricos, timestamptz sobre timestamp para marcas de tiempo con zona horaria.
• Normaliza pero no exageres: La normalización reduce redundancia, pero la desnormalización controlada puede mejorar el rendimiento de lectura.
• Define claves primarias: Siempre define una clave primaria, preferiblemente usando serial o bigserial para IDs autoincrementales.
• Usa text en lugar de varchar sin límite: text y varchar son equivalentes en PostgreSQL, pero text es más explícito para cadenas sin límite.

Tipos de Datos

• Numéricos: smallint, integer, bigint, numeric (para decimales exactos), real, double precision.
• Texto: text, varchar(n), char(n).
• Fecha/Hora: date, time, timestamp, timestamptz, interval.
• JSON: json, jsonb (preferido por soporte de índices).
• Geométricos: point, line, polygon, etc. (con extensión PostGIS).

Indexación

• Índices B-tree: Para búsquedas de igualdad y rangos, son el tipo por defecto.
• Índices GiST: Para datos geométricos y búsquedas de texto completo.
• Índices GIN: Para arrays y datos JSONB.
• Índices parciales: Crear índices solo en subconjuntos de datos, ej. CREATE INDEX idx_active_users ON users (email) WHERE active = true;.
• Índices multicolumna: Ordena las columnas por selectividad (la más selectiva primero).

Restricciones

• NOT NULL: Usa para columnas que nunca deben ser nulas.
• UNIQUE: Asegura valores únicos, crea automáticamente un índice B-tree.
• CHECK: Valida condiciones, ej. CHECK (age >= 0).
• FOREIGN KEY: Mantiene integridad referencial, usa ON DELETE CASCADE o RESTRICT según la lógica de negocio.

Patrones de Rendimiento

• Usa EXPLAIN ANALYZE: Para analizar planes de ejecución de consultas.
• Evita SELECT *: Selecciona solo las columnas necesarias para reducir E/S y transferencia de datos.
• Aprovecha la partición de tablas: Para tablas grandes, usa PARTITION BY RANGE o LIST.
• Configura work_mem: Ajusta el límite de memoria para operaciones de ordenación y hash.
• Mantén estadísticas actualizadas: Ejecuta ANALYZE regularmente para ayudar al optimizador.

Características Avanzadas

• Extensiones: Ej. pg_trgm para búsqueda de texto similar, uuid-ossp para UUIDs.
• Vistas materializadas: Almacenan resultados de consultas para mejorar rendimiento en informes.
• Funciones de ventana: Ej. ROW_NUMBER() OVER (PARTITION BY ...) para análisis avanzados.
• CTEs recursivas: Para consultas jerárquicas, ej. árboles de organización.
• Políticas de seguridad a nivel de fila (RLS): Restringe el acceso a filas según roles.

Ejemplo de Esquema

CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    nombre TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    edad INTEGER CHECK (edad >= 0),
    creado_en TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_usuarios_edad ON usuarios (edad) WHERE edad > 18;

Referencias

• Documentación oficial de PostgreSQL
• Guía de mejores prácticas de PostgreSQL