• Postgres №42

  Apr 9, 2026 postgres, sql

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

  Среди прочего рассмотрим авторизацию. До версии 15 алгоритм по умолчанию был md5. Он довольно прост: нужно сцепить пользователя и пароль, посчитать md5, приклеить соль, взять md5 еще раз, а затем привести к hex-строке:

  message = hex(md5(md5(user+password) + salt))
  

  Эта авторизация занимает одно сообщение сервера и одно клиента. Вычисления клиента при этом простые: два md5-хэша.

  С версии 15 авторизация по умолчанию называется scram. Она происходит в несколько шагов, примерно так:

  • сервер говорит: начинаем авторизацию scram, доступны варианты sha-256 и sha-256-plus (о них позже);
  • клиент загадывает случайный массив байтов и отправляет серверу;
  • сервер возвращает особую крипту и число шагов (по умолчанию 4096);
  • клиент выполняет цикл из 4096 итераций, где на пароль накладывается XOR-ом байтовый массив и вычисляется отпечаток hmac sha265; эта крипта отправляется серверу;
  • сервер отправляет финальную сигнатуру. Клиент проверяет ее и если что не так, кидает исключение.

  Вся эта котовасия нужна вот зачем:

  • клиент считает не пару md5-хэшей, а множество XOR- и hmac-операций. Это крайне осложняет брутфорс;
  • сделать реверс 4096 операций — задача нереальная в отличие от md5;
  • scram допускает защиту от человека посередине (разберем чуть позже).

  Итого, чтобы авторизоваться, клиент вычисляет хэши в цикле. Это гораздо дольше обычного md5. Поэтому со scram новое соединение обходится еще дольше, чем раньше.

  Что касается защиты от MITM, то scram предлагает интересное решение. Клиенту сообщают, какие подвиды авторизации доступны (sha-256 и sha-256-plus), и он может выбирать. Метод sha-256-plus называется связыванием каналов. Он работает так: если мы соединяется по SSL с TLS, то первоначальная крипта, которую загадывает клиент, берется из т.н. peer-сертификата. Это первый сертификат в цепочке TLS, и его особенность в том, что он общий для сервера и клиента. Все вычисления отталкиваются от него, и это гарантирует, что между клиентом и сервером нет посредника.

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

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

  Посмотреть реализацию scram на Джаве можно тут.

• Postgres №41

  Apr 9, 2026 postgres, sql

  В прошлых заметках мы говорили про пулы соединений вроде HikariCP. Их еще называют пулами приложения. Это библиотека, которая открывает соединения, одалживает их тредам и закрывает. Аналогично устроен пул на Питоне из состава psycopg2.

  Есть, однако, другой подход – пулы на уровне сети. Запускается утилита, которая притворяется сервером Postgres (разговаривает по протоколу PG Wire). Она открывает несколько соединений к целевой базе. Как только к ней подсоединяются, она помечает одно из соединений как занятое. Далее она перекладывает сообщения от клиента серверу и обратно. Как только клиент уходит, занятое им соединение помечается свободным. Все это напоминает HTTP-прокси, только с другим протоколом. Наиболее известный PG Wire прокси – это pgbouncer; есть и другие аналоги.

  Pgbouncer отлично подходит для платформ, где по каким-то причинам нет пула приложения. Например, в PHP, многократно вызывая postgres.connect(), мы будет соединяться с сетевым пулом, который направит сообщения в одно из заранее открытых соединений.

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

  Во-первых, появляется бутылочное горлышко, за которым нужно следить. Если pgbouncer или аналог отвалится, доступ к базе пропадет у всех. Сюда же относится конфигурация: нельзя перезагрузить pgbouncer, потому что отвалятся все клиенты.

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

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

  AWS предлагает pgbouncer за отдельную плату. Не хочешь возиться с пулами – будь добр плати.

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

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

