PostgreSQL использует ленивое «swizzling» указателей (lazy pointer swizzling), чтобы ускорить доступ к буферу и держать данные на диске и в памяти синхронизированными без лишних преобразований. Вот как это работает.

Когда PostgreSQL хранит структуры данных, например страницы B-tree индекса, на диске нельзя использовать обычные указатели из оперативки → адреса на диске и в памяти вообще из разных миров. Но как только данные попадают в память, хочется быстро ходить по структуре через указатели, а не через медленные косвенные ссылки.

На диске PostgreSQL хранит ссылки в виде номеров блоков и смещений. Когда страница читается в общий буферный пул, эти логические ссылки остаются как есть → и вот тут начинается интересное.

Буферные страницы

→ Каждая страница в памяти идентифицируется с помощью структуры BufferTag (в ней указаны база, таблица и номер блока).
→ В памяти PostgreSQL держит массив дескрипторов буферов.
→ Когда нужно получить доступ к странице, код идет через менеджер буферов, который по номеру блока находит реальное местоположение страницы в памяти.
→ Это и есть ленивое swizzling — преобразование адреса выполняется не заранее, а при каждом обращении.

Пример структуры BufferTag из исходников PostgreSQL:

typedef struct
{
  Oid   spcOid;      /* tablespace */
  Oid   dbOid;       /* database */
  Oid   relNumber;   /* relation (table or index) */
  ForkNumber forkNum;/* main, fsm, vm, init forks */
  BlockNumber blockNum; /* block number within the relation */
} BufferTag;

Почему это круто

→ Страницы в буферном пуле сохраняют свой дисковый формат, не нужно ничего конвертировать туда-сюда.
→ Несколько процессов могут обращаться к одной странице, используя номера блоков, а не указатели → это делает параллельный доступ простым и безопасным.
→ Когда страница выкидывается из буфера, не нужно «раскручивать» swizzling обратно.

PostgreSQL таким образом жертвует крошечной долей производительности на каждом обращении (из-за дополнительного уровня косвенности), но выигрывает в простоте буферного менеджмента и надежности при многопоточном доступе.

Очень аккуратный и элегантный дизайн.

👉 @SQLPortal

Тема → Нормализация в SQL

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

Представь нормализацию как организацию своих школьных конспектов

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

1NF (Первая нормальная форма) → «Без повторяющихся списков»

Каждая ячейка должна содержать одно значение, а не список.

2NF (Вторая нормальная форма) → «Без частичной зависимости»

Не храни данные, которые зависят только от части составного ключа.

3NF (Третья нормальная форма) → «Без лишней информации»

Убираем данные, которые зависят не от ключевых столбцов, а от других колонок.

BCNF (Форма Бойса-Кодда) → усиленная версия 3NF

Правило → любой детерминант должен быть кандидатом в ключ.
(Проще говоря → если один столбец определяет другой, то первый должен быть уникальным идентификатором.)

4NF (Четвёртая нормальная форма) → убирает многозначные зависимости

Если одна запись хранит два независимых списка → раздели их.

5NF (Пятая нормальная форма) → «Самая чистая форма»

Убираем зависимости, возникающие при соединениях.
Если таблицу можно разбить на более мелкие и потом собрать обратно без потери данных → разбей.

Вопрос → почему не нормализовать всё до упора?

Переизбыток нормализации → куча JOIN-ов → запросы тормозят, особенно в отчётах.

Поэтому в транзакционных системах (OLTP) → применяют нормализацию,
а в аналитических (OLAP) → часто используют денормализацию.

👉 @SQLPortal

Топ-10 инструментов для SQL-разработчиков и администраторов баз данных

Подробности: тык

👉 @SQLPortal

PostgreSQL 18: оператор RETURNING теперь поддерживает OLD и NEW значения в UPDATE

Теперь можно в одном запросе получать и старые, и новые данные строк после обновления

👉 @SQLPortal

Используй Heap Maps в Oracle Database, чтобы увидеть:

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

Matt DeMarco показывает, как с этим работать.

👉 @SQLPortal

Когда нет ни первичного, ни внешнего ключа, а связать таблицы 1 к 1 всё равно нужно

Иногда попадаются такие таблицы, где нет ни PRIMARY KEY, ни FOREIGN KEY, но данные логически связаны один к одному.
Как аккуратно сделать это в SQL?

Самый чистый вариант — сгенерировать порядковый номер для каждой строки через ROW_NUMBER() и потом соединить таблицы по нему.

