постгрес

Недавно я узнал, что, кроме text, varchar и char, в Постгресе есть ещё и строковый тип "char", именно так, в кавычках. Используется во внутренних таблицах Постгреса для простого перечисления. Интересен тем, что занимает ровно один байт, в отличие от более известных типов, которые во внутреннем представлении хранят ещё и свою длину, поэтому всегда занимают больше.

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

Вчера под ночь боролся с «Постгресом» (это такая СУБД), в итоге поборол, но не так, как хотелось бы. Бизнес-задачу мне не хотелось бы сейчас рассматривать, поэтому для иллюстрации проблемы создам тестовую таблицу безо всякой смысловой нагрузки:

SELECT id, CASE WHEN RANDOM()>.5 THEN value END AS value
INTO test
FROM generate_series(1, 1000000) WITH ORDINALITY AS test(id, value)

В тестовой таблице два поля — идентификатор (id) и значение (value), примерно половина значений в поле value — NULL. И теперь нам надо создать индекс для запросов, которые выглядит примерно так:

SELECT id FROM test WHERE value IS NULL ORDER BY id;
SELECT id FROM test WHERE value IS NOT NULL ORDER BY id;

План для них выглядит одинаково печально, что логично — на таблице ни одного индекса:

Sort  (cost=61739.93..62977.68 rows=495100 width=4) (actual time=47289.775..47761.235 rows=498778 loops=1)
   Sort Key: id
   Sort Method: external merge  Disk: 6848kB
   ->  Seq Scan on test  (cost=0.00..14910.00 rows=495100 width=4) (actual time=12.039..40854.731 rows=498778 loops=1)
         Filter: (value IS NULL)
         Rows Removed by Filter: 501222
 Planning Time: 0.174 ms
 Execution Time: 48134.553 ms

Чтобы сэкономить место, мне очень хотелось сделать индекс функциональным:

CREATE UNIQUE INDEX ON test((value IS NULL), id);

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

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

Первый запрос ожидаемо улучшился:

Index Scan using test_expr_id_idx on test  (cost=0.42..33882.43 rows=495100 width=4) (actual time=0.056..519.584 rows=498778 loops=1)
   Index Cond: ((value IS NULL) = true)
 Planning Time: 0.182 ms
 Execution Time: 894.551 ms

А второй — нет:

Sort  (cost=62738.26..64000.51 rows=504900 width=4) (actual time=1046.582..1471.180 rows=501222 loops=1)
   Sort Key: id
   Sort Method: external merge  Disk: 6880kB
   ->  Seq Scan on test  (cost=0.00..14910.00 rows=504900 width=4) (actual time=0.033..522.071 rows=501222 loops=1)
         Filter: (value IS NOT NULL)
         Rows Removed by Filter: 498778
 Planning Time: 0.136 ms
 Execution Time: 1841.282 ms

Несмотря на то, что в индексе содержится информация, достаточная для выполнения этого запроса, в данном случае «Постгрес» не понимает, что VALUE IS NOT NULL эквивалентно (VALUE IS NULL) = false.

Причём, если попробовать переписать запрос, чтобы условие выборки выглядело как (VALUE IS NULL) = false, то тут оптимизатор не оплошает — перепишет за нас условие в виде VALUE IS NOT NULL и опять не будет узнавать его в индексе.

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

Во-первых, можно «замаскировать» конструкцию …IS [NOT] NULL, чтобы оптимизатор перестал его нормализировать:

CREATE UNIQUE INDEX ON test((CASE WHEN value IS NULL THEN TRUE ELSE FALSE END), id);

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

Во-вторых, можно создать два индекса вместо одного:

CREATE UNIQUE INDEX ON test((value IS NULL), id);
CREATE UNIQUE INDEX ON test((value IS NOT NULL), id);

Недостаток: у нас два индекса — при вставке или обновлении будем писать в два места, раздуваться (bloat) у нас будут тоже два индекса вместо одного.

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

