Базы Данных (Data Base) | SQL & NoSQL. PostgreSQL, MySQL и Big Data. Уроки для Backend и Data Engineer. Архитектура БД, Оптимизация и Design.

❌ Антипаттерн: Хранить даты и время в VARCHAR

Встречали такое?

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_date VARCHAR(20)
);

На первый взгляд — всё ок: дата есть, строка хранит. Но на практике — сплошные проблемы:

🔴 Нет гарантии формата
'2024-12-01', '12/01/2024', '01.12.24', 'вчера' — всё ляжет, но работать с этим потом боль.

🔴 Сложность фильтрации и сортировки
Сравнение строк ≠ сравнение дат.
Запросы типа WHERE order_date > '2024-01-01' могут вести себя непредсказуемо.

🔴 Нельзя использовать функции времени
Ни DATE_TRUNC, ни AGE(), ни агрегаты по времени не работают нормально с VARCHAR.

✅ Как правильно
Используйте типы DATE, TIMESTAMP, TIMESTAMPTZ — они:

* валидируют данные на вставке;
* дают мощный инструментарий для анализа;
* упрощают работу с часовыми поясами и интервалами.

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_date TIMESTAMPTZ DEFAULT now()
);

💡 Если данные приходят в виде строк — парси их при загрузке, а не храни как есть.

Сохрани, чтобы не наступить на эти же грабли ☝️

#db

👉 @database_info

🔎 Мини-гайд: Индексы в PostgreSQL — быстро и по делу

Индексы — главный инструмент для ускорения запросов. Но неправильное использование может только навредить.

Основные типы индексов в PostgreSQL:
- B-tree — по умолчанию. Идеален для поиска по равенству и диапазону (=, <, >, BETWEEN).
- Hash — только для поиска по точному равенству (=). Становится актуальным реже.
- GIN — для массивов, JSONB, полнотекстового поиска.
- GiST — геоданные, поиск по диапазонам, сложные типы.
- BRIN — для очень больших таблиц с упорядоченными данными (например, логи).

Практические советы:
- Не злоупотребляй индексами: каждый индекс замедляет INSERT/UPDATE/DELETE.
- Следи за актуальностью: периодически проверяй и удаляй неиспользуемые (pg_stat_user_indexes поможет).
- Составные индексы ((col1, col2)) эффективны, только если условия WHERE учитывают порядок колонок.
- Используй EXPLAIN ANALYZE, чтобы понять, работает ли индекс в реальности.

Типичная ошибка:
Создать индекс на всё подряд без анализа запросов. Итог — тормоза на записи и огромный размер базы.

✅ Индексы — это как специи: мало — пресно, много — несъедобно.


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

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

#db

👉 @database_info

SQL Important Queries

Сохрани на потом, тебе пригодится👌

#db

👉 @database_info

Сегодня я хочу рассказать вам про одну часто недооцененную фишку в PostgreSQL - partial indexes (частичные индексы).

Обычно мы создаём индексы на всю таблицу, но что если нам нужно ускорить только небольшую часть данных? Например, часто выбираются только активные пользователи (status = 'active'). Вместо полного индекса можно создать индекс только для нужного поднабора данных:

CREATE INDEX idx_active_users
ON users (last_login)
WHERE status = 'active';

Что это даёт:
- Индекс меньше по размеру → быстрее поиск и обновление.
- Используется только тогда, когда запрос соответствует условию status = 'active'.
- Меньше нагрузка на диск при обновлениях таблицы.

🛠 Где это реально помогает:
- Таблицы с миллионами записей, где активно работают только с частью строк.
- Сценарии "горячих" и "холодных" данных.

Рекомендую попробовать partial indexes там, где обычные индексы слишком тяжелы или тормозят обновления!

#db

👉 @database_info

🚀 Сегодня я покажу вам один из моих любимых хаков для PostgreSQL – генерация серий дат без циклов и хранимок. Это идеальный способ быстро собрать таймлайн для аналитики или отчётов.

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

Вот как это делается с помощью generate_series:

SELECT generate_series(
date_trunc('day', current_date) - interval '30 days',
date_trunc('day', current_date),
interval '1 day'
) AS day;

💡 Результат — 31 строка с датами от 30 дней назад до сегодняшнего дня.

Теперь добавим, например, LEFT JOIN к таблице events, чтобы увидеть активность по дням:

SELECT
d.day,
COUNT(e.id) AS events_count
FROM
generate_series(
date_trunc('day', current_date) - interval '30 days',
date_trunc('day', current_date),
interval '1 day'
) AS d(day)
LEFT JOIN events e ON date_trunc('day', e.created_at) = d.day
GROUP BY d.day
ORDER BY d.day;

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

