Lo que alguien me hubiera gustado que me dijera sobre Postgres

 (challahscript.com)
7 puntos por GN⁺ 2024-11-13 | 2 comentarios | Compartir por WhatsApp
  • La documentación oficial de Postgres es excelente, pero el PDF de Postgres 17 tiene 3,200 páginas, así que a una persona principiante le cuesta aprender solo con la documentación todo sobre diseño de esquemas, comportamiento de SQL y trampas operativas antes de usarlo en trabajo real
  • Salvo que haya una razón especial, conviene normalizar los datos, y la desnormalización para mejorar el rendimiento de lectura implica asumir costos como inconsistencias y mayor complejidad de escritura
  • Las palabras clave de SQL no distinguen mayúsculas de minúsculas, pero NULL se parece más a “desconocido”, así que compararlo como el null de un lenguaje general puede dar resultados inesperados
  • Con solo usar bien el pager, \x, .psqlrc, \pset null, el autocompletado, los comandos con barra invertida y \copy en psql, mejora mucho la legibilidad de la salida, la exploración y la exportación a CSV
  • Los índices, locks, transacciones y JSONB son potentes, pero si no se entienden los planes de consulta y las restricciones operativas, pueden causar degradación de rendimiento o problemas de disponibilidad

Contexto que conviene conocer antes de la enorme documentación oficial

  • La documentación oficial de Postgres, según la versión actual 17, tiene 3,200 páginas si se imprime en PDF tamaño carta US letter, y 3,024 páginas en A4
  • Hay mucho conocimiento práctico útil que conviene saber antes de usar Postgres, y aunque parte también aplica a otros DBMS SQL, su alcance no siempre es totalmente claro

Normaliza los datos por defecto

  • La normalización es el proceso de eliminar datos duplicados o innecesarios en el esquema de una base de datos
  • Si guardas user_email directamente en la tabla documents, cuando un usuario cambie su correo habrá que actualizar todas las filas de documentos de ese usuario
    • En cambio, puedes hacer que cada fila de documents apunte a una fila de otra tabla como users mediante una clave foránea user_id
  • No hace falta memorizar todas las formas normales como la “1st normal form”, pero el proceso general de normalización suele llevar a esquemas más fáciles de mantener
  • La desnormalización consiste en guardar datos duplicados para leer más rápido sin recalcular cierta información cada vez
    • En una app de turnos de empleados, en lugar de calcular cada vez las horas acumuladas del año sumando toda la duración de los turnos, puedes calcularlas y guardarlas periódicamente o cuando cambie el horario trabajado
    • Esos datos pueden estar dentro de Postgres o en una capa de caché como Redis
  • La desnormalización casi siempre tiene un costo, y los más comunes son la posibilidad de inconsistencias de datos y el aumento en la complejidad de escritura

Consejos de “no hagas esto” del proyecto Postgres

  • La wiki oficial de Postgres tiene una lista de “Don’t do this”
  • No pasa nada si no entiendes todos los puntos, y si no entiendes alguno, probablemente también sea menos probable que cometas ese error
  • Vale la pena recordar especialmente estos consejos

Comportamientos de SQL que suelen confundir

  • Las palabras clave de SQL no tienen que ir en mayúsculas

    • Las palabras clave de SQL no distinguen mayúsculas de minúsculas
    • Las siguientes consultas significan lo mismo
    SELECT * FROM my_table WHERE x = 1 AND y > 2 LIMIT 10;
select * from my_table where x = 1 and y > 2 limit 10;
SELECT * from my_table WHERE x = 1 and y > 2 LIMIT 10;
    • Esta característica no es exclusiva de Postgres

  • NULL es distinto del null/nil de los lenguajes generales

    • En SQL, NULL se parece más a “desconocido” que al null o nil de un lenguaje de programación general
    • NULL = NULL no devuelve true, sino NULL
    • En una comparación donde uno de los lados es NULL, la mayoría de las veces el resultado también es NULL
    • Para comparar con NULL, hay que usar estas operaciones
      • x IS NULL: true si x es NULL
      • x IS NOT NULL: true si x no es NULL
      • x IS NOT DISTINCT FROM y: parecido a x = y, pero trata NULL como un valor normal
      • x IS DISTINCT FROM y: parecido a x != y/x <> y, pero trata NULL como un valor normal
    • La cláusula WHERE solo devuelve filas cuando la condición es true
      • SELECT * FROM users WHERE title != 'manager' no devuelve las filas donde title es NULL
      • Eso se debe a que el resultado de NULL != 'manager' es NULL
    • COALESCE devuelve el primer valor que no sea NULL entre varios argumentos
    COALESCE(NULL, 5, 10) = 5