CREATE UNIQUE INDEX ON test(value, id);

Фатальный недостаток: индекс, по сути, бесполезен — он содержит всю таблицу целиком, «Постгрес» скорее всего выберет последовательное сканирование таблицы, а индекс использовать не будет.

И, наконец, вариант, который мне нравится в этой ситуации больше всего — частичные индексы:

CREATE UNIQUE INDEX ON test(id) WHERE value IS NULL;
CREATE UNIQUE INDEX ON test(id) WHERE value IS NOT NULL;

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

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

В продукте, который мы делаем в нашей компании, не используется PDO для соединения с базой данных — так исторически сложилось и вряд ли имеет смысл менять. Причина тому — оптимизации; мы широко используем эскуэль и задействуем диалектные особенности используемой СУБД (в нашем случае это «Постгрес»). То есть универсальность PDO нам ничего бы не дала.

Недавно возникла задача выполнения запроса с таймаутом — если выполнение запроса занимает больше времени, чем по нашему мнению пользователь готов ждать, то лучше прервать запрос и показать заглушку. В PDO кажется такая возможность есть (параметр можно задать, но мы не тестировали), а в постгресовском модуле для ПХП готового способа не оказалось.

Мы долгое время перебирали идеи (вплоть до внешнего процесса с таймером), но ничего не выглядело достаточно хорошо, чтобы помещать это в код. В конечном счёте мой братишка предложил очень простое решение. Ответ лежал в работе с асинхронным АПИ. Обычно это выглядит как-то так:

$con = pg_pconnect("…");
if ($con === false) {
    throw new \RuntimeException('Could not connect');
}
// асинхронно выполняем запрос, в синхронном варианте тут было бы pg_query
if (pg_send_query($conn, "select * from users")) {
    // тут можно делать что угодно, пока мы не будем готовы принять результат
    // …
    // синхронно получаем результат
    var_dump(pg_get_result($conn));
}

Поскольку pg_send_query неблокирующий, то можно было бы попытаться как-то проконтролировать не истёк ли таймаут. Но как это сделать? Вот что он придумал:

function waitForResult($connection, callable $callback, int $timeout): void
{
    $stream = pg_socket($connection);
    while (pg_connection_busy($connection)) {
        $read = [$stream];
        $write = $except = null;

        $numChangedStreams = stream_select($read, $write, $except, 0, $timeout);
        if ($numChangedStreams > 0 || $numChangedStreams === false) {
            break;
        }

        $callback();
    }
}

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

Есть такая система репликации на триггерах для СУБД «Постгрес» — Slony, не знаю кто как читает это слово по всему миру, я всегда произносил его как «Сло́ни» — вслед за нашими системными администраторами.

Оказывается читать это слово следует немного иначе.

Примерно в конце девяностых на логотипе «Постгреса» появился слон, к этому логотипу, да и видимо вёрстке сайта, приложили руку россияне. Так картинка со слоном называлась «slonik.gif» и прислал её, как достоверно известно, петербуржец Дмитрий Самерсов, рисунок сделала какая-то его знакомая.

Тот самый логотип со слоном

Ян Вик, активный участник сообщества, в то время искал название для своей системы репликации и поинтересовался что значит «slonik», а потом спросил как будет много слонов, так и родилось название «Slony» — «Слоны́».

В синтаксисе запроса SELECT «Постгреса» есть приятная мелочь, которая хоть и нестандартна, но греет мне душу, — согласно описанию, выбираемые поля (часть между SELECT и FROM) можно опускать.

Вот как это работает в консоли:

SELECT FROM usr;
--
(588910 строк)

Кстати, можно ещё поставить там ключевое слово ALL, будет результат будет точно такой же.

Для чего это может пригодиться? Для подзапросов с EXISTS. Мне не нравится, как выглядит всем привычный вариант:

SELECT COUNT(*)
FROM document
WHERE EXISTS (
    SELECT NULL
    FROM document_n
    WHERE id = document.id
)

