How to clean dead tuples and monitor PostgreSQL (using VACUUM)
Whenever a record is deleted, it does not create an extra space in the system. PostgreSQL rather creates what is called a «dead tuple». If there is no more dependency on those tuples by the running transactions, PostgreSQL cleans it up using a process called VACUUM. The space used up by those tuples are sometimes called «Bloat».
However it should be noted that running VACUUM does not actually create any free space in the machine disk, instead it is rather kept by PostgreSQL for future inserts. VACUUM is a non-blocking operation, i.e., it does not create exclusive locks on the tables. Hence, VACUUM process can actually run in parallel to any ongoing transactions to the database. But running VACUUM FULL is a different case and it also locks the tables thereby preventing any further transaction on those tables. More documentation regarding VACUUM can be found here in the PostgreSQL documentation.
PostgreSQL already has settings to configure an autovacuum process. Nowadays, one does not need to think how and when to exceute the PostgreSQL VACUUM, it is done automatically by the database. So let’s begin with checking if the autovacuum process if it’s on in your case.
This tells us that the autovacuum process is already set up. If it’s not then one can find the settings in the postgresql.conf file and control when/how the VACUUM daemon runs. Please don’t forget to restart the PostgreSQL after any change in the settings in the file.
PostgreSQL vacuuming
In order to understand the reason behind the vacuuming process, let’s go bit deeper to the PostgreSQL basics. PostgreSQL uses multi-version concurrency control (MVCC) to ensure data consistency and accessibilty in high-concurrency environments. Whenever any transaction begins, it operates in its own snapshot of the database, that means whenever any record is deleted, PostgreSQL instead of actually deleting it, it creates a dead row (called dead tuple). VACUUM process thereby helps in optimising the the resource usage, in a way also helping in the database performance.
VACUUM does the following:
Dead tuples postgresql что это
Модуль pgstattuple предоставляет различные функции для получения статистики на уровне кортежей.
F.30.1. Функции
Функция pgstattuple возвращает физическую длину отношения, процент « мёртвых » кортежей и другую информацию. Она может быть полезна для принятия решения о необходимости очистки. В аргументе передаётся имя (возможно, дополненное схемой) или OID целевого отношения. Например:
Столбцы результата описаны в Таблице F.22.
Таблица F.22. Столбцы результата pgstattuple
| Столбец | Тип | Описание |
|---|---|---|
| table_len | bigint | Физическая длина отношения в байтах |
| tuple_count | bigint | Количество «живых» кортежей |
| tuple_len | bigint | Общая длина «живых» кортежей в байтах |
| tuple_percent | float8 | Процент «живых» кортежей |
| dead_tuple_count | bigint | Количество «мёртвых» кортежей |
| dead_tuple_len | bigint | Общая длина «мёртвых» кортежей в байтах |
| dead_tuple_percent | float8 | Процент «мёртвых» кортежей |
| free_space | bigint | Общий объём свободного пространства в байтах |
| free_percent | float8 | Процент свободного пространства |
Примечание
Функция pgstattuple получает блокировку отношения только для чтения. Таким образом, её результаты отражают не мгновенный снимок; на них будут влиять параллельные изменения.
Эта функция равнозначна функции pgstattuple(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstatindex(regclass) returns record
Функция pgstatindex возвращает запись с информацией об индексе типа B-дерево. Например:
| Столбец | Тип | Описание |
|---|---|---|
| version | integer | Номер версии B-дерева |
| tree_level | integer | Уровень корневой страницы в дереве |
| index_size | bigint | Общий объём индекса в байтах |
| root_block_no | bigint | Расположение страницы корня (0, если её нет) |
| internal_pages | bigint | Количество « внутренних » страниц (верхнего уровня) |
| leaf_pages | bigint | Количество страниц на уровне листьев |
| empty_pages | bigint | Количество пустых страниц |
| deleted_pages | bigint | Количество удалённых страниц |
| avg_leaf_density | float8 | Средняя плотность страниц на уровне листьев |
| leaf_fragmentation | float8 | Фрагментация на уровне листьев |
Выдаваемый размер индекса ( index_size ) обычно вычисляется по формуле internal_pages + leaf_pages + empty_pages + deleted_pages плюс одна страница, так как в нём учитывается и метастраница индекса.
Эта функция равнозначна функции pgstatindex(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstatginindex(regclass) returns record
Функция pgstatginindex возвращает запись с информацией об индексе типа GIN. Например:
| Столбец | Тип | Описание |
|---|---|---|
| version | integer | Номер версии GIN |
| pending_pages | integer | Количество страниц в списке ожидающих обработки |
| pending_tuples | bigint | Количество кортежей в списке ожидающих обработки |
Функция pgstathashindex возвращает запись с информацией о хеш-индексе. Например:
| Столбец | Тип | Описание |
|---|---|---|
| version | integer | Номер версии HASH |
| bucket_pages | bigint | Количество страниц групп |
| overflow_pages | bigint | Количество страниц переполнения |
| bitmap_pages | bigint | Количество страниц битовой карты |
| unused_pages | bigint | Количество неиспользованных страниц |
| live_items | bigint | Количество «живых» кортежей |
| dead_tuples | bigint | Количество «мёртвых» кортежей |
| free_percent | float | Процент свободного пространства |
Функция pg_relpages возвращает число страниц в отношении. pg_relpages(text) returns bigint
Эта функция равнозначна функции pg_relpages(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstattuple_approx(regclass) returns record
Выходные столбцы описаны в Таблице F.23.
Тогда как pgstattuple всегда производит полное сканирование таблицы и возвращает точное число живых и мёртвых кортежей (и их размер), а также точный объём свободного пространства, функция pgstattuple_approx пытается избежать полного сканирования и возвращает точную статистику только по мёртвым кортежам, а количество и объём живых кортежей, как и объём свободного пространства определяет приблизительно.
Она делает это, пропуская страницы, в которых, согласно карте видимости, есть только видимые кортежи (если для страницы установлен соответствующий бит, предполагается, что она не содержит мёртвых кортежей). Для таких страниц эта функция узнаёт объём свободного пространства из карты свободного пространства и предполагает, что остальное пространство на странице занято живыми кортежами.
На страницах, которые нельзя пропустить, она сканирует каждый кортеж, отражает его наличие и размер в соответствующих счётчиках и суммирует свободное пространство на странице. В конце она оценивает приблизительно общее число живых кортежей, исходя из числа просканированных страниц и кортежей (так же, как VACUUM рассчитывает значение pg_class.reltuples).
Таблица F.23. Столбцы результата pgstattuple_approx
| Столбец | Тип | Описание |
|---|---|---|
| table_len | bigint | Физическая длина отношения в байтах (точная) |
| scanned_percent | float8 | Просканированный процент таблицы |
| approx_tuple_count | bigint | Количество «живых» кортежей (приблизительное) |
| approx_tuple_len | bigint | Общая длина «живых» кортежей в байтах (приблизительная) |
| approx_tuple_percent | float8 | Процент «живых» кортежей |
| dead_tuple_count | bigint | Количество «мёртвых» кортежей (точное) |
| dead_tuple_len | bigint | Общая длина «мёртвых» кортежей в байтах (точная) |
| dead_tuple_percent | float8 | Процент «мёртвых» кортежей |
| approx_free_space | bigint | Общий объём свободного пространства в байтах (приблизительный) |
| approx_free_percent | float8 | Процент свободного пространства |
В показанном выше выводе показатели свободного пространства могут не соответствовать выводу pgstattuple в точности, потому что карта свободного пространства показывает верное значение, но не гарантируется, что оно будет точным до байта.
Sysadminium
База знаний системного администратора
Разрастание таблиц и индексов в PostgreSQL
Научимся оценивать в процентном соотношении разрастание таблиц или индексов в PostgreSQL. Для оценки будем использовать расширение pgstattuple.
В начале скомпилируем расширение pgstattuple, которое поможет понять сколько в базе данных полезной информации, а сколько служебной или неактуальной. Для этого перейдем в каталог с исходниками и выполним:
Затем подключим расширение в самой базе данных с помощью команды CREATE EXTENSION:
Создадим табличку bloat и наполним её данными. После чего сделаем для этой таблицы индекс:
Теперь с помощью функции pgstattuple(‘таблица’), которая входит в расширение pgstattuple узнаем сколько в таблице полезной информации, а сколько неактуальной или служебной:
Из вывода нам важно посмотреть на два параметра:
Если от 100% вычтем tuple_percent и free_percent, то получим приблизительный размер служебной информации.
Теперь посмотрим на разрастание индекса с помощью функции pgstatindex(‘индекс’):
Из вывода обратите внимание на колонку avg_leaf_density – это заполненность страниц индекса. Если заполненность небольшая, значит в страницах есть пустоты.
Следующим шагом обновим половину строк, а затем опять посмотрим на разрастание таблицы:
Тут мы уже видим что tuple_percent упал до 51,83 %. То есть половина таблицы имеет неактуальные версии строк, dead_tuple_count – это количество неактуальных строк.
И еще раз посмотрим на индекс:
И здесь мы видим, что avg_leaf_density упал до 45.15 %. То есть в страницах образовались пустоты. Эти пустоты постепенно будут заполнятся, а когда в таблицу придет autovacuum, то неактуальные версии строк будут удалены. Так что avtovacuum и работа сервера PostgreSQL постепенно поправят ситуацию.
Dead tuples postgresql что это
Модуль pgstattuple предоставляет различные функции для получения статистики на уровне кортежей.
F.41.1. Функции
Функция pgstattuple возвращает физическую длину отношения, процент « мёртвых » кортежей и другую информацию. Она может быть полезна для принятия решения о необходимости очистки. В аргументе передаётся имя (возможно, дополненное схемой) или OID целевого отношения. Например:
Столбцы результата описаны в Таблице F.24.
Таблица F.24. Столбцы результата pgstattuple
| Столбец | Тип | Описание |
|---|---|---|
| table_len | bigint | Физическая длина отношения в байтах |
| tuple_count | bigint | Количество «живых» кортежей |
| tuple_len | bigint | Общая длина «живых» кортежей в байтах |
| tuple_percent | float8 | Процент «живых» кортежей |
| dead_tuple_count | bigint | Количество «мёртвых» кортежей |
| dead_tuple_len | bigint | Общая длина «мёртвых» кортежей в байтах |
| dead_tuple_percent | float8 | Процент «мёртвых» кортежей |
| free_space | bigint | Общий объём свободного пространства в байтах |
| free_percent | float8 | Процент свободного пространства |
Примечание
Функция pgstattuple получает блокировку отношения только для чтения. Таким образом, её результаты отражают не мгновенный снимок; на них будут влиять параллельные изменения.
Эта функция равнозначна функции pgstattuple(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstatindex(regclass) returns record
Функция pgstatindex возвращает запись с информацией об индексе типа B-дерево. Например:
| Столбец | Тип | Описание |
|---|---|---|
| version | integer | Номер версии B-дерева |
| tree_level | integer | Уровень корневой страницы в дереве |
| index_size | bigint | Общий объём индекса в байтах |
| root_block_no | bigint | Расположение страницы корня (0, если её нет) |
| internal_pages | bigint | Количество « внутренних » страниц (верхнего уровня) |
| leaf_pages | bigint | Количество страниц на уровне листьев |
| empty_pages | bigint | Количество пустых страниц |
| deleted_pages | bigint | Количество удалённых страниц |
| avg_leaf_density | float8 | Средняя плотность страниц на уровне листьев |
| leaf_fragmentation | float8 | Фрагментация на уровне листьев |
Выдаваемый размер индекса ( index_size ) обычно вычисляется по формуле internal_pages + leaf_pages + empty_pages + deleted_pages плюс одна страница, так как в нём учитывается и метастраница индекса.
Эта функция равнозначна функции pgstatindex(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstatginindex(regclass) returns record
Функция pgstatginindex возвращает запись с информацией об индексе типа GIN. Например:
| Столбец | Тип | Описание |
|---|---|---|
| version | integer | Номер версии GIN |
| pending_pages | integer | Количество страниц в списке ожидающих обработки |
| pending_tuples | bigint | Количество кортежей в списке ожидающих обработки |
Функция pg_relpages возвращает число страниц в отношении. pg_relpages(text) returns bigint
Эта функция равнозначна функции pg_relpages(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstattuple_approx(regclass) returns record
Выходные столбцы описаны в Таблице F.25.
Тогда как pgstattuple всегда производит полное сканирование таблицы и возвращает точное число живых и мёртвых кортежей (и их размер), а также точный объём свободного пространства, функция pgstattuple_approx пытается избежать полного сканирования и возвращает точную статистику только по мёртвым кортежам, а количество и объём живых кортежей, как и объём свободного пространства определяет приблизительно.
Она делает это, пропуская страницы, в которых, согласно карте видимости, есть только видимые кортежи (если для страницы установлен соответствующий бит, предполагается, что она не содержит мёртвых кортежей). Для таких страниц эта функция узнаёт объём свободного пространства из карты свободного пространства и предполагает, что остальное пространство на странице занято живыми кортежами.
На страницах, которые нельзя пропустить, она сканирует каждый кортеж, отражает его наличие и размер в соответствующих счётчиках и суммирует свободное пространство на странице. В конце она оценивает приблизительно общее число живых кортежей, исходя из числа просканированных страниц и кортежей (так же, как VACUUM рассчитывает значение pg_class.reltuples).
Таблица F.25. Столбцы результата pgstattuple_approx
| Столбец | Тип | Описание |
|---|---|---|
| table_len | bigint | Физическая длина отношения в байтах (точная) |
| scanned_percent | float8 | Просканированный процент таблицы |
| approx_tuple_count | bigint | Количество «живых» кортежей (приблизительное) |
| approx_tuple_len | bigint | Общая длина «живых» кортежей в байтах (приблизительная) |
| approx_tuple_percent | float8 | Процент «живых» кортежей |
| dead_tuple_count | bigint | Количество «мёртвых» кортежей (точное) |
| dead_tuple_len | bigint | Общая длина «мёртвых» кортежей в байтах (точная) |
| dead_tuple_percent | float8 | Процент «мёртвых» кортежей |
| approx_free_space | bigint | Общий объём свободного пространства в байтах (приблизительный) |
| approx_free_percent | float8 | Процент свободного пространства |
В показанном выше выводе показатели свободного пространства могут не соответствовать выводу pgstattuple в точности, потому что карта свободного пространства показывает верное значение, но не гарантируется, что оно будет точным до байта.
Dead tuples postgresql что это
Модуль pgstattuple предоставляет различные функции для получения статистики на уровне кортежей.
F.31.1. Функции
Функция pgstattuple возвращает физическую длину отношения, процент « мёртвых » кортежей и другую информацию. Она может быть полезна для принятия решения о необходимости очистки. В аргументе передаётся имя (возможно, дополненное схемой) или OID целевого отношения. Например:
Столбцы результата описаны в Таблице F.23.
Таблица F.23. Столбцы результата pgstattuple
| Столбец | Тип | Описание |
|---|---|---|
| table_len | bigint | Физическая длина отношения в байтах |
| tuple_count | bigint | Количество «живых» кортежей |
| tuple_len | bigint | Общая длина «живых» кортежей в байтах |
| tuple_percent | float8 | Процент «живых» кортежей |
| dead_tuple_count | bigint | Количество «мёртвых» кортежей |
| dead_tuple_len | bigint | Общая длина «мёртвых» кортежей в байтах |
| dead_tuple_percent | float8 | Процент «мёртвых» кортежей |
| free_space | bigint | Общий объём свободного пространства в байтах |
| free_percent | float8 | Процент свободного пространства |
Примечание
Функция pgstattuple получает блокировку отношения только для чтения. Таким образом, её результаты отражают не мгновенный снимок; на них будут влиять параллельные изменения.
Эта функция равнозначна функции pgstattuple(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstatindex(regclass) returns record
Функция pgstatindex возвращает запись с информацией об индексе типа B-дерево. Например:
| Столбец | Тип | Описание |
|---|---|---|
| version | integer | Номер версии B-дерева |
| tree_level | integer | Уровень корневой страницы в дереве |
| index_size | bigint | Общий объём индекса в байтах |
| root_block_no | bigint | Расположение страницы корня (0, если её нет) |
| internal_pages | bigint | Количество « внутренних » страниц (верхнего уровня) |
| leaf_pages | bigint | Количество страниц на уровне листьев |
| empty_pages | bigint | Количество пустых страниц |
| deleted_pages | bigint | Количество удалённых страниц |
| avg_leaf_density | float8 | Средняя плотность страниц на уровне листьев |
| leaf_fragmentation | float8 | Фрагментация на уровне листьев |
Выдаваемый размер индекса ( index_size ) обычно вычисляется по формуле internal_pages + leaf_pages + empty_pages + deleted_pages плюс одна страница, так как в нём учитывается и метастраница индекса.
Эта функция равнозначна функции pgstatindex(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstatginindex(regclass) returns record
Функция pgstatginindex возвращает запись с информацией об индексе типа GIN. Например:
| Столбец | Тип | Описание |
|---|---|---|
| version | integer | Номер версии GIN |
| pending_pages | integer | Количество страниц в списке ожидающих обработки |
| pending_tuples | bigint | Количество кортежей в списке ожидающих обработки |
Функция pgstathashindex возвращает запись с информацией о хеш-индексе. Например:
| Столбец | Тип | Описание |
|---|---|---|
| version | integer | Номер версии HASH |
| bucket_pages | bigint | Количество страниц групп |
| overflow_pages | bigint | Количество страниц переполнения |
| bitmap_pages | bigint | Количество страниц битовой карты |
| unused_pages | bigint | Количество неиспользованных страниц |
| live_items | bigint | Количество «живых» кортежей |
| dead_tuples | bigint | Количество «мёртвых» кортежей |
| free_percent | float | Процент свободного пространства |
Функция pg_relpages возвращает число страниц в отношении. pg_relpages(text) returns bigint
Эта функция равнозначна функции pg_relpages(regclass) за исключением того, что для неё целевое отношение задаётся в текстовом виде. Данная функция оставлена для обратной совместимости, в будущем она может перейти в разряд устаревших. pgstattuple_approx(regclass) returns record
Выходные столбцы описаны в Таблице F.24.
Тогда как pgstattuple всегда производит полное сканирование таблицы и возвращает точное число живых и мёртвых кортежей (и их размер), а также точный объём свободного пространства, функция pgstattuple_approx пытается избежать полного сканирования и возвращает точную статистику только по мёртвым кортежам, а количество и объём живых кортежей, как и объём свободного пространства определяет приблизительно.
Она делает это, пропуская страницы, в которых, согласно карте видимости, есть только видимые кортежи (если для страницы установлен соответствующий бит, предполагается, что она не содержит мёртвых кортежей). Для таких страниц эта функция узнаёт объём свободного пространства из карты свободного пространства и предполагает, что остальное пространство на странице занято живыми кортежами.
На страницах, которые нельзя пропустить, она сканирует каждый кортеж, отражает его наличие и размер в соответствующих счётчиках и суммирует свободное пространство на странице. В конце она оценивает приблизительно общее число живых кортежей, исходя из числа просканированных страниц и кортежей (так же, как VACUUM рассчитывает значение pg_class.reltuples).
Таблица F.24. Столбцы результата pgstattuple_approx
| Столбец | Тип | Описание |
|---|---|---|
| table_len | bigint | Физическая длина отношения в байтах (точная) |
| scanned_percent | float8 | Просканированный процент таблицы |
| approx_tuple_count | bigint | Количество «живых» кортежей (приблизительное) |
| approx_tuple_len | bigint | Общая длина «живых» кортежей в байтах (приблизительная) |
| approx_tuple_percent | float8 | Процент «живых» кортежей |
| dead_tuple_count | bigint | Количество «мёртвых» кортежей (точное) |
| dead_tuple_len | bigint | Общая длина «мёртвых» кортежей в байтах (точная) |
| dead_tuple_percent | float8 | Процент «мёртвых» кортежей |
| approx_free_space | bigint | Общий объём свободного пространства в байтах (приблизительный) |
| approx_free_percent | float8 | Процент свободного пространства |
В показанном выше выводе показатели свободного пространства могут не соответствовать выводу pgstattuple в точности, потому что карта свободного пространства показывает верное значение, но не гарантируется, что оно будет точным до байта.
