Внутренняя архитектура PostgreSQL

1. Архитектура на основе процессов
PostgreSQL использует архитектуру «один процесс на соединение», то есть для каждого клиента создаётся отдельный процесс операционной системы.

2. Журнал предзаписи
PostgreSQL применяет WAL для обеспечения надёжности данных, согласованности, восстановления после сбоев и репликации

3. Многоверсионность и управление параллелизмом
Благодаря MVCC PostgreSQL позволяет выполнять параллельные транзакции без необходимости жёсткой блокировки данных

4. Конвейер выполнения запросов
Запросы в PostgreSQL проходят через чёткую последовательность этапов: разбор, планирование, исполнение и возврат результатов

5. Система индексации
PostgreSQL поддерживает множество типов индексов (B-Tree, GIN, GiST, BRIN и др.) для оптимизации запросов под разные типы данных

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

7. Логическая декодировка
Позволяет транслировать изменения из WAL в логическом формате, что полезно для репликации и систем отслеживания изменений (CDC).

8. Расширения
PostgreSQL построен как модульная система и поддерживает расширения, которые позволяют добавлять новые функции и возможности.

9. Сбор статистики
Механизм сбора статистики PostgreSQL отслеживает активность в реальном времени, что помогает анализировать и оптимизировать производительность базы данных

👉 @SQLPortal

Использование привязанных переменных — ключ к быстрой работе приложений

Пишите SQL вот так:

'SELECT ... WHERE col = ' || var

Каждое новое значение VAR создаёт новый запрос ⇒ много разборов ⇒ медленно

Используйте привязку вот так:

'SELECT ... WHERE col = :bind'

Один и тот же запрос для любого значения ⇒ один разбор ⇒ быстро

👉 @SQLPortal

В Postgres 18 появилась поддержка аутентификации пользователей через OAUTH 2.0

👉 @SQLPortal

Знаешь, что Postgres можно тюнить по одной таблице?

ALTER TABLE SET позволяет задавать отдельные параметры тюнинга, такие как fill factor, vacuum и параллельные воркеры, для конкретных нагрузок. Это более гранулярный и точный подход и может быть лучше, чем настройка на уровне всей базы данных.

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

ALTER TABLE orders
SET (
  fillfactor = 80,
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_analyze_scale_factor = 0.05,
  parallel_workers = 4
);

ALTER TABLE events_log
SET (
  fillfactor = 70,
  autovacuum_enabled = true,
  parallel_workers = 2
);

👉 @SQLPortal

Наглядный гайд с функциями от SQL до Pandas

👉 @SQLPortal

Многие ИИ-воркфлоу у разработчиков выглядят так:

upsert строки → вызов API → обработка через ИИ → обновление строки

А что если запускать LLM прямо в базе данных, с доступом ко всем примитивам и аутентификацией?

Чуваки открыли исходный код Postgres LLM.

- полностью асинхронно, не блокирует транзакции
- работает с любой моделью

можно быстро стартовать в supabase, не выходя из дашборда или просто передать это агенту

👉 @SQLPortal

Пилообразные всплески ввода-вывода в Postgres часто связаны с агрессивными настройками контрольных точек.

Чтобы стабилизировать задержки, стоит увеличить параметр checkpoint_timeout.

Также имеет смысл выставить checkpoint_completion_target = 0.9, чтобы распределить нагрузку ввода-вывода более равномерно во времени.

Пример настройки в postgresql.conf:

checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

Если настраивать через SQL:

ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;

SELECT pg_reload_conf();

Проверка текущих значений:

SHOW checkpoint_timeout;
SHOW checkpoint_completion_target;

👉 @SQLPortal

Познакомьтесь с Postgresus — open-source и self-hosted решением для автоматизации бэкапов PostgreSQL.

Инструмент закрывает типичный набор задач, которые обычно размазываются по cron-скриптам и ручной поддержке.

Возможности:

- простое планирование резервного копирования;
- выгрузка в облачные хранилища (S3, R2, Google Drive);
- уведомления в Slack и Telegram.

Работа с бэкапами PostgreSQL часто превращается в набор разрозненных скриптов, где нужно отдельно держать расписание, ретраи, доставку в хранилища и алерты. Postgresus собирает это в единый воркфлоу с самохостингом и автоматизацией.

