Claves en la base de datos

5.1 Introducción

Internet está lleno de preceptos dogmáticos sobre cómo se deben elegir y utilizar las claves en las bases de datos relacionales. A veces, las disputas incluso se convierten en holivares: ¿se deben usar claves naturales o artificiales? ¿Integros de incremento automático o UUID?

Después de leer sesenta y cuatro artículos, hojear secciones de cinco libros y hacer toneladas de preguntas sobre IRC y StackOverflow, yo (Joe "begriffs" Nelson, el autor del artículo original) parece haber juntado las piezas del rompecabezas y ahora puede reconciliar a los adversarios. Muchas disputas clave en realidad surgen de un malentendido del punto de vista de otra persona.

Desmontemos el problema y volvamos a armarlo al final. Primero, hagamos la pregunta: ¿qué es una "clave"?

Olvidémonos de las claves primarias por un momento, nos interesa una idea más general. Una clave es una columna (column) o columnas que no tienen valores duplicados en las filas . Además, las columnas deben ser irreductiblemente únicas, es decir, ningún subconjunto de las columnas es tan único.

Pero antes, algo de teoría:

Clave primaria

Clave primariautilizado directamente para identificar filas en una tabla. Debe cumplir con las siguientes restricciones:

• La clave primaria debe ser única todo el tiempo.
• Siempre debe estar presente en la tabla y tener un valor.
• No debe cambiar su valor con frecuencia. Idealmente, no debería cambiar el valor en absoluto .

Por lo general, una clave principal representa una sola columna de una tabla, pero también puede ser una clave compuesta que consta de varias columnas.

clave compuesta

Clave personalizada- una combinación de atributos (columnas) que identifican de forma única cada fila de la tabla. Puede ser todas las columnas, y varias, y una. En este caso, las líneas que contienen los valores de estos atributos no deben repetirse.

Clave potencial

llave candidata- representa la clave compuesta mínima de la relación (tabla), es decir, un conjunto de atributos que satisface una serie de condiciones:

• Irreductibilidad : No se puede reducir, contiene el mínimo conjunto posible de atributos.
• Unicidad : Debe tener valores únicos independientemente del cambio de fila.
• Presencia de un valor : No debe tener un valor nulo, es decir, debe tener un valor.

5.2 El curioso caso de las claves primarias

Lo que acabamos de llamar "claves" en la sección anterior se conoce comúnmente como "claves candidatas". El término "candidato" implica que todas esas claves compiten por el papel honorario de "clave principal" (clave principal), y el resto se asignan como "claves alternativas" (claves alternativas).

Las implementaciones de SQL tardaron un tiempo en superar la falta de coincidencia entre las claves y el modelo relacional, y las primeras bases de datos estaban orientadas hacia el concepto de bajo nivel de una clave principal. Se requerían claves primarias en tales bases de datos para identificar la ubicación física de una fila en medios de almacenamiento secuencial. Así es como lo explica Joe Celko:

El término "clave" significaba una clave de clasificación de archivos, que era necesaria para realizar cualquier operación de procesamiento en un sistema de archivos secuenciales. Se leyó un juego de tarjetas perforadas en un solo orden; era imposible volver. Las primeras unidades de cinta imitaban el mismo comportamiento y no permitían el acceso bidireccional. Es decir, el Sybase SQL Server original requería “rebobinar” la tabla hasta el principio para leer la fila anterior.

En SQL moderno, no necesita concentrarse en la representación física de la información, las tablas modelan las relaciones y el orden interno de las filas no es importante en absoluto. Sin embargo, incluso ahora, el servidor SQL crea de forma predeterminada un índice agrupado para las claves principales y, según la antigua tradición, organiza físicamente el orden de las filas.

En la mayoría de las bases de datos, las claves primarias son cosa del pasado y proporcionan poco más que un reflejo o una ubicación física. Por ejemplo, en una tabla de PostgreSQL, declarar una clave principal impone automáticamente una restricción NOT NULLy define una clave externa predeterminada. Además, las claves primarias son las columnas preferidas por el operador JOIN.

La clave principal no anula la posibilidad de declarar otras claves. Al mismo tiempo, si no se asigna ninguna clave como principal, la tabla seguirá funcionando bien. Los rayos, en cualquier caso, no te golpearán.