• Postgres №40

  Apr 9, 2026 postgres, sql

  Продолжение про пулы и соединения.

  Если вы часто видите ошибку “too many connections”, это не значит, что нужно срочно повышать max_connections. Это то же самое, что дать деньги алкоголику в надежде, что он выйдет на работу. Если приложение плохо работает с соединениями, не сомневайтесь: оно угробит двойной лимит подключений, тройной и так далее. Надо искать причину.

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

  Первая – открывать и закрывать соединение на каждый запрос. Например, в одном месте выбираем пользователей:

  conn = postgres.connect(host=localhost, port=5432...)
  conn.execute("select * from users where...")
  

  В другом — профили:

  conn = postgres.connect(host=localhost, port=5432...)
  conn.execute("select * from profiles where...")
  

  В третьем — что-то другое. На ровном месте три соединения вместо одного, вдобавок не закрытых.

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

  (dеf config {:host … :port …})
  (jdbc/execute! config "select * from table")
  

  Этим страдает кложурный JDBC. Функция execute! и другие принимают не соединение, а экземпляр протокола Connectable. У протокола два метода: borrow-connection и return-connection – занять и вернуть соединение (пишу по памяти, но семантика такая). У обычного соединения borrow-connection вернет его же, а return-connection ничего не делает. Для пула borrow-connection займет одно из свободных соединений, return-connection – вернет в пул.

  Наконец, можно передать словарь с параметрами подключения. Для него borrow-connection откроет соединение, а return-connection – закроет. Из этого следует, что каждый запрос будет открывать и закрывать соединение. Производительность такого кода ниже на два порядка, вдобавок вы насилуете сервер частыми fork.

  Другая ошибка – не закрывать соединение при исключении:

  conn = postgres.connect(host=..., port=...)
  users = conn.execute("select …")
  for user in users:
    process_user(user) # exception!
  

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

  Решение в том, чтобы использовать конструкции, которые гарантированно что-то сделают даже при ошибке. Самое банальное – try/catch. В Джаве есть try with resources, когда у каждого объекта вызывается метод .close. В Кложе для этого служит макрос with-open. В питоне есть контекстный менеджер with, где в точке выхода можно проверить, было ли исключение.

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

  (with-open [conn (jdbc/get-connection ...)]
    (let [users (jdbc/execute! conn "select ...")]
      (doseq [user useres]
        (process-user users))))
  

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

  (let [users
        (with-open [conn (jdbc/get-connection ...)]
          (jdbc/execute! conn "select ..."))]
    (doseq [user users]
      ...))
  

  В этом коде макрос with-open содержит только запрос. Как только он выполнится, соединение закроется, а результат окажется в переменной users, для работы с которой соединение не нужно. Разумеется, если вы стримите из базы или пагинируетесь по ней, способ не подойдет — соединение должно быть открыто все время.

  Всех этих ошибок можно избежать, если использовать пул. Многие клиенты возвращают в него соединения автоматом. Даже если этого не случилось, пул определяет, что соединение взяли и не вернули, и пишет лог. По умолчанию у него нет права закрывать потерянные соединения – это слишком сурово. Однако такое полномочие можно дать, и пул будет закрывать соединения, которые, по его мнению, заняли безвозвратно.

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

• Postgres №39

  Apr 9, 2026 postgres, sql

  Поговорим про пулы и проблемы соединений.

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

  При запуске сервера запускается мастер-процесс. Когда к нему кто-то подключается, процесс клонирует себя системным вызовом fork. Получаются два процесса: мастер занимается общими задачами, а второй — слушает и отвечает на сообщения клиента. Когда подключается кто-то еще, мастер снова клонирует себя и так далее.

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

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

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

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

  Настройка, которая определяет максимальное число соединений, называется max_connections. Увидеть ее можно командой SHOW:

  SHOW max_connections;
  

  У себя я вижу число 100. Это весьма адекватная цифра: число соединений не должно превышать нескольких десятков и уж тем более — сотен. Ситуация, когда вам не хватает 300 соединений, говорит об одном из двух. Либо у вас биллинг федерального значения и супер-пупер хайлоад. Либо приложение неправильно работает с соединениями: не закрывает их, теряет, удерживает без всякой нужды. Гораздо вероятней второе, а не первое.

  Соединениями управляют на двух уровнях: сетевом (прокси) и в приложении. Во втором случае это пул соединений. Это объект, который имитирует обычное соединение, но на самом деле открывает их несколько. Когда мы выполняем запрос, пул помечает одно из них занятым и передает сообщения через него. Когда мы закрываем соединение, этого не происходит — оно помечается свободным и доступно другим потокам приложения.

  Самый известный пул в Джаве называется HikariCP (от слова “харакири”). Он очень быстрый: разработчики писали, что анализировали байт-код и путем экспериментов уменьшили его минимума. В пуле нет блокирующих операций вроде synchronized: все сделано через CAS-примитивы. Написаны свои версии классов List и подобных, которые не проверяют выход за границы — это гарантируется в коде.

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

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

  Последнее — частая ошибка ребят, пришедших их PHP. В их мире не принято следить за ресурсами. Открыл файл, соединение, сокет и работай с ними, а закрывать на надо. Зачем, если скрипт отработает, и все закроется само? В системах, отличных от скриптовых, состояние не исчезает. Если открыли соединение, его нужно закрыть, но при этом следить, чтобы “открыл-закрыл” это не повторялось постоянно.

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

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

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

  Поэтому — гадать не нужно, все сводится к метрикам.

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