Подходит для DevOps-сценариев, где нужно централизовать резервное копирование без внешних SaaS-зависимостей.

👉 @SQLPortal

Мониторинг реплик Postgres

Смотри pg_stat_replication на primary-ноде для отслеживания лага в реальном времени и состояния синхронизации. На standby используй pg_last_xact_replay_timestamp(), чтобы точно увидеть, когда был применён последний коммит.

Пример SQL-запросов для мониторинга.

Основная нода — состояние репликации

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

Основная нода — минимальная проверка подключённых реплик

SELECT *
FROM pg_stat_replication;

Резервная нода — время последнего применённого коммита

SELECT pg_last_xact_replay_timestamp();

Резервная нода — оценка отставания по времени

SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;

👉 @SQLPortal

Foreign Data Wrapper (FDW) в Postgres позволяют работать с удалёнными БД как с локальными таблицами. Поддерживаются FDW для других Postgres-баз, MongoDB, MySQL, ClickHouse и даже OGR spatial-файлов. Один запрос, несколько источников — без ETL.

👉 @SQLPortal

В PostgreSQL есть LEAST и GREATEST для сравнения значений по колонкам — это не то же самое, что агрегационные MIN и MAX.

👉 @SQLPortal

Claude Code: достигнут лимит использования.

ожидание сброса лимита.

👉 @SQLPortal

50 дней DATA ANALYSIS с PYTHON

👉 @SQLPortal

Компания Zomato обрабатывает более 2 млн заказов в день.

450 млн сообщений в Kafka в минуту.
20 млрд событий обрабатывается в неделю.
Петабайты данных выполняются через запросы ежедневно.

Когда-нибудь задумывался, что происходит с данными твоего заказа ПОСЛЕ нажатия «Оформить заказ»?

Вот как реально выглядит data-пайплайн Zomato:

𝟭. 𝗧𝘆 𝗻𝗮𝘇𝗵𝗮𝗹 «𝗢𝗳𝗼𝗿𝗺𝗶𝘁𝘆 𝘇𝗮𝗸𝗮𝘇»
⤷ Заказ, оплата и геолокация попадают в Aurora MySQL + DynamoDB.
𝟮. 𝗦𝗼𝗯𝘆𝘁𝗶𝘆𝗮 𝗶𝗱𝘂𝘁 𝘃 𝗞𝗮𝗳𝗸𝗮
⤷ 450M+ сообщений в минуту.
⤷ Отсюда стартуют все downstream-пайплайны.
𝟯. 𝟰 𝗽𝗮𝗽𝗮𝗶𝗻𝗹𝗮𝗶𝗻𝗮 𝘇𝗮𝗽𝘂𝘀𝗸𝗮𝘆𝘂𝘁𝘀𝘆𝗮 (𝗻𝗲 𝟭, 𝗮 𝗰𝗵𝗲𝘁𝘆𝗿𝗲)
MySQL → Sqoop → S3 (пакетный ETL)
MySQL → Debezium CDC → Kafka → Flink → Iceberg (стриминг)
DynamoDB Streams → Spark → S3 (serverless-данные)
App Events → Kafka → Flink → Druid (реалтайм-дашборды)
𝟰. 𝗩𝘀𝗲 𝗽𝗮𝗱𝗮𝗲𝘁 𝘃 𝗦𝟯
⤷ Формат ORC. Таблицы Iceberg.
⤷ Вся система сходится в data lake.
𝟱. 𝗧𝗿𝗶𝗻𝗼 𝗼𝗯𝗿𝗮𝗯𝗮𝘁𝘆𝘃𝗮𝗲𝘁 𝗱𝗮𝘁𝗮𝗹𝗮𝗸𝗲
⤷ 250K+ запросов в неделю. 2 петабайта сканирования.
⤷ 3 типа кластеров: ad-hoc, ETL (через Airflow), отчётность.
⤷ JOIN’ы между MySQL, Druid, Pinot и MongoDB.
𝟲. 𝗗𝗿𝘂𝗶𝗱 𝗱𝗮𝗲𝘁 𝗿𝗲𝗮𝗹𝘁𝗮𝗶𝗺
⤷ 20 млрд событий в неделю. 8 млн запросов в неделю.
⤷ Время ответа — миллисекунды.
𝟳. 𝗞𝗼𝗺𝗮𝗻𝗱𝘆 𝘀𝗺𝗼𝘁𝗿𝘆𝗮𝘁 𝗱𝗮𝘀𝗵𝗯𝗼𝗮𝗿𝗱𝘆
⤷ Superset + Redash + Jupyter

