In Postgres, large database tables can experience some issues such as table/index bloat or corrupted indexes, etc. So how to deal with such issues in PostgreSQL?

PostgreSQL offers several commands to optimize, improve or maintain the health of a database, tables, etc., such as ANALYZE and VACUUM commands.

This post will explain how to optimize the performance of a database or table in PostgreSQL using VACUUM and ANALYZE commands. So, let’s get started.

What is VACUUM Command and How to Use it in PostgreSQL?

It is a command-line utility that vacates the space engaged by obsolete records, tuples, etc. The VACUUM command optimizes the performance of the Postgres databases, records, etc.

Syntax:

The below syntax will guide you on how to use the VACUUM command in Postgres:

VACUUM [FULL] [FREEZE] [VERBOSE] (tab_name);

Here, the tab_name is optional, if you specify the tab_name, then only that specific table will be vacuumed; however, if you don’t specify the table’s name, then all the tables of the selected database will be vacuumed. FULL, FREEZE and VERBOSE are the optional arguments that VACUUM can accept. All these options serve different functionalities.

Example: How to Optimize Postgres Performance Using VACUUM Command?

Firstly, establish a connection with the desired database and then apply the VACUUM command on the example database:

\c example;

Now, let’s execute the below command to optimize the performance of example database using VACUUM command:

VACUUM VERBOSE;

The output shows that the VERBOSE option along with VACUUM provides the details of the vacuumed database. Similarly, you can specify the table name with the VACUUM command to optimize only specific table:

VACUUM FULL VERBOSE bike_details;

The output shows that the VACUUM command successfully optimized the performance of the selected table.

What is ANALYZE Command and How to Use it in PostgreSQL?

It is a command line utility that the ANALYZE command collects the statistics about a database, table, or table’s columns for the query planner. These collected stats can be used by the query planner to yield efficient/appropriate execution plans for the Postgres queries.

Syntax:

The ANALYZE command runs on all the tables available in the selected schema. However, you can specify the table’s name with the ANALYZE command to get the stats of only that specific table:

ANALYZE VERBOSE [tab_name];

Example: How to Optimize Postgres Performance Using ANALYZE Command?

Follow the below-listed steps to learn the working of ANALYZE command:

Step #1: List Databases

Let’s execute the \l command to get the list of databases:

\l;

Step #2: Connect to Database

Suppose we want to get the stats of the “example” database. To do that, firstly, we will establish a connection with the selected database using the “\c” command:

\c example;

Step #3: Get Stats of Specific Database

We have successfully established a connection with the targeted database, i.e., “example”. Now, we can get the stats of the “example” database using ANALYZE command:

ANALYZE;

In this way, the ANALYZE command can be used to get the database/table stats.

Step # 4: Optimize Postgres Performance

Run the below command to optimize Postgres performance using the VACUUM and ANALYZE commands combinedly:

VACUUM VERBOSE ANALYZE;

This is how the ANALYZE command works with the VACUUM command.

Conclusion

PostgreSQL offers several commands to optimize, improve or maintain the health of a database, tables, etc., such as ANALYZE and VACUUM commands. Both of them are command line utilities that are used to optimize the Postgres performance. This post shows you how to use ANALYZE and VACUUM commands to improve the Postgres performance.