WAL

Никакой магии нет, есть вызов fsync при записи журнала с операциями на диск. Скорость досигается за счёт того, что последовательная потоковая (а не в случайное место на диске как в случае update) выполняется быстрее. Ну и затем фиксируется fsync, после чего можно уже считать транзакцию закоммиченной. Есть асинхронный режим, в котором пользователя обманывают - говорят что всё закоммитили, но где-то полсекунды всё будет в памяти, а затем walwriter запишет всё скопом. Оптимизация ценой надёжности. fsync работает пофайлово, т.е. вызов commit() в транзакции не вызовет синхронизацию ВООБЩЕ ВСЕГО БУФЕРА ВСЕХ ФАЙЛОВЫХ СИСТЕМ В СИНХРОННОМ РЕЖИМЕ и транзакция не зависнет в хлам. Но тут есть одно но - ОС, конечно скажет, fsync выполнен, т.к. он записывает буфер из памяти на диск. А у диска есть ещё свой буфер записи и при длительной потере питания он не факт что сохранится.

Update 2023.02

Вместо курса взялся читать книгу. 

Аномалии и уровни изоляции

Собрался таки почитать ещё об аномалиях и уровнях изоляции в #PostgreSQL 15.

Разработка без защиты от missed update похожа на ситуацию, когда пишешь пост с мобилы и ожидаешь что приложение крашнется. До чего ж мы дожили – нельзя уже доверять полю ввода текста, страшно, что оно пропадёт.

Оффтоп о редактировании Markdown И ладно бы в вебе или на мобилке, так ведь даже модным #markdown-редакторам в #flatpak доверять не приходится. Но у них хоть сохранение есть на относительно надёжное хранилище по хоткею. Тащить ноут в кресло к читалке неудобно, но заметки в онгоуинге делать хочется. Но на мобиле печатать неудобно, планшет просто отбитый, а сидеть на стуле за ноутом ещё неудобнее. Мне нравится редактор Apostrophe, но я не люблю начинать писать статьи не из консоли. Этот блог - git-репа, в ней есть специальный примитивный баш-скрипт, который создаёт файл из шаблона, подставляет нужную дату, название итд. И я не понимаю как понять какой командой запускать Apostrophe, сидящий во флатпаке, из консоли, пробовал "отревёрсить" найдя команду в ps aux | grep, но не помогло, "показать подробности" с док-панели ведёт в магазин приложений, а не показывает команду, которой он запускается, читать документацию впадлу, а ползать во всплывашке по файлового менеджера - больно.

Сериализация

Я таки понял что такое ошибки сериализации спустя много лет после первой встречи с ними, что от них никуда не деться, если денежки считаешь, а всё что остаётся при её получении – делать повторную попытку. Вспомнил ситуацию “и хочется и колется” – SERIALIZABLE, позволял не изъёбываться с защитой от аномалий на уровне приложения, но делал БД бутылочным горлышком, выжирая ресурсы сервера. Surprise motherfucker, но на репликах SERIALIZABLE не работает. Понятно почему, но не понятно, что делать - хочешь консистентно считать бабки – теряй доступность, навернулся мастер - иди чини. Ох уж старая добрая CAP.

Мне нравится, что в PostgreSQL уровень изоляции можно задать на уровне отдельной транзакции - маловажную в вопросе консистентности статистику собирать можно на уровне READ COMMITTED. В Firebird это вроде только глобально рулилось. Странно, но не понимаю, почему BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFFERABLE нельзя подразумевать, анализируя текст одиночного SQL-запроса, автоматически обёртываемого в транзакцию, на клиенте, до отправки запроса.

Ошибки

Нравится как PostgreSQL отдаёт при ошибке ERROR, DETAIL и HINT. Я недавно похожее поведение делал, чтобы в интерфейсе ошибки писали:

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

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

Слои абстракции

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

Смена СУБД весёлая тема, за десять лет помню только как с MySQL на PostgreSQL переезжали, но причин не помню - мб MySQL взяли чисто для прототипирования, а для тестов SQLite использовали, чтобы не тащить docker и не возиться с конфигурированием сетевого доступа. Программисты абстрагируются от СУБД, чтобы переезжать по щелчку, но переезды происходят редко, а неудобства и без абстракций на уровне кода хватает.

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

Vacuum

Сильно хихикаю с формулировки – 1 are dead but not yet removable. Тяжело от трупов избавляться, даже если это БД.

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

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

