Рубрика «sql tips and tricks» - 2

в 7:20, , рубрики: dba, explain, explain.tensor.ru, postgresql, sql, sql tips and tricks, Администрирование баз данных, базы данных, Блог компании Тензор, визуализация данных

Несколько месяцев назад мы анонсировали explain.tensor.ru — публичный сервис для разбора и визуализации планов запросов к PostgreSQL.

За прошедшее время вы уже воспользовались им более 6000 раз, но одна из удобных функций могла остаться незамеченной — это структурные подсказки, которые выглядят примерно так:

Рецепты для хворающих SQL-запросов - 1

Прислушивайтесь к ним, и ваши запросы «станут гладкими и шелковистыми». :)

А если серьезно, то многие ситуации, которые делают запрос медленным и «прожорливым» по ресурсам, типичны и могут быть распознаны по структуре и данным плана.

В этом случае каждому отдельному разработчику не придется искать вариант оптимизации самостоятельно, опираясь исключительно на свой опыт — мы можем ему подсказать, что тут происходит, в чем может быть причина, и как можно подойти к решению. Что мы и сделали.

Рецепты для хворающих SQL-запросов - 2

Давайте чуть подробнее рассмотрим эти кейсы — как они определяются и к каким рекомендациям приводят.
Читать полностью »

в 16:01, , рубрики: dba, IS DISTINCT FROM, postgresql, sql, sql antipatterns, sql tips and tricks, update, Администрирование баз данных, базы данных, Блог компании Тензор, Программирование

Особенности работы внутренних механизмов PostgreSQL позволяют ему быть очень быстрым в одних ситуация и «не очень» в других. Сегодня остановимся на классическом примере конфликта между тем, как работает СУБД и тем, что делает с ней разработчик — UPDATE vs принципы MVCC.

Кратко сюжет из отличной статьи:

Когда строка изменяется командой UPDATE, фактически выполняются две операции: DELETE и INSERT. В текущей версии строки устанавливается xmax, равный номеру транзакции, выполнившей UPDATE. Затем создается новая версия той же строки; значение xmin у нее совпадает с значением xmax предыдущей версии.

Через какое-то время после завершения этой транзакции старая или новая версии, в зависимости от COMMIT/ROOLBACK, будут признаны «мертвыми» (dead tuples) при проходе VACUUM по таблице и зачищены.

PostgreSQL Antipatterns: сражаемся с ордами «мертвецов» - 1

Но это произойдет далеко не сразу, а вот проблемы с «мертвецами» можно нажить очень быстро — при многократном или массовом обновлении записей в большой таблице, а чуть позже столкнуться с ситуацией, что и VACUUM не сможет помочь.
Читать полностью »

в 5:50, , рубрики: dba, distinct, explain, explain.tensor.ru, GIN, pg_trgm, postgresql, sql, sql antipatterns, sql tips and tricks, union, Администрирование баз данных, базы данных, Блог компании Тензор, высокая производительность, поиск по названию

Тысячи менеджеров из офисов продаж по всей стране фиксируют в нашей CRM-системе ежедневно десятки тысяч контактов — фактов общения с потенциальными или уже работающими с нами клиентами. А для этого клиента надо сначала найти, и желательно очень быстро. И происходит это чаще всего по названию.

Поэтому неудивительно, что, разбирая в очередной раз «тяжелые» запросы на одной из самых нагруженных баз — нашего собственного корпоративного аккаунта СБИС, я обнаружил «в топе» запрос для «быстрого» поиска по названию для карточек организаций.

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

0: чего же хотел пользователь

PostgreSQL Antipatterns: сказ об итеративной доработке поиска по названию, или «Оптимизация туда и обратно» - 1

[КДПВ отсюда]

Что вообще обычно подразумевает пользователь, когда говорит про «быстрый» поиск по названию? Почти никогда это не оказывается «честный» поиск по подстроке типа ... LIKE '%роза%' — ведь тогда в результат попадают не только 'Розалия' и 'Магазин Роза', но и 'Гроза' и даже 'Дом Деда Мороза'.

Пользователь же подразумевает на бытовом уровне, что вы ему обеспечите поиск по началу слова в названии и покажете более релевантным то, что начинается на введенное. И сделаете это практически мгновенно — при подстрочном вводе.
Читать полностью »

в 5:50, , рубрики: dba, postgresql, sql, sql antipatterns, sql tips and tricks, update, Администрирование баз данных, базы данных, Блог компании Тензор

Рано или поздно многие сталкиваются с необходимостью что-то массово исправить в записях таблицы. Я уже рассказывал, как это делать лучше, а как — лучше не делать. Сегодня расскажу о втором аспекте массового обновления — о сработке триггеров.

