Базы данных (Data Base)

❌ Антипаттерн: Хранить даты и время в 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()
);

💡 Если данные приходят в виде строк — парси их при загрузке, а не храни как есть. Сохрани, чтобы не наступить на эти же грабли ☝️ А как у вас хранят даты? 📲 Мы в MAX #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, чтобы понять, работает ли индекс в реальности. Типичная ошибка: Создать индекс на всё подряд без анализа запросов. Итог — тормоза на записи и огромный размер базы. ✅ Индексы — это как специи: мало — пресно, много — несъедобно. Вывод: Хотите быструю базу — планируйте индексацию так же внимательно, как сами запросы. Сохрани, чтобы не забыть! 📲 Мы в MAX #db 👉 @database_info

SQL Important Queries Сохрани на потом, тебе пригодится👌 📲 Мы в MAX #db 👉 @database_info

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

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

Что это даёт: - Индекс меньше по размеру → быстрее поиск и обновление. - Используется только тогда, когда запрос соответствует условию status = 'active'. - Меньше нагрузка на диск при обновлениях таблицы. 🛠 Где это реально помогает: - Таблицы с миллионами записей, где активно работают только с частью строк. - Сценарии "горячих" и "холодных" данных. Рекомендую попробовать partial indexes там, где обычные индексы слишком тяжелы или тормозят обновления! 📲 Мы в MAX #db 👉 @database_info

Новые возможности – рядом! Теперь каждый может протестировать бесплатную «коробочную» версию объектного хранилища «S3 Архипелаг» 👨🏻‍💻 🚪Бесплатная версия снижает порог входа для проектов. Что нужно, чтобы начать? Просто скачайте дистрибутив, установите на свои серверы и приступите к работе сразу после авторизации через корпоративный SSO. Готово! Сам «S3 Архипелаг» от Диасофт – российское S3-совместимое хранилище для работы с петабайтами данных в архитектурах Data Lakehouse, аналитических системах и консолидации разрозненных файловых хранилищ. Оставляем ссылку на «S3 Архипелаг» 🔗 #реклама О рекламодателе

🚀 Сегодня я покажу вам один из моих любимых хаков для 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? А может быть, используете что-то подобное в других СУБД? Делитесь в комментариях👇 📲 Мы в MAX #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): создается колонка, которая принимает значение только если запись активна, и на нее вешается уникальный индекс. • Составные индексы: включение флага удаления или временной метки в сам индекс. 📲 Мы в MAX #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 алиас или даже обернуть в скрипт. Как вы ищете “тяжёлые” запросы в проде? Поделитесь в комментариях. 📲 Мы в MAX #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;

Этот запрос покажет, кто кого блокирует, и с каким запросом. 🙌 Это простая, но мощная техника диагностики. Помогала мне не раз в проде - особенно, когда времени мало, а багов много. Ты пользуешься pg_stat_activity в проде? Или сразу лезешь в лог? Расскажи в комментах! 📲 Мы в MAX #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, то получишь стабильный ночной бэкап без боли. 📲 Мы в MAX #db 👉 @database_info

🎯 Сегодня покажу простой способ ускорить запросы в PostgreSQL, даже не трогая сам SQL-код. Часто вижу, как разработчики и админы оптимизируют запросы, играя с индексами или переписывая JOIN'ы. Но забывают про один мощный инструмент — ANALYZE. ANALYZE обновляет статистику по таблицам. Эта статистика — хлеб для планировщика запросов. Если она устарела, PostgreSQL может выбрать неэффективный план, даже если у вас всё индексировано как надо. 👨‍🔧 Простой пример:

ANALYZE my_big_table;

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

VACUUM ANALYZE my_big_table;

Так ты и "мусор" уберёшь, и статистику обновишь за один проход. 📲 Мы в MAX #db 👉 @database_info

Этот файл был удален пользователем Встречаемся в Москве с теми, кто отвечает за хранение данных бизнеса. Обсудим, как решить задачу со звездочкой — уберечь массивы файлов и резервных копий от атак шифровальщиков, утечки или случайной перезаписи. На бизнес-ужине эксперты провайдера ИТ-инфраструктуры Selectel расскажут: ➕как устроено хранилище S3 под капотом; ➕какие угрозы данным существуют и как выстроить многослойную систему защиты; ➕почему защита данных — это не статья расходов, а управление рисками. Будет актуально руководителям в ИТ-компаниях, старшим архитекторам, системным администраторам. Поговорим и про стратегию хранения, и про реализацию. 📆 24 сентября (чт), 18:30 📍Москва, м. Динамо ⏩Участие бесплатное, дождитесь подтверждения заявки. Смотрите полную программу и регистрируйтесь: https://slc.tl/pophe Реклама. АО "Селектел". erid:2W5zFG59449