WITH t1 AS (
  SELECT Product, ROW_NUMBER() OVER (ORDER BY Product) AS rn
  FROM Table_1
),
t2 AS (
  SELECT Price, ROW_NUMBER() OVER (ORDER BY Price) AS rn
  FROM Table_2
)
SELECT t1.Product, t2.Price
FROM t1
JOIN t2 ON t1.rn = t2.rn;

Что тут происходит:

ROW_NUMBER() OVER (ORDER BY Product) пронумеровывает продукты 1, 2, 3...

ROW_NUMBER() OVER (ORDER BY Price) делает то же самое для цен

Потом просто соединяем по rn, и получаем аккуратное 1-к-1 сопоставление

Не путай с RANK() или DENSE_RANK() = они не гарантируют строгую последовательность при дубликатах.
И обязательно добавляй ORDER BY в ROW_NUMBER(), чтобы нумерация была детерминированной.

👉 @SQLPortal

⭐️ Road to Highload: видеопроект о проектировании архитектуры высоконагруженных систем

Инженеры Яндекс 360 накопили значительный опыт в проектировании и разработке систем, которыми пользуются больше 95 миллионов человек ежемесячно.

В этом видеопроекте разработчики на практических примерах рассказывают, как создают архитектуру систем, которые держат 1 000 000 RPS и хранят петабайты мета-данных.

В выпусках обсуждаем:

🎙 Серия 1. Функциональные и нефункциональные требования. Как сбор требований помогает создавать надёжные и масштабируемые решения

🎙 Серия 2. Надёжный API. Принципы проектирования API, которые помогут сделать его консистентным, предсказуемым и поддерживаемым

🎙 Серия 3. Крупноблочная архитектура: карта вашей системы. Как выглядит модель на примере Яндекс Календаря и как ребята применяют её для эффективной коммуникации с различными командами разработки

🎙Серия 4. Практика: Рост баз данных: от единиц запросов к тысячам. Как правильно организовать работу с БД, чтобы система оставалась стабильной и эффективной

🎙 Серия 5. Практика. Взаимодействие со смежными системами. Типичные сложности, с которыми сталкиваются команды при интеграции с внешними сервисами, и как их предотвратить или минимизировать

Смотрите проект, чтобы узнать, как создаются одни из крупнейших облачных сервисов в России:

⭐️ Наш сайт
⭐️ VK Видео
⭐️ Ютуб

Провели бенчмарк 96 комбинаций PostgreSQL 17 и 18 — и результаты местами неожиданны. 😕

В целом Postgres 18 показал себя лучше: заметные улучшения в производительности и несколько действительно интересных оптимизаций.

Подробности, результаты тестов и анализ: смотреть

👉 @SQLPortal

Не нужен весь набор данных — хватит и выборки? Тогда попробуй Postgres TABLESAMPLE.

В PostgreSQL есть встроенная функция для выборки случайных строк из таблицы. Например, чтобы взять примерно 10% строк, можно просто написать:

SELECT * FROM your_table TABLESAMPLE BERNOULLI (10);

Удобно, когда нужно быстро глянуть структуру или прикинуть распределение данных, не прогоняя весь объём.

👉 @SQLPortal

Главный совет для разработчиков, которые только начинают работать с Postgres: всегда задавай statement timeout.

ALTER DATABASE mydatabase
SET statement_timeout = '60s';

👉 @SQLPortal

Есть неагрегированные колонки, которые ты хочешь добавить в SELECT, но не в GROUP BY?

Можно использовать ANY_VALUE, например:

SELECT c1, ANY_VALUE(c2), COUNT(...)
FROM ...
GROUP BY c1

Функция вернёт значение из одной случайной строки (оптимизировано так, что обычно берётся первая). То есть результат — недетерминированный.

👉 @SQLPortal

Вместо удаления строк через DELETE многие приложения помечают их как удалённые, ставя флаг.

В Oracle SQL можно партиционировать по этому флагу, разделяя активные и неактивные данные.

Но есть тонкости: читать

👉 @SQLPortal

Удобный приём в psql, чтобы выгрузить данные из SQL в CSV:

SELECT * FROM customers \g (format=csv) ~/Downloads/clients.csv

Команда \g format отправляет результат запроса в указанный формат — в данном случае CSV.

Это работает не только с CSV, но и с другими форматами, например:

SELECT * FROM customers \g (format=html) ~/Downloads/clients.html

А для любителей математики — даже в LaTeX:

SELECT * FROM customers \g (format=latex) ~/Downloads/clients.tex

👉 @SQLPortal

20 SQL-вопросов среднего уровня (с подробными ответами)

1. Напиши SQL-запрос, чтобы найти вторую по величине зарплату.

SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

2. Как оптимизировать медленный SQL-запрос?

