Микропост: режем UNICODE-строки для PostgreSQL / forpes.ru

Главная
Микропост: режем UNICODE-строки для PostgreSQL

Микропост: режем UNICODE-строки для PostgreSQL +3

20.02.2020 07:15

Kilor 32 1000 Источник

Периодически возникает желание положить в базу «неположимое» — например, засунуть очень длинную строку. Нет, записать ее в поле таблицы — для PostgreSQL проблем нет, но вот в индекс…

Проблема в том, что вся строка (ROW) индекса целиком должна полностью умещаться на одной странице данных (8KB), иначе вас ждет примерно такая ошибка:

ERROR: index row size… exceeds maximum… for index ...

То есть даже в простейшем случае индекса из единственной строки — можно наступить на грабли. Как с ними бороться?

Можно, конечно, сделать функциональный индекс по substring, но тогда это же выражение придется протаскивать и во все запросы, что совсем не добавляет счастья.

Аналогичные грабли ждут нас и при попытке отправить слишком большой (больше 8000 байт) payload в NOTIFY.

Вроде бы все должно быть предельно просто — возьми да отрежь (или порежь дольками, если надо) строку по 8K. Только вот по 8K — байт, а строки-то у нас на JavaScript (подставить язык по вкусу) все сплошь из unicode-символов, которые еще и длину могут иметь в байтах переменную. А заносить в базу «полувалидную» строку как-то совсем нехорошо.

А что будет, если мы строку все-таки порежем «посередине» символа? Тут нам на помощь приходит стандарт UNICODE:

U+FFFD ? REPLACEMENT CHARACTER used to replace an unknown, unrecognized or unrepresentable character

Оказывается, если мы попытаемся преобразовать такую «порезаную побайтово» строку обратно, на конце мы получим символ с кодом FFFD:

Итого, если нам целиком вся-вся строка не очень дорога (например, с текстом ошибки, от которого нам важно только начало), подрежем ее до последнего целого символа под требуемый размер в байтах:

const MAX_BYTE_LEN = 8191;
let buf = Buffer.from(errorString);
if (buf.length > MAX_BYTE_LEN) {
  errorString = buf.slice(0, MAX_BYTE_LEN).toString().replace(/\uFFFD$/, '');
}

Комментарии (32)

os9
20.02.2020 10:23
#21300788
-2
Получили русские мужики электропилу из Японии. Положили бревно:
— Вжик! — сказала пила, и бревно пополам.
— Ух ты! — сказали мужики и положили бревно потолще.
— Вжжжик! — сказала пила и… бревно пополам.
— Ого! — сказали мужики и положили рельсу.
— Вжж-крях! — сказала пила и поломалась.
— Ага!!! — сказали мужики, — То-то же…

