Русский
Русский
English
Статистика
Реклама

Блог компании тензор

Первый парсер на деревне

24.12.2020 16:21:15 | Автор: admin
Сегодня мы померяемся парсерами. Точнее, померяем эффективность разных вариантов JavaScript-парсеров на примере одной простой задачи преобразования строки конкретного формата в объект.


Вопросы сложностей организации многопоточности в JavaScript оставим за рамками этой статьи и сосредоточимся на различных вариантах и малоизвестных способах использования регулярных выражений для разбора строки buffers-атрибута узла плана PostgreSQL.

То есть из строки вида 'Buffers: shared hit=123 read=456, local hit=789' мы хотим как можно быстрее получить JSON такого формата:

{  "shared-hit"  : 123, "shared-read" : 456, "local-hit"   : 789}

Выглядит вроде все тривиально, правда же?


Немного предыстории


Откуда вообще возникла такая задача разбирать строки как можно быстрее?

Я уже рассказывал, что у нас в Тензоре используется много сотен серверов PostgreSQL. И чтобы приглядывать за актуальной производительностью запросов на них, мы разработали коллектор-анализатор логов этой СУБД, который выцепляет из потока от сервера планы запросов, разбирает их и вычисляет нагрузку для каждого отдельного узла, что не так уж и просто.

То есть надо уметь сидеть на потоке и быстро-быстро анализировать (а потому иметь максимальную производительность и минимальный прирост памяти) примерно вот такие блоки текста, а среди них и наши buffers-строки:

