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

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

(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

Совет по Postgres: не всегда нужно гонять SELECT COUNT(*)

Можно посмотреть внутреннюю статистику таблиц:

SELECT reltuples::numeric as count
FROM pg_class
WHERE relname = 'table_name';

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

👉 @SQLPortal

В Postgres 18 подъезжает приятное обновление: теперь при использовании pg_dump или pg_upgrade можно сохранять статистику таблиц 🎉

Это значит, что больше не нужно гонять долгий ANALYZE после апгрейда или дампа с восстановлением. Для тестов и разработки можно будет сразу брать статистику из продакшена. В pg_dump добавили опции -statistics-only и -no-statistics, так что можно самому решать, включать ли статистику.

Небольшое, но реально полезное улучшение, которое упростит жизнь многим разработчикам

👉 @SQLPortal

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

Подробнее об этом подходе тут

👉 @SQLPortal

Функции нумерации строк в SQL:

ROW_NUMBER — последовательные целые числа, начиная с единицы.

RANK — строки с одинаковым значением сортировки получают один и тот же номер; следующая строка после «ничьей» получает значение так, как если бы использовался ROW_NUMBER

DENSE_RANK — работает как RANK, но нумерация идёт подряд без пропусков после «ничьих».

👉 @SQLPortal

Google представил новый язык программирования — Mangle, предназначенный для работы с базами данных. 😐

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

Репозиторий проекта доступен здесь: github.com/google/mangle

👉 @SQLPortal

Проверь соответствие безопасности своей Oracle Database с помощью Database Security Assessment Tool (DBSAT) v4.0.

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

Об этом рассказывает Pedro Lopes.

https://blogs.oracle.com/database/post/dbsat40

👉 @SQLPortal

JOIN против UNION в Postgres:

JOIN нужен для объединения столбцов ➡️
UNION нужен для объединения строк ⬇️

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

SELECT employees.name, departments.location
FROM employees
JOIN departments
ON employees.dept_id = departments.id;

✤ UNION складывает результаты двух и более SELECT в один набор строк. Это про объединение данных по вертикали. Таблицы должны иметь одинаковое количество столбцов и совместимые типы данных. Пример:

SELECT city
FROM customers
UNION
SELECT city;

👉 @SQLPortal

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

В строковом варианте, как в Postgres или MySQL, все значения одной строки лежат рядом на диске.

Такой подход хорошо работает для транзакционных систем, где запросы чаще всего достают одну или несколько строк целиком, например SELECT * FROM camera WHERE model = 'A7R'. Здесь выгодно то, что обычно обрабатывается не так много строк, но при этом почти всегда требуется сразу много полей.

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

Если нужно обработать миллионы строк, но использовать только одно поле, например в запросе SELECT avg(price) FROM camera, то колонковая база читает только нужный столбец, а строковой пришлось бы загружать каждую строку целиком.

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

Но по мере роста нагрузки обычно выбирают разные решения: Postgres или MySQL для OLTP и отдельные колонночные хранилища для аналитики и бизнес-отчётов. В итоге выбор всегда зависит от задачи, и лучший вариант — подбирать инструмент под конкретный сценарий.

👉 @SQLPortal

Знал ли ты, что можно пользоваться psql — консольным клиентом Postgres без установки всего PostgreSQL?

Да-да, это реально. Благодаря libpq можно подключаться к Postgres без того, чтобы тащить весь пакет целиком.

Для macOS это делается так:

brew install libpq

(на Linux — что-то вроде apt-get install postgresql-client).

После установки появится сообщение вроде такого, и нужно будет добавить ещё один шаг, чтобы правильно прописать путь:

==> libpq
libpq is keg-only, which means it was not symlinked into /opt/homebrew,  
because it conflicts with PostgreSQL.
If you need to have libpq first in your PATH, run:
echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> /Users/.bash_profile

После этого можно подключаться к удалённому хосту через стандартную строку подключения:

psql postgres://{username}:{password}@{hostname}:{port}/{database-name}

👉 @SQLPortal