PostgreSQL en el ecosistema de datos

En el bloque anterior vimos cómo guardar un histórico en Parquet. Ahora un equipo de atención al cliente necesita revisar las últimas recogidas de una zona, mientras que otro equipo calcula un informe diario de toda la ciudad. Las dos preguntas usan los mismos datos, pero obligan al motor a leer cantidades muy distintas de información. En dos sesiones comprobaremos qué puede resolver PostgreSQL con un índice, y qué ocurre cuando una consulta necesita recorrer casi todo el histórico.

Entrar al laboratorio

En el anfitrión, sitúate en modulo01/ del repositorio del laboratorio. Cada alumno o pareja necesita su propia instancia para poder modificar el índice. Arranca el servidor:

make pg-up

Sin Make, ejecuta:

docker compose -f postgres/compose.yaml up -d --wait

Compose espera a que pg_isready confirme que PostgreSQL acepta conexiones. La imagen pgvector/pgvector:0.8.1-pg17 proporciona PostgreSQL 17.8 y pgvector 0.8.1. El contenedor se llama postgres-vector y publica PostgreSQL en 127.0.0.1:5432. La base es bigdata y el usuario y la contraseña del laboratorio son alumno. Un volumen conserva la base entre sesiones.

Si el puerto 5432 está ocupado, detén el PostgreSQL local o cambia el puerto del anfitrión en postgres/compose.yaml a "127.0.0.1:5433:5432". Los comandos con docker exec siguen funcionando porque se ejecutan dentro del contenedor. Para comprobar el arranque o consultar los registros:

docker exec postgres-vector pg_isready -U alumno -d bigdata
docker logs --tail 50 postgres-vector

Antes de entrar en psql, comprueba que se ha preparado la tabla public.viajes en bigdata. Si partes de un volumen nuevo, sigue el procedimiento de carga del Parquet oficial o restaura una copia que incluya la tabla. Para comprobar el servidor y pgvector en una base temporal, ejecuta make test-pg o, sin Make, bash prueba-postgres.sh.

Abre una sesión con make pg-psql o con esta orden:

docker exec -it postgres-vector psql -U alumno -d bigdata

A partir de aquí los comandos se ejecutan en psql, dentro de la base bigdata. \d describe la tabla y sus índices. \pset pager off deja el plan completo visible en la terminal.

\pset pager off
\d public.viajes
SELECT count(*) FROM public.viajes;

Esperamos 2.964.624 filas del archivo de enero de 2024 de la New York City Taxi and Limousine Commission. La tabla conserva la zona y hora local de recogida, la zona de destino y el importe total registrado en dólares con dos decimales. id es una clave generada para esta copia y permite desempatar si dos viajes tienen la misma hora. El fichero contiene algunas fechas fuera de enero. Conservamos las filas originales y delimitamos el periodo en SQL. El importe registrado puede incluir ajustes. En esta práctica calculamos una media del campo publicado, sin reconstruir la facturación.

Primeros pasos con PostgreSQL y SQL

PostgreSQL es un sistema gestor de bases de datos relacionales. Organiza la información en tablas, con filas que representan registros y columnas que describen sus campos. El servidor conserva los datos, recibe consultas y controla las operaciones. psql es el cliente de terminal que estamos utilizando para enviarle órdenes SQL y consultar sus respuestas.

Una misma instancia de PostgreSQL puede contener varias bases de datos. Dentro de cada base, los esquemas agrupan tablas y otros objetos. En public.viajes, public es el esquema y viajes es la tabla. Para esta práctica estamos conectados a la base bigdata.

Crear una tabla pequeña

Antes de trabajar con los millones de viajes, vamos a crear una tabla de tres zonas ficticias para reconocer las operaciones básicas. Ejecuta estas órdenes en la sesión de psql que ya tienes abierta.

CREATE TEMP TABLE zonas_ejemplo (
    codigo integer,
    nombre text
);

CREATE TABLE define una tabla y sus columnas. Aquí añadimos TEMP para que la tabla pertenezca a esta sesión y se elimine al cerrarla. integer almacena números enteros y text almacena cadenas de texto. Cada orden SQL termina con punto y coma. Si psql muestra bigdata-#, está esperando que completes la orden que has empezado.

Insertar registros

INSERT INTO añade filas a una tabla. Indicamos las columnas que vamos a rellenar y, después de VALUES, los valores de cada fila entre paréntesis. Las cadenas de texto se escriben entre comillas simples.

INSERT INTO zonas_ejemplo (codigo, nombre)
VALUES (101, 'Zona norte'),
       (102, 'Zona centro'),
       (103, 'Zona sur');

Los códigos y nombres de esta tabla son ejemplos inventados. La tabla real public.viajes ya está cargada a partir de los registros de la TLC. Si repites este INSERT, añadirás otras tres filas a la tabla de ejemplo.