COALESCE(2, NULL, 9) = 2
COALESCE(NULL, NULL) IS NULL

Cómo sacarle más provecho a psql

  • Mejorar la legibilidad de la salida

    • Si al consultar una tabla con muchas columnas o valores largos la salida es difícil de leer, puede que el pager esté desactivado
    • El pager del terminal permite desplazarte por textos largos o tablas de psql dentro del viewport
    • Para tablas con muchas columnas, puedes activar el expanded mode con \pset expanded o \x
    • Si quieres usarlo por defecto, agrega \x a ~/.psqlrc en tu directorio home

  • Hacer más clara la salida de NULL

    • La configuración predeterminada no muestra claramente cuándo un valor es NULL
    • En psql puedes definir la cadena que se mostrará para NULL
    \pset null '[NULL]'
    • También puedes usar cadenas Unicode, y si quieres dejarlo como valor predeterminado, agrega el mismo comando a ~/.psqlrc

  • Aprovechar el autocompletado y los comandos con barra invertida

    • psql soporta autocompletado como una consola interactiva
    • Si escribes parte de una palabra clave o del nombre de una tabla y presionas Tab, puede completar el resto
    • Algunos comandos útiles con barra invertida son
      • \?: lista de todos los shortcuts
      • \d: muestra relaciones, es decir, tablas y secuencias, junto con sus propietarios
      • \d+: agrega tamaño y algunos metadatos a \d
      • \d table_name: muestra el esquema de la tabla, tipos de columnas, si aceptan null, valores por defecto, índices y restricciones de clave foránea
      • \e: edita la consulta en el editor predeterminado definido en la variable de entorno $EDITOR
      • \h SQL_KEYWORD: muestra la sintaxis de esa palabra clave SQL y un enlace a la documentación

  • Exportar a CSV y usar alias en SELECT

    • Puedes guardar el resultado de una consulta en CSV con \copy
    \copy (select * from some_table) to 'my_file.csv' CSV
    • Para incluir los nombres de las columnas en la primera línea, agrega la opción HEADER
    \copy (select * from some_table) to 'my_file.csv' CSV HEADER
    • \copy puede evitar los privilegios elevados que requiere la sentencia más estándar COPY
    • A las columnas de salida de SELECT se les puede poner alias con AS
    SELECT vendor, COUNT(*) AS number_of_backpacks
FROM backpacks
GROUP BY vendor
ORDER BY number_of_backpacks DESC;
    • En GROUP BY y ORDER BY puedes referenciar los números de columna según el orden en que aparecen después de SELECT
    SELECT vendor, COUNT(*) AS number_of_backpacks
FROM backpacks
GROUP BY 1
ORDER BY 2 DESC;
    • Esta forma abreviada es útil, pero conviene no usarla en consultas que se despliegan a producción

