wake-up-neo.net

Was ist der optimale Datentyp für ein MD5-Feld?

Wir entwickeln ein System, von dem bekannt ist, dass es schwer zu lesen ist (in der Größenordnung von Zehntausenden von Lesevorgängen pro Minute).

  • Es gibt eine Tabelle names, die als eine Art zentrale Registrierung dient. Jede Zeile hat ein text Feld representation und ein eindeutiges key, das ein MD5-Hash dieses representation ist.1 Diese Tabelle enthält derzeit mehrere zehn Millionen Datensätze und wird voraussichtlich über die Lebensdauer der Anwendung in Milliardenhöhe wachsen.
  • Es gibt Dutzende anderer Tabellen (mit sehr unterschiedlichen Schemata und Datensatzzahlen), die auf die Tabelle names verweisen. Jeder Datensatz in einer dieser Tabellen hat garantiert einen name_key, Der funktional ein Fremdschlüssel für die Tabelle names ist.

1: Übrigens sind Datensätze in dieser Tabelle, wie zu erwarten, nach dem Schreiben unveränderlich.

Für jede andere Tabelle als die Tabelle names folgt die häufigste Abfrage diesem Muster:

SELECT list, of, fields 
FROM table 
WHERE name_key IN (md5a, md5b, md5c...);

Ich möchte die Leseleistung optimieren. Ich vermute, dass mein erster Stopp darin bestehen sollte, die Größe der Indizes zu minimieren (obwohl es mir nichts ausmacht, dort als falsch erwiesen zu werden).

Die Frage :
Was ist/sind die optimalen Datentypen für die Spalten key und name_key?
Gibt es einen Grund, hex(32) über bit(128) zu verwenden? BTREE oder GIN?

postgresqldatabase-designindexdatatypesuniqueidentifier
35
bobocopy

Der Datentyp uuid ist perfekt für die Aufgabe geeignet . Es belegt nur 16 Bytes im Gegensatz zu 37 Bytes in RAM für die Darstellung varchar oder text. (Oder 33 Bytes auf der Festplatte, aber die ungerade Zahl würde in vielen Fällen ein Auffüllen erfordern, um es 40 Bytes effektiv zu machen.) Und der Typ uuid hat einige weitere Vorteile.

Beispiel:

SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash

Details und weitere Erklärungen:

Sie könnten andere (billigere) Hashing-Funktionen in Betracht ziehen, wenn Sie die kryptografische Komponente von md5 nicht benötigen, aber ich würde md5 für Ihren Anwendungsfall verwenden (meistens schreibgeschützt).

Ein Wort der Warnung : Für Ihren Fall (immutable once written) A funktional abhängig (pseudo-natürlich) ) PK ist in Ordnung. Aber das gleiche wäre ein Schmerz wo Updates auf text möglich sind. Denken Sie daran, einen Tippfehler zu korrigieren: Die PK und alle abhängigen Indizes, FK-Spalten in dozens of other tables Und andere Referenzen müssten sich ebenfalls ändern. Aufblähen von Tabellen und Indizes, Probleme beim Sperren, langsame Aktualisierungen, verlorene Referenzen, ...

Wenn sich text im normalen Betrieb ändern kann, ist eine Ersatz-PK die bessere Wahl. Ich schlage eine bigserial Spalte vor (Bereich -9223372036854775808 to +9223372036854775807 - das ist neun Billionen zweihundert dreiundzwanzig Billiarden dreihundertzweiundsiebzig Billionen sechsunddreißig etwas Milliarden ) verschiedene Werte für billions of rows. Könnte eine gute Idee sein in any case: 8 anstelle von 16 Bytes für Dutzende von FK-Spalten und -Indizes!) . Oder eine zufällige UUID für viel größere Kardinalitäten oder verteilte Systeme. Sie können das besagte md5 (als uuid) zusätzlich jederzeit speichern, um schnell Zeilen in der Haupttabelle aus dem Originaltext zu finden. Verbunden:

Wie für Ihre Abfrage:

So adressieren Sie @ Daniels Kommentar : Wenn Sie eine Darstellung ohne Bindestriche bevorzugen, entfernen Sie die Bindestriche für die Anzeige:

SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')

Aber ich würde mich nicht darum kümmern. Die Standarddarstellung ist in Ordnung. Und das Problem ist wirklich nicht die Darstellung hier.

Wenn andere Parteien einen anderen Ansatz verfolgen und Strings ohne Bindestriche in die Mischung werfen sollten, ist dies ebenfalls kein Problem. Postgres akzeptiert mehrere sinnvolle Textdarstellungen als Eingabe für ein uuid. Die Dokumentation :

PostgreSQL akzeptiert auch die folgenden alternativen Formen für die Eingabe: Verwendung von Großbuchstaben, das von geschweiften Klammern umgebene Standardformat, Weglassen einiger oder aller Bindestriche, Hinzufügen eines Bindestrichs nach einer Gruppe von vier Ziffern. Beispiele sind:

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

Darüber hinaus gibt die Funktion md5()text zurück. Sie würden decode() verwenden, um in zu konvertieren. bytea und die Standarddarstellung von das ist:

SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')

\220\267R^\204\366HP\302\357\264\007\372\343\362q

Sie müssten encode() erneut ausführen, um die ursprüngliche Textdarstellung zu erhalten:

SELECT encode(my_md5_as_bytea, 'hex');

Um das Ganze abzurunden, würden Werte, die als bytea gespeichert sind, 20 Bytes in RAM (und 17 Bytes auf der Festplatte, 24 mit Auffüllung ) aufgrund von belegen der interner varlena Overhead , der für die Größe und Leistung einfacher Indizes besonders ungünstig ist.

Alles arbeitet hier zugunsten eines uuid.

44
Erwin Brandstetter

Ich würde das MD5 in einer text oder varchar Spalte speichern. Es gibt keinen Leistungsunterschied zwischen den verschiedenen Zeichendatentypen. Möglicherweise möchten Sie die Länge der md5-Werte mithilfe von varchar(xxx) einschränken, um sicherzustellen, dass der md5-Wert niemals eine bestimmte Länge überschreitet.

Große IN-Listen sind normalerweise nicht sehr schnell. Es ist besser, Folgendes zu tun:

with md5vals (md5) as (
  values ('one'), ('two'), ('three')
)
select t.*
from the_table t
  join md5vals m on t.name_key  = m.md5;

Eine andere Option, die manchmal als schneller bezeichnet wird, ist die Verwendung eines Arrays:

select t.*
from the_table t
where name_key = ANY (array['one', 'two', 'three']);

Da Sie nur auf Gleichheit vergleichen, sollte ein regulärer BTree-Index in Ordnung sein. Beide Abfragen sollten in der Lage sein, einen solchen Index zu verwenden (insbesondere wenn nur ein kleiner Teil der Zeilen ausgewählt wird.

2
a_horse_with_no_name

Eine andere Option ist die Verwendung von 4 INTEGER- oder 2 BIGINT-Spalten.

0
happy_marmoset