𝗙𝘂𝗹𝗹 𝘀𝘁𝗲𝗸:
- Kafka (450M сообщений/мин)
- Flink, Spark, Sqoop на EMR
- Trino (batch) + Druid (real-time)
- S3 + Iceberg + ORC
- Debezium CDC
- Airflow
- Superset, Redash, Jupyter

В следующий раз, когда закажешь бирьяни в пятницу в 21:00…

заказ проходит через Kafka → Flink → S3 → Trino → дашборд.
в Zomato видят, что заказы бирьяни в Мумбаи выросли на 40%.
всё это почти в реальном времени. Это дата-инжиниринг на масштабе.

👉 @SQLPortal

Oracle Database 26ai расширила конструкцию возврата значений при операциях изменения данных.
Теперь при операциях изменения можно получить значения до и после изменения через OLD и NEW, включая поддержку MERGE.

Пример для UPDATE:

UPDATE ...
RETURN OLD col, NEW col
INTO :before, :after

Пример для MERGE:

MERGE INTO ...
RETURN OLD col, NEW col
INTO :before, :after

👉 @SQLPortal

Старые приёмы работы с 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

Новый клиент для современных баз данных

С встроенным AI и MCP, расширяемый через плагины.
✓ Поддержка PostgreSQL, MariaDB, MySQL и SQLite
✓ Для macOS, Windows и Linux
✓ Переведён на испанский язык

Open source и бесплатный: TabularisDB

👉 @SQLPortal

Когда ты пишешь SQL-запрос, порядок команд вроде бы логичен SELECT, FROM, WHERE и тд. Но база данных выполняет их в другом порядке 😄

Настоящая последовательность выполнения 👇

FROM
JOIN
ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

💡 Поэтому WHERE не может использовать SUM() — он идёт до агрегации. А HAVING — можно

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

👉 @SQLPortal

Если вы всё ещё используете PostgreSQL 14-й версии, вот две важные вещи, которые стоит знать:

Последний релиз от сообщества выйдет уже в этом ноябре.

Вы упускаете 5 лет серьёзных улучшений производительности, новых возможностей и повышения стабильности.
Обновление между мажорными версиями требует подготовки. Изменения в page headers, форматах tuple и оптимизациях индексов означают, что вам потребуется пересобрать индексы и структуры данных. Типичные пути миграции — pg_dump/restore, pg_upgrade или логическая репликация (logical replication) со всеми её известными ограничениями.

Также функции и extensions после обновления могут вести себя иначе, поэтому корректная поведенческая валидация (behavioral validation) критически важна — особенно для production-систем. Так что просто YOLO-обновление тут не вариант.
Но бояться этого тоже не стоит. Улучшения производительности и надёжности в новых версиях PostgreSQL действительно заметны — и ваши запросы, и ваша повседневная работа станут ощутимо приятнее.

https://www.crunchydata.com/blog/examining-postgres-upgrades-with-pg_upgrade

👉 @SQLPortal

5 SQL-советов, которые ускорят ваши запросы и сберегут нервы

🛑 Не злоупотребляйте индексами
🛑Индексы ускоряют поиск, но замедляют запись и занимают место
🛑Создавайте их для столбцов в WHERE, JOIN и ORDER BY

🛑 Структура запроса важна
🛑Разбивайте сложные запросы на несколько простых
🛑Запрашивайте только нужные столбцы

🛑 Оптимизируйте JOIN и WHERE
🛑Используйте индексы и фильтруйте данные как можно раньше

🛑 Избегайте коррелированных подзапросов
🛑Переписывайте их через JOIN или используйте временные таблицы

🛑 Дизайн базы — основа производительности
🛑Балансируйте между нормализацией (целостность) и денормализацией (производительность)

👉 @SQLPortal