Consultar filas y columnas

SELECT indica qué columnas queremos recuperar y FROM indica de qué tabla proceden. El asterisco selecciona todas las columnas.

SELECT * FROM zonas_ejemplo;

La consulta devuelve los tres códigos y sus nombres. Para obtener solo parte de las filas, añadimos WHERE. Para solicitar un orden concreto, utilizamos ORDER BY.

SELECT codigo, nombre
FROM zonas_ejemplo
WHERE codigo >= 102
ORDER BY codigo DESC;

Esta segunda consulta devuelve primero 103, Zona sur y después 102, Zona centro. El filtro deja pasar los códigos mayores o iguales que 102 y DESC ordena de mayor a menor. Sin ORDER BY, el orden de las filas puede variar.

También podemos calcular un resultado sobre varias filas. La función COUNT(*), que ya hemos usado para comprobar la carga de los taxis, cuenta los registros de una tabla.

SELECT count(*) AS numero_zonas FROM zonas_ejemplo;

El resultado es 3 si hemos ejecutado el INSERT una sola vez. AS numero_zonas asigna un nombre a la columna del resultado. A continuación añadiremos reglas sobre los valores permitidos y comprobaremos cómo revertir una modificación.

Restricciones y transacciones

El operador puede marcar una incidencia de un viaje como revisada. Es una actualización puntual distinta de los registros que publica la TLC. Para ensayar usamos una tabla temporal, que desaparece al cerrar psql.

CREATE TEMP TABLE revision_viaje (
    viaje_id bigint PRIMARY KEY,
    estado text NOT NULL CHECK (estado IN ('pendiente', 'revisado'))
);

INSERT INTO revision_viaje (viaje_id, estado)
SELECT min(id), 'pendiente' FROM public.viajes;
SELECT * FROM revision_viaje;

La clave primaria admite una fila de revisión por viaje. Ejecuta el siguiente UPDATE por separado. La restricción CHECK debe rechazarlo.

UPDATE revision_viaje SET estado = 'cerrado';

Ahora el cambio es válido y se puede comprobar dentro de la transacción. ROLLBACK revierte el cambio y COMMIT confirmaría el cambio para el resto de la sesión.

BEGIN;
UPDATE revision_viaje SET estado = 'revisado';
SELECT * FROM revision_viaje;
ROLLBACK;
SELECT * FROM revision_viaje;

Diagnosticar la consulta de una zona

El panel de operaciones pide las últimas 20 recogidas de la zona 237 durante enero. Las dos condiciones de fecha forman un intervalo que incluye el inicio de enero y excluye el inicio de febrero. En caso de empate de fecha, el identificador decide el orden.

SELECT id, fecha_recogida, zona_destino, importe_total
FROM public.viajes
WHERE zona_recogida = 237
  AND fecha_recogida >= TIMESTAMP '2024-01-01'
  AND fecha_recogida <  TIMESTAMP '2024-02-01'
ORDER BY fecha_recogida DESC, id DESC
LIMIT 20;

El límite de 20 filas describe la salida. Para saber cuánto trabajo ha hecho el motor, ejecuta la misma consulta con el plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, fecha_recogida, zona_destino, importe_total
FROM public.viajes
WHERE zona_recogida = 237
  AND fecha_recogida >= TIMESTAMP '2024-01-01'
  AND fecha_recogida <  TIMESTAMP '2024-02-01'
ORDER BY fecha_recogida DESC, id DESC
LIMIT 20;

ANALYZE en EXPLAIN ejecuta el SELECT y mide el trabajo observado. Lee el plan desde los nodos interiores hacia arriba:

Seq Scan o Parallel Seq Scan recorre la tabla y filtra filas. Si el plan indica varios loops, las filas mostradas para ese nodo son promedios por ejecución. Las filas que el nodo entrega y las que elimina el filtro permiten estimar cuántas examinó.
Sort ordena las candidatas. Limit entrega como máximo 20.
Buffers: shared hit=… read=… distingue accesos que encontraron el bloque en la caché de PostgreSQL y accesos que tuvieron que cargarlo. Una lectura puede servirse desde la caché del sistema operativo. Compara la línea de buffers del nodo superior, sin sumar los nodos hijos de nuevo.
Execution Time es el tiempo de ejecución medido. Puede variar con la caché y la carga del equipo, incluso si el plan es idéntico.

M01-PG-01 — Obtener la referencia

Conserva el mensaje de error de la restricción y los dos estados observados alrededor del ROLLBACK.
Verifica con \d public.viajes que aún no aparece viajes_zona_fecha_idx. Si ya lo creaste en una sesión anterior de este laboratorio, puedes quitar ese índice con DROP INDEX IF EXISTS public.viajes_zona_fecha_idx;.
Ejecuta la consulta de la zona y conserva los identificadores de las 20 filas. Guarda el plan inicial y señala dónde se filtra, dónde se ordena y cuántos bloques indica la línea superior de buffers.