В подзапросе после SELECT по стандарту надо что-то указать, всё равно что, использоваться это не будет. Мне такая избыточность не по вкусу. А вот «Постгрес» позволяет ничего там не указывать:

SELECT COUNT(*)
FROM document
WHERE EXISTS (
    SELECT
    FROM document_n
    WHERE id = document.id
)

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

Если когда-либо встанет вопрос о переезде на другую СУБД, то вышеописанное — самая малая из проблем и решить её просто — заменой через регулярное выражение.

Читая документацию по «Постгресу», набрёл на интересную конструкцию — TABLESAMPLE. Она позволяет обработать запросом не всю таблицу, а только указанный процент, причём строки выбираются случайным образом.

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

SELECT id FROM (
    SELECT id FROM very_big_table TABLESAMPLE SYSTEM (.0001) LIMIT 100
) _ ORDER BY RANDOM() LIMIT 1

Тут выбирается одна десятитысячная процента таблицы (0,0001%), а потом из этой выборки берётся первая строка. На настоящей таблице с почти миллиардом строк, такой запрос у меня занял около ста миллисекунд.

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

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

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

SELECT sex, AVG(spent)
FROM meal TABLESAMPLE SYSTEM (1)
WHERE mtime BETWEEN SYMMETRIC '12:00:00' AND '14:00:00'
GROUP BY 1

Или сколько примерно раз я ездил такси «Везёт» за последние семь лет (оценка по пяти процентам и экстраполяция):

SELECT COUNT(*)*20
FROM taxi_call TABLESAMPLE BERNOULLI (5)
WHERE carrier='vezet' AND cdate >= '2011-08-03'

Пока сегодня ехал домой с военного поиска (я в таком уже участвовал в прошлом году), написала в «телеграм» дипломница — она не смогла разобраться как сконвертировать в буквы формат постгреса bytea, в котором мы ей выгрузили кое-какие данные по её теме.

Ограничусь фотографией на которой почти ничего не видно, дабы не эпатировать публику

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

def decodeBytea(bytes: bytearray, charset: str) -> str:
	if bytes[:2] == b'\\x':
		return bytearray.fromhex(bytes[2:].decode()).decode(charset)
	else:
		raise ValueError('Invalid format')

Формат немудрёный — впереди слеш-экс, дальше идут шестнадцатеричные коды байт, которые надо превратить в символы.

Братишка прислал ссылку на интересную статью — «Reducing bloat without locking». Речь идёт о так называемом «распухании» (bloating) таблиц в «Постгресе».

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

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

test=> CREATE TABLE test AS SELECT generate_series(1, 1000) id;
SELECT 1000
test=> SELECT MAX(ctid) FROM test;
  max
--------
 (4,96)
(1 row)
test=> DELETE FROM test WHERE id & 1 = 1;
DELETE 500
test=> SELECT MAX(ctid) FROM test;
  max
--------
 (4,96)
(1 row)

ctid — это специальный внутренний столбец «Постгреса», который отображает физическое расположение строки. Для каждой строки в нём записано два числа — номер страницы, где располагается строка и порядковый номер строки в странице.

Как видим, таблица «распухла» — половина строк удалена, а количество занимаемых страниц не изменилось. Что же можно сделать? Давайте попробуем команду VACUUM:

test=> VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 500 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 500 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

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

Что же делать? Можно выполнить VACUUM FULL — эта команда полностью перельёт таблицу на новое место, избавив нас от «дыр», но во время работы таблица будет эксклюзивно заблокирована, для больших таблиц, да ещё в продуктиве это непозволительно.

Другой способ — перенести строки с последних страниц в «дыры» на первых, освободив таким образом несколько страниц в конце. Попробуем:

test=> BEGIN;
BEGIN
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 161
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 161
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 48
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 48
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 34
test=> UPDATE test SET id=id WHERE ctid>='(3,0)';
UPDATE 0
test=> COMMIT;
COMMIT

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

