Dan Levy

Cuestionario: Deep Postgres: Parte 2

¿Eres un SQL Steele? ¿O un SQL Softie?

Parte 2 de 2. Volver a la Parte 1

¡Bienvenido a la Parte 2 de mi cuestionario avanzado de PostgreSQL!

Este cuestionario combina características conocidas y menos documentadas de PostgreSQL y sus trampas: JOIN, NULL, ANSI SQL, Transacciones, Internos, Indexación, Muestreo de datos… ¡y mucho más!

¡Buena suerte! 🍀

1. 

Calentamiento: Joins

¿Cuál no es ❌ una expresión JOIN válida de PostgreSQL?

Sabías de CROSS JOIN, ¿verdad?

¿Te hizo pensar el JOIN ALL en otra operación, UNION ALL?

La respuesta aquí es JOIN ALL ❌.

Los tipos de JOIN correctos son:

  • INNER JOIN (JOIN por defecto)
  • LEFT JOIN (o LEFT OUTER JOIN)
  • RIGHT JOIN (o RIGHT OUTER JOIN)
  • FULL JOIN (o FULL OUTER JOIN)
  • CROSS JOIN (el producto cartesiano, a menos que una cláusula WHERE posterior lo filtre)

2. 

Columnas de identidad

¿Cuál es la sintaxis del (estándar SQL) para una columna IDENTITY auto‑incremental en PostgreSQL (v10+)?

Aunque SERIAL sigue usándose comúnmente, la forma del estándar SQL es:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • id INT IDENTITY(1,1) es sintaxis de SQL Server.
  • id INT AUTO_INCREMENT es sintaxis de MySQL.
  • id INTEGER AUTO_INCREMENT PRIMARY KEY es sintaxis de MySQL.

Esto tiene varias ventajas sobre SERIAL:

  • Cumple con el estándar SQL
  • Mejor gestión de la propiedad de la secuencia
  • Semántica más clara en inserciones manuales
  • Funciona mejor con replicación

SERIAL es en realidad solo una abreviatura para crear una secuencia y establecer un valor por defecto, lo que puede provocar problemas con volcados y restauraciones.

3. 

ANSI SQL

¿Qué cláusula documenta PostgreSQL como una extensión del estándar SQL?

PostgreSQL documenta su cláusula RETURNING como una extensión del estándar SQL. ILIKE es otra extensión de PostgreSQL.

No clasifiques todo el soporte de arrays o JSON como no estándar: SQL ha estandarizado funciones en estas áreas, mientras PostgreSQL también ofrece tipos y operadores propios, como jsonb.

Las distintas ediciones del estándar SQL han incorporado:

  • WITH (expresiones de tabla comunes).
  • LIKE para coincidencia de patrones.
  • Uniones LATERAL.
  • Funciones de ventana.

4. 

Transacciones

Excepto por “Depende de los efectos secundarios” ¿Qué ocurre después de un error?

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

Cuando ocurre un error aquí:

  • La transacción completa se revierte
  • No se confirman cambios
  • La transacción se marca como fallida

Para confirmar parcialmente:

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

5. 

Internos de SQL

¿Qué condición ON puede usar PostgreSQL para un HASH JOIN?

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

Un hash join es posible con una condición de igualdad (=) en la cláusula ON.

  • = es la forma de predicado clave para los hash joins
  • > y >= no son predicados de hash join
  • HASH JOIN no es una sintaxis válida en PostgreSQL

El planificador aún puede elegir un bucle anidado o un merge join según el tamaño de la tabla, las estadísticas, los índices y la configuración de memoria.

6. 

Indexación

¿Qué índice es mejor para esta consulta?

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

Para mí, lo sorprendente aquí es que un índice de varias columnas puede usarse incluso cuando el orden de las columnas en el texto de la consulta no coincide con la definición del índice.

Nota: Con los índices B‑tree, la columna líder importa. Dado que esta consulta filtra por grade_level y last_name, un índice que comience con grade_level y luego last_name es adecuado.

Cuanto más rápido SQL pueda seleccionar menos filas, mejor será el rendimiento.

Por ejemplo, si tienes una tabla task y sabes que status es más selectivo que created_at, podrías elegir un índice que empiece por status para una consulta como status = 'done' AND created_at > '2024-01-01'. No importa el orden de las condiciones en el texto del WHERE; lo que cuenta es el orden de las columnas del índice y la forma de los predicados.

Un índice B+Tree puede usarse para:

  • La columna líder, o un prefijo izquierdo de columnas.
  • Operadores como =, >, <, BETWEEN e IN, según la forma de la consulta.
  • Coincidencia de prefijo, LIKE 'prefix%'.

Gracias a u/mwdb2 por señalar un error anterior en esta explicación!

7. 

Comillas 1

¿Cuál es correcta para nombres de columnas sensibles a mayúsculas?

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