Los índices no siempre se usan solo por agregarlos

  • Índices y planes de consulta

    • Un índice es una estructura de datos que funciona como un directorio de atajos para encontrar filas de una tabla según ciertos campos
    • El índice más común es el B-tree, y funciona para condiciones de igualdad exacta como WHERE a = 3 y de rango como WHERE a > 5
    • No se puede indicarle directamente a Postgres que use un índice específico
    • Postgres predice si un índice será más rápido que leer toda la tabla con un sequential scan basándose en estadísticas que mantiene para cada tabla
    • Si antepones EXPLAIN a SELECT ... FROM ..., puedes ver el plan de consulta de cómo Postgres ejecutará la consulta
    • Para leer planes de consulta, puedes consultar la guía de EXPLAIN ANALYZE de thoughtbot, la documentación de pganalyze, la documentación oficial y explain.depesz.com

  • Tablas pequeñas e índices multicolumna

    • En tablas con pocas filas, como una base local de desarrollo, un índice puede no ayudar mucho
    • Si hay unas 100 filas, Postgres puede decidir que un sequential scan es más rápido que usar un índice
    • Postgres soporta índices multicolumna
    CREATE INDEX CONCURRENTLY ON tbl (a, b);
    • Una condición como WHERE a = 1 AND b = 2 puede ser más rápida que tener índices separados para a y b
    • Eso se debe a que puede recorrer un solo B-tree y combinar eficientemente las condiciones de búsqueda
    • Un índice (a, b) también hace rápidas las consultas que filtran solo por a, casi igual que un índice exclusivo sobre a
    • Consultas como WHERE b = 5 pueden acelerarse, pero quizá no de la mejor manera
      • Como el índice está ordenado primero por a y luego por b, tiene que pasar por todos los valores de a para encontrar b
    • Si necesitas consultar varias combinaciones de columnas, es común tener tanto un índice (a, b) como otro solo sobre b
    • Según el caso, también puedes depender de índices individuales sobre a y b

  • Para prefix match, usa text_pattern_ops

    • Puede que guardes directorios jerárquicos con el enfoque de materialized path y necesites encontrar todos los descendientes que empiecen con un prefijo dado
    SELECT * FROM directories WHERE path LIKE '/1/2/3/%'
    • Aunque crees un índice B-tree normal sobre la columna path, puede que esa consulta no lo use
    CREATE INDEX CONCURRENTLY ON directories (path);
    • Para permitir el ordenamiento por caracteres necesario en prefix match o pattern match, hay que especificar una operator class
    CREATE INDEX CONCURRENTLY ON directories (path text_pattern_ops);

Problemas operativos causados por locks y transacciones

  • Los locks en Postgres

    • Un lock o mutex es un mecanismo que permite que solo un cliente haga una operación riesgosa a la vez
    • En una base de datos, las actualizaciones de objetos como row, table o view deben completarse totalmente o fallar totalmente, y para evitar que operaciones concurrentes dejen estados parciales, se adquieren locks sobre los objetos relacionados
    • Los niveles de lock de tabla en Postgres van de menos restrictivos a más restrictivos
      • ACCESS SHARE: SELECT
      • ROW SHARE: SELECT ... FOR UPDATE
      • ROW EXCLUSIVE: UPDATE, DELETE, INSERT
      • SHARE UPDATE EXCLUSIVE: CREATE INDEX CONCURRENTLY
      • SHARE: CREATE INDEX, sin CONCURRENTLY
      • ACCESS EXCLUSIVE: muchas formas de ALTER TABLE, ALTER INDEX
    • En una misma tabla, las siguientes operaciones pueden ejecutarse o tienen que esperar
      • UPDATE durante SELECT: sí se puede
      • UPDATE durante CREATE INDEX CONCURRENTLY: sí se puede
      • SELECT durante CREATE INDEX: sí se puede
      • SELECT durante ALTER TABLE: por lo general espera
      • ALTER TABLE durante SELECT: por lo general espera
    • Algunas formas de ALTER TABLE pueden requerir locks más débiles; para los detalles completos se puede consultar la documentación oficial sobre locks explícitos y esta guía de conflictos de locks por operación

  • ALTER TABLE lento y colas de locks

    • Si ALTER TABLE tarda mucho, también puede bloquear SELECT sobre esa misma tabla
    • Si se trata de una tabla crítica como users, que todas las solicitudes de una app web consultan, las peticiones pueden quedar esperando, hacer timeout y devolver 503
    • Causas comunes de un ALTER TABLE lento
      • agregar una columna con un default no constante
      • cambiar el tipo de una columna
      • agregar una restricción de unicidad
    • Desde Postgres 11, ya se corrigió el problema por el que cualquier default al agregar una columna volvía lenta la operación; el caso problemático puede ser un default no constante
    • Aunque ALTER TABLE en sí sea rápido, no se ejecuta hasta conseguir el lock
      • Si hay un SELECT lento ejecutándose en un dashboard interno antiguo, ALTER TABLE tendrá que esperar
    • Como los locks de Postgres forman una cola, las consultas posteriores sobre la misma tabla que lleguen detrás de un ALTER TABLE en espera también pueden quedar bloqueadas
    • Ese mismo escenario se explora con más detalle en Migrations and exclusive locks

  • Las transacciones largas también son peligrosas

    • Una transacción agrupa varias sentencias de base de datos bajo la lógica de todo o nada; comienza con BEGIN y termina con COMMIT
    • Los cambios hechos dentro de una transacción no son visibles para otros clientes y solo se publican en la base de datos al hacer COMMIT
    • Es ideal para operaciones como una transferencia bancaria, donde la disminución del saldo en una cuenta y el aumento en otra deben tener éxito juntos o revertirse juntos
    • Si una transacción obtiene locks, los conserva hasta COMMIT
    • Si después de BEGIN actualizas una row específica con UPDATE y te alejas de la terminal, otro cliente que intente hacer DELETE sobre esa row se quedará detenido hasta que la transacción haga commit
    • Mantener transacciones abiertas más tiempo del necesario puede bloquear consultas o actualizaciones de otros clientes

