Cómo Crear y Optimizar una Base de Datos PostgreSQL para Apps Escalables

Una base de datos útil no comienza con el comando CREATE DATABASE, sino con las reglas que los datos deben preservar. Este tutorial utiliza PostgreSQL y un ejemplo de clientes y pedidos para mostrar creación, relación, consulta, seguridad y respaldo.

Respuesta directa

Instala PostgreSQL, crea una base de datos y un usuario de aplicación, conéctate con psql, modela entidades y relaciones, crea tablas con restricciones, inserta datos de prueba, consulta con JOIN, analiza índices, limita privilegios y automatiza respaldos restaurables.

1. Crea la base de datos y conéctate

Con PostgreSQL instalado y el servicio activo:

createdb tienda
psql tienda

O dentro de una sesión administrativa:

CREATE DATABASE tienda;

Si aparece “permission denied”, el usuario actual no tiene CREATEDB. No conviertas la cuenta de la aplicación en superusuario; pide la creación a un administrador.

2. Modela antes de crear tablas

Reglas del ejemplo:

• el cliente tiene un correo electrónico único;
• el pedido pertenece a un cliente;
• el valor no puede ser negativo;
• el estado acepta solo estados conocidos;
• las fechas se registran con zona horaria.

Evita almacenar la lista de pedidos dentro de una columna de cliente. Las relaciones y restricciones hacen que las inconsistencias sean más difíciles.

3. Crea las tablas

CREATE TABLE clientes (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nombre text NOT NULL,
  email text NOT NULL UNIQUE,
  creado_en timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE pedidos (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id bigint NOT NULL REFERENCES clientes(id),
  valor numeric(12,2) NOT NULL CHECK (valor >= 0),
  estado text NOT NULL CHECK (estado IN ('pendiente','pagado','cancelado')),
  creado_en timestamptz NOT NULL DEFAULT now()
);

Usa numeric para dinero cuando se necesite precisión decimal. No uses punto flotante para valores financieros. La clave foránea impide que un pedido se asocie a un cliente inexistente.

4. Inserta y consulta

INSERT INTO clientes (nombre, email)
VALUES ('Ana', 'ana@example.com')
RETURNING id;

INSERT INTO pedidos (cliente_id, valor, estado)
VALUES (1, 149.90, 'pagado');

SELECT c.nombre, p.id, p.valor, p.estado
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
ORDER BY p.creado_en DESC;

En aplicaciones, usa parámetros preparados. Nunca construyas SQL concatenando texto recibido del usuario.

5. Usa transacciones

Cuando dos cambios necesitan ocurrir juntos:

BEGIN;
UPDATE inventario SET cantidad = cantidad - 1
WHERE producto_id = 10 AND cantidad > 0;
INSERT INTO pedidos (cliente_id, valor, estado)
VALUES (1, 149.90, 'pagado');
COMMIT;

El ejemplo aún necesitaría verificar si la actualización alteró una fila. En caso de error, usa ROLLBACK. Las transacciones mantienen atomicidad, pero la concurrencia y el aislamiento deben ser diseñados según el proceso.

6. Crea índices con evidencia

Las claves primarias y UNIQUE ya crean índices. Para buscar pedidos recientes de un cliente:

CREATE INDEX pedidos_cliente_creado_idx
ON pedidos (cliente_id, creado_en DESC);

Valida con:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM pedidos
WHERE cliente_id = 1
ORDER BY creado_en DESC
LIMIT 20;

Cada índice ocupa espacio y encarece la escritura. No crees un índice por columna sin observar consultas reales.

7. Separa el usuario de la aplicación

CREATE ROLE tienda_app LOGIN PASSWORD 'cambia-por-una-contraseña-fuerte';
GRANT CONNECT ON DATABASE tienda TO tienda_app;
GRANT USAGE ON SCHEMA public TO tienda_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON clientes, pedidos TO tienda_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO tienda_app;

Almacena la credencial en un gestor de secretos, no en el código. Ajusta privilegios a lo que la aplicación realmente hace. Los entornos y bases de producción y prueba deben estar separados.

8. Versiona migraciones

No alteres producción manualmente sin registro. Crea archivos de migración ordenados, haz revisión y prueba de actualización y retroceso cuando sea posible. Cambios grandes pueden requerir expansión, migración de datos y eliminación posterior para no interrumpir versiones antiguas.

9. Haz respaldo y prueba restauración

pg_dump -Fc -d tienda -f tienda.dump
createdb tienda_restaurar
pg_restore -d tienda_restaurar tienda.dump

Un respaldo que nunca ha sido restaurado es solo una esperanza. Define frecuencia, retención, cifrado, acceso, ubicación separada y objetivos de pérdida aceptable y tiempo de recuperación.

Checklist para producción

• restricciones NOT NULL, UNIQUE, CHECK y FKs adecuadas;
• migraciones versionadas y probadas;
• consultas parametrizadas;
• usuario sin privilegio administrativo;
• conexiones protegidas por TLS;
• pool dimensionado;
• consultas lentas monitoreadas;
• respaldos automatizados y restauración probada;
• datos personales con retención y acceso definidos.

Para aplicaciones multiempresa, lee arquitectura multi-tenant. Para la stack completa, ve Next.js y Supabase.

Preguntas frecuentes

¿PostgreSQL es bueno para principiantes?

Sí. Ofrece SQL estandarizado, documentación completa y recursos que siguen siendo útiles en sistemas grandes.

¿Una hoja de cálculo reemplaza una base de datos?

Las hojas de cálculo atienden análisis y procesos pequeños. La concurrencia, integridad, relaciones y control de acceso normalmente justifican una base de datos.

Fuentes primarias

• PostgreSQL: tutorial oficial
• PostgreSQL: creación de base de datos
• PostgreSQL: respaldo y restauración