При реализации некоторых прикладных задач в рамках экосистемы СБИС случается сталкиваться с неочевидными возможностями PostgreSQL, которые позволяют вместо сложной логики создать решение "в один ход".
Сегодня на примере вполне реальной задачи рассмотрим такие возможности оператора INSERT ... ON CONFLICT.
В первой части серии статей про работу с адресами по КЛАДР мы научились импортировать данные этого справочника к себе в базу и превращать их во что-то более удобное для дальнейшей работы.
Сегодня же займемся реализацией конкретных прикладных алгоритмов на этой структуре и рассмотрим, как можно реализовать мгновенную помощь пользователю при вводе адреса, используя возможности префиксного поиска в PostgreSQL.
Иногда при анализе производительности запроса на предмет "куда ушло все время" возникает стойкое ощущение deja vu, что вот ровно этот же кусок плана ты уже где-то раньше видел...
Пролистываешь выше - и таки-да, вот он рядом - но почему он там оказался, и как выйти из Матрицы самому и помочь коллегам?
Достаточно часто при работе с вводимыми пользователем адресными данными возникает необходимость сначала подсказать ему, как правильно свой адрес указать, а потом - полученную строку адреса привести в некоторый машинно-читаемый вид.
Таким видом у нас в стране можно считать код по справочникам КЛАДР или ФИАС.
Первый из них уже несколько лет считается устаревающим, но отличается более простой структурой и исторически продолжает использоваться во множестве систем, поскольку вполне подходит для большинства задач.
Давайте научимся разбирать строку адреса "туда и обратно", а заодно познакомимся с некоторыми алгоритмическими подходами и их реализацией на SQL.
Иногда мы пишем SQL-запросы, мало задумываясь над тем фактом, что сначала они должны быть по сети как-то доставлены до сервера, а затем их результат - обратно в клиентское приложение. Если при этом на пути до сервера присутствует еще и пулер соединений типа pgbouncer, дополнительно "перекладывающий" байты между входящими и исходящими коннектами, ситуация становится еще тяжелее...
Поэтому сегодня рассмотрим некоторые типичные ситуации, в которых разработчики иногда принимают не самые оптимальные решения, гоняя по сети мегабайты трафика при общении с сервером PostgreSQL - а заодно посмотрим, как можно увидеть такую ситуацию в плане с помощью explain.tensor.ru и подумаем над вариантами, как сделать подобное взаимодействие более эффективным.
Однажды при выполнении достаточно тривиального запроса:
UPDATE tbl SET val = val + 1 WHERE id IN (1, 2, 3)
... вы получаете ошибку ERROR: deadlock detected
Но почему? Ведь еще вчера все успешно работало!
И что с этим теперь делать? Давайте разбираться.
Если ваша жизнь DBA, сопровождающего PostgreSQL, наполнена вопросами "а почему так медленно?" и "как сделать, чтобы запрос не тормозил?", наш сервис анализа и визуализации планов запросов explain.tensor.ru сделает ее немного легче за счет привлечения коллег и обновленных подсказок.
Иногда в интерфейсе наших приложений СБИС возникает необходимость "сгруппировать" часть записей в некотором списке (например, служебные сообщения в чате, контакты и телефонные звонки).
Хорошо, если все эти записи приходят с одного источника, а вот если из разных сервисов, да с навигацией по курсору - алгоритм реализации становится весьма нетривиальным.
Иногда при выполнении длительных или плохо написанных запросов в PostgreSQL происходят разные неприятные вещи типа внезапного сбоя процесса или краша всего сервера.
В таких случаях на носителе могут остаться "мертвые души" - файлы (иногда совсем немаленькие, а вполне сравнимые по объему со всей остальной базой), которые были созданы во время работы процесса в качестве временного хранилища промежуточных данных.
Эти данные уже никому не нужны, никем не могут быть использованы, но сервер не торопится избавиться от них как Плюшкин.
В конце прошлого года Иван Панченко предложил мне рассказать на внутреннем семинаре Postgres Pro, чего, по нашему опыту использования PostgreSQL в "кровавом энтерпрайзе" "Тензора", не хватает в этой СУБД.
С докладом пока так и не сложилось, зато появилась эта статья, в которой я постарался собрать наиболее показательные вещи, которые вызывают "напряги" при активном использовании PostgreSQL в реальном бизнесе.
Многие знают правила этой головоломки (Skyscrapers):
"Перед вами вид сверху на городской квартал. В каждой клетке стоит "небоскреб" высотой, равной числу в этой клетке. Числа с боков сетки означают количество "небоскребов", видимых из соответствующей строки или столбца, если смотреть от этого числа.
Задача: заполнить сетку числами так, чтобы в каждой строке и в каждом столбце каждое число использовалось лишь единожды."
Понятно, что алгоритмом полного перебора можно решить что угодно, но - за экспоненциальное время. Поэтому мы попробуем написать такой SQL-запрос, который решит нам такую головоломку за приемлемое время.
Зачем же делать это на SQL? Потому что можем! А заодно потому что это позволит научиться конструировать "очень сложные запросы", что может пригодиться и в обычной работе.
Рассказ об опыте создания "с нуля", от идеи до рабочего устройства, простой автоматизации для своего автомобиля. Используя микроконтроллер STM32 и CAN-шину автомобиля сделаем жизнь автолюбителя немного проще и приятнее.
Как нормальные DBA, мы подождали выпуск пары минорных версий к PostgreSQL 13, который должен порадовать нас многими полезными вещами, и теперь готовы перенести базу нашего сервиса мониторинга этой СУБД с 12-й версии на 13-ю.
Но как это сделать с минимальным простоем, а лучше вообще без него? На помощь придет функционал Foreign Data Wrappers, а точнее - postgres_fdw.
Практически любая enterprise-система (под которой мы будем подразумевать некоторое ПО, где пользователи работают постоянно в течение всего рабочего дня) в современном мире стремится вырасти вместе с управляемым ей бизнесом в высоконагруженное web-решение вроде нашего СБИС.
Оно и понятно: доступность с любого устройства, где есть браузер, минимальные вложения "на старте" - все, что бизнес так любит. Но с развитием системы растет не только ее размер, но и сложность архитектуры решения, а с ней - и цена любой ошибки, вызывающей сразу каскад возможных проблем и "эффект домино".
Когда, где и как их может вызвать затаившийся до поры диверсант?
"Шеф, всё пропало, у нас serial на мегатаблице кончился!" - а это значит, что либо вы его неаккуратно накрутили сами, либо у вас действительно данных столько, что разрядности integer-столбца уже не хватает для вашей большой и активной таблицы в PostgreSQL-базе.
Да и столбец этот не простой, а целый PRIMARY KEY, на который еще и ряд других немаленьких таблиц по FOREIGN KEY завязан. А еще и приложение останавливать совсем не хочется, ибо клиентам 24x7 обещано...
В общем, надо как-то с минимальными блокировками увеличить размер PK-поля в большой таблице, на которое многое завязано.
Можно ли достоверно предсказать будущее хоть на немного вперед? Иногда - вполне, надо только много везения... или немного знаний.
Сегодня пронаблюдаем сеанс черной магии с последующим разоблачением, или «Я угадаю твой рандом с 3 строк!»
В предыдущей статье мы остановились на варианте, который с помощью SWAR-хинта превращает 8 последовательных цифр в одно числовое 32bit-значение. Но что если мы предположим, что все значения у нас, в основном, невелики - до 3 цифр? Тогда нам вполне достаточно использовать всего лишь 32bit-инструкции, а SWAR будет выполнен за 2 операции вместо 3 - сплошной выигрыш!
Давайте перепишем наш код так, чтобы первый блок из 4 символов обрабатывался 32bit-инструкциями, а второй блок из 8 символов, если понадобится - уже 64bit-инструкциями.
И... вместо 29ms получаем 31ms! Значит, наше предположение относительно длины чисел не оправдалось, и в первом блоке выгоднее обрабатывать сразу побольше символов.
То есть больше размерность регистра - лучше? И такие регистры есть - это 128-битные SSE-регистры XMM - в WebAssembly они доступны нам как переменные с типом v128 и суперскалярные операции над ними.
В первой части статьи мы исследовали скорость различных вариантов обмена информацией между JavaScript и WASM-кодом. В этом продолжении - наконец-то займемся написанием прикладного кода нашего парсера.
Мы ведь теперь пишем "прямо на ассемблере" - значит, все будет супербыстро! Правда ведь?
В прошлой статье, посвященной выяснению победителя в состязании JS-парсеров строки buffers-атрибута узла плана PostgreSQL, мы дошли до факта, что самый эффективный вариант - реализовать примитивный конечный автомат и никогда не трогать регулярные выражения и любые операции над строками сложнее .charCodeAt.
Такой код на тестовом нормализованном наборе показывает время порядка 48ms на 6.3MB или около 130MB/s, что примерно в 11 раз быстрее наивного варианта со .split.
Но всегда остается вопрос: "А еще быстрее - можно?"
Чтобы приблизиться к возможностям "железа", но по-прежнему остаться в инфраструктуре JavaScript, сегодня мы научимся решать эту задачу с использованием WebAssembly и SIMD-инструкций, постаравшись по пути споткнуться обо все подводные камни.
Тема "распухания" таблиц и индексов из-за реализации MVCC - больная для пользователей и администраторов PostgreSQL.
Однажды я уже поднимал ее в статье "DBA: когда пасует VACUUM — чистим таблицу вручную", разобрав на конкретных примерах, насколько драматический эффект для производительности запросов может оказывать невовремя проведенный или бесполезно отработавший из-за конкурентных транзакций VACUUM.
Но, помимо влияния на скорость, есть еще и факт влияния на занятое место. Наверное, вы сильно удивитесь, если таблица с единственной "живой" записью после успешного прохода autovacuum продолжит занимать гигабайты пространства на дорогих SSD.
Сегодня немного поисследуем структуру хранения данных в файлах и копнем pg_catalog - схему с описанием базы PostgreSQL, чтобы понять, как можно определить таблицы, которые явно занимают подозрительно много места.