• Postgres №38

  Apr 7, 2026 postgres, sql

  Иногда бизнес-логику проще выразить на SQL – нравится вам это или нет. При этом окружающие теряют лицо: кричат “боже, хранимки!” и ведут себя недостойно. Бояться тут нечего, а истерика — от незнания.

  Я не говорю, что нужно писать хранимки. Речь о том, чтобы поручить базе ту работу, что делает ваш Питон или Джава. Часто SQL выигрывает: он короче, точнее, не требует многих обращений к базе.

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

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

  На какой-нибудь питонячей ORM это выглядит так:

  user_id = 51315
  points = 100
  
  points_entry = UserPoints.create(user_id=user_id, points=points)
  points_entry.save()
  
  rating_entry = UserRating.get_or_create(user_id=user_id)
  rating_entry.points += points
  rating_entry.save()
  

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

  Предположим, руководитель сказал: “ребят, переносим логику в БД”, но не удосужился объяснить, что это значит. Окей, программист пишет процедуру на plpgsql:

  create procedure add_points(int user_id, int points)
  begin
    create_points_history(user_id, points);
    rating_entry = get_user_rating_by_user_id(user_id);
    if (rating_entry is NULL) then
      insert_user_rating(user_id, points);
    else
      update_user_rating(user_id, points);
    endif;
  end;
  

  Эта процедура – построчная калька с Питона. Далее пишутся функции get_user_rating_by_user_id, get_user_rating_by_user_id и другие. Получается два экрана SQL, который трудно понять и поддерживать. Запросов по-прежнему много. Один раз увидев такое, разработчик получает душевную травму. При слове “хранимки” у него начинается истерика.

  Проблема в том, что автор процедуры использует SQL не по назначению. Он пишет на нем императивно, а SQL создан для операций над отношениями. Прочитайте еще раз: SQL создан для операций над отношениями. Единица операции — таблица. Если ваш SQL полон функций get_user_by_id, get_profile_by_id, то это ошибка. Копаешь граблями – получишь заведомо плохой результат. Нельзя пересаживать программиста с императивного Питона на SQL. Пока мозги не перестроились, хорошего результата не ждите.

  Достойное решение на SQL выглядит примерно так:

  with update_history as (
    insert into user_history (user_id, points)
    values ($1, $2)
  )
  insert into user_rating(user_id, points)
    values ($1, $2)
    on conflict user_id do update
    set points += excluded.points
    returning user_id, points
  

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

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

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

• Postgres №37

  Apr 7, 2026 postgres, sql

  Совет №36 оканчивался фразой:

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

  О них и поговорим.

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

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

  alter table profiles drop constraint fk_user_id
  

  Далее удаляется целевая сущность — пользователь. В дальнейших запросах используется внутреннее соединение, чтобы отсечь записи, которые ссылаются в никуда. Например, если мы выбрали профили по особому критерию, то пользователи цепляются не LEFT, а обычным JOIN. Это гарантирует, что в выборке не будет “висюков”:

  select * from profiles p
  where p.open_to_work
  join users u on p.user_id = u.id
  

  Позже “висюки” подчищаются запросом по расписанию. Заметим, что внутренний JOIN в целом работает лучше, чем левый и правый аналоги. Он быстрее и отсекает больше данных. Как правило, LEFT JOIN означает, что в базе что-то не так.

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

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