Пользуетесь ли вы generate_series? А может быть, используете что-то подобное в других СУБД? Делитесь в комментариях👇

#db

👉 @database_info

⚡️ Совет по работе с базами данных 💡

Уникальные индексы с исключением определенных строк

Создание уникальных индексов в некоторых случаях невозможно из-за дублирования значений - например, в строках, помеченных как «мягко удаленные» (soft-deleted). Исключив такие строки из индекса, можно корректно настроить ограничение уникальности. В MySQL частичные уникальные индексы (unique partial indexes) требуют эмуляции.


В современных базах данных часто используется паттерн Soft Delete, когда данные не удаляются физически, а помечаются флагом is_deleted = true.

Если вы хотите, чтобы поле email было уникальным только для активных пользователей, обычный уникальный индекс выдаст ошибку при попытке регистрации нового пользователя с почтой, которая уже есть в «корзине». Использование частичного индекса решает эту проблему, позволяя игнорировать помеченные на удаление записи.

Нюанс для MySQL:
В отличие от PostgreSQL или SQL Server, MySQL не поддерживает синтаксис WHERE внутри команды CREATE INDEX. Чтобы добиться такого же поведения, разработчики обычно используют:

• Виртуальные колонки (Generated Columns): создается колонка, которая принимает значение только если запись активна, и на нее вешается уникальный индекс.
• Составные индексы: включение флага удаления или временной метки в сам индекс.

#db

👉 @database_info

Как быстро найти “тяжёлые” запросы в PostgreSQL

Сегодня покажу простой способ найти самые ресурсоёмкие запросы, которые прямо сейчас выполняются в PostgreSQL. Это помогает, когда база начинает “тормозить”, а понять почему - сложно.

Используем pg_stat_activity и pg_stat_statements. Но сначала убедись, что pg_stat_statements включён:

-- Проверка:
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- Включение (если не установлен):
CREATE EXTENSION pg_stat_statements;

Теперь сам запрос на поиск “тяжёлых” запросов:

SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT 5;

А если интересует то, что прямо сейчас выполняется — тогда так:

SELECT
pid,
now() - query_start AS duration,
state,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
ORDER BY
duration DESC;

Я часто сохраняю эти запросы в отдельный .sql-файл, чтобы запускать сразу при проблемах с производительностью. Полезно добавить в .psqlrc алиас или даже обернуть в скрипт.

Как вы ищете “тяжёлые” запросы в проде? Поделитесь в комментариях.

#db

👉 @database_info

🚀 Сегодня покажу, как быстро диагностировать «тормоза» в PostgreSQL - без всяких внешних тулов и дополнительных логов. Только pg_stat_activity и немного здравого смысла.


Пользователи жалуются - "всё тормозит". Как понять, что именно? Открываем сессию в psql от суперпользователя и запускаем:

SELECT pid, state, wait_event_type, wait_event, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