=> VACUUM VERBOSE tfreeze;
INFO: aggressively vacuuming "internals.public.tfreeze"

А мы с PostgreSQL похожи…

По мере чтения происходит disenchantment – никакой магии и волшебства в постгресе нет. Скорее много костылей для того, чтобы сглаживать пиквые нагрузки за счёт откладывания на потом. И местами костыли, чтобы не делать лишней работы. Теперь мне не так страшно такие костыли и на работе мутить (хотя я и раньше не боялся).

Сейчас читаю про заморозку, которая помечает строки созданные транзакцией, ушедшей за горизонт событий особым образом так, чтобы их номер (игнорировался и мог быть переиспользован) всегда считался в прошлом, чтобы на номер транзакции можно было полагаться при сравнении транзакций, даже при условии переполнения 32-битного счётчика транзакций. 64-битный юзать стрёмно так как заголовок верси строки в базе содержит 2 версии (min, max) лишних 8 байт выйдет.

Читаю про аварийную заморозку. Там упоминают что есть кейсы, когда VACUUM вырубают. Но зачем? В каком кейсе это было бы актуально? При высокой нагрузке на чтение из readonly БД со статичными данными и в очень ограниченном пространстве памяти? Зачем?

Многоверсионность

PostgreSQL во многом похож на прокрастинатора - многоверсионность можно описать “пофигу, просто запишем строку целиком ещё раз, а потом кто-нибудь (возможно я сам) разберётся”. Если простыми словами, подводные камни в следующем - UPDATE под капотом работает как DELETE + INSERT, а SELECT может вызвать дисковую запись. Первое пытаются регулировать внутристраничной очисткой (см. fillfactor).

Буферный кэш

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

Вытеснение из буфера через clock sweep прикольно сделали, нраица, звучит похоже на мобилизацию ЕВПОЧЯ.

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

Попутные мысли

Интересно, насколько сложно написать расширение поверх PostgreSQL, которое будет на 53 порту слушать и на DNS запросы отвечать. По сути DNS-сервера сводятся либо к построению собственной базы данных в памяти, либо подключению внешней, опционально проксируя часть запросов к другим серверам. И я подумал, а что если начать с другой стороны? Так не придётся гонять данные туда-сюда.

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

Репликация

Не из книги, но тема та же. Глянул кусочек highload.guide, выцепил хорошую выжимку про классификацию репликаций:

  • логическая - работает с кортежами; пример - row based binary log в MySQL; бутылочное горлышко - процессор slave.
  • физическая - работает со страницами; примеры - pg_wal, innodb undo-redo; бутылочное горлышко - диск (не совсем понял master’а или slave’а, не совсем понял, а как же буферы; возможно дело в большей степени касается MySQL с несколькими журналами одновременно)
  • statement based - сплошная боль, есть места где можно получить неконсистентные данные между master и slave, но в случае условной бигдаты - очень низкие накладные расходы на передачу данных по сети, большой update на всю таблицу прилетит практически одним запросом; разве что выполнять его придётся заново на slave, из-за этого может быть длинный лаг репликации.

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

В PostgreSQL есть Logical Log Streaming Replication, который позволяет не миррорить часть таблиц на slave. Как это применять я не придумал. В теории можно не хранить на слейвах для актуальных данных архивные данные, что позволит немного сэкономить на дисковом пространстве, особенно если slave’ов много.

Контрольные точки и фоновая запись

2023.06.12. За полгода я прочитал треть книжки. За выходные на природе прочитал 30 страниц. Медленно.

Контрольная точка - это две метки в wal с началом и концом. В начале фиксируется список грязных буферов, в конце - все зафиксированные на момент начала грязные буферы сдамплены на диск. По контрольной точке восстановливается согласованное состояние на момент её начала при восстановлении из резервных копий. Файлы wal, кроме предыдущей завершённой и текущей контрольной точки бесполезны. При достижении max_wal_size форсируется внеплановая контрольная точка. Много внеплановых контрольных точек – плохо. С ними вообще любопытно: делаешь часто - лишние накладные расходы, плохо, делаешь редко – плохо, возрастает время восстановления, растёт объём хранимых wal-файлов. Это как менеджер, спрашивающий как дела по задаче. Подстраивать интервалы для checkpointer’а нужно по обратной связи из мониторинга, учитывая профиль нагрузки на систему. Такое себе, я ожидал больше динамики и автоматизации.

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

