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

🧩 Как сделать 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

🧩 Сегодня покажу вам простой, но крайне полезный приём, как находить “тяжёлые” запросы в 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

🚀 Подборка полезных IT каналов в Max Системное администрирование, DevOps 📌 https://max.ru/i_odmin Все для системного администратора https://max.ru/bash_srv Bash Советы https://max.ru/sysadminof Книги для админов, полезные материалы https://max.ru/i_odmin_book Библиотека Системного Администратора https://max.ru/i_devops DevOps: Пишем о Docker, Kubernetes и др. https://max.ru/tipsysdmin Типичный Сисадмин 1C разработка 📌 https://max.ru/odin1c_rus Cтатьи, курсы, советы, шаблоны кода 1С Программирование C++📌 https://max.ru/cpp_lib Библиотека C/C++ разработчика Программирование Go📌 https://max.ru/golang_lib Библиотека Go (Golang) разработчика Программирование React📌 https://max.ru/react_lib React Программирование Python 📌 https://max.ru/python_of Python академия. https://max.ru/BookPython Библиотека Python разработчика Java разработка 📌 https://max.ru/bookjava Библиотека Java разработчика GitHub Сообщество 📌 https://max.ru/githublib Интересное из GitHub Базы данных (Data Base) 📌 https://max.ru/database_info Все про базы данных Фронтенд разработка 📌 https://max.ru/frontend_1 Подборки для frontend разработчиков Библиотеки 📌 https://max.ru/programmist_of Книги по программированию https://max.ru/proglb Библиотека программиста https://max.ru/bfbook Книги для программистов Программирование 📌 https://max.ru/bookflow Лекции, видеоуроки, доклады с IT конференций https://max.ru/itmozg Программисты, дизайнеры, новости из мира IT https://max.ru/php_lib Библиотека PHP программиста 👨🏼‍💻👩‍💻 Шутки программистов 📌 https://max.ru/itumor Шутки программистов Защита, взлом, безопасность 📌 https://max.ru/thehaking Канал о кибербезопасности https://max.ru/xakkep_1 Хакер Free Книги, статьи для дизайнеров 📌 https://max.ru/odesigners Статьи, книги для дизайнеров Математика 📌 https://max.ru/Pomatematike Канал по математике https://max.ru/phismat_1 Обучающие видео, книги по Физике и Математике Вакансии 📌 https://max.ru/progjob Вакансии в IT Мир технологий 📌 https://max.ru/mir_teh Канал для любознательных Бонус 📌 https://max.ru/piterspb_78 Свежие новости Санкт-Петербурга https://max.ru/mockva_life Свежие новости Москвы

📊 Зачем 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

🧠 Почему 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 строк. Это снизит нагрузку и ускорит выполнение. 📲 Мы в MAX #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. 📲 Мы в 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