Mis notas sobre el diseño del esquema Postgres de GitLab (2022)

Mis notas sobre el diseño del esquema Postgres de GitLab

• Al revisar el esquema de Postgres de GitLab, quería compararlo con el que diseño yo y aprender buenas prácticas a partir de su definición de esquema.
• GitLab es una plataforma DevOps de código abierto, una alternativa a GitHub, y se puede autoalojar.

Usar el tipo correcto de clave primaria

• Cuando la base de datos es pequeña esto no se nota, pero a medida que crece la clave primaria impacta en el espacio de almacenamiento, la velocidad de escritura y la velocidad de lectura.
• GitLab usa bigserial como tipo de clave primaria en 380 de sus 573 tablas, serial4 en 170 y claves primarias compuestas en las 23 restantes.

Uso de IDs internos y externos

• Es una buena práctica no exponer la clave primaria al mundo exterior.
• GitLab usa tanto ID interno (id) como ID externo (iid) en tablas como issues, ci_pipelines, deployments y epics.

Usar el tipo text y restricciones de validación

• El esquema de GitLab usa tanto character varying(n) como text, pero utiliza más frecuentemente text.
• text no tiene una restricción de longitud, y define las restricciones de longitud con CHECK.

Convenciones de nomenclatura

• Todas las tablas usan plural y utilizan el prefijo del nombre del módulo para proporcionar un namespace.
• Los nombres de tablas y columnas siguen el formato snake_case.

Uso de zonas horarias en timestamps

• GitLab usa tanto timestamp with timezone como timestamp without timezone.
• Utiliza timestamp without timezone para operaciones del sistema y timestamp with timezone para acciones de usuario.

Restricciones de clave foránea

• GitLab usa restricciones de clave foránea en la mayoría de las tablas, aunque hay algunas en las que no las usa, como audit_events, abuse_reports, web_hooks_logs y spam_logs.

Particionamiento de tablas grandes

• GitLab particiona tablas que podrían crecer para mejorar el rendimiento de las consultas.

Soportar búsquedas LIKE con trigrams y gin_trgm_ops

• GitLab usa índices GIN (Generalized Inverted Index) para realizar búsquedas de forma eficiente.

Uso de jsonb

• El esquema de GitLab usa el tipo de dato jsonb en varias tablas.

Otros consejos

• Usa campos de auditoría como updated_at en tablas que pueden modificarse y no los usa en tablas de logs que no se pueden modificar.
• Los enums se guardan como smallint en lugar de character varying, lo que ahorra espacio.

Opinión de GN⁺:

• El diseño del esquema de GitLab ofrece ideas de diseño de base de datos y contiene lecciones importantes para la optimización de esquemas en sistemas de gran escala.
• Como GitLab es de código abierto, estas decisiones de diseño de esquema ofrecen ejemplos prácticos que otros desarrolladores pueden aplicar en sus propios proyectos.
• Lo que se puede aprender del esquema de GitLab es que elementos como la selección del tipo de dato, la estrategia de indexación, el particionamiento y el uso de restricciones de clave foránea impactan de forma importante en el rendimiento y mantenimiento de la base de datos.

[GN⁺]

Comentarios de Hacker News