Kwisatz
20.02.2020 10:25
#21300792
+1
А не могли бы вы use-case добавить? Зачем индексировать все это дело?
1. Kilor Автор
  20.02.2020 10:55
  #21300920
  Летит что-то такое в логе, например: "ERROR: syntax error at or near "abc" at character 123\nSELECT ...". Понятно, что дальнейший текст запроса можно отрезать, но если вместо «abc» в теле ошибки оказывается что-то очень длинное — от него так просто не избавиться.
  Чуть позже напишу отдельный пост, как вообще можно быстро матчить/группировать ошибки при наличии исходного набора форматов (вроде 'syntax error at or near "%s"').
  Зато «начало» ошибки (даже если оно усеченное) можно подставить под индекс с varchar_pattern_ops, что дает индексный LIKE по префиксу.
  1. Kwisatz
    20.02.2020 11:12
    #21301002
    Ага, благодарю. Не приходило мне в голову искать лайком по тексту лога. Обычно самые главное поля объекта-ошибки выношу в столбцы, остальное в jsonb и пусть себе валяется.
    
    varchar_pattern_ops,
    
    Какая интересная штука, как то я ее упустил, ни разу не пользовался. Хотя с другой стороны я не ищу обычно лайком за исключением автокомплитера, но там триграммы полезней.
    
    Kilor Автор
    20.02.2020 11:17
    #21301030
    Триграммы хорошо помогают, если поиск нужен просто по подстроке. А в реальных бизнес-кейсах чаще бывает нужен префиксный поиск от начала слова.
    
    И тут сначала надеешься, что человек набирает все-таки от начала строки — и тогда btree(varchar_pattern_ops) + LIKE 'abc%', а если не нашлось достаточное количество — тогда уже gin(to_tsvector(...)) + to_tsquery('abc:*').
    
    Kwisatz
    20.02.2020 12:33
    #21301454
    Триграммы хорошо помогают, если поиск нужен просто по подстроке
    
    create index consultants_active_trgm_idx on directories.consultants using GIST(name gist_trgm_ops) where is_deleted=false;
    
    select *, tableoid::regclass from directories.counterparties where name ~* ('(^|\s)' || ?) and is_deleted = false order by name limit 10;
    
    Index Scan using consultants_active_trgm_idx on consultants (cost=0.41..71.88 rows=61 width=58) (actual time=1.119..58.472 rows=40 loops=1)
    
    Kilor Автор
    20.02.2020 12:37
    #21301486
    Я не в смысле «не помогают», а в смысле «хорошо для подстроки, но для начала строки можно еще лучше».
    58.472мс — это все-таки не очень быстро для поиска 10 целевых записей. Интересно посмотреть на buffers и вариант с LIKE на тех же данных.
    
    Kwisatz
    20.02.2020 13:03
    #21301646
    А держите
    На самом деле это запрос вовсе не к одной таблице (6 млн строк суммарно) и машина это моя собственная, не сервак, до сервака сейчас не дотянусь.
    
    Кстати для ФИО можно и like? || '%' использовать по идее. Как минимум в русском, в остальных языках — не уверен. А вот по юрлицам итд итп — нет.
    
    like% имеет почти тот же план только выполняется 37 мс
    
    Kilor Автор
    20.02.2020 14:56
    #21302340
    Видимо, я не успел.
    
    This page is no longer available.
    
    А вот по юрлицам итд итп — нет.
    Тоже можно, если сначала их определенным алгоритмом преобразовать.
    
    Kwisatz
    20.02.2020 15:22
    #21302502
    хм, действительно протухла, вот
    
    По юрлицам это вариант, но работающий не всегда.
    
    Kilor Автор
    20.02.2020 15:34
    #21302572
    6.5K buffers — это все-таки многовато. Поэтому мы не стали триграммы использовать для аналогичной задачи, дорого очень.
    [link]
    
    Kwisatz
    20.02.2020 15:45
    #21302612
    Совсем забыл про сей инструмент, быстро привыкаешь к PEV2.
    
    Ну вот ваш вариант.
    
    create index consultants_active_btree_idx on directories.consultants using btree(name varchar_pattern_ops) where is_deleted = false;
    
    Причем обратите внимание, ищу лайком, по паттерну будет еще хуже.
    
    Kilor Автор
    20.02.2020 16:02
    #21302740
    Таки у вас запрос на индекс не сел за счет раскастовки name:
    
    Parallel Bitmap Heap Scan on consultants (cost=8844.65..64082.74 rows=25 width=58) (actual time=162.509..251.519 rows=3 loops=3) Recheck Cond: (NOT is_deleted) Filter: ((name)::text ~~* 'Евлам%'::text) Rows Removed by Filter: 207262 Heap Blocks: exact=17893 Buffers: shared hit=54451
    
    Kwisatz
    20.02.2020 16:16
    #21302828
    Ух я даже внимания не обратил. Эка его покорежило, сейчас поправимс.
    
    UPD а собственно вариант без раскастовки только like вместо ilike, а значит lower/upper до кучи
    
    UPD2 Да в том что-то есть, хотя применимо только для like? || % и требует танца с upper/lover, но работает быстрее в тысячу раз. Хотя вот именно для того триграммы и придумали, чтобы при поиски Площади святого Петра мы не испытывали проблем.
    
    Limit (cost=3382.76..3382.78 rows=10 width=70) (actual time=0.036..0.037 rows=3 loops=1) Buffers: shared hit=6 -> Sort (cost=3382.76..3390.43 rows=3069 width=70) (actual time=0.036..0.036 rows=3 loops=1) Sort Key: name Sort Method: quicksort Memory: 25kB Buffers: shared hit=6 -> Bitmap Heap Scan on consultants (cost=61.58..3316.44 rows=3069 width=70) (actual time=0.024..0.029 rows=3 loops=1) Recheck Cond: (NOT is_deleted) Filter: (lower((name)::text) ~~ 'расти%'::text) Heap Blocks: exact=3 Buffers: shared hit=6 -> Bitmap Index Scan on consultants_active_btree_idx (cost=0.00..60.81 rows=3069 width=0) (actual time=0.013..0.013 rows=3 loops=1) Index Cond: ((lower((name)::text) ~>=~ 'расти'::text) AND (lower((name)::text) ~<~ 'растй'::text)) Buffers: shared hit=3 Planning Time: 0.085 ms Execution Time: 0.055 ms
    
    Kilor Автор
    20.02.2020 17:04
    #21303122
    Можно примерно так:
    ( ... WHERE ... LIKE 'abc%' LIMIT 10 ) UNION ALL ( ... WHERE ... @@ to_tsquery('abc:*') -- а можно и триграммы LIMIT 10 ) LIMIT 10
    
    Kwisatz
    20.02.2020 17:12
    #21303170
    Ну во первых вы забыли lower/upper и сортировку во трех местах, во вторых решение будет работать медленнее за счет сортировки и двойного поиска, в третьих вам нужно два разных индекса.
    
    Kilor Автор
    20.02.2020 17:18
    #21303210
    Если 10 записей найдутся в первом подзапросе, второй не будет выполняться, а сортировка в нем нормально ложится на префиксный индекс.
    Но индексов будет два, да. Мы у себя моделировали — в большинстве запросов пользователю хватает результатов в LIKE-блоке, а выигрыш в тысячу раз стоит того, чтобы иметь второй индекс.
    
    Kwisatz
    20.02.2020 18:25
    #21303586
    +1
    Для красоты все таки итоговый запрос,
    хотя нужно держать в голове, что сортировка все же неправильная.
    
    (select *, tableoid::regclass from directories.consultants where lower(name) like lower(?) || '%' and is_deleted = false order by lower(name) using ~<~ limit 10) union all (select *, tableoid::regclass from directories.consultants where name ~* ('(\W)' || ?) and is_deleted = false order by name limit 10) limit 10;
    
    Explain
    
    Kwisatz
    20.02.2020 17:10
    #21303156
    Вариант совмещенного поиска конечно иногда бывает интересен, но точно не с tsvector, все таки это из области триграмм а не полнотекстового поиска.
    
    Вариант с нормализованной базой юрлиц интересен, но опять же, если это делалось на этапе создания приложения, когда в руках таблица с миллонами записей по нескольки странам, тут только использовать что есть. Хотя я бы потестил нормализованную базу, не видел еще доведенного до идеала решения, поскольку всевозможные сокращения ака 'НПФ', 'ДЮСШ' и прочие, тысячи их, не оставляют ни малейшего шанса.
    
    С географией тоже удобней искать по границе слова, это я на себе почувствовал. Вот с фамилиями согласен, тут префиксный поиск в полный рост, хотя черт его знает как там в других языках.
    
    Kilor Автор
    20.02.2020 17:20
    #21303222
    Нет предела совершенству, но мы стараемся поддерживать именно вариант с выносом значимой части вперед в названии. Можете попробовать: sbis.ru/contragents
    
    Kwisatz
    20.02.2020 17:51
    #21303374
    Интересно, есть сходная задача, посмотрю как будет вставать вопрос об апи. Хотя с сайта поиск у вас несколько странный, либо я ниразу не попал в юрлицо, которое у вас есть в базе.

