Комментарии 12
Спасибо за статью.
Подскажите, в чём плюсы упорядоченных таблиц, только ли в дешёвом bitmap heap scan при чтении диапазонов? Если так, то выглядит логично попробовать
1) таблицу секционировать по диапазонам
2) при необходимости и возможности - все холодные диапазоны упорядочивать через CLUSTER.
Что касается INCLUDE и других широких многоколоночных индексов: надо иметь в виду, что это простой и надёжный путь не делать HOT UPDATE с большим усилением записи и хорошим распуханием индексов.
Добрый вечер!
Большое Вам спасибо за комментарий и за то, что читаете блог компании!
По поводу первого вопроса. Дело вот в чём:
1) Поскольку строки будут физически упорядочены на основе ключевых полей индекса, то планировщик выберет Index Scan вместо Bitmap Index Scan + Btimap Heap Scan
Мне как-то доводилось проверять скорость работы в процессе одного расчёта. И там получалось, что Index Scan + Cluster работал быстрее. Т.е, строки не только упорядочены по номерам блоков, но ещё и по ключам индекса. Это также важно на случай, если у Вас после сканирования таблицы надо сделать сортировку по тем же полям индекса.
В случае Bitmap Index Scan строки упорядочиваются по номерам блоков, чтобы не читать одни и те же блоки таблицы много раз. Но в этом случае полученная выборка должна быть явно отсортирована, что может быть дорогой операцией. А в случае Index Scan + Cluster такого не будет.
2) Вот как раз так у одного клиента и было сделано, как Вы написали. Данные разделили на диапазоны дат, самые холодные секции упорядочивали с помощью CLUSTER
По поводу второго вопроса:
Да, разбиение (split) страниц индексов с последующим распределением строк между ними никто не отменял. Ровно как и затраты на синхронизацию строк индексов и таблицы.
Чтобы HOT работал нормально, надо учитывать не только это, но и:
1) Порядок столбцов таблицы, что влияет на ширину строки с учётом выравнивания.
2) Понять сколько на одно добавление строки приходит обновлений.
3) На основе первых двух пунктов определить процент резервирования fillfactor, поскольку он считается от размера блока. И на основе этого уже есть понимание, сколько строк поместится в один блок.
4) И не исключено, что fillfactor придётся регулировать. Да, мы резервируем место под обновления, но таблица тоже увеличивается в размерах.
Как было написано, INCLUDE не является заменой IOT-таблицы, хотя бы потому, что это отдельная от таблицы сущность со своими блоками и сегментами. Но иногда этот индекс может пригодиться.
Include - очень крутая штука, возникшая благодаря вашей компании. Вполне вероятно, она может стать в перспективе IOT, хотя при нынешнем устройстве Postgres это маловероятно. Очень серьезное препятствие вижу в самой оценке, когда возможен index only scan и зачастую оптимизатор его отвергает из-за visibility map. В противовес include другой подход с узким уникальным индексом для ключа не требует index only scan и выглядит выигрышно при интенсивных update за счёт HOT, если вообще не обновлять столбцы в индексах.
Что про bitmap scan, то он выглядит быстрее, чем index scan, если recheck после сканирования не фильтрует много строк. Наблюдал, что bitmap scan чаще бывает, если в pg_stats хорошая correlation у столбца, по которому запрашивается поиск по диапазону. Кроме столбцов, которые сами растут со временем (sequence, now), это можно сделать, если заранее выполнить cluster. Есть тут подвох, что для tuple из 2 столбцов(напр., client_id, create_time) оценка correlation не выполняется и запрос с условием client_id=<constant> and create_time between... не будет использовать bitmap scan даже если заранее сделать cluster по индексу из этих двух столбцов. Хотя тут это самый эффективный метод.
Поэтому в тексте статьи написано, что в некоторых случаях INCLUDE-индекс может послужить заменой. Кроме того, надо понимать, что Index Only Scan это не совсем сканирование только по индексу. Даже если его планировщик выберет, то может быть ситуация, при которой придётся пойти в часть блоков из-за карты видимости.
Узкий уникальный индекс это отдельная история. В частности, если у Вас уникальный индекс состоит из одного столбца, то селективность считается немного по-другому. Более того, статистика по столбцу может быть проигнорирована, про это даже есть комментарий в коде СУБД.
Что касается bitmap index scan, то это зависит от того, сколько памяти выделяется в том числе и под карту. Потому что может быть ситуация, когда проверяются все строки блока, поскольку в карте сказано, что в нём находится интересующая нас строка. И это, конечно, вообще не быстро.
В предыдущем комментарии просто было сказано, что поскольку при CLUSTER строки будут упорядочены физически и по ключам индекса, то особо смысла нет делать Bitmap Heap Scan, будет просто Index Scan, что может позволить сэкономить на сортировках.
Корреляция это статистика. То, что Вы описали, это учёт корреляции по нескольким столбцам. Частично напоминает историю с разработкой расширенной статистики, когда всё хорошо считалось по одному столбцу, а по нескольким, мягко говоря, не очень.
Пока с корреляцией ничего такого не реализовали.
"Многоуровневое локальное секционирование" - Вы имеете в виду Subpartitioning?
Но он поддерживается PG.
Отсутствие поддержки глобальных индексов - это уже другой недостаток PostgreSQL.
Аналог DataGuard есть в PG: это обычный физический стенбай, доступный на чтение.
Вы, видимо, имеете в виду DataGuard Observer и Global Data Services - автоматическую активацию стенбай-БД и переключение на него приложений.
Кстати, последнее можно сделать без GDS - на стороне клиента с помощью Transparent Application Failover (в дескрипторе соединения на клиенте описываются подключения к примари и ее репликам).
Добрый день! Спасибо за комментарий!
Да, многоуровневое локальное секционирование поддерживается в СУБД PostgreSQL
А вот subpartition template нет. В статье приведён пример, показывающий что это такое.
Что касается DataGuard, наверное, стоило явно написать, что в СУБД PostgreSQL возможно использовать физическую репликацию, ведомый сервер выступает в качестве горячего резерва.
А далее в статье просто указываются технологии, позволяющие осуществить автоматическое переключение с ведущего на ведомый сервер.
Единственный момент – Patroni ни в коем случае не является аналогом DataGuard.
Аналог DataGuard – Postgres Streaming Replication
А Patroni – это аналог DataGuard Broker. То есть не репликация, а автоматизация переключения.
Спасибо!
С нетерпением ждем следующую статью про конвертацию кода.
Ума не приложу, что делать с очередями Advanced Queuing, обьектными типами PL/SQL, глобальными контекстами и цепочками джобов (scheduler chains), а также динамическим SQL 4-ого типа (dbms_sql). - Как эти 600 тыс. строк переписать на pg/sql ...
Расскажите, пожалуйста! 😀
Отличная серия статей, спасибо, @ppetrov91!
В разделе про фэйловер у вас не упомянут "pg_auto_failover" от Microsoft/Citus. Я не первый раз обращаю внимание на то, что это решение незаслуженно обходится стороной. Для этого есть какая-то причина или его просто забыли?
Перенос данных из Oracle в PostgreSQL: секционирование, временные таблицы и инструменты