SELECT * FROM users LIMIT 100

SQL: "Не говори больше ничего, вот вся продакшен-база данных"

👉 @SQLPortal

JSON, вставка данных.

Ты можешь изменять существующие JSONB-объекты с помощью функции jsonb_set(). Она возвращает новый JSONB-объект, где значение установлено по указанному пути ключей (keypath). Если по этому пути не хватает промежуточных объектов, она их создаст.

👉 @SQLPortal

Колонарное хранилище vs построчное (row store) наглядно

Берем простую агрегацию и сравниваем, как она выполняется в row store и columnar store.

В row store данные лежат строками, поэтому движку приходится читать каждую строку целиком, чтобы из нее вытащить значение колонки age.

В columnar store данные лежат по колонкам, поэтому он читает сразу только значения колонки age, без трогания остальных полей и без лишнего I/O.

👉 @SQLPortal

Менеджер баз данных с редактором SQL и искусственным интеллектом: Dataflare

👉 @SQLPortal

Радим Марек: Введение в буферы в PostgreSQL

Про то, как PostgreSQL держит данные в памяти и почему от этого сильно зависит производительность

👉 @SQLPortal

SQL тренажеры для практики

- sql-academy.org
- sql-ex.ru
- schoolsw3.com
- SQL Fiddle
- sqltest.online
- Oracle LiveSQL
- stratascratch.com
- stepik.org (Интерактивный тренажер SQL)
- sql-practice.com
- pgexercises.com
- HackerRank
- sqlzoo.net

👉 @SQLPortal

SQL tip:

Обновление значений в таблице через коррелированный подзапрос.

👉 @SQLPortal

PostgreSQL по умолчанию не страхует тебя от забытых транзакций, idle-сессий и улетевших в бесконечность запросов: все это тихо накапливается, пока база не начинает казаться "залипшей".

Если мигрируешь на PostgreSQL, эти 5 параметров таймаутов критично настроить

👉 @SQLPortal

Индексация без простоя с CONCURRENTLY

Создание индекса может залочить таблицу, из-за чего она фактически становится недоступной и приложение/пользователи начинают получать ошибки.

Если добавить CONCURRENTLY, Postgres соберет индекс конкурентно, в фоне. Это займет чуть больше времени, зато пользователи вообще не заметят, что индекс строится.

👉 @SQLPortal

В Postgres 18 появилась новая фича для ограничений по временным диапазонам: WITHOUT OVERLAPS. Это прям то, что нужно для систем бронирований, и в целом заметно проще, чем прошлые решения.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE staff_schedule (
  staff_id int,
  shift_duration tstzrange,
  task_description text,
  PRIMARY KEY (staff_id, shift_duration WITHOUT OVERLAPS)
);

- создаёт индекс, который комбинирует range и int
- не даёт добавлять дублирующиеся временные записи

👉 @SQLPortal

Неиспользуемые индексы в PostgreSQL: риски, обнаружение и безопасное удаление

Индексы нужны, чтобы ускорять доступ к данным. Они позволяют PostgreSQL избегать full scan по таблице и заметно сокращают время выполнения запросов в read-heavy нагрузках.

По реальному прод-опыту мы видели, что грамотно спроектированные, точечные индексы могут ускорять запросы в 5 раз и больше, особенно на больших транзакционных таблицах.

Но индексы не бесплатны.

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

1. Почему большие неиспользуемые индексы со временем становятся проблемой

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

1.1. Медленнее INSERT, UPDATE и DELETE

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

1.2. Больше нагрузки на VACUUM и autovacuum

В индексах так же копятся мертвые tuple, как и в таблицах. Их тоже нужно вакуумить, из-за чего растут I/O и время работы vacuum.

1.3. Дольше окна обслуживания

Операции вроде VACUUM и REINDEX выполняются тем дольше, чем больше индексов и чем они крупнее.

1.4. Пустая трата диска и “засорение” кеша

Большие неиспользуемые индексы едят место на диске и могут вытеснять полезные данные из shared_buffers, снижая эффективность кеша.

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

2. Как безопасно удалить неиспользуемые индексы в PostgreSQL

Ниже чеклист в стиле “можно в прод”, который стоит пройти перед удалением любого индекса.

2.1. Проверь, когда в последний раз сбрасывали статистику

Если статистику недавно reset’нули, индекс может выглядеть “неиспользуемым”, хотя он нужен под нагрузкой.

SELECT
  datname,
  stats_reset
FROM pg_stat_database
WHERE datname = current_database();

Старый stats_reset (или NULL, то есть статистику никогда не сбрасывали) дает больше доверия метрикам использования индексов.

2.2. Проверь, не обслуживает ли индекс constraint

Большой индекс может быть “нулевым” по статистике, но удалять его нельзя, если он обеспечивает PRIMARY KEY, UNIQUE или FOREIGN KEY constraint.

PostgreSQL использует такие индексы для гарантий целостности и не даст их удалить, пока не удалишь сам constraint.

SELECT
  i.relname AS index_name,
  c.conname AS constraint_name,
  c.contype AS constraint_type,
  c.conrelid::regclass AS table_name
FROM pg_constraint c
JOIN pg_class i ON i.oid = c.conindid
WHERE i.relname = '<IDX_NAME>';

Если запрос вернул строки, этот индекс удалять нельзя.

2.3. Проверь статистику использования индекса

Так ты подтверждаешь, что планировщик реально не использовал индекс при выполнении запросов.

SELECT
  s.indexrelname AS index_name,
  s.relname AS table_name,
  s.idx_scan,
  s.idx_tup_read,
  s.idx_tup_fetch
