Очистка

Подключитесь ролью postgres к одноименной БД.
```
[postgres@ServerName ~]$ psql
```
```
psql (15.5)
Type "help" for help.
postgres=#
```
Вы получили приглашение psql для базы данных postgres.

Создайте БД monman_db и подключитесь к ней.

postgres@postgres=# CREATE DATABASE monman_db;

CREATE DATABASE

postgres@postgres=# \c monman_db

You are now connected to database "monman_db" as user "postgres".

Создайте таблицу fatab с двумя столбцами.

postgres@monman_db=# CREATE TABLE fatab (id serial PRIMARY KEY, dte date);

CREATE TABLE

Посмотрите структуру созданной таблицы.

postgres@monman_db=# \d+ fatab

                                  Table "public.fatab"
Column  | Type    | Collation | Nullable |           Default                 | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+-----------------------------------+---------+-------------+--------------+-------------
     id | integer |           | not null | nextval('fatab_id_seq'::regclass) |  plain  |             |              |
    dte | date    |           |          |                                   |  plain  |             |              |
Indexes.
        "fatab_pkey" PRIMARY KEY, btree (id)
Access method: heap

Оба столбца таблицы используют метод хранения plain, то есть отсутствует TOAST для чистоты эксперимента.

Запретите автоочистке обрабатывать таблицу fatab.
```
postgres@monman_db=# ALTER TABLE fatab SET (autovacuum_enabled = off);
```
```
ALTER TABLE
```
Таблица fatab исключена из обработки автоочисткой, что позволит управлять процессом очистки вручную.
Вставьте в таблицу 100000 строк.
```
postgres@monman_db=# INSERT INTO fatab(dte) SELECT current_date + g.i FROM generate_series(1,100000) AS g(i);
```
Разбор команды
- INSERT INTO fatab(dte) — вставка значений в столбец dte таблицы fatab;
- SELECT current_date + g.i — генерация дат путем прибавления числа к текущей дате;
- FROM generate_series(1,100000) AS g(i) — функция генерации последовательности целых чисел от 1 до 100000, доступная как g(i).
```
INSERT 0 100000
```

Получите суммарный размер таблицы.

postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));

pg_size_pretty
----------------
5776 kB
(1 row)

Измените 50 % строк таблицы и снова проверьте ее размер.

Выполните обновление строк с четными значениями id.
```
postgres@monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0;
```
```
UPDATE 50000
```
Проверьте новый размер таблицы.
```
postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));
```
```
pg_size_pretty
----------------
9744 kB
(1 row)
```
Размер таблицы увеличился, так как у 50 % строк появились новые версии. Старые, уже ненужные версии все так же находятся на своих местах.
Повторите то же самое для нечетных строк.

Выполните обновление строк с нечетными значениями id.
```
postgres@monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 1;
```
```
UPDATE 50000
```
Проверьте новый размер таблицы.
```
postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));
```
```
pg_size_pretty
----------------
10 MB
(1 row)
```
Размер снова увеличился, хоть и не настолько, как в прошлый раз. Это связано с внутристраничной очисткой, но 50 % строк все равно имеют неактуальные версии, которые можно очистить.

Выполните очистку.

Запустите VACUUM с опцией VERBOSE для получения детального отчета о работе.

postgres@monman_db=# VACUUM VERBOSE fatab;

INFO: vacuuming "monman_db.public.fatab"
INFO: finished vacuuming "monman_db.public.fatab": index scans: 1
pages: 0 removed, 758 remain, 758 scanned (100.00% of total)
tuples: 50000 removed, 100000 remain, 0 are dead but not yet removable, oldest xmin: 838

removable cutoff: 838, which was 0 XIDs old when operation ended
new relfrozenxid: 836, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan needed: 443 pages from table (58.44% of total) had 71222 dead item identifiers removed
index "fatab_pkey": pages: 551 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 0.216 MB/s, avg write rate: 0.541 MB/s
buffer usage: 2519 hits, 2 misses, 5 dirtied
WAL usage: 2196 records, 5 full page images, 660831 bytes
system usage: CPU: user: 0.05 s, system: 0.00 s, elapsed: 0.07 s
VACUUM

Проверьте размер таблицы после очистки.

postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));

pg_size_pretty
----------------
10 MB
(1 row)

Команда очистки отработала, было очищено 50000 версий строк, а также был очищен индекс. Но размер таблицы остался без изменений. Так и должно быть: задача очистки состоит в освобождении места на страницах для вставки новых версий строк.

Несколько раз повторите эксперимент из пункта 8, но каждый раз выполняйте очистку. Затем измерьте размер таблицы, он должен перестать увеличиваться.