5.3 Encontrar claves naturales

Las claves discutidas anteriormente se denominan "naturales" porque son propiedades del objeto modelado que son interesantes en sí mismas, incluso si nadie quiere convertirlas en una clave.

Lo primero que debe recordar al examinar una tabla en busca de posibles claves naturales es tratar de no ser demasiado inteligente. El usuario sqlvogel en StackExchange da el siguiente consejo:

Algunas personas tienen dificultades para elegir una clave "natural" porque se les ocurren situaciones hipotéticas en las que una clave en particular podría no ser única. No entienden el significado mismo de la tarea. El significado de la clave es definir la regla de que los atributos deben y siempre serán únicos en una tabla en particular en un momento dado. La tabla contiene datos en un contexto específico y bien entendido (en el "área temática" o "área discursiva"), y el único significado es la aplicación de la restricción en esa área en particular.

La práctica muestra que es necesario introducir una restricción clave cuando la columna es única con los valores disponibles y seguirá siéndolo en escenarios probables. Y si es necesario, se puede eliminar la restricción (si esto le molesta, a continuación hablaremos sobre la estabilidad de la clave).

Por ejemplo, una base de datos de miembros de un club de pasatiempos puede tener unicidad en dos columnas: first_name, last_name. Con una pequeña cantidad de datos, es poco probable que se produzcan duplicados y, antes de que surja un conflicto real, es bastante razonable utilizar una clave de este tipo.

A medida que crece la base de datos y aumenta el volumen de información, la elección de una clave natural puede volverse más difícil. Los datos que almacenamos son una simplificación de la realidad externa y no contienen algunos aspectos que distinguen a los objetos en el mundo, como sus coordenadas que cambian con el tiempo. Si un objeto carece de código, ¿cómo puedes distinguir dos latas de bebida o dos cajas de avena aparte de su disposición espacial o ligeras diferencias en peso o empaque?

Es por eso que los organismos de normalización crean y aplican marcas distintivas a los productos. Los vehículos están estampados con un número de identificación del vehículo (VIN) , los libros están impresos con ISBN y los empaques de alimentos tienen UPC . Puede objetar que estos números no parecen naturales. Entonces, ¿por qué las llamo claves naturales?

La naturalidad o artificialidad de las propiedades únicas en una base de datos es relativa al mundo exterior. Una clave que era artificial cuando se creó en un organismo de estándares o una agencia gubernamental se vuelve natural para nosotros, porque se convierte en un estándar en todo el mundo y/o se imprime en los objetos.

Hay muchos estándares industriales, públicos e internacionales para una variedad de temas, que incluyen monedas, idiomas, instrumentos financieros, productos químicos y diagnósticos médicos. Estos son algunos de los valores que a menudo se usan como claves naturales:

• Códigos de país ISO 3166
• Códigos de idioma ISO 639
• Códigos de moneda según ISO 4217
• Símbolos bursátiles ISIN
• UPC/EAN, VIN, GTIN, ISBN
• nombres de inicio de sesión
• correos electrónicos
• números de habitación
• dirección mac de la red
• latitud, longitud para puntos en la superficie de la Tierra

Recomiendo declarar claves siempre que sea posible y razonable, tal vez incluso varias claves por tabla. Pero recuerda que todo lo anterior puede tener excepciones.

• No todo el mundo tiene una dirección de correo electrónico, aunque esto puede ser aceptable en algunas condiciones de la base de datos. Además, las personas cambian sus direcciones de correo electrónico de vez en cuando. (Más sobre la estabilidad clave más adelante).
• Los símbolos bursátiles ISIN cambian de vez en cuando, por ejemplo, los símbolos GOOG y GOOGL no describen con precisión la reorganización de la empresa de Google a Alphabet. A veces puede surgir confusión, ya que con TWTR y TWTRQ, algunos inversores compraron por error este último durante la salida a bolsa de Twitter.
• Los números de Seguro Social solo los usan los ciudadanos estadounidenses, tienen restricciones de privacidad y se reutilizan después de la muerte. Además, después del robo de documentos, las personas pueden obtener nuevos números. Finalmente, el mismo número puede identificar tanto a una persona como a un identificador de impuesto sobre la renta.
• Los códigos postales son una mala elección para las ciudades. Algunas ciudades tienen un índice común, o viceversa, hay varios índices en una ciudad.

