Как B- и B+-деревья лежат в основе индексов в базах данных (например, InnoDB в MySQL), почему размер и тип первичного ключа (например, BIGINT против UUID) напрямую влияют на глубину дерева, порядок хранения и количество I/O, и как это отражается на скорости поиска и диапазонных запросов

https://planetscale.com/blog/btrees-and-database-indexes

👉 @SQLPortal

Тутор по ограничениям в Postgres — тык

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

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

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

👉 @SQLPortal

Мы часто обсуждаем с людьми тему «Почему именно Postgres?». Какие фичи делают его сильнее других баз. В этом списке почти всегда фигурирует индексируемый JSONB.

JSONB в Postgres изначально сделан для эффективных запросов и индексации

Вот отличный материал с обзором этих возможностей.

👉 @SQLPortal

SQL против Python для работы с данными: must-have для аналитиков и дата-сайентистов, которые прокачивают скиллы

👉 @SQLPortal

Используй правильный тип данных при определении колонок в базе данных. Например:

для чисел — numeric, для дат/времени — date или timestamp

Неправильный выбор типа данных приводит к неявным преобразованиям, что может:

- отключить использование индексов → замедлить SQL
- вызвать ошибки во время выполнения

Выбирай типы внимательно.

👉 @SQLPortal

Давай разберём типы сканов в Postgres. Многие из нас уже смотрели планы через EXPLAIN. Ниже — детали по разным видам сканов и схема, которая помогает понять, как они работают.

❖ Sequential Scan
Читает всю таблицу построчно и проверяет каждую строку на соответствие условиям запроса.

❖ Index Scan
Базовый индексный скан использует B-tree, чтобы быстро найти точное место данных. Это двухшаговый процесс: сначала Postgres находит запись в индексе, потом извлекает строку из таблицы.

❖ Bitmap Scan

Bitmap Index Scan. Сначала Postgres сканирует один или несколько индексов и строит в памяти «битмап» — карту страниц таблицы, где возможно находятся нужные строки.

Bitmap Heap Scan. Затем этот битмап используется для прохода по основной таблице. Важный момент — чтение нужных страниц идёт последовательно с диска, что обычно быстрее, чем случайные переходы при стандартном Index Scan.

❖ Parallel Sequential Scan
Postgres запускает несколько background worker’ов, чтобы одновременно сканировать одну большую таблицу. Таблица делится на части, каждая часть достаётся отдельному worker’у. В конце результаты собираются вместе через gather.

❖ Parallel Index Scan
Несколько worker’ов параллельно сканируют разные части одного B-tree индекса и возвращают совпавшие строки из heap. Worker’ы читают индекс по очереди.

❖ Index-Only Scan
Самый быстрый вариант: запрос полностью обслуживается данными, которые есть в индексе. Таблица вообще не трогается.

👉 @SQLPortal

Индексируй базу данных с осторожностью.

Каждый новый индекс приводит к:

(a) увеличению затрат на хранение (и, как следствие, дублированию данных)
(b) увеличению накладных расходов на INSERT

Общий принцип такой: индексы ускоряют чтение, но замедляют запись.

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

Хороший обзор этой темы есть у Маркуса в «Use the Index, Luke.»

👉 @SQLPortal

Для проверки видимости строк Postgres собирает все активные транзакции в массив, а строки сверяет с этим списком. Это становится частью снимка состояния транзакции.

Если строку создала одна из этих транзакций, она не видна.

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

Postgres сортирует этот массив и делает по нему бинарный поиск. Есть патчи, которые при необходимости переносят этот список в хеш-таблицу

👉 @SQLPortal

Вся жизнь перед глазами пронеслась

👉 @SQLPortal

Postgres 18 читает в 2–3 раза быстрее ⚡️

В Postgres 18 появляется крупное изменение в производительности чтения — новая фича async I/O (асинхронный ввод-вывод).

Что такое async I/O?

Когда Postgres читает данные с диска (не из shared buffer), нужен I/O для получения данных.
Синхронный I/O значит, что каждый запрос к диску ждёт завершения, прежде чем делать что-то ещё. Для нагруженных баз, особенно в облаке, это узкое место.

Асинхронный I/O позволяет воркерам использовать простои с пользой.

Запись в Postgres останется синхронной.

Аналогия с async I/O

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

Где async I/O применяется