test=> VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 452 row versions in 2 pages
INFO:  "test": found 452 removable, 500 nonremovable row versions in 5 out of 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 469 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": truncated 5 to 3 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

Как видимо команда удалила две неиспользуемые страницы и уменьшила общее количество страниц с пяти до трёх.

Идея, описанная в статье, лежит в основе скрипта pgcompacttable из инструментария компании «Дейта Игрет».

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

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

Посмотрел какие запросы вылезли наверх в «Барсуке», глазам не поверил — запросы довольно простые, с чего бы им тормозить, посмотрел планы и причину нашёл довольно быстро. Но обо всём по порядку.

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

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

А когда восстанавливались из дампа, вот что пошло не так:

sed=> CREATE INDEX du_uid_did ON document_user USING btree (user_id, document_id);
ERROR: temporary file size exceeds temp_file_limit (4194304kB)

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

Продолжаю серию удивительных открытий в мире перехода на «Постгрес». В документации к функции pg_execute есть малозаметное примечание к последнему параметру — в нём передаются значения для запроса:

Думаю мало кто обращает на него внимания, собственно, я тоже не обращал. Прежде чем двинуться дальше, разберёмся — что же здесь написано?

Перевод такой: все значения, которые передаются, приводятся к строкам. Код, который это выполняется выглядит так (взял из ПХП 7.2):

if (num_params > 0) {
        int i = 0;
        params = (char **)safe_emalloc(sizeof(char *), num_params, 0);

        ZEND_HASH_FOREACH_VAL(Z_ARRVAL_P(pv_param_arr), tmp) {
                ZVAL_DEREF(tmp);
                if (Z_TYPE_P(tmp) == IS_NULL) {
                        params[i] = NULL;
                } else {
                        zval tmp_val;

                        ZVAL_COPY(&tmp_val, tmp);
                        convert_to_cstring(&tmp_val);
                        params[i] = estrndup(Z_STRVAL(tmp_val), Z_STRLEN(tmp_val));
                        zval_ptr_dtor(&tmp_val);
                }
                i++;
        } ZEND_HASH_FOREACH_END();
}

pgsql_result = PQexecParams(pgsql, query, num_params,
                                NULL, (const char * const *)params, NULL, NULL, 0);

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

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

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

Ещё когда мы работали только с «Ораклом», заметили, что если вместо чисел привязывать строки, то иногда планы выполнения запросов меняются в худшую сторону. Лёгкость обращения с типами в ПХП иногда к этому приводит — переменная, используемая для хранения числа, имеет строковый тип.

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

В «Постгресе» проблемы будут, я их описал выше, так ещё и способа хорошего нет — все функции любого модуля для работы с этой базой, доступные в ПХП любой версии, преобразуют все числа в строки. К несчастью, в «Постгресе» проблема изменения планов тоже имеет место — недавно наткнулись на запрос, который через ПХП выполняется почти полторы секунды, а через консольный клиент — меньше миллисекунды.

Мой братишка придумал оригинальное решение — определять позиции на которых мы привязываем числа и автоматически внутри нашего фреймворка в этом месте запроса указывать тип bigint явным образом. То есть добавлять после плейсхолдера параметра конструкцию «::bigint».

Пришлось изменить несколько наших хранимых процедур, но в целом всё плошло довольно гладко.

Мы ещё в процессе перевода последних наших проектов с «Оракла» на «Постгрес», поэтому всё ещё не закончили избавляться от характерных для диалекта «Оракла» функций, тащим их с собой — пока так проще.

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

Ниже две функции на разных языках, поддерживаемых «Постгресом», по смыслу идентичные — обе представляют собой замену функции NVL2 «Оракла» над целыми числами:

CREATE FUNCTION nvl2_plpgsql(a numeric, b numeric, c numeric) RETURNS numeric AS '
BEGIN
  IF a IS NULL
    THEN RETURN c;
    ELSE RETURN b;
  END IF;