5.4 Claves artificiales

Dado que la clave es una columna con valores únicos en cada fila, una forma de crearla es hacer trampa: puede escribir valores únicos ficticios en cada fila. Se trata de claves artificiales: código inventado que se utiliza para hacer referencia a datos u objetos.

Es muy importante que el código se genere a partir de la propia base de datos y sea desconocido para todos excepto para los usuarios de la base de datos. Esto es lo que distingue las claves artificiales de las claves naturales estandarizadas.

Si bien las claves naturales tienen la ventaja de proteger contra filas duplicadas o inconsistentes en una tabla, las claves artificiales son útiles porque facilitan que los humanos u otros sistemas se refieran a la fila, y aceleran las búsquedas y las uniones porque no usan comparaciones de cadenas (o varias columnas).

sustitutos

Las claves artificiales se utilizan como anclas: no importa cómo cambien las reglas y las columnas, siempre se puede identificar una fila de la misma manera. La clave artificial utilizada para este propósito se denomina "clave sustituta" y requiere una atención especial. Consideraremos los sustitutos a continuación.

Las claves artificiales no sustitutas son útiles para hacer referencia a una fila desde fuera de la base de datos. Una clave artificial identifica brevemente un dato u objeto: puede especificarse como una URL, adjuntarse a una factura, dictarse por teléfono, obtenerse de un banco o imprimirse en una placa de matrícula. (La matrícula de un automóvil es una llave natural para nosotros, pero diseñada por el gobierno como una llave artificial).

Las claves sintéticas deben elegirse teniendo en cuenta los posibles medios de transmisión para minimizar las erratas y los errores. Cabe señalar que la clave puede ser hablada, leída impresa, enviada por SMS, leída manuscrita, tecleada desde el teclado e incrustada en una URL. Además, algunas claves artificiales, como los números de tarjetas de crédito, contienen una suma de verificación para que, si se producen ciertos errores, al menos puedan reconocerse.

Ejemplos:

• Para las matrículas de EE. UU., existen reglas sobre el uso de caracteres ambiguos, como O y 0.
• Los hospitales y farmacias deben tener especial cuidado, dada la letra de los médicos.
• ¿Envían un código de confirmación por mensaje de texto? No vaya más allá del juego de caracteres GSM 03.38.
• A diferencia de Base64, que codifica datos de bytes arbitrarios, Base32 usa un conjunto de caracteres limitado que es conveniente para que los humanos lo usen y manejen en sistemas informáticos más antiguos.
• Los proquints son identificadores legibles, escribibles y pronunciables. Estos son QUINT-uplets de consonantes y vocales que se entienden sin ambigüedades.

Tenga en cuenta que tan pronto como presente su llave artificial al mundo, la gente extrañamente comenzará a prestarle una atención especial. Basta con mirar las matrículas de los "ladrones" o el sistema de creación de identificadores pronunciables, que se ha convertido en el infame generador automático de maldiciones.

Aunque nos limitemos a las teclas numéricas, existen tabúes como el decimotercer piso. Si bien los proquintos tienen una mayor densidad de información por sílaba hablada, los números también están bien de muchas maneras: en URL, teclados pin y notas escritas a mano, siempre que el destinatario sepa que la clave son solo números.

Sin embargo, tenga en cuenta que no debe usar el orden secuencial en las claves numéricas públicas, ya que esto le permite hurgar en los recursos (/videos/1.mpeg, /videos/2.mpeg, etc.) y también filtra información sobre el número. datos. Superponga una red de Feistel en una secuencia de números y conserve la unicidad mientras oculta el orden de los números.

El único argumento en contra de declarar claves adicionales es que cada nueva trae consigo otro índice único y aumenta el costo de escribir en la tabla. Por supuesto, depende de cuán importante sea para usted la exactitud de los datos, pero lo más probable es que las claves aún deban declararse.

