SincroDev El Viaje de una Consulta SQL: Del código a los datos y vuelta
Escribes SELECT * FROM users WHERE email = 'ana@ejemplo.com' y presionas Enter. Milisegundos después, tienes los datos. Pero entre tu consulta y esos resultados, el motor de base de datos ejecutó un proceso fascinante de análisis, optimización y búsqueda. Veamos cómo.
Capa 1: El Cliente Envía
Tu aplicación (o cliente SQL) envía la consulta al servidor de base de datos:
cursor.execute("SELECT * FROM users WHERE email = %s", ['ana@ejemplo.com'])El driver de base de datos:
- Serializa la consulta en el protocolo del motor (ej: protocolo de PostgreSQL)
- Envía por TCP al servidor (típicamente puerto 5432 para Postgres)
- Espera la respuesta
┌─────────────────┐ TCP:5432 ┌─────────────────┐
│ Tu App │ ───────────────────────→ │ PostgreSQL │
│ (cliente) │ │ (servidor) │
└─────────────────┘ └─────────────────┘Capa 2: El Parser Analiza
El servidor recibe el texto SQL y lo pasa al Parser (analizador sintáctico):
- Lexer: Divide el texto en tokens
- Parser: Construye un AST (Abstract Syntax Tree)
- Validación sintáctica: ¿Es SQL válido?
"SELECT * FROM users WHERE email = 'ana@ejemplo.com'"
│
▼ Lexer
┌────────┬───┬──────┬───────┬───────┬───┬─────────────────────┐
│ SELECT │ * │ FROM │ users │ WHERE │ = │ 'ana@ejemplo.com' │
└────────┴───┴──────┴───────┴───────┴───┴─────────────────────┘
│
▼ Parser
┌─────────────┐
│ SELECT │
└──────┬──────┘
│
┌────────────┼────────────┐
▼ ▼ ▼
┌────────┐ ┌────────┐ ┌────────┐
│ cols:* │ │ FROM: │ │ WHERE: │
└────────┘ │ users │ │ email= │
└────────┘ └────────┘Si escribes SELEC (sin T), aquí es donde obtienes: syntax error at or near "SELEC".
Capa 3: El Analyzer Valida
El Analyzer (analizador semántico) verifica que la consulta tenga sentido:
- ¿Existe la tabla
users? → Consulta el catálogo del sistema - ¿Existe la columna
email? → Verifica el esquema - ¿Tienes permisos? → Revisa roles y grants
- Resuelve tipos:
emailesVARCHAR, el literal también
┌─────────────────────┐
│ System Catalog │
│ (pg_catalog) │
│ │
AST ───────────────→│ ¿users existe? ✓ │
│ ¿email existe? ✓ │
│ ¿permisos? ✓ │
│ │
└──────────┬──────────┘
│
▼
┌─────────────────────┐
│ Query Tree │
│ (validado) │
└─────────────────────┘Si la tabla no existe: ERROR: relation "users" does not exist.
Capa 4: El Rewriter Transforma
El Rewriter aplica transformaciones automáticas:
- Expande vistas: Si
usersfuera una vista, la reemplaza por su definición - Aplica reglas: Reglas de reescritura definidas
- Row-Level Security: Añade filtros de seguridad si aplica
Vista:
CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';
Consulta:
SELECT * FROM active_users WHERE email = 'ana@ejemplo.com'
│
▼ Rewriter
SELECT * FROM users
WHERE status = 'active'
AND email = 'ana@ejemplo.com'Capa 5: El Planner Optimiza
Aquí está la magia. El Query Planner (optimizador) decide cómo ejecutar la consulta:
- Genera planes candidatos: Diferentes formas de obtener los datos
- Estima costos: Usando estadísticas de las tablas
- Elige el mejor: El de menor costo estimado
¿Qué opciones tiene?
Para SELECT * FROM users WHERE email = 'ana@ejemplo.com':
Opción A: Sequential Scan Leer TODA la tabla, fila por fila, filtrando.
Costo: O(n) donde n = total de filas
Si users tiene 1,000,000 filas → leer 1,000,000 filasOpción B: Index Scan
Si existe un índice en email, usarlo para ir directo.
Costo: O(log n) para encontrar + O(1) para leer
Si users tiene 1,000,000 filas → leer ~20 nodos del índice + 1 filaEl planner consulta estadísticas:
-- PostgreSQL guarda estadísticas en pg_stats
SELECT * FROM pg_stats WHERE tablename = 'users' AND attname = 'email';
-- Información como:
-- - n_distinct: cantidad de valores únicos
-- - null_frac: porcentaje de NULLs
-- - most_common_vals: valores más frecuentes
-- - histogram_bounds: distribución de valoresEl plan elegido
┌─────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────┤
│ Index Scan using idx_users_email on users │
│ Index Cond: (email = 'ana@ejemplo.com') │
│ Estimated rows: 1 │
│ Estimated cost: 8.29 │
└─────────────────────────────────────────────────────────┘Puedes ver el plan con EXPLAIN:
EXPLAIN SELECT * FROM users WHERE email = 'ana@ejemplo.com';
-- O con más detalle:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ana@ejemplo.com';Capa 6: El Executor Ejecuta
El Executor toma el plan y lo ejecuta paso a paso:
Plan: Index Scan en idx_users_email
│
▼
┌─────────────────────────────────────────────────────────┐
│ EXECUTOR │
│ │
│ 1. Abrir índice idx_users_email │
│ 2. Buscar entrada para 'ana@ejemplo.com' │
│ 3. Obtener puntero a la fila (ctid) │
│ 4. Leer la fila de la tabla (heap) │
│ 5. Verificar visibilidad (MVCC) │
│ 6. Devolver resultado │
│ │
└─────────────────────────────────────────────────────────┘Capa 7: El Storage Manager Accede
El Storage Manager es quien realmente toca los datos:
Estructura de almacenamiento
Base de datos en disco:
/var/lib/postgresql/data/
└── base/
└── 16384/ ← Database OID
├── 16385 ← Tabla users (heap)
├── 16386 ← Índice idx_users_email
└── ...El índice B-Tree
Los índices más comunes son B-Tree. Estructura jerárquica:
┌─────────────────────┐
│ Nodo Raíz │
│ [M] ─────── [Z] │
└──────┬────────┬─────┘
│ │
┌────────────┘ └────────────┐
▼ ▼
┌─────────────────┐ ┌─────────────────┐
│ Nodo Interno │ │ Nodo Interno │
│ [A]──[F]──[L] │ │ [N]──[S]──[Y] │
└──┬────┬────┬────┘ └──┬────┬────┬────┘
│ │ │ │ │ │
▼ ▼ ▼ ▼ ▼ ▼
┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐
│Hojas│ │Hojas│ ... │Hojas│ │Hojas│
│ A-E │ │ F-K │ │ N-R │ │ S-X │
└─────┘ └─────┘ └─────┘ └─────┘Buscar ana@ejemplo.com:
- Raíz: ¿‘a’ < ‘M’? → Izquierda
- Interno: ¿‘a’ < ‘A’? No. ¿‘a’ < ‘F’? Sí → Primera hoja
- Hoja: Búsqueda binaria → Encontrado, puntero a fila
Capa 8: Buffer Cache
Antes de ir al disco, se consulta el Buffer Cache (memoria):
┌─────────────────────────────────────────────────────────┐
│ SHARED BUFFERS │
│ (Caché en RAM) │
│ │
│ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │
│ │Página│ │Página│ │Página│ │Página│ │Página│ ... │
│ │users │ │users │ │index │ │index │ │otras │ │
│ │ #1 │ │ #2 │ │ #1 │ │ #2 │ │ │ │
│ └──────┘ └──────┘ └──────┘ └──────┘ └──────┘ │
│ │
│ ¿Página del índice en caché? ──→ SÍ: leer de RAM │
│ NO: leer de disco │
│ │
└─────────────────────────────────────────────────────────┘Si la página está en caché: ~0.1ms Si hay que ir a disco (SSD): ~0.1-1ms Si hay que ir a disco (HDD): ~5-10ms
Capa 9: MVCC y Visibilidad
PostgreSQL usa MVCC (Multi-Version Concurrency Control). Cada fila tiene:
- xmin: Transacción que la creó
- xmax: Transacción que la eliminó (0 si está viva)
Fila en disco:
┌────────┬────────┬───────────────────────────────┐
│ xmin │ xmax │ email = 'ana@ejemplo.com' │
│ 1000 │ 0 │ name = 'Ana García' │
└────────┴────────┴───────────────────────────────┘
El executor verifica:
- ¿Mi transacción puede ver xmin=1000? → ¿Está committed?
- ¿xmax=0? → La fila no fue borrada
- ✓ Fila visibleEsto permite que múltiples transacciones lean sin bloquearse.
Capa 10: Resultado al Cliente
Finalmente, los datos viajan de vuelta:
Executor Cliente
│ │
│ ┌─────────────────────┐ │
│ │ email: ana@... │ │
│ │ name: Ana García │ │
│ │ created_at: ... │ │
│ └─────────────────────┘ │
│ │ │
│ ▼ │
│ Serializa en protocolo│
│ │ │
│ ▼ │
│ TCP:5432 │
└──────────────────────────┤
│
Deserializa │
│
┌──────────▼──────────┐
│ {'email': 'ana@...', │
│ 'name': 'Ana...'} │
└─────────────────────┘El Diagrama Completo
┌─────────────────────────────────────────────────────────────────────┐
│ CLIENTE │
│ cursor.execute("SELECT * FROM users WHERE email = ?", [email]) │
└───────────────────────────────────┬─────────────────────────────────┘
│ TCP
▼
┌─────────────────────────────────────────────────────────────────────┐
│ SERVIDOR POSTGRESQL │
│ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 1. PARSER │ │
│ │ SQL text → Tokens → AST │ │
│ └─────────────────────────────┬───────────────────────────────┘ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 2. ANALYZER │ │
│ │ ¿Tabla existe? ¿Columnas? ¿Permisos? → Query Tree │ │
│ └─────────────────────────────┬───────────────────────────────┘ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 3. REWRITER │ │
│ │ Expande vistas, aplica reglas │ │
│ └─────────────────────────────┬───────────────────────────────┘ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 4. PLANNER │ │
│ │ Genera planes → Estima costos → Elige el mejor │ │
│ │ (Seq Scan vs Index Scan vs ...) │ │
│ └─────────────────────────────┬───────────────────────────────┘ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 5. EXECUTOR │ │
│ │ Ejecuta el plan paso a paso │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │ │
│ │ │ Buffer Cache│ ←→ │ Storage │ ←→ │ Disco │ │ │
│ │ │ (RAM) │ │ Manager │ │ (SSD/HDD) │ │ │
│ │ └─────────────┘ └─────────────┘ └─────────────┘ │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ MVCC: Verificar visibilidad de filas │ │
│ │ │ │ │
│ │ ▼ │ │
│ │ Resultado: filas que cumplen el WHERE │ │
│ └─────────────────────────────┬───────────────────────────────┘ │
│ │ │
└────────────────────────────────┼────────────────────────────────────┘
│ TCP
▼
┌─────────────────────────────────────────────────────────────────────┐
│ CLIENTE │
│ Recibe resultados │
└─────────────────────────────────────────────────────────────────────┘¿Cuánto tiempo toma cada paso?
| Paso | Tiempo típico |
|---|---|
| Parse | 0.01-0.1ms |
| Analyze | 0.01-0.1ms |
| Rewrite | 0.01ms |
| Plan | 0.1-5ms (consultas complejas: más) |
| Execute (caché hit) | 0.1-1ms |
| Execute (disco SSD) | 1-10ms |
| Execute (disco HDD) | 10-100ms |
| Red (local) | < 1ms |
| Total típico | 1-50ms |
Herramientas para inspeccionar
-- Ver el plan de ejecución
EXPLAIN SELECT * FROM users WHERE email = 'ana@ejemplo.com';
-- Ver el plan CON ejecución real
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'ana@ejemplo.com';
-- Ver estadísticas de una tabla
SELECT * FROM pg_stats WHERE tablename = 'users';
-- Ver tamaño de tablas e índices
SELECT pg_size_pretty(pg_table_size('users'));
SELECT pg_size_pretty(pg_indexes_size('users'));
-- Ver hits de caché
SELECT
sum(heap_blks_hit) as cache_hits,
sum(heap_blks_read) as disk_reads,
sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) as ratio
FROM pg_statio_user_tables;Conclusión
Una simple consulta SQL:
- Viaja por TCP al servidor de base de datos
- Se parsea de texto a árbol sintáctico
- Se valida contra el catálogo del sistema
- Se transforma (vistas, reglas)
- Se optimiza eligiendo el mejor plan de ejecución
- Se ejecuta accediendo a índices y tablas
- Consulta caché antes de ir a disco
- Verifica visibilidad con MVCC
- Retorna los resultados al cliente
El motor de base de datos hace un trabajo increíble optimizando cada consulta. Por eso, entender EXPLAIN y cómo funcionan los índices puede marcar la diferencia entre una consulta de 1ms y una de 10 segundos.
Este post es parte de la serie “El Viaje de la Información”, donde exploramos qué sucede realmente cuando interactuamos con nuestros sistemas.