Aytuar
21.02.2020 10:30
#21305878
Блин, думал статья о том что реально есть способ пожать строки чтобы они в блобы не лезли, а оказалось что-то про обрезание.
И зачем вообще в БД хранить логи? Или это типа путь 1С, хранить файлы в БД. )
1. Kilor Автор
  21.02.2020 10:39
  #21305940
  Сами логи в базе хранить смысла мало, а вот результат их процессинга — бывает очень полезно.
  habr.com/post/487380
  www.youtube.com/watch?v=OVeTdSWeMYo
  
  А вам, видимо, надо смотреть в сторону v11 и
  
  ALTER TABLE ... SET (toast_tuple_target = ...); ALTER TABLE ... ALTER COLUMN ... SET STORAGE MAIN;
  
  Напишу попозже про наш опыт тюнинга этих параметров.
1. Kwisatz
  21.02.2020 11:01
  #21306094
  Зачем в базе логи хранить? Возьмите какойнить PipelineDB, сыпьте логи в его стрим и довешивайте continous view по мере необходимости с нужной обработкой.
  
  Либо сразу обрабатывать, нужные поля в таблицу, остальное в jsonb, если вдруг понадобилось то индексируем нужные поля частичным индексом по диапазону времени.

oxff
24.02.2020 00:35
#21311426
А как насчёт FTS + GIN?
1. Kilor Автор
  24.02.2020 01:20
  #21311502
  А что про них? Удобная штука, но дорогая в использовании.
  1. oxff
    24.02.2020 02:40
    #21311582
    Зато без изврата.
    Всё имеет свою цену. Если у вас вот прям такая задача стоит, то логично использовать соответствующие инструменты. Или не использовать Postgres совсем, а например Elasticsearch.
    
    Kilor Автор
    24.02.2020 10:05
    #21311898
    Все зависит от задачи. Если можно найти решение, которое дает неотличимый для пользователя ответ, но "дешевле" — почему бы им не воспользоваться.
    
    oxff
    24.02.2020 16:53
    #21312898
    Потому что вы свой колхоз опубликовали на Хабре, а кто-то его заплюсовал. Вы надеетесь этим привлечь клиентов? Скорее вы добьётесь обратного эффекта. Пожалуйста, добавьте вступление в начале поста:
    
    Мы в компании "Тензор" любим изобретать велосипеды, но иногда они выходят с квадратными колёсами. Вместо того чтобы использовать решения промышленного уровня, признанные во всём мире, мы лепим колхоз из палок и синей изоленты. В данной задаче нам требовалось хранить логи и выполнять поиск по ним. Советуем вам для начала обратить внимание на Elastic Stack (бывший ELK), который развертывается в считанные минуты и агрегирует логи с разных частей приложения. Также недавно дозрел до продакшна Grafana Loki. Есть и более экзотические решения, хорошо зарекомендовавшие себя на рынке. Решать вам, но помните, что скупой платит дважды!
    
    Kilor Автор
    24.02.2020 17:35
    #21313002
    Вы почему-то считаете, что у любой задачи есть только единственное "правильное" решение, но это не так.
    Мы используем для разных задач и Elastic, и ClickHouse, и Redis — но всегда надо учитывать не только их возможности, но и ограничения. А в корпоративных велосипедах нет ничего особо ужасного — так появились и Bootstrap, и AWS, и Google Cloud.
    
    oxff
    24.02.2020 18:39
    #21313230
    Сравнение некорректное. Все перечисленные вами продукты отнюдь не являются велосипедами. Они были тщательнейшим образом спроектированы, и в их разработку были вложены сотни миллионов долларов. И теперь они задают отраслевые стандарты.
    
    Да, я считаю что нужно использовать по возможности только "правильные" решения, являющиеся продуктом коллективного разума лучших инженеров планеты, признанные корп. стандартом, имеющие поддержку сообщества и интеграцию с другими "правильными" решениями из коробки.
    И я предложил не одно такое решение а три, на вскидку.
    
    Kilor Автор
    24.02.2020 18:50
    #21313248
    То есть весь упрек — в недостаточно тщательном проектировании наших решений (для задач, всех условий которых вы не знаете) или в отсутствии ссылок на решения от "коллективного разума" (который не факт, что решал именно такие задачи)?
    
    Я бы предложил не оффтопить здесь, а вынести дальнейшее общение в личку, если обсудить наши задачи и их решения действительно интересно.

Микропост: режем UNICODE-строки для PostgreSQL +3

Комментарии (32)

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор

Kilor Автор