Improving PostgreSQL Performance

 Optimize and Improve PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX utility.

VACUUM:

-Reclaims storage occupied by dead tuples with the following commands

1. vacuum analyze
2. vacuum full

AWS RDS show the database load in active session. It shows timeout due to vacuum delay.

REINDEX

It rebuilds one or more indices, replacing the previous version of the index. If an index has become corrupted, and no longer contains valid data, reindex can be executed.

REINDEX INDEX myindex, REINDEX TABLE mytable etc.

ANALYZE 

-It collects statistics about specific table columns, entire table, or entire database. The PostgreSQL query planner then uses that data to generate efficient execution plans for queries. Samples:

ANALYZE users; collects statistics for users table.

ANALYZE VERBOSE users; does exactly the same plus prints progress messages.

ANALYZE users (id, display_name); collects statistics for id and display_name columns of users table.

ANALYZE; collects statistics for all table in the current database.

To see the results of actually executing the query, you can use the EXPLAIN ANALYZE command:

EXPLAIN

- To see how a query is executing and adjust the query to be more efficient

EXPLAIN ANALYZE SELECT seqid FROM traffic WHERE serial_id<21;

- Instead of returning the data provides a query plan detailing what approach the planner took to executing the statement provided.

//All above VACUUM, ANALYZE, and REINDEX need to be executed though admin user.