• Me pregunto por qué hace falta la práctica de no exponer la clave primaria hacia afuera. Si la solicitud de todos modos tiene que estar autenticada, tampoco veo qué valor tiene impedir que se adivine el ID
  Si con solo adivinar un ID se puede hacer algo útil sin autenticación ni autorización, entonces ya hay algo gravemente roto en otra parte, y convendría enfocarse en eso en vez de agregar complejidad innecesaria al esquema. Ocultar inteligencia competitiva para que la competencia no pueda estimar cuántos clientes tienes podría tener cierto valor, pero no parece que GitLab se haya preocupado demasiado por eso. La decisión de GitLab de usar id + iid probablemente estuvo más impulsada por requisitos de rendimiento de consultas que por evitar la adivinación de IDs internos

  • Es cierto, pero que se pueda adivinar el ID puede convertir una vulnerabilidad de seguridad en algo terrible o empeorarla muchísimo
    Si se hubiera expuesto un UUID al usuario, incluso con la misma vulnerabilidad sería mucho más difícil porque el atacante tendría que acertar el UUID, y quizá necesitaría una fuente secundaria. Puede haber filtración de datos, pero habría tiempo para responder y se podría estimar el volumen filtrado. En cambio, con IDs secuenciales el problema puede escalar de inmediato a una filtración total, y convertirse en un incidente masivo que obliga a reportarlo a las autoridades de protección de datos personales. Este tipo de cosas debería ser una defensa en profundidad que idealmente no hiciera falta, pero en la práctica sí existe software terrible que ha sido vulnerado de esta manera
  • Como dice el artículo, esto se acerca más a inteligencia competitiva que a seguridad. Un ID autoincremental simple revela la cantidad total de registros de la tabla o su ritmo de crecimiento
    Si expones la clave primaria id de la tabla de issues, al crear un issue en un proyecto no empezará desde 1, así que es fácil estimar cuántos issues hay en todo GitLab
  • La expresión teatro de seguridad se usa demasiado. La seguridad puede y debe estar compuesta por varias capas, y si una capa como la autenticación se rompe, eso no debería dar acceso fácil a todo lo demás
    Claro, si solo con adivinar un ID se puede hacer algo sin autenticación ni autorización, eso sí es un problema grave. Pero si en ese punto no hay otras capas, el juego ya está perdido. Los bugs no avisan con anticipación, y los bugs sutiles menos. Cuando aparezca uno de esos, agradecerás haber hecho que al menos los IDs no sean adivinables, porque así evitarás una situación en la que también sea fácil acceder a las cuentas de todos los usuarios del sistema
  • También hay bugs en el control de acceso. Los IDs no adivinables hacen mucho más difícil explotar algunos de esos bugs
    Por supuesto, primero hay que concentrarse en garantizar un control de acceso correcto, pero los IDs no adivinables pueden marcar la diferencia entre un desastre terrible y un incidente que apenas se salva. Si UUID no encaja, también se puede usar un ID autoincremental de base de datos y cifrarlo, y con una capa de software adecuada los IDs cifrados funcionan casi de forma automática
  • Es una diferencia menor de terminología, pero esto normalmente se llamaría más bien inteligencia competitiva que “business intelligence”, que suele referirse al uso interno de datos dentro de una empresa. https://en.wikipedia.org/wiki/Competitive_intelligence