Журнал можно записывать синхронно и асинхронно. Синхронный режим это медленная жопа, а много OLTP транзакций её насилуют. Поэтому для синхронного режима придумали батчинг записи коммитов в журнал, по дефолту он выключен, регулируется опцией commit_delay. Нравится метафора из книги с удерживаемой кнопкой дверью лифта, когда первая транзакция, которая готова закоммититься, ждёт немного, вдруг с ней за компанию ещё одна транзакция влетит записываться на диск. Асинхронный режим допустим, если вы готовы потерять пару сотен последних транзакций, даже если нужно будет повторить их в ручном режиме или компенсировать убытки из своего кармана. Исправный ИБП, сигнал о потере питания от которого вызывает штатное завершение работы системы, снижает вероятность такой ситуации раз в десять.

У журнала несколько уровней записи - minimal, replica, logical. Как я понял, logical это для master, replica это для slave, minimal это для fucking slaves. Про logical надо бы подробнее почитать, в книжке он мимоходом упоминается. Синхронная репликация журнала, когда коммит записи на мастере означает гарантию чтения этой записи на реплике, звучит как головная боль для администратора БД и тормоза. Мастер-мастер репликация звучит ещё более сложной. Асинхронная репликация журнала без гарантии чтения с реплики выглядит гораздо проще, кажется большинству систем этого за глаза хватать должно.

Видел в highload.guide милую схемку с:

  • 1 master,
  • 1-2 slaves, выделенных для снятия резервных копий,
  • N-slaves для readonly OLAP-нагрузки.

SQL

Where по нескольким параметрам

Заметил в книжке пример о том, что можно делать хитрый WHERE по сочетанию параметров через VALUES и скобочки.

WHERE (x, y) IN (VALUES ('a', 'b'), ('c', 'd'))

Интересно насколько эффективно оно работает в сравнении с пачкой OR?

  • Длина собранного запроса короче, больше шансы уложиться в один TCP PUSH/ACK пакет при средних размерах таких списков, это понятно.
  • Читаемость - хз.
  • А с точки зрения вычислений хз вдвойне. Композитным индексам, наверное, пофигу. Если COALESCE для обыгрывания NULL-значений делать, наверное, вообще вся работа индексов по одному месту пойдёт. В 15й версии для этого даже специальную опцию сделали, но, уверен, под капотом там тот же COALESCE будет. А на работе 14, так что я даже не пробовал. Это такая скучная херня, возиться с работающими SQL’никами ради 5-10% выигрыша производительности, но так успокаивает и умиротворяет, радует, когда в итоге получается, когда нет срочных задач, само собой.

Методы доступа

Табличный доступ

Увидел что начиная с 12 версии можно пилить свои методы табличного доступа и загорелся идеей fine tuned custom приколов заточенных под специфику данных. Теоретически можно накостылить эдакий clickhouse внутри постгреса. Вот на старом проекте одну задачу можно было бы изящно утащить в PostgreSQL, сделать бешено быстрый парсинг и CDC. Вышло бы охренительно, очень смешно и не совсем понятно.

Индексный доступ

Обнаружил что #индексы не могут использоваться в случае если в запросе к индексируемому полю применяется функция. Но и для этого есть обходной путь — можно индекс строить по этой же функции, а не по самому столбцу. При добавлении строки — правильно, будет вычисляться эта функция и результат запишется в индекс. Но при поиске, оно, скорее всего будет перепроверяться (re-check cond в explain). Ну, хоть не всю таблицу сканировать, конечно, но может это вычисление как-то можно избежать.

Сортировка по индексу

Btree индексы можно и для сортировки за O(n) использовать без буферизации всех строк где-либо. Но с неопределёнными значениями и порядком сортировки есть нюанс - положение их в индексе задаётся при создании и, если запрашиваемое положение отличается, то индекс не может быть использован.

Вообще странно, что не воткнули костыль для простейших случаев, если nulls не там, просто читаем индекс с другого конца, до тех пор, пока не наткнёмся на не #null, после чего начнём читать индекс так, как изначально и планировалось, до тех пор, пока не наткнёмся на null, который послужит нам эдаким EOF. Сложность чтения бы сохранилась линейной, всего пару переменных и ифников для стейт-контрола добавить. Не думаю, что это был бы значимый оверхед.