JSONB es una herramienta filosa

  • Problemas de rendimiento y esquema en JSONB

    • JSONB es flexible, pero mal usado tiene desventajas importantes
    • Postgres no rastrea estadísticas de columnas JSONB, así que una consulta de igualdad sobre una sola columna JSONB puede ser mucho más lenta que una consulta equivalente sobre columnas normales
    • Un caso muestra un ejemplo donde JSONB es 2000 veces más lento
    • En una columna JSONB puede entrar prácticamente cualquier cosa, lo cual es poderoso, pero ofrece pocas garantías sobre la estructura
    • En una tabla normal puedes mirar el esquema y prever el resultado de una consulta, pero con JSONB no siempre sabes si los nombres de key están en camelCase o snake_case, o si un estado es boolean o enum
    • Las propiedades de tipado estático que sí tienen los datos normales de Postgres no se aplican de la misma forma a JSONB

  • Lo incómodo de comparar tipos en JSONB

    • Si quieres encontrar filas donde el campo brand dentro de la columna JSONB data de la tabla backpacks sea JanSport, la siguiente consulta no funciona
    select * from backpacks where data['brand'] = 'JanSport';
    • Postgres espera que el tipo del lado derecho de la comparación coincida con el del lado izquierdo, y el lado derecho debe ser un documento JSON válido
    • Un documento JSON debe ser un objeto, arreglo, string, número, boolean o null, así que JanSport por sí solo no es JSON válido
    • La consulta correcta consiste en comparar con un string JSON o convertir el lado izquierdo a text de Postgres
    select * from backpacks where data['brand'] = '"JanSport"';

select * from backpacks where data['brand'] = '"JanSport"'::jsonb;

select * from backpacks where data->>'brand' = 'JanSport';
    • El NULL de SQL y el null de JSONB se comportan distinto
      • 'null'::jsonb = 'null'::jsonb devuelve true, pero NULL = NULL devuelve NULL
    • JSONB tiene muchos operadores y funciones dedicados, así que no es fácil memorizarlos todos de una sola vez
    • En Postgres existen tanto JSON, que guarda el valor JSON como texto, como JSONB, que lo convierte a un formato binario eficiente
    • JSONB tiene ventajas como la posibilidad de indexarlo, y el formato JSON puede verse como un caso más especial

Lecturas relacionadas

2 comentarios

 
bbulbum 2024-11-19

Definitivamente tendré que leer alguna vez lo que no se debe hacer.
 
