Старые приёмы работы с Postgres которые могут быть полезны. Пример функция soundex

Можно написать поиск по именам которые звучат как Jon — их не обязательно писать так же или иметь совпадающую триграмму

SELECT first_name, last_name
FROM customers
WHERE soundex(first_name) = soundex('Jon');

Такой запрос найдёт "John" "Jon" и даже "Joan"

Soundex — это специальный алгоритм для английского произношения. Он берёт первую букву слова и оставляет её в качестве якоря. Остальную часть слова он упрощает сгруппировав согласные в несколько общих категорий звуков. Например согласные которые произносятся с помощью губ как B и P считаются одной группой. Гласные (A E I O U) и мягкие согласные вроде H и W полностью игнорируются так как они мало влияют на основное звучание. В итоге получается стандартизированный код из четырёх символов. Запрос ищет другие совпадения с таким же четырёхсимвольным кодом

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

Больше про soundex, полнотекстовый поиск и нечеткое сравнение строк читайте здесь: ссылка

👉 @SQLPortal

libpq deep dive День 2 - переменные окружения Postgres

Когда вы подключаетесь к Postgres, соединение под капотом ведет libpq

libpq проверяет параметры подключения в таком порядке приоритета

1. Явные параметры подключения

psql postgres://postgres:flXEXcSpx@p.qz
Это может быть формат URL как выше или отдельные флаги команды вроде -h <host> или -p <port>
psql преобразует свои флаги командной строки в параметры подключения для одной из процедур libpq

2. Переменные окружения

Если нужного параметра подключения не найдено, libpq затем смотрит переменные окружения операционной системы. Это позволяет разработчикам приложений задавать переменные вроде PGHOST, PGUSER, PGDATABASE и получать эти значения в любом приложении, которое использует libpq

Многие ORM и драйверы тоже используют libpq для хранения и поддержки соединения. Схема такая
Наше приложение или ORM → драйвер обертка → libpq → PostgreSQL
Когда драйвер вроде psycopg2 который линкуется с libpq инициализирует соединение, он автоматически ищет переменные типа PGHOST, PGUSER и тд в текущем окружении и использует их

3. Значения по умолчанию

Если настройка не указана ни в строке подключения ни в переменной окружения, libpq использует зашитые при компиляции дефолты например подключение к localhost под текущим пользователем ОС
Поэтому psql postgres просто подключится к вашему localhost

👉 @SQLPortal

Talk to DBs

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

→ Загружаешь URL своей базы
→ Задаёшь любой вопрос
→ Получаешь SQL-запрос, результаты и пояснения

https://github.com/Arindam200/awesome-ai-apps/tree/main/simple_ai_agents/talk_to_db

👉 @SQLPortal

Разбор libpq. День 3 — libpq и режим pipeline

Новое в Postgres 14 — libpq теперь поддерживает пакетные запросы.

Раньше Postgres работал по принципу «одна команда — один ответ». Сначала отправлялся запрос на сервер, ждали ответ, потом отправляли следующий. В режиме pipeline приложение может отправить целую пачку команд сразу.

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

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

👉 @SQLPortal

Postgres и MySQL поддерживают два типа репликации — логическую и физическую

Репликация используется для HA-схем с primary/replica и для миграций между провайдерами без простоя

Почему два вида

🔸Физическая репликация

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

В MySQL это называется ROW-level replication (binlog_format=ROW), а в Postgres — физическая репликация

🔸Логическая репликация

Передаёт сами изменения в логическом виде.
Например, при UPDATE строки реплика получит команду вида «изменить колонку X на значение Y в строке Z», а не побайтовые изменения.
Это медленнее, но позволяет реплицировать между разными основными версиями сервера

В MySQL это называется STATEMENT-level replication (binlog_format=STATEMENT)

Оба варианта дают широкие возможности по масштабированию и миграциям баз данных

👉 @SQLPortal

libpq deep dive День 4 . Несколько хостов в одном подключении

Новое в Postgres 16 Теперь libpq умеет распределять подключения между несколькими хостами В строке подключения можно указать сразу список серверов, а не только один вместе с данными пользователя и базы

Пример строки подключения

host=pg-replica-01,pg-replica-02,pg-replica-03 user=app_user dbname=my_app_db load_balance_hosts=random

Здесь указано несколько хостов и в конце добавлен параметр load_balance_hosts=random

Как это работает

1) libpq берет список хостов pg-replica-01 pg-replica-02 pg-replica-03 и случайным образом их перемешивает
2) Пытается подключиться к хостам в новом случайном порядке пока соединение не установится