Временные метки для CDC в базовых моделях ORM

Про ширину таблиц и индексы. Я вот всё ещё не могу для себя сформулировать, насколько плохим может оказаться влияние created_at и modified_at колнок, которые частенько включают в базовую модель ORM. Обычно весь их смысл - служебный, для переливки в другие БД, витрины итд. Но чтобы по этим колонкам выгребать данные эффективно - нужны идексы по ним. Хоп - и вставка подорожала. В приложении семантику на них завязать тоже редко получается — обычно важна дата изменения конкретного столбца.

BRIN

Давно были интересны, думал что они уместны только для мониторинга с временными рядами, но ведь многие системы имеют метки для CDC, которые гораздо легче пылесосить по такому индексу. Места толком не занимает, дешёвый. Да и с другой стороны, если BI строить, там им тоже самое место. Опять же на created_at вешать смысл имеет для объектов, а вот на updated_at чот хз. Там ведь суть в том, что он хорош, когда ключ индексирования упорядочен и не меняется.

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

В BRIN можно присунуть блум-фильтры, как я понимаю, по фильтру на (диапа)зону и это просто идеальное для них применение. Уже хочется делать замерчик и достать pg_bench’ик сладкий pg_bench’ик.

Неконсистентный нейминг обработчиков

SELECT amname, amhandler FROM pg_am WHERE amtype = 'i';
amname amhandler
btree bthandler
hash hashhandler
gist gisthandler
gin ginhandler
spgist spghandler
brin brinhandler

На этом книжку можно закрывать и в PostgreSQL дальше не копаться. У меня ОКР? Ну вот треснула бы жопа сделать btreehandler и spgisthandler?

GiST, SP-GiST

NULL

Неопределенные значения допускаются GiST-индексом, но обрабатываются не слишком эффективно. Считается, что неопределенное значение не расширяет ограничивающий прямоугольник; поэтому при вставке такие значения попадают в разные поддеревья индекса случайным образом, и их приходится искать во всем дереве.

Тоже странное. Можно же одну выделенную ветку с nulls сделать было.

UPD: Иронично, но SP-GIST делает не просто выделенную ветку под них, а прям выделенное дерево со своим корнем.

CIDR, inet type

Есть встроенный тип inet (предположу, что есть и inet6), который вроде как имеет поддержку индексов. А ещё поверх GiST есть префиксные деревья, это выходит radix можно замутить. Интересно, насколько быстро эта штука работает и сколько памяти жрёт, если сравнивать с aggregate, net::CIDR в Perl и моей реализацией диффалки-аггрегатора на Go. Или пора эти вьетнамские флэшбэки в прошлом оставить уже…

Шардинг

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

GIN

GIN-индексы странная штука. Для полнотекстового поиска вроде норм, но такая прям куча нюансов. Вставка может быть медленной, но может ускоряться за счёт fastupdate (эдакий несортированный буфер-входящик), который сделает медленнее поиск (надо глянуть в два места) и добавит непредсказуемую задержку при вставке (при переполнении входящика его надо вмержить в основное дерево). Схемы чот пролистал по диагонали, почти ничего не понял. Прикольно то что из коробки есть лексемодробилка, может я свой epythets зря вообще поверх sqlite сделал.

Господи, какое же неэффективное тупое говно этот ваш GIN по jsonb!

Рассмотрим запрос по условию route @> ‘{“days_of_week”: [6]}’, кото- рое отбирает документы JSON, содержащие указанный путь (то есть рейсы, совершаемые по субботам).

Опорная функция выделяет из поискового запроса, представленного значением JSON, ключи поиска: «days_of_week» и «6». Эти ключи ищутся в дереве элементов, и документы, содержащие по крайней мере один ключ, проверяются функцией согласованности. Для стратегии «содержит» функция требует наличия всех ключей из поискового запроса, но результаты все равно должны перепроверяться по таблице, поскольку с точки зрения индекса указанный путь соответствует, например, документу {“days_of_week”: [2], “foo”: [6]}.

Это jsonb_ops такой всратый, jsonb_path_ops вроде логичнее. Hstore вот интересно звучит, но про него ни слова в книге.

Выводы

Почитал про методы доступа, которые Index Scan, Index Only Scan, Bitmap Scan, Seq Scan. Общий вывод, который делаю - на этапе проектирования системы крайне желательно иметь представление какого рода запросы в ней будут преобладать и оптимизировать систему под них.

