PostgreSQL - Manual VACUUM Execution Using psql

 

Overview

This guide explains how to connect to a PostgreSQL database using psql, configure session-level memory parameters, and run VACUUM ANALYZE on selected tables. All hostnames, database names, schema names, and table names are kept generic for reuse across environments.

1. Connect to the Database

psql -h <db-host> -U <username> -d <database_name>

Example Output

psql (client_version, server_version)

WARNING: psql major version mismatch may show this message.

SSL connection established.

Type "help" for help.

2. Execute a SQL Script (Optional)

\i /path/to/script.sql

Explanation

This command executes SQL commands stored in a file.

3. Enable Timing

\timing on

Explanation

This shows execution time for each command.

4. Set Session Memory Parameters

SET work_mem = '64MB';

SET maintenance_work_mem = '10GB';

Explanation

work_mem is used for query operations like sorting and hashing.

maintenance_work_mem is used for maintenance operations such as VACUUM and CREATE INDEX.

Increasing maintenance_work_mem can significantly improve VACUUM performance for large tables.

5. Validate Session Time

SELECT now();

6. Run VACUUM ANALYZE on Tables

VACUUM ANALYZE schema_name.table1;

VACUUM ANALYZE schema_name.table2;

VACUUM ANALYZE schema_name.table3;

VACUUM ANALYZE schema_name.table4;

VACUUM ANALYZE schema_name.table5;

VACUUM ANALYZE schema_name.table6;

VACUUM ANALYZE schema_name.table7;

Explanation

VACUUM removes dead tuples and frees space, while ANALYZE updates planner statistics.

Running both together ensures optimal query performance.

Best Practices

• Run during maintenance windows for large tables

• Increase maintenance_work_mem temporarily for large operations

• Monitor disk I/O and CPU during execution

• Avoid running many VACUUM operations concurrently

• Use autovacuum for routine maintenance

Conclusion

Manual VACUUM ANALYZE is useful for large tables or after heavy data modifications. Proper memory tuning and monitoring help reduce execution time and improve performance.