День 1638. #PostgresTips
Советы по Postgres для Начинающих
Давно я не писал ничего про базы данных. А тут наткнулся на советы по PostgreSQL и решил пройтись по ним, а заодно повторить для себя некоторые моменты, поскольку скоро мне предстоит переход на эту СУБД. Это будет серия постов с тегом #PostgresTips, которые периодически будут появляться на канале.
1. Кортежи — это физические версии строк
Одним из основополагающих аспектов PostgreSQL, который удивляет многих новичков, является концепция кортежей. Попросту говоря, кортеж в Postgres — это физическая версия строки данных. Это означает, что при изменении данных в строке вместо изменения существующих данных Postgres добавляет новую версию этой строки, кортеж. Эта система управления версиями называется MVCC (Multiversion Concurrency Control), и важно понимать её для разработки высокопроизводительных систем.
Вот что происходит во время различных операций записи:
- Когда вы выполняете команду DELETE, она не сразу освобождает место на диске. Вместо этого старый кортеж помечается как мёртвый, но остаётся в БД до тех пор, пока VACUUM не удалит его. Если эти мёртвые кортежи накапливаются и удаляются при очистке больших объёмов, это приводит к раздуванию таблиц и индексов (они занимают гораздо больше места на диске, чем содержат данных).
- Точно так же, когда вы обновляете строку, Postgres не изменяет существующий кортеж. Вместо этого он создаёт новую версию этой строки (новый кортеж) и помечает старую как мёртвую.
- Даже отменённый INSERT создаёт мёртвый кортеж, что может удивить многих. Это означает, что, если вы попытаетесь вставить запись, а затем откатите это действие, кортеж, который должен был быть вставлен, помечается как мёртвый.
Чтобы помочь понять эти концепции, каждая таблица в Postgres имеет скрытые столбцы, которые вы можете посмотреть: ctid, xmin и xmax. ctid представляет расположение кортежа (номер страницы + смещение внутри неё), а xmin и xmax можно рассматривать как «дату (номер транзакции) рождения» и «дату смерти» для кортежей.
Поняв это поведение на раннем этапе, вы будете лучше подготовлены к решению проблем, связанных с дисковым пространством, раздуванием и процессами автоочистки, которые направлены на удаление этих мёртвых кортежей. Вот базовый пример, тривиальный, но очень важный:
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
Советы по Postgres для Начинающих
Давно я не писал ничего про базы данных. А тут наткнулся на советы по PostgreSQL и решил пройтись по ним, а заодно повторить для себя некоторые моменты, поскольку скоро мне предстоит переход на эту СУБД. Это будет серия постов с тегом #PostgresTips, которые периодически будут появляться на канале.
1. Кортежи — это физические версии строк
Одним из основополагающих аспектов PostgreSQL, который удивляет многих новичков, является концепция кортежей. Попросту говоря, кортеж в Postgres — это физическая версия строки данных. Это означает, что при изменении данных в строке вместо изменения существующих данных Postgres добавляет новую версию этой строки, кортеж. Эта система управления версиями называется MVCC (Multiversion Concurrency Control), и важно понимать её для разработки высокопроизводительных систем.
Вот что происходит во время различных операций записи:
- Когда вы выполняете команду DELETE, она не сразу освобождает место на диске. Вместо этого старый кортеж помечается как мёртвый, но остаётся в БД до тех пор, пока VACUUM не удалит его. Если эти мёртвые кортежи накапливаются и удаляются при очистке больших объёмов, это приводит к раздуванию таблиц и индексов (они занимают гораздо больше места на диске, чем содержат данных).
- Точно так же, когда вы обновляете строку, Postgres не изменяет существующий кортеж. Вместо этого он создаёт новую версию этой строки (новый кортеж) и помечает старую как мёртвую.
- Даже отменённый INSERT создаёт мёртвый кортеж, что может удивить многих. Это означает, что, если вы попытаетесь вставить запись, а затем откатите это действие, кортеж, который должен был быть вставлен, помечается как мёртвый.
Чтобы помочь понять эти концепции, каждая таблица в Postgres имеет скрытые столбцы, которые вы можете посмотреть: ctid, xmin и xmax. ctid представляет расположение кортежа (номер страницы + смещение внутри неё), а xmin и xmax можно рассматривать как «дату (номер транзакции) рождения» и «дату смерти» для кортежей.
Поняв это поведение на раннем этапе, вы будете лучше подготовлены к решению проблем, связанных с дисковым пространством, раздуванием и процессами автоочистки, которые направлены на удаление этих мёртвых кортежей. Вот базовый пример, тривиальный, но очень важный:
pg=# create table t1 as select 1 as id;Мы создали таблицу с одной строкой, проверили расположение живого кортежа этой строки (ctid), а затем сделали обновление, которое логически ничего не делает, т.е. не меняет значение. Но местоположение изменилось с (0,1) (страница 0, смещение 1) на (0,2). Потому что физически Postgres создал новый кортеж — новую версию строки. Понимание этого поведения Postgres поможет вам проектировать системы, работающие более эффективно.
SELECT 1
pg=# select ctid, xmin, xmax, * from t1;
ctid | xmin | xmax | id
-------+-------+------+----
(0,1) | 47496 | 0 | 1
(1 row)
pg=# update t1 set id = id where id = 1;
UPDATE 1
pg=# select ctid, xmin, xmax, * from t1;
ctid | xmin | xmax | id
-------+-------+------+----
(0,2) | 47497 | 0 | 1
(1 row)
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
👍26👎1
День 1643. #PostgresTips
Советы по Postgres для Начинающих
2. Делайте EXPLAIN ANALYZE всегда с BUFFERS
Понимание того, как работает запрос, имеет решающее значение для оптимизации его производительности. В PostgreSQL команда EXPLAIN является основным инструментом для достижения этой цели. Однако для более детального разбора следует использовать EXPLAIN (ANALYZE, BUFFERS). И вот почему:
- EXPLAIN сам по себе предоставляет план запроса, давая вам представление об операциях, которые Postgres намеревается использовать для выборки или изменения данных: последовательное сканирование, сканирование индекса, объединение, сортировку и многое другое. Эту команду следует использовать исключительно для проверки плана запроса без выполнения.
- EXPLAIN ANALYZE не только показывает запланированные операции, но также выполняет запрос и предоставляет фактическую статистику времени выполнения. Это позволяет сравнивать, например, предполагаемые количества строк на каждом этапе с фактическими количествами, помогая определить, где Postgres может делать неточные предположения. Также ANALYZE предоставляет информацию о времени выполнения каждого этапа.
- EXPLAIN (ANALYZE, BUFFERS) предоставляет информацию об использовании буфера — в частности, сколько блоков попало в пул буферов или считано в него из базового кеша или диска. Это даёт ценную информацию о том, насколько интенсивен по дисковому чтению/записи ваш запрос, что может серьёзно сказаться на времени его выполнения. А если у вас задан параметр
3. Оптимальный выбор UI-инструментов: помимо pgAdmin
При изучении Postgres одним из первых вопросов, с которым вы столкнётесь, будет выбор клиента или IDE. Многие новички начинают с pgAdmin из-за его популярности и доступности. Но доступны более мощные и универсальные инструменты.
Одним из самых мощных клиентов для PostgreSQL является встроенный инструмент командной строки psql. Он содержит функции, обеспечивающие эффективное взаимодействие с базой данных, и вы найдёте его практически в любой системе, где установлен PostgreSQL.
Если вы больше склоняетесь к графическим IDE, есть несколько, которые предлагают баланс между удобством для пользователя и расширенными возможностями: DBeaver, DataGrip от JetBrains и Postico предоставляют сложные UI-интерфейсы с поддержкой выполнения запросов, визуализации данных и многого другого.
Однако независимо от того, какой графический инструмент вы выберете, потратить некоторое время на изучение всех тонкостей psql может оказаться невероятно полезным и обязательно окупится.
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
Советы по Postgres для Начинающих
2. Делайте EXPLAIN ANALYZE всегда с BUFFERS
Понимание того, как работает запрос, имеет решающее значение для оптимизации его производительности. В PostgreSQL команда EXPLAIN является основным инструментом для достижения этой цели. Однако для более детального разбора следует использовать EXPLAIN (ANALYZE, BUFFERS). И вот почему:
- EXPLAIN сам по себе предоставляет план запроса, давая вам представление об операциях, которые Postgres намеревается использовать для выборки или изменения данных: последовательное сканирование, сканирование индекса, объединение, сортировку и многое другое. Эту команду следует использовать исключительно для проверки плана запроса без выполнения.
- EXPLAIN ANALYZE не только показывает запланированные операции, но также выполняет запрос и предоставляет фактическую статистику времени выполнения. Это позволяет сравнивать, например, предполагаемые количества строк на каждом этапе с фактическими количествами, помогая определить, где Postgres может делать неточные предположения. Также ANALYZE предоставляет информацию о времени выполнения каждого этапа.
- EXPLAIN (ANALYZE, BUFFERS) предоставляет информацию об использовании буфера — в частности, сколько блоков попало в пул буферов или считано в него из базового кеша или диска. Это даёт ценную информацию о том, насколько интенсивен по дисковому чтению/записи ваш запрос, что может серьёзно сказаться на времени его выполнения. А если у вас задан параметр
track_io_timing = on, вы получите информацию о времени выполнения для всех операций ввода-вывода.3. Оптимальный выбор UI-инструментов: помимо pgAdmin
При изучении Postgres одним из первых вопросов, с которым вы столкнётесь, будет выбор клиента или IDE. Многие новички начинают с pgAdmin из-за его популярности и доступности. Но доступны более мощные и универсальные инструменты.
Одним из самых мощных клиентов для PostgreSQL является встроенный инструмент командной строки psql. Он содержит функции, обеспечивающие эффективное взаимодействие с базой данных, и вы найдёте его практически в любой системе, где установлен PostgreSQL.
Если вы больше склоняетесь к графическим IDE, есть несколько, которые предлагают баланс между удобством для пользователя и расширенными возможностями: DBeaver, DataGrip от JetBrains и Postico предоставляют сложные UI-интерфейсы с поддержкой выполнения запросов, визуализации данных и многого другого.
Однако независимо от того, какой графический инструмент вы выберете, потратить некоторое время на изучение всех тонкостей psql может оказаться невероятно полезным и обязательно окупится.
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
👍14
День 1656. #PostgresTips
Советы по Postgres для Начинающих
4. Ведение журнала: настройте параметры как можно раньше
Как и во многих других системах, в Postgres журналы — это кладезь информации, дающий вам подробное представление о работе системы и потенциальных проблемах. Включив всестороннее ведение журналов, вы сможете избежать проблем, оптимизировать производительность и обеспечить общую работоспособность вашей базы данных.
Что логировать
Ключ к эффективному ведению журнала — это знать, что записывать, не перегружая систему. Установив такие параметры, как
Баланс между информативностью и избыточностью
Важно отметить, что, хотя детальное ведение журнала может предоставить ценную информацию, оно также может привести к накладным расходам. Это особенно верно, если вы установите
Несмотря на то, что ведение журнала является чрезвычайно мощным инструментом, оно требует тщательной настройки и периодической проверки, чтобы убедиться, что оно остается помощью, а не помехой.
5. Улучшите наблюдаемость с помощью расширений
Когда вы стремитесь поддерживать производительность и работоспособность Postgres, расширения могут серьёзно помочь. Среди них pg_stat_statements выделяется как обязательный элемент, который не подлежит обсуждению.
pg_stat_statements предоставляет средства для отслеживания статистики выполнения всех операторов SQL, успешно выполненных сервером. Проще говоря, это помогает отслеживать, какие запросы выполняются часто, какие занимают больше времени, а какие могут нуждаться в оптимизации. С помощью этого расширения вы можете посмотреть на работу вашей базы данных, обнаруживать и исправлять неэффективности.
Существуют и другие примечательные расширения, которые могут предложить более глубокое понимание:
- pg_stat_kcache
Помогает понять фактическое использование дискового ввода-вывода и ЦП. С помощью него вы можете определить запрос, который отвечает за высокую загрузку ЦП или дисковый ввод-вывод.
- pg_wait_sampling или pgsentinel
Дают более чёткое представление о том, где запросы тратят время на ожидание, обеспечивая так называемый анализ событий ожидания, также известный как анализ истории активных сеансов.
- auto_explain
Автоматически регистрирует планы выполнения медленных операторов, что упрощает их понимание и оптимизацию.
Помните, что эти расширения требуют начальной установки и настройки, чтобы получить наилучшие результаты и снизить накладные расходы. И, к сожалению, большинство провайдеров Postgres не предоставляют pg_stat_kcache или pg_wait_sampling/pgsentinel.
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
Советы по Postgres для Начинающих
4. Ведение журнала: настройте параметры как можно раньше
Как и во многих других системах, в Postgres журналы — это кладезь информации, дающий вам подробное представление о работе системы и потенциальных проблемах. Включив всестороннее ведение журналов, вы сможете избежать проблем, оптимизировать производительность и обеспечить общую работоспособность вашей базы данных.
Что логировать
Ключ к эффективному ведению журнала — это знать, что записывать, не перегружая систему. Установив такие параметры, как
log_checkpoints = on,
log_autovacuum_min_duration = 0,
log_temp_files = 0,
log_lock_waits = on
вы получаете видимость контрольных точек, операций автоочистки, создания временных файлов и ожидания блокировки. Это некоторые из наиболее распространённых областей, в которых могут возникнуть проблемы, что делает их крайне важными для мониторинга.Баланс между информативностью и избыточностью
Важно отметить, что, хотя детальное ведение журнала может предоставить ценную информацию, оно также может привести к накладным расходам. Это особенно верно, если вы установите
log_min_duration_statement на очень низкое значение. Например, если установить его на 200мс, запрос будет логироваться дольше, чем выполняться, что может как повысить информативность, так и потенциально снизить производительность. Всегда будьте осторожны и помните об «эффекте наблюдателя» — влиянии процесса мониторинга на наблюдаемую систему. Но без детальной информации из журналов диагностика проблем была бы намного сложнее.Несмотря на то, что ведение журнала является чрезвычайно мощным инструментом, оно требует тщательной настройки и периодической проверки, чтобы убедиться, что оно остается помощью, а не помехой.
5. Улучшите наблюдаемость с помощью расширений
Когда вы стремитесь поддерживать производительность и работоспособность Postgres, расширения могут серьёзно помочь. Среди них pg_stat_statements выделяется как обязательный элемент, который не подлежит обсуждению.
pg_stat_statements предоставляет средства для отслеживания статистики выполнения всех операторов SQL, успешно выполненных сервером. Проще говоря, это помогает отслеживать, какие запросы выполняются часто, какие занимают больше времени, а какие могут нуждаться в оптимизации. С помощью этого расширения вы можете посмотреть на работу вашей базы данных, обнаруживать и исправлять неэффективности.
Существуют и другие примечательные расширения, которые могут предложить более глубокое понимание:
- pg_stat_kcache
Помогает понять фактическое использование дискового ввода-вывода и ЦП. С помощью него вы можете определить запрос, который отвечает за высокую загрузку ЦП или дисковый ввод-вывод.
- pg_wait_sampling или pgsentinel
Дают более чёткое представление о том, где запросы тратят время на ожидание, обеспечивая так называемый анализ событий ожидания, также известный как анализ истории активных сеансов.
- auto_explain
Автоматически регистрирует планы выполнения медленных операторов, что упрощает их понимание и оптимизацию.
Помните, что эти расширения требуют начальной установки и настройки, чтобы получить наилучшие результаты и снизить накладные расходы. И, к сожалению, большинство провайдеров Postgres не предоставляют pg_stat_kcache или pg_wait_sampling/pgsentinel.
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
👍8
День 1661. #PostgresTips
Советы по Postgres для Начинающих
6. Используйте ветки БД для разработки
Процесс разработки и тестирования в базах данных часто требует копии базы, что может быть ресурсоёмким, медленным и громоздким. Однако тонкое клонирование и ветвление БД предоставляют более удобный способ.
Что такое тонкое клонирование?
Инструменты тонкого клонирования обеспечивают легковесные, доступные для записи клоны вашей базы данных. Эти клоны используют те же базовые блоки данных, что и источник, но отображаются для пользователя как независимые базы данных. Когда в клон вносятся изменения, только эти изменения потребляют дополнительное место на диске — это достигается с помощью копирования при записи (copy-on-write, CoW), аналогично тому, что есть в контейнерах или Git, но на уровне блоков, а не на уровне файлов. Это позволяет невероятно быстро и эффективно создавать несколько копий для разработки, тестирования или анализа.
Преимущества ветвления БД
Ветвление базы данных — это тонкое клонирование, расширенное возможностью сохранения изменений и разрешения дальнейшего создания клонов на основе нового состояния. Как и в случае контроля версий кода, ветвление в контексте базы данных позволяет разработчикам создавать ответвления от основного набора данных. Это означает, что вы можете протестировать новую функцию или изменение в изолированной среде, не затрагивая первичные данные.
Database Lab
Такие инструменты, как Database Lab (DBLab), предоставляют мощные возможности тонкого клонирования и ветвления. Более того, в сочетании с решениями ИИ, такими как ChatGPT, разработчики могут даже получать мгновенные результаты своих экспериментов с SQL-запросами, не влияя на производственную среду или работу коллег. У ChatGPT часто возникают проблемы с «галлюцинациями», и всегда важно проверять советы, сгенерированные ИИ, на клоне базы данных. Ветвление даёт наиболее эффективный с точки зрения затрат и времени способ сделать это.
По сути, использование тонкого клонирования и ветвления базы данных означает более быстрые циклы разработки, снижение затрат на хранение и возможность экспериментировать без риска. Это новый подход к тому, как мы обрабатываем данные в средах разработки.
Самый быстрый способ начать работу с тонкими клонами и ветвлением БД — установить DBLab SE в несколько кликов с помощью консоли Postgres.ai (поддерживаются: AWS, GCP, DigitalOcean и Hetzner Cloud, а также любые другие локации, в том числе on-premise).
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
Советы по Postgres для Начинающих
6. Используйте ветки БД для разработки
Процесс разработки и тестирования в базах данных часто требует копии базы, что может быть ресурсоёмким, медленным и громоздким. Однако тонкое клонирование и ветвление БД предоставляют более удобный способ.
Что такое тонкое клонирование?
Инструменты тонкого клонирования обеспечивают легковесные, доступные для записи клоны вашей базы данных. Эти клоны используют те же базовые блоки данных, что и источник, но отображаются для пользователя как независимые базы данных. Когда в клон вносятся изменения, только эти изменения потребляют дополнительное место на диске — это достигается с помощью копирования при записи (copy-on-write, CoW), аналогично тому, что есть в контейнерах или Git, но на уровне блоков, а не на уровне файлов. Это позволяет невероятно быстро и эффективно создавать несколько копий для разработки, тестирования или анализа.
Преимущества ветвления БД
Ветвление базы данных — это тонкое клонирование, расширенное возможностью сохранения изменений и разрешения дальнейшего создания клонов на основе нового состояния. Как и в случае контроля версий кода, ветвление в контексте базы данных позволяет разработчикам создавать ответвления от основного набора данных. Это означает, что вы можете протестировать новую функцию или изменение в изолированной среде, не затрагивая первичные данные.
Database Lab
Такие инструменты, как Database Lab (DBLab), предоставляют мощные возможности тонкого клонирования и ветвления. Более того, в сочетании с решениями ИИ, такими как ChatGPT, разработчики могут даже получать мгновенные результаты своих экспериментов с SQL-запросами, не влияя на производственную среду или работу коллег. У ChatGPT часто возникают проблемы с «галлюцинациями», и всегда важно проверять советы, сгенерированные ИИ, на клоне базы данных. Ветвление даёт наиболее эффективный с точки зрения затрат и времени способ сделать это.
По сути, использование тонкого клонирования и ветвления базы данных означает более быстрые циклы разработки, снижение затрат на хранение и возможность экспериментировать без риска. Это новый подход к тому, как мы обрабатываем данные в средах разработки.
Самый быстрый способ начать работу с тонкими клонами и ветвлением БД — установить DBLab SE в несколько кликов с помощью консоли Postgres.ai (поддерживаются: AWS, GCP, DigitalOcean и Hetzner Cloud, а также любые другие локации, в том числе on-premise).
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
👍9👎2
День 1677. #PostgresTips
Советы по Postgres для Начинающих
7. Убедитесь, что контрольные суммы данных включены.
Целостность данных является краеугольным камнем любой базы данных. Без уверенности в точности и согласованности данных даже самые совершенные структуры и алгоритмы БД станут бесполезными. Именно здесь контрольные суммы данных в Postgres играют решающую роль.
Что это?
В контексте БД контрольная сумма — это значение, полученное из суммы всех байтов в блоке данных. Если контрольные суммы данных включены, Postgres использует их для проверки целостности данных, хранящихся на диске. Когда данные записываются на диск, Postgres вычисляет и сохраняет значение контрольной суммы. Позже, когда эти данные считываются обратно в память, Postgres пересчитывает контрольную сумму и сравнивает её с сохранённым значением, чтобы убедиться, что данные не были повреждены.
Почему это важно?
Повреждение на уровне диска может быть вызвано различными факторами: от сбоев оборудования до ошибок ПО. Если включены контрольные суммы, Postgres может идентифицировать повреждённые данные до того, как это повлияет на ваше приложение или приведёт к более серьезным проблемам.
Активация и накладные расходы
Важно отметить, что контрольные суммы необходимо активировать во время создания кластера БД (initdb). Их невозможно включить для существующего кластера БД без дампа и восстановления данных или без использования специального инструмента pg_checksums (для этого потребуется некоторый опыт). Накладные расходы, связанные с контрольными суммами, относительно малы, особенно по сравнению с преимуществами обеспечения целостности данных.
8. Настройте автоочистку для частых запусков и более быстрого выполнения.
Процесс автоочистки (autovacuum) в Postgres похож на бригаду уборщиков для БД. Он работает незаметно, очищая старые данные и освобождая место для новых данных, обеспечивая эффективность БД.
Что это?
Каждая операция INSERT, UPDATE или DELETE в Postgres создаёт новую версию строки (в случае DELETE помечает строку как удалённую). Со временем старые версии строк накапливаются, и их необходимо очищать. Autovacuum выполняет эту очистку, освобождая место для хранения и удаляя «мёртвые» строки. Он также отвечает за поддержание актуальности статистики таблиц и предотвращение инцидентов, связанных с переносом идентификаторов транзакций.
Почему это важно?
Без регулярной автоматической очистки БД может страдать от раздувания — неиспользуемого пространства, которое удерживает БД, что может замедлять запросы и тратить дисковое пространство. Другая проблема — устаревшая статистика, которая может приводить к неоптимальному выбору плана запроса и снижению производительности.
Как настроить
Настройка автоочистки сводится к тому, чтобы она запускалась чаще и быстрее выполняла свои задачи. По сути, настройку приходится вести в двух направлениях:
- дать автоочистке больше мощности (больше рабочих процессов, большая квоту), т.к. по умолчанию разрешено только 3 рабочих процесса, и они довольно консервативно ограничиваются;
- заставить её срабатывать чаще, т.к., опять же, по умолчанию она срабатывает только при значительных изменениях строк (10-20%); в OLTP базе данных желательно уменьшить этот показатель до 1% или даже ниже.
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
Советы по Postgres для Начинающих
7. Убедитесь, что контрольные суммы данных включены.
Целостность данных является краеугольным камнем любой базы данных. Без уверенности в точности и согласованности данных даже самые совершенные структуры и алгоритмы БД станут бесполезными. Именно здесь контрольные суммы данных в Postgres играют решающую роль.
Что это?
В контексте БД контрольная сумма — это значение, полученное из суммы всех байтов в блоке данных. Если контрольные суммы данных включены, Postgres использует их для проверки целостности данных, хранящихся на диске. Когда данные записываются на диск, Postgres вычисляет и сохраняет значение контрольной суммы. Позже, когда эти данные считываются обратно в память, Postgres пересчитывает контрольную сумму и сравнивает её с сохранённым значением, чтобы убедиться, что данные не были повреждены.
Почему это важно?
Повреждение на уровне диска может быть вызвано различными факторами: от сбоев оборудования до ошибок ПО. Если включены контрольные суммы, Postgres может идентифицировать повреждённые данные до того, как это повлияет на ваше приложение или приведёт к более серьезным проблемам.
Активация и накладные расходы
Важно отметить, что контрольные суммы необходимо активировать во время создания кластера БД (initdb). Их невозможно включить для существующего кластера БД без дампа и восстановления данных или без использования специального инструмента pg_checksums (для этого потребуется некоторый опыт). Накладные расходы, связанные с контрольными суммами, относительно малы, особенно по сравнению с преимуществами обеспечения целостности данных.
8. Настройте автоочистку для частых запусков и более быстрого выполнения.
Процесс автоочистки (autovacuum) в Postgres похож на бригаду уборщиков для БД. Он работает незаметно, очищая старые данные и освобождая место для новых данных, обеспечивая эффективность БД.
Что это?
Каждая операция INSERT, UPDATE или DELETE в Postgres создаёт новую версию строки (в случае DELETE помечает строку как удалённую). Со временем старые версии строк накапливаются, и их необходимо очищать. Autovacuum выполняет эту очистку, освобождая место для хранения и удаляя «мёртвые» строки. Он также отвечает за поддержание актуальности статистики таблиц и предотвращение инцидентов, связанных с переносом идентификаторов транзакций.
Почему это важно?
Без регулярной автоматической очистки БД может страдать от раздувания — неиспользуемого пространства, которое удерживает БД, что может замедлять запросы и тратить дисковое пространство. Другая проблема — устаревшая статистика, которая может приводить к неоптимальному выбору плана запроса и снижению производительности.
Как настроить
Настройка автоочистки сводится к тому, чтобы она запускалась чаще и быстрее выполняла свои задачи. По сути, настройку приходится вести в двух направлениях:
- дать автоочистке больше мощности (больше рабочих процессов, большая квоту), т.к. по умолчанию разрешено только 3 рабочих процесса, и они довольно консервативно ограничиваются;
- заставить её срабатывать чаще, т.к., опять же, по умолчанию она срабатывает только при значительных изменениях строк (10-20%); в OLTP базе данных желательно уменьшить этот показатель до 1% или даже ниже.
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
👍13
День 1684. #PostgresTips
Советы по Postgres для Начинающих
9. Оптимизация запросов или настройки конфигурации
Когда дело касается производительности, в большинстве случаев полезно оптимизировать конфигурацию Postgres «достаточно хорошо», изредка пересматривая решения (например, после серьёзного обновления Postgres), а затем полностью сосредоточиться на настройке запросов. Особенно, если вы часто меняете приложение.
Первоначальная настройка конфигурации может привести к повышению производительности. Но по мере роста и развития приложения основная борьба за производительность часто смещается к оптимизации запросов. Среди новичков распространено заблуждение: «Если я хорошо настрою конфигурацию, у меня не будет проблем». Настройка конфигурации жизненно важна, но это только начало. В конце концов, придётся сосредоточиться на постоянной оптимизации запросов.
Уже обсуждавшийся pg_stat_statements — бесценный инструмент для выявления проблемных запросов. Он предоставляет ранжированный список операторов SQL, упорядоченный по различным показателям. В сочетании с EXPLAIN (ANALYZE, BUFFERS), вы можете понять план выполнения запроса и выявить неэффективность.
10. Ведение индекса: необходимая практика
Индексы имеют решающее значение для производительности любой реляционной БД, и Postgres не является исключением. Со временем, по мере изменения данных, индексы становятся фрагментированными и менее эффективными. Даже при использовании современных версий Postgres и хорошо настроенной автоочистке работоспособность индекса по-прежнему ухудшается с течением времени, по мере того как происходят многочисленные записи.
Когда данные вставляются, обновляются или удаляются, индексы, отражающие эти данные, претерпевают изменения. Эти изменения могут привести к тому, что структура индекса станет несбалансированной или появится неработающие записи, что замедлит производительность поиска.
Индексы не сохраняют свою оптимальную структуру бесконечно. Периодически их необходимо восстанавливать (REINDEX). Этот процесс включает в себя создание новой версии индекса, что часто приводит к более компактной и эффективной структуре. Настройка таких восстановлений, желательно в автоматическом режиме, гарантирует постоянство производительности БД.
Не менее важно удалять неиспользуемые или избыточные индексы. Они не только тратят место, но и могут замедлять операции записи. Регулярная проверка и очистка ненужных индексов должны быть частью вашей процедуры обслуживания.
Бонус: документация Postgres - ваш надёжный спутник
Всегда имейте в закладках официальную документацию Postgres:
- Она всеобъемлюща и актуальна: является наиболее авторитетным источником информации, подробно описывающим каждую функцию, поведение и нюансы Postgres.
- Содержит примечания к релизу с кратким описанием всех изменений, новых функций и улучшений. Это отличный способ быть в курсе того, что нового появилось и что может повлиять на ваши существующие настройки.
- Комментарии к исходному коду/файлы README: для тех, кто любит глубоко погружаться, исходный код Postgres — это не только учебный ресурс, но и справочный материал. Они могут дать ценную информацию и объяснения, которые могут быть неочевидны в других источниках.
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
Советы по Postgres для Начинающих
9. Оптимизация запросов или настройки конфигурации
Когда дело касается производительности, в большинстве случаев полезно оптимизировать конфигурацию Postgres «достаточно хорошо», изредка пересматривая решения (например, после серьёзного обновления Postgres), а затем полностью сосредоточиться на настройке запросов. Особенно, если вы часто меняете приложение.
Первоначальная настройка конфигурации может привести к повышению производительности. Но по мере роста и развития приложения основная борьба за производительность часто смещается к оптимизации запросов. Среди новичков распространено заблуждение: «Если я хорошо настрою конфигурацию, у меня не будет проблем». Настройка конфигурации жизненно важна, но это только начало. В конце концов, придётся сосредоточиться на постоянной оптимизации запросов.
Уже обсуждавшийся pg_stat_statements — бесценный инструмент для выявления проблемных запросов. Он предоставляет ранжированный список операторов SQL, упорядоченный по различным показателям. В сочетании с EXPLAIN (ANALYZE, BUFFERS), вы можете понять план выполнения запроса и выявить неэффективность.
10. Ведение индекса: необходимая практика
Индексы имеют решающее значение для производительности любой реляционной БД, и Postgres не является исключением. Со временем, по мере изменения данных, индексы становятся фрагментированными и менее эффективными. Даже при использовании современных версий Postgres и хорошо настроенной автоочистке работоспособность индекса по-прежнему ухудшается с течением времени, по мере того как происходят многочисленные записи.
Когда данные вставляются, обновляются или удаляются, индексы, отражающие эти данные, претерпевают изменения. Эти изменения могут привести к тому, что структура индекса станет несбалансированной или появится неработающие записи, что замедлит производительность поиска.
Индексы не сохраняют свою оптимальную структуру бесконечно. Периодически их необходимо восстанавливать (REINDEX). Этот процесс включает в себя создание новой версии индекса, что часто приводит к более компактной и эффективной структуре. Настройка таких восстановлений, желательно в автоматическом режиме, гарантирует постоянство производительности БД.
Не менее важно удалять неиспользуемые или избыточные индексы. Они не только тратят место, но и могут замедлять операции записи. Регулярная проверка и очистка ненужных индексов должны быть частью вашей процедуры обслуживания.
Бонус: документация Postgres - ваш надёжный спутник
Всегда имейте в закладках официальную документацию Postgres:
- Она всеобъемлюща и актуальна: является наиболее авторитетным источником информации, подробно описывающим каждую функцию, поведение и нюансы Postgres.
- Содержит примечания к релизу с кратким описанием всех изменений, новых функций и улучшений. Это отличный способ быть в курсе того, что нового появилось и что может повлиять на ваши существующие настройки.
- Комментарии к исходному коду/файлы README: для тех, кто любит глубоко погружаться, исходный код Postgres — это не только учебный ресурс, но и справочный материал. Они могут дать ценную информацию и объяснения, которые могут быть неочевидны в других источниках.
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
👍4