К примеру OLAP-нагрузка с комбинированными запросами может перекашиваться в сторону Bitmap Scan, использующий несколько независимых индексов на отдельные колонки — такой системе нужно мало воркеров, но с большим work_mem, это позволит избегать загрубления битмапов и лишнего recheck cond, что сократит I/O.

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

Если же таблица небольшая (10-20 строк, 3-5 столбцов, например, но точное число сильно зависит от, надо бы поиграться), редко меняется, но часто читается, может оказаться выгодным вообще не создавать для неё индексов – при частом чтении, она будет лежать целиком в буферном кэше, не будет вытесняться и расходы на Seq Scan будут ничтожны.

Статистика

Про статистику.

Для оценки селективности и кардинальности используется статистика. Она состоит из:

  • n_distinct - число уникальных значений,
  • MCV + MCF - самых популярных значений и их частоты (при больших разбросах там аж гистограмы подрубаются с бакетами),
  • доли null значений, средний размер полей вариативной длины,
  • корреляция — насколько совпадает физическое расположение строк в таблице с их порядком выдачи из индекса, чем меньше, тем хуже, но в случае с SSD не ясно, насколько хуже, типа проблема ж не в рандомном чтении без префетчей, а в (перепро)чтении лишних страниц, как я понял.

Статистика - бро твоего планировщика и позволяет автоматически дрочить диск оптимальным способом, если она, конечно, есть и правдива. Статистика собирается полуслучайным образом, типа берём рандомные то ли 300, то ли 30 000 страниц, берём из них рандомные 30 000 (?) строк и АНАЛИЗИРУЕМ. Вакуум такой стоит, аж буферные кэши вытесняются.

Если честно, я порой охреневаю, какой это ебовый овер(?)килл и сколько всего порождает простая запись в табличку. И всё это нужно ведь прочитать, обсчитать, проанализировать перед непосредственным запуском читающего запроса. Вроде у планировщика есть кэш запросов, но ведь в него ещё и попадать надо. А ещё к нему надо обратиться перед планированием, вдруг есть чо, а если нет - это ж ещё одни накладные расходы. Зато SQL - простой и декларативный язык, невероятно ведь круто описать что ты хочешь получить, не заморачиваясь вопросом “как”.

Кэширование

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

Join’ы

Узнал, что (NOT) EXISTS вырождаются в anti/semi-join, типа LEFT JOIN ... WHERE key IS (NOT) NULL и при наличии индекса на key, всё выглядит не так уж страшно.

Hash Join

Хэш-таблицы с шириной строго являющейся степенью двойки - это довольно ловкий ход, позволяющий вместо остатка от деления (дорого) просто дёргать младшие N-битов хэша (дёшево).

Merge Join и алгосики/стандарты/велосипеды

Дочитал до реализации Merge Join (странное название, отдаёт тавтологией, но пусть). Кажется, вот оно, место, где понимание способов сортировки двух массивов имеет хоть какой-то приближённый смысл в этой нашей айтишечке! Одна (ну почти) реализация на способ (нет велосипедов), система даже сама оптимальный способ выберет (спасибо планировщику).

Но тогда непонятно, зачем всё это знать рядовому разработчику, если это забота DBA? Рядовому достаточно знать как навесить индекс хотя бы на одно используемое в запросе поле, можно даже за тип индекса не заморачиваться, дефолтный btree 99% потребностей закрывает и, обычно, ничем не хуже hash, + знать что много индексов замедляют вставку. Всё!

Чем мне нравится PostgreSQL - так это тем, что он имеет стандартизированные базовые блоки для операций над данными. Никаких велосипедов! А я их видел много и выгоды от этого кастома было мало. Но иногда была, когда использовалась выверенная бенчмарками хэш-функция для специфической структуры данных для большого паттерн-матчинга с кучей доп. правил, которая при росте объёмов выигрывала у компилированной гигарегулярки/схожей реализации средствами PostgreSQL).

Enum

И снова к ширине строк таблиц. У меня в обоих проектах есть статусы некоторых объектов. В целом это короткие VARCHAR, обычно до 16 символов длиной (хотя разок уткнулись, семантично ну никак не могли название придумать короче 18 символов). В python это, само собой, #Enum. Понятное дело, хочется там, где можно избавиться от TOAST’а, шоб всё было быстро и весело, все таблицы помещались в одну страницу итд, и вкатить enum’ы и в базе, 4 байта вместо 16 звучит привлекательно, а на 20 000 000 строк ещё привлекательнее, минус 76мб из веса таблицы, да ещё и дополнительная защита от дурака.

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

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

