In simple terms, VACUUM is a process of cleaning DEAD rows/tuples from the database.

Why use VACUUM in PostgreSQL?

PostgreSQL does not make use of the IN-PLACE update mechanism. This mechanism means whenever DELETE operations are performed on rows, mark these deleted rows as DEAD instead of removing rows physically. Similarly, it works with the UPDATE operation marks the old existing record as DEAD, and inserts the new row.

So, there will be always DEAD rows present in the database. In order to reclaim unused space from DEAD rows, we make use of VACUUM.

Types of VACUUM

Plain VACUUM: Frees up space for re-use.

Syntax: VACUUM [tablename];

Full VACUUM: Frees up more space than plain vacuum and locks the database table during the process.

Syntax: VACUUM FULL [tablename];

Full VACUUM and ANALYZE: Performs full vacuum, analysis, and Updates statistics used by the planner to determine the most efficient way to execute a query.

Syntax: VACUUM FULL ANALYZE [tablename];

Full VACUUM, ANALYZE, and VERBOSE: Performs full vacuum, analysis, and display action for each activity.

Syntax: VACUUM (FULL, ANALYZE, VERBOSE) [tablename];

Let’s consider an example to understand practically more on VACUUM.

PostgreSQL extension pg_freespacemap can help us in understanding the available free space after the dead tuple.

Query to check the number of pages and the average ratio of Freespace.

SELECT COUNT(*) AS npages, round(100 * AVG(avail)/8192 ,2) AS average_freespace_ratio FROM pg_freespace(‘medm’);

After running VACUUM medm;

After running VACUUM FULL medm;

So, this was all about manual VACCUM. We also have an option for auto VACUUM. To read about auto VACUUM

Links for reference:

Do clap, if find useful🙂. Do share your experience with using VACUUM in databases.

Keep learning and keep sharing knowledge.✌

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻

Love podcasts or audiobooks? Learn on the go with our new app.

BOSS LIFE

PhoneGap Devs: It’s Time to Embrace a UI Framework

A class for patterns ?

CS371p Fall 2020: Andrew Chen — Week 5

Scrape your service RabbitMQ messages with Prometheus | Kubernetes

Being a full-stack developer: A guide to your responsibilities — Part 1

How I managed to encounter and recover from Computer Science’s Two Hardest Problems

IBM DB2 Cloud Database and SQL : PART 1

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Nidhi Gupta

Nidhi Gupta

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻

More from Medium

Database Basics: Database Introduction, SQL vs NoSQL

Databases Overview — Thumbnail

Using cursors and for loops in MySQL

Using cursors and for loops in MySQL

How to set sequence to value?, How to sequence set current value? In SQL / Postgresql

Log Analysis with PgBadger