shurtupin_HW3.md

1. Создать таблицу с текстовым полем и заполнить случайными или сгенерированными
   данным в размере 1 млн строк

CREATE TABLE hw3 (text text);  
SELECT pg_size_pretty(pg_total_relation_size('hw3')) AS total_size;  
 8192 bytes
INSERT INTO hw3 (text)  
SELECT substr(md5(random()::text), 1, 10)  
FROM generate_series(1, 1000000);  

2. Посмотреть размер файла с таблицей

base/16726/17129  
 ls -lah /var/lib/postgresql/14/main/base/16726/ | grep 17129  
-rw-------  1 postgres postgres  85M Oct 13 14:43 17129  

3. 5 раз обновить все строчки и добавить к каждой строчке любой символ

UPDATE hw3  
SET text = text || chr(97 + floor(random() * 26)::int); 

4. Посмотреть количество мертвых строчек в таблице и когда последний раз приходил
   автовакуум

wb=# SELECT relname AS table_name, n_dead_tup AS dead_tuples, last_autovacuum FROM pg_stat_all_tables WHERE relname = 'hw3';  
 table_name | dead_tuples |        last_autovacuum          
 hw3        |           0 | 2024-10-14 11:41:18.323752+03  
85 MB  

5. Подождать некоторое время, проверяя, пришел ли автовакуум
6. 5 раз обновить все строчки и добавить к каждой строчке любой символ

x5  
UPDATE hw3  
SET text = text || chr(97 + floor(random() * 26)::int);  

7. Посмотреть размер файла с таблицей

$ ls -lah /var/lib/postgresql/14/main/base/16726/ | grep 17129  
-rw-------  1 postgres postgres 100M Oct 14 11:44 17129

8. Отключить Автовакуум на конкретной таблице

ALTER TABLE hw3 SET (autovacuum_enabled = false);  

9. 10 раз обновить все строчки и добавить к каждой строчке любой символ

 table_name | dead_tuples |        last_autovacuum  
------------+-------------+-------------------------------  
 hw3        |    10000000 | 2024-10-14 11:44:19.216864+03  

10. Посмотреть размер файла с таблицей

$ ls -lah /var/lib/postgresql/14/main/base/16726/ | grep 17129  
-rw-------  1 postgres postgres 586M Oct 14 11:47 17129  

11. Объясните полученный результат

При включенном автовакууме во время обновления всех строк в таблице он включался (autovacuum_vacuum_scale_factor: 0.05) и, в идеальном случае, успевал после каждой итерации обновления данных вычищать весь млн. мертвых строк. В следствии чего следующая итерация обновления таблицы не создавала новые страницы на диске, а вставляла обновленные строки на место вычищенных мертвых. И только в конце, когда строки стали увеличиваться, из-за того, что в каждой итерации добавлялся символ в конец строки, размер таблицы начал незначительно увеличиваться (85->100)  
При выключенном автовакууме каждое полное обновление таблицы фактически добавляло млн. строк в нее, помечая старые строки как мертвые, но не очищая их. В следствии чего после 10 итераций мы увидели 10 млн. мертвых строк. Пройдя автовакуумом мы не уменьшим физ. размер нашей таблицы, но у нас будет "дырок" доступных для заполнения на целых 10 млн. Чтобы физически его освободить надо воспользоваться vacuum full, но потребуется эксклюзивная блокировка на таблицу для ее перестройки.  

P.S. При первом выполнении этого задания начал с процедуры, написал ее и видимо при ее прогоне CALL update_table('hw3', 5) так быстро изменялись данные в таблице, что autovacuum не успевал их вычищать и даже с включенным автовакуумом после 10 итераций таблица распухала значительно (с 50 до ~300). Поэтому приходилось запускать ее с 1 циклом, чтобы было более наглядно.
Задание со *:

Написать анонимную процедуру, в которой в цикле 10 раз обновятся все строчки в
искомой таблице.
Не забыть вывести номер шага цикла.

CREATE OR REPLACE PROCEDURE update_table(table_name TEXT, num INT)  
LANGUAGE plpgsql  
AS $$  
DECLARE  
    n INT;  
    query TEXT;  
BEGIN  
    FOR n IN 1..num LOOP  
        RAISE NOTICE 'Step %', n;  
        query := 'UPDATE ' || quote_ident(table_name) || ' SET text = text || chr(97 + floor(random() * 26)::int)';  
        EXECUTE query;  
    END LOOP;  
END;  
$$;