Crear un índice para la consulta

Un índice B-tree permite buscar por un valor y recorrer claves ordenadas. Ponemos primero la zona porque la consulta fija su valor. Después colocamos la fecha y el identificador en el orden solicitado para que el motor pueda empezar por los viajes recientes y parar tras 20 filas.

CREATE INDEX viajes_zona_fecha_idx
ON public.viajes (zona_recogida, fecha_recogida DESC, id DESC);

SELECT pg_size_pretty(pg_relation_size('public.viajes_zona_fecha_idx')) AS tamano_indice;

Repite el SELECT y el EXPLAIN (ANALYZE, BUFFERS) anteriores sin cambiar filtros, orden ni límite. Busca un Index Scan con el índice nuevo y comprueba si desaparece Sort. El índice ocupa espacio y PostgreSQL tendrá que mantenerlo al insertar filas o cambiar alguna de sus claves.

El informe diario de toda la ciudad

El informe necesita muchas filas del mes y carece del filtro por zona. COUNT(*) cuenta registros. AVG(importe_total) calcula la media de los importes presentes e ignora los nulos. ::date extrae el día local y ROUND(..., 2) redondea la media a céntimos.

SELECT fecha_recogida::date AS dia,
       count(*) AS registros,
       round(avg(importe_total), 2) AS importe_medio_usd
FROM public.viajes
WHERE fecha_recogida >= TIMESTAMP '2024-01-01'
  AND fecha_recogida <  TIMESTAMP '2024-02-01'
GROUP BY fecha_recogida::date
ORDER BY dia;

Anteponer EXPLAIN (ANALYZE, BUFFERS) a este SELECT permite comprobar cómo se lee y se agrupa el mes. El resultado debe incluir 31 días. Es razonable que PostgreSQL elija un recorrido de tabla si debe leer casi todos los registros para calcular el informe.

M01-PG-02 — Explicar el efecto del índice

Guarda el plan con el índice y verifica que devuelve los mismos 20 identificadores en el mismo orden. Explica qué trabajo ha cambiado y compara los bloques accedidos.
Ejecuta el informe y su plan. Explica qué columnas necesita y por qué el índice pensado para una zona puede aportar poco a esa consulta.
Escribe una decisión para el equipo. Indica qué conservarías en PostgreSQL para atender incidencias y cuándo tendría sentido consultar el histórico en Parquet con Hive. Usa los patrones de acceso observados.

De PostgreSQL a Hive

PostgreSQL puede ejecutar las dos consultas. Si el histórico crece y se repite el análisis por meses, almacenar los viajes en Parquet permitiría seleccionar los periodos y leer las columnas que usa el informe. El siguiente bloque explica cómo Hive registra archivos, esquemas y particiones para consultarlos. El ensayo de PostgreSQL ocurre en un solo equipo. Permite observar planes y accesos a bloques. Para medir rendimiento distribuido o disponibilidad harían falta otros ensayos.

Guardar, restaurar y cerrar el laboratorio

Sal de psql con \q. Guarda tus consultas, planes y conclusiones en el anfitrión. Con el servidor en marcha, guarda una copia SQL desde modulo01/:

make pg-dump

La copia queda en trabajo/bigdata-m01.sql del anfitrión. Las órdenes equivalentes generan el fichero dentro del contenedor y después lo copian al anfitrión:

mkdir -p trabajo
docker exec postgres-vector pg_dump -U alumno -d bigdata -f /tmp/bigdata-m01.sql
docker cp postgres-vector:/tmp/bigdata-m01.sql trabajo/bigdata-m01.sql

Para recuperar esa copia desde cero, verifica primero que tienes el fichero en el anfitrión. make pg-clean borra el volumen y su base. Después del arranque tendrás una base bigdata nueva en la que cargar el SQL:

make pg-clean
make pg-up
make pg-restore

Sin Make, el recorrido equivalente es:

docker compose -f postgres/compose.yaml down -v
docker compose -f postgres/compose.yaml up -d --wait
docker cp trabajo/bigdata-m01.sql postgres-vector:/tmp/bigdata-m01.sql
docker exec postgres-vector psql -U alumno -d bigdata -v ON_ERROR_STOP=1 -q -f /tmp/bigdata-m01.sql

Si has restaurado la copia, entra de nuevo en psql, repite el recuento inicial y sal con \q. Al terminar, detén el servicio desde modulo01/:

make pg-down

Sin Make, usa docker compose -f postgres/compose.yaml down. Al volver a arrancar, PostgreSQL abrirá la misma base desde el volumen.