Fundamentos de PostgreSQL

10 ejemplos para empezar con los fundamentos de PostgreSQL: 7 básicos y 3 intermedios.

Prerrequisitos

Instala PostgreSQL 18 localmente o usa Docker. Necesitas psql y acceso de superusuario para crear objetos de ejemplo.

# Desarrollo local con Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Ejemplos Básicos

1. Conectar a una Base de Datos

Cada sesión se adjunta a exactamente una base de datos en el clúster.

\c postgres
SELECT current_database(), current_user, version();

• El clúster es la instancia de PostgreSQL en ejecución; la base de datos es el objetivo de la conexión.
• \c nombre_db en psql cambia de base de datos sin volver a conectar el binario del cliente.
• version() devuelve la compilación del servidor; fija las herramientas del cliente a la misma versión principal cuando sea posible.

Relacionado: Arquitectura de Postgres - procesos detrás de la conexión.

2. Crear un Esquema y una Tabla

Los esquemas agrupan tablas, vistas y funciones. public es el esquema predeterminado.

CREATE SCHEMA app AUTHORIZATION postgres;
 
CREATE TABLE app.customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO app.customers (email) VALUES ('ada@example.com');

• Usa esquemas para separar aplicaciones o inquilinos en una sola base de datos.
• Prefiere columnas de identidad sobre serial para tablas nuevas (PostgreSQL 10+).
• Las restricciones viven en el catálogo y sobreviven a los rediploy de aplicaciones.

3. Consultar con Columnas Explícitas

SQL legible lista columnas y filtra temprano.

SELECT id, email, created_at
FROM app.customers
WHERE email LIKE '%@example.com'
ORDER BY created_at DESC
LIMIT 10;

• Evita SELECT * en el código de la aplicación; los cambios de esquema rompen los clientes silenciosamente.
• Filtra en columnas indexadas cuando las tablas crecen.
• LIMIT sin ORDER BY devuelve un conjunto de filas arbitrario.

Relacionado: Fundamentos de Consultas SQL

4. Inspeccionar Metadatos de Objetos

Los catálogos del sistema describen cada objeto en el clúster.

SELECT n.nspname AS schema, c.relname AS table_name, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app' AND c.relkind = 'r'
ORDER BY 1, 2;

• pg_class + pg_namespace son el registro de tablas de bajo nivel.
• information_schema ofrece vistas estándar de SQL con menos detalle.
• Las consultas de metadatos impulsan migraciones, linters y catálogos de datos.

Relacionado: Catálogos del Sistema

5. Usar Transacciones para Escrituras Múltiples

Las transacciones ACID agrupan cambios en una unidad atómica.

BEGIN;
INSERT INTO app.customers (email) VALUES ('grace@example.com');
UPDATE app.customers SET email = lower(email) WHERE email = 'grace@example.com';
COMMIT;

• El autocommit predeterminado envuelve cada sentencia a menos que uses BEGIN.
• En caso de error, ROLLBACK deshace el trabajo en la transacción actual.
• Mantén las transacciones cortas para reducir la presión de bloqueos e hinchazón.

Relacionado: Fundamentos de Transacciones

6. Añadir un Índice para Patrones de Búsqueda

Los índices B-tree aceleran los predicados de igualdad y rango en columnas.

CREATE INDEX customers_created_at_idx
  ON app.customers (created_at DESC);
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM app.customers
WHERE created_at > now() - interval '7 days';

• Los índices aceleran las lecturas pero añaden amplificación de escritura en INSERT/UPDATE.
• EXPLAIN ANALYZE ejecuta la consulta y muestra los tiempos reales.
• Indexa solo lo que consultas; verifica los planes después de cambios de esquema.

Relacionado: Fundamentos de Índices

7. Instalar una Extensión

Las extensiones envían funciones opcionales como módulos controlados.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
 
SELECT gen_random_uuid();

• Las extensiones tienen versión y se registran en pg_extension.
• Algunas extensiones requieren superusuario o CREATE en la base de datos.
• Fija las versiones de las extensiones en los scripts de migración para reproducibilidad.

Ejemplos Intermedios

8. Establecer search_path de Forma Segura

Los nombres no calificados se resuelven a través de search_path. La mala configuración causa errores de tabla incorrecta.

SHOW search_path;
 
SET search_path = app, public;
 
SELECT email FROM customers LIMIT 1;
 
-- Más seguro en aplicaciones: siempre calificar por esquema
SELECT email FROM app.customers LIMIT 1;

• El valor predeterminado es "$user", public; los esquemas vacíos pueden sorprender a los roles nuevos.
• El código sensible a la seguridad debe calificar por esquema o establecer la ruta por sesión.
• Nunca confíes en public para datos multi-inquilino sin RLS.

Relacionado: Base de Datos, Esquema y search_path

9. Comprobar Señales de Salud del Servidor

Consultas rápidas al catálogo exponen conexiones, indicios de hinchazón y versión.

SELECT count(*) AS backends FROM pg_stat_activity;
 
SELECT datname, numbackends, xact_commit, blks_read
FROM pg_stat_database
WHERE datname = current_database();

• pg_stat_activity lista sesiones activas y eventos de espera.
• pg_stat_database agrega commits, lecturas y conflictos por base de datos.
• Empareja las comprobaciones del catálogo con métricas del sistema operativo (CPU, disco, memoria) para diagnóstico.

Relacionado: Arquitectura de Postgres

10. Elegir timestamptz para Eventos

Almacena instantes en UTC; convierte en el borde de la aplicación.

ALTER TABLE app.customers
  ADD COLUMN last_login_at timestamptz;
 
UPDATE app.customers
SET last_login_at = now()
WHERE email = 'ada@example.com';
 
SELECT email, last_login_at AT TIME ZONE 'America/New_York' AS login_ny
FROM app.customers;

• timestamptz almacena UTC internamente; la visualización depende de la TimeZone de la sesión.
• Evita timestamp without time zone para tiempos de eventos del mundo real.
• Documenta las suposiciones de zona horaria en los contratos de API.

Relacionado: Tipos de Datos de Fecha, Hora y timestamptz

Versiones de la Pila: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x y PostGIS 3.5+.