우리는 읽기 / 무거운 것으로 알려진 시스템을 설계하고 있습니다 (분당 수만 번 읽기).
names
일종의 중앙 레지스트리 역할을 하는 테이블 이 있습니다. 각 행에는 해당text
필드 의 MD5 해시 인representation
고유 한 필드key
가representation
있습니다. 1 이 표는 현재 수천만 건의 레코드를 보유하고 있으며 애플리케이션 수명 기간 동안 수십억 건으로 증가 할 것으로 예상됩니다.- 테이블을 참조하는 수십 개의 다른 테이블 (매우 다양한 스키마 및 레코드 수)이
names
있습니다. 이러한 테이블 중 하나에 지정된 레코드name_key
는 기능적으로names
테이블 의 외래 키인을 갖습니다.
1 : 예상대로이 테이블의 레코드는 한 번 쓴 후에는 변경할 수 없습니다.
테이블 이외의 지정된 테이블 names
에 대해 가장 일반적인 쿼리는 다음 패턴을 따릅니다.
SELECT list, of, fields
FROM table
WHERE name_key IN (md5a, md5b, md5c...);
읽기 성능을 최적화하고 싶습니다. 나는 첫 번째로 지수의 크기를 최소화해야한다고 생각합니다.
질문 : 및 열에
대한 최적의 데이터 유형은 무엇입니까 ? 이상
사용할 이유가 있습니까? 또는 ?key
name_key
hex(32)
bit(128)
BTREE
GIN
답변
데이터 유형 uuid
은 작업에 완벽하게 적합합니다. varchar
또는 text
표현을 위해 RAM에서 37 바이트가 아닌 16 바이트 만 차지합니다 . (또는 디스크의 33 바이트이지만 홀수는 40 바이트를 효과적으로 만들기 위해 패딩이 필요합니다 .) 그리고이 uuid
유형에는 몇 가지 장점이 있습니다.
예:
SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash
세부 사항 및 추가 설명 :
md5의 암호화 구성 요소가 필요하지 않으면 다른 (저렴한) 해싱 함수를 고려할 수 있지만 사용 사례 (대부분 읽기 전용)에 md5를 사용합니다.
경고 단어 : 귀하의 경우 ( immutable once written
) 기능에 의존하는 (의사-자연) PK 가 좋습니다. 그러나 업데이트 가 가능한 고통도 마찬가지입니다 text
. 오타 수정 : PK 및 모든 종속 인덱스, FK 열 dozens of other tables
및 기타 참조도 변경해야합니다. 테이블 및 인덱스 팽창, 잠금 문제, 느린 업데이트, 손실 된 참조 …
경우 text
정상 작동 변경할 수 있습니다하는 대리의 PK가 더 나은 선택이 될 것입니다. 나는 bigserial
열 (범위 -9223372036854775808 to +9223372036854775807
– 구 quintillion 이백 이십 삼십 삼 삼백 삼십 삼십 삼십 육십 억 무엇인가 )에 대해 다른 값을 제안한다 billions of rows
. 에서 좋은 아이디어가 될 수 있는 경우 : 8 대신 16 ! FK 컬럼과 인덱스 수십 바이트). 아니면 랜덤 UUID 에 대한 훨씬 더 큰 카디널리티 또는 분산 시스템. 당신은 항상 상점은 MD5 (로 말했다 수 있습니다 uuid
) 추가로 신속하게 원래의 텍스트에서 기본 테이블에서 행을 찾을 수 있습니다. 관련 :
귀하의 쿼리에 관해서 :
@Daniel의 주석 을 처리하려면 : 하이픈이없는 표현을 선호하는 경우 표시 할 하이픈을 제거하십시오.
SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')
그러나 나는 귀찮게하지 않을 것입니다. 기본 표현은 괜찮습니다. 그리고 문제는 실제로 여기에 대한 표현이 아닙니다.
다른 당사자가 다른 접근 방식을 사용해야하고 하이픈이없는 문자열을 믹스에 넣으면 문제가되지 않습니다. Postgres는에 대한 입력으로 몇 가지 합리적인 텍스트 표현을 허용합니다 uuid
. 설명서 :
PostgreSQL은 다음과 같은 대체 입력 형식도 허용합니다. 대문자 숫자 사용, 중괄호로 묶은 표준 형식, 일부 또는 모든 하이픈을 생략하고 4 자리 그룹 뒤에 하이픈을 추가합니다. 예를 들면 다음과 같습니다.
A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11 {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11} a0eebc999c0b4ef8bb6d6bb9bd380a11 a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11 {a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}
게다가는 md5()
함수가 반환은 text
, 당신이 사용하는 것이 decode()
로 변환 할 bytea
및 기본 표현 즉 이다 :
SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')
\220\267R^\204\366HP\302\357\264\007\372\343\362q
encode()
원래 텍스트 표현 을 다시 가져와야합니다.
SELECT encode(my_md5_as_bytea, 'hex');
또한 내부 오버 헤드 로 인해 bytea
RAM에 20 바이트 (및 디스크에 17 바이트, 패딩이있는 24 바이트)를 차지하는 것처럼 저장된 값 은 특히 간단한 인덱스의 크기와 성능에 바람직하지 않습니다.varlena
모든 것이uuid
여기 에 유리 합니다.
답변
MD5를 text
또는 varchar
열에 저장합니다 . 다양한 문자 데이터 유형간에 성능 차이는 없습니다. varchar(xxx)
md5 값이 특정 길이를 초과하지 않도록함으로써 md5 값 의 길이를 제한 할 수 있습니다 .
큰 IN 목록은 일반적으로 빠르지 않으므로 다음과 같이하는 것이 좋습니다.
with md5vals (md5) as (
values ('one'), ('two'), ('three')
)
select t.*
from the_table t
join md5vals m on t.name_key = m.md5;
때때로 더 빠른 다른 옵션은 배열을 사용하는 것입니다.
select t.*
from the_table t
where name_key = ANY (array['one', 'two', 'three']);
평등을 비교할 때 정기적 인 BTree 지수가 좋습니다. 두 쿼리 모두 이러한 인덱스를 사용할 수 있어야합니다 (특히 행 중 일부만 선택하는 경우).
답변
다른 옵션은 4 INTEGER 또는 2 BIGINT 컬럼을 사용하는 것입니다.