🔥 Оптимизация индексов: частая ошибка 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;

Здесь ищем индексы, которые дублируют друг друга.

⚡ Вывод
Чем меньше избыточных индексов — тем быстрее работает ваша база данных. Проверьте свои индексы прямо сейчас!

#db

👉 @database_info

🔍 Как ускорить SELECT в больших таблицах?

Сегодня расскажу, как можно ускорить выборку данных из больших таблиц. Если у вас запрос SELECT тормозит, попробуйте эти методы:

1️⃣ Индексы
Без индексов даже самый мощный сервер не спасёт. Используйте B-Tree для точных совпадений (=) и диапазонов (BETWEEN). Для полнотекстового поиска — GIN или Full-Text Index.

2️⃣ EXPLAIN ANALYZE
Перед оптимизацией всегда проверяйте план запроса:

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

Ищите Seq Scan (последовательное сканирование) – оно медленное. Заменяйте его на Index Scan или Bitmap Index Scan.

3️⃣ Ограничение выборки
Не грузите тысячи строк, если нужно 10 записей:

SELECT * FROM logs ORDER BY created_at DESC LIMIT 10;

Такой запрос работает быстрее, чем выборка всего.

4️⃣ Партиционирование
Разделяйте большие таблицы по дате, ID или другому критерию. Например, если у вас логи за 5 лет, можно хранить их по месяцам.

5️⃣ Материализованные представления
Если отчёты строятся медленно, попробуйте кэшировать результат:

CREATE MATERIALIZED VIEW fast_report AS
SELECT status, COUNT(*) FROM orders GROUP BY status;

Обновлять можно по расписанию:

REFRESH MATERIALIZED VIEW fast_report;

#db

👉 @database_info

Оптимизируем SQL-запросы – индексы, которые вы могли забыть

Оптимизация SQL-запросов – это как чистка кода: делаешь вовремя, и всё летает, откладываешь – потом разгребаешь баги и тормоза. Сегодня о трёх индексах, которые часто упускают:

✅ Составные индексы
Ошибка: делать индекс на одно поле, если запрос использует WHERE col1 = X AND col2 = Y.
Правильный индекс:

CREATE INDEX idx_example ON table_name (col1, col2);

📌 Запрос ускорится, потому что БД не будет делать лишние проверки.

✅ Индекс для ORDER BY
Ошибка: сортировка без индекса приводит к сканированию всей таблицы.
Решение – индекс в порядке сортировки:

CREATE INDEX idx_order ON table_name (col1 ASC);

✅ Индекс на FOREIGN KEY
Ошибка: внешний ключ без индекса – медленный джойн.
Исправляем:

CREATE INDEX idx_fk ON table_name (foreign_key_column);

🔍 Бонус: если не знаете, какой индекс поможет – посмотрите EXPLAIN ANALYZE в PostgreSQL или EXPLAIN в MySQL.

#db

👉 @database_info

Комплексное техническое руководство для опытных администраторов баз данных: Обеспечение целостности данных

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

https://bookflow.ru/kompleksnoe-tehnicheskoe-rukovodstvo-dlya-opytnyh-administratorov-baz-dannyh-obespechenie-tselostnosti-dannyh/

#db

👉 @database_info

Как индекс может замедлить запрос?

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

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

🚀 1. Неправильный выбор индекса
Допустим, у нас есть индекс по created_at, а мы выполняем запрос:

SELECT * FROM orders WHERE YEAR(created_at) = 2024;

Проблема в том, что функция YEAR(created_at) делает так, что индекс не используется эффективно. База данных должна пройтись по всем строкам, применяя функцию ко всем значениям. Лучше переписать так:

SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Теперь индекс сможет работать оптимально. 🔥

🏗 2. Слишком широкий индекс (Over-indexing)
Если у нас слишком много индексов на таблице, это приведёт к замедлению операций INSERT, UPDATE, DELETE. Почему? Потому что каждый раз при изменении данных БД должна обновлять все индексы. Поэтому добавляйте индексы осознанно!