📌 Что это нам даёт:
- Видим все активные (и зависшие) запросы.
- Сколько времени они уже выполняются (duration).
- На чём конкретно «висят»: CPU, IO, Lock, Client и т.д. (wait_event_type + `wait_event).

Пример:
wait_event_type: Lock
wait_event: relation→ Сразу ясно: кто-то держит блокировку на таблицу, и все остальные ждут.


🔥Чтобы найти виновника, можно запустить:

SELECT blocked_locks.pid AS blocked_pid,
blocking_locks.pid AS blocking_pid,
blocked_activity.query AS blocked_query,
blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
AND blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database
AND blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
AND blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page
AND blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
AND blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
AND blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
AND blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
AND blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Этот запрос покажет, кто кого блокирует, и с каким запросом.

🙌 Это простая, но мощная техника диагностики. Помогала мне не раз в проде - особенно, когда времени мало, а багов много.

#db

👉 @database_info

🧩 Как сделать backup PostgreSQL с минимальной нагрузкой на прод?

Сегодня покажу один из самых эффективных способов бэкапа PostgreSQL — с помощью pg_basebackup + реплики.

Сценарий: у нас есть продовый PostgreSQL и настроенная горячая реплика (streaming replication). Зачем использовать реплику для бэкапа?

Причины:
- 💡 На проде бэкап может замедлить отклик приложения.
- 🔁 Реплика — отличный способ разгрузить основной сервер.
- ⏱ Бэкап с pg_basebackup возможен только на стопнутой БД или через репликацию.

Как сделать:
pg_basebackup -h replica.host -U repl_user -D /backup/pg -F tar -z -P

Пояснения:
- -h — адрес реплики
- -U — пользователь с правами репликации
- -D — куда класть бэкап
- -F tar -z — формат архива и сжатие
- -P — прогресс в консоли

Важно:
Пользователь repl_user должен быть прописан в pg_hba.conf и иметь роль REPLICATION.

А если добавить в cron, то получишь стабильный ночной бэкап без боли.

#db

👉 @database_info

🎯 Сегодня покажу простой способ ускорить запросы в PostgreSQL, даже не трогая сам SQL-код.

Часто вижу, как разработчики и админы оптимизируют запросы, играя с индексами или переписывая JOIN'ы. Но забывают про один мощный инструмент — ANALYZE.

ANALYZE обновляет статистику по таблицам. Эта статистика — хлеб для планировщика запросов. Если она устарела, PostgreSQL может выбрать неэффективный план, даже если у вас всё индексировано как надо.

👨‍🔧 Простой пример:

ANALYZE my_big_table;

Запускаешь — и вдруг сложный JOIN срабатывает в разы быстрее. Потому что PostgreSQL теперь знает, какие там объемы данных, сколько уникальных значений в колонках и т.п.

🧠 Совет: если ты регулярно заливаешь данные в таблицы (например, через ETL или бэкапы) — добавь ANALYZE в конец процедуры. Это дёшево, но может дать мощный прирост производительности.

Можно даже так:
VACUUM ANALYZE my_big_table;
Так ты и "мусор" уберёшь, и статистику обновишь за один проход.

#db

👉 @database_info

🧩 Сегодня покажу вам простой, но крайне полезный приём, как находить “тяжёлые” запросы в PostgreSQL, которые тормозят базу.

📌 Если у вас база под нагрузкой, и “что-то всё стало медленно”, первым делом проверьте:

SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 5;

Этот запрос показывает топ-5 самых долгих активных запросов. Обратите внимание на query_start - именно он поможет понять, кто завис и тормозит остальных.

А если хотите посмотреть историю медленных запросов за последние часы/дни - подключайте pg_stat_statements:

SELECT
calls,
total_time,
mean_time,
query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

🔍 Тут видно, какие запросы в сумме "съели" больше всего времени. И это гораздо честнее, чем смотреть только на mean_time или calls по отдельности.

💡Совет: подключите pg_stat_statements на проде и делайте такой анализ хотя бы раз в неделю. Это поможет находить проблемные места в приложении до того, как начнётся пожар.

#db

👉 @database_info

За год число вакансий с требованиями ИИ-навыков значительно выросло.

Нейросети становятся рабочим инструментом для специалистов в маркетинге, IT, финансах, дизайне, клиентском сервисе и других сферах.

У Яндекс Практикума есть большая линейка курсов по ИИ для разных задач:

→ нейросети для работы и повседневной жизни;
→ создание AI-агентов и автоматизация процессов;
→ разработка и вайбкодинг с помощью ИИ;
→ применение нейросетей в маркетинге, аналитике и других профессиях.

Начать можно бесплатно: у многих программ есть вводный модуль, а часть курсов полностью доступна без оплаты. Промокод PRACT10SEPT дает скидку 10% на покупку курса.

→ Найти свой ИИ-трек

Erid: 2SDnjeZyn4j
Название: ООО "ЯНДЕКС"
ИНН: 7736207543

Сегодня расскажу вам про одну часто недооценённую, но крайне полезную SQL-фишку - CROSS APPLY в SQL Server (и его аналог в других СУБД - LATERAL).

Когда обычный JOIN бессилен

Допустим, у нас есть таблица Orders, и мы хотим для каждой строки выбрать топ-1 продукт по сумме, но выборка зависит от строки - тут уже обычный JOIN не справится. Вот пример, где приходит на помощь CROSS APPLY:

SELECT
o.OrderID,
p.ProductName,
p.Amount
FROM Orders o
CROSS APPLY (
SELECT TOP 1 *
FROM Products p
WHERE p.OrderID = o.OrderID
ORDER BY p.Amount DESC
) p;

Что делает CROSS APPLY?

Он буквально говорит: «Для каждой строки из Orders выполни подзапрос с её параметрами». Это похоже на foreach, где внутренняя выборка может меняться в зависимости от строки внешней таблицы.

Аналог в PostgreSQL:

SELECT
o.order_id,
p.product_name,
p.amount
FROM orders o,
LATERAL (
SELECT *
FROM products p
WHERE p.order_id = o.order_id
ORDER BY p.amount DESC
LIMIT 1
) p;


🔥 Используйте CROSS APPLY, когда:
- Нужна подстрочная логика внутри запроса
- Не получается реализовать через обычный JOIN
- Вы работаете с функциями, которые возвращают таблицу (TVF)

#db

👉 @database_info

📊 Зачем DBA нужно уметь читать планы выполнения запросов (EXPLAIN)?

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

Когда приходит запрос от разработчика: "Почему тормозит?" - ты открываешь EXPLAIN (ANALYZE, BUFFERS) и видишь:

Seq Scan on users (cost=0.00..44231.00 rows=1000000 width=64)
Filter: (status = 'active')

И тут всё понятно: фильтрация идёт по колонке без индекса, Postgres делает полный проход по таблице. Один CREATE INDEX - и запрос летит 🚀

Но не всё так просто. Иногда план говорит:

Index Scan using idx_users_status on users
Index Cond: (status = 'active')

А запрос всё равно медленный. Почему?

➡️ Buffers: shared hit=5 read=100000 dirtied=0 - вот оно. Индекс-то используется, но данные не в кэше, приходится читать с диска. А диск медленный. Решение? Подумать о горячем кэше, пачке RAM или REINDEX, если индекс раздулся.

Каждый EXPLAIN - как рентген. Не читаешь - лечишь наугад.

#db

👉 @database_info

⚡️ 40 000 000 ₽ ждут, пока ты дочитаешь этот пост

Если ты пишешь код, рисуешь интерфейсы или анализируешь данные до трёх ночи — у нас для тебя новость. Стартовала регистрация на конкурс «Лидеры цифровой трансформации», и призовой фонд там не шуточный —40 000 000 ₽ 🔥

🥇 1 место — 1 000 000 ₽
🥈 2 место — 600 000 ₽
🥉 3 место — 400 000 ₽

Что ещё:
💼 20 живых задач Правительства Москвы и бизнеса: Positive Technologies, «Газпромбанк.Тех», билайн бизнес, Yandex Cloud, VK Tech, РСХБ, «Фалькон Тех», ГК «Геоскан», ИТ Школа Ростелекома, ОЭЗ «Алабуга».
🧠 14 дней на разработку вашего решения
🪩 в конце — молодежный технологический фестиваль с награждением, нетворкингом, интерактивными стендами и крутой программой

Кто может участвовать?
👉🏻 Специалисты от 16 лет в любом цифровом направлении — ИИ-инженеры, разработчики, специалисты по обработке и анализу данных, аналитики, проджект-менеджеры, дизайнеры….

Собирай свою команду профи и принимай вызов!
Кто знает, возможно, эти призовые деньги ждут именно тебя 😉

📲 Регистрация тут:
https://i.moscow/lct?utm_source=sp&utm_medium=social&utm_campaign=database_info/?erid=2VtzqwHJYYU
До 14 сентября — не пропусти!

💡7 обязательных стратегий для масштабирования вашей базы данных.

1 - Индексация:
Проверьте шаблоны запросов вашего приложения и создайте подходящие индексы.

2 - Материализованные представления:
Предварительно вычислите результаты сложных запросов и сохраните их для быстрого доступа.

3 - Денормализация:
Уменьшите количество сложных соединений (join), чтобы улучшить производительность запросов.

4 - Вертикальное масштабирование:
Увеличьте мощность вашего сервера базы данных, добавив больше ЦП, оперативной памяти или хранилища.

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

6 - Репликация:
Создайте реплики вашей основной базы данных на разных серверах для масштабирования чтений.

7 - Шардинг:
Разделите таблицы базы данных на более мелкие части и распределите их по серверам. Используется для масштабирования как записей, так и чтений.

#db

👉 @database_info

SQL JOINs наглядно: как работать с объединением таблиц

Хотите лучше понимать SQL JOIN? Вот наглядная шпаргалка с примерами и визуализацией!

🔹 INNER JOIN – пересечение двух таблиц, возвращает только совпадающие строки.

SELECT *
FROM A
INNER JOIN B ON A.key = B.key;

🔹 FULL JOIN – объединяет все данные из обеих таблиц, заполняя пропущенные значения NULL.

SELECT *
FROM A
FULL JOIN B ON A.key = B.key;

🔹 FULL JOIN с фильтрацией NULL – выбирает только строки, которые есть только в одной из таблиц.

SELECT *
FROM A
FULL JOIN B ON A.key = B.key
WHERE A.key IS NULL OR B.key IS NULL;

🔹 LEFT JOIN – возвращает все строки из A и совпадающие строки из B.

SELECT *
FROM A
LEFT JOIN B ON A.key = B.key;

🔹 LEFT JOIN (только уникальные в A) – возвращает только строки из A, которых нет в B.

SELECT *
FROM A
LEFT JOIN B ON A.key = B.key
WHERE B.key IS NULL;

🔹 RIGHT JOIN – аналогично LEFT JOIN, но с приоритетом B.

SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key;

🔹 RIGHT JOIN (только уникальные в B) – выбирает строки, которые есть в B, но отсутствуют в A.

SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key
WHERE B.key IS NULL;

Сохраняйте в закладки и пользуйтесь! ⚡

#db

👉 @database_info