Hash Left Join (actual time=9.248..51.659 rows=551 loops=1)  Hash Cond: (c.reloftype = t.oid)  Buffers: shared hit=5814 read=251 dirtied=63  ->  Hash Join (actual time=2.990..7.148 rows=551 loops=1)        Hash Cond: (c.relnamespace = nc.oid)        Buffers: shared hit=4249 read=2        ->  Seq Scan on pg_class c (actual time=0.046..3.922 rows=555 loops=1)              Filter: ((relkind = ANY ('{r,v,f,p}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))              Rows Removed by Filter: 3308              Buffers: shared hit=1829        ->  Hash (actual time=2.931..2.931 rows=7 loops=1)              Buckets: 1024  Batches: 1  Memory Usage: 9kB              Buffers: shared hit=2420 read=2              ->  Seq Scan on pg_namespace nc (actual time=0.035..2.912 rows=7 loops=1)                    Filter: (NOT pg_is_other_temp_schema(oid))                    Rows Removed by Filter: 784                    Buffers: shared hit=2420 read=2  ->  Hash (actual time=6.199..6.199 rows=1629 loops=1)        Buckets: 2048  Batches: 1  Memory Usage: 277kB        Buffers: shared hit=105 read=162 dirtied=63        ->  Hash Join (actual time=0.338..5.640 rows=1629 loops=1)              Hash Cond: (t.typnamespace = nt.oid)              Buffers: shared hit=105 read=162 dirtied=63              ->  Seq Scan on pg_type t (actual time=0.015..4.910 rows=1629 loops=1)                    Buffers: shared hit=57 read=162 dirtied=63              ->  Hash (actual time=0.307..0.307 rows=791 loops=1)                    Buckets: 1024  Batches: 1  Memory Usage: 86kB                    Buffers: shared hit=48                    ->  Seq Scan on pg_namespace nt (actual time=0.004..0.121 rows=791 loops=1)                          Buffers: shared hit=48

Формат строки


В общем случае, формат описан в исходниках PostgreSQL. Если представить его в виде JS-кода, то получится что-то вроде:

const keys = [  ['shared', ['hit', 'read', 'dirtied', 'written']], ['local',  ['hit', 'read', 'dirtied', 'written']], ['temp',   ['read', 'written']] // да, тут другой набор ключей 2-го уровня];let str = 'Buffers: ' + // константное начало  keys    .filter(([keyo, keysi]) => node[keyo])    .map(([keyo, keysi]) => [        keyo      , ...keysi          .filter(keyi => node[keyo][keyi] > 0)          .map(keyi => `${keyi}=${node[keyo][keyi]}`)      ].join(' ') // внутри собираем сегменты через пробел    )    .join(', ');  // снаружи - через запятая-пробел

Методика тестирования


Но поскольку парсить пробелы неинтересно совсем, давайте сразу договоримся, что экспериментировать мы будем на уже нормализованном наборе, где они убраны:

Buffers: shared hit=31770Buffers: shared hit=1159Buffers: shared hit=255Buffers: shared hit=2579 read=2961 dirtied=3Buffers: shared hit=3 read=1Buffers: shared hit=205 read=44Buffers: shared hit=230 read=34 dirtied=3Buffers: shared hit=13Buffers: shared hit=5Buffers: shared hit=6...

Чтобы исключить возможное влияние GC, запускать наши тесты будем с ключами --expose-gc --initial-old-space-size=1024. Оцениваем всех участников по двум показателям: общее время работы и прирост объема памяти, который пришлось использовать (и на чистку которого потом придется потратить время GC и ресурсы CPU).

Шаблон нашего теста будет выглядеть примерно так:

const fs = require('fs');const heapdump = require('heapdump');const buffers = fs.readFileSync('buffers.txt').toString().split('\n');const parseBuffers = line => {// -- 8< --// ...// -- 8< --};global.gc();// нулевое состояние до тестаheapdump.writeSnapshot();const hrb = process.hrtime();for (let line of buffers) {  let obj = parseBuffers(line);}const hre = process.hrtime(hrb);// состояние памяти после тестаheapdump.writeSnapshot();const usec = hre[0] * 1e+9 + hre[1];console.log(usec);

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


И начнем с самого простого.

Бронза: обыкновенный .split


const parseBuffers = line => {  let rv = {};  line.slice('Buffers: '.length)              // "константное" начало нас не интересует    .split(', ').forEach(part => {            // 'shared ..., local ..., temp ...' => ['shared ...', 'local ...', 'temp ...']      let [kind, ...pairs] = part.split(' '); // 'shared hit=1 read=2' => ['shared', ['hit=1', 'read=2']]      pairs.forEach(pair => {        let [type, val] = pair.split('=');    // 'hit=1' => ['hit', '1']        rv[`${kind}-${type}`] = Number(val);  // ['shared-hit'] = 1      });    });  return rv;};

Time, avg: 544msSize Delta: +14.8MB: - (sliced string) : +6.8 // сегменты строк без 'Buffers: ' - (string)        : +6.3 // строки имен ключей - (array)         : +1.7 // массивы pairs

Серебро: .lastIndex + итерация по .matchAll(RegExp)


Итак, сделаем выводы из предыдущего теста: .slice и .split нам не друзья, как и динамическая генерация имен ключей.

С именами ключей все понятно давайте сгенерируем их все заранее, их же всего 10 вариантов. А вот .slice строки мы использовали, только лишь чтобы каждый раз сдвинуть начало анализа на одинаковое начало 'Buffers: '. А нельзя ли как-то сделать это без порождения новых строк?

Оказывается, можно, если использовать принудительную установку re.lastIndex для глобального RegExp.
Подробнее про g- и y-ключи и использование .lastIndex для более точного применения RegExp.

На этот раз будем искать в строке только те ключевые слова, которые нас интересуют:

const buffersRE = /(shared|local|temp)|(hit|read|dirtied|written)=(\d+)/g;const buffersKeys = {  'shared' : {    'hit'     : 'shared-hit'  , 'read'    : 'shared-read'  , 'dirtied' : 'shared-dirtied'  , 'written' : 'shared-written'  }, 'local' : {    'hit'     : 'local-hit'  , 'read'    : 'local-read'  , 'dirtied' : 'local-dirtied'  , 'written' : 'local-written'  }, 'temp' : {    'read'    : 'temp-read'  , 'written' : 'temp-written'  }};const parseBuffers = line => {  let rv = {};  let keys;  buffersRE.lastIndex = 9; // сдвигаем начало поиска на 'Buffers: '.length  for (let match of line.matchAll(buffersRE)) {    if (match[1]) {      keys = buffersKeys[match[1]];    }    else {      rv[keys[match[2]]] = Number(match[3]);    }  }  return rv;};

Time, avg: 270msSize Delta: +8.5MB

Золото: полнопозиционный .match(RegExp)


Но ни в одном из предыдущих вариантов мы никак не использовали информацию о том, что наши потенциальные ключи идут в строго определенном порядке.

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

const buffersRE = /^Buffers:(?:,? shared(?: hit=(\d+))?(?: read=(\d+))?(?: dirtied=(\d+))?(?: written=(\d+))?)?(?:,? local(?: hit=(\d+))?(?: read=(\d+))?(?: dirtied=(\d+))?(?: written=(\d+))?)?(?:,? temp(?: read=(\d+))?(?: written=(\d+))?)?$/;const buffersKeys = ['shared-hit', 'shared-read', 'shared-dirtied', 'shared-written', 'local-hit', 'local-read', 'local-dirtied', 'local-written', 'temp-read', 'temp-written'];const parseBuffers = line =>   line.match(buffersRE)    .slice(1) // в match[0] лежит исходная строка, которая нам не нужна    .reduce(      (rv, val, idx) => (val !== undefined && (rv[buffersKeys[idx]] = Number(val)), rv)    , {}    );

Time, avg: 111msSize Delta: +8.5MB

Наблюдательный читатель сразу же задаст вопрос разве не будет быстрее, если убрать из регулярки константное начало '^Buffers:':

const buffersRE = /(?:,? shared(?: hit=(\d+))?(?: read=(\d+))?(?: dirtied=(\d+))?(?: written=(\d+))?)?(?:,? local(?: hit=(\d+))?(?: read=(\d+))?(?: dirtied=(\d+))?(?: written=(\d+))?)?(?:,? temp(?: read=(\d+))?(?: written=(\d+))?)?$/;

Ведь результат от этого не должен никак измениться? Но нет, такой вариант на четверть хуже:

Time, avg: 140ms

Дело в том, что наш полный RegExp /^...$/ не содержит ни одной переменной части, а в случае без начала для каждой позиции этого сегмента приходится проверять, не начинается ли тут один из хвостов (shared ...|local ...|temp ...) что требует гораздо больше ресурсов, чем просто впустую проверить совпадение двух подстрок.

Вне конкурса: скрещиваем ужа и ежа


В предыдущем варианте мы все-таки внесли начало строки в регулярку, и оно проверяется каждый раз! Давайте же воспользуемся методом с .lastIndex:

  • но он работает только с глобальными RegExp
  • для глобального RegExp обычный .match захватывает сразу всю строку, а не позиционно
  • для получения позиционного набора нам придется использовать первый (а на самом деле, единственный) результат итератора .matchAll


const buffersRE = /(?:,? shared(?: hit=(\d+))?(?: read=(\d+))?(?: dirtied=(\d+))?(?: written=(\d+))?)?(?:,? local(?: hit=(\d+))?(?: read=(\d+))?(?: dirtied=(\d+))?(?: written=(\d+))?)?(?:,? temp(?: read=(\d+))?(?: written=(\d+))?)?$/g;const buffersKeys = ['shared-hit', 'shared-read', 'shared-dirtied', 'shared-written', 'local-hit', 'local-read', 'local-dirtied', 'local-written', 'temp-read', 'temp-written'];const parseBuffers = line => {  buffersRE.lastIndex = 8; // 'Buffers:'.length  return line.matchAll(buffersRE).next().value    .slice(1)    .reduce(      (rv, val, idx) => (val !== undefined && (rv[buffersKeys[idx]] = Number(val)), rv)    , {}    );};

Time, avg: 304msSize Delta: +8.5MB

То есть наш странный гибрид по памяти никакого выигрыша не дал, а по скорости проиграл обоим своим родителям.

Итого


В сегодняшнем забеге кубок вручается обычному полнопозиционному .match(RegExp). Ура, товарищи!
Подробнее..

Мониторинг многопоточных приложений Node.JS

28.12.2020 10:22:36 | Автор: admin
В этой статье мы разберем особенности мониторинга многопоточного Node.JS приложения на примере нашего коллектора для сервиса мониторинга и анализа логов серверов PostgreSQL.


Для масштабирования коллектора мы используем многопроцессный подход, с одним управляющим и несколькими рабочими процессами, межпроцессное взаимодействие происходит только между рабочим и управляющим процессом.
Рабочие процессы выполняют одинаковые задачи сбор, обработка и запись логов с серверов PostgreSQL. При этом сбор и запись это по сути IO-задачи, в которых nodejs очень хороша. А вот обработка и парсинг планов запросов это довольно CPU-емкая задача, блокирующая event-loop. Поэтому такие задачи лучше выносить в отдельный воркер или пул воркеров, передавая им данные на обработку посредством обмена IPC-сообщениями.

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

Например при передаче по IPC буфера, в которой содержится строка 'test' происходит передача строки:
'{"type":"Buffer","data":[116,101,115,116]}'

При большом количестве передаваемых данных накладные расходы могут стать такими:


Решением для нас стало использование worker_threads, появившихся в Node.JS 10.5.0, работающих в рамках одного процесса и позволяющих использовать новые методы коммуникации между потоками.
Архитектура изменилась:

А вместе с ней и подходы к мониторингу. Например, использование CPU внутри worker_thread традиционным способом измерить не получится.
Т.е. раньше, для каждого процесса-воркера, мы измеряли CPU-usage с помощью process.cpuUsage() и process.hrtime() примерно так:
let startCpuUsage = process.cpuUsage();let startTime = process.hrtime();let n = 1000;while (n--) Math.sin(n);let {user, system} = process.cpuUsage(startCpuUsage); // время в микросекундахlet time = process.hrtime(startTime); // наносекундыlet cpuUsage = 100 * 1000 * (user + system) / (time[0] * 1e9 + time[1]);

Но для процесса с worker_threads вызов process.cpuUsage() выдает процессорное время для всего процесса в целом, суммируя все его потоки. И такой же результат мы получим, если вызовем process.cpuUsage() изнутри worker_thread.
Почему так происходит?
Дело в то что process.cpuUsage() использует вызов uv_getrusage, а тот в ОС Linux выполняет системный вызов getrusage с параметром RUSAGE_SELF, т.е. возвращает статистику для вызывающего процесса как сумму по всем его потокам, при этом не важно из какого потока мы делаем этот вызов во всех потоках он будет возвращать одинаковые значения.

Как же получить CPU-usage для worker_threads и почему в Node.JS нет встроенных методов для профилирования CPU worker_threrads?
Здесь есть ответ разработчика worker_threads.
Предложено два варианта либо с помощью системного вызова gettid() получить tid для worker_thread и просматривать данные в /proc/${tid}, либо использовать getrusage() с параметром RUSAGE_THREAD, позволяющим получать статистику только для вызывающего потока.
Кстати, таким же образом можно получать метрики использования CPU и для основного потока процесса, без учета всех дополнительных потоков и worker_threads.

Итак, разобравшись с этим вопросом, мы стали искать готовые модули для мониторинга worker_threads, и не нашли Поэтому сделали свой , заодно добавив в него выдачу всех остальных метрик для мониторинга Node.JS приложения. Серверные метрики мы уже получаем с помощью своей системы сбора метрик .

Мониторинг CPU


Для анализа использования CPU мы берем метрики от воркеров и worker_threads, а также метрики общей загруженности CPU и в разрезе ядер:

  • Для воркеров в целом:

  • Для основных потоков воркеров:

  • Для worker_threads (по первым из пула, но полезным будет и суммарный в разрезе воркера):

  • Общая загрузка CPU на сервере:



С метриками CPU разобрались, а что насчет профайлинга worker_threads?
Проверим, запустив этот небольшой тест с параметром node --prof
Код теста
const { Worker, isMainThread} = require('worker_threads');const crypto = require('crypto');function mainTest() {  let cnt = 10000;  while (cnt--) crypto.randomBytes(cnt);}function threadTest() {  let cnt = 10000;  while (cnt--) crypto.randomBytes(cnt);}if (isMainThread) {  let worker = new Worker(__filename);  setInterval(mainTest, 1000);} else {  setInterval(threadTest, 1000);}


В результате получили два isolate-* файла, для основного потока и для worker_thread.
Далее, с помощью node --prof-process <isolate_file> можем посмотреть нужный профайл.
Кстати, с опцией --no-logfile-per-isolate вместо нескольких isolate* файлов будет один v8.log с суммарным результатом по всем потокам, включая основной.

И еще используя опцию node --inspect или послав сигнал SIGUSR1 работающему процессу с целью снять CPU профайл в Chrome DevTools, мы увидим данные только по основному потоку.

Использование памяти


Также как и для CPU, снимая профайл в Chrome DevTools мы получим Heap snapshot только основного потока.
К счастью, с версии node 12.17.0 появилась возможность получить heap snapshot прямо из кода worker_threads с помощью вызова worker.getHeapSnapshot(), а с версии 11.13.0 также для основного потока вызовом v8.getHeapSnapshot()

Попробуем
const { Worker, isMainThread } = require('worker_threads');const v8 = require('v8');const fs = require('fs');if (isMainThread) {  let worker = new Worker(__filename);  let mainArray = [];  function mainTest() {    let cnt = 100;    while (cnt--) mainArray.push(`main-msg-${cnt}`);  }  process.on('SIGUSR2', () => {    v8.getHeapSnapshot().pipe(fs.createWriteStream(`process_${process.pid}.heapsnapshot`));    worker.getHeapSnapshot().then((heapsnapshot) => {      heapsnapshot.pipe(fs.createWriteStream(`process_${process.pid}_wt_${worker.threadId}.heapsnapshot`));    })  });  setInterval(mainTest, 1000);} else {  let threadArray = [];  function threadTest() {    let cnt = 100;    while (cnt--) threadArray.push(`thread-msg-${cnt}`);  }  setInterval(threadTest, 1000);}


Послав сигнал SIGUSR2 процессу, мы получим два heapsnapshot, которые затем можно проанализировать в Chrome DevTools:

  • Основной процесс:

  • worker_thread:



Какие метрики памяти интересны для анализа?
Мы используем те, что выдает process.memoryUsage() rss, heapTotal, heapUsed, external.
И также v8.getHeapSpaceStatistics(), с его помощью можно получить данные по сегментам Heap new_space, old_space, code_space, large_object_space.
rss всегда выдается для всего процесса, а остальные метрики в рамках вызывающего контекста.

  • Суммарный по воркерам:

  • По воркеру:

  • По worker_threads:



Сборка мусора


Т.к. в каждом worker_thread запускается свой инстанс Node.JS с v8/libuv, то и GC у каждого тоже свой и мониторить их надо по отдельности.
Для мониторинга GC нам нужно получать данные об общей продолжительности сборки мусора, а также количество запусков и время выполнения каждого цикла.
Уже довольно давно, с версии 8.5.0, в Node.JS появился механизм PerformanceObserver, позволяющий кроме всего прочего получить всю необходимую информацию по циклам GC.

Например так
const { PerformanceObserver, constants } = require('perf_hooks');let stats = {};let gcObserver = new PerformanceObserver((list) => {  list    .getEntries()    .map(({kind, duration}) => {      stats['gc.time'] += duration;      switch (kind) {        case constants.NODE_PERFORMANCE_GC_MINOR:          stats['gc.Scavenge.count']++;          stats['gc.Scavenge.time'] += duration;          break;        case constants.NODE_PERFORMANCE_GC_MAJOR:          stats['gc.MarkSweepCompact.count']++;          stats['gc.MarkSweepCompact.time'] += duration;          break;        case constants.NODE_PERFORMANCE_GC_INCREMENTAL:          stats['gc.IncrementalMarking.count']++;          stats['gc.IncrementalMarking.time'] += duration;          break;        case constants.NODE_PERFORMANCE_GC_WEAKCB:          stats['gc.ProcessWeakCallbacks.count']++;          stats['gc.ProcessWeakCallbacks.time'] += duration;          break;      }    })});function resetStats() {  Object.assign(stats, {    'gc.time': 0,    'gc.Scavenge.count': 0,    'gc.Scavenge.time': 0,    'gc.MarkSweepCompact.count': 0,    'gc.MarkSweepCompact.time': 0,    'gc.IncrementalMarking.count': 0,    'gc.IncrementalMarking.time': 0,    'gc.ProcessWeakCallbacks.count': 0,    'gc.ProcessWeakCallbacks.time': 0,  });}resetStats();gcObserver.observe({entryTypes: ['gc'], buffered: true});function triggerScavenge() {  let arr = [];  for (let i = 0; i < 5000; i++) {    arr.push({});  }  setTimeout(triggerScavenge, 50);}let ds = [];function triggerMarkCompact() {  for (let i = 0; i < 10000; i++) {    ds.push(new ArrayBuffer(1024));  }  if (ds.length > 40000) {    ds = [];  }  setTimeout(triggerMarkCompact, 200);}triggerScavenge();triggerMarkCompact();setInterval(() => {  console.log(stats);  resetStats();}, 5000);

Результат:
{  'gc.time': 158.716144,  'gc.Scavenge.count': 11,  'gc.Scavenge.time': 135.690545,  'gc.MarkSweepCompact.count': 2,  'gc.MarkSweepCompact.time': 22.96941,  'gc.IncrementalMarking.count': 2,  'gc.IncrementalMarking.time': 0.056189,  'gc.ProcessWeakCallbacks.count': 0,  'gc.ProcessWeakCallbacks.time': 0}


Этот метод работает как в основном потоке так и в worker_threads, для нашего коллектора мы получаем графики с метриками за секунду:
  • По воркерам

  • По worker_threads

  • Общее время GC в разрезе воркеров

  • Общее время GC в разрезе worker_threads


Event-loop latency


Для мониторинга задержек event-loop удобно использовать появившийся в версии 11.10.0 monitorEventLoopDelay тут можно получить не только среднее и предельные значения, но и различные перцентили.
Мы используем max, min, mean, и percentile(99):
  • Суммарный по всем воркерам

  • Суммарный по worker_threads

  • По воркеру

  • По worker_thread


Мониторинг пула worker_threads


Системные показатели работы пула уже приведены выше, а здесь поговорим о метриках производительности многопоточного приложения.
При старте каждый воркер коллектора запускает пул с одним worker_thread, который обрабатывает очередь поступающих планов запросов.
Дополнительные worker_thread запускаются при увеличении размера очереди и при нахождении задач в очереди дольше определенного времени. Также они автоматически завершаются после периода неактивности.
Код обработки очереди задач
  const SPAWN_LAG = 2000;  this._queue = [];  assignTask(msg) {    if (this.mainExplainer.ready === true) {      this.mainExplainer.ready = false;      this.mainExplainer.sendMessage(msg);    } else if (this._idleExplainers.length > 0) {      let explainer = this._idleExplainers.pop();      clearTimeout(explainer.timeoutIdle);      explainer.sendMessage(msg);    } else {      this._checkAndStartNew(msg);    }  }  _checkAndStartNew(msg) {    let ts = Date.now();    let q = this._queue;    if (msg && process[hasFreeRAM]) q.push({msg, ts});    if (this._canCreateExplainer && q.length > this._workersCnt() && q[0].ts + SPAWN_LAG < ts) {      this._createExplainer();    }  }  explainer.on('explainDone', (msg) => {    explainer.pulse();  });  explainer.pulse = () => {    if (this._queue.length > explainer.id) {      explainer.sendMessage(this._queue.shift().msg);    } else if (this._isMain(explainer)) {      explainer.ready = true;    } else {      this._idleExplainers.push(explainer);      explainer.unpool();    }  };


Важными метриками пула worker_thread являются размер очереди и количество работающих потоков:


Кроме этого мы мониторим скорость и производительность worker_thread и воркеров в целом:
  • Скорость обработки планов запросов:

  • Производительность воркеров по количеству задач:

  • Производительность воркеров по объему данных:

  • Производительность worker_thread по количеству задач:

  • Производительность worker_thread по объему данных:


Заключение


Мы рассмотрели особенности мониторинга многопоточного приложения Node.JS.
Для комплексного анализа работы приложения необходимо отслеживать массу показателей метрики по серверу в целом, использование приложением системных ресурсов, метрики среды выполнения, а также различные показатели самого приложения. В общем всего, что включает в себя APM.
Подробнее..

PostgreSQL Antipatterns скованные одной цепью EXISTS

14.12.2020 14:08:42 | Автор: admin
Я уже как-то рассказывал про особенности вычисления условий в SQL вообще и в PostgreSQL, в частности. Сегодня продолжим тему и попробуем написать и пооптимизировать простой запрос у кого из сотрудников есть на выполнении суперприоритетные задачи.

CREATE TABLE task ASSELECT  id, (random() * 100)::integer person -- всего 100 сотрудников, least(trunc(-ln(random()) / ln(2)), 10)::integer priority -- каждый следующий приоритет в 2 раза менее вероятенFROM  generate_series(1, 1e5) id; -- 100K задачCREATE INDEX ON task(person, priority);

Слово есть в SQL превращается в EXISTS вот с самого простого варианта и начнем:

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 10  );


все картинки планов кликабельны

Пока все выглядит неплохо, но

EXISTS + IN


тут к нам пришли, и попросили к супер отнести не только priority = 10, но еще и 8 и 9:

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority IN (10, 9, 8)  );



Читать стали в 1.5 раза больше, ну и на времени выполнения это сказалось.

OR + EXISTS


Давайте попробуем воспользоваться нашим знанием, что встретить запись с priority = 8 много вероятнее, чем с 10:

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 8  ) OR  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 9  ) OR  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority = 10  );



Обратите внимание, что PostgreSQL 12 уже достаточно умен, чтобы после 100 поисков по значению 8 делать последующие EXISTS-подзапросы только для ненайденных предыдущими всего 13 по значению 9, и лишь 4 по 10.

CASE + EXISTS + ...


На предыдущих версиях аналогичного результата можно добиться, спрятав под CASE последующие запросы:

SELECT  *FROM  generate_series(0, 99) pidWHERE  CASE    WHEN      EXISTS(        SELECT          NULL        FROM          task        WHERE          person = pid AND          priority = 8      ) THEN TRUE    ELSE      CASE        WHEN          EXISTS(            SELECT              NULL            FROM              task            WHERE              person = pid AND              priority = 9          ) THEN TRUE        ELSE          EXISTS(            SELECT              NULL            FROM              task            WHERE              person = pid AND              priority = 10          )      END  END;

EXISTS + UNION ALL + LIMIT


То же самое, но чуть быстрее можно получить, если воспользоваться хаком UNION ALL + LIMIT:

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    (      SELECT        NULL      FROM        task      WHERE        person = pid AND        priority = 8      LIMIT 1    )    UNION ALL    (      SELECT        NULL      FROM        task      WHERE        person = pid AND        priority = 9      LIMIT 1    )    UNION ALL    (      SELECT        NULL      FROM        task      WHERE        person = pid AND        priority = 10      LIMIT 1    )    LIMIT 1  );



Правильные индексы залог здоровья базы


А теперь зайдем на задачу совсем с другой стороны. Если мы точно знаем, что тех task-записей, которые мы хотим найти, в разы меньше, чем остальных так сделаем подходящий частичный индекс. Заодно сразу перейдем от точечного перечисления 8, 9, 10 к >= 8:

CREATE INDEX ON task(person) WHERE priority >= 8;

SELECT  *FROM  generate_series(0, 99) pidWHERE  EXISTS(    SELECT      NULL    FROM      task    WHERE      person = pid AND      priority >= 8  );



В 2 раза быстрее и в 1.5 раза меньше пришлось читать!

Но ведь, наверное, вычитать сразу вообще все подходящие task сразу будет еще быстрее?..

SELECT DISTINCT  personFROM  taskWHERE  priority >= 8;



Далеко не всегда, и точно не в этом случае потому что вместо 100 чтений первых попавшихся записей, на приходится вычитывать больше 400!

А чтобы не гадать, какой из вариантов запроса будет более эффективен, а знать это уверенно пользуйтесь explain.tensor.ru.
Подробнее..

Множественные источники данных в интерфейсе client-side SQL

25.05.2021 12:05:50 | Автор: admin

Иногда в интерфейсе наших приложений СБИС возникаетнеобходимость "сгруппировать" часть записейв некотором списке (например, служебные сообщения в чате, контакты и телефонные звонки).

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

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

Постановка задачи

У нас есть два сервиса. Как бы может быть и больше, но, следуя предыдущей картинке, пусть для определенности это будутсервисы Звонков и Контактов.

Спасибо коллегам из CRM за интересную задачу.

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

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

Группировка нескольких звонков в одну записьГруппировка нескольких звонков в одну запись

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

Неудачное решение #1: "дай мне все"

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

Да и для сервиса задача "отдать все" совсем нелегка, если там данных за несколько лет работы. Но зачем нам "все", если пользовательдальше первой страницы листает очень редко?

Неудачное решение #2: "частый гребень"

Так, нам контакты группировать не надо?.. Давайте тогдазапросим первую страницу (20 записей) с сервиса контактов, адля каждого интерваладат между "соседними" контактами спросим, что там есть в звонках - сразу и количество получим.

А теперь давайте представим, что у нас все звонки (или очень много) оказались хронологически "над" первым же контактом - что будет? Абудут те же самые тормоза в интерфейсе, что и в предыдущем варианте.

Кроме того, мы илиотправим на сервис много запросов(на каждый из интервалов), чем создадим избыточную нагрузку. Или отправимодин запрос со списком всех интервалов, но он заведомо будет отрабатывать "долго".

Удачное решение #1: "чтение сегментами"

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

Что дальше делать с двумяупорядоченными сегментамиданных, достаточно очевидно - сливаем (merge ordered) и отрезаем (limit) от упорядоченного все записи после ближайшего из "крайних" ключей от каждого из сервисов.

Например, в нашем примере получилось, что ключ времени "крайнего" звонка соответствует только 15 из 20 прочитанных контактов. Про порядок оставшихся 5 контактов и других звонков мы не можем ничего сказать, потому что "других звонков" как раз нету в обозримом пространстве - поэтомунарисовать их пока не можем.

Неудачное решение #3: "One Ring to rule them all"

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

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

Неудачное решение #4: "два ключа на server-side"

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

Поскольку у насstateless server-side БЛ, то либо мы их таки и не сохраним, или вынуждены будем городить где-тоотдельное хранилище состояний. Сделать это можно, но совсем не просто:

  • хранение сегментов должно происходить по уникальномуключу экземпляра-выборки-на-странице

  • необходимаполитика инвалидацииэтих данных со временем, чтобы память не "текла"

  • работа с этим хранилищем подразумеваетдополнительные издержки на сериализацию-пересылку-десериализацию

Удачное решение #2: "два ключа на client-side"

Собственно, а зачем нам уносить все это на сервер-сайд, если вседанные нам нужны только на клиенте?.. Давайте их там и оставим.

То есть ровно теданные, которые "не отрисовали" оставить хранитьсяна клиенте (например, прямо в памяти вкладки, даже не в localStorage), пока нам не понадобится их нарисовать.

В нашем предыдущем примере получится что-то вроде:

  • прочитали параллельно 20 контактов и 20 звонков

  • звонки "сгруппировали" в 5 записей

  • нарисовали 5 "групповых" звонков + 15 контактов

  • 5 ненарисованных конктактов оставили в хранилище

  • до 20 чего-то не хватает? запрашиваем! (контакты и звонки по 20, параллельно от своих "крайних" ключей)

  • "задача сведена к предыдущей", только у нас уже сразу 25 контактов на 20 звонков есть

Edge Cases

Фактически, единственный отрицательный эффект у такого решения - последняя нарисованная"групповая" запись может "крутить счетчиком", пока мы дочитываем "вливающиеся" в нее все новые и новые записи.

К счастью, такая ситуация достаточнопозитивно воспринимается в интерфейсе, поскольку мы показываем пользователю: "Эй, все хорошо, мы не умерли, не повисли, мы работаем!"

Подробнее..

Анализируем слона по частям

29.10.2020 14:07:14 | Автор: admin
Если вы регулярно отлаживаете производительность запросов к PostgreSQL, а EXPLAIN (ANALYZE, BUFFERS) ... любимый инструмент познания особенностей работы этой СУБД, то новые полезные фишки нашего сервиса визуализации и анализа планов explain.tensor.ru наверняка пригодятся вам в этом нелегком деле.

Но сразу напомню, что без полноценного всестороннего мониторинга базы PostgreSQL использовать только анализ плана это выступать с позиции мудреца #5!


[источник КДПВ, Слепые и слон]

Самуил Маршак, 1940
Ученый спор

Слепцы, числом их было пять,
В Бомбей явились изучать
Индийского слона.
Исследовав слоновий бок,
Один сказал, что слон высок
И прочен, как стена.

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

Ощупал третий два клыка,
И утверждает он:
На два отточенных штыка
Похож индийский слон!

Слепец четвертый, почесав
Колено у слона,
Установил, что слон шершав,
Как старая сосна.

А пятый, подойдя к слону
Со стороны хвоста,
Определил, что слон в длину
Не больше чем глиста.

Возникли распри у слепцов
И длились целый год.
Потом слепцы в конце концов
Пустили руки в ход.

А так как пятый был силен,
Он всем зажал уста.
И состоит отныне слон
Из одного хвоста!

Итак, сегодня в программе:

  • меняем шевроны на погоны
  • сводим мега-планы
  • ведем персональный архив
  • изучаем генеалогию планов
  • всматриваемся в окна

Не цветом единым!


Исторически, при просмотре плана самые горячие узлы мы отмечали вертикальным шевроном слева от значения чем больше значение, тем насыщеннее цвет.



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



Полезная статистика для мега-планов


Многие не замечают у плана вкладку Статистика, вот она справа:


А кто и заметил вряд ли активно пользовался. Мы решили исправить это упущение, и сделать ее действительно полезной для анализа крупногабаритных планов (100+ узлов).

Группировка узлов


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

А для наглядности каждый тип узла несет цветовую метку:

  • красная чтение данных
    узлы Seq Scan, Index Scan, CTE Scan и разные другие ... Scan
  • желтая обработка данных
    узлы Sort, Unique, Aggregate, Group, Materialize, ...
  • зеленая соединения
    узлы Nested Loop, Merge Join, Hash Join, ...


Сортировка по любому показателю


Если вдруг вам требуется анализ не по суммарному времени, а типу узла, например просто кликните по заголовку столбца и все будет:



Контекстная подсказка узла


Чтобы детально понять вклад конкретного узла в группе, наведите на номер любого из них и увидите традиционную подсказку, что конкретно там происходило:



Персональный архив планов

Без регистрации и SMS!

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


Генеалогия планов


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

Просто укажите имя при добавлении плана:



или на уже существующем плане вы можете задать его, отредактировать или добавить связанный план:



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



А если ссылка на конкретный план вдруг потерялась, его можно легко опознать по имени в персональном архиве:


Всматриваемся в окна


Небольшое, но полезное усовершенствование Query Profiler, о котором я писал ранее мы научили его заглядывать в окна и правильно сопоставлять с узлами плана:

->  WindowAgg   ==>  WINDOW / OVER      ->  Sort  ==>    PARTITION BY / ORDER BY

как несколько независимых определений окна (WINDOW) в рамках одного запроса:



так и сортировки в оконных функциях без явного определения:



Удачной охоты на разные неэффективности!
Подробнее..

One Tool to Analyze Them All

07.12.2020 12:09:16 | Автор: admin
Мы рады сообщить о реализации на explain.tensor.ru базовой поддержки анализа и визуализации планов, специфичных для PostgreSQL-совместимых решений: Timescale, Citus, Greenplum и Redshift.


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

EXPLAIN <-> SQL


В развитие темы сопоставления узлов плана и запроса добавлена возможность быстрого просмотра и переключения между ними:



Поддерживается определение узлов [Parallel] Custom Scan (ChunkAppend):




Собственное время Task-узлов, выполняющихся на отдельных нодах кластера, определяется на основании фактического Execution Time:



Время родителя Custom Scan (Citus Adaptive) вычисляется корректно, даже в случае одновременного выполнения Task сразу на нескольких узлах кластера:


Подробнее об анализе EXPLAIN в Citus.


Поддерживаются специфичные узлы вроде Broadcast Motion, Redistribute Motion, Gather Motion, Partition Selector, Sequence:


Подробнее об анализе EXPLAIN в Greenplum.


Поддерживается работа со всеми XN-узлами, включая специфичные XN Network, XN Merge, XN Window и data redistribution у Join-узлов:



Подробнее об анализе EXPLAIN в Redshift.



Если вдруг вы нашли что-то, что мы пока не учли напишите в комментариях.
Подробнее..

Анализируем слона вместе с коллегами

15.06.2021 14:10:21 | Автор: admin

Если ваша жизнь DBA, сопровождающего PostgreSQL, наполнена вопросами "а почему так медленно?" и "как сделать, чтобы запрос не тормозил?", наш сервис анализа и визуализации планов запросовexplain.tensor.ru сделает ее немного легче за счет привлечения коллег и обновленных подсказок.

м/ф "Следствие ведут Колобки"м/ф "Следствие ведут Колобки"

"Ландон из зе кепитал оф Грейт Британ"

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

Обсуждайте проблемный план там, где вам удобноОбсуждайте проблемный план там, где вам удобно

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

Подсказки к плану

Про базовый набор подсказок и способов ими воспользоваться я уже рассказывал в статье "Рецепты для хворающих SQL-запросов" - теперь мы сделали их еще больше и удобнее!

Рекомендательные подсказки узлов планаРекомендательные подсказки узлов плана
  • цветовая и текстовая кодировка разных видов

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

  • несколько подсказок у одного узла

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

  • на самом видном месте

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

Все подсказки - вместе, клик - и вы на местеВсе подсказки - вместе, клик - и вы на месте

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

Масштабируемая диаграмма

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

Карта будущего сражения за производительностьКарта будущего сражения за производительность

Пользуйтесь! Возникнут идеи или замечания - прошу в комментарии.

Подробнее..

PostgreSQL Antipatterns работаем с отрезками в кровавом энтерпрайзе

10.11.2020 10:22:20 | Автор: admin
В различных бизнес-приложениях регулярно возникает необходимость решить какую-либо задачу с отрезками/интервалами. Самое сложное в них понять, что это именно одна из таких задач.


Как правило, они отчаянно маскируются, и даже у нас в СБИС их найти можно в абсолютно разных сферах управления предприятием: контроле рабочего времени, оценке загрузки линий АТС или даже в бухгалтерском учете.
Отличие enterprise [решения] от всего остального он всегда идёт от запросов бизнеса и решает какую-то бизнес-задачу. [src]
Вот и давайте посмотрим, какие именно прикладные задачи и как можно решить с помощью PostgreSQL и сократить время анализа данных с нескольких секунд на бизнес-логике до десятков миллисекунд, умея эффективно применять следующие алгоритмы непосредственно внутри SQL-запроса:

  • поиск отрезков, пересекающих точку/интервал
  • слияние отрезков по максимальному перекрытию
  • подсчет количества отрезков в каждой точке

Отрезки в точке


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



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

CREATE TABLE ranges(  owner    integer, dtb -- начало отрезка    date, dte -- окончание отрезка    date);INSERT INTO ranges(  owner, dtb, dte)SELECT  (random() * 1e3)::integer, dtb, dtb + (random() * 1e2)::integerFROM  (    SELECT      now()::date - (random() * 1e3)::integer dtb    FROM      generate_series(1, 1e5)  ) T;

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

BETWEEN + btree


Как правило, первый вариант запроса будет выглядеть как-то так:

SELECT  *FROM  rangesWHERE  '2020-01-01'::date BETWEEN dtb AND dte;

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

CREATE INDEX ON ranges(dtb, dte);

И это не работает примерно никак, потому что мы все равно перебрали всю таблицу (100K записей, из которых 95K отбросили):


Чтобы понять почему, достаточно представить визуально, как именно должен работать такой индекс, как мы делали это в статье DBA: находим бесполезные индексы: значение dtb ограничено только справа, dte только слева, в результате имеем два диапазонных ключа, которые совместно по btree работают плохо.

*range + GiST


Но, конечно же, эффективное решение есть это использование возможностей диапазонных типов и GiST-индексов:

CREATE INDEX ON ranges  USING gist( -- используем "пространственный" GiST-индекс    daterange(dtb, dte, '[]') -- формируем диапазон дат  );

Наш запрос теперь нужно модифицировать к такому виду:

SELECT  *FROM  rangesWHERE  daterange(dtb, dte, '[]') @> '2020-01-01'::date; -- значение входит в диапазон



С помощью подходящего индекса мы сократили время работы запроса в 3 раза.

Отрезки по группе


Но зачем нам все 5K записей больничных? Ведь, как правило, нас интересуют только коллеги по отделу то есть какая-то конкретная группа отрезков, которую надо отфильтровать из общего набора.



Поэтому усложним задачу и добавим в нашу модель принадлежность одному из отделов:

ALTER TABLE ranges ADD COLUMN segment integer;UPDATE ranges SET segment = (random() * 1e2)::integer;

SELECT  *FROM  rangesWHERE  segment = 1 AND  daterange(dtb, dte, '[]') @> '2020-01-01'::date;



Индекс, конечно, по-прежнему используется, но теперь получается, что 99% данных мы вычитываем зря 4945 строк отбрасывается при проверке сегмента.

Решение очевидно добавим нужное нам поле в индекс, да?..

CREATE INDEX ON ranges USING gist(segment, daterange(dtb, dte, '[]'));-- ОШИБКА:  для типа данных integer не определён класс операторов по умолчанию для метода доступа "gist"-- HINT:  Вы должны указать класс операторов для индекса или определить класс операторов по умолчанию для этого типа данных.

Увы, нет GiST-индекс просто так не поддерживает операции над скалярными типами. Зато если подключить расширение btree_gist научится:

CREATE EXTENSION btree_gist;CREATE INDEX ON ranges USING gist(segment, daterange(dtb, dte, '[]'));



Наш запрос избавился от всех неиндексных фильтраций и стал в 20 раз быстрее! До кучи, еще и читать стал в 10 раз меньше данных (buffers).

Отрезки на интервале


Но пересечение отрезков с точкой требуется гораздо реже, чем пересечение отрезков с интервалом.
Типичная бизнес-задача: "кто ходил в отпуск в этом месяце?", "какие задачи в моем календаре на сегодня?"



Собственно, все нужные для этого индексы у нас уже есть, осталось правильно записать условие:

SELECT  *FROM  rangesWHERE  segment = 1 AND  daterange(dtb, dte, '[]') && daterange('2020-01-01', '2020-02-01', '[)'); -- пересечение отрезков, без включения правой границы



Объединение отрезков


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



Если бы мы знали заранее все интервалы, участвующие в объединении, то могли бы просто написать через оператор "+":

SELECT int4range('[0,2]') + int4range('[1,3]');-- [0,4)

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



Вариант, работающий даже в версии 8.4, приведен в PostgreSQL Wiki:

WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT  min(s) s, max(e) eFROM  (    SELECT      s    , e    , max(new_start) OVER(ORDER BY s, e) left_edge    FROM      (        SELECT          s        , e        , CASE            WHEN s < max(le) OVER(ORDER BY s, e) THEN              NULL            ELSE              s          END new_start        FROM          (            SELECT              s            , e            , lag(e) OVER(ORDER BY s, e) le            FROM              rng          ) s1      ) s2  ) s3GROUP BY  left_edge;

Но план у него выглядит не слишком приятно сортировка, три WindowAgg, группировка:



И обратите внимание, что в точке 10 он не сливает касающиеся интервалы, хотя обычно это требуется как в нашей задаче про отпуска.

Давайте сконструируем запрос, который нас устроит:

  1. Отсортируем все отрезки по паре (начало, конец).
  2. Для каждого начала вычислим, стоит ли оно правее максимального из предшествующих ему концов. Если левее вообще ничего нет или самый правый конец из предыдущих все равно левее это точка начала искомого интервала.
  3. Отнесем все отрезки в группы по количеству предшествующих им найденных начал.
  4. В каждой группе берем наименьшее из начал и наибольший из концов это и есть искомый непрерывный интервал.



WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT -- min/max по группе  min(s) s, max(e) eFROM  (    SELECT      *    , sum(ns::integer) OVER(ORDER BY s, e) grp -- определение групп    FROM      (        SELECT          *        , coalesce(s > max(e) OVER(ORDER BY s, e ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), TRUE) ns -- начало правее самого правого из предыдущих концов == разрыв        FROM          rng      ) t  ) tGROUP BY  grp;

Ну, и одно выполнение WindowAgg нам удалось отыграть:


Длина объединенных отрезков


Если же перед нами не стоит цели вычислять сами целевые отрезки, а нужна только их суммарная длина, то можно сократить количество WindowAgg до единственного узла:

  1. Для каждого отрезка вычисляем максимум концов всех предыдущих.
  2. Берем разность между концом отрезка и большим из начала отрезка и найденного конца.
  3. Осталось только просуммировать полученные разности.



WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT  sum(delta)FROM  (    SELECT      *    , greatest(        e - greatest(          max(e) OVER(ORDER BY s, e ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)        , s        )      , 0      ) delta    FROM      rng  ) T;


Спасибо imschur за этот вариант оптимизации.

Подсчет отрезков на каждом интервале


Итак, мы смогли узнать, сколько всего времени у нас работали не все сотрудники. Но ведь их отсутствовало разное количество в разные промежутки времени а сколько именно когда?
Типичная бизнес-задача: анализ и распределение нагрузки например, между операторами call-центра: "Сколько звонков мы обслуживаем одновременно? Сколько для этого нужно операторов в ночной смене?"
  1. Присвоим каждому началу отрезка вес +1, а каждому концу -1.
  2. Просуммируем накопительно значения в каждой точке это и есть количество отрезков на интервале, начинающемся с этой точки и вплоть до следующей по порядку.



WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT DISTINCT ON(p) -- уникализация до единственного значения в точке  p, sum(v) OVER(ORDER BY p) qty -- накопительная суммаFROM  (    SELECT      s p    , +1 v    FROM      rng  UNION ALL    SELECT      e p    , -1 v    FROM      rng  ) TORDER BY  1;

На этом сегодня все. Если знаете еще какие-то полезные нетривиальные алгоритмы работы с отрезками для решения бизнес-задач напишите в комментариях.
Подробнее..

SQL HowTo префиксный FTS-поиск с релевантностью по дате

18.12.2020 00:05:23 | Автор: admin
В нашем СБИС, как и в любой другой системе работы с документами, по мере накопления данных у пользователей возникает желание их "поискать".

Но, поскольку люди не компьютеры, то и ищут они примерно как "что-то там такое было от Иванова или от Ивановского нет, не то, раньше, еще раньше вот оно!"

То есть технически верное решение это префиксный полнотекстовый поиск с ранжированием результатов по дате.

Но разработчику это грозит жуткими проблемами ведь для FTS-поиска в PostgreSQL используются пространственные типы индексов GIN и GiST, которые не предусматривают подсовывания дополнительных данных, кроме текстового вектора.

Остается только грустно вычитывать все записи по совпадению префикса (тысячи их!) и сортировать или, наоборот, идти по индексу даты и фильтровать все встречающиеся записи на совпадение префикса, пока не найдем подходящие (как скоро найдется абракадабра?..).

И то, и другое не особо приятно для производительности запроса. Или что-то все же можно придумать для быстрого поиска?

Сначала сгенерируем наши тексты-на-дату:

CREATE TABLE corpus ASSELECT  id, dt, strFROM  (    SELECT      id::integer    , now()::date - (random() * 1e3)::integer dt -- дата где-то за последние 3 года    , (random() * 1e2 + 1)::integer len -- длина "текста" до 100    FROM      generate_series(1, 1e6) id -- 1M записей  ) X, LATERAL(    SELECT      string_agg(        CASE          WHEN random() < 1e-1 THEN ' ' -- 10% на пробел          ELSE chr((random() * 25 + ascii('a'))::integer)        END      , '') str    FROM      generate_series(1, len)  ) Y;

Наивный подход #1: gist + btree


Попробуем накатить индекс и для FTS, и для сортировки по дате вдруг да помогут:

CREATE INDEX ON corpus(dt);CREATE INDEX ON corpus USING gist(to_tsvector('simple', str));

Будем искать все документы, содержащие слова, начинающиеся на 'abc...'. И, для начала, проверим, что таких документов достаточно немного, и FTS-индекс используется нормально:

SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*');



Ну он, конечно, используется, но занимает это больше 8 секунд, что явно не то, что мы хотели бы потратить на поиск 126 записей.

Может, если добавить сортировку по дате и искать только последние 10 записей станет лучше?

SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*')ORDER BY  dt DESCLIMIT 10;



Но нет, просто сверху добавилась сортировка.

Наивный подход #2: btree_gist


Но ведь есть же отличное расширение btree_gist, которое позволяет подсунуть скалярное значение в GiST-индекс, что должно нам дать возможность сразу использовать индексную сортировку с помощью оператора расстояния <->, который можно использовать для kNN-поисков:

CREATE EXTENSION btree_gist;CREATE INDEX ON corpus USING gist(to_tsvector('simple', str), dt);

SELECT  *FROM  corpusWHERE  to_tsvector('simple', str) @@ to_tsquery('simple', 'abc:*')ORDER BY  dt <-> '2100-01-01'::date DESC -- сортировка по "расстоянию" от даты далеко в будущемLIMIT 10;



Увы, это не помогает примерно никак.

Геометрия в помощь!


Но отчаиваться рано! Посмотрим на список встроенных классов операторов GiST оператор расстояния <-> доступен только для геометрических circle_ops, point_ops, poly_ops, а с версии PostgreSQL 13 и для box_ops.

Так давайте попробуем перевести нашу задачу в плоскость нашим использующимся для поиска парам (слово, дата) присвоим координаты некоторых точек, чтобы префиксные слова и недалеко отстоящие даты находились как можно ближе:



Разбиваем текст на слова


Конечно, наш поиск будет не вполне полнотекстовым, в том смысле, что нельзя задавать условие для нескольких слов одновременно. Но уж префиксным-то точно будет!

Сформируем вспомогательную таблицу-словарь:

CREATE TABLE corpus_kw ASSELECT  id, dt, kwFROM  corpus, LATERAL (    SELECT      kw    FROM      regexp_split_to_table(lower(str), E'[^\\-a-zа-я0-9]+', 'i') kw    WHERE      length(kw) > 1  ) T;

В нашем примере на 1M текстов пришлось 4.8M слов.

Укладываем слова


Чтобы перевести слово в его координату, представим что это число, записанное в системе счисления с основанием 2^16 (ведь UNICODE-символы мы тоже хотим поддержать). Только записывать мы его будем начиная с фиксированной 47-й позиции:



Можно было бы начинать и с 63-й позиции, это даст нам значения чуть меньше 1E+308, предельных для double precision, но тогда возникнет переполнение при построении индекса.

Получается, что на координатной оси все слова окажутся упорядочены:



ALTER TABLE corpus_kw ADD COLUMN p point;UPDATE  corpus_kwSET  p = point(    (      SELECT        sum((2 ^ 16) ^ (64 - i) * ascii(substr(kw, i, 1)))      FROM        generate_series(1, length(kw)) i    )  , extract('epoch' from dt)  );CREATE INDEX ON corpus_kw USING gist(p);

Формируем поисковый запрос


WITH src AS (  SELECT    point(      ( -- копипасту можно вынести в функцию        SELECT          sum((2 ^ 16) ^ (48 - i) * ascii(substr(kw, i, 1)))        FROM          generate_series(1, length(kw)) i      )    , extract('epoch' from dt)    ) ps  FROM    (VALUES('abc', '2100-01-01'::date)) T(kw, dt) -- поисковый запрос)SELECT  *, src.ps <-> kw.p dFROM  corpus_kw kw, srcORDER BY  dLIMIT 10;



Теперь у нас на руках id искомых документов, уже отсортированных в нужном порядке и заняло это меньше 2ms, в 4000 раз быстрее!

Небольшая ложка дегтя


Оператор <-> ничего не знает про наше упорядочение по двум осям, поэтому искомые наши данные находятся лишь в одной из правых четвертей, в зависимости от необходимой сортировки по дате:



Ну и выбрать мы все-таки хотели сами тексты-документы, а не их ключевые слова, поэтому нам понадобится давно забытый индекс:

CREATE UNIQUE INDEX ON corpus(id);

Доработаем запрос:

WITH src AS (  SELECT    point(      (        SELECT          sum((2 ^ 16) ^ (48 - i) * ascii(substr(kw, i, 1)))        FROM          generate_series(1, length(kw)) i      )    , extract('epoch' from dt)    ) ps  FROM    (VALUES('abc', '2100-01-01'::date)) T(kw, dt) -- поисковый запрос), dc AS (  SELECT    (      SELECT        dc      FROM        corpus dc      WHERE        id = kw.id    )  FROM    corpus_kw kw  , src  WHERE    p[0] >= ps[0] AND -- kw >= ...    p[1] <= ps[1]     -- dt DESC  ORDER BY    src.ps <-> kw.p  LIMIT 10)SELECT  (dc).*FROM  dc;



Нам немного добавили возникшие InitPlan с вычислением константных x/y, но все равно мы уложились в те же 2 мс!

Ложка дегтя #2


Ничто не дается бесплатно:

SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class WHERE relname LIKE 'corpus%';

corpus          | 242 MB -- исходный набор текстовcorpus_id_idx   |  21 MB -- это его PKcorpus_kw       | 705 MB -- ключевые слова с датамиcorpus_kw_p_idx | 403 MB -- GiST-индекс

242 MB текстов превратились в 1.1GB поискового индекса.

Но ведь в corpus_kw лежат дата и само слово, которые мы в самом-то поиске уже никак не использовали так давайте их удалим:

ALTER TABLE corpus_kw  DROP COLUMN kw, DROP COLUMN dt;VACUUM FULL corpus_kw;

corpus_kw       | 641 MB -- только id и point

Мелочь а приятно. Помогло не слишком сильно, но все-таки 10% объема удалось отыграть.
Подробнее..

DBA прибираем мертвые души

12.05.2021 18:04:50 | Автор: admin

Иногда при выполнении длительных или плохо написанных запросов в PostgreSQL происходят разные неприятные вещи типа внезапного сбоя процесса или краша всего сервера.

В таких случаях на носителе могут остаться "мертвые души" - файлы (иногда совсем немаленькие, а вполне сравнимые по объему со всей остальной базой), которые были созданы во время работы процесса в качестве временного хранилища промежуточных данных.

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

Сегодня посмотрим, как их можно найти и безболезненно "зачистить".

Разыскиваем temp buffers

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

Получить такой эффект достаточно просто - забыть поставить или выбрать слишком большой предел рекурсии:

explain (analyze, buffers)WITH RECURSIVE T AS (  SELECT    0 i  , '' sUNION ALL  SELECT    i + 1  , repeat('a', i + 1)  FROM    T  WHERE    i < 1e4 -- 10k итераций)TABLE T ORDER BY s DESC LIMIT 1;

[просмотреть на explain.tensor.ru]

Корень беды заключается в том, что для сортировки рекурсивной выборки T необходимо вычислить и куда-то записать ее полностью, что и показывает атрибут temp written:

->  CTE Scan on t  (actual time=0.008..374.157 rows=10001 loops=1)      Buffers: temp written=6126

Давайте теперь сэмулируем неприятность, случившуюся во время выполнения запроса - увеличим для этого ограничение рекурсии на порядок:

SELECT pg_backend_pid();-- 15004 - это PID процесса, обслуживающего наше клиентское соединениеexplain (analyze, buffers)WITH RECURSIVE T AS (  SELECT    0 i  , '' sUNION ALL  SELECT    i + 1  , repeat('a', i + 1)  FROM    T  WHERE    i < 1e5 -- 100k итераций)TABLE T ORDER BY s DESC LIMIT 1;
kill -9 15004
Плохо "убитый" клиентский процесс тянет за собой postmaster и весь PostgreSQL-серверПлохо "убитый" клиентский процесс тянет за собой postmaster и весь PostgreSQL-сервер

Сервер быстро упал - быстро поднялся. Но место на диске у нас убыло почти на 4GB - где же они?

Найти их нам поможет функция получения списка временных файлов pg_ls_tmpdir:

SELECT * FROM pg_ls_tmpdir();
 name            |  size      |  modificationpgsql_tmp15004.0 | 1073741824 | 2021-05-12 10:46:10+03pgsql_tmp15004.1 | 1073741824 | 2021-05-12 10:46:38+03pgsql_tmp15004.2 | 1073741824 | 2021-05-12 10:47:06+03pgsql_tmp15004.3 |  958078976 | 2021-05-12 10:47:31+03

Данная функция появилась только в PostgreSQL 12, поэтому если версия вашего сервера младше, придется воспользоваться pg_ls_dir по <data>/base/pgsql_tmp - это как раз то место, где сохраняются временные файлы, которые мы ищем.

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

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/pgsql_tmp' dir), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir), tmp AS (  SELECT    *  , regexp_replace(fn, '^pgsql_tmp(\d+).*$', '\1')::integer pid  , (pg_stat_file(dir || '/' || fn)).*  FROM    ls)SELECT  dir || '/' || fnFROM  tmpLEFT JOIN  pg_stat_activity sa    USING(pid)WHERE  sa IS NOT DISTINCT FROM NULL;

Теперь осталось пройти по полученному списку и поудалять. Замечу, что если "прибивать" запрос через pg_terminate_backend(pid), то и сервер не "падает", и подобного "мусора" в каталоге не остается.

Ничейные TEMPORARY TABLE

CREATE TEMPORARY TABLE x ASSELECT  i, repeat('a', i::integer) sFROM  generate_series(1, 1e5) i;

Теперь в списке схем нашего соединения появилась pg_temp_5:

SELECT current_schemas(true);-- {pg_temp_5,pg_catalog,public}

Именно на эту схему проецируется обращение к псевдосхеме pg_temp - то есть в этом соединении запросы TABLE x, TABLE pg_temp.x и TABLE pg_temp_5.x будут эквивалентны, пока эта временная таблица существует.

Но раз эта таблица полноценная, а не "полуфабрикат", как в случае temp buffers, то мы должны бы увидеть ее и в pg_class:

SELECT  oid, relnamespace::regnamespace, relname, relfilenodeFROM  pg_classWHERE  relnamespace::regnamespace::text ~ '^pg(_toast)?_temp';
 oid  |  relnamespace   |  relname             |  relfilenode66112 | pg_toast_temp_5 | pg_toast_66109       | 6611266114 | pg_toast_temp_5 | pg_toast_66109_index | 6611466109 | pg_temp_5       | x                    | 66109

Выяснение такой странной нумерации схем приводит к письму Tom Lane аж от февраля 2003:

> What is the origin of these schemas? local temporary tables? sorts?

Right, they're made to hold temporary tables. The first time a givenbackend does CREATE TEMP TABLE, it looks for a pg_temp_n schema, and makes it if it's not there. On shutdown, it removes the temp tables, but it seemed like a waste of cycles to remove the pg_temp_n schema itself.

(ObTrivialFact: the 'n' is the backend's pgproc slot number, so it's known not to be in use by any concurrently running backend. But it will certainly be used again in future.)

Итак, при штатном гашении сервера сами файлы временных таблиц должны быть вычищены. Собственно, а где они?

В отличие от temp buffers, относящихся ко всему серверу, файлы временных таблиц и индексов относятся к конкретной базе, но имеют несколько другой формат имени:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/' || oid dir  FROM    pg_database  WHERE    datname = current_database()), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir)SELECT  *FROM  lsWHERE  fn ~ '^t';
 dir                |  fn.../data/base/16393 | t5_66109.../data/base/16393 | t5_66112.../data/base/16393 | t5_66114

То есть имя файла временного объекта выглядит как t<temp schema N>_<temp object OID>. Если сейчас мы "уроним" сервер снова, эти файлы останутся, как и записи в pg_class.

Чтобы избавиться от них, можно прогнать VACUUM FULL по всей базе, но это практически невозможно, если она достаточно велика. Или просто подождать когда то же самое доберется сделать autovacuum:

LOG: autovacuum: found orphan temp table "tst.pg_temp_5.x"

Но если таблиц в базе немало, это может наступить ой как нескоро, а дисковое пространство по-прежнему будет занято.

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

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/' || oid dir  FROM    pg_database  WHERE    datname = current_database()), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir), lsid AS (  SELECT    *  , (pg_stat_file(dir || '/' || fn)).*  FROM    ls  WHERE    fn ~ '^t'), sch AS (  SELECT DISTINCT    regexp_replace(fn, '^t(\d+)_.*$', '\1') sch  FROM    lsid  WHERE    modification < (      SELECT        stats_reset      FROM        pg_stat_database      WHERE        datid = 0    ))SELECT  string_agg('DROP SCHEMA ' || nspname || ' CASCADE;', E'\n') sFROM  schJOIN  pg_namespace nsp    ON nsp.nspname IN ('pg_temp_' || sch, 'pg_toast_temp_' || sch);

Получаем готовый текст запроса, который останется только выполнить:

DROP SCHEMA pg_temp_5 CASCADE;DROP SCHEMA pg_toast_temp_5 CASCADE;
Подробнее..

Из песочницы Фаззинг тестирование веб-интерфейса. Расшифровка доклада

10.11.2020 14:15:42 | Автор: admin


В начале этого года Тензор проводил митап в городе Иваново, на котором я выступил с докладом про эксперименты с фаззинг-тестированием интерфейса. Тут расшифровка этого доклада.

Когда обезьяны заменят всех QA? Можно ли отказаться от ручного тестирования и UI автотестов, заменив их фаззингом? Как будет выглядеть полная диаграмма состояний и переходов для простого TODO приложения? Пример реализации и о том, как работает такой фаззинг далее под катом.

Всем привет! Меня зовут Докучаев Сергей. Последние 7 лет я занимаюсь тестированием во всех его проявлениях в компании Тензор.



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



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



В фильме Матрица в одной из сцен Морфеус предлагает Нео выбрать красную или синюю таблетку. Томас Андерсон работал программистом и мы помним какой выбор он сделал. Будь он отъявленным тестировщиком слопал бы обе таблетки, чтобы посмотреть, как система поведёт себя в нестандартных условиях.

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

Однако, в создании и поддержке автотестов внезапно не так много авто- и довольно много ручной работы:

  1. Нужно придумать что и как протестировать.
  2. Нужно найти элементы на странице, вбить нужные локаторы в Page Objects.
  3. Написать и отладить код.
  4. При любых изменениях актуализировать сценарий. Причём если функционал/интерфейс очень часто меняются, то автотесты оказываются не у дел, а ROI стремится к нулю.



К счастью, в мире тестирования существует ни две и не три таблетки. А целая россыпь: манки-тестирование, формальные методы, фаззинг-тестирование, решения на основе AI. И ещё больше их комбинаций.



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



Допустим сделали мы такой TODO и хотим его проверить. Берём подходящий сервис или инструмент и видим обезьянок в действии:



По такому же принципу мой кот как-то, полежав на клавиатуре, безвозвратно сломал презентацию и её пришлось делать заново:



Удобно, когда после 10 действий приложение выбрасывает исключение. Тут наша обезьянка сразу понимает, что произошла ошибка, а мы по логам можем понять хотя бы приблизительно, как она повторяется. А что если ошибка произошла после 100К случайных кликов и выглядит как валидный ответ? Единственным значимым плюсом этого подхода является максимальная простота ткнул кнопку и готово.



Противоположностью такого подхода являются формальные методы.



Это фотография Нью-Йорка в 2003 году. Одно из самых ярких и многолюдных мест на планете, Таймс-сквер, освещают только фары, проезжающих мимо машин. В тот год миллионы жителей Канады и США на три дня оказались в каменном веке из-за каскадного отключения электростанций. Одной из ключевых причин произошедшего оказалась race condition ошибка в ПО.

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



На слайде часть модели алгоритма двух рукопожатий, написанной на языке TLA+. Думаю для всех очевидно, что использование этих инструментов при проверке формочек на сайте сравни постройке Боинга 787 для проверки аэродинамических свойств кукурузника.



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

Фаззинг тестирование сейчас чаще всего рассматривается в контексте тестирования безопасности. И типовую схему, демонстрирующую такой подход, возьмём из OWASP гайда:



Тут у нас есть сайт, который требуется протестировать, есть БД с тестовыми данными и инструменты, при помощи которых будем отправлять указанные данные на сайт. Вектора представляют из себя обычные строки, которые были получены опытным путём. Такие строки с наибольшей вероятностью могут привести к обнаружению уязвимости. Это как та кавычка, которую многие на автомате ставят на место числа в URL из адресной строки.



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



Пользователь вводит новую дату и нажимает кнопку Сохранить. На сервер улетает запрос, с данными в json формате.



И если всё хорошо, то сервис отвечает двухсотым кодом.



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



И если мы в ответ вместо сообщения о невалидной дате получили исключение, то фиксируем ошибку.

Фаззить API несложная задача. Вот у нас передаваемые параметры в jsonе, вот мы отправляем запрос, получаем ответ и анализируем его. А как быть с GUI?

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



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



С каждым из контролов мы можем сделать не так-то и много. У нас есть мышка с двумя кнопками, колёсиком и клавиатура. Можно кликать по элементу, наводить на него курсор мыши, в текстовые поля можно вводить текст.

Если мы введём в текстовое поле какой-то текст и нажмём Enter, то наша страница перейдёт из одного состояния в другое:



Схематически это можно изобразить вот так:



Из этого состояния мы можем перейти в третье добавив ещё одну задачу в список:



А можем удалить добавленную задачу, вернувшись в первое состояние:



Или кликнуть по надписи TODOs и остаться во втором состоянии:



А теперь попробуем реализовать Proof-of-Concept такого подхода.



Для работы с браузером возьмём chromedriver, работать с диаграммой состояний и переходов будем через python библиотеку NetworkX, а рисовать будем через yEd.



Запускаем браузер, создаём инстанс графа, в котором между двумя вершинами может быть множество связей с разной направленностью. И открываем наше приложение.



Теперь мы должны описать состояние приложения. Из-за алгоритма сжатия изображения, мы можем использовать размер картинки в формате PNG как идентификатор состояния и через метод __eq__ реализовать сравнение этого состояния с другими. Через атрибут iterated мы фиксируем, что были прокликаны все кнопки, введены значения во все поля в этом состоянии, чтобы исключить повторную обработку.



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



При фаззинге текущего состояния мы должны каждый раз возвращаться в это состояние из нового. Для этого мы используем функцию nx.shortest_path, которая вернёт список элементов, которые нужно прокликать, чтобы перейти из базового состояния в текущее.
Для того, чтобы дождаться окончания реакции приложения на наши действий в функции wait используется Network Long Task API, показывающий занят ли JS какой-либо работой.

Вернёмся к нашему приложению. Исходное состояние имеет следующий вид:



После десяти итераций по приложению мы получим такую диаграмму состояний и переходов:



Через 22 итерации вот такой вид:



Если же запустить наш скрипт на несколько часов, то он внезапно сообщит, что обошёл все возможные состояния, получив следующую диаграмму:



Так, с простым демонстрационным приложением мы справились. А что будет, если натравить этот скрипт на реальное веб-приложение. А будет хаос:



Мало того, что на бэкенде происходят изменения, сама по себе страница постоянно перерисывается при реакции на таймеры или события, при выполнении одних и тех же действий мы можем получить разные состояния. Но даже в таких приложениях можно найти куски функционала, с которыми наш скрипт может справиться без значительных доработок.
Возьмём для испытаний страницу аутентификации СБИС:



И для неё достаточно быстро получилось построить полную диаграмму состояний и переходов:





Отлично! Теперь мы можем обходить все состояния приложения. И чисто в теории найти все ошибки, которые зависят от действий. Но как научить программу понимать, что перед ней ошибка?

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



Рассмотрим последний паттерн а раньше было по-другому. Автотесты ведь регрессионным тестированием занимаются.

Вернёмся к графу после 10 итерации по TODO:



Сломаем код, который отвечает за открытие корзины и вновь прогоним 10 итераций:



А далее сравним два графа и найдём разницу в состояниях:



Можем подвести итог для данного подхода:



В текущем виде этот приём можно использовать для тестирования небольшого приложения и выявления очевидных или регрессионных ошибок. Для того, чтобы методика взлетела для больших приложений с нестабильным GUI потребуются значительные доработки.
Весь исходный код и список использованных материалов можно найти в репозитории: https://github.com/svdokuchaev/venom. Тем, кто хочет разобраться с применением фаззинга в тестировании, очень рекомендую The Fuzzing Book. Там в одной из частей описан такой же подход к фаззингу простых html форм.

Подробнее..

PostgreSQL Antipatterns DBA-детектив, или Три дела о потерянной производительности

18.11.2020 10:21:27 | Автор: admin
Сегодня вместо решения абстрактных алгоритмических задач мы выступим в роли детектива, по крупицам доставшейся информации исследующего неэффективные запросы, и рассмотрим три реальных дела, встречавшихся в разное время на просторах нашего приложения СБИС, когда простота и наивность при написании SQL превращалась в дополнительную нагрузку для PostgreSQL-сервера.


Дедукция и индукция помогут нам вычислить, что же все-таки хотел получить от СУБД разработчик, и почему это получилось не слишком оптимально. Итак, сегодня нас ждут:

  • Дело о непростом пути вверх
    Разберем в live-видео на реальном примере некоторые из способов улучшения производительности иерархического запроса.
  • Дело о худеющем запросе
    Увидим, как можно запрос упростить и ускорить в несколько раз, пошагово применяя стандартные методики.
  • Дело о развесистой клюкве
    Восстановим структуру БД на основании единственного запроса с 11 JOIN и предложим альтернативный вариант решения на ней той же задачи.

#1: Дело о непростом пути вверх



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

WITH RECURSIVE h AS (  SELECT    n."@Номенклатура" id  , ARRAY[      coalesce(        (          SELECT            ne."Info"          FROM            "NomenclatureExt" ne          WHERE            ne."@Номенклатура" = n."@Номенклатура"          LIMIT 1        )      , '{}'      )    ] res  , n."Раздел" -- предок по иерархии  FROM    "Номенклатура" n  WHERE    n."@Номенклатура" = ANY($1::integer[])UNION -- уникализация  SELECT    h.id  , array_append(      h.res    , coalesce(        (          SELECT            ne."Info"          FROM            "NomenclatureExt" ne          WHERE            ne."@Номенклатура" = n."@Номенклатура"          LIMIT 1        )      , '{}'      )    ) -- расширение массива  , n."Раздел"  FROM    "Номенклатура" n  , h  WHERE    n."@Номенклатура" = h."Раздел" -- двигаемся вверх по иерархии в сторону предков)SELECT  h.id, h.resFROM  hWHERE  h."Раздел" IS NULL;

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

Что/зачем делает запрос?


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

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

WITH RECURSIVE / Path
На этом же шаге, помимо самого ID номенклатурной карточки, мы получаем идентификатор ее предка по иерархии и начинаем формировать массив-путь.

Subquery
Обратим внимание, что для каждой найденной записи номенклатуры будет произведен поиск связанной записи в соседней таблице NomenclatureExt. Явно это какая-то расширенная информация по номенклатурной карточке, связанная 1-в-1.

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

Path Filter
Ну, и в финале мы оставляем только те записи, которые являются финальными в цепочке то есть мы простроили путь вверх отдельно для каждого исходного идентификатора.

Проблемы в запросе


Какие очевидные проблемы при выполнении данного запроса нам грозят?

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

    Представим, что на первом шаге мы передали 60 идентификаторов в запрос и нашли эти 60 карточек из одного раздела. Несмотря на это, мы будем искать одного и того же предка те же 60 раз.
  2. Повторная вычитка связанной записи
    Поскольку мы вычитываем связанную запись независимо ни от чего, то и этот поиск мы делаем ровно столько же раз, сколько вычитываем запись основную.
  3. Вложенный запрос под уникализацией
    Одно и то же значение из связанной записи вычитывается каждый раз, и только после этого схлапывается до единственного экземпляра.

    То есть в нашем примере 59 из 60 вложенных запросов будут выполнены заведомо абсолютно зря.

Обратим внимание на конкретный вариант плана такого запроса:

  • 107 карточек вычитано Bitmap Scan на стартовой итерации рекурсии и плюсом к ним 107 индексных поисков связанных
  • Поскольку PostgreSQL заранее не понимает, сколько и каких записей мы найдем вверх по иерархии, он вычитывает сразу все 18K из номенклатуры с помощью Seq Scan. В результате, из 22мс выполнения запроса 12мс мы потратили на чтение всей таблицы и еще 5мс на ее хэширование, итого больше 77%.
  • Из вычитанных 18K нужными нам по результату Hash Join окажутся только 475 штук и теперь добавим к ним еще 475 Index Scan по связанным записям.
  • Итого: 22мс и 2843 buffers суммарно.

Что/как можно исправить?


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

  1. Поскольку нам нужны сразу и идентификатор самой карточки, и идентификатор ее предка, будем вычитывать записи сразу целиком как (tableAlias).
  2. Вычитку будем производить с помощью конструкции = ANY(ARRAY(...)), исключая возможность возникновения неудобных JOIN.
  3. Для возможности уникализации и хэширования скастуем записи таблицы в (row)::text.
  4. Поскольку внутри рекурсии обращение к рекурсивной части может быть только однократным и строго не внутри вложенных запросов, вместо этого материализуем ее внутри отдельной CTE.
  5. Таблицу состоящую из единственного столбца можно свернуть с помощью ARRAY(TABLE X) до скалярного значения-массива. А если в ней и так одна запись, то использовать ее с нужной раскастовкой (TABLE X)::integer[].

-- рекурсивный подъем вверх до корня с поиском только уникальных записей, it AS (  SELECT    it::text -- иначе не работает уникализация через UNION  FROM    "Номенклатура" it  WHERE    "@Номенклатура" = ANY((TABLE src)::integer[])UNION  (    WITH X AS (      SELECT DISTINCT        (it::"Номенклатура")."Раздел"      FROM        it      WHERE        (it::"Номенклатура")."Раздел" IS NOT NULL    )    SELECT      it2::text    FROM      "Номенклатура" it2    WHERE      "@Номенклатура" = ANY(ARRAY(TABLE X))  ))

Поскольку мы получили только набор самих записей, теперь нам надо выстроить из них цепочки потомок-предок рекурсивным спуском сверху-вниз:

-- рекурсивный спуск вниз для формирования "пути" к каждой карточке, itr AS (  SELECT    ARRAY[(it::"Номенклатура")."@Номенклатура"] path  , it::"Номенклатура" -- запись исходной таблицы  FROM    it  WHERE    (it::"Номенклатура")."Раздел" IS NULL -- стартуем от "корневых" записейUNION ALL  SELECT    ARRAY[((_it.it)::"Номенклатура")."@Номенклатура"] || itr.path -- наращиваем "путь" спереди  , (_it.it)::"Номенклатура"  FROM    itr  JOIN    it _it      ON ((_it.it)::"Номенклатура")."Раздел@" IS NOT FALSE AND      ((_it.it)::"Номенклатура")."Раздел" = (itr.it)."@Номенклатура")

Теперь из полученных путей-из-ID восстановим значение поля Info из связанной таблицы. Но бегать так по каждому ID несколько раз для преобразования каждого отдельного пути будет очень долго, поэтому:

  • Соберем весь набор ID, встречающихся в путях. Но это ровно тот же набор, который дают ID самих наших извлеченных записей.
  • Извлечем опять сразу все нужные нам записи связанной таблицы за один проход через = ANY(ARRAY(...)).
  • Сложим все полученные значения нужного поля в hstore-словарик.

-- формируем словарь info для каждого ключа, чтобы не бегать по записям CTE, hs AS (  SELECT    hstore(      array_agg("@Номенклатура"::text)    , array_agg(coalesce("Info", '{}'))    )  FROM    "NomenclatureExt"  WHERE    "@Номенклатура" = ANY(ARRAY(      SELECT        (it)."@Номенклатура"      FROM        itr    )))

Остался последний шаг преобразовать цепочку ID в цепочку Info с помощью ARRAY(SELECT ... unnest(...)):

, ARRAY(    SELECT      (TABLE hs) -> id::text -- извлекаем данные из "словаря"    FROM      unnest(path) id  ) res

В результате, целиком наш запрос будет выглядеть примерно так:

-- список всех исходных IDWITH RECURSIVE src AS (  SELECT $1::integer[] -- набор ID в виде сериализованного массива)-- рекурсивный подъем вверх до корня с поиском только уникальных записей, it AS (  SELECT    it::text -- иначе не работает уникализация через UNION  FROM    "Номенклатура" it  WHERE    "@Номенклатура" = ANY((TABLE src)::integer[])UNION  (    WITH X AS (      SELECT DISTINCT        (it::"Номенклатура")."Раздел"      FROM        it      WHERE        (it::"Номенклатура")."Раздел" IS NOT NULL    )    SELECT      it2::text    FROM      "Номенклатура" it2    WHERE      "@Номенклатура" = ANY(ARRAY(TABLE X))  ))-- рекурсивный спуск вниз для формирования "пути" к каждой карточке, itr AS (  SELECT    ARRAY[(it::"Номенклатура")."@Номенклатура"] path  , it::"Номенклатура"  FROM    it  WHERE  WHERE    (it::"Номенклатура")."Раздел" IS NULL -- стартуем от "корневых" записейUNION ALL  SELECT    ARRAY[((_it.it)::"Номенклатура")."@Номенклатура"] || itr.path -- наращиваем "путь" спереди  , (_it.it)::"Номенклатура"  FROM    itr  JOIN    it _it      ON ((_it.it)::"Номенклатура")."Раздел@" IS NOT FALSE AND      ((_it.it)::"Номенклатура")."Раздел" = (itr.it)."@Номенклатура")-- формируем словарь info для каждого ключа, чтобы не бегать по записям CTE, hs AS (  SELECT    hstore(      array_agg("@Номенклатура"::text)    , array_agg(coalesce("Info", '{}'))    )  FROM    "NomenclatureExt"  WHERE    "@Номенклатура" = ANY(ARRAY(      SELECT        (it)."@Номенклатура"      FROM        itr    )))-- строим цепочку info для каждого id из оригинального набораSELECT  path[1] id, ARRAY(    SELECT      (TABLE hs) -> id::text -- извлекаем данные из "словаря"    FROM      unnest(path) id  ) resFROM  itrWHERE  path[1] = ANY((TABLE src)::integer[]); -- ограничиваемся только стартовым набором

  • Теперь на каждом шаге рекурсии (а их получается 4, в соответствии с глубиной дерева) мы добавляем, в среднем, всего по 12 записей.
  • Восстановление путей вниз заняло большую часть времени 10мс. Можно сделать и меньше, но это гораздо сложнее.
  • Итого, новый запрос выполняется 15мс вместо 22мс и читает только лишь 860 страниц данных вместо 2843, что имеет принципиальное влияние на время работы, когда нет возможности обеспечить постоянное присутствие этих данных в кэше.

#2: Дело о худеющем запросе



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

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

Регулярно возникают реплики типа "Вот ты ускорил запрос в 10 раз, но всего на 10мс оно же того не стоит! Мы лучше поставим еще пару реплик! Вместо 100MB памяти получилось 1MB? Да нам проще памяти на сервер добавить!"

Тут какой момент разработчик, вооруженный набором стандартных приемов, на оптимизацию запроса тратит константное время (= деньги), а с увеличением функционала и количества пользователей нагрузка на БД растет примерно как N(logN), а даже не линейно. То есть если сейчас ваш проект ест CPU базы на 50%, готовьтесь к тому, что уже через год вам придется ставить еще один такой же сервер (= деньги), потом еще и еще

Оптимизация запросов не избавляет от добавления мощностей, но сильно отодвигает их в будущее. Добившись вместо нагрузки в 50% всего 10%, вы сможете не расширять железо еще года 2-3, а вложить те же деньги, например, в увеличение штата или чьей-то зарплаты.

00: исходное состояние


00: исходный запрос, 7.2мс
WITH personIds("Персона") AS (  SELECT    $1::uuid[]), persons AS (  SELECT    P."Персона"  , coalesce(P."Фамилия", '') "Фамилия"  , coalesce(P."Имя", '') "Имя"  , coalesce(P."Отчество", '') "Отчество"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ФамилияЛица"        ELSE          P."Фамилия"      END    , ''    ) "ФамилияЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ИмяЛица"        ELSE          P."Имя"      END    , ''    ) "ИмяЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ОтчествоЛица"        ELSE          P."Отчество"      END    , ''    ) "ОтчествоЛица"  , P."Примечание"  , P."Обновлено"  , P."Уволен"  , P."Группа"  , P."Пол"  , P."Логин"  , P."Город"  , P."ДатаРождения"  , P."$Создано"::date "ДатаРегистрации"  , coalesce(P."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(P."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  FROM    "Персона" P  WHERE    "Персона" = ANY((TABLE personids)::uuid[])), counts AS (  SELECT    NULL c), users AS (  SELECT    hstore(      array_agg("Персона"::text)    , array_agg(udata::text)    )  FROM    (      SELECT        "Персона"::text      , array_agg(u::text) udata      FROM        "Пользователь" u      WHERE        "Персона" IN (          SELECT            "Персона"          FROM            persons        ) AND        (          "Главный" OR          (            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )        )      GROUP BY 1    ) u2), T1 AS (  SELECT    persons."Персона"  , persons."Фамилия"  , persons."Имя"  , persons."Отчество"  , persons."ФамилияЛица"  , persons."ИмяЛица"  , persons."ОтчествоЛица"  , persons."Примечание"  , persons."Обновлено"  , persons."Город"  , coalesce(persons."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(persons."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , counts.c "Всего"  , persons."Группа"  , (      SELECT        ARRAY(          SELECT            row_to_json(t2)          FROM            (              SELECT                "Пользователь" >> 32 as "Account"              , "Пользователь" & x'FFFFFFFF'::bigint "Face"              , coalesce("ЕстьПользователь", TRUE) "HasUser"              , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE) "HasLoggedIn"              , coalesce("Уволен", persons."Уволен") "Fired"              FROM                (                  SELECT                    *                  FROM                    (                      SELECT                        (udata::"Пользователь").*                      FROM                        unnest(((TABLE users) -> "Персона"::text)::text[]) udata                    ) udata15                  WHERE                    "Уволен" IS DISTINCT FROM TRUE AND                    "Удален" IS DISTINCT FROM TRUE                ) udata2            ) t2        )    )::text[] "Users"  , coalesce(      (        SELECT          row_to_json(t3)        FROM          (            SELECT              "Пользователь" >> 32 as "Account"            , "Пользователь" & x'FFFFFFFF'::bigint "Face"            FROM              (                SELECT                  (udata::"Пользователь").*                FROM                  unnest(((TABLE users) -> "Персона"::text)::text[]) udata              ) udata2            WHERE              "Уволен" IS DISTINCT FROM TRUE AND              "Удален" IS DISTINCT FROM TRUE AND              "Пользователь" >> 32 = 5313189::int            ORDER BY              "ЕстьПользователь" DESC, "Входил" DESC            LIMIT 1          ) t3      )    , (        SELECT          row_to_json(t4)        FROM          (            SELECT              "Пользователь" >> 32 as "Account"            , "Пользователь" & x'FFFFFFFF'::bigint "Face"            FROM              (                SELECT                  (udata::"Пользователь").*                FROM                  unnest(((TABLE users) -> "Персона"::text)::text[]) udata              ) udata2            WHERE              "Уволен" IS DISTINCT FROM TRUE AND              "Удален" IS DISTINCT FROM TRUE AND              "Главный"            ORDER BY              "ЕстьПользователь" DESC, "Входил" DESC            LIMIT 1          ) t4      )    , (        SELECT          row_to_json(t5)        FROM          (            SELECT              "Пользователь" >> 32 as "Account"            , "Пользователь" & x'FFFFFFFF'::bigint "Face"            FROM              (                SELECT                  (udata::"Пользователь").*                FROM                  unnest(((TABLE users) -> "Персона"::text)::text[]) udata              ) udata2            WHERE              "Уволен" IS DISTINCT FROM TRUE AND              "Удален" IS DISTINCT FROM TRUE            LIMIT 1          ) t5      )    ) "PrimaryFaceAccount"  , (      SELECT        "Пользователь" >> 32      FROM        (          SELECT            "Пользователь"          FROM            (              SELECT                (udata::"Пользователь").*              FROM                unnest(((TABLE users) -> "Персона"::text)::text[]) udata            ) udata2          WHERE            "Главный"        ) t3      LIMIT 1    ) "MainAccount"  , ARRAY(      SELECT        "Значение"::int      FROM        "КонтактныеДанные"      WHERE        persons."Группа" AND        "Персона" = persons."Персона" AND        "Тип" = 'account'    ) "АккаунтыГруппы"  , persons."Пол"  , persons."Логин"  , persons."ДатаРождения"  , persons."ДатаРегистрации"  FROM    persons  , counts)SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"    ELSE      "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  T1;



Даже беглого взгляда на диаграмму выполнения достаточно, чтобы сразу увидеть, что в плане встречаются подозрительно одинаковые куски (SubPlan 8, SubPlan 10, SubPlan 12, SubPlan 14, SubPlan 16), внутри которых время тратится на unnest записей из массива внутри CTE.

Эти субпланы соответствуют подзапросам по развороту массива пользователей из hstore по ключу каждой отдельной персоны:

  , coalesce(      (        SELECT          row_to_json(T)        FROM          (            SELECT              ...            FROM              (                SELECT                  (udata::"Пользователь").*                FROM                  unnest(((TABLE users) -> "Персона"::text)::text[]) udata              ) udata2            WHERE              ...            ORDER BY              ...            LIMIT 1          ) T      )

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

  1. Можно ли сделать все то же самое за один проход? Конечно! В этом нам помогут FILTER (9.4+) и LATERAL (9.3+).
  2. Вместо построения JSON независимо в 5 разных местах (по одним и тем же записям, в основном). Построим эти JSON сразу для каждой исходной записи в полном (5 ключей) и коротком (2 ключа) вариантах.
  3. Сортировка исходного набора совпадает во всех местах, где используется. Где не используется значит, непринципиально для данных, и ее можно использовать все равно.
  4. LIMIT 1 можно успешно заменить на извлечение первого элемента массива: arr[1]. Так что собираем по каждому условию именно массивы.
  5. Для одновременного возврата нескольких агрегатов используем сериализацию в ARRAY[aggx::text, aggy::text].

01. FILTER + LATERAL + single JSON (4мс, -45%)
WITH personIds("Персона") AS (  SELECT    $1::uuid[]), persons AS (  SELECT    P."Персона"  , coalesce(P."Фамилия", '') "Фамилия"  , coalesce(P."Имя", '') "Имя"  , coalesce(P."Отчество", '') "Отчество"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ФамилияЛица"        ELSE          P."Фамилия"      END    , ''    ) "ФамилияЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ИмяЛица"        ELSE          P."Имя"      END    , ''    ) "ИмяЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ОтчествоЛица"        ELSE          P."Отчество"      END    , ''    ) "ОтчествоЛица"  , P."Примечание"  , P."Обновлено"  , P."Уволен"  , P."Группа"  , P."Пол"  , P."Логин"  , P."Город"  , P."ДатаРождения"  , P."$Создано"::date "ДатаРегистрации"  , coalesce(P."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(P."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  FROM    "Персона" P  WHERE    "Персона" = ANY((TABLE personids)::uuid[])), counts AS (  SELECT    NULL c), users AS (  SELECT    hstore(      array_agg("Персона"::text)    , array_agg(udata::text)    )  FROM    (      SELECT        "Персона"::text      , array_agg(u::text) udata      FROM        "Пользователь" u      WHERE        "Персона" IN (          SELECT            "Персона"          FROM            persons        ) AND        (          "Главный" OR          (            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )        )      GROUP BY 1    ) u2), T1 AS (  SELECT    persons."Персона"  , persons."Фамилия"  , persons."Имя"  , persons."Отчество"  , persons."ФамилияЛица"  , persons."ИмяЛица"  , persons."ОтчествоЛица"  , persons."Примечание"  , persons."Обновлено"  , persons."Город"  , coalesce(persons."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(persons."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , counts.c "Всего"  , persons."Группа"-- 8< --  , coalesce(usjs[1]::text[], '{}') "Users"  , coalesce(      (usjs[2]::json[])[1]    , (usjs[3]::json[])[1]    , (usjs[4]::json[])[1]    ) "PrimaryFaceAccount"  , ((usjs[5]::json[])[1] ->> 'Account')::bigint "MainAccount"-- 8< --  , ARRAY(      SELECT        "Значение"::int      FROM        "КонтактныеДанные"      WHERE        persons."Группа" AND        "Персона" = persons."Персона" AND        "Тип" = 'account'    ) "АккаунтыГруппы"  , persons."Пол"  , persons."Логин"  , persons."ДатаРождения"  , persons."ДатаРегистрации"  FROM    persons  , counts-- 8< --  , LATERAL (      SELECT        ARRAY[ -- массив сериализованных json[]          array_agg(json_f) FILTER (WHERE -- фильтрация по необходимому условию            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Пользователь" >> 32 = 5313189::int          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Главный"          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Главный"          )::text        ] usjs      FROM        (          SELECT -- готовим JSON для каждой записи сразу, потом только используем готовый            json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            , 'HasUser'            , coalesce("ЕстьПользователь", TRUE)            , 'HasLoggedIn'            , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE)            , 'Fired'            , coalesce("Уволен", persons."Уволен")            ) json_f          , json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            ) json_s          , *          FROM            (              SELECT                (unnest).*              FROM                unnest(((TABLE users) -> "Персона"::text)::"Пользователь"[])            ) T          ORDER BY -- сортировка одна на всех            "ЕстьПользователь" DESC, "Входил" DESC        ) T    ) usjs-- 8< --)SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"    ELSE      "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  T1;



План уже много приятнее и много короче. Кто самое слабое звено теперь? unnest!

Так, стоп Мы в unnest по каждой персоне разворачиваем массив, который ранее засунули в hstore с ключом этой же персоны? А физически-то мы все равно отбираем в hstore независимо по каждой персоне.

Я это к тому, что мы сначала нашли, сгруппировали, сериализовали, потом достали, десериализовали, развернули Что бы серверу не поработать-то?..

  1. В общем, выносим формирование JSON в подзапрос именно по каждой из персон. В результате у нас исчезает CTE users и hstore.

02. Подзапрос (4мс, -45%)
WITH personIds("Персона") AS (  SELECT    $1::uuid[]), persons AS (  SELECT    P."Персона"  , coalesce(P."Фамилия", '') "Фамилия"  , coalesce(P."Имя", '') "Имя"  , coalesce(P."Отчество", '') "Отчество"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ФамилияЛица"        ELSE          P."Фамилия"      END    , ''    ) "ФамилияЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ИмяЛица"        ELSE          P."Имя"      END    , ''    ) "ИмяЛица"  , coalesce(      CASE        WHEN nullif(P."ФамилияЛица", '') IS NOT NULL THEN          P."ОтчествоЛица"        ELSE          P."Отчество"      END    , ''    ) "ОтчествоЛица"  , P."Примечание"  , P."Обновлено"  , P."Уволен"  , P."Группа"  , P."Пол"  , P."Логин"  , P."Город"  , P."ДатаРождения"  , P."$Создано"::date "ДатаРегистрации"  , coalesce(P."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(P."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"-- 8< --  , (      SELECT        ARRAY[ -- массив сериализованных json[]          array_agg(json_f) FILTER (WHERE -- фильтрация по необходимому условию            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Пользователь" >> 32 = 5313189::int          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Главный"          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Главный"          )::text        ] usjs      FROM        (          SELECT -- готовим JSON для каждой записи сразу, потом только используем готовый            json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            , 'HasUser'            , coalesce("ЕстьПользователь", TRUE)            , 'HasLoggedIn'            , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE)            , 'Fired'            , coalesce("Уволен", P."Уволен")            ) json_f          , json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            ) json_s          , *          FROM            "Пользователь"          WHERE            "Персона" = P."Персона" AND            (              "Главный" OR              (                "Уволен" IS DISTINCT FROM TRUE AND                "Удален" IS DISTINCT FROM TRUE              )            )          ORDER BY -- сортировка одна на всех            "ЕстьПользователь" DESC, "Входил" DESC        ) T    ) usjs-- 8< --  FROM    "Персона" P  WHERE    "Персона" = ANY((TABLE personids)::uuid[])), counts AS (  SELECT    NULL c), T1 AS (  SELECT    persons."Персона"  , persons."Фамилия"  , persons."Имя"  , persons."Отчество"  , persons."ФамилияЛица"  , persons."ИмяЛица"  , persons."ОтчествоЛица"  , persons."Примечание"  , persons."Обновлено"  , persons."Город"  , coalesce(persons."ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce(persons."ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , counts.c "Всего"  , persons."Группа"  , coalesce(usjs[1]::text[], '{}') "Users"  , coalesce(      (usjs[2]::json[])[1]    , (usjs[3]::json[])[1]    , (usjs[4]::json[])[1]    ) "PrimaryFaceAccount"  , ((usjs[5]::json[])[1] ->> 'Account')::bigint "MainAccount"  , ARRAY(      SELECT        "Значение"::int      FROM        "КонтактныеДанные"      WHERE        persons."Группа" AND        "Персона" = persons."Персона" AND        "Тип" = 'account'    ) "АккаунтыГруппы"  , persons."Пол"  , persons."Логин"  , persons."ДатаРождения"  , persons."ДатаРегистрации"  FROM    persons  , counts)SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"  ELSE    "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  T1;



Кто теперь выглядит лишним?

  1. Очевидно, CTE personids (заменяем на inline-параметр с раскастовкой) и CTE counts (вообще какой-то странный атавизм, возвращающий один NULL).
  2. После этого замечаем, что все выборки у нас стали из единственной таблички, поэтому лучше убрать избыточные алиасы.

03. Inline-параметры (3.9мс, -46%)
WITH persons AS (  SELECT    "Персона"  , coalesce("Фамилия", '') "Фамилия"  , coalesce("Имя", '') "Имя"  , coalesce("Отчество", '') "Отчество"  , coalesce(      CASE        WHEN nullif("ФамилияЛица", '') IS NOT NULL THEN          "ФамилияЛица"        ELSE          "Фамилия"      END    , ''    ) "ФамилияЛица"  , coalesce(      CASE        WHEN nullif("ФамилияЛица", '') IS NOT NULL THEN          "ИмяЛица"        ELSE          "Имя"      END    , ''    ) "ИмяЛица"  , coalesce(      CASE        WHEN nullif("ФамилияЛица", '') IS NOT NULL THEN          "ОтчествоЛица"        ELSE          "Отчество"      END    , ''    ) "ОтчествоЛица"  , "Примечание"  , "Обновлено"  , "Уволен"  , "Группа"  , "Пол"  , "Логин"  , "Город"  , "ДатаРождения"  , "$Создано"::date "ДатаРегистрации"  , coalesce("ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce("ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , (      SELECT        ARRAY[ -- массив сериализованных json[]          array_agg(json_f) FILTER (WHERE -- фильтрация по необходимому условию            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Пользователь" >> 32 = 5313189::int          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Главный"          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Главный"          )::text        ] usjs      FROM        (          SELECT -- готовим JSON для каждой записи сразу, потом только используем готовый            json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            , 'HasUser'            , coalesce("ЕстьПользователь", TRUE)            , 'HasLoggedIn'            , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE)            , 'Fired'            , coalesce("Уволен", p."Уволен")            ) json_f          , json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            ) json_s          , *          FROM            "Пользователь"          WHERE            "Персона" = p."Персона" AND            (              "Главный" OR              (                "Уволен" IS DISTINCT FROM TRUE AND                "Удален" IS DISTINCT FROM TRUE              )            )          ORDER BY -- сортировка одна на всех            "ЕстьПользователь" DESC, "Входил" DESC        ) T    ) usjs  FROM    "Персона" p  WHERE-- 8< --    "Персона" = ANY($1::uuid[])-- 8< --), T1 AS (  SELECT    "Персона"  , "Фамилия"  , "Имя"  , "Отчество"  , "ФамилияЛица"  , "ИмяЛица"  , "ОтчествоЛица"  , "Примечание"  , "Обновлено"  , "Город"  , coalesce("ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"  , coalesce("ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"  , NULL::bigint "Всего"  , "Группа"  , coalesce(usjs[1]::text[], '{}') "Users"  , coalesce(      (usjs[2]::json[])[1]    , (usjs[3]::json[])[1]    , (usjs[4]::json[])[1]    ) "PrimaryFaceAccount"  , ((usjs[5]::json[])[1] ->> 'Account')::bigint "MainAccount"  , ARRAY(      SELECT        "Значение"::int      FROM        "КонтактныеДанные"      WHERE        persons."Группа" AND        "Персона" = persons."Персона" AND        "Тип" = 'account'    ) "АккаунтыГруппы"  , "Пол"  , "Логин"  , "ДатаРождения"  , "ДатаРегистрации"  FROM    persons)SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"  ELSE    "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  T1;


Смотрим теперь на запрос очень-очень пристально, и задумываемся:

  1. Зачем нам лишняя CTE T1 (ведь CTE Scan стоит ресурсов)?
  2. Зачем мы один и тот же список полей переписываем дважды?
  3. Зачем дважды применяется coalesce на одни и те же поля?

04. Убрали все лишнее (3.2мс, -56%)
WITH p AS (  SELECT    *-- 8< --  , CASE      WHEN nullif("ФамилияЛица", '') IS NOT NULL THEN        ARRAY[          coalesce("ФамилияЛица", '')        , coalesce("ИмяЛица", '')        , coalesce("ОтчествоЛица", '')        ]      ELSE        ARRAY[          coalesce("Фамилия", '')        , coalesce("Имя", '')        , coalesce("Отчество", '')        ]    END fio-- 8< --  , (      SELECT        ARRAY[ -- массив сериализованных json[]          array_agg(json_f) FILTER (WHERE -- фильтрация по необходимому условию            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Пользователь" >> 32 = 5313189::int          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE AND            "Главный"          )::text        , array_agg(json_s) FILTER (WHERE            "Уволен" IS DISTINCT FROM TRUE AND            "Удален" IS DISTINCT FROM TRUE          )::text        , array_agg(json_s) FILTER (WHERE            "Главный"          )::text        ] usjs      FROM        (          SELECT -- готовим JSON для каждой записи сразу, потом только используем готовый            json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            , 'HasUser'            , coalesce("ЕстьПользователь", TRUE)            , 'HasLoggedIn'            , coalesce("ЕстьПользователь", TRUE) AND coalesce("Входил", TRUE)            , 'Fired'            , coalesce("Уволен", p."Уволен")            ) json_f          , json_build_object(              'Account'            , "Пользователь" >> 32            , 'Face'            , "Пользователь" & x'FFFFFFFF'::bigint            ) json_s          , *          FROM            "Пользователь"          WHERE            "Персона" = p."Персона" AND            (              "Главный" OR              (                "Уволен" IS DISTINCT FROM TRUE AND                "Удален" IS DISTINCT FROM TRUE              )            )          ORDER BY -- сортировка одна на всех            "ЕстьПользователь" DESC, "Входил" DESC        ) T    ) usjs  FROM    "Персона" p  WHERE    "Персона" = ANY($1::uuid[]))-- 8< --SELECT  CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ФамилияЛица"  ELSE    "Фамилия"  END "LastName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ИмяЛица"    ELSE      "Имя"  END "FirstName", CASE    WHEN "ЕстьКорпАккаунт" OR "MainAccount" IS NOT NULL THEN      "ОтчествоЛица"    ELSE      "Отчество"  END "PatronymicName", *FROM  (    SELECT      "Персона"    , coalesce("Фамилия", '') "Фамилия"    , coalesce("Имя", '') "Имя"    , coalesce("Отчество", '') "Отчество"-- 8< --    , fio[1] "ФамилияЛица"    , fio[2] "ИмяЛица"    , fio[3] "ОтчествоЛица"-- 8< --    , "Примечание"    , "Обновлено"    , "Город"    , coalesce("ОтдельныйПрофиль", FALSE) "ОтдельныйПрофиль"    , coalesce("ЕстьКорпАккаунт", FALSE) "ЕстьКорпАккаунт"    , NULL::bigint "Всего"    , "Группа"    , coalesce(usjs[1]::text[], '{}') "Users"    , coalesce(        (usjs[2]::json[])[1]      , (usjs[3]::json[])[1]      , (usjs[4]::json[])[1]      ) "PrimaryFaceAccount"    , ((usjs[5]::json[])[1] ->> 'Account')::bigint "MainAccount"    , ARRAY(        SELECT          "Значение"::int        FROM          "КонтактныеДанные"        WHERE          p."Группа" AND-- 8< --          ("Персона", "Тип") = (p."Персона", 'account')-- 8< --      ) "АккаунтыГруппы"    , "Пол"    , "Логин"    , "ДатаРождения"    , "$Создано"::date "ДатаРегистрации"    FROM      p  ) T;-- 8< --



Итого, запрос мы ускорили больше чем в 2 раза, а упростили на порядок. Будьте ленивее, не пишите много, не копипастите!

#3: Дело о развесистой клюкве



В своем докладе на конференции я говорил, что самая большая проблема, с которой приходится сталкиваться при оптимизации запросов алгоритмическая. То есть когда разработчик не заморачивается пониманием того, как конкретно БД должна/будет выполнять запрос.

Классический пример цепочка JOIN'ов, приводящая к развесистой клюкве из Nested Loop/Hash Join/Merge Join в плане. В особо клинических случаях к ней добавляется схлапывание полученной матрицы с помощью DISTINCT/GROUP BY.
Именно таким оказался запрос из последнего сегодняшнего дела:

Оригинальный запрос, 10.1мс, 11600 buffers
SELECT DISTINCT ON (db."@ПулСерверов")  group_id."@ПулСерверов" "ИдГруппы", group_id."Название" "ИмяГруппы", CASE    WHEN group_id."Название" = 'Управление облаком' THEN      TRUE  ELSE    FALSE  END "ЭтоУправлениеОблаком", group_id."Тип" "Тип", group_id."Заблокирован" "Заблокирован", CASE    WHEN group_id."Тип" = 15 THEN      app."Код"  ELSE    group_id."Код"  END "Код", is_demo."@ПулСерверов" is not null "Демо", group_ext_id."ДопустимоеЧислоПользователей" "ДопустимоеЧислоПользователей", group_ext_id."Состояние" "Состояние", db."@ПулСерверов" "ИдБД", db_name."ИмяБД" "ИмяБД", hosts."Название" "ХостБД", db_name."Порт" "ПортБД", group_id. "Отстойник" "Отстойник", (    WITH params AS(      SELECT        cpv."Значение"      , cpv."Сайт"      FROM        "ОбщиеПараметры" cp      INNER JOIN        "ЗначенияОбщихПараметров" cpv          ON cp."@ОбщиеПараметры" = cpv."ОбщиеПараметры"      WHERE        cp."Название" = 'session_cache_time' AND        (cpv."Сайт" = 9 or cpv."Сайт" is null)    )    SELECT      coalesce(        (SELECT "Значение" FROM params WHERE "Сайт" = 9)      , (SELECT "Значение" FROM params WHERE "Сайт" IS NULL)      , (SELECT "ЗначениеПоУмолчанию" FROM "ОбщиеПараметры" WHERE "Название" = 'session_cache_time')      , 60::text      )::integer  ) "ТаймаутКэша", CASE    WHEN nullif(111, 0) IS NULL THEN      NULL    WHEN 111 = group_id."@ПулСерверов" THEN      TRUE    ELSE      FALSE  END "Эталонная", site."@Сайт" "ИдСайта", site."Адрес" "ИмяСайта"FROM  "ПулСерверов" group_idJOIN  "ПулРасширение" group_ext_id    ON group_id."@ПулСерверов" = group_ext_id."@ПулСерверов" AND NOT (group_id."@ПулСерверов" = ANY('{}'::integer[]))JOIN  "ПулСерверов" folder_db    ON group_id."@ПулСерверов" = folder_db."Раздел"JOIN  "ПулСерверов" db    ON folder_db."@ПулСерверов" = db."Раздел"LEFT JOIN  "Сервер" hosts    ON db."Сервер" = hosts."@Сервер"JOIN  "БазаДанных" db_name    ON db."@ПулСерверов" = db_name."@ПулСерверов"LEFT JOIN  (    WITH list_demo_app AS (      SELECT        ps0."ПулСерверов"      FROM        "ОбщиеПараметры" p0      INNER JOIN        "ОбщиеПараметры" p1          ON p1."Раздел" = p0."@ОбщиеПараметры" AND p0."Название" = 'Управление облаком'      INNER JOIN        "ОбщиеПараметры" p2          ON p2."Раздел" = p1."@ОбщиеПараметры" AND p1."Название" = 'Шайтан' AND p2."Название" = 'ЭтоДемонстрационнаяГруппа'      INNER JOIN        "ОбщиеПараметрыСервис" ps0          ON ps0."ОбщиеПараметры" = p2."@ОбщиеПараметры"    )    , list_demo_srv AS (      SELECT        pool1."@ПулСерверов"      FROM        list_demo_app ls      INNER JOIN        "ПулСерверов" pool0          ON ls."ПулСерверов" = pool0."@ПулСерверов"      INNER JOIN        "ПулСерверов" pool1          ON pool1."Раздел" = pool0."@ПулСерверов" AND pool1."Тип" = 15    )    SELECT      "@ПулСерверов"    FROM      list_demo_srv  ) is_demo    ON is_demo."@ПулСерверов" = group_id."@ПулСерверов"JOIN  "ПулСерверов" app    ON group_id."Раздел" = app."@ПулСерверов"LEFT JOIN  "Приложение" service    ON service."ПулСерверов" = group_id."@ПулСерверов"LEFT JOIN  "СайтПриложение" site_app    ON site_app."Приложение" = service."Раздел"LEFT JOIN  "Сайт" site    ON site."@Сайт" = site_app."Сайт"WHERE  group_id."Тип" = 15 AND  folder_db."Тип" = 8 AND  db."Тип" = 4 AND  db_name."ИмяБД" IS NOT NULL AND  (    (1 = 1 AND is_demo."@ПулСерверов" IS NOT NULL) OR    (1 = 2 AND is_demo."@ПулСерверов" IS NULL) OR    1 NOT IN (1, 2)  );


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

  1. В запросе используется 11 таблиц, провязанных JOIN'ами Это очень смело. Чтобы так делать безболезненно, вы должны точно знать, что после каждого шага связывания количество записей будет ограничено, буквально, единицами. Иначе рискуете получить join 1000 x 1000.
  2. Внимательно смотрим на запрос и строим понятийную модель БД. Разработчику, который это писал проще он ее и так знает, а нам придется восстановить на основе условий соединений, названий полей и бытовой логики. Вообще, если вы графически представляете, как у вас устроена БД, это может сильно помочь с написанием хорошего запроса. У меня получилось вот так:


  3. За счет DISTINCT ON(db."@ПулСерверов") мы ожидаем результат, уникализованный по записи db, в нашей схеме она вон аж в каком низу Но посмотрим на условия запроса в самом низу они из каждой сущности (group_id, folder_db, db) отсекают сверху вниз по значению типа существенные куски.
  4. Теперь самое интересное вложенный запрос, создающий выборку is_demo. Заметим, что его тело не зависит ни от чего то есть его можно смело поднять в самое начало основного WITH-блока. То есть лишнее выделение в подзапрос тут только усложняет все без какого-либо профита.
  5. Заметим, что условия is_demo."@ПулСерверов" = group_id."@ПулСерверов" и is_demo."@ПулСерверов" IS NOT NULL при LEFT JOIN этих таблиц, фактически, означает необходимость присутствия PK group_id среди идентификаторов в is_demo.

    Самое очевидное, что тут можно сделать так и переписать запрос, отбирая записи group_id по набору идентификаторов is_demo.
  6. Переписываем извлечение этих сущностей в независимые CTE, и с удивлением замечаем, что у нас на БД отсутствуют подходящие индексы по ПулСерверов(Тип, Раздел). Причем эти типы константны с точки зрения приложения, поэтому лучше триплет индексов ПулСерверов(Раздел) WHERE Тип = ....
  7. Вспомним, что пересечение нескольких CTE может быть весьма затратным, и заменим его на JOIN через словарь, предварительно сформировав его из записей group_id, folder_db и db ведь это одна исходная таблица ПулСерверов.
  8. Вложенный запрос получения параметра ТаймаутКэша просто переписываем, избавляя от ненужных CTE.

Результат: 0.4мс (в 25 раз лучше), 134 buffers (в 86 раз лучше)
WITH demo_app AS (  SELECT    ps0."ПулСерверов"  FROM    "ОбщиеПараметры" p0  JOIN    "ОбщиеПараметры" p1      ON (p1."Раздел", p1."Название") = (p0."@ОбщиеПараметры", 'Шайтан')  JOIN    "ОбщиеПараметры" p2      ON (p2."Раздел", p2."Название") = (p1."@ОбщиеПараметры", 'ЭтоДемонстрационнаяГруппа')  JOIN    "ОбщиеПараметрыСервис" ps0      ON ps0."ОбщиеПараметры" = p2."@ОбщиеПараметры"  WHERE    p0."Название" = 'Управление облаком'), demo_srv as(  SELECT    pool1."@ПулСерверов"  FROM    demo_app ls  JOIN    "ПулСерверов" pool0      ON ls."ПулСерверов" = pool0."@ПулСерверов"  JOIN    "ПулСерверов" pool1      ON (pool1."Тип", pool1."Раздел") = (15, pool0."@ПулСерверов") -- CREATE INDEX CONCURRENTLY "iПС-tmp0-t15" ON "ПулСерверов"("Раздел") WHERE "Тип" = 15;), grp AS (  SELECT    grp  FROM    "ПулСерверов" grp  WHERE    "Тип" = 15 AND    "@ПулСерверов" = ANY(ARRAY(      SELECT        "@ПулСерверов"      FROM        demo_srv    ))), fld AS (  SELECT    fld  FROM    "ПулСерверов" fld  WHERE    "Раздел" = ANY(ARRAY(      SELECT        (grp)."@ПулСерверов"      FROM        grp    )) AND    "Тип" = 8 -- CREATE INDEX CONCURRENTLY "iПС-tmp0-t8" ON "ПулСерверов"("Раздел") WHERE "Тип" = 8;), dbs AS (  SELECT    dbs  FROM    "ПулСерверов" dbs  WHERE    "Раздел" = ANY(ARRAY(      SELECT        (fld)."@ПулСерверов"      FROM        fld    )) AND    "Тип" = 4 -- CREATE INDEX CONCURRENTLY "iПС-tmp0-t4" ON "ПулСерверов"("Раздел") WHERE "Тип" = 4;), srvhs AS (  SELECT    hstore(      array_agg((dbs)."@ПулСерверов"::text)    , array_agg((dbs)::text)    )  FROM    (      TABLE dbs    UNION ALL      TABLE fld    UNION ALL      TABLE grp    ) T)SELECT  (grp)."@ПулСерверов" "ИдГруппы", (grp)."Название" "ИмяГруппы", (grp)."Название" IS NOT DISTINCT FROM 'Управление облаком' "ЭтоУправлениеОблаком", (grp)."Тип", (grp)."Заблокирован", CASE    WHEN (grp)."Тип" = 15 THEN      app."Код"    ELSE      (grp)."Код"  END "Код", TRUE "Демо", grpe."ДопустимоеЧислоПользователей", grpe."Состояние", (dbn)."@ПулСерверов" "ИдБД", dbn."ИмяБД", dbh."Название" "ХостБД", dbn."Порт" "ПортБД", (grp)."Отстойник", (    SELECT      coalesce(        (          SELECT            "Значение"          FROM            "ЗначенияОбщихПараметров"          WHERE            "ОбщиеПараметры" = cp."@ОбщиеПараметры" AND            coalesce("Сайт", 9) = 9          ORDER BY            "Сайт" NULLS LAST          LIMIT 1        )      , "ЗначениеПоУмолчанию"      , '60'      )::integer    FROM      "ОбщиеПараметры" cp    WHERE      "Название" = 'session_cache_time'  ) "ТаймаутКэша", CASE    WHEN nullif(111, 0) IS NULL THEN      NULL    WHEN (grp)."@ПулСерверов" = 111 THEN      TRUE    ELSE      FALSE  END "Эталонная", site."@Сайт" "ИдСайта", site."Адрес" "ИмяСайта"--, *FROM  dbsJOIN  "БазаДанных" dbn    ON dbn."@ПулСерверов" = (dbs.dbs)."@ПулСерверов"JOIN LATERAL  (    SELECT      ((TABLE srvhs)->((dbs)."Раздел"::text))::"ПулСерверов" fld  ) fld ON TRUEJOIN LATERAL  (    SELECT      ((TABLE srvhs)->((fld)."Раздел"::text))::"ПулСерверов" grp  ) grp ON TRUEJOIN  "ПулРасширение" grpe    ON grpe."@ПулСерверов" = (grp)."@ПулСерверов"JOIN  "ПулСерверов" app    ON app."@ПулСерверов" = (grp)."Раздел"JOIN  "Сервер" dbh    ON dbh."@Сервер" = (dbs)."Сервер"LEFT JOIN  "Приложение" srv    ON srv."ПулСерверов" = (grp)."@ПулСерверов"LEFT JOIN  "СайтПриложение" site_app    ON site_app."Приложение" = srv."Раздел"LEFT JOIN  "Сайт" site    ON site."@Сайт" = site_app."Сайт"WHERE  dbn."ИмяБД" IS NOT NULL;




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

PostgreSQL Antipatterns:


SQL HowTo:

Подробнее..

PostgreSQL в Тензоре публикации за год

26.11.2020 10:08:34 | Автор: admin
Ровно год назад с рассказа о нашем сервисе визуализации планов запросов мы начали публикацию на Хабре серии статей, посвященных работе с PostgreSQL и его особенностям. Это уже пройденные нами грабли, интересные наработки, накопившиеся рекомендации, применяемые в разработке Тензора те вещи, которые помогают нам делать СБИС более эффективным.


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


И все их данные надо где-то хранить и эффективно извлекать. Поэтому еще в далеком 2012 году мы сделали ставку на PostgreSQL, и теперь это основное хранилище данных наших сервисов:

  • почти 9000 баз общим объемом 1PB
  • свыше 200TB данных клиентов
  • 1500 разработчиков работают с БД

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

Для удобства все статьи разбиты на несколько циклов:

  • Анализ запросов
    Наглядно демонстрируем все тайны EXPLAIN [ANALYZE].
  • SQL Antipatterns и оптимизация SQL
    Понимаем как [не] надо решать те или иные задачи в PostgreSQL и почему.
  • SQL HowTo
    Пробуем подходы к реализации сложных алгоритмов на SQL для развлечения и с пользой.
  • DBA
    Присматриваем за базой, чтобы ей легко дышалось.
  • Прикладные решения
    Решаем с помощью PostgreSQL конкретные бизнес-задачи.



Анализ запросов в PostgreSQL


Первое, чему необходимо научиться для разработки эффективных запросов это оценивать их ресурсоемкость. Для этого мы разработали и выложили в публичный доступ удобный сервис визуализации планов explain.tensor.ru.

26.11 О чем молчит EXPLAIN, и как его разговорить (+38, &check;128)
...
Классический вопрос, с которым разработчик приходит к своему DBA или владелец бизнеса к консультанту по PostgreSQL, почти всегда звучит одинаково: Почему запросы выполняются на базе так долго?

Но, как сказано в той же документации, Понимание плана это искусство, и чтобы овладеть им, нужен определённый опыт, Но можно обойтись и без него, если воспользоваться подходящим инструментом!

11.02 Массовая оптимизация запросов PostgreSQL (видео) (+28, &check;131)
...
В докладе представлены некоторые подходы, которые позволяют следить за производительностью SQL-запросов, когда их миллионы в сутки, а контролируемых серверов PostgreSQL сотни.

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

26.03 Рецепты для хворающих SQL-запросов (видео) (+23, &check;143)
...
Многие ситуации, которые делают запрос медленным и прожорливым по ресурсам, типичны и могут быть распознаны по структуре и данным плана.

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

05.06 Понимаем планы PostgreSQL-запросов еще удобнее (+25, &check;88)
...
Новые возможности explain.tensor.ru:

  • Поддержка плана вместе с запросом, в том числе в JSON/YAML-форматах
  • Расширенная визуализация Planning/Execution Time и I/O Timing.
  • Новые фичи из PostgreSQL 13: Planning buffers, Incremental Sort
  • Улучшения UI/UX: скриншоттинг, рекомендации на узлах плана, удаление из архива.

29.07 Вооруженным глазом: наглядно о проблемах PostgreSQL-запроса (+32, &check;69)
...
Сегодня мы научимся определять больные места навскидку в больших и сложных планах, лишь мельком взглянув на них вооруженным глазом. В этом нам помогут различные варианты визуализации: сокращенный текстовый вид, круговая диаграмма, плитка, диаграмма выполнения.

10.08 Правильно [c]читаем параллельные планы PostgreSQL (+17, &check;33)
...
Рассматриваем странности со временем исполнения узлов при активации параллельного выполнения.
В наш век закончившейся гонки мегагерцев и победивших многоядерных и многопроцессорных систем такое поведение является непозволительной роскошью и расточительностью. Поэтому, начиная с версии PostgreSQL 9.6, при отработке запроса часть операций может выполняться несколькими процессами одновременно.

03.09 PostgreSQL Query Profiler: как сопоставить план и запрос (видео) (+13, &check;59)
...
Какие соображения помогают нам превращать сложно читаемый кусок лога сервера в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана.

29.10 Анализируем слона по частям (+19, &check;24)
...
Очередные улучшения юзабилити explain.tensor.ru: гистограммы на узлах, полезная статистика для мега-планов, персональный архив и генеалогия планов.



SQL Antipatterns и оптимизация SQL


09.12 CTE x CTE (+8, &check;35)
...
JOIN нескольких CTE почти всегда зло. Небольшая заметка, как его можно избежать в конкретном примере.
Тут надо вспомнить, что CTE Scan является аналогом Seq Scan то есть никакой индексации, а только полный перебор.

В данном случае нам еще сильно повезло, что для соединения был выбран Hash Join, а не Nested Loop, поскольку тогда мы получили бы не один-единственный проход CTE Scan, а 10K!

10.12 вредные JOIN и OR (+20, &check;108)
...
Разбираем на примере конкретного запроса несколько методик оптимизации и учимся использовать ленивые вычисления в PostgreSQL:

  • оптимизируем JOIN + LIMIT 1
  • BitmapOr vs UNION
  • прячем под CASE сложные условия

11.12 статистика всему голова (+10, &check;54)
...
В столбце ratio как раз показывается отношение в разах между планировавшимся на основании статистики и фактически прочитанным количеством записей. Чем больше это значение тем хуже статистика отражает реальное положение дел в вашей таблице.

Приводит это к выбору неэффективного плана и, как следствие, дичайшей нагрузке на базу. Чтобы ее оперативно убрать, достаточно все-таки прислушаться к рекомендациям мануала и пройти ANALYZE по основным таблицам.

12.12 сизифов JOIN массивов (+14, &check;37)
...
Иногда возникает задача склеить внутри SQL-запроса из переданных в качестве параметров линейных массивов целостную выборку с теми же данными по столбцам.
Вспоминаем о расширенных возможностях работы с массивами:

  • WITH ORDINALITY
  • Multi-argument UNNEST

19.12 передача наборов и выборок в SQL (+8, &check;95)
...
Периодически у разработчика возникает необходимость передать в запрос набор параметров или даже целую выборку на вход. Иногда попадаются очень странные решения этой задачи.
Сравниваем разные варианты передачи данных в запрос:

  • сериализованное представление массива/матрицы + unnest
  • JSON + json_populate_recordset/json_to_recordset
  • TEMPORARY TABLE
  • переменные сессии

24.12 обновляем большую таблицу под нагрузкой (+14, &check;126)
...
Как стоит поступить (а как точно не надо), если в многомиллионной активно используемой таблице PostgreSQL нужно обновить большое количество записей проинициализировать значение нового поля или скорректировать ошибки в существующих записях? А при этом сохранить свое время и не потерять деньги компании из-за простоя.
Почему один UPDATE и ORDER BY + LIMIT это печально для подобной задачи, а сегментное обновление и предварительно рассчитанные вычисления в самый раз.

20.01 редкая запись долетит до середины JOIN (+18, &check;119)
...
Если писать SQL-запросы без анализа алгоритма, который они должны реализовать, ни к чему хорошему с точки зрения производительности это обычно не приводит.

Такие запросы любят кушать процессорное время и активно почитывать данные практически на ровном месте. Причем, это вовсе не обязательно какие-то сложные запросы, наоборот чем проще он написан, тем больше шансов получить проблемы. А уж если в дело вступает оператор JOIN
Разбираем на моделях способы оптимизации JOIN + GROUP BY и JOIN + LIMIT с помощью CASE и LATERAL.

27.01 ударим словарем по тяжелому JOIN (+8, &check;107)
...
Итоговые выводы:

  • если надо сделать JOIN с многократно повторяющимися записями лучше использовать ословаривание таблицы
  • если ваш словарь ожидаемо маленький и читать вы из него будете немного можно использовать json[b]
  • во всех остальных случаях hstore + array_agg(i::text) будет эффективнее

02.03 меняем данные в обход триггера (+24, &check;61)
...
Например, на таблице, в которой вам надо что-то поправить, висит злобный триггер ON UPDATE, переносящий все изменения в какие-нибудь агрегаты. А вам надо все пообновлять (новое поле проинициализировать, например) так аккуратно, чтобы эти агрегаты не затронулись.
Почему быстро отключить и снова включить триггер плохая идея. Как его обойти с помощью переменных сессии.

10.03 сказ об итеративной доработке поиска по названию (+17, &check;82)
...
Что вообще обычно подразумевает пользователь, когда говорит про быстрый поиск по названию? Почти никогда это не оказывается честный поиск по подстроке типа ... LIKE '%роза%' ведь тогда в результат попадают не только 'Розалия' и 'Магазин Роза', но и роза' и даже 'Дом Деда Мороза'.

Пользователь же подразумевает на бытовом уровне, что вы ему обеспечите поиск по началу слова в названии и покажете более релевантным то, что начинается на введенное. И сделаете это практически мгновенно при подстрочном вводе.
Как ищут строки: pg_trgm, FTS, text_pattern_ops, btree + UNION ALL. И как можно неаккуратно все разломать: пейджинг, подзапросы, DISTINCT.

12.03 сражаемся с ордами мертвецов (+32, &check;106)
...
Как оградить свои UPDATE'ы от лишней работы с диском и блокировок с помощью объединения операций и IS DISTINCT FROM.

31.03 вычисление условий в SQL (+26, &check;65)
...
SQL это не C++, и не JavaScript. Поэтому вычисление логических выражений происходит иначе.
  • ускоряем триггер за счет выноса проверки из функции в WHEN.
  • оптимизируем OR/AND-цепочку с помощью CASE
  • упрощаем написание сложных условий

27.04 навигация по реестру (+22, &check;74)
...
Все будет очень просто, на уровне Капитана Очевидность делаем просмотр реестра событий с сортировкой по времени.
  • плохо: считать сегменты на бизнес-логике
  • плохо: использовать LIMIT + OFFSET
  • хорошо: использовать курсоры, но делать это аккуратно

14.05 насколько глубока кроличья нора? пробежимся по иерархии (+19, &check;83)
...
В сложных ERP-системах многие сущности имеют иерархическую природу, когда однородные объекты выстраиваются в дерево отношений предок потомок это и организационная структура предприятия (все эти филиалы, отделы и рабочие группы), и каталог товаров, и участки работ, и география точек продаж, ...
Пишем сложный запрос, чтобы извлекать минимум данных при проходах по дереву.

24.06 подозрительные типы (+40, &check;60)
...
Типизация данных в PostgreSQL, при всей своей логичности, действительно преподносит порой очень странные сюрпризы. В этой статье мы постараемся прояснить некоторые их причуды, разобраться в причине их странного поведения и понять, как не столкнуться с проблемами в повседневной практике.

28.06 накручиваем себе проблемы (+21, &check;56)
...
Рассматриваем причины накрутки serial при ON CONFLICT и счетчика транзакций при ROLLBACK.

08.07 SELF JOIN vs WINDOW (+14, &check;32)
...
Ускоряем запрос в 100 раз с помощью оконных функций на примере мониторинга блокировок.

14.07 Unreal Features of Real Types, или Будьте осторожны с REAL (+9, &check;10)
...
Я решил бегло пробежаться по коду доступных мне SQL-запросов, чтобы посмотреть, насколько часто в них используется тип REAL. Достаточно часто используется, как оказалось, и не всегда разработчики понимают опасности, стоящие за ним. И это несмотря на то, что в Интернете и на Хабре достаточно много хороших статей про особенности хранения вещественных чисел в машинной памяти и о работе с ними. Поэтому в этой статье я постараюсь применить такие особенности к PostgreSQL, и попробую на пальцах рассмотреть связанные с ними неприятности, чтобы разработчикам SQL-запросов было легче избежать их.

04.08 Должен остаться только один! (+24, &check;80)
...
Сегодня на предельно простых примерах посмотрим, к чему это может приводить в контексте использования GROUP/DISTINCT и LIMIT вместе с ними.

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

20.08 уникальные идентификаторы (+21, &check;67)
...
Рассматриваем эффективность и проблемы различных способов получить уникальные идентификаторы в базе и их проблемы:

  • таблица счетчиков
  • объект SEQUENCE
  • псевдотип serial
  • GENERATED-столбцы
  • генерируемый UUID
  • скрытые системные поля: tableoid/ctid/oid
  • честное время clock_timestamp

01.10 Бесконечность не предел!, или Немного о рекурсии (+18, &check;47)
...
Рекурсия очень мощный и удобный механизм, если над связанными данными делаются одни и те же действия вглубь. Но неконтролируемая рекурсия зло, которое может приводить или к бесконечному выполнению процесса, или (что случается чаще) к выжиранию всей доступной памяти.

СУБД в этом отношении работают по тем же принципам "сказали копать, я и копаю". Ваш запрос может не только затормозить соседние процессы, постоянно занимая ресурсы процессора, но и уронить всю базу целиком, съев всю доступную память. Поэтому защита от бесконечной рекурсии обязанность самого разработчика.

07.10 убираем медленные и ненужные сортировки (+27, &check;91)
...
Просто так результат SQL-запроса возвращает записи в том порядке, который наиболее удобен серверу СУБД. Но человек гораздо лучше воспринимает хоть как-то упорядоченные данные это помогает быстро сравнивать соответствие различных датасетов.

Поэтому со временем у разработчика может выработаться рефлекс Дай-ка я на всякий случай это вот отсортирую!
Учимся опознавать типовые кейсы и делаем запрос чуть быстрее:

  • нехватка work_mem
  • сортировка уже отсортированного
  • вложенная отладочная сортировка
  • Index Scan вместо сортировки
  • UNION ALL вместо сортировки
  • сортировки для оконных функций


10.11 работаем с отрезками в кровавом энтерпрайзе (+27, &check;64)
...
Давайте посмотрим, какие именно прикладные задачи и как можно решить с помощью PostgreSQL и сократить время анализа данных с нескольких секунд на бизнес-логике до десятков миллисекунд, умея эффективно применять следующие алгоритмы непосредственно внутри SQL-запроса:

  • поиск отрезков, пересекающих точку/интервал
  • слияние отрезков по максимальному перекрытию
  • подсчет количества отрезков в каждой точке

18.11 DBA-детектив, или Три дела о потерянной производительности (видео) (+16, &check;45)
...
Дедукция и индукция помогут нам вычислить, что же все-таки хотел получить от СУБД разработчик, и почему это получилось не слишком оптимально. Итак, сегодня нас ждут:

  • Дело о непростом пути вверх
    Разберем в live-видео на реальном примере некоторые из способов улучшения производительности иерархического запроса.
  • Дело о худеющем запросе
    Увидим, как можно запрос упростить и ускорить в несколько раз, пошагово применяя стандартные методики.
  • Дело о развесистой клюкве
    Восстановим структуру БД на основании единственного запроса с 11 JOIN и предложим альтернативный вариант решения на ней той же задачи.



SQL HowTo


30.12 рисуем морозные узоры на SQL (+24, &check;52)
...
Немного SQL-магии: математика, рекурсия, псевдографика.

13.01 собираем цепочки с помощью window functions (+11, &check;40)
...
Иногда при анализе данных возникает задача выделения цепочек в выборке то есть упорядоченных последовательностей записей, для каждой из которых выполняется некоторое условие.

Традиционные решения предусматривают разные варианты self join, когда выборка соединяется с собой же, либо использование некоторых фактов за пределами данных например, что записи должны иметь строго определенный шаг (N+1, за каждый день, ...).

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

Но эту задачу нам помогут эффективно решить оконные функции в PostgreSQL.

31.01 пишем while-цикл прямо в запросе, или Элементарная трехходовка (+8, &check;97)
...
Периодически возникает задача поиска связанных данных по набору ключей, пока не наберем нужное суммарное количество записей.

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

19.06 1000 и один способ агрегации (+12, &check;74)
...
Рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в PostgreSQL или ускорить выполнение SQL-запроса.

  • совместные агрегаты
  • вложенные запросы
  • FILTER-агрегаты
  • агрегаты от условия
  • агрегация в массив
  • DISTINCT + OVER
  • сложный агрегат с помощью рекурсии

28.07 красивые отчеты по дырявым данным GROUPING SETS (+8, &check;28)
...
В этой статье рассмотрим, как все это можно экономично расположить в БД, и как максимально эффективно собрать по этим данным отчет с помощью оконных функций и GROUPING SETS.

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

05.09 курсорный пейджинг с неподходящей сортировкой (+18, &check;64)
...
Пусть у нас есть реестр документов, с которым работают операторы или бухгалтеры. Традиционно, при подобном отображении используется или прямая (новые снизу) или обратная (новые сверху) сортировка по дате и порядковому идентификатору, назначаемому при создании документа ORDER BY dt, id или ORDER BY dt DESC, id DESC.

Но что если пользователю зачем-то захотелось нетипичного например, отсортировать одно поле так, а другое этак ORDER BY dt, id DESC? Но второй индекс мы создавать не хотим ведь это замедление вставки и лишний объем в базе.

Можно ли решить эту задачу, эффективно используя только индекс (dt, id)?

23.09 PostgreSQL 13: happy pagination WITH TIES (+40, &check;45)
...
Используем новые возможности PostgreSQL 13 для упрощения организации постраничной навигации по реестру.

19.10 ломаем мозг об дерево упорядочиваем иерархию с рекурсией и без (+16, &check;62)
...
чтобы для вывода упорядочить элементы дерева в соответствии с иерархией, уж точно придется воспользоваться рекурсией! Или нет? Давайте разберемся, а заодно решим на SQL пару комбинаторных задач.



DBA


20.12 вычищаем клон-записи из таблицы без PK (+13, &check;45)
...
Случаются ситуации, когда в таблицу без первичного ключа или какого-то другого уникального индекса по недосмотру попадают полные клоны уже существующих записей.

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

Как избавить базу от ненужных клонов?

25.12 когда пасует VACUUM чистим таблицу вручную (+21, &check;98)
...
VACUUM может зачистить из таблицы в PostgreSQL только то, что никто не может увидеть то есть нет ни одного активного запроса, стартовавшего раньше, чем эти записи были изменены.

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

15.01 перенос значений SEQUENCE между базами PostgreSQL (+11, &check;43)
...
Как можно перенести в другую PostgreSQL-базу последнее назначавшееся значение автоинкремент-поля типа serial, если в таблице могли быть какие-то удаления, и просто подставить max(pk) уже не подходит?

19.02 находим бесполезные индексы (+19, &check;114)
...
Регулярно сталкиваюсь с ситуацией, когда многие разработчики искренне полагают, что индекс в PostgreSQL это такой швейцарский нож, который универсально помогает с любой проблемой производительности запроса. Достаточно добавить какой-нибудь новый индекс на таблицу или включить поле куда-нибудь в уже существующий, а дальше (магия-магия!) все запросы будут эффективно таким индексом пользоваться.

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

19.03 грамотно организовываем синхронизации и импорты (+11, &check;48)
...
При сложной обработке больших наборов данных (разные ETL-процессы: импорты, конвертации и синхронизации с внешним источником) часто возникает необходимость временно запомнить, и сразу быстро обработать что-то объемное.

Чтобы справиться с ними в PostgreSQL (да и не только в нем), можно использовать некоторые возможности для оптимизаций, которые позволят обработать все быстрее и с меньшим расходом ресурсов.

20.05 мониторинг базы PostgreSQL кто виноват, и что делать (+23, &check;100)
...
На что обращать внимание при мониторинге PostgreSQL-базы и как трактовать полученные данные:

  • состояние соединений
  • блокировки
  • transactions per second (TPS)
  • количество операций над записями
  • использование кэша данных
  • самый длительный запрос/транзакция

27.05 в погоне за пролетающими блокировками (+18, &check;41)
...
Шансов поймать блокировки в моменте крайне мало, да и длиться они могут всего по несколько секунд, но ухудшая при этом плановое время выполнения запроса в десятки раз. А хочется-то не сидеть и ловить происходящее в онлайн-режиме, а в спокойной обстановке разобраться постфактум, кого из разработчиков покарать в чем именно была проблема кто, с кем и из-за какого ресурса базы вступил в конфликт.

Но как? Ведь, в отличие от запроса с его планом, который позволяет детально понять, на что пошли ресурсы, и сколько времени это заняло, подобных наглядных следов блокировка не оставляет после себя

Разве что короткую запись в логе:
process ... still waiting for ...
А давайте попробуем зацепиться именно за нее!

10.06 классифицируем ошибки из PostgreSQL-логов (+9, &check;38)
...
Если мы не хотим потом хвататься за голову, то возникающие в логах PostgreSQL ошибки недостаточно просто считать поштучно их надо аккуратно классифицировать. Но для этого нам придется решить нетривиальную задачу индексированного поиска регулярного выражения, наиболее подходящего для строки.

15.06 кто скрывается за блокировкой (+11, &check;48)
...
Научимся трактовать собранные блокировки и узнавать, кто именно может скрываться за конкретной матрицей конфликтов, и почему результат выглядит именно так.



Решения для PostgreSQL


09.01 БД мессенджера (ч.1): проектируем каркас базы (+3, &check;62)
...
Как можно перевести бизнес-требования в конкретные структуры данных на примере проектирования с нуля базы для мессенджера.

09.01 БД мессенджера (ч.2): секционируем наживую (+5, &check;67)
...
Мы удачно спроектировали структуру нашей PostgreSQL-базы для хранения переписки, прошел год, пользователи активно ее наполняют, вот в ней уже миллионы записей, и что-то все начало подтормаживать.

Дело в том, что с ростом объема таблицы растет и глубина индексов хоть и логарифмически. Но со временем это заставляет сервер для выполнения тех же задач чтения/записи обрабатывать в разы больше страниц данных, чем в начале.

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

22.01 PubSub почти бесплатно: особенности NOTIFY в PostgreSQL (+20, &check;76)
...
Если ваши микросервисы уже используют общую базу PostgreSQL для хранения данных, или ей пользуются несколько экземпляров одного сервиса на разных серверах, можно относительно дешево получить возможность обмена сообщениями (PubSub) между ними без интеграции в архитектуру Redis, RabbitMQ-кластера или встройки в код приложения другой MQ-системы.

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

Передавать и получать данные мы станем с помощью механизма NOTIFY/LISTEN, а модельную реализацию соберем для Node.js.

13.02 Фантастические advisory locks, и где они обитают (+11, &check;34)
...
В PostgreSQL существует очень удобный механизм рекомендательных блокировок, они же advisory locks. Мы в Тензоре используем их во многих местах системы, но мало кто детально понимает, как конкретно они работают, и какие проблемы можно получить при неправильном обращении.

13.04 Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB (+19, &check;78)
...
Рассматриваем традиционные подходы масштабирования производительности на конкретном примере:

  • Секционирование
  • Эволюция и рефакторинг БД
  • Размазываем пиковую нагрузку
  • Кэшируем, что можно
Терабайт-в-сутки только звучит страшно. Если вы все делаете правильно, то это всего лишь 2^40 байт / 86400 секунд = ~12.5MB/s, что держали даже настольные IDE-винты. :)

А если серьезно, то даже при десятикратном перекосе нагрузки в течение суток, вы спокойно можете уложиться в возможности современных SSD.

22.04 Экономим копеечку на больших объемах в PostgreSQL (+11, &check;44)
...
Продолжая тему записи больших потоков данных, поднятую предыдущей статьей про секционирование, в этой рассмотрим способы, которыми можно уменьшить физический размер хранимого в PostgreSQL, и их влияние на производительность сервера.

Речь пойдет про настройки TOAST и выравнивание данных. В среднем эти способы позволят сэкономить не слишком много ресурсов, зато вообще без модификации кода приложения.

03.06 Как мы в СБИС автоматический расчет себестоимости делали (видео) (+7, &check;17)
...
Как суровую прагматику требований бизнеса перенести на разработку высоконагруженных сервисов, как бороться с конкурентным доступом к данным, как это все аккуратно обходить и при этом не отстрелить себе ногу.

17.08 У меня зазвонил телефон. Кто говорит?.. Поможет слон (+10, &check;29)
...
Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой HelpDesk или CRM-системы. Только надо уметь делать это быстро тогда появляется масса возможностей.

25.08 Телепортация тонн данных в PostgreSQL (+11, &check;60)
...
Выжимаем максимум пропускной способности из PostgreSQL:
  • Как балансировать писателей и управлять соединениями на бизнес-логике?
  • Как настроить СУБД и ОС?
  • Как избавиться от блокировок?



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

SQL HowTo рейтинг-за-интервал

11.01.2021 16:10:01 | Автор: admin

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

Но просто "самые" за весь доисторический период обычно неинтересны - продал ты 3 года назад вагон валенок, и теперь он у тебя в "самых" продажах вечно. Поэтому обычно хочется видеть"топ" на каком-то ограниченном последнем интервале- например, "за последний год" (точнее, за последние 12 календарных месяцев).

Традиционно, есть два подхода к этой задаче: запрос по требованию по "сырым" данным или предварительная агрегация. И если "просто посчитать" такой отчет по первичке - упражнение для SQL-новичка, но очень "тяжелое" для производительности СУБД, то вариант сделать так, чтобы он строился практически мгновенно при большом количестве активных аккаунтов независимых бизнесов, как у нас в СБИС, без необходимости пересчитывать агрегированную статистику каждого 1-го числа месяца судорожно по всем клиентам - интересная задача.

Структура хранения

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

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

CREATE TABLE item_stat(  item -- товар    integer, sum    numeric(32,2));CREATE INDEX ON item_stat(sum DESC);

Наполнять ее данными мы можем легко и просто -инкрементом в триггерепри проведении продажи. Но как все-таки сделать эффективное "вычитание" данных при завершении месяца?..

"Нужно больше золота"

Чтобы быстро что-то вычесть, нужно четко понимать, что именно.

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

CREATE TABLE item_stat(  interval_id -- 0 - текущие счетчики, 202001 - январь 2020, 202002 - февраль, ...    integer, item    integer, sum    numeric(32,2), UNIQUE(interval_id, item));CREATE INDEX ON item_stat(interval_id, sum DESC);

Момент обновления

Чтобы понять, что вот прямо сейчас надо "вычесть" какой-то месяц, достаточно оперироватьединственным дополнительным параметромтипа"месяц последней актуализации рейтинга продаж". Хранить его можно даже в служебной записи в этой же таблице (если это не помешает Foreign Key, который вы можете захотеть добавить на item):

INSERT INTO item_stat(  interval_id, item, sum)VALUES  (0, 0, 202012) -- служебный ключ (0, 0), значение - 2020'12 вместо суммыON CONFLICT(interval_id, item)  DO UPDATE SET    sum = EXCLUDED.sum; -- всегда заменяем значение

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

INSERT INTO item_stat(  interval_id, item, sum)VALUES  (202001, 1, 100) -- + в рейтинг за январь 2020, (     0, 1, 100) -- + в текущий рейтингON CONFLICT(interval_id, item)  DO UPDATE SET    sum = item_stat.sum + EXCLUDED.sum; -- всегда добавляем в сумму

Если текущиймесяц операции разошелся с месяцем из параметра,асинхронностартуем пересчет "годовых" значений, вычитая показатели за ставшие избыточными месяцы, и переактуализируем значение параметра:

-- "новый" месяц актуальностиWITH next AS (  SELECT 202101)-- предыдущий месяц актуальности, prev AS (  SELECT    sum::integer  FROM    item_stat  WHERE    (interval_id, item) = (0, 0))-- все продажи за период, ставший неактуальным, в разрезе товаров, diff AS (  SELECT    item  , sum(sum) sum  FROM    item_stat  WHERE    interval_id BETWEEN (TABLE prev) - 100 AND (TABLE next) - 100  GROUP BY    1)UPDATE  item_stat dstSET  sum = dst.sum - diff.sumFROM  diffWHERE  (dst.interval_id, dst.item) = (0, diff.item);UPDATE  item_statSET  sum = 202101WHERE  (interval_id, item) = (0, 0);

При построении отчета

Если текущий месяц совпадает с месяцем из параметра, то все значения в "годичном" интервале актуальны - просто выводим топ по индексу:

SELECT  *FROM  item_statWHERE  interval_id = 0 -- текущий "годичный" интервалORDER BY  sum DESCLIMIT 10;

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

Подробнее..

Агрегаты в БД многомерные суперагрегаты

03.02.2021 10:04:43 | Автор: admin

В прошлой статье мини-цикла о работе с агрегатами я рассказывал, как организоватьэффективное многопоточное преобразование потока первичных данныхв данные агрегированные. Там мы рассматривали задачу "свертки" продаж в агрегаты видатовар/дата/кол-во.

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

Бизнес-требования

И швец, и жнец, и на дуде игрец...И швец, и жнец, и на дуде игрец...
  • уметь быстро получить информацию не толькопо товарам, но ипо складам

  • в том числе исводка-TOPпродаж товаров на интервале

  • в том числес фильтром по складу... или без

  • а ещеграфик динамики продажза месяц по дням... и за год по месяцам... и за все время по годам

  • ... ис любым из фильтровсклад/товар

  • ... и чтобывсе быстроработало!

Итак, вычленяем ключевое для нас относительно предыдущей задачи:

  • появляетсяновый разрез агрегации- по складу

  • необходимаагрегация "без учета" или "по всем"- например, сводные продажи всех товаров склада или продажи товара по всем складам

  • нужныагрегаты для динамики(дневные/месячные/годовые) в разрезе любого фильтра

Структура новых агрегатов

Добавим в таблицу агрегатов новое поле -тип интервалаагрегации (D/M/Y) и новый аналитический разрез -склад.

CREATE TABLE agg(  it    -- товар    integer, wh    -- склад    integer, dt    -- дата продажи/начала кванта    date, quant -- тип кванта D/M/Y    "char", qty   -- количество    double precision);

Замечу, что тут для экономии размера данных мы использовали однобайтныйспецтип "char". Например, такой тип имеет полеrelkind(тип объекта) в системной таблицеpg_class.

Неудобный NULL и удобный ноль

Для аналитики "по всем" используемзначение = 0(не NULL) соответствующего разреза. Это позволит нам всегда передавать значения в один и тот же запрос, без изменения его модели на IS [NOT] NULL.

То есть для запроса "какие товары продавались лучше всего в таком-то месяце" будем использовать запрос вида:

SELECT  it, qtyFROM  aggWHERE  wh = $1::integer AND -- передадим 0 для разреза "по всем складам"  (quant, dt) = ($2::"char", $3::date) -- передадим 'M' для обращения к "месячному" агрегатуORDER BY  qty DESCLIMIT ...;

А если нам понадобится этот же рейтинг по конкретному складу, то простопередадим сюда же ID склада! Очевидно, для такого запроса подходящим будет индекс(quant, dt, wh, qty DESC).

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

Динамика в разрезе фильтра

Давайте теперь сконструируем запрос, который поможет нам нарисовать красивый график по динамике:

SELECT  dt, qtyFROM  aggWHERE  (quant, it, wh) = ($1::"char", $2::integer, $3::integer) AND  dt BETWEEN $4::date AND $5::date -- период графикаORDER BY  dt;

Индекс под него -(quant, it, wh, dt). Почему на первом месте именно quant? Потому что у него очень маленькая селективность, и индекс будет занимать меньше места.

Сборка агрегатов

Итак, вернемся к предыдущей статье и проблемам, которые мы успешно решали там - разделению кросс-блокировок между параллельно работающими потоками. В нынешней ситуации, разделив обработкупо ключу (it, wh), мы заведомо устраним конфликты между обработчиками.

Но возникнет небольшая проблема - давайте посмотрим, как именно эффективнее всего добиться формирования агрегатов:

При проходе по курсору над flow-таблицей мы формируемв памяти "дифф" для инкрементазаписей соответствующих агрегатов по обрабатываемому этим потоком ключу(it, wh)- сразу для каждого из типов интервалов.

Вместе с этим мы вставляем во flow новую "первичку" для последующих "надагрегатов",заменяя нулем каждый из вариантов разрезованализа. Понятно, что при последующей обработке такого ключа, содержащего хотя бы один ноль, записи "надагрегатов" формировать уже не нужно.

Обходим блокировки

Единственная точка, в которой у нас могут пересечься два параллельно работающих потока, это формирование/обновление записи queue, соответствующей этому ключу "надагрегата" - например,(0, 0).

К счастью, это достаточно просто обходитсявставкой новой записи в queue, еслиpg_try_advisory_xact_lock(it, wh)для такого ключа вернула намFALSE. То есть да, в разрезе ключа распределения записи в очереди могут быть неуникальны. Но в этом нет ничего страшного, потому что они всего лишь выполняют функциюсигнализатора "во flow что-то может быть по этому ключу". И если нет - не страшно, при обработке этой записи очереди мы заглянем во flow, ничего не найдем, и спокойно завершим обработку.


Итого - мы получили в БД все нужные агрегаты во всех требуемых разрезах, которые помогут нам обеспечить быстрый показ отчета/графика в любой комбинации фильтров:

Подробнее..

Агрегаты в БД прокси-таблицы

08.02.2021 20:16:10 | Автор: admin

Мы заканчиваем мини-серию статей о работе с агрегатами в PostgreSQL:

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

Поговорим об этом на примере коллектора нашего сервиса анализа PostgreSQL-логов, о котором я уже рассказывал в предыдущих статьях:

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

Если первая оптимизация достаточно очевидна (посылать в базу не "10 раз +1", а "1 раз +10"), то о второй стоит рассказать подробнее.

"Все, что нажито непосильным трудом!.."

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

Давайте посмотрим, из чего конкретно состоит время вставки нового экземпляра записи агрегата.

Я уже рассказывал, что для вставки в агрегаты мы используем тот же самыйCOPY, что и в таблицы "фактов" + триггер, который преобразует вставку вINSERT ON CONFLICT ... DO UPDATE. Агрегатов у нас существенно меньше, чем летящих в них фактов - то есть, практически каждая вставка приводит кUPDATE! А что это у нас "технически"?..

  • накладываемRowExclusiveLock

  • находим по индексу и вычитываемтекущий образ записи

  • прописываем служебное поле xmaxв нем

  • вставляем новый образ записис измененными данными вWAL-файли heap таблицы

  • вписываем изменения вовсе относящиеся индексы- если повезло, будет HOT update с чуть меньшей нагрузкой

Как-то очень много всяких "читаем" и "пишем" получается. А в активный агрегат через секунду прилетит следующий UPDATE, и следующий...

Создаем прокси-таблицу

А вот было бы хорошо, если бы можно было вставлять сразу в такую табличку - чтобыни индексов, ни WAL-файлов, ни UPDATE'ов - только "чистые"INSERT... А ведь так можно сделать!

Давайте породим промежуточную таблицу по формату целевой:

CREATE UNLOGGED TABLE px$agg(  LIKE agg);

Зачем нужен UNLOGGED и что он дает, можно подробно прочитать в статье "DBA: грамотно организовываем синхронизации и импорты".

Теперь все что мы хотим записать - будем писать прямо сюда без всяких триггеров и индексов.

Обрабатываем прокси-таблицу

Но в целевую-то таблицу данные у нас теперь никак не попадают - значит, надо кому-то это поручить. Причем, чтобы нам не пришлось решать никаких конфликтов параллельности, поручим этокому-то одному, вообще постороннемуотносительно вставки данных.

В нашей архитектуре для этого как раз удачно оказался диспетчер-балансировщик, описанный в статье "Динамическая балансировка нагрузки в pull-схеме".

Мы можемкаждые 10 секунд вычитывать все содержимоев прокси-таблице (да, это будет Seq Scan, но это самый быстрый способ доступа ко "всем" данным таблицы), динамически агрегировать в памяти процесса PostgreSQL без передачи на клиента, исразу вставлять в целевую таблицуза один запрос.

Если не знаете, как совместить вычисление разных агрегатов в одном запросе, стоит прочитать "SQL HowTo: 1000 и один способ агрегации".

После того, как мы все вычитали, сагрегировали и вставили - просто и быстро зачистим прокси с помощьюTRUNCATE:

BEGIN;  INSERT INTO agg  SELECT    pk1    ...  , pkN  , <aggfunc>(val1) -- sum/min/max/...  , <aggfunc>(val2)    ...  FROM    px$agg -- тот самый Seq Scan  GROUP BY -- агрегация в памяти в разрезе PK таблицы агрегатов = (pk1, ..., pkN)    pk1    ...  , pkN;  TRUNCATE px$agg;COMMIT;

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

Защита от параллельной вставки

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

Можно, конечно, повесить перед началомLOCKна всю таблицу, чтобы уж никто точно ничего не смог ничего с ней сделать, но это не наш вариант - ведь ждать будет слишком много вставок.

Зато... мы можем практически мгновенноподменить ее на пустую такую же!

BEGIN;  SET LOCAL lock_timeout = '100ms'; -- ждем блокировку не дольше 100мс  LOCK TABLE px$agg IN ACCESS EXCLUSIVE MODE; -- собственно, блокируем от всех  ALTER TABLE px$agg RENAME TO px$agg_swap; -- обменяли имена двух табличек  ALTER TABLE px$agg_ RENAME TO px$agg;  ALTER TABLE px$agg_swap RENAME TO px$agg_;COMMIT;

Как только мы дождемся блокировку, так сразу и переименование пройдет. Если не дождемся - то попробуем в следующий раз, не страшно.

В принципе, можно обойтись и двумя RENAME, но прокси-таблиц тогда должно быть больше:

  1. px -> px0, px1 -> px

  2. px -> px1, px0 -> px

Примерно так же живет внутренняя "механика" брокера очередей PgQ и базирующейся на ней репликации Londiste.

Пруфы

Теперь самое интересное - сколько это все дало:

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

Подробнее..

DBA Кто-то слишком много ест!

12.02.2021 14:21:36 | Автор: admin

Тема "распухания" таблиц и индексов из-за реализации MVCC - больная для пользователей и администраторов PostgreSQL.

Однажды я уже поднимал ее в статье "DBA: когда пасует VACUUM чистим таблицу вручную", разобрав на конкретных примерах, насколько драматический эффект для производительности запросов может оказывать невовремя проведенный или бесполезно отработавший из-за конкурентных транзакций VACUUM.

Но, помимо влияния на скорость, есть еще и факт влияния на занятое место. Наверное, вы сильно удивитесь, если таблица с единственной "живой" записью после успешного прохода autovacuum продолжит занимать гигабайты пространства на дорогих SSD.

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

Как быстро наступить на грабли

Для определенности скажем, что все тесты мы проводим на PostgreSQL 12, потому что с каждой новой версией разработчики стараются снизить сайд-эффекты MVCC.

CREATE TABLE bloat AS SELECT 0 i;CREATE INDEX ON bloat(i);-- вспомогательные функции удобно "прятать" в pg_temp, чтобы не зачищать потом вручнуюCREATE OR REPLACE PROCEDURE pg_temp.upd() AS $$  UPDATE bloat SET i = i + 1;$$ LANGUAGE sql; -- и да, это plainSQL-процедура, а не функцияDO $$DECLARE  i integer;  ts timestamp;BEGIN  FOR i IN 1 .. 1 << 14 LOOP    ts := clock_timestamp();    CALL pg_temp.upd();    RAISE NOTICE '% : %', i, clock_timestamp() - ts;  END LOOP;END;$$ LANGUAGE plpgsql;

В процессе выполнения этого скрипта можно прямо глазами видеть, насколько сильно деградирует среднее время выполнения UPDATE нашей единственной записи. Точно единственной?

TABLE bloat;
  i-----16384

И сколько же все это счастье из единственной записи занимает?

SELECT pg_relation_size('bloat');-- 598016

Оу... больше полумегабайта! Замечу, что при отсутствии индекса фокус не пройдет, и таблица не разрастется благодаря HOT-update. Но и тут нам же должен помочь VACUUM - ведь никаких мешающих транзакций у нас нету!

VACUUM bloat;-- ... и нет!SELECT pg_relation_size('bloat');-- 598016

Хранение данных в таблице

Чтобы понять, почему так произошло, впору вспомнить про организацию физического хранения данных в PostgreSQL, которая в предельно упрощенном виде выглядит так:

  • каждая таблица - отдельный файл (pg_class.relfilenode)

  • каждый файл делится на сегменты, не превышающие 1GB

  • каждый сегмент состоит из последовательности страниц данных по (обычно) 8KB

  • страница данных содержит непосредственно набор записей

"Обычный" VACUUM (неVACUUM FULL) никак не перемещает сами записи - ни между страницами, ни в рамках самой страницы, поэтому может уменьшить размер файла, только отрезав "хвост" из целиком пустых страниц.

А наша единственная "живая" запись лежит ровно на самой последней странице, потому что все предыдущие были заняты "мертвыми" - потому VACUUM ничего и не срезал.

В недрах pg_catalog

pg_catalog - это системная схема, описывающая все внутреннее мироустройство данной конкретной базы - схемы, таблицы, индексы, поля, статистика, ... Чтобы понять, насколько там всего много, достаточно взглянуть на схему от Joel Jacobson. Но нам сегодня понадобятся всего несколько таблиц, описывающих отдельные аспекты базы:

  • pg_namespace - схемы (schema)

  • pg_class - основные объекты - таблицы, индексы, matview, ...

  • pg_depend - зависимости между объектами, в нашем случае - схем и таблиц

  • pg_attribute - столбцы таблиц

  • pg_statistic - статистика о распределении данных

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

  • она относится (pg_depend) к конкретной схеме (pg_namespace) public (чтобы в анализ не попадали всякие системные таблицы из pg_catalog и information_schema)

  • количество страниц (pg_class.relpages) в ней существенно больше необходимого для хранения такого количества записей (pg_class.reltuples)

  • чтобы оценить это самое "необходимое количество" мы по статистике распределения данных (pg_statistic) для каждого из столбцов таблицы (pg_attribute) поймем, сколько там NULL-значений (pg_statistic.stanullfrac) и средний размер хранимых данных (pg_statistic.stawidth) с учетом выравнивания (pg_attribute.attalign)

  • и все это попробуем максимально плотно "разложить" в соответствии с компоновкой страницы на минимальное их количество

Упаковываемся на страницу

Итак, в нашем распоряжении есть страница данных - сколько записей реально туда "упаковать"?

Физический размер страницы определяется на моменте компиляции движка PostgreSQL, поэтому обычно его никто не меняет и оставляет равным 8KB. Но более правильным, чем захардкодить константу, будет в явном виде спросить ее у сервера из параметра block_size:

SELECT current_setting('block_size')::integer;-- 8192

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

структура размещения данных в таблицеструктура размещения данных в таблице

Тут стоит обратить внимание, что если начать считать по табличке в документации, то будет упорно выходить 27 байт, если не обратить внимание на один факт:

структура HeapTupleHeaderDataструктура HeapTupleHeaderData

В исходниках, t_cid и t_xvac описаны как C-union, то есть занимают одни и те же 4 байта.

"Почти бесплатные" NULL'ы

Отдельно стоит отметить момент хранения NULL-значений столбцов записи. Если конкретная запись содержит NULL-значение в любом из полей, то в HeapTupleHeaderData к 23 "стабильным" байтам заголовка будет добавлена битовая маска по количеству полей.

То есть если у вас в записи 8 полей и любое из них NULL, то добавлен будет 1 байт, что в сумме даст 24 байта заголовка и с учетом выравнивания. А вот если полей 9, то ой... добавится 2 байта, что в сумме даст уже 25, а с учетом выравнивания на 64-bit системах - уже 32 байта.

При этом в "теле" записи NULL не хранится никак и дополнительного места не занимает.

Немного математики

Заметим, что для определения общего размера записи нам необходимо знать о наличии NULL в любом из ее полей, а в pg_statistic.stanullfrac хранится доля NULL-значений для конкретного поля.

Поэтому, чтобы получить долю записей, содержащих хотя бы один NULL, нам всего лишь надо перемножить вероятности. А для этого нам пригодился бы агрегат-произведение, которого, увы, нету среди стандартных sum/avg/min/max/count.

Не беда! Тут нам на помощь придет математический "хак", который я приводил в статье "SQL HowTo: 1000 и один способ агрегации":

P = exp(sum(ln(...)))P = exp(sum(ln(...)))

Пора писать код!

Для самопроверки добавим точно такую же таблицу, как наша, но не будем над ней издеваться:

CREATE TABLE nobloat ASSELECT 0 i;CREATE INDEX ON nobloat(i);
-- objects-in-schemeWITH dep AS (  -- https://postgrespro.ru/docs/postgresql/12/catalog-pg-depend  SELECT    objid  FROM    pg_depend  WHERE    (      refclassid    , refobjid    , classid    ) = (      'pg_namespace'::regclass    , (        -- https://postgrespro.ru/docs/postgresql/12/catalog-pg-namespace        SELECT          oid        FROM          pg_namespace        WHERE          nspname = 'public' -- schema        LIMIT 1      )    , 'pg_class'::regclass    ))-- objects, cl AS (  -- https://postgrespro.ru/docs/postgresql/12/catalog-pg-class  SELECT    oid  , relname  , relpages  , reltuples  FROM    pg_class cl  WHERE    oid = ANY(ARRAY(TABLE dep)::oid[]) AND    relkind IN ('r', 'm', 'p') -- relation | matview | partition)SELECT  T.*, cl.*, CASE    WHEN ratio >= 1 THEN (ratio - 1) * def.PAGESIZE  END::bigint repack_effect_sizeFROM  (    -- https://postgrespro.ru/docs/postgresql/12/storage-page-layout    SELECT      current_setting('block_size')::integer PAGESIZE    , CASE        WHEN version() ~ 'mingw32' OR version() ~ '64-bit' THEN 8        ELSE 4      END MAXALIGN  ) def, cl, LATERAL (    WITH cols AS (      SELECT        *      , (sz + szq - 1) / szq * szq sza -- aligned size      FROM        (            -- https://postgrespro.ru/docs/postgresql/12/catalog-pg-attribute            -- https://postgrespro.ru/docs/postgresql/12/catalog-pg-statistic            SELECT              attname            , attnum            , coalesce(st.stanullfrac, 0) nfr            , CASE                WHEN attlen = -1 THEN                  CASE                    WHEN attstorage IN ('p', 'm') OR stawidth < 2048 THEN -- toast_tuple_target                      stawidth                    ELSE 8 -- len + toast_id                  END                ELSE attlen              END sz            , CASE attalign                WHEN 'c' THEN 1                WHEN 's' THEN 2                WHEN 'i' THEN 4                WHEN 'd' THEN 8              END szq            FROM              pg_attribute at            LEFT JOIN              pg_statistic st                ON (st.starelid, st.staattnum) = (at.attrelid, at.attnum)            WHERE              at.attrelid = cl.oid AND              NOT at.attisdropped AND -- without dropped columns              at.attnum > 0           -- without system columns (tableoid, ctid, ...)            ORDER BY              attnum        ) T    )    SELECT      CASE        WHEN reltuples = 0 AND relpages = 0 THEN 1        WHEN reltuples = 0 AND relpages > 0 THEN NULL        ELSE          relpages /            ceil( -- need pages              reltuples /                (                  (PAGESIZE - 24) / -- PageHeaderData                    ceil(szt::double precision / reltuples)::bigint -- avg tuple size                ) -- tuples-per-page            )      END ratio    FROM      (        SELECT          reltuples * 4 + -- ItemIdData          (            (reltuples - fnnt) * hdr_tuple_w_nulls + -- hdr, tuples w/nulls            fnnt * hdr_tuple_wo_nulls                -- hdr, tuples wo/nulls          ) +          sztc szt -- size of tuples        FROM          (            SELECT              sztc            , fnnt              -- aligned headers            , ceil(hdr_cols_sys::double precision / MAXALIGN)::integer * MAXALIGN hdr_tuple_wo_nulls            , ceil((hdr_cols_sys + hdr_cols_null)::double precision / MAXALIGN)::integer * MAXALIGN hdr_tuple_w_nulls            FROM              (                SELECT                  -- https://doxygen.postgresql.org/htup__details_8h_source.html#l00121                  -- 4 : t_xmin                  -- 4 : t_xmax                  -- 4 : t_cid | t_xvac                  -- 6 : t_ctid                  -- 2 : t_infomask2                  -- 2 : t_infomask                  -- 1 : t_hoff                  23 hdr_cols_sys                , ceil(count(*)::double precision / 8)::integer hdr_cols_null                , sum(ceil(sza * reltuples * (1 - nfr))) sztc -- size of tuples cols                , trunc(exp(sum(ln(CASE WHEN nfr < 1 THEN 1 - nfr ELSE 1 END))) * reltuples) fnnt -- full-not-null-tuples                FROM                  cols              ) T          ) T      ) T  ) T;

Что в результате?

ratio |   oid | relname | relpages | reltuples | repack_effect_size-------------------------------------------------------------------   73 | 41333 | bloat   |       73 |        15 |             589824    1 | 41337 | nobloat |        1 |         1 |                  0

Заметим, что PostgreSQL считает, что в bloat у нас 15 записей, а не 1, как в реальности - это следствие неактуальной статистики как результат не выполненного вовремя ANALYZE. Ровно по той же причине может получиться, что ratio окажется меньше 1.

А дальше - сами определяйтесь, какие из таблиц вы будете "сжимать" и чем:

Подробнее..

DBA Когда почти закончился serial

24.03.2021 18:04:11 | Автор: admin

"Шеф, всё пропало, у нас serial на мегатаблице почти закончился!" - а это значит, что либо вы его неаккуратно накрутили сами, либо у вас действительно данных столько, что разрядности integer-столбца уже не хватает для вашей большой и активной таблицы в PostgreSQL-базе.

Да и столбец этот не простой, а целый PRIMARY KEY, на который еще и ряд других немаленьких таблиц по FOREIGN KEY завязан. А еще и приложение останавливать совсем не хочется, ибо клиентам 24x7 обещано...

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

Организуем небольшой тестовый полигон:

CREATE TABLE tblpk(  pk    serial      PRIMARY KEY, valx    integer);INSERT INTO tblpk(valx)SELECT generate_series(1, 1e6);CREATE TABLE tblfk(  fk    integer      REFERENCES tblpk, valy    integer);INSERT INTO tblfk(fk, valy)SELECT (random() * (1e6 - 1))::integer + 1, generate_series(1, 1e6);-- не забываем, что для FK нужно создавать индекс "вручную"CREATE INDEX ON tblfk(fk);

Подготовительные работы

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

Добавляем новое поле:

ALTER TABLE tblpk ADD COLUMN _pk bigint;ALTER TABLE tblfk ADD COLUMN _fk bigint;

Универсальный копирующий триггер

Чтобы для всех добавляемых и изменяемых записей состояние нового и старого полей у нас не разбегалось, повесим на таблицу копирующий триггер - на вставку новой записи или изменение отслеживаемого поля:BEFORE INSERT OR UPDATE OF <PK-поле>.

Ровно такую же задачу нам придется решать и для таблицы tblfk, поэтому сразу напишем триггерную функцию, которую можно будет универсально применять на любой таблице, использовав немного SQL-магии:

CREATE OR REPLACE FUNCTION copy_fld() RETURNS trigger AS $$DECLARE  fld_src text := quote_ident(TG_ARGV[0]); -- имя исходного поля  fld_dst text := quote_ident(TG_ARGV[1]); -- имя целевого поляBEGIN  EXECUTE $q$                 -- собираем тело запроса как текст    SELECT      (        json_populate_record( -- наполняем запись данными из JSON          $1                  -- NEW        , json_build_object(  -- {[fld_dst] : NEW[fld_src]}::json            '$q$ || fld_dst || $q$'          , $1.$q$ || fld_src || $q$::text          )        )      ).*                     -- "разворачиваем" record по столбцам    $q$    USING NEW -- используем NEW в качестве $1-аргумента    INTO NEW; -- результат складываем обратно в NEW  RETURN NEW; -- не забываем вернуть NEW, иначе изменения не применятсяEND $$ LANGUAGE plpgsql;

Теперь мы можем передать синхронизируемые поля как аргументы триггера - разные для каждой из таблиц:

CREATE TRIGGER copy BEFORE INSERT OR UPDATE OF pk   ON tblpk   FOR EACH ROW   EXECUTE PROCEDURE copy_fld('pk', '_pk'); -- откуда/кудаCREATE TRIGGER copy BEFORE INSERT OR UPDATE OF fk   ON tblfk   FOR EACH ROW   EXECUTE PROCEDURE copy_fld('fk', '_fk');

Массовое обновление записей

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

UPDATE tblpk SET _pk = pk WHERE _pk IS NULL;UPDATE tblfk SET _fk = fk WHERE _fk IS NULL;

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

Лучше всего воспользоваться сегментным обновлением, как это описано в статье "PostgreSQL Antipatterns: обновляем большую таблицу под нагрузкой". В результате единый UPDATE превратится в серию быстрых запросов, которые отлично садятся на индекс первичного ключа:

UPDATE  tblpkSET  _pk = pkWHERE  pk BETWEEN $1 AND $1 + 999 AND -- перебираем сегменты значений по 1K  _pk IS NULL;

Создаем новый индекс

В неблокирующем режиме создаем индекс, который будет выполнять роль нового первичного ключа:

CREATE UNIQUE INDEX CONCURRENTLY _pk ON tblpk(_pk); -- индекс под новый PKCREATE INDEX CONCURRENTLY _fk ON tblfk(_fk);        -- индекс под новый FK

В принципе, индексы можно было создать и раньше, но тогда все наши UPDATE писали бы еще и в него, поэтому работали бы существенно дольше.

Быстрая неблокирующая* конвертация

Сначала поймем, как примерно должен выглядеть наш целевой результат в самом простом варианте:

  • снимаем все autovacuum/autoanalyze, которые блокируют наши таблицы

    Эти процессы запустятся с очень большой вероятностью практически сразу, поскольку мы UPDATE'нули все записи в каждой из таблиц. Если мы не снимем их и накладываемые ими блокировки, все наши ALTER TABLE будут ждать получения блокировки сами (Access Exclusive), а за ними будет копиться очередь всех остальных запросов, даже SELECT (Access Share) по этим таблицам.

  • блокируем таблицы в монопольном режиме

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

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

  • модифицируем последовательность: привязываем ее к новому столбцу (OWNED BY) и снимаем ограничение на максимальное значение (NO MAXVALUE)

  • модифицируем основную таблицу:

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

    • переименовываем новый столбец в старый

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

    • создаем новый первичный ключ с использованием заранее подготовленного уникального индекса, что заодно этот индекс и переименует

  • аналогично модифицируем связанную таблицу в чуть другом порядке:

    • удаляем и переименовываем столбцы

    • восстанавливаем внешний ключ в NOT VALID-режиме без фактической проверки уже содержащихся в таблице данных

    • восстанавливаем имя индекса под внешним ключом

BEGIN;  -- снимаем все процессы autovacuum/autoanalyze по нашим таблицам  SELECT    pg_terminate_backend(pid)  FROM    pg_stat_activity sa  WHERE    CASE      WHEN backend_type = 'autovacuum worker' THEN        EXISTS(          SELECT            NULL          FROM            pg_locks          WHERE            locktype = 'relation' AND            relation = ANY(ARRAY['tblpk', 'tblfk']::regclass[])        )    END;  -- сразу блокируем все таблицы, чтобы никто не влез  LOCK TABLE tblpk, tblfk IN ACCESS EXCLUSIVE MODE NOWAIT;  -- sequence  ALTER SEQUENCE tblpk_pk_seq OWNED BY tblpk._pk;  ALTER SEQUENCE tblpk_pk_seq NO MAXVALUE;  -- tblpk  ALTER TABLE tblpk    DROP COLUMN pk CASCADE; -- сносит заодно copy-триггер, PK и все FK  ALTER TABLE tblpk    RENAME COLUMN _pk TO pk;  ALTER TABLE tblpk    ALTER COLUMN pk SET DEFAULT nextval('tblpk_pk_seq');  ALTER TABLE tblpk    ADD CONSTRAINT tblpk_pkey PRIMARY KEY USING INDEX _pk;  -- tblfk  ALTER TABLE tblfk    DROP COLUMN fk CASCADE;  ALTER TABLE tblfk    RENAME COLUMN _fk TO fk;  ALTER TABLE tblfk    ADD CONSTRAINT tblfk_fk_fkey      FOREIGN KEY(fk)      REFERENCES tblpk      NOT VALID; -- без проверки ограничения по существующим данным  ALTER INDEX _fk RENAME TO tblfk_fk_fkey;COMMIT;

Все эти действия происходят единым куском под общей блокировкой, поэтому, благодаря транзакционности DDL в PostgreSQL, либо успешно выполнятся целиком, либо целиком же - нет. Однако, за счет того, что тут нет ни одной длительной операции, весь скрипт должен отработать за минимальное время.

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

ALTER TABLE tblfk  VALIDATE CONSTRAINT tblfk_fk_fkey;

Что мы забыли?

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

Связанные объекты

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

Тут мы встречаем нескольких персонажей, чьи имена мы нигде ранее не упоминали, не задавали, и потому знать не можем:

  • tblpk_pkey - имя ограничения первичного ключа

  • tblfk_fk_fkey - имя ограничения внешнего ключа

  • tblpk_pk_seq - имя serial-последовательности

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

Сложные индексы

Аналогично, мы исходили из предположения, что индексы у нас самые простые, из единственного поля и без всяких условий. Но FK-индекс запросто может иметь вид tblfk(fk) WHERE fk IS NOT NULL, чтобы NULL-строки не замусоривали его, а PK включать в себя и другие поля, кроме serial.

Действия внешних ключей

Внешние ключи также могут быть определены существенно более сложно, чем в нашей модели - там может оказаться что-то вроде MATCH PARTIAL INITIALLY DEFERRED или ON DELETE SET NULL ON UPDATE RESTRICT.

Триггеры

Удалив каскадно старый столбец, мы снесли также и copy-триггер. А что если он был не один на этом поле?..

Имена и комментарии

Имя индекса внешнего ключа мы восстанавливали "по наитию", но нет абсолютно никакой гарантии, что оно совпадает с именем FK-ограничения.

А еще мы забыли восстановить комментарии объектов, которые могли быть наложены через COMMENT ON.

Скрипт миграции

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

  • sequence ссылается на поле через OWNED BY, а оно обратно через DEFAULT

  • индексы и триггеры ссылаются на поле напрямую

  • FK-constraint связывает поля пары таблиц и уникальный индекс на ведущей таблице

  • и все это может быть откомментировано

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

CREATE TABLE "1st table"(  "primary key col"    serial      PRIMARY KEY, valx    integer);COMMENT ON COLUMN "1st table"."primary key col"  IS 'col-comment';INSERT INTO "1st table"(valx)SELECT generate_series(1, 1e5);CREATE TABLE "2nd table"(  fk    integer      CONSTRAINT "FK-name" REFERENCES "1st table"        ON UPDATE SET NULL        ON DELETE RESTRICT, valy    integer);COMMENT ON CONSTRAINT "FK-name" ON "2nd table"  IS 'con-comment';INSERT INTO "2nd table"(fk, valy)SELECT (random() * (1e5 - 1))::integer + 1, generate_series(1, 1e5);CREATE INDEX "FK-idx-name" ON "2nd table"(fk);COMMENT ON INDEX "FK-idx-name"  IS 'idx-comment';CREATE OR REPLACE FUNCTION tmp() RETURNS trigger AS $$BEGIN  RAISE NOTICE 'NEW : %', NEW::text;END $$ LANGUAGE plpgsql;CREATE TRIGGER tmp AFTER INSERT OR UPDATE OF "primary key col"  ON "1st table"  FOR EACH ROW  EXECUTE PROCEDURE tmp();COMMENT ON TRIGGER tmp ON "1st table"  IS 'trg-comment';

Ну, а теперь дело за малым! Вот наш скрипт:

Скрипт расширения serial -> bigserial
-- $1 : '"1st table"'     - с кавычками!-- $2 : 'primary key col' - без кавычек!WITH src(rel, fld) AS (  VALUES($1::regclass, $2::name)), fld AS (  SELECT    *  FROM    src  JOIN    pg_attribute at      ON (at.attrelid, at.attname) = (src.rel, src.fld)), idx AS (  SELECT    idx.*  FROM    fld  JOIN    pg_index idx      ON indrelid = attrelid AND      indkey::smallint[] && ARRAY[attnum]), con AS (  SELECT    CASE contype      WHEN 'p' THEN attnum      WHEN 'f' THEN conkey[array_position(confkey, attnum)]    END idkey  , con.*  FROM    fld  JOIN    pg_constraint con      ON (conrelid = attrelid AND conkey && ARRAY[attnum]) OR      (confrelid = attrelid AND confkey && ARRAY[attnum]))-- столбцы, входящие в PK или FK, colkey AS (  SELECT    *  , attrelid::regclass::text _attrel  , '_' || md5(attname) _attname  , quote_ident(attname) _qiattname  , replace(col_description(attrelid, attnum), '''', '''''') dsccol  FROM    con  INNER JOIN    pg_attribute at      ON (attrelid, attnum) = (conrelid, idkey)  WHERE    atttypid <> 'bigint'::regtype), code_col AS (  SELECT    string_agg($$-- $$ || _attrel || $$ALTER TABLE $$ || _attrel || $$  ADD COLUMN $$ || _attname || $$ bigint;$$ ||      CASE        WHEN dsccol IS NOT NULL THEN$$COMMENT ON COLUMN $$ || _attrel || '.' || _attname || $$  IS '$$ || dsccol || $$';$$        ELSE ''      END || $$CREATE TRIGGER copy  BEFORE INSERT OR UPDATE OF $$ || _qiattname || $$    ON $$ || _attrel || $$    FOR EACH ROW    EXECUTE PROCEDURE copy_fld('$$ || attname || $$', '$$ || _attname || $$');UPDATE $$ || _attrel || $$ SET $$ || _attname || $$ = $$ || _qiattname || $$ WHERE $$ || _attname || $$ IS NULL; -- лучше сегментно!!!$$    , ''    ) code  FROM    colkey)-- индексы, indkey AS (  SELECT    *  , quote_ident('_' || md5(sch || '.' || rel || '.' || idxname)) _idxname  FROM    (      SELECT        pg_get_indexdef(indexrelid) def      , cli.relnamespace::regnamespace::text sch      , idx.indrelid::regclass::text rel      , quote_ident(cli.relname) idxname      , replace(obj_description(cli.oid, 'pg_class'), '''', '''''') dscidx      , *      FROM        colkey      JOIN        pg_index idx          ON indrelid = attrelid AND          indkey::smallint[] && ARRAY[attnum]      JOIN        pg_class cli          ON cli.oid = idx.indexrelid    ) T), code_idx AS (  SELECT    string_agg(      E'-- ' || idxname || E'\n' ||      regexp_replace(        regexp_replace(          def        , E'(CREATE(?: UNIQUE)? INDEX ).*?( ON ).*?( USING )'        , E'\\1CONCURRENTLY ' || _idxname || E'\n  ON ' || sch || '.' || rel || E'\n  USING '        )      , E'(USING \\S+ \\(.*)' || _qiattname || E'(.*\\))'      , E'\\1' || _attname || E'\\2'      , 'g'      ) || E';\n'      || CASE        WHEN dscidx IS NOT NULL THEN$$COMMENT ON INDEX $$ || _idxname || $$  IS '$$ || dscidx || $$';$$        ELSE ''      END    , ''    ) code  FROM    indkey)-- тфблицы, code_rel AS (  SELECT    $q$-- зачищаем мешающие autovacuumSELECT  pg_terminate_backend(pid)FROM  pg_stat_activity saWHERE  CASE    WHEN backend_type = 'autovacuum worker' THEN      EXISTS(        SELECT          NULL        FROM          pg_locks        WHERE          locktype = 'relation' AND          relation = ANY('$q$ || array_agg(rel)::text || $q$'::regclass[])      )    END;-- блокируем все таблицыLOCK TABLE $q$ || string_agg(rel, ', ') || $q$ IN ACCESS EXCLUSIVE MODE NOWAIT;$q$ code  FROM    (      SELECT DISTINCT        _attrel rel      FROM        colkey    ) T)-- последовательность, seqkey AS (  SELECT    pg_get_serial_sequence(attrelid::regclass::text, attname) seq  , *  FROM    colkey), code_seq AS (  SELECT$q$ALTER SEQUENCE $q$ || seq || $q$  OWNED BY $q$ || _attrel || '.' || _attname || $q$;ALTER SEQUENCE $q$ || seq || $q$  NO MAXVALUE;$q$  FROM    seqkey  WHERE    seq IS NOT NULL)-- столбцы, code_col_tx AS (  SELECT    string_agg($$-- $$ || _attrel || $$ALTER TABLE $$ || _attrel || $$  DROP COLUMN $$ || _qiattname || $$ CASCADE;ALTER TABLE $$ || _attrel || $$  RENAME COLUMN $$ || _attname || $$ TO $$ || _qiattname || $$;$$ ||      CASE        WHEN adsrc IS NOT NULL THEN$$ALTER TABLE $$ || _attrel || $$  ALTER COLUMN $$ || _qiattname || $$    SET DEFAULT $$ || adsrc || $$;$$        ELSE ''      END    ,   ''    ) code  FROM    colkey  LEFT JOIN    pg_attrdef ad      ON (adrelid, adnum) = (attrelid, attnum))-- индексы, code_idx_tx AS (  SELECT    string_agg($$ALTER INDEX $$ || _idxname || $$  RENAME TO $$ || idxname || $$;$$    , '')  FROM    indkey)-- ключи, code_con_tx AS (  SELECT    string_agg(    (      SELECT        string_agg(          'ALTER TABLE ' || conrelid::regclass::text || E'\n  ADD ' ||          CASE con.contype            WHEN 'p' THEN              'PRIMARY KEY USING INDEX ' || idxname            WHEN 'u' THEN              'UNIQUE USING INDEX ' || idxname            WHEN 'f' THEN              'CONSTRAINT ' || quote_ident(con.conname) || ' ' || pg_get_constraintdef(con.oid) || CASE WHEN pg_get_constraintdef(con.oid) !~* 'NOT VALID' THEN E'\n    NOT VALID' ELSE '' END          END || E';\n' ||          CASE            WHEN obj_description(con.oid, 'pg_constraint') IS NOT NULL THEN$$COMMENT ON CONSTRAINT $$ || quote_ident(conname) || $$ ON $$ || conrelid::regclass::text || $$  IS '$$ || replace(obj_description(con.oid, 'pg_constraint'), '''', '''''') || $$';$$            ELSE ''          END        , ''        ORDER BY          CASE con.contype            WHEN 'p' THEN 0            WHEN 'u' THEN 1            WHEN 'f' THEN 2          END        )      FROM        pg_constraint con      WHERE        conindid = indexrelid    )    , ''    ) code  FROM    indkey)-- триггеры, trgkey AS (  SELECT    pg_get_triggerdef(trg.oid) def  , replace(obj_description(trg.oid, 'pg_trigger'), '''', '''''') dsctrg  , *  FROM    colkey  JOIN    pg_trigger trg      ON tgrelid = attrelid AND      tgattr::smallint[] && ARRAY[attnum]  WHERE    NOT tgisinternal), code_trg AS (  SELECT    string_agg(      def || E';\n'      || CASE        WHEN dsctrg IS NOT NULL THEN$$COMMENT ON TRIGGER $$ || quote_ident(tgname) || $$ ON $$ || _attrel || $$  IS '$$ || dsctrg || $$';$$        ELSE ''      END    , ''    ) code  FROM    trgkey)SELECT  E'-- столбцы\n' ||  (TABLE code_col) ||  E'\n-- индексы\n' ||  (TABLE code_idx) ||  E'\nBEGIN;\n' ||  regexp_replace(    (TABLE code_rel) ||    E'\n-- последовательность\n' ||    (TABLE code_seq) ||    E'\n-- столбцы\n' ||    (TABLE code_col_tx) ||    E'\n-- индексы\n' ||    (TABLE code_idx_tx) ||    E'\n-- ключи\n' ||    (TABLE code_con_tx) ||    E'\n-- триггеры\n' ||    (TABLE code_trg)  , E'^(.)'  , E'  \\1'  , 'gm'  ) ||  E'COMMIT;\n';

Надеюсь, когда-то этот скрипт пригодится и вам.

Подробнее..

DBA меняем слонов на переправе

05.04.2021 12:20:40 | Автор: admin

Как нормальные DBA, мы подождали выпуск пары минорных версий к PostgreSQL 13, который должен порадовать нас многими полезными вещами, и теперь готовы перенести базу нашего сервиса мониторинга этой СУБД с 12-й версии на 13-ю.

Но как это сделать с минимальным простоем, а лучше вообще без него? На помощь придет функционал Foreign Data Wrappers, а точнее - postgres_fdw.

Структура исходной базы

Некоторые детали об устройстве базы нашего сервиса, которые помогают нам очень быстро записывать приходящие данные, я рассказывал в статьях "Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB" и "Экономим копеечку на больших объемах в PostgreSQL". Если в двух словах, то грамотное секционирование БД решает массу проблем производительности.

Мы применяем секционирование по дням в силу того, что у нас приходит по 100-150GB данных ежедневно. Давайте смоделируем структуру нашей базы tst на PostgreSQL 12:

CREATE TABLE archive(  dt    date, val    integer)PARTITION BY RANGE(dt); -- секционирование по диапазонамCREATE TABLE archive_20210401 -- секция конкретного дня  PARTITION OF archive    FOR VALUES FROM ('2021-04-01') TO ('2021-04-02');    -- dt >= '2021-04-01' AND dt < '2021-04-02'CREATE TABLE archive_20210402  PARTITION OF archive    FOR VALUES FROM ('2021-04-02') TO ('2021-04-03');

Хоть каждый день и представлен конкретным значением dt, мы все равно предпочитаем использовать PARTITION BY RANGE с указанием FOR VALUES FROM (dt) TO (dt + 1) вместо PARTITION BY LIST, поскольку этот вариант допускает определенную гибкость - например, можно слить некоторые архивные секции до месячных вместо суточных.

Проверим, как раскладываются по секциям записи при вставке в такую таблицу:

INSERT INTO archiveVALUES  ('2021-04-01', 1), ('2021-04-02', 2)RETURNING  tableoid::regclass, *;
 tableoid        |  dt        | valarchive_20210401 | 2021-04-01 |   1archive_20210402 | 2021-04-02 |   2

tableoid - это одно из системных полей записи, представляющее ссылку на конкретную таблицу (в нашем случае - секцию), в которой она находится. Подробнее можно прочитать в статье "PostgreSQL Antipatterns: уникальные идентификаторы".

"Подцепляем" старые данные

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

Поэтому мы можем спокойно развернуть PostgreSQL 13 прямо на том же сервере, не создавая никакой избыточной нагрузки. Для определенности примем, что v12 доступна на :5439, а v13 будет на :5440.

Сначала повторим структуру базы для новых секций на новом сервере:

CREATE TABLE archive(  dt    date, val    integer)PARTITION BY RANGE(dt);CREATE TABLE archive_20210403  PARTITION OF archive    FOR VALUES FROM ('2021-04-03') TO ('2021-04-04');

Чтобы не создавать проекции всех старых секций с помощью IMPORT FOREIGN SCHEMA, ограничимся единственной вспомогательной секцией на определенный вид данных (планы, запросы, ...). Но раз таких видов у нас много, сразу настроим полный доступ к старому серверу, чтобы не копипастить параметры каждый раз:

CREATE EXTENSION postgres_fdw;CREATE SERVER postgresql_12  FOREIGN DATA WRAPPER postgres_fdw    OPTIONS (host '127.0.0.1', port '5439', dbname 'tst');CREATE USER MAPPING FOR postgres  SERVER postgresql_12    OPTIONS (user 'postgres', password 'postgres');

Теперь остается всего лишь создать служебную секцию, обеспечивающую доступ к старым данным:

CREATE FOREIGN TABLE archive_old  PARTITION OF archive    FOR VALUES FROM ('-infinity') TO ('2021-04-03')    -- dt < '2021-04-03'  SERVER postgresql_12    OPTIONS(table_name 'archive');

Тут нам снова удачно помогло использование PARTITION BY RANGE вместо BY LIST, позволив опереться на неравенство.

Проверим, как на новом сервере отработает аналогичная вставка данных:

INSERT INTO archiveVALUES  ('2021-04-01', 1), ('2021-04-02', 2), ('2021-04-03', 3)RETURNING  tableoid::regclass, *;
 tableoid        |  dt        | valarchive_old      | 2021-04-01 |   1 -- старый серверarchive_old      | 2021-04-02 |   2archive_20210403 | 2021-04-03 |   3 -- новая секция

Дополнительные настройки FDW

Если в течение какого-то времени нам необходимо поддерживать возможность записи в старые секции, убедитесь в правильном задании значения FDW-параметра updatable, а если планируете активно читать оттуда - обратите внимание на use_remote_estimate и fetch_size.

Ровно таким же способом, с помощью FDW-секций, можно распределять дисковую нагрузку на другие серверы - только тут уже лучше использовать PARTITION BY HASH, конечно.

Подробнее..

Чего энтерпрайзу в PostgreSQL не хватает

26.04.2021 10:24:01 | Автор: admin

В конце прошлого года Иван Панченко предложил мне рассказать на внутреннем семинаре Postgres Pro, чего, по нашему опыту использования PostgreSQL в "кровавом энтерпрайзе" "Тензора", не хватает в этой СУБД.

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

Обслуживание сервера

Легковесный менеджер соединений

он же Built-in connection pooler

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

Процесс старта ресурсоемкий и небыстрый сам по себе, а постоянно существующий процесс еще и резервирует на себя некоторую долю RAM, которая со временем имеет свойство расти за счет "накачки" метаинформацией.

Просто представьте, что у вас в базе развернуто миллион таблиц, к которым вы достаточно случайно обращаетесь. Только на системных таблицах pg_class, pg_depend, pg_statistics это даст объем порядка 1GB, который рано или поздно окажется в памяти процесса.

Поэтому в реальных условиях соединения непосредственно с БД стараются беречь, без нужды новые не создавать, а неактивные закрывать поскорее. Для этого "в разрыв" БЛ-БД ставят отдельный сервис connection pooler, который принимает от БЛ все "хотелки" по открытию/закрытию соединений и выполнению запросов, а в БД транслирует их в ограниченное количество длительно живущих коннектов.

Пример работы connection poolПример работы connection pool

Классические представители:

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

Поэтому еще в начале 2018 года Константин Книжник начал эксперименты с реализацией менеджера соединений "из коробки" на основе интеграции потоков (thread model) в ядро PostgreSQL. В настоящий момент его патч включен на ревью в июльский Commitfest, так что ждем и надеемся на появление хотя бы в v15.

64-bit XID

Если вы пишете в базу много данных, как мы, то достаточно скоро у вас запустится он - autovacuum (to prevent wraparound), чья единственная задача - пробежать по данным и "поправить" их так, чтобы уберечь счетчик транзакций от переполнения.

Одно из наглядных объяснений 32-bit transaction ID wraparoundОдно из наглядных объяснений 32-bit transaction ID wraparound

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

А чтобы никаких подобных процессов не запускалось, достаточно расширить идентификатор транзакции xid с 32 до 64 бит - соответствующий патч Александр Коротков запустил еще в 2017 году. В ядро он тогда так и не был включен, зато попал в Postgres ProEnterprise, откуда рано или поздно доберется и до "ванильного" ядра.

Система хранения данных

Микротаблицы

Каждая таблица и индекс в PostgreSQL с точки зрения хранения представляет из себя не меньше 3 файлов:

То есть если вам необходимо иметь небольшую статичную "словарную" табличку на пару десятков записей, то вы автоматически получаете 3 файла по 8KB, хотя можно было бы обойтись и единственной страницей heap.

Поэтому такая табличка, созданная в каждой из клиентских схем, где изолированы пользовательские данные, сразу дает резкое увеличение количества файлов на носителе (миллионы их!), что негативно влияет как на время бэкапа, так и на производительность всей дисковой подсистемы.

zheap

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

Информация о транзакциях хранится отдельно от контейнера записиИнформация о транзакциях хранится отдельно от контейнера записи

Почитать по теме:

Append-only Storage

В бизнесе СУБД часто используются для хранения многократно повторяющихся или монотонно увеличивающихся значений - например, логи, дата и время создания какого-то документа, его числовой PK, ...

Знание этого факта позволяет существенно сэкономить записываемый объем. Например, переведя базу нашего сервиса мониторинга с v12 на v13, мы сразу получили примерно 10% выигрыша в объеме индексов за счет дедупликации в btree-индексах на реальных данных.

В эту же категорию можно отнести уже принятый патч "BRIN multi-minmax and bloom indexes".

В идеале, хотелось бы иметь возможность назначать часть полей индексов или целых таблиц как "no-MVCC" (то есть не поддерживается никакое версионирование, а потому - и изменение данных, плюс мгновенная видимость со стороны других транзакций), чтобы иметь возможность сэкономить на полях поддержки MVCC.

Timescale базируется на ядре PostgreSQL, но "заточена" на timeseries-dataTimescale базируется на ядре PostgreSQL, но "заточена" на timeseries-data

Отложенная индексация

Одной из причин Uber-скандала, всколыхнувшего PostgreSQL-сообщество несколько лет назад была Index Write Amplification, когда записываемый в таблицу кортеж сразу же одновременно записывается и во все индексы, подходящие по условию для него. Получается, чем больше индексов есть на таблице, тем дольше будет производиться вставка в нее.

Но ведь большинство этих индексов заведомо не будут использованы для поиска этой записи сразу же после завершения запроса! Точнее, разработчик точно может отметить эти индексы и их желаемую "актуальность", примерно как это настраивается для реплики.

CREATE INDEX ... WITH (max_lag = '1min');

В этом случае сервер сам сможет "размазать" необходимые операции во времени для балансировки нагрузки. Наиболее актуальна эта тема при конкуренции с поисковыми системами вроде Sphinx/ElasticSearch, где основная задача "найти вообще", а не "найти прямо сразу сейчас".

Почитать по теме:

Columnar Storage

В идеале - в ядре или в contrib иметь возможность подключения колоночного хранилища для различных аналитических нужд.

Хранение данных "по столбцам"Хранение данных "по столбцам"

Да, есть решения от Citus, но "в энтерпрайзе" не всегда есть возможность установки дополнительных внешних модулей.

In-memory Storage

Появление очень быстрого нетранзакционного хранилища без сброса на диск сильно помогло бы использовать разноуровневые кэши прямо в PostgreSQL, а не выносить их куда-то в Redis, например - получился бы некий аналог Oracle TimesTen In-Memory Database и Tarantool.

Масштабирование

TEMPORARY TABLE и реплики

Если для генерации отчетов в своей системе вы предпочитаете не писать мегасложные запросы, а более простые итерации по временным таблицам, то неприятностей будет ровно две:

И если с первой проблемой еще как-то можно смириться и исправлять регулярным VACUUM pg_class, то второй недостаток достаточно сильно мешает.

Multimaster

Давно хочется иметь "из коробки".

SQL

SQL-defined Index

Было бы фантастично уметь описывать новые виды индексов прямо на SQL/PLPGSQL, без необходимости C-кодинга - фактически, тут нет ограничений, кроме производительности из-за необходимости сделать все "здесь и сейчас". Но если вспомнить про описанную выше возможность отложенной индексации, то задача уже не кажется такой уж нереальной.

Ведь если можно описывать на различных языках логику формирования и преобразования записи таблицы, то почему бы не позволить то же самое для записи индекса?..

Почитать по теме:

Мониторинг

В инфраструктуре PostgreSQL сильно не хватает удобных средств быстрого визуального контроля и анализа происходящего с производительностью базы. Тут мы в меру собственных сил стараемся создавать подобные инструменты и формировать некоторую базу возможных оптимизаций.

Производительность запросов

Сам анализ, куда ушло время в запросе по тексту плана является нетривиальным, но если его прогнать через визуализацию на explain.tensor.ru, становится попроще:

Визуальный анализ плана запросаВизуальный анализ плана запросаДиаграмма выполнения планаДиаграмма выполнения плана

Почитать по теме:

Снапшоты статистики таблиц

Чтобы определить, в каком именно таблице/индексе "сильно болит", у Oracle есть AWR а наиболее близким аналогом для PostgreSQL является pg_profile.

Но идея хранить дополнительные данных ровно в той же базе, на которой и так идут проблемы (иначе зачем вам нужен столь подробный мониторинг?), нам показалась не слишком эффективной. Поэтому мы вынесли съем, хранение и визуализацию этой информации на отдельный сервис, который собирает у нас внутри компании всю статистику о работе PostgreSQL-серверов.

Тепловая карта операций по таблицамТепловая карта операций по таблицам

Почитать по теме:


Это - что напрягает нас в этой, безусловно, отличной СУБД. В комментариях оставляйте рассказы о своих "болях" и "хотелках" при использовании PostgreSQL.

Подробнее..

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru