Научимся оценивать в процентном соотношении разрастание таблиц или индексов в PostgreSQL. Для оценки будем использовать расширение pgstattuple.
В начале скомпилируем расширение pgstattuple, которое поможет понять сколько в базе данных полезной информации, а сколько служебной или неактуальной. Для этого перейдем в каталог с исходниками и выполним:
postgres@s-pg13:~$ exit выход root@s-pg13:~# cd ~/pg/postgresql-13.3/contrib/pgstattuple/ root@s-pg13:~/pg/postgresql-13.3/contrib/pgstattuple# make ... root@s-pg13:~/pg/postgresql-13.3/contrib/pgstattuple# make install ... root@s-pg13:~/pg/postgresql-13.3/contrib/pgstattuple# su - postgres
Затем подключим расширение в самой базе данных с помощью команды CREATE EXTENSION:
postgres@s-pg13:~$ psql Timing is on. psql (13.3) Type "help" for help. postgres@postgres=# CREATE EXTENSION pgstattuple; CREATE EXTENSION Time: 45,688 ms
Создадим табличку bloat и наполним её данными. После чего сделаем для этой таблицы индекс:
postgres@postgres=# CREATE TABLE bloat (id serial, s text); CREATE TABLE Time: 7,015 ms postgres@postgres=# INSERT INTO bloat(s) SELECT g.id::text FROM generate_series(1,100000) AS g(id); INSERT 0 100000 Time: 210,917 ms postgres@postgres=# CREATE INDEX ON bloat(s); CREATE INDEX Time: 331,867 ms
Теперь с помощью функции pgstattuple(‘таблица’), которая входит в расширение pgstattuple узнаем сколько в таблице полезной информации, а сколько неактуальной или служебной:
postgres@postgres=# SELECT * FROM pgstattuple('bloat') \gx
-[ RECORD 1 ]------+--------
table_len | 4423680
tuple_count | 100000
tuple_len | 3388895
tuple_percent | 76.61
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 16552
free_percent | 0.37
Time: 8,817 ms
Из вывода нам важно посмотреть на два параметра:
- tuple_percent — процент полезной информации;
- free_percent — процент свободного пространства (неактуальные строки и пустоты).
Если от 100% вычтем tuple_percent и free_percent, то получим приблизительный размер служебной информации.
Теперь посмотрим на разрастание индекса с помощью функции pgstatindex(‘индекс’):
postgres@postgres=# SELECT * FROM pgstatindex('bloat_s_idx') \gx
-[ RECORD 1 ]------+--------
version | 4
tree_level | 1
index_size | 2260992
root_block_no | 3
internal_pages | 1
leaf_pages | 274
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 89.83
leaf_fragmentation | 0
Time: 1,678 ms
Из вывода обратите внимание на колонку avg_leaf_density — это заполненность страниц индекса. Если заполненность небольшая, значит в страницах есть пустоты.
Следующим шагом обновим половину строк, а затем опять посмотрим на разрастание таблицы:
postgres@postgres=# UPDATE bloat SET s = s || '!' WHERE id % 2 = 0;
UPDATE 50000
Time: 343,118 ms
postgres@postgres=# SELECT * FROM pgstattuple('bloat') \gx
-[ RECORD 1 ]------+--------
table_len | 6635520
tuple_count | 100000
tuple_len | 3438895
tuple_percent | 51.83
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 2017232
free_percent | 30.4
Time: 8,563 ms
Тут мы уже видим что tuple_percent упал до 51,83 %. То есть половина таблицы имеет неактуальные версии строк, dead_tuple_count — это количество неактуальных строк.
И еще раз посмотрим на индекс:
postgres@postgres=# SELECT * FROM pgstatindex('bloat_s_idx') \gx
-[ RECORD 1 ]------+--------
version | 4
tree_level | 2
index_size | 4513792
root_block_no | 412
internal_pages | 3
leaf_pages | 547
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 45.15
leaf_fragmentation | 49.91
Time: 0,545 ms
И здесь мы видим, что avg_leaf_density упал до 45.15 %. То есть в страницах образовались пустоты. Эти пустоты постепенно будут заполнятся, а когда в таблицу придет autovacuum, то неактуальные версии строк будут удалены. Так что avtovacuum и работа сервера PostgreSQL постепенно поправят ситуацию.
