Maintenance

Garbage collect (reclaim storage)

VACUUM [__Table__] vacuum(verbose, analyze) employee; INFO: vacuuming "public.employee" INFO: scanned index "employee_pkey" to remove 1 row versions . . . sample, 1 estimated total rows VACUUM

// Use the vacuum command to reclaim storage from deleted rows in the employee table (SQL)

1. Table rows that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM command is executed. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables.

2. Verbose Prints a detailed vacuum activity report for each table.

3. Analyze update statistics for table.

Gather statistics

ANALYZE [__table__] analyze verbose employee; INFO: analyzing "public.employee" INFO: "employee": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows ANALYZE

// Analyzes a table and stores the results in the pg_statistic system catalog (SQL)

1. ANALYZE gathers statistics for the query planner to create the most efficient query execution plans. Accurate statistics assist planner to choose the most appropriate query plan, and thereby improve the speed of query processing.

2. Verbose Prints a detailed analyze activity report for each table.

3. With no table name specified, ANALYZE examines every table in the current database.