FROM pg_stat_user_indexes s
WHERE s.indexrelname = '<IDX_NAME>';

Все счетчики должны быть 0.

3. Подготовь откат

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

SELECT pg_get_indexdef('<IDX_NAME>'::regclass) AS create_index_sql;

Сохрани результат как часть rollback-плана.

4. Удаляй безопасно

DROP INDEX CONCURRENTLY не блокирует чтение и запись по таблице, поэтому подходит для прода.

DROP INDEX CONCURRENTLY <IDX_NAME>;

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

5. Итог

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

Не полагайся на статистику в одиночку: проверяй constraints, понимай паттерны нагрузки и всегда готовь rollback.

В проде корректность и стабильность важнее скорости “уборки”.

👉 @SQLPortal

То, что Postgres в одиночку не тянет, он может закрывать вместе с DuckDB.

Расширение pg_duckdb показывает, как две базы могут работать в связке: аналитические данные крутятся в DuckDB, а потом джойнятся с транзакционными данными в Postgres. Фишка в том, что расширение встраивает DuckDB прямо внутрь Postgres. DuckDB буквально запускается внутри процесса Postgres.

Как это работает (см. диаграмму):

1. Приложение отправляет запрос в Postgres, который джойнит данные портфеля (Postgres) с историческими рыночными данными (lakehouse)
2. pg_duckdb видит доступ к lakehouse и делегирует выполнение DuckDB
3. DuckDB подтягивает нужные рыночные данные из lakehouse, читает таблицы Postgres и делает join
4. Postgres возвращает приложению финальный результат

👉 @SQLPortal

Postgres бывает реально коварным, когда в тексте намешан разный регистр. Для поиска и индексации такого текста есть расширение citext, оно в некоторых случаях помогает. И это поле/тип тоже можно индексировать обычным b-tree.

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

-- Подключаем расширение
CREATE EXTENSION IF NOT EXISTS citext;

-- Создаем таблицу с типом citext
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name CITEXT UNIQUE NOT NULL,
  description TEXT
);
``` :contentReference[oaicite:0]{index=0}
::contentReference[oaicite:1]{index=1}

👉 @SQLPortal

Правило SQL про GROUP BY, которое должен знать каждый

Если ты выбираешь в SELECT неагрегированные колонки, добавь их в GROUP BY. Это не просто хорошая практика, в некоторых СУБД без этого будет ошибка.

Почему это важно? Потому что GROUP BY определяет, что именно значит одна строка результата. Базе нужно понимать, какое единственное значение положить в каждую ячейку этой строки.

В примере выше одна строка = должность (job_title) внутри отдела (department).

Поэтому и department, и job_title обязаны быть в GROUP BY.

Если неагрегированную колонку не добавить:
❌часть баз сразу упадет с ошибкой
❌другие молча вернут случайные значения
❌запрос может "работать" сегодня и врать завтра

Общее правило: Каждая колонка в SELECT должна быть функционально зависима от GROUP BY. Если внутри группы у колонки может быть несколько значений, она не может появляться в SELECT, пока:

- ты не добавил ее в GROUP BY, или
- не завернул в агрегатную функцию.

👉 @SQLPortal

Совет по Postgres для SSD: поставь random_page_cost ниже. Значение по умолчанию 4.0 больше подходит для HDD (вращающихся дисков). На SSD можно опустить до 1.1 или 1.5, потому что случайные и последовательные чтения почти одинаковы по стоимости. Это помогает оптимизатору выбирать более удачные планы выполнения.

👉 @SQLPortal

Postgres продолжает развиваться в сторону gen AI. Тут наткнулся на два новых расширения.

VectorChord (vchord) это расширение для более дешевого и дисково-эффективного поиска похожих векторов. Обещают, что можно хранить 400 000 векторов всего за $1, и за счет этого получается ощутимая экономия: в 6 раз больше векторов по сравнению с оптимизированным хранилищем Pinecone и в 26 раз больше, чем у pgvector.

pgpu это еще одно расширение, которое умеет использовать NVIDIA GPU через CUDA, чтобы ускорять отдельные операции в базе и/или переносить их с CPU на GPU. Сейчас основной кейс это ускорение времени сборки индексного типа vchord с помощью GPU.

На диаграмме показано, как pgpu считает центроиды для индекса vchord с использованием GPU:

1. читаем данные из базы
2. считаем центроиды на GPU
3. пишем центроиды в таблицу Postgres
4. создаем индекс vchord, передавая таблицу с заранее посчитанными центроидами

👉 @SQLPortal

БОЛЬШОЙ SQL грех

Никогда не пихай ORDER BY внутрь CTE (или подзапроса), если там же рядом нет LIMIT.

Если LIMIT забыть, база обычно будет сортировать всю таблицу вообще без смысла.

👉 @SQLPortal

Быстрый sanity check: не забыли ли вы критичные индексы?

SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

Если видишь много seq scan и при этом почти нет idx_scan, значит таблицы часто читаются последовательным сканом и, скорее всего, где-то реально просится индекс.

👉 @SQLPortal

Не пиши в SQL = NULL или != NULL

Для NULL всегда используй IS NULL / IS NOT NULL

NULL это “нет значения / неизвестно”, и обычные операторы сравнения (=, !=, <, >) с ним не работают, потому что NULL не равен, не больше и не меньше вообще ничего.

= / != сравнивают значения. А NULL это не значение, а отсутствие значения. Поэтому IS / IS NOT проверяют именно наличие или отсутствие значения.

👉 @SQLPortal

warda bibi: Раскрываем высокую производительность PostgreSQL: ключевые оптимизации памяти

👉 @SQLPortal