Например, на таблице, в которой вам надо что-то поправить, висит злобный триггер ON UPDATE, переносящий все изменения в какие-нибудь агрегаты. А вам надо все пообновлять (новое поле проинициализировать, например) так аккуратно, чтобы эти агрегаты не затронулись.

Давайте просто отключим триггеры!

BEGIN;
  ALTER TABLE ... DISABLE TRIGGER ...;
  UPDATE ...; -- тут долго-долго
  ALTER TABLE ... ENABLE TRIGGER ...;
COMMIT;

Собственно, тут и все — все уже висит.

Потому что ALTER TABLE накладывает AccessExclusive-блокировку, под которой никто параллельно выполняющийся, даже простой SELECT, ничего из таблицы прочитать не сможет. То есть пока эта транзакция не закончится, все желающие даже «просто почитать» будут ждать. А мы помним, что UPDATE у нас до-о-олгий…
Читать полностью »

в 9:56, , рубрики: array, dba, explain, explain.tensor.ru, postgresql, sql, sql tips and tricks, unnest, Администрирование баз данных, Алгоритмы, базы данных, ненормальное программирование, рекурсия

Периодически возникает задача поиска связанных данных по набору ключей, пока не наберем нужное суммарное количество записей.

Наиболее «жизненный» пример — вывести 20 самых старых задач, числящихся на списке сотрудников (например, в рамках одного подразделения). Для различных управленческих «дашбордов» с краткими выжимками по участкам работы похожая тема требуется достаточно часто.

SQL HowTo: пишем while-цикл прямо в запросе, или «Элементарная трехходовка» - 1

В статье рассмотрим реализацию на PostgreSQL «наивного» варианта решения такой задачи, «поумнее» и совсем сложный алгоритм «цикла» на SQL с условием выхода от найденных данных, который может быть полезен как для общего развития, так и для применения в других похожих случаях.
Читать полностью »

в 12:11, , рубрики: case, dba, explain, explain.tensor.ru, lateral, lazy sql, postgresql, sql, sql antipatterns, sql tips and tricks, Администрирование баз данных, Алгоритмы, базы данных, высокая производительность

Если писать SQL-запросы без анализа алгоритма, который они должны реализовать, ни к чему хорошему с точки зрения производительности это обычно не приводит.

Такие запросы любят «кушать» процессорное время и активно почитывать данные практически на ровном месте. Причем, это вовсе не обязательно какие-то сложные запросы, наоборот — чем проще он написан, тем больше шансов получить проблемы. А уж если в дело вступает оператор JOIN…

PostgreSQL Antipatterns: редкая запись долетит до середины JOIN - 1

Само по себе соединение таблиц не вредно и не полезно — это просто инструмент, но и пользоваться им надо уметь.
Читать полностью »

в 8:43, , рубрики: dba, postgresql, sql, sql tips and tricks, Алгоритмы, базы данных, визуализация данных, математика, ненормальное программирование, рекурсия

Рисуем морозные узоры на SQL - 1

Немного SQL-магии под катом: математика, рекурсия, псевдографика.

Вспоминаем под Новый год формулу угла между векторами:
Рисуем морозные узоры на SQL - 2
Читать полностью »

в 17:15, , рубрики: dba, explain, postgresql, sql, sql tips and tricks, truncate, vacuum, Администрирование баз данных, Алгоритмы, базы данных, высокая производительность

VACUUM может «зачистить» из таблицы в PostgreSQL только то, что никто не может увидеть — то есть нет ни одного активного запроса, стартовавшего раньше, чем эти записи были изменены.

А если такой неприятный тип (продолжительная OLAP-нагрузка на OLTP-базе) все же есть? Как почистить активно меняющуюся таблицу в окружении длинных запросов и не наступить на грабли?

Когда пасует VACUUM — чистим таблицу вручную - 1
Читать полностью »

в 14:57, , рубрики: dba, explain, explain.tensor.ru, postgresql, sql, sql antipatterns, sql tips and tricks, Администрирование баз данных, базы данных, чистка дублей

Случаются ситуации, когда в таблицу без первичного ключа или какого-то другого уникального индекса по недосмотру попадают полные клоны уже существующих записей.

Вычищаем клон-записи из таблицы без PK - 1

Например, пишутся в PostgreSQL COPY-потоком значения хронологической метрики, а потом внезапный сбой, и часть полностью идентичных данных приходит повторно.

Как избавить базу от ненужных клонов?
Читать полностью »

12
Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js