📦 3. Низкая селективность индекса
Допустим, у нас есть индекс по status, но всего три возможных значения ('new', 'processing', 'done'). Если в таблице миллионы строк, но мало уникальных значений, индекс бесполезен — оптимизатор может решить, что проще выполнить полный скан таблицы.

⚠️ 4. Ошибка с покрывающим индексом
Иногда индекс покрывает все нужные колонки (INDEX(col1, col2, col3)), но запрос выбирает ещё одну (col4). Тогда база вынуждена обращаться к самой таблице, что убивает эффективность индекса.

📌 Вывод: индекс — мощный инструмент, но его неправильное использование может навредить. Перед добавлением индексов всегда анализируйте планы выполнения запросов (EXPLAIN в MySQL, EXPLAIN ANALYZE в PostgreSQL).

💬 Делитесь в комментариях, сталкивались ли вы с проблемами из-за индексов?

#db

👉 @database_info

Визуализация SQL-запросов

Ментальная модель, помогающая представить, как выполняются SQL-запросы.

Фактическая последовательность выполнения может отличаться от этой модели из-за стратегий оптимизации, применяемых оптимизатором запросов.

#db

👉 @database_info

Подборка Telegram каналов для программистов

https://news.1rj.ru/str/bash_srv Bash Советы
https://news.1rj.ru/str/win_sysadmin Системный Администратор Windows
https://news.1rj.ru/str/lifeproger Жизнь программиста. Авторский канал.
https://news.1rj.ru/str/devopslib Библиотека девопса | DevOps, SRE, Sysadmin
https://news.1rj.ru/str/rabota1C_rus Вакансии для программистов 1С

Системное администрирование 📌
https://news.1rj.ru/str/sysadmin_girl Девочка Сисадмин
https://news.1rj.ru/str/srv_admin_linux Админские угодья
https://news.1rj.ru/str/linux_srv Типичный Сисадмин

https://news.1rj.ru/str/linux_odmin Linux: Системный администратор
https://news.1rj.ru/str/devops_star DevOps Star (Звезда Девопса)
https://news.1rj.ru/str/i_linux Системный администратор
https://news.1rj.ru/str/linuxchmod Linux
https://news.1rj.ru/str/sys_adminos Системный Администратор
https://news.1rj.ru/str/tipsysdmin Типичный Сисадмин (фото железа, было/стало)
https://news.1rj.ru/str/sysadminof Книги для админов, полезные материалы
https://news.1rj.ru/str/i_odmin Все для системного администратора
https://news.1rj.ru/str/i_odmin_book Библиотека Системного Администратора
https://news.1rj.ru/str/i_odmin_chat Чат системных администраторов
https://news.1rj.ru/str/i_DevOps DevOps: Пишем о Docker, Kubernetes и др.
https://news.1rj.ru/str/sysadminoff Новости Линукс Linux

1C разработка 📌
https://news.1rj.ru/str/odin1C_rus Cтатьи, курсы, советы, шаблоны кода 1С
https://news.1rj.ru/str/DevLab1C 1С:Предприятие 8

Программирование C++📌
https://news.1rj.ru/str/cpp_lib Библиотека C/C++ разработчика
https://news.1rj.ru/str/cpp_knigi Книги для программистов C/C++
https://news.1rj.ru/str/cpp_geek Учим C/C++ на примерах

Программирование Python 📌
https://news.1rj.ru/str/pythonofff Python академия. Учи Python быстро и легко🐍
https://news.1rj.ru/str/BookPython Библиотека Python разработчика
https://news.1rj.ru/str/python_real Python подборки на русском и английском
https://news.1rj.ru/str/python_360 Книги по Python Rus

Java разработка 📌
https://news.1rj.ru/str/BookJava Библиотека Java разработчика
https://news.1rj.ru/str/java_360 Книги по Java Rus
https://news.1rj.ru/str/java_geek Учим Java на примерах