• Postgres №36

  Apr 7, 2026 postgres, sql

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

  Уточнение: будет много планов. Поскольку они длинные, их трудно записать в Телеграме в понятном виде. Здесь только фрагменты, а в конце – ссылка на гист.

  Итак, делаем две таблицы, пользователей и профили:

  create table users (
      id serial primary key
  );
  
  create table profiles (
      id serial primary key,
      user_id integer not null references users(id)
  );
  

  Вставим миллион пользователей:

  insert into users
  select x
  from generate_series(1, 999999) as seq(x);
  

  Профилей тоже миллион, но есть нюанс. Я не хочу, чтобы n-ая строка профиля ссылалась на n-ую строку пользователя. Это слишком хорошо и не отражает действительности. Поэтому первый профиль ссылается на последнего пользователя, второй – на предпоследнего и так далее. Другими словами, максимально усложним базе жизнь в плане соединений:

  insert into profiles
  select x, 1000000 - x
  from generate_series(1, 999999) as seq(x);
  

  Проверим, что в таблицах:

  table users limit 100;
  

  ┌─────┐
  │ id  │
  ├─────┤
  │   1 │
  │   2 │
  │   3 │
  │   4 │
  │   5 │
  │   6 │
  │   7 │
  │   8 │
  │   9 │
  │  10 │
  

  table profiles limit 100;
  

  ┌─────┬─────────┐
  │ id  │ user_id │
  ├─────┼─────────┤
  │   1 │  999999 │
  │   2 │  999998 │
  │   3 │  999997 │
  │   4 │  999996 │
  │   5 │  999995 │
  │   6 │  999994 │
  │   7 │  999993 │
  │   8 │  999992 │
  │   9 │  999991 │
  

  Индекса на user_id пока что нет. Что будет, если выбрать профили ста определенных пользователей? Посмотрим план:

  explain
  select * from profiles
  where user_id between 450000 and 450100;
  

  Gather  (cost=1000.00..11684.39 rows=94 width=8)
  Workers Planned: 2
  Parallel Seq Scan on profiles
    (cost=0.00..10674.99 rows=39 width=8)
  Filter: ((user_id >= 450000) AND (user_id <= 450100))
  

  Видим, что было полное сканирование профилей. При этом Postgres сделал это параллельно в два процесса (Parallel Seq Scan, Workers =2), а потом объединил результат (шаг Gather). Итоговая стоимость довольно высокая (11684).

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

  explain
  select *
  from users u, profiles p
  where u.id = p.user_id;
  

  Hash Join  (cost=30831.98..59602.98 rows=999999 width=12)
  Hash Cond: (p.user_id = u.id)
  Seq Scan on profiles p
    (cost=0.00..14424.99 rows=999999 width=8)
  Hash  (cost=14424.99..14424.99 rows=999999 width=4)
  Seq Scan on users u
     (cost=0.00..14424.99 rows=999999 width=4)
  

  То, что был seq scan по таблице users, это нормально – мы выбираем все записи. Однако по таблице profiles тоже был full scan, при этом стоимость хэширования огромна (cost=14424.99..14424.99). Финальный обход хэшей тоже дорогой: 59602.98

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

  explain
  with some_users as (
      select * from users
      where id between 450000 and 450100
  )
  select *
  from some_users sm
  join profiles p on sm.id = p.user_id;
  

  Считаем, что выборка some_users даст всего 100 пользователей. В реальности мы бы фильтровали по городу или другому признаку, но поскольку у нас только id, остается по нему.

  Gather  (cost=1007.61..10702.82 rows=98 width=12)
  Workers Planned: 2
  Hash Join  (cost=7.61..9693.02 rows=41 width=12)
  Hash Cond: (p.user_id = users.id)
  Parallel Seq Scan on profiles p
    (cost=0.00..8591.66 rows=416666 width=8)
  Hash  (cost=6.39..6.39 rows=98 width=4)
  Index Only Scan using users_pkey on users
    (cost=0.42..6.39 rows=98 width=4)
  Index Cond: ((id >= 450000) AND (id <= 450100))
  

  Этот план говорит о следующем: выборка some_users сработала моментально, но дальше все плохо. Чтобы найти профили, Postgres опять зарядил два параллельных потока с объединением. Итоговая стоимость (10702.82) высокая.

  Добавим индекс на поле user_id и посмотрим, как все изменится:

  create unique index idx_profiles_user_id_u
  on profiles using btree (user_id);
  
  analyze profiles;
  

  Простой пример: найти профили по диапазону пользователей:

  explain
  select * from profiles
  where user_id between 450000 and 450100;
  

  Index Scan using idx_profiles_user_id_u on profiles
    (cost=0.42..10.37 rows=97 width=8)
  Index Cond: ((user_id >= 450000) AND (user_id <= 450100))
  

  Очевидно, стоимость копеечная.

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

  explain
  with some_users as (
      select * from users
      where id between 450000 and 450100
  )
  select *
  from some_users sm
  join profiles p on sm.id = p.user_id;
  

  Nested Loop
    (cost=0.85..825.75 rows=98 width=12)
  Index Only Scan using users_pkey on users
    (cost=0.42..6.39 rows=98 width=4)
  Index Cond: ((id >= 450000) AND (id <= 450100))
  Index Scan using idx_profiles_user_id_u on profiles p  (cost=0.42..8.36 rows=1 width=8)
  Index Cond: (user_id = users.id)
  

  Было два прохода по индексам, итоговые данные соединены вложенным циклом (Nested Loop). Сложность такого соединения квадратична, но на малых объемах выигрывает. Стоимость 825 – низкая.

  И наконец, узрите истинную мощь Merge Join – соединения слиянием. Повторим запрос, в котором мы соединяем две таблицы без каких-либо фильтров:

  explain
  select *
  from users u, profiles p
  where u.id = p.user_id;
  

  Вы, наверное, думаете, что все полетит? Нет, план останется таким же, то есть очень медленным:

  Hash Join
    (cost=30831.98..59602.98 rows=999999 width=12)
  ...
  

  Будет два seq scan-а по обеим таблицам и ручное хеширование. Напомню, я специально создал худший сценарий, когда порядок профилей обратный: первый профиль ссылается на последнего пользователя и так далее. Это делает хэширование дорогим. Стоимость первой строки равна 30831 – это значит, что перед получением данных база будет тупить – считать хэши.

  Сделаем вот что: в запросе выше заменим profiles на подзапрос, отсортированный по user_id:

  explain
  select *
  from users u, (select * from profiles order by user_id) p
  where u.id = p.user_id
  order by u.id;
  

  План:

  Merge Join  (cost=5.14..81384.50 rows=999999 width=12)
  Merge Cond: (profiles.user_id = u.id)
  Index Scan using idx_profiles_user_id_u on profiles  (cost=0.42..30408.41 rows=999999 width=8)
  Index Only Scan using users_pkey on users u  (cost=0.42..25980.41 rows=999999 width=4)
  

  Оба набора упорядочены по тем полям, по которым их соединяют, поэтому сработает соединение слиянием. Стоимость его гораздо ниже, чем у хеширования. Первая строка доступна уже через 5.14 единиц стоимости – а было 30831.98. Четыре порядка! Когда я вижу в плане Merge Join, все мои железы активируются и выделяют жидкости.

  Планы – полезная вещь. Читайте их, экспериментируйте, пытайтесь понять, почему план именно такой, а не другой.

  Ссылка на код.