También vale la pena declarar varias claves artificiales, si las hay. Por ejemplo, una organización tiene candidatos para el puesto (Solicitantes) y empleados (Empleados). Cada empleado fue una vez un candidato y se refiere a los candidatos por su propio identificador, que también debería ser la clave del empleado. Otro ejemplo, puede configurar la identificación del empleado y el nombre de inicio de sesión como dos claves en Empleados.

5.5 Claves sustitutas

Como ya se mencionó, un tipo importante de clave artificial se denomina "clave sustituta". No necesita ser conciso y transitable como otras claves artificiales, pero se usa como una etiqueta interna que siempre identifica la cadena. Se usa en SQL, pero la aplicación no accede explícitamente a él.

Si está familiarizado con las columnas del sistema de PostgreSQL, entonces puede pensar en los sustitutos casi como un parámetro de implementación de la base de datos (como ctid), que, sin embargo, nunca cambia. El valor sustituto se selecciona una vez por fila y nunca se cambia a partir de entonces.

Las claves sustitutas son excelentes como claves externas y se deben especificar restricciones en cascada ON UPDATE RESTRICTpara que coincidan con la inmutabilidad del suplente.

Por otro lado, las claves externas a las claves compartidas públicamente deben marcarse con ON UPDATE CASCADE, para brindar la máxima flexibilidad. Una actualización en cascada se ejecuta al mismo nivel de aislamiento que la transacción circundante, así que no se preocupe por los problemas de simultaneidad: la base de datos estará bien si elige un nivel de aislamiento estricto.

No haga claves sustitutas "naturales". Una vez que muestra el valor de la clave sustituta a los usuarios finales, o peor aún, les permite trabajar con ese valor (particularmente a través de una búsqueda), le está dando un valor a la clave. Luego, la clave mostrada de su base de datos puede convertirse en una clave natural en la base de datos de otra persona.

Obligar a los sistemas externos a usar otras claves artificiales diseñadas específicamente para la transmisión nos permite cambiar esas claves según sea necesario para satisfacer las necesidades cambiantes, mientras mantenemos la integridad referencial interna con sustitutos.

Incremento automático INT/BIGINT

El uso más común para las claves sustitutas es la columna "bigserial" de incremento automático , también conocida como IDENTITY . (De hecho, PostgreSQL 10 ahora es compatible con la construcción IDENTITY, al igual que Oracle, consulte CREATE TABLE).

Sin embargo, creo que un entero de incremento automático es una mala elección para las claves sustitutas. Esta opinión es impopular, así que déjame explicarte.

Desventajas de las claves seriales:

• Si todas las secuencias comienzan en 1 y aumentan gradualmente, las filas de diferentes tablas tendrán los mismos valores clave. Esta opción no es ideal, aún es preferible usar conjuntos de claves disjuntos en las tablas, de modo que, por ejemplo, las consultas no puedan confundir accidentalmente las constantes JOINy arrojar resultados inesperados. (Alternativamente, para asegurarse de que no haya intersecciones, se podría construir cada secuencia a partir de múltiplos de números primos diferentes, pero esto sería bastante laborioso).
• La llamada nextval() para generar una secuencia en el SQL distribuido actual da como resultado que todo el sistema no se escale bien.
• El consumo de datos de una base de datos que también usó claves secuenciales generará conflictos porque los valores secuenciales no serán únicos en todos los sistemas.
• Desde un punto de vista filosófico, el aumento secuencial de los números está asociado a sistemas antiguos en los que estaba implícito el orden de las líneas. Si ahora desea ordenar las filas, hágalo explícitamente con una columna de marca de tiempo o algo que tenga sentido en sus datos. De lo contrario, se viola la primera forma normal.
• Razón débil, pero estos identificadores cortos son tentadores para decírselo a alguien.

UUID

Veamos otra opción: utilizar números enteros grandes (128 bits) generados según un patrón aleatorio. Los algoritmos para generar tales identificadores únicos universales (UUID) tienen una probabilidad extremadamente baja de elegir el mismo valor dos veces, incluso cuando se ejecutan en dos procesadores diferentes al mismo tiempo.

En ese caso, los UUID parecen una opción natural para usar como claves sustitutas, ¿no es así? Si desea etiquetar filas de una manera única, ¡no hay nada mejor que una etiqueta única!