En PostgreSQL:

  • Las comillas dobles (") se usan para identificadores (nombres de tablas, nombres de columnas)
  • Las comillas simples (') se usan para literales de cadena

Buenas prácticas:

  • Evita mayúsculas mezcladas o espacios en los nombres
  • Usa snake_case para los identificadores
  • Sólo usa identificadores entre comillas cuando sea absolutamente necesario

8. 

Comillas 2

¿Cómo especificas un nombre de columna que contiene un punto?

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

PostgreSQL usa comillas dobles para citar identificadores:

  • Comillas dobles "..." para identificadores (nombres de columnas, nombres de tablas)
  • Comillas simples '...' para literales de cadena
  • La forma first.name se interpreta como table_name.column_name

Sin comillas, un punto en un nombre de columna se interpretaría como separador de esquema/tabla! Además, todos los identificadores sin comillas se convierten a minúsculas por defecto.

Para evitar sorpresas, lo mejor es usar snake_case y evitar caracteres especiales.

9. 

Muestreo de datos

¿Cuál es la sintaxis válida para muestrear aleatoriamente el 10% de los estudiantes?

Esta es complicada! Tiene alrededor de 6 trampas, algunas incluyen:

  1. Descripción de alto nivel + fragmentos de sintaxis. Prueba cómo adaptas y ensamblas información incompleta.
  2. Requiere conocer la sintaxis de TABLESAMPLE, ya que ninguna de las opciones de TABLESAMPLE usa la sintaxis correcta! (¡Siniestra! Lo sé! Sigue adelante, ¡creo en ti!!! ❤️)
  3. Los ROW_NUMBER() OVER (ORDER BY RAND()) y ORDER BY RANDOM() > 10 son más pistas falsas. Parecen correctos, pero no lo son. RAND() no existe. Y RANDOM() > 10 es una expresión booleana que siempre será falsa porque RANDOM() siempre está en el rango 0.0-1.0. Otro problema, el ORDER BY RANDOM() > 10 también ordenaría por un valor booleano, no por un número aleatorio. Pero ese > 10 suena como si fuera un porcentaje, ¿no?
  4. BERNOULLI es válido, sin embargo SAMPLETABLE no lo es.
  5. WHERE RANDOM() >= 0.1 es una trampa! RANDOM() devuelve un valor en el rango 0.0 <= x < 1.0, así que >= 0.1 devolvería aproximadamente el 90% de las filas, no el 10%.

En cuanto al método TABLESAMPLE, PostgreSQL soporta dos métodos de muestreo:

  • BERNOULLI: Cada fila tiene igual probabilidad
  • SYSTEM: Muestreo a nivel de bloque (más rápido pero menos aleatorio)

Para aprender más sobre muestreo, revisa este artículo de Render.

Ejemplo:

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);


-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

También vale mencionar que TABLESAMPLE ha sido reportado como no muy aleatorio. Si necesitas una muestra fija más aleatoria, considera usar ORDER BY RANDOM() LIMIT 10. Pero ten en cuenta que esto devuelve 10 filas, no el 10%, y puede ser lento en tablas grandes.

Existen también extensiones de terceros que ofrecen mejores métodos de muestreo aleatorio, pero eso está fuera del alcance de este ejercicio! 😅

10. 

Indexación

¿Qué hace esta invocación?

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

Este recurso se llama índice parcial (Partial Index).

Son útiles para reducir el uso de disco y acelerar consultas cuando solo se consultan un subconjunto de filas.

El planificador puede considerar este índice si puede demostrar que las condiciones de la consulta implican status = 'active'. No siempre se requiere una coincidencia textual exacta, pero esas comprobaciones son limitadas: una condición parametrizada puede no demostrar el predicado al planificar. Que el índice sea elegible no garantiza que se elija.

Recordatorio: Postgres no soporta ‘query hints’ como otros SGBD. El planificador de consultas decidirá cuándo usar cualquier índice apropiado.

11. 

Comportamiento de NULL

¿Cuántas filas devolverá esto?

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

Vale, esto fue un poco complicado. El truco es que x = NULL nunca coincidirá con ninguna fila, y SIEMPRE devolverá una fila con un recuento de 0.

Dado que NULL es un valor desconocido, no se puede comparar con nada, ni siquiera consigo mismo.

La forma correcta de comprobar valores NULL es con IS NULL.

Ejemplo:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)


postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

12. 

Rendimiento

¿Qué hace EXPLAIN ANALYZE con las sentencias UPDATE?

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

EXPLAIN ANALYZE en realidad ejecuta la consulta! Ten cuidado al analizar una operación de escritura, específicamente sentencias UPDATE, DELETE y INSERT. Puede que necesites ejecutar operaciones de limpieza después de cada EXPLAIN ANALYZE para obtener resultados repetibles.

Mejores prácticas:

  • Usa EXPLAIN (sin ANALYZE) para UPDATE/DELETE/INSERT
  • Envuelve las modificaciones en transacciones:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

¡Bien hecho! ¡Has profundizado en varias áreas de PostgreSQL! 🐘

Espero que hayas aprendido algo nuevo, o al menos que tengas una puntuación de la que presumir. 🏆

¿Quieres más emociones en la vida? Consulta mi Colección de Quiz para diversión sin fin*.

Loading comments...