• Postgres №35

  Apr 7, 2026 postgres, sql

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

  create table users (
      id serial primary key...
  );
  
  create table profiles(
      id serial primary key,
      user_id integer not null references users(id)
  );
  

  Ключевое слово references создает это ограничение автоматом. Можно задать его явно таким выражением:

  alter table profiles
  add constraint fk_profile_user_id
  foreign key (user_id)
  references users (id);
  

  Первичные ключи бывают составными, и тогда внешний ключ тоже составной.

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

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

  select from users where id = 100500
  

  Под капотом Postgres проверяет только индекс – нет смысла дергать таблицу — поэтому запроса в логах вы не увидите.

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

  select * from profiles where user_id = 100500
  

  Увы, это не так. По умолчанию внешний ключ не индексируется, и запрос выше даст full scan. Повесьте на него btree:

  create index idx_profile_user_id
  on profiles using btree (user_id);
  

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

  Пример с профилем можно сделать еще строже: мы допускаем только один профиль на пользователя, поэтому повесим на user_id уникальный индекс:

  create UNIQUE index ...
  

  Индексы на внешние ключи обязательны, если используется join. Скажем, вы строите отчет по всем пользователям системы:

  select * from users u
  left join profiles p on u.id = p.user_id
  

  Без индекса на profiles.user_id запрос будет очень тяжелым. Зато если поле индексировано и вдобавок обе таблицы отсортированы по коду пользователя, возможно, сработает Merge Join – соединение слиянием. Это самый быстрый алгоритм соединения. Принцип у него такой же, как и у сортировки слиянием.

  Внешние ключи вносят в базу ограничения, и желательно следовать им. В противном случае вы рискуете создать “висяки” – ссылки, ведущие в никуда. Как и в случае с дублями, их тяжело расследовать и удалять.

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

