Jaki jest optymalny typ danych dla pola MD5?

Projektujemy system, który jest znany z dużego odczytu (rzędu dziesiątek tysięcy odczytów na minutę).

• Istnieje tabela, namesktóra służy jako rodzaj centralnego rejestru. Każdy wiersz ma textpole representationi unikat, keyktóry jest skrótem tego MD5 representation. ¹ Ta tabela ma obecnie dziesiątki milionów rekordów i oczekuje się, że wzrośnie do miliardów przez cały okres użytkowania aplikacji.
• Istnieją dziesiątki innych tabel (o bardzo różnych schematach i liczbie rekordów), które odnoszą się do namestabeli. Każdy rekord w jednej z tych tabel ma gwarantowany, że name_keyjest funkcjonalnie kluczem obcym do namestabeli.

_{1: Nawiasem mówiąc, jak można się spodziewać, zapisy w tej tabeli są niezmienne po zapisaniu.}

W przypadku dowolnej tabeli innej niż namestabela najczęstsze zapytanie będzie zgodne z następującym wzorcem:

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

Chciałbym zoptymalizować wydajność odczytu. Podejrzewam, że moim pierwszym przystankiem powinno być zminimalizowanie wielkości indeksów (choć nie miałbym nic przeciwko temu, by udowodnić, że się mylę).

Pytanie:
Jakie są / są optymalne typy danych dla kolumn keyi name_key?
Czy istnieje powód, aby korzystać z hex(32)ponad bit(128)? BTREEczy GIN?

Typ danych uuidjest idealnie nadaje się do tego zadania. Zajmuje tylko 16 bajtów w przeciwieństwie do 37 bajtów w pamięci RAM dla reprezentacji varcharlub text. (Lub 33 bajty na dysku, ale w wielu przypadkach liczba nieparzysta wymagałaby uzupełnienia, aby efektywnie osiągnąć 40 bajtów.) A ten uuidtyp ma jeszcze więcej zalet.

Przykład:

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

Szczegóły i więcej wyjaśnień:

• Konwertuj szesnastkową reprezentację tekstu na liczbę dziesiętną
• Gdyby wszystkie wartości miały 36 znaków, wyszukiwanie indeksu byłoby zauważalnie szybsze przy użyciu char vs varchar

Możesz rozważyć inne (tańsze) funkcje haszujące, jeśli nie potrzebujesz komponentu kryptograficznego md5, ale wybrałbym md5 dla twojego przypadku użycia (głównie tylko do odczytu).

Słowo ostrzeżenia : W twoim przypadku ( immutable once written) funkcjonalnie zależna (pseudo-naturalna) PK jest w porządku. Ale to samo będzie bolało,text gdy możliwe są aktualizacje . Pomyśl o poprawieniu literówki: PK i wszystkie zależne indeksy, kolumny FK dozens of other tablesi inne odniesienia również musiałyby się zmienić. Nadęty tabel i indeksów, problemy z blokowaniem, powolne aktualizacje, utracone referencje, ...

Jeśli textmoże się zmienić podczas normalnej pracy, lepszym wyborem byłby zastępczy PK . Proponuję bigserialkolumnę (zakres -9223372036854775808 to +9223372036854775807- to dziewięć kwintillion dwieście dwadzieścia trzy biliard trzysta siedemdziesiąt dwa tryliony trzydzieści sześć coś miliard ) odrębnych wartości billions of rows. W każdym razie może to być dobry pomysł : 8 zamiast 16 bajtów na dziesiątki kolumn i indeksów FK!). Lub losowy UUID dla znacznie większych liczności lub systemów rozproszonych. Zawsze można przechowywać powiedział md5 (a uuid) dodatkowo do szybko znaleźć wiersze w tabeli głównej z oryginalnego tekstu. Związane z:

• Wartość domyślna dla kolumny UUID w Postgres

Jeśli chodzi o twoje zapytanie :

• Optymalizacja zapytania Postgres z dużym IN

Aby odpowiedzieć na komentarz @ Daniela : Jeśli wolisz reprezentację bez łączników, usuń łączniki do wyświetlenia:

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

Ale nie zawracałbym sobie głowy. Domyślna reprezentacja jest w porządku. A problemem tak naprawdę nie jest tutaj reprezentacja.

Jeśli inne strony powinny mieć inne podejście i wrzucać ciągi bez łączników do miksu, nie stanowi to również problemu. Postgres akceptuje kilka rozsądnych reprezentacji tekstowych jako dane wejściowe dla uuid. Dokumentacja :

PostgreSQL akceptuje także następujące alternatywne formy wprowadzania danych: użycie wielkich liter, standardowy format otoczony nawiasami klamrowymi, pomijanie niektórych lub wszystkich łączników, dodawanie łącznika po dowolnej grupie czterech cyfr. Przykładami są:

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

Co więcej, md5()funkcja zwraca text, należałoby użyć decode()do konwersji byteai domyślnej reprezentacji , które brzmi:

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

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

Musisz encode()ponownie uzyskać oryginalną reprezentację tekstu:

SELECT encode(my_md5_as_bytea, 'hex');

Co więcej, wartości przechowywane jako byteazajmowałyby 20 bajtów w pamięci RAM (i 17 bajtów na dysku, 24 z wypełnieniem ) z powodu wewnętrznego varlenaobciążenia , co jest szczególnie niekorzystne dla rozmiaru i wydajności prostych indeksów.

Wszystko działa na korzyść uuidtutaj.

Chciałbym zapisać MD5 w kolumnie textlub varchar. Nie ma różnicy w wydajności między różnymi typami danych znaków. Możesz ograniczyć długość wartości md5, varchar(xxx)upewniając się, że wartość md5 nigdy nie przekracza określonej długości.

Duże listy IN zwykle nie są naprawdę szybkie, lepiej zrobić coś takiego:

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

Inną opcją, o której mówi się, że jest szybsza, jest użycie tablicy:

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

Ponieważ porównujesz tylko dla równości, zwykły indeks BTree powinien być w porządku. Oba zapytania powinny mieć możliwość korzystania z takiego indeksu (szczególnie jeśli wybierają tylko niewielką część wierszy.

Inną opcją jest użycie 4 kolumn INTEGER lub 2 BIGINT.