GitHub Сообщество 📌
https://news.1rj.ru/str/Githublib Интересное из GitHub

Базы данных (Data Base) 📌
https://news.1rj.ru/str/database_info Все про базы данных

Мобильная разработка: iOS, Android 📌
https://news.1rj.ru/str/developer_mobila Мобильная разработка
https://news.1rj.ru/str/kotlin_lib Подборки полезного материала по Kotlin

Фронтенд разработка 📌
https://news.1rj.ru/str/frontend_1 Подборки для frontend разработчиков
https://news.1rj.ru/str/frontend_sovet Frontend советы, примеры и практика!
https://news.1rj.ru/str/React_lib Подборки по React js и все что с ним связано

Разработка игр 📌
https://news.1rj.ru/str/game_devv Все о разработке игр

Библиотеки 📌
https://news.1rj.ru/str/book_for_dev Книги для программистов Rus
https://news.1rj.ru/str/programmist_of Книги по программированию
https://news.1rj.ru/str/proglb Библиотека программиста
https://news.1rj.ru/str/bfbook Книги для программистов
https://news.1rj.ru/str/books_reserv Книги для программистов

БигДата, машинное обучение 📌
https://news.1rj.ru/str/bigdata_1 Data Science, Big Data, Machine Learning, Deep Learning

Программирование 📌
https://news.1rj.ru/str/bookflow Лекции, видеоуроки, доклады с IT конференций
https://news.1rj.ru/str/coddy_academy Полезные советы по программированию
https://news.1rj.ru/str/rust_lib Полезный контент по программированию на Rust
https://news.1rj.ru/str/golang_lib Библиотека Go (Golang) разработчика
https://news.1rj.ru/str/itmozg Программисты, дизайнеры, новости из мира IT
https://news.1rj.ru/str/php_lib Библиотека PHP программиста 👨🏼‍💻👩‍💻
https://news.1rj.ru/str/nodejs_lib Подборки по Node js и все что с ним связано
https://news.1rj.ru/str/ruby_lib Библиотека Ruby программиста

QA, тестирование 📌
https://news.1rj.ru/str/testlab_qa Библиотека тестировщика

Шутки программистов 📌
https://news.1rj.ru/str/itumor Шутки программистов

Защита, взлом, безопасность 📌
https://news.1rj.ru/str/thehaking Канал о кибербезопасности
https://news.1rj.ru/str/xakep_2 Хакер Free

Книги, статьи для дизайнеров 📌
https://news.1rj.ru/str/ux_web Статьи, книги для дизайнеров

Математика 📌
https://news.1rj.ru/str/Pomatematike Канал по математике
https://news.1rj.ru/str/phis_mat Обучающие видео, книги по Физике и Математике

Excel лайфхак📌
https://news.1rj.ru/str/Excel_lifehack

https://news.1rj.ru/str/tikon_1 Новости высоких технологий, науки и техники💡
https://news.1rj.ru/str/mir_teh Мир технологий (Technology World)

Вакансии 📌
https://news.1rj.ru/str/sysadmin_rabota Системный Администратор
https://news.1rj.ru/str/progjob Вакансии в IT

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

Оптимизация запросов: как найти узкое место? 🔍

Привет, коллеги! Сегодня я покажу вам, как находить узкие места в SQL-запросах и оптимизировать их. Если ваш запрос работает медленно, скорее всего, проблема в одном из трех мест:

1️⃣ Неверные индексы
- Проверьте EXPLAIN ANALYZE, если используется Seq Scan вместо Index Scan, значит, индексы либо отсутствуют, либо неэффективны.
- Добавьте индексы на часто фильтруемые и соединяемые поля.

2️⃣ Проблемные JOIN'ы
- Проверьте, какие типы JOIN используются. NESTED LOOP JOIN может быть проблемой на больших таблицах.
- Используйте HASH JOIN или MERGE JOIN, если это возможно.