GN⁺ 2024-11-13
Opiniones de Hacker News

  • PostgreSQL en general distingue entre mayúsculas y minúsculas, pero escribir las palabras clave de SQL en mayúsculas suele ser un intento de mejorar la legibilidad mediante reconocimiento visual de patrones.
    No es estrictamente necesario, pero si tuviera que depurar consultas de otra persona, creo que las pasaría por un prettifier para revisar rápidamente la definición sin trabarme con detalles menores de la forma de la sintaxis.
    Igual que al formatear código en otros lenguajes, una estructura visual como la indentación consistente reduce el tiempo dedicado a entender las partes obvias y permite enfocarse en lo importante.
    Eso sí, realmente detesto que se mezclen mayúsculas y minúsculas en identificadores como actuallyUsingCaseInIdentifiers, y no quiero ver columnas que requieren comillas dobles para revisarlas desde la CLI.

    • Los identificadores en mayúsculas parecen bloques intercambiables, por lo que ralentizan la lectura frente a la forma de las palabras que tienen las minúsculas.
    • Al trabajar con SQL de forma interactiva, conocer esta distinción es bastante útil.
      Si voy a escribir rápido una consulta temporal que nadie verá y luego descartarla, no me preocupo por las mayúsculas/minúsculas, pero el SQL que se commitea al repositorio lo escribo con los comandos en ALL CAPS.
    • Entiendo que las mayúsculas cumplían la función de resaltado de sintaxis en pantallas en blanco y negro.
      Ahora que hay color ya no hacen falta, pero es un recuerdo viejo y no tengo material que lo respalde.
    • PostgreSQL convierte los identificadores a minúsculas, mientras que el estándar los convierte a mayúsculas, así que rompe el estándar en cuanto al manejo de mayúsculas/minúsculas.
      Aun así, no se deben mezclar identificadores entrecomillados y no entrecomillados, y las consultas a estructuras internas tampoco están demasiado estandarizadas, así que no tiene mucha importancia.
    • Me interesa saber recomendaciones de prettifiers o linters para SQL.

  • No conocía la sección “don’t do this” de la wiki de PostgreSQL, y es bastante útil: https://wiki.postgresql.org/wiki/Don%27t_Do_This

    • Si estas funcionalidades son trampas tan fáciles, me pregunto por qué no las marcan como obsoletas.
      Por ejemplo, en esquemas nuevos parecería correcto desactivar funciones como la herencia de tablas y exigir una configuración deliberadamente complicada para volver a habilitarlas.
    • Me recuerda a SQL Anti-patterns, y creo que es un libro que debería leer cualquiera que trabaje con bases de datos.
    • Me hizo replantear algunos hábitos que aprendí del lado de MySQL.

  • Mucho de lo que aparece aquí no aplica solo a PostgreSQL.
    Pasa con cosas como el comportamiento extraño de NULL y el orden de las columnas en los índices; en particular, la interacción entre NULL e índices/restricciones unique tampoco es intuitiva en MySQL.
    Por ejemplo, si en una tabla de usuarios email no permite NULL y username sí permite NULL, y se define una restricción unique sobre (email, username), se puede insertar varias veces el mismo email con username en NULL. Esto ocurre porque NULL no es igual a otro NULL.

    • Como referencia, desde PostgreSQL 15 se puede influir en este comportamiento con NULLS [NOT] DISTINCT en restricciones e índices unique.
      https://www.postgresql.org/docs/devel/sql-createtable.html#S...
    • Creo que este valor por defecto es práctico.
      Los casos de uso que necesitan el comportamiento opuesto son mucho más raros.

  • No alcanza con decir “normaliza los datos si no hay una buena razón para no hacerlo” y dejarlo ahí.
    Incluso la página enlazada por el autor enumera 11 formas normales, incluida la forma no normalizada, y la mayoría ni siquiera sabe qué son; de esas, 7 prácticamente nunca se usan.
    No hay que hacer que la gente salga a perseguir formas normales más altas.

    • Aun así, el autor en general agregó un párrafo explicando a qué se refería, y creo que la dirección es correcta.
      En un proyecto al que me pasé hace poco también tuve que corregir varios problemas de este tipo, y casi nunca hay motivos para duplicar datos.
    • Si este artículo apunta a principiantes, cuando no se está seguro la respuesta casi siempre es la tercera forma normal.
    • La regla general es normalizar tanto como sea posible y luego desnormalizar hasta obtener el rendimiento necesario.

  • El primer consejo es ejecutar VACUUM todos los días.
    Cuando empezamos no sabía esto, así que nunca hicimos VACUUM en la base de datos de reddit, y un día no nos quedó otra que ejecutarlo; reddit estuvo caído casi todo un día mientras esperábamos a que terminara.

    • Parece que no tenían autovacuum.
      Para la escala de reddit, sorprende que no se hayan agotado primero los ID de transacción.

  • Me gustaría que los desarrolladores prestaran más atención a la normalización y dejaran de meter todo en columnas JSONB.

    • Mucho antes de que las bases de datos pudieran almacenar JSON estructurado, los desarrolladores junior ya tenían debates de escritorio bastante intensos sobre cuál era el nivel adecuado de normalización.
      Los desarrolladores con más experiencia sabían que la respuesta correcta era no duplicar nada salvo las claves, y desnormalizar solo muy a regañadientes.
      Luego aparecieron bases de datos como Mongo, que ofrecían “algo parecido a una base de datos” donde la normalización era difícil o no tenía sentido, alentando a esos juniors; como resultado, durante un tiempo florecieron diseños de bases de datos horribles y torres de basura imposibles de mantener.
      Ahora el péndulo volvió y redescubrimos las ventajas de las bases de datos normalizadas, pero las columnas JSON siguen siendo una vía de escape donde pueden crecer malas prácticas.
    • Hay dos razones para usar columnas JSONB.
      La primera es almacenar JSON. Cuando un servidor web llama a una API de terceros, guardar la respuesta original de la API en una columna JSONB y procesarla desde ahí deja un registro auditable para depurar problemas provenientes de esa API.
      La segunda es almacenar tipos suma (sum types). Que SQL no soporte tipos suma puede considerarse una de las mayores deficiencias al modelar datos en bases de datos SQL.
      Hay varias soluciones alternativas, y “simplemente meterlo en una columna JSONB y validarlo en la aplicación” es una de ellas, pero ninguna es especialmente buena.
    • Incluso prestando atención a la normalización, muchas veces uno termina con un cajón de JSONB misceláneo.
      Mientras no se escriban malas consultas dentro de JSONB en lugar de promover esos valores a columnas separadas, no lo veo como un gran problema en sí mismo.
    • La mayoría de los desarrolladores que usan estas herramientas hoy, en la práctica, están construyendo su propio sistema de gestión de bases de datos y delegan solo la persistencia a otro DBMS.
      Esto se debe a que, si logran satisfacer con éxito los requisitos de persistencia, no hay una presión fuerte para pensar en un buen diseño.
      Es discutible si tiene sentido construir otro DBMS encima de un DBMS, pero en cualquier caso así están las cosas hoy.
    • Para que este enfoque funcione bien, se necesita un procedimiento de migración de esquemas que incluya la capacidad de revertir cambios de esquema.
      Si una columna nueva arruina el rendimiento o causa problemas, debería poder revertirse.
      Si hay herramientas CLI involucradas, también hay que manejar cuánto downtime se puede tolerar, si es posible una actualización de versión sincronizada en toda la empresa o si se van a soportar tanto el esquema antiguo como el nuevo durante un tiempo.
      Si la base de datos no forma parte del producto principal del equipo, puede que todo esto falte.

  • Escribí este texto para ayudar a principiantes: https://tomcam.github.io/postgres/

  • El artículo es realmente bueno, y no sabía que la documentación de PostgreSQL tuviera 3200 páginas.
    Lo he estado usando desde hace un tiempo y voy aprendiendo cuando lo necesito; también me gusta bastante la documentación oficial, y me gusta leer artículos relacionados cuando necesito un tema específico.
    Creo que al lector le ayudaría que el autor agregara en https://challahscript.com/what_i_wish_someone_told_me_about_... que un índice de columnas (b, a) funciona bien al consultar solo por b.
    Está algo implícito cuando se habla de consultar solo por a, pero no estaría mal hacerlo más explícito.
    La parte de JSON/JSONB no la miré mucho porque casi no lo uso.

  • Pensando en las consultas SQL ridículas que he visto en entornos reales, creo que sería bueno empezar por leer el paper de Codd y entender qué es el modelo relacional.
    Tiene apenas 11 páginas, y solo con leerlo se reduciría el sufrimiento en este mundo.

  • Casi todo este artículo también aplica a otras bases de datos MVCC como MySQL.
    Los detalles pueden variar, pero MySQL también sufre con transacciones largas, toma bloqueos de metadatos durante ALTER y tiene problemas divertidos similares.