PostgreSQL: разница между типами "bytea" и "bit differenting"

Типы PostgreSQL bytea и bit varying звучат одинаково:

В документации не указывается максимальный размер. Это 1GB, как character varying?

У меня есть два отдельных варианта использования, как по таблице с миллионами строк:

Хранение хешей MD5

Это будет bytea длиной 16 байт или bit(128). Он будет использоваться для:

  • Дедупликация: интенсивное использование GROUP BY, с индексом, который я предполагаю.
  • Запрос с помощью WHERE md5 = только для точных совпадений.
  • Отображение в виде шестнадцатеричной строки для использования человеком.

Сохранение произвольных двоичных данных

Строки двоичных данных различной длины до 4 КБ для:

  • Побитовые операции для поиска строк, соответствующих определенной маске. Пример в конце этого сообщения.
  • Извлечение некоторых байтов, например, получение целочисленного значения байта 14 в моей строке.
  • Некоторая дедупликация.

Рабочий пример для побитовой операции, используя bit varying. Маска X'00FF00 ', и она возвращает только строку X'AAAAAA'. Я сократил строки для примера, но это было бы на всю длину, до 4 КБ. Можно ли сделать что-то подобное с bytea?

CREATE TABLE test1 (mystring bit varying);
INSERT INTO test1 VALUES (X'AAAAAA'), (X'ABCABC');
SELECT * FROM test1 WHERE mystring & X'00FF00' = X'00AA00';

Какой из bytea и bit varying более подходит?

Я видел, что тип UUID создан для хранения ровно 16 байт, было бы ли это преимуществом для хранения MD5?

Ответы

Ответ 1

В общем случае, если вы не используете побитовые операции, вы должны использовать bytea.

Я храню большие значения в bytea, а затем конвертирует подстроки в bit varying для поразрядных операций, где это возможно, главным образом потому, что клиенты понимают bytea гораздо более последовательно, чем bit varying, а формат ввода/вывода более компактен.

Значения MD5 должны храниться как bytea. Побитовые операции над ними не имеют никакого смысла, и вы обычно хотите получить их как двоичные.

Я думаю, что bit varying действительно имеет два применения:

  • Сохранение полей флагов, которые являются буквально битными строками; и
  • В качестве промежуточного типа данных для внутренних вычислений

Для почти всего остального используйте bytea.

Там ничего не мешает вам хранить бит 4 бит, если это то, что это такое.

Ответ 2

  • Максимальная длина bytea составляет 1 ГБ. [1]
  • Для побитового использования используйте bit varying (объяснение см. ниже)
  • Для хранения хеша MD5 используйте bytea. Это займет меньше места, чем bit varying
  • Преимущества использования UUID - это алгоритм UUID, который гарантирует вашу уникальность не только в вашей таблице, но также в вашей базе данных или даже в вашей базе данных (даже если вы создаете UUID в своем приложении). Я думаю, что если вы используете UUID без тире, это будет более эффективно для хранения, сравнения и сортировки в UUID (сравнение между bytea и UUID см. Ниже).

Для побитовой операции используйте bit varying

Если вы относитесь к хранению: bit varying занимает больше памяти, чем bytea. Если вы в порядке, попробуйте сравнить функцию, которую они предлагают:

бит изменяется против bytea

До сих пор я вижу, что bit varying будет более подходящим для выполнения побитовой операции, хотя bytea является общепринятым способом хранения произвольных данных.

PostgreSQL предлагает один оператор bytea: конкатенация. Вы можете добавить одно значение byte к другому значению bytea, используя оператор конкатенации ||. [1]

Обратите внимание, что вы не можете сравнить два значения bytea, даже для равенства/неравенства. Разумеется, вы можете преобразовать значение bytea в другое значение с помощью CAST() и открыть другие операторы. [1]

Сравнение UUID и bytea

  create table u(uuid uuid primary key, payload character(300));
  create table b( bytea bytea primary key, payload character(300));

  INSERT INTO u                                                  
  SELECT uuid_generate_v4()                                                     
  FROM generate_series(1,1000*1000);

  INSERT INTO b                                                   
  SELECT random_bytea(16)                                                       
  FROM generate_series(1,1000*1000);

  VACUUM ANALYZE u;
  VACUUM ANALYZE b;

  ## Your table size
  SELECT pg_size_pretty(pg_total_relation_size('u'));
  pg_size_pretty 
  ---------------- 
  81 MB

  SELECT pg_size_pretty(pg_total_relation_size('b'));
  pg_size_pretty 
  ---------------- 
  101 MB

  ## Speed comparison
  \timing on

  ## Common select
  select * from u limit 1000;
  Time: 1.433 ms

  select * from b limit 1000;
  Time: 1.396 ms

  ## Random Select
  SELECT * FROM u OFFSET random()*1000 LIMIT 10000;
  Time: 42.453 ms

  SELECT * FROM b OFFSET random()*1000 LIMIT 10000;
  Time: 10.962 ms

Заключение: я не думаю, что при использовании UUID будет больше пользы, кроме его уникальности и меньшего размера (будет быстрее вставляться)

Примечание. Нет индекса, есть только одно соединение

Некоторые источники:

  • PostgreSQL: "Всеобъемлющее руководство по созданию, программированию и администрированию баз данных PostgreSQL".