Cuestionario: Deep Postgres: Parte 2

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)

Aunque SERIAL sigue usándose comúnmente, la forma SQL Standard 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.

Ejemplos de extensiones de PostgreSQL al ANSI SQL:

• cláusula RETURNING.
• ILIKE para coincidencias sin distinción de mayúsculas.
• tipos y operaciones de arreglos.
• tipos JSON/JSONB.

El estándar ANSI SQL original incluye:

• WITH (expresiones de tabla comunes).
• LIKE para coincidencias de patrones.
• joins LATERAL.
• funciones de ventana.

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;

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.

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!

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

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.

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! 😅

Este truco se llama Partial Index.

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

Postgres solo usará el índice cuando la condición status = 'active' esté presente en la consulta.

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

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)

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;