Работа со строками в PostgreSQL — извлекаем данные с помощью регулярных выражений!

В аналитике часто нужно разобрать строку: вытащить домен из email, код из SKU, номер из текста. PostgreSQL предоставляет функции regexp_match и regexp_replace, позволяющие делать это напрямую в SQL.

Создадим таблицу:

CREATE TABLE users (
    id INT,
    email TEXT,
    profile_code TEXT
);

Извлечём домен из email:

SELECT 
    id,
    email,
    (regexp_match(email, '@(.+)$'))[1] AS domain
FROM users;

regexp_match возвращает один массив, и [1] достаёт первую группу. Паттерн @(.+)$ берёт всё, что стоит после символа @.

Вытащим числовую часть из кода профиля, например "USR-2391-A":

SELECT 
    id,
    profile_code,
    (regexp_match(profile_code, '([0-9]+)'))[1] AS numeric_part
FROM users;

Паттерн ([0-9]+) извлекает последовательность цифр.

Удалим всё кроме букв и цифр — удобно для нормализации входных данных:

SELECT 
    id,
    regexp_replace(profile_code, '[^A-Za-z0-9]', '', 'g') AS cleaned
FROM users;

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

➡️ SQL Ready | #практика

😎 На Хабре вышла полезная статья: «6 лайфхаков при внедрении СУБД: учимся на чужих граблях»!

В этой статье:
• Разберёте реальные ошибки при развёртывании СУБД;
• Узнаете, как повысить производительность запросов через правильное партицирование и не только;
• Поймёте, как организовать конкурентный доступ и обновления данных без блокировок и простоев;
• Получите шесть конкретных лайфхаков, которые помогут избежать критических проблем.

🔊 Продолжайте читать на Habr!

➡️ SQL Ready | #статья

✍️ LangShift — учись новому языку программирования, используя знания, которые у тебя уже есть!

Этот сайт предлагает другой путь: выбираешь язык, который уже знаешь, и переходишь на новый через сопоставление синтаксиса и парадигм. Более 80 модулей, 30+ проектов, всё бесплатно и без регистрации.

📌 Оставляю ссылочку: langshift.dev

➡️ SQL Ready | #ресурс

Keyset-пагинация: быстрый скролл без OFFSET!

OFFSET…LIMIT прост, но плохо масштабируется: чем дальше страница, тем медленнее запрос и выше риск дубликатов при вставках.
Keyset использует курсор (id/дату) и даёт стабильную скорость на больших объёмах.

Создаём таблицу (пример на PostgreSQL):

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Подготавливаем входящие данные с помощью CTE:

WITH cursor AS (
    SELECT 1000::BIGINT AS last_seen_id
)

Здесь мы храним «курсор» — id последней записи, которую клиент уже получил.

Получаем следующую страницу без OFFSET по keyset-подходу:

SELECT p.id, p.title, p.created_at
FROM posts p
JOIN cursor c ON TRUE
WHERE p.id < c.last_seen_id
ORDER BY p.id DESC
LIMIT 20;

Запрос отдаёт следующие 20 записей с id < last_seen_id.
На клиенте берём минимальный id из результата и используем его как новый last_seen_id для следующего запроса.

🔥 Подход работает в PostgreSQL, MySQL, SQL Server и др.: стабильно, эффективно и без проблем с дубликатами при конкурентных вставках.

➡️ SQL Ready | #практика

Как понять, какие индексы только тратят место?

Ненужные индексы замедляют вставки, обновления и VACUUM. PostgreSQL умеет показать, какие индексы ни разу не использовались.

Посмотрим статистику использования:

SELECT relname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

idx_scan = 0 — индекс ни разу не участвовал в плане.
size покажет, сколько места он занимает на диске.

Нужно увидеть “почти бесполезные” индексы? С сортировкой по минимальному использованию:

SELECT relname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 10;

🔥 Инструмент позволяет быстро уменьшить нагрузку,
ускорить записи и освободить место.

➡️ SQL Ready | #совет

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

Например, B+ Tree Index используется для быстрого поиска и сортировки, а Hash Index подходит для точных совпадений по ключу.

На картинке — 5 основных структур данных, на которых строятся индексы в современных СУБД.

Сохрани, чтобы не забыть!

➡️ SQL Ready | #ресурс

Почему Index Only Scan в PostgreSQL не всегда работает?

Если PostgreSQL не использует Index Only Scan, проблема часто не в запросе и не в самом индексе.

Index Only Scan работает только если страницы помечены как видимые в visibility map. Если этого нет PostgreSQL всё равно идёт в таблицу.

Проверьте план выполнения:

EXPLAIN ANALYZE
SELECT id FROM orders WHERE status = 'pending';

Если видите Index Scan, а не Index Only Scan, одна из частых причин в том, что visibility map не заполнена (при наличии подходящего covering index).

Исправляется простой командой:

VACUUM (ANALYZE) orders;