• De los 128 millones de repositorios públicos, la mayoría son forks de otros repositorios y solo existen para crear pull requests al repositorio principal, así que salvo error probablemente no tengan issues
  También es muy probable que los proyectos pequeños de juguete o los proyectos abandonados rápidamente no tengan issues o tengan muy pocos. Sin duda hay proyectos con cientos o miles de issues, pero el promedio entre los 128 millones de repositorios seguramente es bastante bajo, así que probablemente siga por debajo del límite de 2 mil millones. Aun así, coincido en que usar un tipo de 4 bytes, o más exactamente 31 bits, en esa tabla es una bomba de tiempo para algunas organizaciones, incluido github.com

  • Incluso hoy sigue por debajo del límite, con 362,107,148 repositorios y 818,516,506 issues y pull requests únicos
    https://play.clickhouse.com/play?user=play#U0VMRUNUIHVuaXEoc...
  • Creo que en la decisión de GitHub de alejarse de Rails también influyó en parte un defecto importante de ActiveRecord: la falta de soporte para claves primarias compuestas
    Un requisito tan básico como PRIMARY KEY(repo_id, issue_id) se vuelve innecesariamente complicado dentro de ActiveRecord, y obliga a usar un arreglo con una clave única y otra clave primaria separada para adaptarse a ActiveRecord, que exige una sola columna de clave primaria. Las claves primarias UUID también parecen un arreglo, pero igual necesitas la restricción única del par (repo_id, issue_id), lo que aumenta el tamaño de la base de datos y el overhead. En un plano más amplio, la estructura MVC monolítica de Ruby on Rails, basada en una sola capa de modelos, controladores y vistas, también genera problemas de escalabilidad y mantenibilidad a medida que la aplicación crece; creo que MVC encaja mejor en estructuras modulares o basadas en componentes
  • Me pregunto si alguien sabe con certeza si GitLab Cloud usa una base de datos multi-tenant, o si usa una base de datos por usuario, cliente u organización
    Los productos que ofrecen tanto self-hosting como nube muchas veces han preferido una base de datos por cliente. Eso simplifica mucho la parte compartida del codebase, porque permite usar las mismas consultas sin importar el tipo de hosting. Si fuera una base de datos por cliente, sería muy raro acercarse a ese tipo de límite de uso, y si alguien llegara a hacerlo, probablemente el self-hosting sería más adecuado
  • Esa bomba de tiempo es una bomba que se desactiva con una migración de 11 segundos
  • Migrar una clave primaria de int a bigint es posible. Requiere algo de preparación y código personalizado, pero se puede hacer sin downtime
    En general estoy manejando migraciones grandes siguiendo este procedimiento, con algunas modificaciones: http://zemanta.github.io/2021/08/25/column-migration-from-in...
    Las claves foráneas, índices y restricciones en general vuelven el proceso más difícil, pero no imposible. En mi caso, la migración de datos tomó varias horas, pero no necesitaba ser rápida. Entiendo que GitLab tiene una herramienta para ejecutar tareas posteriores a la actualización de manera que funcionen en cualquier punto durante una actualización de versión

• El argumento sobre el tamaño de almacenamiento de una columna UUID no resulta muy convincente. Si una tabla tiene otras 5 columnas, la diferencia entre 128 bits y 64 bits no es tan grande.
  La preocupación más importante es el rendimiento. UUIDv4 tiene soporte amplio, pero como es completamente aleatorio no es ideal para el rendimiento de los índices. UUIDv7[0] se parece más a Snowflake[1] y tiene localidad temporal, pero sus implementaciones están menos difundidas. Otra opción es usar bigserial y cifrar la clave: https://github.com/abevoelker/gfc64
  Pero este método tiene dos problemas: 1) no se puede rotar el secreto y 2) una vez filtrado, cualquiera puede hacer una estimación de Fermi del tamaño de la tabla. Separar el ID público del ID interno es engorroso, y si el ID público es UUIDv4 también se sacrifica rendimiento. UUIDv7 me parece la solución que mejor cumple con la mayor cantidad de requisitos.
  [0]: https://uuid7.com/
  [1]: https://en.wikipedia.org/wiki/Snowflake_ID

  • El problema no es solo el tamaño de esa columna, sino también todos los lugares donde ese id se usa como clave foránea y el tamaño de los índices necesarios para esas columnas de clave foránea.
    Basta pensar en un valor como el ID de usuario, que puede ser referenciado por decenas o cientos de claves foráneas en toda la base de datos.
  • El problema es que las otras 5 columnas no están indexadas.
    El rendimiento de una base de datos tiene tres etapas. 1) tanto los índices como los datos caben en memoria. 2) los índices caben en memoria, pero los datos no. 3) ni los índices ni los datos caben en memoria. La etapa 1 es ideal, pero si no se puede, hay que defender con uñas y dientes la etapa 2. Duplicar el tamaño de los índices hace eso más difícil.
  • Puedes pensar en la clave primaria de una base de datos como un puntero básico tipo typedef void*. Su tamaño afecta el rendimiento general: uso de memoria y disco, cuellos de botella de throughput, e incluso el tiempo de CPU para comparar claves en los bucles internos más profundos de joins y consultas.
    Cuando aparecieron las CPUs x86-64, el impacto en rendimiento del cambio a punteros de 64 bits fue tan grande que se creó x32/ilp32, y por eso .NET todavía deja “prefer 32-bit” como valor predeterminado. Usar UUIDs de 128 bits como claves primarias en una base de datos es un error terrible.
  • UUIDv7 tampoco es una solución universal. En muchos casos no quieres filtrar la hora de creación de un recurso.
    Por ejemplo, podrías querer subir un video un mes antes de publicarlo sin que la audiencia pueda darse cuenta.
  • También hay otras variantes de este enfoque: https://pgxn.org/dist/permuteseq/
    También es posible cifrar el valor al mostrarlo en URLs, correos electrónicos, etc.: https://wiki.postgresql.org/wiki/Pseudo_encrypt
    Esto permite conservar muchas de las ventajas de los índices secuenciales y además cambiar la clave. Pero si cambias la clave, se rompen los marcadores, los enlaces enviados en correos anteriores dejan de funcionar y, en la práctica, el efecto es similar a renombrarlo todo.

• Es una observación menor, pero quiero señalar la sección de text vs varchar.
  El autor gasta muchas palabras tratando de demostrar una diferencia de rendimiento que no existe, para luego concluir que “no hay una gran diferencia de rendimiento entre los dos tipos”. Esto ya se resolvió hace mucho tiempo, y no es que “no sea grande”, sino que no existe. La wiki de PostgreSQL[1] dice explícitamente que se use text salvo que haya una muy buena razón para no hacerlo, y la documentación[2] también dice que “para muchos propósitos, character varying actúa como un dominio sobre text”, además de afirmar en un recuadro verde de Tip que “no hay diferencia de rendimiento entre estos tres tipos”. Por eso, que GitLab use mayormente text parece indicar que leyeron la documentación y diseñaron el esquema para PostgreSQL, en vez de hacer un esquema torpemente “portable”.
  [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...
  [2] https://www.postgresql.org/docs/current/datatype-character.h...

  • De hecho, cuando sí hay que migrar el esquema para ajustarlo a un cambio en la longitud de las cadenas almacenadas, aparece una diferencia de rendimiento considerable.
    Cambiar varchar(300) a varchar(200) obliga a reescribir todas las filas, mientras que actualizar la restricción de una columna text es básicamente casi gratis y solo requiere un escaneo completo de la tabla para verificar que los valores existentes cumplan la nueva restricción. El propio artículo también dice que usar el tipo text con una restricción CHECK facilita más la evolución del esquema que character varying o varchar(n) cuando hay que validar la longitud.

• La idea de que las claves foráneas son caras se repite mucho, pero rara vez viene acompañada de benchmarks.
  Hay muchas formas de implementarlas mal, pero de todos modos en alguna parte del stack ya se está imponiendo la integridad. Si vas a aprovechar la base de datos en vez de reimplementar eso por tu cuenta, hace falta conocimiento y experimentación, y por lo general ayuda a prevenir desastres grandes.

• Me pregunto si alguien ha recopilado o seguido de cerca la diferencia de rendimiento entre GitLab y GitHub
  Ambos son aplicaciones basadas en Rails, pero siento que, en general, el tiempo de carga de páginas de GitLab es terrible comparado con GitHub

  • Cuando usé GitLab hace algunos años, los problemas de rendimiento del lado del cliente eran graves en pull requests grandes. GitHub tampoco es ideal, pero al menos lo maneja a un nivel aceptable
  • Compararlo con GitHub es parecido a comparar Chrome con otros navegadores, incluso con navegadores basados en Chromium
    Chrome y GitHub van a usar todo tipo de trucos aunque perjudiquen al usuario. Por ejemplo, en el GitHub de la empresa abrí un diff de merge y busqué con Ctrl F; como no había resultados, fui pasando al siguiente y revisando manualmente el historial de Git, hasta que recién en el diff número 100 descubrí que el archivo más importante estaba escondido en lo profundo. Probablemente porque así a alguien le resultaba más fácil cumplir una métrica de carga de página y conseguir un ascenso
  • GitHub, salvo dos caídas el año pasado, por lo general es estable y normalmente bastante rápido. Si no fuera así, no usaría atajos de teclado
    Aquí hay una publicación de un exdesarrollador que puede ayudar a entender la cultura de GitLab y su desdén por el rendimiento: https://news.ycombinator.com/item?id=39303323
    Yo no uso GitLab lo suficiente como para notar de primera mano los problemas de rendimiento, pero creo que esta publicación puede servir

• Siempre me pregunté qué significa la I extra en las variables de CI CI_PIPELINE_IID y CI_MERGE_REQUEST_IID
  Imaginaba que era una decisión relacionada con la base de datos, y esta publicación lo confirma

• Al ver que “1 quintillion es igual a 1,000,000,000 billion”, se siente bastante raro que casi siempre elijamos solo entre int32 e int64. Parece que debería existir un tipo entero de 5 bytes que soporte una cardinalidad de alrededor de 1 billón

  • A menos que estés empaquetando valores al máximo, no tiene sentido elegir tamaños que no sean potencias de 2

• No usar IDs autoincrementales puede ser razonable, pero no termino de ver la ventaja de tener 2 IDs, uno interno y otro externo
  Aumenta la cantidad de columnas e índices, siempre obliga a hacer una búsqueda previa, y tampoco se me ocurre un escenario de seguridad en el que cambies la clave interna pero no la externa. ¿Me estoy perdiendo de algo?

  • Cuando haces algo a nivel de proyecto, de todos modos ya tienes la información necesaria. Además, para el usuario es más amigable que los issues de cada proyecto empiecen en 1 y no en números como 270,300,571,7325

• Se dice que usar el tipo UUID v4 nativo de PostgreSQL en vez de bigserial hace que la tabla sea 25% más grande y que la tasa de inserción caiga al 25% de la de bigserial; me pregunto por qué UUIDv4 es tan malo
  ¿Acaso un UUID no es solo un número de 128 bits? No sé si el costo de generarlo es enorme o qué está pasando

  • UUIDv4 es completamente aleatorio, y los índices B-tree esperan valores “sesgados hacia la derecha” con un orden razonable
    Por eso el trabajo de indexación sobre columnas UUIDv4 se vuelve lento, y esa fue la motivación para desarrollar UUIDv6 y UUIDv7
  • Ese aumento de tamaño del 25% es real, pero son solo 8 bytes por fila: un incremento lineal pequeño y predecible. Comparado con el resto de los datos de la fila, no es algo tan preocupante
    El problema mayor es la tasa de inserción. En el caso de UUID, la tasa de inserción queda limitada por la cantidad de RAM disponible. Con enteros autoincrementales no pasa eso. Los enteros están correlacionados con el tiempo, pero UUID4 es aleatorio, así que a gran escala las características de rendimiento cambian de forma fundamental. En tablas pequeñas, la penalización de inserción es casi despreciable, pero cuando el tamaño del índice B-tree alcanza el límite de la memoria, PostgreSQL ya no puede mantener todo el B-tree de UUID en memoria y depende del reemplazo de páginas en disco. Los enteros autoincrementales usan la misma página de índice para filas cercanas en el tiempo, así que bajo la misma carga no necesitan golpear disco. Al llegar a esa escala, la diferencia no es una caída constante del 25%, sino un precipicio de rendimiento de 25x; salvo hacer una migración de esquema, no queda más que comprar más RAM
  • Yo diría que es por el B-tree. Los B-tree y las páginas funcionan mejor cuando se usa mucho solo la última página
    Los UUID generan muchas escrituras desordenadas y eso termina en inflación de páginas
  • Si el orden de clasificación se distribuye aleatoriamente, empeora la localidad de caché del B-tree. Las inserciones no van a la última página, sino que se dispersan por todos lados
    La localidad de las inserciones por lotes también empeora al momento de consultar después, porque hay que buscar registros relacionados de manera aleatoria. Al final, pagas el costo tanto al insertar como luego al hacer selecciones