PostgreSQL: грабли, на которые мы наступаем… снова
СУБД PostgreSQL является одной из самых популярных реляционных баз данных с открытым исходным кодом, и благодаря почти 40-летней истории развития она является отличным выбором для приложений любого размера. Но многие начинающие администраторы и разработчики совершают одни и те же ошибки при работе с этой СУБД. В этой статье мы рассмотрим некоторые из них.
VACUUM и его использование
Когда вы удаляете записи из таблицы базы данных Postgresql, удаление данных не происходит немедленно; они просто помечаются как удаленные. То есть, предыдущая версия записи все еще остается в файле данных. То же самое происходит и с обновлениями: каждое обновление строки создает новую версию этой строки. Это называется раздуванием таблицы. Мертвые зоны — это пустые строки, которые занимают неиспользуемое дисковое пространство в файле данных и остаются там до тех пор, пока не будет выполнена очистка.
В PostgreSQL имеется мощный инструмент, предназначенный управления обслуживанием и производительностью — VACUUM. С ее помощью мы можем сканировать таблицы на наличие устаревших записей, помечать их как повторно используемые или удалять полностью.
Как мы уже сказали, при длительном использовании PostgreSQL накапливаются мёртвые записи, которые приводят к тому, что таблица занимает больше места, чем ей фактически нужно. Механизм VACUUM позволяет избежать чрезмерного раздувания таблиц и с его помощью можно убрать лишние кортежи. Когда выполняется процесс очистки, он помечает место, занятое мертвыми кортежами, как пригодное для повторного использования другими кортежами.
Также удаление устаревших записей позволяет оптимизировать производительность. Регулярная очистка гарантирует, что PostgreSQL сможет быстро получить доступ к большему количеству данных, что уменьшает время выполнения запроса.
Помимо этого, удаление лишних записей освобождает место на диске. Так использование команды VACUUM FULL в PostgreSQL позволяет освободить место на диске, что делает его доступным для операционной системы.
И наконец, VACUUM обновляет статистику таблицы, чтобы помочь планировщику запросов принимать более эффективные решения о том, как выполняется запрос.
К сожалению, многие начинающие пользователи не знают, что для борьбы с раздуванием БД необходимо выполнять команду VACUUM, что приводит к чрезмерному увеличению объема базы данных и снижению производительности.
Для того, чтобы определить таблицы, требующие очистки, выполним следующий запрос:
SELECT relname, n_dead_tup
FROM pg_stat_all_tables
WHERE n_dead_tup > 0;Далее к тем таблицам, которые содержат избыточные записи мы можем применить VACUUM. Так, для того, чтобы очистить таблицу table, а также провести оптимизацию и вывести детальный отчет, выполним команду:
VACUUM (VERBOSE, ANALYZE) table;На рисунке ниже представлено состояние таблицы до и после очистки с помощью VACUUM
Как видно, до выполнения очистки у нас в таблице была дублирующаяся запись, после выполнения VACUUM мы ее удалили.
Также, очистку можно выполнять не только вручную, но и автоматически с помощью AUTOVACUUM. Для того, чтобы определить, когда должна срабатывать данная функция, необходимо определить два параметра:
autovacuum_vacuum_threshold — минимальное количество изменённых строк для запуска.
autovacuum_vacuum_scale_factor — доля измененных строк от общего числа.
Например:
ALTER TABLE your_table
SET (autovacuum_vacuum_threshold = 500,
autovacuum_vacuum_scale_factor = 0.05);Несмотря на то, что VACUUM необходим для оптимизации работы БД, его неправильное использование может привести к пагубным последствиям. Например, использование команды VACUUM FULL на производственной базе данных может заблокировать ее на некоторое время.
Забытые соединения
Следующая ошибка новичков собственно относится не только к Postgresql, но и к другим СУБД, языкам программирования и средам разработки.
Каждый раз, когда мы открываем соединение для получения или обновления данных, это занимает время и использует такие ресурсы, как память и процессор. Если эти соединения не закрываются должным образом после использования, они остаются открытыми и простаивают, потребляя системные ресурсы и в конечном итоге исчерпывая лимит соединений базы данных. Это называется утечкой соединений и может привести к ошибкам и сбоям.
Собственно, одной из наиболее распространенных причин низкой производительности СУБД являются простаивающие соединения. Большинство разработчиков, начинающих работать с Postgresql, считают, что открытые соединения — это просто неработающие соединения, которые ничего не делают, но это неверно, поскольку пока эти соединения открыты, они потребляют ресурсы сервера.
Важно понимать, что, если соединение не закрыто, оно может привести к увеличению потребления ресурсов, блокировке таблиц или строк и даже к задержке при выполнении других запросов. Со временем это может привести к снижению производительности базы данных или даже к неожиданному падению приложения.
Решение этой проблемы достаточно простое, каждое открытое соединение необходимо закрывать после использования. Вы можете сделать это самостоятельно в своем коде или с помощью инструментов пула соединений, таких как PgBouncer, которые эффективно управляют соединениями и предотвращают исчерпание ресурсов. Pgbouncer — это приложение, управляющее пулом соединений Postgresql. Любое конечное приложение может подключиться к pgbouncer, как если бы это был непосредственно сервер Postgresql, и pgbouncer создаст подключение к реальному серверу, либо задействует одно из ранее установленных подключений.
Вот пример фрагмента кода на Python для правильной обработки соединений PostgreSQL и предотвращения утечек соединений:
import psycopg2
conn = psycopg2.connect("dbname=test user=postgres password=secret")
try:
cur = conn.cursor()
cur.execute("SELECT id, name FROM users")
print(cur.fetchall())
finally:
cur.close()
conn.close()Написание неэффективных запросов
Неоптимальный код — это проблема разработки на любом языке программирования. SQL не является исключением, более того, некорректно написанный запрос может повлиять худшим образом на работу всей БД. Так, если вы работаете с множеством строк из большой таблицы, вам нужно быть осторожным при написании запросов, потому что неоптимизированный запрос может затронуть значительно больше строк, чем необходимо.
Неэффективные запросы часто являются результатом плохо написанного SQL кода, отсутствия индексов или непонимания того, как PostgreSQL выполняет запросы. Когда запрос обрабатывает слишком много строк, он увеличивает объем дискового ввода‑вывода и потребляет больше ресурсов процессора и памяти (тем самым уменьшая доступные ресурсы для других запросов). В конечном итоге это приводит к снижению общей производительности базы данных и вашего приложения.
Для лучшего понимания того, что представляют из себя неправильные запросы, рассмотрим несколько примеров. Начнем с классики, SELECT * без WHERE или INNER или объединение двух больших таблиц. Подобные конструкции без соответствующих индексов заставляет PostgreSQL сканировать и обрабатывать больше данных, чем необходимо, что приводит к проблемам с производительностью.
Лучший способ запросить данные — быть конкретным. Вместо того чтобы запрашивать все данные из базы и фильтровать их после запроса с помощью кода приложения, запрашивайте только те колонки данных, которые вам действительно нужны. Так запросы будут выполняться быстрее, а результаты будут более четкими.
Например, вместо того чтобы писать:
SELECT * FROM users WHERE email = 'test@example.com';Укажем конкретные столбцы, значения которых нас интересуют:
SELECT id, name FROM users WHERE email = 'test@example.com';Выборка всех столбцов, когда вам нужно только несколько, может увеличить объем обрабатываемых и передаваемых данных. Это называется «безумием подстановочных знаков». Использование подстановочных знаков может показаться заманчивым, поскольку он выводит все данные сразу, но такой подход не рекомендуется. Это все равно что перебирать множество ненужных вещей, чтобы найти то, что вам нужно, а это неэффективно.
Еще одна полезная команда SQL это LIMIT. При работе с небольшими таблицами вы можете не беспокоиться о количестве возвращаемых строк. Однако при работе с более объемными таблицами LIMIT полезен для повышения производительности запросов. Здесь также не стоит забирать все значения, а потом фильтровать их в коде приложения.
Гораздо лучше на уровне SQL запросов указывать необходимое максимальное количество возвращаемых записей.
SELECT * FROM users LIMIT 10Индексы — один из самых эффективных способов ускорения запросов. Они позволяют PostgreSQL быстро находить строки без опроса всей таблицы. Например, если вы часто запрашиваете из таблицы users значение столбца email, то создайте индекс для столбца email:
CREATE INDEX idx_users_email ON users(email);
Теперь запросы к столбцу email будут выполняться значительно быстрее, благодаря индексам.
Заключение
Мы рассмотрели некоторые наиболее распространенные ошибки новичков, начинающих работать с PostgreSQL. Стоит отметить, что с опытом приходит понимание того, как можно эффективно бороться с подобными проблемами. Однако лучше все‑таки сразу не допускать чрезмерного раздувания баз данных, забытых соединений и не оптимально написанных запросов.
В следующей статье мы поговорим о том, какие ошибки может допустить архитектор при построении структуры таблиц и как это можно исправить.
Если вам интересна более глубокая работа с PostgreSQL и вы хотите эффективно устранять ошибки и улучшать производительность, рекомендую обратить внимание на эти открытые уроки, которые скоро пройдут в Otus:
28 апреля — Секреты логирования PostgreSQL: находим и исправляем ошибки в приложениях
Разберёте различные способы логирования запросов и хранимых процедур, а также встроенные механизмы для улучшения диагностики.12 мая —B‑tree индексы: как сделать PostgreSQL быстрее
Узнаете, как работает B‑tree индекс, когда и как его использовать для ускорения работы с базой данных.21 мая — PostgreSQL и секционирование: разделяй и властвуй!
Рассмотрите, как секционирование помогает решать проблемы с большими таблицами и улучшать производительность и масштабируемость.
Habrahabr.ru прочитано 37867 раз
