A PostgreSQL table needs to be maintained in order for the database to continue being reliable, secure, and consistent.

Video About PostgreSQL Maintenance

Check Size of Table and Index

SELECT pg_size_pretty(pg_total_relation_size('my_table')) AS total_size, pg_size_pretty(pg_relation_size('my_table')) AS data_size;
SELECT pg_size_pretty(pg_relation_size('idx_name')) AS index_size;

Vacuum all Tables of Database

VACUUM;

Vacuum all Tables of Database and Show progress on Screen

VACUUM verbose;

Vacuum all Tables of Database and its lock the objects till processing

VACUUM FULL;

Vacuum all Tables of Database which Show progress on screen but its lock the objects till process

VACUUM FULL verbose;

Vacuum Table ( For Release Dead Tuples)

vacuum employee;

Vacuum Specific Table of Database and Show progress on Screen

vacuum verbose employee;

Vacuum Specific Table of Database which show progress on Screen and locks the table

vacuum full verbose employee;

Vacuum and Analyse Table which also show progress on Screen

vacuum analyze verbose employee;

Analyze All Objects of Database

Analyze;

Analyze All Objects of Database which progress on screen

analyze verbose;

Analyze Table

ANALYZE my_table;

Analyse Table which also show progress on Screen

ANALYZE verbose my_table;

Check Index of Tables

SELECT tablename,indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' AND tablename = 'employee';

Re Index All index in Tables

REINDEX table employee;

Re Index Table Index without Locking

REINDEX Table CONCURRENTLY employee;

Re Index Specific Index without Locking

REINDEX INDEX CONCURRENTLY my_index;