Какой тип данных наиболее подходит для столбца таблицы?
В настоящее время я пытаюсь разработать схему базы данных PostgreSQL, в ней всего 2 таблицы, одна из которых содержит имена пользователей. Сложность в том, что по определенным причинам я не могу хранить имя пользователя напрямую, поэтому мне приходится хранить хэш (SHA256).
Postgres имеет тип данных bytea, который представляет собой массив байтов, технически это SHA256.
Мой вопрос заключается в том, существует ли лучший тип хранения хеша с точки зрения скорости поиска, существует ли имя пользователя в базе данных или нет.
Возможно, стоит посмотреть в сторону NoSQL-решений, там такой поиск быстрее?
-----Добавлять-----
Ответ и комментарии подсказывают, что оптимально использовать bytea.
CREATE TABLE users (
username bytea PRIMARY KEY,
somedata text NOT NULL
);
Да, я ищу точные совпадения, и да, имя пользователя будет хешировано на стороне клиента. Но что, если я выберу CHAR(64) вместо bytea? Или, может быть, что-то еще?
bytea — вероятно, самый компактный способ хранения хеша.
Ответы 2
Вероятно, это микрооптимизация, но bytea будет занимать значительно меньше места и выполнять сравнение быстрее.
Если вы в конечном итоге используете text (избегайте character), убедитесь, что вы используете сопоставление C:
hash_value text COLLATE "C"
Другие сопоставления приведут к гораздо более дорогостоящим сравнениям.
Почему bytea — это микрооптимизация? 32 байта против 64 байтов кажутся существенной разницей, если они используются в качестве индексного ключа.
@Charlieface Возможно, моя формулировка была не очень хороша. Я не думаю, что производительность будет сильно отличаться (используя сопоставление C). Разница в хранении может быть значительной.
Дважды длиннее = вдвое больше времени для проверки равенства в btree, а загрузка и сохранение также будут длиннее. Я не понимаю, почему производительность не станет немного медленнее.
Попробуйте. Как вы и подозревали и как уже подтвердили в комментариях и ответе от @Laurenz Albe, bytea выигрывает, будучи самым компактным и быстрым для поиска.
Вот как сравнивается хранение 400 тыс. хэшей:
И вот сколько времени нужно, чтобы его найти:
хэш-индекс, потому что меня интересуют только проверки на равенство
Вы не можете использовать using hash для уникальных индексов, поэтому вам нужно будет поддерживать и его, и отдельный уникальный индекс, который создается для обработки ограничения UNIQUE в вашем username столбце. Я бы просто придерживался уникального значения по умолчанию, которое у вас уже есть.
Кроме того, хеш-индексы не работают быстрее.
Если вы ищете точные совпадения, не могли бы вы просто хешировать входное имя пользователя для поиска, а затем сравнивать его со столбцом? Или здесь будут другие сценарии поиска?