- последовательные сканы
- bitmap heap scan (после bitmap index scan)
- часть обслуживания, например vacuum

Как включить?

Есть два варианта:

🔸io_method = worker
Shared worker-процессы синхронно загружают данные в shared buffers. Можно задать количество воркеров на инстанс. Поддерживается на всех платформах, где работает Postgres.

🔸io_method = io_uring
Для Postgres на Linux 5.1+ можно использовать системные вызовы io_uring. Тогда обращения выполняются самим backend-процессом, а не отдельными воркерами.

Также появится pg_aios — новый системный view для анализа данных async I/O.

👉 @SQLPortal

Это один из лучших AI-инструментов для разработчиков 🔥

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

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

Отлично подходит для прототипов и MVP.

Линк: database.build

👉 @SQLPortal

PostGIS 3.6 скоро выйдет, и в нём появилась новая функция: ST_CoverageClean

Приходилось работать с полигонами, которые почти идеально укладываются в мозаику, но остаются мелкие зазоры или пересечения? Это и есть проблема очистки покрытия.

В реальных данных покрытия встречаются повсюду: штаты, округа, земельные участки… Каждый полигон должен стыковаться с соседними без наложений и дыр. Чистые покрытия открывают новые возможности: быстрое объединение через ST_CoverageUnion, упрощение границ с помощью ST_CoverageSimplify и другие операции.

Теперь, с поддержкой очистки покрытий в GEOS, PostGIS может автоматически исправлять такие несовершенные данные с помощью ST_CoverageClean

CREATE TABLE polygons_clean AS (
  SELECT
    id,
    ST_CoverageClean(geom, gapMaximumWidth => 10000) OVER () AS geom
  FROM polygons
);

👉 @SQLPortal

Нравится контраст между буферизацией страниц данных в MySQL и Postgres. Подходы кардинально разные.

InnoDB в MySQL делает ставку на больший контроль. С innodb_flush_method='O_DIRECT' движок обходит большую часть накладных расходов на кэш страниц ОС и управляет всем сам. Объём кэша задаёт innodb_buffer_pool_size. На выделенных БД-серверах это часто 70%+ всей RAM.

Пул делится на несколько подэкземпляров. Каждый держит двухчастный список страниц LRU: young и old. Новые страницы попадают в old и должны «прожить» без вытеснения заданное время (innodb_old_blocks_time), прежде чем их пустят в young. Это защищает от thrashing при массовых операциях вроде table scan.

Postgres выбирает более простой внутренний кэш и полагается на кэш страниц ОС для загрузки/выгрузки. Размер задаёт shared_buffers. Часто ставят около 30% доступной RAM, оставляя место под кэш ОС.

Для вытеснения Postgres использует clock-sweep. У каждой страницы в shared_buffers есть счётчик: он увеличивается при обращениях (до лимита) и уменьшается при проходе «часов». Когда счётчик падает до 0, страницу можно заменить другой.

Отличный README из репозитория Postgres: ссылка

👉 @SQLPortal

Совет по SQL

Использование ORDER BY с несколькими столбцами

Когда вы пишете ORDER BY с несколькими колонками, база данных сначала сортирует все строки по первой колонке (в примере это sales). Это образует группы строк с одинаковым значением. Например, Mira и Bob попали в одну группу, так как у обоих значение продаж равно 12 000.

Основная сортировка по sales (по убыванию) дала ничью между Mira и Bob. Тогда в ход идёт вторая колонка. Сортировка по age (по возрастанию) ставит Mira (30 лет) перед Bob. Такой принцип применяется ко всем значениям, которые совпали на первом шаге.

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

👉 @SQLPortal

ByteByteGo выпустили наглядный разбор основных типов блокировок, которые используются в СУБД для управления конкурентным доступом к данным.

В списке:

• Shared Lock (S) — позволяет читать, но не менять данные
• Exclusive Lock (X) — блокировка для чтения и изменения
• Update Lock (U) — предотвращает взаимные блокировки при обновлениях
• Schema Lock — защита структуры базы
• Bulk Update Lock (BU) — оптимизация при массовых вставках
• Key Range Lock — предотвращает фантомные записи в индексах
• Row-Level Lock — блокировка на уровне строки
• Page-Level Lock — блокировка страницы данных
• Table-Level Lock — блокировка всей таблицы

👉 @SQLPortal