Это простой способ распределить нагрузку на read реплики без использования внешнего балансировщика

👉 @SQLPortal

Подготовленные выражения и параметризованные запросы теперь работают с самым популярным пулером соединений для Postgres — pgBouncer

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

👉 @SQLPortal

libpq углублённый разбор. День 5 - Почему оно называется libpq?

Ну lib понятно — от слова library. А вот почему именно ‘pq’...

Есть идеи? Без подсказок ;)

👉 @SQLPortal

Два SQL-запроса ниже для планировщика разные:

select * from emp where id = 7

select * from emp where id = 9

Планировщику нужно разобрать скомпилировать и построить план для каждого

Использование bind-переменных позволяет избежать парсинга
Теперь оба хешируются в один и тот же план:

select * from emp where id = ?, 7

select * from emp where id = ?, 9

Из этого вылезают любопытные ситуации:

select * from emp where id between ? and ?, 1, 3

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

select * from emp where id between ? and ?, 0, 9000000

может быть не таким эффективным как полный скан

Ничего не бывает бесплатным. Всегда есть компромисс

👉 @SQLPortal

Хочешь попрактиковаться в SQL на реальных задачах от топовых компаний и подготовиться к собеседованиям?

Заходи на stratascratch.com — там собраны сотни задач с интервью в FAANG и других крупных компаниях

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

👉 @SQLPortal

Если ты запускаешь транзакцию в уровне изоляции Read Committed (назовём её tx1) и выполняешь долгий SELECT — например, с сортировками и join’ами по большим датасетам — база берёт снимок зафиксированных данных на момент старта этого запроса.

Пока этот SELECT ещё работает, другая транзакция (tx2) в другом сеансе обновляет те же строки и успевает закоммитить.

Несмотря на то что tx2 закоммитила во время выполнения SELECT, твой запрос внутри tx1 этих апдейтов не увидит, потому что каждый запрос в Read Committed использует снимок данных, сделанный в начале его выполнения. Всё, что было зафиксировано после старта запроса, отфильтровывается.

Но если потом в рамках tx1 ты запустишь ещё один SELECT, он уже увидит изменения из tx2, так как возьмёт новый снимок, включающий все коммиты, сделанные к этому моменту.

В Postgres этот снимок строится на основе идентификаторов транзакций.

👉 @SQLPortal

Copilot как функция в Excel

Microsoft только что анонсировала функцию copilot в Excel

Новая функция =COPILOT() в Excel позволяет анализировать данные, генерировать контент и брейнштормить прямо в ячейках таблицы

Подробнее: ссылка

👉 @SQLPortal

Строки, которые возвращают операторов множеств в SQL:

🔸UNION
Все строки из обеих таблиц

🔸MINUS / EXCEPT
Строки из первой таблицы, которых нет во второй

🔸INTERSECT
Строки, которые есть в обеих таблицах одновременно

По умолчанию эти операторы убирают дубликаты.
Клаузa ALL сохраняет и учитывает дубликаты.

👉 @SQLPortal

Проверка производительности Postgres: random_page_cost

Если вы держите базу на SSD, то дефолтные настройки Postgres замедляют вас.

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

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

Представим, что вам нужно найти несколько конкретных товаров в большом супермаркете. Есть два варианта:

- sequential scan
Это как пройтись по каждому ряду магазина подряд от начала до конца. В Postgres это последовательное сканирование. Предсказуемо, между товарами минимум времени.

- index scan
Это как посмотреть в каталог магазина (индекс), найти номер ряда для каждого товара и сразу идти туда. Это индексное сканирование.

random_page_cost отражает цену перехода между разными, несмежными рядами.

🔸Высокий random_page_cost
Это говорит планировщику, что переходы между рядами очень затратные.
В итоге он чаще выберет sequential scan, даже если нужно всего несколько товаров, потому что так он «экономит» на случайных переходах.

🔸Низкий random_page_cost
Это говорит планировщику, что переходы между рядами быстрые.
Тогда он охотнее использует index scan, так как выборочные переходы стоят дешево.

🔸Значения по умолчанию
По умолчанию random_page_cost = 4.0.
Это означает, что случайное чтение страницы считается в 4 раза дороже последовательного.
Это консервативное значение под традиционные HDD.

На SSD или если база целиком помещается в RAM, штраф за случайные чтения намного ниже.
Индексные сканирования быстрее, и «переходы по рядам» дешевле.
Для SSD обычно лучше ставить значение от 1.1 до 1.5.

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

👉 @SQLPortal

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

https://dbquacks.com/

👉 @SQLPortal