Обновите строки и выполните очистку для нечетных id.

postgres@monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 1; VACUUM fatab;

UPDATE 50000
VACUUM

Обновите строки и выполните очистку для четных id.

postgres@monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0; VACUUM fatab;

UPDATE 50000
VACUUM

Повторите для нечетных id.

postgres@monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 1; VACUUM fatab;

UPDATE 50000
VACUUM

Повторите для четных id.

postgres@monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0; VACUUM fatab;

UPDATE 50000
VACUUM

Измерьте итоговый размер таблицы.

postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));

pg_size_pretty
----------------
10 MB
(1 row)

Размер таблицы стабилизировался на уровне 10 МБ — регулярная очистка предотвращает рост за счет устаревших версий строк.

Получите имя файла данных таблицы, перестройте ее командой VACUUM FULL, проверьте получившийся размер и снова получите имя файла таблицы.

Узнайте относительный путь к файлу данных таблицы.

postgres@monman_db=# SELECT pg_relation_filepath('fatab');

pg_relation_filepath
----------------------
base/16441/16457
(1 row)

Файл данных таблицы находится по пути base/16441/16457.

Выполните полную перестройку таблицы.

postgres@monman_db=# VACUUM FULL fatab;

VACUUM

Проверьте размер перестроенной таблицы.

postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));

pg_size_pretty
----------------
5752 kB
(1 row)

Узнайте новый путь к файлу данных таблицы.

postgres@monman_db=# SELECT pg_relation_filepath('fatab');

pg_relation_filepath
----------------------
base/16441/16463
(1 row)

Размер таблицы уменьшился, поскольку все актуальные версии строк были физически скопированы в другой файл. Старый файл назывался 16457, новый — 16463 (у вас эти названия могут быть другими).

Сравните размеры старого и нового файлов на диске.

postgres@monman_db=# \! ls -l $PGDATA/base/16441/{16463,16457}

-rw------- 1 postgres postgres 0 Nov 10 21:46 /pgdata/06/data/base/16441/16457
-rw------- 1 postgres postgres 3629056 Nov 10 21:46 /pgdata/06/data/base/16441/16463

Это размер файла данных. Функция pg_total_relation_size() показывает общий размер таблицы со всеми зависимыми объектами, включая индексы.

Самопроверка

Вопрос 1

В сеансе psql выполнены команды:

postgres@monman_db=# INSERT INTO fatab(dte) SELECT current_date + g.i FROM generate_series(1,100000) AS g(i);
INSERT 0 100000

postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));
 pg_size_pretty
----------------
 5776 kB
(1 row)

postgres@monman_db=# UPDATE fatab SET dte = dte - 1 WHERE id % 2 = 0;
UPDATE 50000

postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));
 pg_size_pretty
----------------
 9744 kB
(1 row)

postgres@monman_db=# VACUUM fatab;
VACUUM

postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));

Какой размер таблицы будет получен в результате выполнения последнего запроса?

Вопрос 2

В сеансе psql выполнены команды:

postgres@monman_db=# SELECT pg_relation_filepath('fatab');
 pg_relation_filepath
----------------------
 base/16441/16457
(1 row)

postgres@monman_db=# VACUUM FULL fatab;
VACUUM

postgres@monman_db=# SELECT pg_relation_filepath('fatab');
 pg_relation_filepath
----------------------
 base/16441/16463
(1 row)

Почему pg_relation_filepath() вернул другой путь после VACUUM FULL?

VACUUM FULL изменил OID базы данных, но не файлового узлаVACUUM FULL скопировал актуальные строки в новый файл данных, изменив OID файлового узлаVACUUM FULL удалил старый файл и пересоздал таблицу в той же позицииVACUUM FULL переместил таблицу в другое табличное пространство

Вопрос 3

В сеансе psql выполнены команды:

postgres@monman_db=# SELECT pg_size_pretty(pg_total_relation_size('fatab'));
 pg_size_pretty
----------------
 5752 kB
(1 row)

postgres@monman_db=# \! ls -l $PGDATA/base/16441/16463
-rw------- 1 postgres postgres 3629056 Nov 10 21:46 /pgdata/06/data/base/16441/16463

Почему pg_total_relation_size() показывает 5752 kB, а файл на диске — 3629056 байт (~3.5 MB)?

pg_total_relation_size() включает размер всех индексов и TOAST, а ls показывает только основной слой mainpg_total_relation_size() округляет размер до килобайт, а ls — точный в байтахРазница вызвана тем, что файл сжат на уровне файловой системыpg_total_relation_size() включает слои fsm и vm, которые не видны в файловой системе

Самопроверка​

Вопрос 1

Вопрос 2

Вопрос 3

Самопроверка