END;
' LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION nvl2_sql(a numeric, b numeric, c numeric) RETURNS numeric AS '
SELECT CASE WHEN a IS NULL THEN c ELSE b END;
' LANGUAGE SQL IMMUTABLE;

Будет ли какая-то разница в производительности? Посмотрим на тесты:

 p95=# EXPLAIN ANALYZE SELECT nvl2_plpgsql(g, 0, 0) FROM generate_series(1, 10000000) _(g);

 Planning time: 0.066 ms  Execution time: 38881.927 ms

p95=# EXPLAIN ANALYZE SELECT nvl2_sql(g, 0, 0) FROM generate_series(1, 10000000) _(g);

 Planning time: 0.253 ms  Execution time: 15435.231 ms

Как ни странно, функция на чистом ЭсКуЭле быстрее. Судя по моим экспериментам, так всегда. Иногда результаты различаются вдвое, иногда на порядок — зависит от того, что она делает, но как правило быстрее, обратных примеров я ещё не встречал.

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

Страшно удивился, когда увидел в логах «Постгреса» запрос, которого не было в коде нашего продукта:

select oid,typname from pg_type

Как-то сразу догадался, что он порождается вызовом pg_field_type, который у нас используется, чтобы прозрачно для верхнего уровня обрабатывать тип bytea. Заменил вызов на pg_field_type_oid и запрос пропал.

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

Я ещё не так давно работаю с «Постгресом» и раз в месяц натыкаюсь на что-то неожиданное.

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

В «Оракле» такую миграцию наши ребята, насколько я знаю, разбивали хранимой процедурой на части и коммитили внутри кусками по миллиону, в «Постгресе» так нельзя — блок на языке PL/pgSQL выполняется в собственной транзакции и она прервётся при попытке сделать COMMIT. Автономных транзакций нет.

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

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

DO $$
  DECLARE
    max_id table.id % TYPE;
    query TEXT;
BEGIN
  -- делим таблицу на куски по миллиону, теперь в max_id у нас номер последнего куска
  SELECT CEIL(MAX(id)::float/1000000) INTO max_id FROM mytable;

  -- очищаем файл /tmp/sqlquery
  COPY (SELECT '') TO '/tmp/sqlquery';

  -- айдишники в моём конкретном случае идут с единицы, но более правильно идти с минимума до максимума с шагом
  FOR i IN 0..max_id LOOP
        query = FORMAT(
            'UPDATE mytable '
            'SET something=value '
            'WHERE id BETWEEN ' || i*1000000+1 || ' AND ' || (i+1)*1000000 || ';'
        );

        -- запуск /bin/cat нужен, чтобы дописывать в файл
        EXECUTE  'COPY (SELECT ' || quote_literal(query) || ') TO PROGRAM ''/bin/cat >> /tmp/sqlquery''';
  END LOOP;
END$$;

-- запуск запроса
\i /tmp/sqlquery

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

Обратите внимание на вызов /bin/cat — это важно, так как команда COPY сама не умеет добавлять что-то в файл, она его перезаписывает.

При обыденной смене типа колонки в «Постгресе» с текстового типа на бинарный получил ошибку «ERROR: invalid input syntax for type bytea». Запрос довольно рядовой и сюрпризов я не ожидал:

ALTER TABLE tbl ALTER COLUMN colmn TYPE bytea USING colmn::bytea;

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

