Los riesgos de usar UUID como clave primaria en SQLite

• La implementación de claves primarias en SQLite hace que el orden de almacenamiento físico sea distinto entre las tablas rowid normales y las tablas WITHOUT ROWID; si se usa un UUID4 aleatorio como índice clúster, se generan reequilibrios del B-tree y costos adicionales de paginación
• La línea base con rowid entero registra alrededor de 1 millón de inserciones por segundo en inserciones por lotes de 1 millón de filas, mientras que UUID4 WITHOUT ROWID mostró tiempos de inserción entre 14 y 16 veces más lentos
• La naturaleza desordenada de UUID4 hace que las filas se inserten de forma aleatoria en el B-tree, y el perfilado mostró más tiempo dedicado al balanceo del árbol y a operaciones de lectura y escritura
• UUID7 WITHOUT ROWID redujo el problema de ordenamiento de UUID4 al ser un UUID ordenado por tiempo, mostrando tiempos de inserción más razonables, aunque sigue siendo más lento que una clave entera de 8 bytes por usar una clave BLOB de 16 bytes
• UUID4 WITH ROWID aprovecha la secuencialidad del rowid oculto, pero la amplificación de escritura causada por tener dos índices y el costo de inserciones aleatorias en el índice siguen presentes, por lo que rinde peor que UUID7 WITHOUT ROWID

---

¿Qué es un índice clúster?

• Un índice clúster es el índice que determina el orden de almacenamiento físico de las filas de una tabla
• Como las filas físicamente solo pueden ordenarse de una manera, cada tabla puede tener un solo índice clúster
• El índice clúster es la tabla misma, mientras que los índices no clúster solo almacenan la columna indexada y un puntero a la ubicación donde están los datos reales de la fila

Rowid

• Todas las tablas normales de SQLite tienen una clave primaria implícita entera de 64 bits llamada rowid
• Los datos de la tabla se almacenan en una estructura B-tree con una entrada por cada fila, usando el valor de rowid como clave
• En la práctica, rowid es el índice clúster de SQLite, y el orden físico de almacenamiento de las filas sigue el orden de rowid

WITHOUT ROWID

• SQLite soporta tablas WITHOUT ROWID, que no tienen el rowid implícito
• En las tablas WITHOUT ROWID, la clave primaria declarada actúa como índice clúster
• Las tablas rowid de SQLite están implementadas como un B*-Tree donde todo el contenido se guarda en las hojas, mientras que las tablas WITHOUT ROWID usan un B-Tree normal que guarda contenido tanto en las hojas como en los nodos intermedios

Línea base: clave primaria entera con rowid

• La línea base midió el tiempo de inserción por bloques de 1 millón de filas en una tabla rowid normal con estructura id INTEGER PRIMARY KEY, data BLOB
• En la tabla de resultados, el total de filas va de 10 millones a 100 millones, y los tiempos medidos van de 692 ms a 838 ms
• El rendimiento base es de aproximadamente 1 millón de inserciones por segundo

UUID4 WITHOUT ROWID

• La prueba con UUID4 insertó valores random-uuid4-bytes como clave primaria en una tabla WITHOUT ROWID con estructura id BLOB PRIMARY KEY, data BLOB
• En la tabla de resultados, el tiempo medido fue de 2649 ms con 10 millones de filas y de 12586 ms con 100 millones de filas
• El rendimiento de inserción fue entre 14 y 16 veces más lento que la línea base con rowid entero

Perfilado

• El diffgraph normalizado compara los snapshots de perfilado de INTEGER y UUID4, y muestra las diferencias en formato flamegraph
• La vista normalizada ajusta el número total de muestras de ambos perfiles para mostrar la diferencia relativa en porcentaje
• Los frames azules indican que en el segundo perfil, UUID4, el tiempo de esa función disminuyó respecto a INTEGER; los rojos indican que aumentó en UUID4
• La intensidad del color representa el cambio relativo en el número de muestras del propio frame, es decir, el cambio relativo del self time delta
• En el diffgraph se observa más tiempo dedicado al rebalanceo del árbol y a operaciones de lectura y escritura
• Debido a la naturaleza desordenada de UUID4, las claves quedan ordenadas en forma aleatoria, y SQLite tiene que reequilibrar continuamente el B-tree

UUID7 WITHOUT ROWID

• UUID7 es un UUID ordenado por tiempo, una forma de eliminar el problema de ordenamiento de UUID4
• La prueba con UUID7 también se ejecutó sobre una tabla WITHOUT ROWID con estructura id BLOB PRIMARY KEY, data BLOB
• En la tabla de resultados, el tiempo medido fue de 1372 ms con 10 millones de filas y de 1258 ms con 100 millones de filas
• UUID7 WITHOUT ROWID volvió a cifras más razonables que UUID4 WITHOUT ROWID, aunque sigue siendo más lento que la línea base
• La diferencia es que una clave primaria UUID BLOB ocupa 16 bytes, mientras que una clave primaria entera ocupa 8 bytes

UUID4 WITH ROWID

• La prueba UUID4 WITH ROWID usó una tabla id BLOB PRIMARY KEY, data BLOB sin WITHOUT ROWID
• En esta configuración, el rowid oculto es el índice clúster, y su ventaja es la secuencialidad
• La desventaja es que la tabla termina con dos índices, lo que provoca amplificación de escritura
• En la tabla de resultados, el tiempo medido fue de 2003 ms con 10 millones de filas y de 7119 ms con 100 millones de filas
• UUID4 WITH ROWID no rinde tan bien como UUID7 WITHOUT ROWID, porque aunque no sea el índice clúster, igual hay que seguir construyendo el índice con inserciones aleatorias

Conclusión

• En SQLite, usar UUID como clave primaria puede afectar mucho el rendimiento de inserción según el índice clúster y el orden de las claves
• El problema de los UUID aleatorios no se limita a SQLite; también se extiende a otras bases de datos que usan índices clúster
• Todo el código del benchmark está publicado en el repositorio de GitHub

[GN⁺]

Opiniones en Lobste.rs

• Bien. También sería interesante ver cifras de qué pasa en tablas rowid cuando la clave entera no es monótonamente creciente sino aleatoria
  Un punto importante que falta en el artículo es que el índice primario de las tablas rowid es un árbol B+, mientras que las tablas without rowid usan un árbol B
  Por eso, en general, si el tamaño promedio del registro supera cierto umbral, la segunda opción deja de ser ideal. Es porque los nodos internos del índice almacenan el registro completo y, si no recuerdo mal, el manual de SQLite sugiere como regla práctica 1/20 del tamaño de página

• Se esforzaron bastante en medir el rendimiento de UUID, pero no consideraron las claves naturales
  Ya sean enteras, UUID o de cualquier otro tipo, las claves sustitutas agregan complejidad, no aportan información y ocultan errores de normalización
  El autor dice que usa UUID para “evitar duplicados”, pero eso no es una razón. Toda fila de toda tabla en toda base de datos debe tener al menos una clave, es decir, un conjunto de columnas que identifique de forma única esa fila
  Una base de datos sin esa clase de clave contiene información duplicada y queda expuesta a inconsistencias lógicas. Si esa clave ya existe, no hace falta una clave sustituta. Si la clave natural ya existe y además se hace cumplir, afirmar que “se necesita por rendimiento” implica costo extra y complejidad innecesaria, así que hace falta demostrarlo

  • Tal vez me falte imaginación, pero cuando se trabaja con datos del mundo real, por lo general es difícil encontrar una clave natural de verdad
    Valores que parecen únicos a simple vista no siempre lo son tanto como se espera, y valores que uno suponía inmutables terminan necesitando cambiar
    En cambio, con una clave sustituta puedo definir la identidad dentro de mi sistema sin depender de cómo otra persona definió la identificabilidad, o más bien de cómo normalmente no la definió bien
    Hay excepciones. Si yo defino el modelo completo y los datos no vienen de eso que llamamos mundo real, una clave natural tiene más sentido. Pero al diseñar un esquema para datos reales que probablemente nunca estuvieron totalmente normalizados, las claves sustitutas suelen ser útiles
  • Las claves sustitutas enteras y monótonamente crecientes tienen bastante utilidad práctica inmediata
    Poder referirse a cualquier fila con un entero simplifica mucho el acceso, y además es fácil de recordar y de usar en consultas
    Si es monótonamente creciente, también contiene información por sí misma. Es información redundante, pero sigue siendo cierta
    La velocidad de consulta también queda optimizada. Es casi el caso ideal para indexar con árboles B, bitmaps, etc.
    Creo que la razón principal por la que la gente usa UUID es la confusión. Normalmente la lógica es ofuscar la clave y hacerla imposible de adivinar, pero ya me rendí con tratar de entender por qué creen que eso debe imponerse incluso a la clave primaria, en vez de usar un identificador aparte para ese propósito
  • La expresión “evitar duplicados” no aparece en la entrada del blog. De hecho, el artículo ni siquiera explica por qué usa UUID

• UUID versión 7 tiene un timestamp de 48 bits al principio, así que no queda distribuido aleatoriamente de esta manera. Por eso también debería reducir la paginación excesiva y el rebalanceo

  • Correcto. El artículo trata precisamente de UUID7

• ¿La gente de verdad usa UUID como clave primaria? Si se necesita un UUID, me pregunto qué ventaja tiene hacerlo así en vez de dejarlo como clave secundaria

  • Por escalabilidad. Si quieres generar IDs únicos de forma distribuida a alta velocidad en muchas computadoras y varios centros de datos alrededor del mundo, por ejemplo para algo como subidas a S3, no quieres poner un lock sobre un único entero incremental. Los locks son lentos para sincronizar globalmente
    GUID y UUID resuelven ese problema por diseño
    v1 y v6 codifican el ID de máquina y el timestamp, así que se parecen a enteros autoincrementales con un espacio de nombres por máquina
    Mucha de la confusión viene de que mucha gente asume que UUID significa aleatorio. Eso solo aplica a v4 y, por desgracia, elegir v4 sí tiene costo
    Muchas veces se necesita cierto grado de determinismo, como con v3, v5 o v7, ya sea para ordenar datos o por garantías de rendimiento y colisiones
  • Aunque pongas UUID aleatorios o valores aleatorios uniformemente distribuidos como clave secundaria, las inserciones igual se vuelven más lentas. Aunque no sea el índice clusterizado, en el árbol B de ese índice siguen ocurriendo inserciones aleatorias