🔥 VACUUM помечает страницы как all-visible, и PostgreSQL может перестать читать таблицу.

➡️ SQL Ready | #совет

❤️ SQL-101 — руководство, помогающее освоить язык и укрепить базу тем, кто уже работает с БД.

В этом ресурсе собраны ключевые темы, которые нужны в работе: базовые запросы, фильтрация, JOIN, группировки, подзапросы, индексы, транзакции и основы оптимизации. Всё объяснено простым языком и дополнено примерами с упражнениями.

Оставляю ссылочку: Github 📱

➡️ SQL Ready | #репозиторий

Префиксные индексы в MySQL — ускоряем поиск по длинным строкам!

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

Создадим таблицу с длинным текстовым атрибутом — типичный кейс, где полный индекс был бы слишком тяжёлым:

CREATE TABLE documents (
    id INT PRIMARY KEY,
    doc_key VARCHAR(500) NOT NULL
);

Добавим префиксный индекс. Индексируются только первые 20 символов:

CREATE INDEX idx_doc_key_prefix
ON documents (doc_key(20));

Если фильтровать данные по фиксированному началу строки, MySQL использует префиксный индекс:

SELECT id
FROM documents
WHERE doc_key LIKE 'INV-2024-%';

Важно: индекс применяется только если шаблон начинается без ведущего %. Например, LIKE '%2024%' уже не сможет его использовать.

Пример с email — если полная индексация не нужна:

CREATE INDEX idx_email_prefix
ON users (email(16));

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

➡️ SQL Ready | #практика

Как избежать блокировок таблиц с помощью advisory locks в PostgreSQL!

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

Для этого PostgreSQL предоставляет advisory locks — логические блокировки, не привязанные к таблицам или строкам.

SELECT pg_advisory_xact_lock(42);

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

Ключ — это просто число. Можно использовать user_id, order_id, хеш или tenant_id.

SELECT pg_advisory_xact_lock(user_id);

🔥 Это превращает PostgreSQL в механизм распределённой синхронизации. После COMMIT или ROLLBACK блокировка снимается автоматически.

➡️ SQL Ready | #совет

📂 Напоминалка по структурам данных для экономии памяти и работы с большими данными!

Например, Bloom Filter позволяет быстро проверить, встречался ли элемент ранее, а HyperLogLog помогает оценить количество уникальных значений, не храня все данные целиком.

На картинке — 6 структур данных, которые стоит держать под рукой при проектировании backend-систем, аналитики и highload-сервисов.

Сохрани, чтобы не забыть!

➡️ SQL Ready | #ресурс

DISTINCT vs GROUP BY — выбираем правильный инструмент для удаления дублей!

В SQL часто нужно избавиться от повторяющихся строк: уникальные пользователи, товары, категории. Для этого используют DISTINCT и GROUP BY. Результат может выглядеть одинаково, но назначение и смысл у этих конструкций разные.

Представим таблицу заказов:

orders(id, customer_id, product_id)

Найдём всех уникальных клиентов, которые делали заказы:

SELECT DISTINCT customer_id
FROM orders;

DISTINCT удаляет дубликаты по всему набору выбранных колонок в результирующем наборе — без группировок и агрегаций.

Сделаем то же самое через GROUP BY:

SELECT customer_id
FROM orders
GROUP BY customer_id;

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

GROUP BY становится необходимым, когда появляются агрегаты.

Посчитаем количество заказов на каждого клиента:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id;

В этом запросе GROUP BY обязателен, потому что мы одновременно выбираем агрегат (COUNT(*)) и неагрегированное поле (customer_id).

Частая ошибка — смешивать DISTINCT и агрегаты без GROUP BY:

SELECT DISTINCT customer_id, COUNT(*)
FROM orders;

Такой запрос в стандартном SQL некорректен: неагрегированные поля должны присутствовать в GROUP BY. В зависимости от СУБД и режима он либо не выполнится, либо вернёт неопределённый результат.

Корректный вариант:

SELECT customer_id, COUNT(*) AS orders_count
FROM orders
GROUP BY customer_id;

🔥 Используй DISTINCT для простого удаления дублей, а GROUP BY — когда нужна агрегация, расчёты по группам или HAVING.

➡️ SQL Ready | #практика

Как корректно сравнивать значения с NULL?

Обычное сравнение может сломаться, когда в данных появляется NULL. В SQL выражение:

email <> 'admin@example.com'

не вернёт строки с email IS NULL — результат будет UNKNOWN.

Для корректного сравнения используйте IS DISTINCT FROM:

email IS DISTINCT FROM 'admin@example.com'

Оно работает так, как ожидаешь:
NULL ≠ любое значение
NULL = NULL

Результат всегда TRUE или FALSE (без UNKNOWN).

То же самое для проверки изменений:

old_value IS DISTINCT FROM new_value

🔥 Это инструмент не для синтаксиса, а для корректности данных.

➡️ SQL Ready | #совет