Getting Inside ‘VACUUM’

We often hear the term ‘database maintenance’. So what exactly is it?
Everything requires effective maintenance, including your database. Regular maintenance helps it run and perform efficiently to meet your business expectations. Database Maintenance describes a set of tasks that are run with the intention to improve your database. There are routines meant to help performance, free up disk space, check for data errors, hardware faults, update internal stats, and many other indistinct (but generally important) things.

Database maintenance is a highly neglected topic in daily PostgreSQL operation. While it is a common understanding that database backups need to be done regularly and essentially, only a few users are aware of the extra work that goes into it. One of the main reasons, you don’t see many database maintenance works is the lack of in-depth knowledge of SQL itself, and being able to carry out the tasks with efficient timelines.
At times, a database maintenance not done properly may go unnoticed, but when the database performance is hit; is when it turns into a real issue.

THIS WHITEPAPER WILL TAKE YOU THROUGH THE COMMON DATABASE MAINTENANCE TASKS IN POSTGRESQL

Key Highlights:

1: Vacuum is processed in PostgreSQL, which does a clean-up job of dead rows or dead tuples. It is like the defragmentation activity of dead rows/tuples or commonly known as bloat.

2: PostgreSQL maintains the old tuples versioning for visibility in a transaction via MVCC. Hence, it will not remove immediately, and due to the MVCC functionality, it keeps those versions of data unless someone tells it to remove them.

3: There is an automatic vacuum process in PostgreSQL, but many times for specific load, DBA prefers to schedule this for better performance.

4: If someone does not vacuum frequently, then database performance will go down as versions of data tuples increase. In fact, after a few days/months, it may crash due to transaction wraparound.

Download Whitepaper