Entonces, ¿por qué no todos los usan en PostgreSQL? Hay varias razones inventadas para esto y una lógica que se puede solucionar, y presentaré puntos de referencia para ilustrar mi punto.

Primero, hablaré de las razones inverosímiles. Algunas personas piensan que los UUID son cadenas porque están escritos en notación hexadecimal tradicional con un guión: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. De hecho, algunas bases de datos no tienen un tipo de uuid compacto (128 bits), pero PostgreSQL sí lo tiene y tiene un tamaño de dos bigint, es decir, en comparación con la cantidad de otra información en la base de datos, la sobrecarga es insignificante.

Los UUID también son injustamente acusados ​​de ser engorrosos, pero ¿quién los pronunciará, escribirá o leerá? Dijimos que tiene sentido que se muestren claves artificiales, pero nadie (por definición) debería ver el UUID sustituto. Es posible que un desarrollador se ocupe del UUID ejecutando comandos SQL en psql para depurar el sistema, pero eso es todo. Y el desarrollador también puede hacer referencia a las cadenas usando claves más convenientes, si se proporcionan.

El problema real con los UUID es que los valores altamente aleatorios conducen a la amplificación de la escritura debido a las escrituras de página completa en el registro de escritura anticipada (WAL) . Sin embargo, la degradación del rendimiento en realidad depende del algoritmo de generación de UUID.

Medimos la amplificación de escritura . En verdad, el problema está en los sistemas de archivos más antiguos. Cuando PostgreSQL escribe en el disco, cambia la "página" en el disco. Si apaga la computadora, la mayoría de los sistemas de archivos aún informarán una escritura exitosa antes de que los datos se almacenen de manera segura en el disco. Si PostgreSQL percibe ingenuamente que tal acción se completó, la base de datos se corromperá durante el siguiente arranque del sistema.

Dado que PostgreSQL no puede confiar en la mayoría de los sistemas operativos/sistemas de archivos/configuraciones de disco para proporcionar continuidad, la base de datos guarda el estado completo de la página de disco modificada en un registro de escritura anticipada que se puede usar para recuperarse de un posible bloqueo. La indexación de valores altamente aleatorios, como los UUID, generalmente involucra un montón de páginas de disco diferentes y da como resultado que el tamaño completo de la página (generalmente 4 u 8 KB) se escriba en el WAL para cada nueva entrada. Esta es la llamada escritura de página completa (full-page write, FPW).

Algunos algoritmos de generación de UUID (como el "copo de nieve" de Twitter o uuid_generate_v1() en la extensión uuid-ossp de PostgreSQL) generan valores crecientes monótonamente en cada máquina. Este enfoque consolida las escrituras en menos páginas de disco y reduce el FPW.

5.6 Conclusiones y recomendaciones

Ahora que hemos visto los diferentes tipos de claves y sus usos, quiero enumerar mis recomendaciones para usarlas en sus bases de datos.

Para cada mesa:

• Defina y declare todas las claves naturales.
• Cree una clave sustituta <table_name>_idde tipo UUID con un valor predeterminado de uuid_generate_v1(). Incluso puede marcarlo como clave principal. Si agrega el nombre de la tabla a este identificador, esto simplificará JOIN, es decir, recibir JOIN foo USING (bar_id)en lugar de JOIN foo ON (foo.bar_id = bar.id). No pase esta clave a los clientes y no la exponga fuera de la base de datos.
• Para las tablas intermedias que pasan por JOIN, declare todas las columnas de clave externa como una sola clave primaria compuesta.
• Opcionalmente, agregue una clave artificial que se pueda usar en la URL u otras indicaciones de referencia de cadena. Use una cuadrícula de Feistel o pg_hashids para enmascarar los enteros que se incrementan automáticamente.
• Especifique una restricción en cascada ON UPDATE RESTRICTutilizando UUID sustitutos como claves externas y para claves externas artificiales ON UPDATE CASCADE. Elija claves naturales basadas en su propia lógica.

Este enfoque garantiza la estabilidad de las claves internas al tiempo que permite e incluso protege las claves naturales. Además, las claves artificiales visibles no se adhieren a nada. Habiendo entendido todo correctamente, no puede obsesionarse solo con las "claves principales" y usar todas las posibilidades de usar claves.