Используй подходящие индексы.
Избегай SELECT *, выбирай только нужные колонки.
Перепиши коррелированные подзапросы через JOIN.
Прогоняй EXPLAIN, чтобы понять план выполнения.
Разбей сложные запросы на CTE (WITH).

3. В чем разница между INNER JOIN и OUTER JOIN?

INNER JOIN: возвращает только совпадающие строки между таблицами.
OUTER JOIN: возвращает совпадающие строки и те, где нет совпадений (с NULL), бывает LEFT, RIGHT, FULL.

4. Напиши SQL-запрос, чтобы найти топ-3 отдела с самой высокой средней зарплатой.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC
LIMIT 3;

5. Как убрать дубликаты строк в SQL-запросе?

Можно использовать DISTINCT, либо ROW_NUMBER() внутри CTE и отфильтровать лишнее:

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name, department_id ORDER BY id) AS rn
  FROM employees
)
DELETE FROM cte WHERE rn > 1;

6. Напиши SQL-запрос, чтобы найти сотрудников с одинаковыми именами в одном отделе.

SELECT name, department_id
FROM employees
GROUP BY name, department_id
HAVING COUNT(*) > 1;

7. В чем разница между UNION и UNION ALL?

UNION: убирает дубликаты.
UNION ALL: оставляет все строки, включая дубликаты.

8. Напиши SQL-запрос, чтобы найти отделы без сотрудников.

SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

9. Как индексирование помогает ускорить запросы?

Индексы уменьшают объем данных, которые сканируются при фильтрации или джойнах.
Создавай индексы на колонках, часто используемых в WHERE, JOIN и ORDER BY.

10. Напиши SQL-запрос, чтобы найти сотрудников, работающих больше 5 лет.

SELECT *
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) > 5 * 365;

11. В чем разница между SUBQUERY и JOIN?

Subquery: подзапрос внутри другого запроса, может быть вложенным.
JOIN: объединяет строки из нескольких таблиц по связанным колонкам.

12. Напиши SQL-запрос, чтобы найти топ-2 продукта с наибольшими продажами.

SELECT product_id, SUM(sales) AS total_sales
FROM sales_table
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 2;

13. Как использование хранимых процедур может улучшить производительность запросов?

Они заранее компилируются и кешируются.
Снижают сетевой трафик, объединяя несколько операций в один вызов.
Повышают модульность и переиспользуемость.

14. Напиши SQL-запрос, чтобы найти клиентов, оформивших заказ, но не сделавших оплату.

SELECT o.customer_id
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE p.payment_id IS NULL;

15. В чем разница между GROUP BY и HAVING?

GROUP BY: группирует строки по одной или нескольким колонкам.
HAVING: фильтрует группы, как WHERE, но для агрегированных данных.

16. Напиши SQL-запрос, чтобы найти сотрудников, работающих в одном отделе со своим менеджером.

SELECT e.employee_id, e.name
FROM employees e
JOIN employees m ON e.department_id = m.department_id AND e.manager_id = m.employee_id;

17. Как использовать оконные функции для решения сложных запросов?

Применяй ROW_NUMBER(), RANK(), SUM() OVER(), LAG() и т. д.
Они позволяют выполнять вычисления по нескольким строкам без группировки.

18. Напиши SQL-запрос, чтобы найти топ-3 продукта с самой высокой средней ценой.

SELECT product_id, AVG(price) AS avg_price
FROM products
GROUP BY product_id
ORDER BY avg_price DESC
LIMIT 3;

19. В чем разница между TRUNCATE и DELETE?

TRUNCATE: удаляет все строки, быстрее, и чаще всего нельзя откатить.
DELETE: удаляет выбранные строки, поддерживает WHERE и откаты через транзакции.

👉 @SQLPortal

При создании индексов или частичных индексов важно понимать, какие значения встречаются чаще всего и как распределены данные по процентилям.

В PostgreSQL это можно проверить с помощью запроса к таблице pg_statistic.

👉 @SQLPortal

Нашёл мёд для тех, кто хочет освоить Git без нудных лекций.

Есть десктопное приложение под Mac, Windows и Linux, которое обучает работе с Git через практику.

Задания выполняются прямо в реальном Git и GitHub, репозитории создаются у вас в аккаунте и остаются навсегда.

Поддержка разных языков тоже в комплекте.

Ссылка: https://github.com/jlord/git-it-electron

👉 @SQLPortal

Функция generate_series в Postgres принимает три аргумента:

- стартовое значение
- конечное значение
- шаг (инкремент)

Пример:

SELECT * FROM generate_series(1, 10, 2);

Результат:

1  
3  
5  
7  
9

👉 @SQLPortal