• Libpq и Java

  Apr 7, 2026 programming, postgres, sql, java

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

  Я хотел проверить: будет ли драйвер для Postgres быстрее, если переписать его с чистой Джавы на Libpq. Кто не знает, Libpq – это библиотека на Си для работы с базой. Идея в том, чтобы поручить большую часть работы коду на Си, а самому только дергать функции через JNI.

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

  Я хотел собрать сишный код под три платформы (винда, мак, линукс) и две архитектуры (x86-64, arm64). Их произведение дает шесть комбинаций. Вопрос: где и как собирать? Допустим, у меня есть Мак на арме; докер на нем дает Линукс на арме. Нужна винда на арме, а где ее взять? То же самое с интелом: у меня есть старый Мак на core i3, но по сегодняшним меркам он такой медленный, что лишний раз трогать его не хочется. Есть игровой комп с Виндой на интеле, но не будешь же носить его с собой? В итоге я поручил сборку Github actions: в нем доступны все шесть видов машин, а сценарий пишется на yaml.

  По наивности я думал, что напишу один Makefile, который вызову с разными переменными – и дело в шляпе. Идея провалилась из-за адского разнообразия сред и платформ. Сборка простого кода на C++ отличается даже не в рамках ОС, а дистрибутива. Везде спонтанные отличия, условности, разные названия папок, путей и так далее.

  Несколько примеров. На винде x64 компилятор MSYS2 ставится в папку c:\temp\a\msys64, а на арме – в d:\temp\a\msys64 (другой диск). Почему был C, а стал D? И почему он ставится не в корень, а куда-то в temp? Уверен, что есть объяснение, но неудобно.

  Мак и его Homebrew тоже хорош. На интеле пакет libpq включает утилиту pg_config – она показывает, где лежат заголовочные файлы и другое барахло. На M1 в таком же пакете утилиты нет. Почему?

  Плохи дела и на этом вашем Линуксе. В той Убунте, что идет на Гитхабе, пакет postgres-client – 16 версии, а мне нужна 18. Нужно подключать частный репозиторий Postgres. У него тоже особенности: на интеле пакет postgres-client-18 включает заголовочные файлы, а на арме – нет. Нужно качать dev-барахло.

  И таких вещей очень много. Разные пути, переменные среды; здесь есть файл, а тут нет и так далее. Смейтесь или нет, но мне понадобилось 166 (сто шестьдесят шесть) попыток, чтобы прошли все билды. Я убил на это вагон времени, забивая на работу и даже личные обязанности. При этом та удачная 166-я попытка – грубый черновик, который еще доводить и доводить до ума.

  Напомню, все это лишь для того, чтобы:

  • получить последнюю версию libpq под нужные ОС и архитектуру;
  • собрать динамическую библиотеку из кода на C++;
  • сохранить libpq и библиотеку в артефакты.

  Допускаю, что плохо знаю тонкости кроссплатформенной сборки; возможно, есть инструменты, которые щелкают эту проблему как дважды два. Почему их нет в поисковой выдаче? Я безвылазно сидел в Гугле и StackOverflow, повидал сотню разных ошибок – и ни разу не было ответа, мол, поставь программу X, и она все тебе соберет.

  Если такая программа есть или вы решали похожую проблему, расскажите, пожалуйста.

• Хорошо или быстро

  Apr 7, 2026 life

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

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

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

  Часто скорость снижает не качество, а нелепые требования. Например, написать код, используя кривую библиотеку – взять нормальную запрещено. Или хочется обратиться к базе, но нельзя – вызывай сервис, который ходит в базу и выдает то же самое, только на два порядка медленней.

  На проверку оказывается, что главный враг качества – внутренние заморочки, и объяснение у них следующее: мы делаем так, потому что мы так делаем.

  Хорошая практика в том, чтобы следить за ощущениями и спрашивать себя: что мешает сделать работу быстро и качественно? При этом ни в коем случае не врать: если это коллега, руководитель или предубеждение, то не пытаться это скрыть. Честно сказать: если выбор решения за мной, я сделаю за X дней, если как говорят – за 3X дней.

  Качеству и скорости нужен третий компонент – свобода действий. Он – тот самый ингредиент, с которым все удается и прогресс идет. Нет свободы – и начинаются торги: долго, но качественно. Быстро, но тяп-ляп.

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

Страница 1 из 108 →