3️⃣ Громоздкие операции (GROUP BY, ORDER BY, DISTINCT)
- Сортировка и группировка требуют много ресурсов.
- Можно ли заменить DISTINCT на EXISTS?
- Используйте индексированные столбцы в ORDER BY.

Вот вам задача:
Какой тип JOIN наиболее эффективен для соединения двух больших таблиц? Пишите свои мысли в комментариях! 👇

#db

👉 @database_info

🧠 Почему PostgreSQL иногда "зависает" на UPDATE и как это пофиксить

Сегодня я расскажу про одну интересную особенность PostgreSQL, с которой сталкивался лично: внезапные подвисания при UPDATE большого количества строк. Причём CPU почти не загружен, а запрос как будто "висит".

📌 Проблема часто кроется в отсутствии индекса на колонку фильтра в WHERE. Пример:

UPDATE orders SET status = 'archived' WHERE created_at < '2022-01-01';

Если на created_at нет индекса, то PostgreSQL делает sequential scan всей таблицы. А теперь внимание: если в таблице много "мертвых" строк, которых ещё не убрал autovacuum, то PostgreSQL должен:

1. Прочитать кучу ненужных версий строк (MVCC).
2. Проверять видимость каждой строки.
3. Иногда ещё и ждать завершения других транзакций, держащих старые снапшоты.

🛠 Что делать:
- Проверить наличие индекса на колонку фильтра:

  CREATE INDEX idx_orders_created_at ON orders(created_at);
  

- Проверить состояние autovacuum:

  SELECT relname, n_dead_tup, last_vacuum, last_autovacuum 
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
  

- Можно вручную запустить:

  VACUUM ANALYZE orders;
  

🔥 Лайфхак: если UPDATE всё равно медленный, попробуй его разбить на батчи по 10 000 строк. Это снизит нагрузку и ускорит выполнение.


#db

👉 @database_info

🧹 VACUUM FULL — спасение от bloated-таблиц или тихий враг?

Сегодня хочу поговорить о том, что многие DBA используют как "универсальную таблетку" — VACUUM FULL. Но стоит ли?

💡 Что делает VACUUM FULL:
Он полностью перебирает таблицу и создаёт её заново, убирая все мёртвые строки. Результат — таблица становится компактной, как после дефрагментации. Это помогает, если у тебя:

- Много UPDATE или DELETE
- Таблица разрослась до абсурдных размеров
- Autovacuum не справляется

🔎 Как понять, что таблица bloated:

SELECT
  schemaname, relname, 
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  (pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_overhead
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Если table_size сильно больше, чем объём живых данных — у тебя блоат.

⚠️ Осторожно: VACUUM FULL = эксклюзивная блокировка!

Пока он работает:
- Все запросы к таблице ждут
- Могут “залипать” аппы
- В проде без окон — 💥

🛠 Что делать вместо:
- Пробовать pg_repack — он делает примерно то же, но без блокировок
- Делать регулярный autovacuum + мониторинг
- Разбивать массивные UPDATE/DELETE на батчи

VACUUM FULL — это инструмент, который стоит использовать осознанно. Как хирургический скальпель — мощный, но не для каждодневного применения. А ты когда последний раз делал VACUUM FULL в проде? 😏

#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

Как работают джойны SQL?

На приведенной ниже схеме подробно показано, как работают 4 типа объединений SQL.

🔹INNER JOIN возвращает совпадающие строки в обеих таблицах
🔹LEFT JOIN возвращает все записи из левой таблицы и соответствующие записи из правой
🔹RIGHT JOIN возвращает все записи из правой таблицы и соответствующие записи из левой
🔹FULL OUTER JOIN возвращает все записи, в которых есть совпадения, в левой или правой таблице

#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)


#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

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

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

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

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

ANALYZE my_big_table;

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

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

Можно даже так:

VACUUM ANALYZE my_big_table;

Так ты и "мусор" уберёшь, и статистику обновишь за один проход.


#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 — без всяких внешних тулов и дополнительных логов. Только 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 в проде? Или сразу лезешь в лог? Расскажи в комментах!


#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