ALTER TABLE tbl ALTER COLUMN colmn TYPE bytea USING REPLACE(colmn, '\', '\\')::bytea;

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

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

Много любопытного я узнал об этой СУБД, возможно когда-нибудь напишу, если не забудется, но самое примечательное следующее. На «Оракле» у нас, естественно, был кластер, это очень удобно программисту — этакий «чёрный ящик», знай кидай туда запросы, оно само разрулит (на самом деле, это типичная «дырявая абстракция», но чаще всего напрягаться не надо).

На «Постгресе» мы, конечно, захотели аналога и, казалось, нашли его — pgpool-II, который разделяет запросы на пишущие и читающие, которые потом распределяются им на мастер и синхронные реплики.

Наши нагрузочные испытания показали, что на пиковых нагрузках синхронные реплики никакие не синхронные, оказалось, что «синхронность» реплики только в том она говорит мастеру «я всё приняла», когда данные попали на диск и именно в этот момент.

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

Это будет исправлено в будущей 9.6, а пока живём с тем, что есть.

Кто-то меня спрашивал о выгоде преподготовленных планов в «Постгресе». Наши испытания показывают, что выигрыш порядка 10—15%, что очень немало. И я уже рассказывал, что придумал рецепт как делить их в условиях «умирающего» ПХП.

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

Какой выход? Краткий ответ: пока никакой.

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

С другой стороны у меня появилась идея — добавить в эскуэль конструкцию PREPARE IF NOT EXISTS, ребята из, с которыми я обсуждал эту идею, PostgresPro уже сделали несколько версий патча.

Основа идеи — всегда слать запрос на компиляцию плана, но если он уже был скомпилирован, ничего не делать. Мы его уже погоняли (оттуда и цифры первого абзаца), но на этом пути больше трудностей, чем себе можно представить — в частности, надо патчить ещё и продукты для балансировки и расширять его клиентский протокол.

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

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

Если вы знаете рецепт лучше, поделитесь, пожалуйста.

Как я уже рассказывал, поскольку продукт у нас мигрирует с «Оракла» на «Постгрес», мы сейчас живём сразу на двух базах и некоторые виды запросов строят унифицированные билдеры.

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

WITH RECURSIVE prior AS (
    WITH virtual AS (
        -- тут у нас подзапрос
    )
    SELECT … FROM virtual WHERE …некий критерий…
    UNION ALL
    SELECT … FROM virtual INNER JOIN prior …критерий соединения…
)
SELECT … FROM prior…

Ну вот что-то такое. Оказалось, конструкция WITH является барьером для постгреского оптимизатора — он не может заглянуть за него и планы строит отдельно — для запроса внутри WITH и того, что снаружи. Разумеет это дико неэффективно. Например, если внутри соединение со слабыми ограничениями (сильные наложатся снаружи — в «рекурсивном» запросе), то внутренний запрос может выбрать гигантское количество данных, что у нас и происходило в нескольких местах.

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

Я перебрал несколько решений, которые пришли мне в голову и остановился на следующем: в данный момент билдер создаёт временную «вьюху» с определённым именем, которое указывается в качестве источника запроса. Выглядит это так:

CREATE OR REPLACE TEMP VIEW некоеимя AS (
    -- тут у нас подзапрос
);
WITH RECURSIVE prior AS (
    SELECT … FROM некоеимя WHERE …некий критерий…
    UNION ALL
    SELECT … FROM некоеимя INNER JOIN prior …критерий соединения…
)
SELECT … FROM prior…

Выходит достаточно эффективно, но смущают две вещи: во-первых, всё это выглядит очень неизящно, во-вторых, для «вьюхи» нельзя подготовить план — все параметры приходится вставлять в запрос, так что максимальная производительность, естественно, не достигается.

Одно из отличий «Постгреса» от «Оракла» — подготовленные планы запросов лежат не в разделяемом ресурсе, а в неком хранилище в рамках одного соединения. Программист, в рамках каждого соединения, должен подготовить их оператором PREPARE, в который передаётся выбранный идентификатор запроса и сам запрос, а потом выполнить его при помощи оператора EXECUTE, используя в качестве параметра переданный при подготовке идентификатор и значения параметров.

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

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

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

Теме работы «Постргеса» с ПХП удивительно мало посвящено в руководстве к языку, так что пришлось произвести ряд самостоятельных исследований. Основной вопрос, на который я хотел для себя ответить — есть ли какая-то долгосрочная польза от подготовленных запросов в разрезе того, что обычная модель работы с ПХП — полная смерть скрипта после обработки запроса от клиента.

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

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

Код всего описанного выглядит вот так, я его упростил и переписал процедурно:

// открываем постоянное соединение с базой
$con = pg_pconnect($connection_string);

// загружаем подготовленные запросы
$res = pg_query("SELECT name FROM pg_prepared_statements");
$prepared = array_fill_keys(pg_fetch_all_columns($res), true);
pg_free_result($res);

// хеш от запроса, tiger — быстрый алгоритм, я люблю его использовать
$hash = base64_encode(hash('tiger160,3', $sql, true));

// смотрим — был ли уже подготовлен такой запрос
if (!isset($prepared[$hash])) {
    $prepared[$hash] = true;
    pg_prepare($con, $hash, $sql);
}

// выполняем запрос
$res = pg_execute($con, $hash, $params);

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

Надо заметить, что модуль «Постгреса» в ПХП в каждое соединение, перед его передачей в приложение, посылает команду RESET ALL, но она, насколько я понял из руководства, лишь сбрасывает различные конфигурационные переметры времени выполнения на значения по-умолчанию и не трогает транзакции и всё остальное (более того, она сама транзакционна).

В комментариях пообещал поописывать свой опыт перевода продукта с «Оракла» на «Постгрес», особенно в особенностях, которым очень мало уделено внимания на просторах интернета. Начну с простого, зато сразу целой пачкой. Примеры я, естественно, упростил, чтобы максимально выпукло продемонстрировать различия.

У нас довольно много генерируемых простых запросов, иногда они генерировались не очень аккуратно. «Оракл» такое прощает, а «Постгрес» — нет. Запрос, где одни и те же таблицы соединяются дважды «Постгресу» не по вкусу:

SELECT * FROM test INNER JOIN test1 USING(n) INNER JOIN test USING(n);

ERROR:  table name "test" specified more than once

«Постгрес» очень трепетно относится к тому, чтобы у подзапроса был алиас, даже если он потом нигде не используется:

SELECT * FROM (SELECT * FROM test);

ERROR:  subquery in FROM must have an alias
СТРОКА 1: SELECT * FROM (SELECT * FROM test);
                        ^
ПОДСКАЗКА:  For example, FROM (SELECT ...) [AS] foo.

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

UPDATE test t SET t.n=1;

ERROR:  column "t" of relation "test" does not exist
СТРОКА 1: UPDATE test t SET t.n=1;

Если «Оракл» спокойно относится к двум и более таблицам с одним алиасом, то «Постгресу» от этого плохеет. Жаль, иногда удобно объединить несколько таблиц алиасом прямо на месте и работать дальше с ним, как с единой таблицой.

SELECT t.n FROM test t, another_test t;

ERROR:  table name "t" specified more than once

В «Оракле» ключевое слово FROM не обязательно в запросах на удаление. У нас было несколько запросов без него.

DELETE test;

ERROR:  syntax error at or near "test"
СТРОКА 1: DELETE test;

В следующий раз буду описывать более комплексные примеры.

У нас идёт замена в наших продуктах сильно платного «Оракла» (это база данных) на бесплатный «Постргес». Несмотря на ударные темпы, продолжаться это ещё будет долго. Продуктов много, несмотря на то, что начали мы ещё в прошлом году, весь этот год будем жить сразу на двух базах.

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

Например:

CREATE UNIQUE INDEX dn_part_num_org_n_cat ON document_n(/*/**/
CASE WHEN d_deleted = 0 AND num IS NULL AND n=0 AND category=0 THEN id END,
CASE WHEN d_deleted = 0 AND num IS NULL AND n=0 AND category=0 THEN org_id END);
--*/ id, org_id) WHERE d_deleted=0 AND num IS NULL AND n=0 AND category = 0;

Часть с /*/**/ и до —*/ видит только «Оракл» — для него это выглядит так: комментарий открывается, сразу закрывается, идёт код, который он и воспринимает, а последняя строка закоментирована при помощи двух минусов — это стандартный коментарий в эскуэле.

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