Бля, я чувствую как во мне просыпается техлид lingualeo.

ORM

Временные метки в базовых моделях, NOW(), clock source в Linux

Задумался, при массовой вставке строк с использованием моделей #ORM явно стоит перепроверить, какой #clocksource стоит в системе (и махнуть его на tsm, если не нужна сверхточность). Беда только в том, что в случае managed postgres, прямого доступа к БД нет и проверить это несколько сложновато (хотя, по-любому в psql есть встроенный remote cat и доступ к файловой системе), а потом ещё и с командой, которая постгрес крутит этот вопрос обкашливать, потом выяснится, что clocksource глобальный на всех, что кому-то эта точность нужна и вообще чего суету навёл, нормально ж и стабильно живём.

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

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

Когда кажется неуместным

Кажется нашёл место, где ORM могут быть неудобными в использовании — полнотекстовый и географический поиск. В целом можно покрыть это всё плагинами. Но как в язык программирования изящно перетаскивать операторы типа @@ - не ясно. Неизящно-то понятно, свести оператор к функции — fn.Tsmatch(ts_vector, ts_query). Но прозрачность теряется, а её место занимает двойное обучение разработчиков.

Ты уже всё понял как написать на SQL, но ещё полдня нужно потратить, чтобы ORM делал тоже самое.

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

Попробовал взвесить свои потребности в ORM:

  • 40% - query builder, который в зависимости от условий строит разные запросы;
  • 40% - буквально object mapper, который к строчке из БД привяжет методы объекта (и сдампит объект в БД);
  • 20% - управление пулом соединений к БД.

Если ни первое ни второе не нужно, а производительность жесть как нужна, ORM таки можно выбросить, а пул коннектов свой на коленке накостылить.

Применимость знаний из книги на практике

Задумался, а как мне это всё в работе поможет и чот хз. Пока самое эффективное, что я делал, было избавление от INSERT ... ON CONFLICT DO NOTHING + UPDATE (разбивка по группам, почти равномерная по модулю от autoincrement integer id) всего что не заигнорилось. Заменил это на вставку чанками по 1000 строк с чередованием этих групп и последующим выравниванием недавно вставленного с помощью хитрожопой математики.

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

  • Дисковое I/O в итоге сократилось на 40%.
  • Помимо этого оно ещё и сгладилось (благодаря вставке чанками) и отсутствию гигаапдейта.
  • Из-за этого сократился лаг репликации.

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

Несортировано/вопросы без ответа.

  • Параметры типа max_mem выставляются на соединение. Соединений может быть много. Выходит нужно считать max_mem_server = max_mem * max_connections?
  • Есть не только кастомные функции (UDF), но и кастомные типы данных и кастомные индексы. Наверное прикольно, например, для IP адресов, хранимых в строке для челокоудобности, индекс на CIDR вкорячить.
  • Как в PostgreSQL выглядит инициализация дополнительной реплики и какую нагрузку это создаёт на master?
  • Возможна ли иерархическая репликация для распределения сетевой нагрузки на Master?
  • Как себя в таком случае чувствуют промежуточные полумастер-реплики?
  • Заметил, что планировщик учитывает только CPU и объём дискового чтения/записи. А вот выделение памяти он не учитывает. Это фишечка встроенного мемори-менеджера, который заранее выделяет пул, а дальше играется с realloc’ами и прочим? Или нет выделения памяти кроме чтения с диска, а в его оценку память уже заложена?
  • DTM - чот полистал доклады и прочее (в книге пока не добрался). Это что выходит, люди с распределёнными транзакциями в пределах одной БД одного сервиса ебутся? Я-то думал это гемор межсервисного взаимодействия, затыкаемый служебными колонками с FSM-статусами и таймерами для реентерабельности, а там всего-то в разных шардах надо две строчки сперва заблокировать, обновить и сделать видимыми одновременно.
  • Как физически устроены jsonb поля. Интересно как с ними работают индексы, как происходит чтение, апдейты и запросы поддеревьев. UPD: Долистал до 616 страницы, там ак раз оно.
  • uuid_minmax_ops – господи, зачем?!