PostgreSQL Database Maintenance

Introduction

In this lab, you will learn essential PostgreSQL database maintenance tasks. The goal is to understand how to monitor database object sizes, perform routine maintenance to optimize performance, and troubleshoot potential issues by checking connections and logs.

You will begin by connecting to a PostgreSQL database and querying the sizes of tables and indexes. Then, you will learn how to run ANALYZE to update database statistics and VACUUM to reclaim storage space. Finally, you will cover how to list active client connections and check server logs for errors to diagnose and resolve database problems.

Querying Database Object Sizes

In this step, you will connect to the PostgreSQL database and query the sizes of a table and its index. Understanding the size of your database objects is crucial for performance tuning and capacity planning.

First, open a terminal. To connect to the PostgreSQL server as the postgres user and access the mydatabase database, execute the following command:

sudo -u postgres psql mydatabase

You should see the psql prompt (mydatabase=#), indicating you are connected to the mydatabase database. All subsequent SQL commands in this lab should be run in this psql shell unless otherwise specified.

Now, let's determine the size of mytable. We will use the pg_size_pretty and pg_relation_size functions. pg_relation_size returns the size of a table in bytes, and pg_size_pretty formats it into a human-readable format (e.g., KB, MB).

Execute the following SQL query to get the size of mytable:

SELECT pg_size_pretty(pg_relation_size('mytable'));

You should see output similar to this, showing the disk space used by the table's data:

 pg_size_pretty
----------------
 56 kB
(1 row)

Next, check the size of the index idx_mytable_name that was created on the name column:

SELECT pg_size_pretty(pg_relation_size('idx_mytable_name'));

The output will show the space consumed by the index:

 pg_size_pretty
----------------
 48 kB
(1 row)

Finally, to get the total size of the table including all its indexes, use the pg_total_relation_size function:

SELECT pg_size_pretty(pg_total_relation_size('mytable'));

This output shows the combined size of the table and its index:

 pg_size_pretty
----------------
 176 kB
(1 row)

Optimizing with ANALYZE

In this step, you will learn about the ANALYZE command, which is essential for maintaining good query performance.

Understanding ANALYZE

The ANALYZE command collects statistics about the contents of tables in the database. The PostgreSQL query planner uses these statistics to choose the most efficient execution plans for queries. Without accurate statistics, the planner might make poor choices, leading to slow query performance. It is good practice to run ANALYZE periodically, especially after significant changes to table data.

While still in the psql shell, run ANALYZE on the mytable table:

ANALYZE mytable;

This command analyzes mytable and updates its statistics. You will see the following output, confirming the command was successful:

ANALYZE

Although the command simply returns ANALYZE, it has updated the internal statistics for mytable in the background.

Reclaiming Storage with VACUUM

In this step, you will use the VACUUM command to perform another critical maintenance task: reclaiming storage.

Understanding VACUUM

In PostgreSQL, when a row is updated or deleted, the old version of the row (a "dead tuple") is not immediately removed from the disk. VACUUM reclaims the storage occupied by these dead tuples, making the space available for reuse. It also updates data visibility information, which helps improve query performance.

Let's run VACUUM on the mytable table. In the psql shell, execute:

VACUUM mytable;

This command will process the table and return a confirmation:

VACUUM

You can also combine VACUUM and ANALYZE into a single, efficient command. This is a common maintenance practice.

VACUUM ANALYZE mytable;

This command first reclaims storage and then updates statistics for the table, returning VACUUM upon completion.

Note: There is also a VACUUM FULL command, which more aggressively reclaims space and shrinks the on-disk file. However, it locks the entire table, preventing any reads or writes during its operation, so it should be used with caution and only when necessary.

Monitoring Connections and Logs

In this final step, you will learn how to monitor active database connections and check the server logs for errors, which are key skills for troubleshooting.

First, while still in the psql shell, you can query the pg_stat_activity view to see all active connections to the server.

Execute the following SQL query:

SELECT datname, usename, client_addr, state FROM pg_stat_activity;

This query shows the database name (datname), user name (usename), client's IP address (client_addr), and the connection's current state (state). You will see your own connection listed in the output, similar to this:

  datname  | usename  | client_addr | state
-----------+----------+-------------+--------
 mydatabase| postgres |             | active
 postgres  | postgres |             |
           | labex    |             |
(3 rows)

Next, you will check the server logs. To do this, you must first exit the psql shell. Type \q and press Enter:

\q

You are now back in the standard Linux terminal prompt. PostgreSQL logs are located in the /var/log/postgresql/ directory. On this system, the log file is postgresql-14-main.log.

Use the grep command to search for any lines containing "ERROR" in the log file:

grep ERROR /var/log/postgresql/postgresql-14-main.log

If there are no errors, this command will produce no output. This is the expected result for this lab, as we have not performed any actions that would cause an error. Examining server logs is a fundamental step in diagnosing database problems.

Summary

In this lab, you have learned how to perform essential PostgreSQL database maintenance tasks. You now understand how to monitor the size of tables and indexes, run ANALYZE to optimize query performance, and use VACUUM to reclaim storage space. You also practiced listing active client connections and checking server logs for errors. These skills are crucial for ensuring the health, performance, and reliability of your PostgreSQL database.