Te recomendamos que revises esta resolución en un entorno controlado antes de pasarlo a producción, saludos.
Solución:
El tipo de datos uuid es perfectamente adecuado para la tarea. Solo ocupa 16 bytes en contraposición a los 37 bytes en RAM para el varchar o text representación. (O 33 bytes en el disco, pero el número impar requeriría relleno en muchos casos para hacerlo 40 bytes de manera efectiva). uuid type tiene algunas ventajas más.
Ejemplo:
SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;
Ver:
- Convertir hexadecimal en representación de texto a número decimal
- ¿La búsqueda de índices sería notablemente más rápida con char vs varchar cuando todos los valores son 36 caracteres?
Puede considerar otras funciones de hash (más baratas) si no necesita el componente criptográfico de md5, pero yo elegiría md5 para su caso de uso (principalmente de solo lectura).
Una palabra de advertencia: Para su caso (immutable once written) a PK funcionalmente dependiente (pseudo-natural) está bien. Pero lo mismo sería un dolor donde se actualiza text es posible. Piense en corregir un error tipográfico: el PK y todos los índices dependientes, las columnas FK en “docenas de otras tablas” y otras referencias también tendrían que cambiar. Hinchazón de tablas e índices, problemas de bloqueo, actualizaciones lentas, referencias perdidas, …
Si text puede cambiar en el funcionamiento normal, PK sustituta sería una mejor opción. Sugiero un bigserial columna (rango -9223372036854775808 to +9223372036854775807 – eso es nueve trillones doscientos veintitrés cuatrillones trescientos setenta y dos billones treinta y seis y algo mil millones) valores distintos para billions of rows. Podría ser una buena idea en ningún caso: 8 en vez de dieciséis bytes para docenas de columnas e índices FK!). O un UUID aleatorio para mucho cardinalidades mayores o sistemas distribuidos. Siempre puede almacenar dicho md5 (como uuid) Adicionalmente para buscar rápidamente filas en la tabla principal a partir del texto original. Relacionado:
- Valor predeterminado para la columna UUID en Postgres
En cuanto a tu consulta:
- Optimización de una consulta de Postgres con un IN grande
Para abordar el comentario de @ Daniel: si prefiere una representación sin guiones, elimine los guiones para mostrar:
SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')
Pero no me molestaría. La representación predeterminada está bien. Y el problema no es realmente la representación aquí.
Si otras partes deberían tener un enfoque diferente y lanzar hilos sin guiones en la mezcla, tampoco hay problema. Postgres acepta varias representaciones de texto razonables como entrada para un uuid. El manual:
PostgreSQL también acepta las siguientes formas alternativas de entrada: uso de dígitos en mayúsculas, el formato estándar entre llaves, omitiendo algunos o todos los guiones, agregando un guión después de cualquier grupo de cuatro dígitos. Ejemplos son:
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 a0eebc999c0b4ef8bb6d6bb9bd380a11 a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11 a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11
Además, el md5() devuelve la función text, usarías decode() convertir a bytea y la representación predeterminada de ese es:
SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')
220267R^204366HP302357264 07372343362q
Usted tendría que encode() nuevamente para obtener la representación del texto original:
SELECT encode(my_md5_as_bytea, 'hex');
Para colmo, los valores almacenados como bytea ocuparía 20 bytes en la RAM (y 17 bytes en el disco, 24 con relleno) debido a la varlena sobrecarga, que es particularmente desfavorable para el tamaño y el rendimiento de índices simples.
Todo trabaja a favor de un uuid aquí.
Guardaría el MD5 en un text o varchar columna. No hay diferencia de rendimiento entre los distintos tipos de datos de caracteres. Es posible que desee restringir la longitud de los valores md5 utilizando varchar(xxx) para asegurarse de que el valor md5 nunca exceda una cierta longitud.
Las listas de entrada grandes generalmente no son muy rápidas, es mejor hacer algo como esto:
with md5vals (md5) as (
values ('one'), ('two'), ('three')
)
select t.*
from the_table t
join md5vals m on t.name_key = m.md5;
Otra opción que a veces se dice que es más rápida es utilizar un array:
select t.*
from the_table t
where name_key = ANY (array['one', 'two', 'three']);
Como solo está comparando la igualdad, un índice BTree regular debería estar bien. Ambas consultas deberían poder hacer uso de dicho índice (especialmente si están seleccionando solo una pequeña fracción de las filas.