PostgreSQL Maintenance

 Database maintenance is essential for ensuring the optimal performance, reliability and security of your database systems.

All databases requires some kind of maintenance tasks be performed regularly to achieve optimum performance.

Maintenance task are ongoing and repetitive which are ideally automated and schedule from cron scripts and task scheduler 

• Routine vacuuming
• Reindex
• Log File Maintenance 

Vacuuming Basics : PostgreSQL databases require periodic maintenance known as vacuuming. 

PostgreSQL includes an auto vacuum process that automatically runs vacuum and analyze operations.

VACUUM command has to process each table on a regular basis for several reasons:

• To recover or reuse disk space occupied by updated or deleted rows.
• To update data statistics used by the PostgreSQL query planner.
• To update the visibility map, which speeds up index-only scans.
• To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.

Note : Delete row it is not seen but occupied space and same case with update . This is vacuuming come to picture it is removed all this rows .

Variants of Vacuum :

There are two variants of VACUUM:

• Vacuum
• Full Vacuum 

Vacuum : Bast operation to reclaim storage and update statistics.

                    Syntax : VACUUM table_name

Vacuum Full : More intensive operation that rewrites the entire table, reclaiming more space but required more resource and locking the table.

                   Syntax : VACUUM FULL table_name;

Demo : Vacuuming

Create table sample1(stri varchar(12),cpu int, memory bigint);

ALTER TABLE sample1 SET (autovacuum_enabled = false);

insert into sample1 select 'Hello World',random()* 100 as cpu , random() * 10 as memory from generate_series(1,10000);

\dt+ sample1 -- 536 kb

#delete from sample1 where cpu < 40;

#select count(*) from sample1;

#explain select * from sample1; Note :This is not give correct values

aws=# +\x

Expanded display is on.

aws=# select * from pg_stat_user_tables where relname='sample1';

aws=# vacuum(full,analyze,verbose) sample1;

Vacuum Freeze:

• Vacuum freeze is a special kind of vacuum which marks rows as frozen.
• Vacuum Freeze marks a table's contents with a very special transaction timestamp that tells postgres that it does not need to be vacuumed ever.
• Vacuum Freeze reduces Autovacuum Overhead, Since frozen rows don't need future cleanup, PostgreSQL spends less time running autovacuum freeing up resource for other task.
• Queries run faster because PostgreSQL doesn't have to check transaction IDs for frozen rows , reducing unnecessary computations.
• Think of it like preserving old documents - Once archived, They don't need further updates, making retrieval faster and safer.
•