🧩 Сегодня покажу вам простой, но крайне полезный приём, как находить “тяжёлые” запросы в 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 на проде и делайте такой анализ хотя бы раз в неделю. Это поможет находить проблемные места в приложении до того, как начнётся пожар. 📲 Мы в MAX #db 👉 @database_info

Сегодня расскажу вам про одну часто недооценённую, но крайне полезную 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) 📲 Мы в MAX #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 - как рентген. Не читаешь - лечишь наугад. 📲 Мы в MAX #db 👉 @database_info

💡7 обязательных стратегий для масштабирования вашей базы данных. 1 - Индексация: Проверьте шаблоны запросов вашего приложения и создайте подходящие индексы. 2 - Материализованные представления: Предварительно вычислите результаты сложных запросов и сохраните их для быстрого доступа. 3 - Денормализация: Уменьшите количество сложных соединений (join), чтобы улучшить производительность запросов. 4 - Вертикальное масштабирование: Увеличьте мощность вашего сервера базы данных, добавив больше ЦП, оперативной памяти или хранилища. 5 - Кэширование: Сохраните часто запрашиваемые данные в более быстром слое хранения, чтобы снизить нагрузку на базу данных. 6 - Репликация: Создайте реплики вашей основной базы данных на разных серверах для масштабирования чтений. 7 - Шардинг: Разделите таблицы базы данных на более мелкие части и распределите их по серверам. Используется для масштабирования как записей, так и чтений. 📲 Мы в MAX #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; Сохраняйте в закладки и пользуйтесь! ⚡ 📲 Мы в MAX #db 👉 @database_info

Визуализация SQL-запросов Ментальная модель, помогающая представить, как выполняются SQL-запросы. Фактическая последовательность выполнения может отличаться от этой модели из-за стратегий оптимизации, применяемых оптимизатором запросов. 📲 Мы в MAX #db 👉 @database_info

🔥 Оптимизация индексов: частая ошибка DBA 🔥 Сегодня разберём распространённую ошибку, которую совершают многие администраторы баз данных — избыточные индексы. 💡Проблема Добавление индексов — это полезно, но если их становится слишком много, то база данных начинает тормозить при вставке, обновлении и удалении данных. Почему? Потому что каждый индекс требует дополнительного обслуживания при изменениях в таблице. 💡Пример ошибки Представим таблицу orders:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL
);

Допустим, мы добавляем индексы:

CREATE INDEX idx_customer ON orders(customer_id);
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_customer_order_date ON orders(customer_id, order_date);

На первый взгляд, всё логично, но есть проблема: индекс idx_customer_order_date покрывает оба предыдущих индекса! 💡Как исправить? Можно удалить idx_customer и idx_order_date, так как составной индекс (idx_customer_order_date) способен выполнять их работу. 📌 Как проверить ненужные индексы? 1️⃣ В PostgreSQL:

SELECT indexrelid::regclass, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

2️⃣ В MySQL:

SHOW INDEX FROM orders;

Здесь ищем индексы, которые дублируют друг друга. Вывод: Чем меньше избыточных индексов — тем быстрее работает ваша база данных. Проверьте свои индексы прямо сейчас! 📲 Мы в MAX #db 👉 @database_info

🔥 Оптимизация запросов: Как убрать тормоза в SQL? Сейчас покажу вам, как ускорить медленный SQL-запрос, который выполняется слишком долго. Если у вас в проекте есть запросы, которые выполняются секундами, а не миллисекундами, пора что-то менять! 🚀 Разбор примера Допустим, у нас есть такой запрос:

SELECT * 
FROM orders 
WHERE customer_id = 123 
ORDER BY order_date DESC;

Кажется простым, но выполняется медленно. В чём может быть проблема? 📌 Основные причины тормозов: 1️⃣ Нет нужного индекса – если customer_id или order_date не индексированы, база будет делать полный скан таблицы. 2️⃣ Слишком много данных – если таблица огромная, ORDER BY без индекса будет работать медленно. 3️⃣ Использование SELECT * – загружает ненужные колонки и увеличивает нагрузку. ✅ Как ускорить? ✔ Добавляем индекс (если его нет):

CREATE INDEX idx_orders_customer ON orders(customer_id, order_date DESC);

✔ Выбираем только нужные колонки:

SELECT order_id, order_date 
FROM orders 
WHERE customer_id = 123 
ORDER BY order_date DESC;

✔ Лимитируем выборку (если нужен только последний заказ):

SELECT order_id, order_date 
FROM orders 
WHERE customer_id = 123 
ORDER BY order_date DESC 
LIMIT 1;

Добавление индекса + правильный выбор колонок + LIMIT = в разы быстрее! 🚀 📲 Мы в MAX #db 👉 @database_info