VACUUM and its importance in PostgreSQL - Mini Project: Build & Apply

Understanding VACUUM and Its Importance in PostgreSQL

📖 Scenario: You are managing a PostgreSQL database for a small online bookstore. Over time, as customers place and cancel orders, the database accumulates dead rows that slow down queries and waste disk space.To keep the database efficient, you need to learn how to use the VACUUM command to clean up these dead rows.

🎯 Goal: Learn how to identify dead rows and run the VACUUM command in PostgreSQL to maintain database performance and reclaim storage.

📋 What You'll Learn

Create a sample table with some data

Insert and delete rows to create dead tuples

Check the number of dead tuples using system views

Run the VACUUM command to clean dead tuples

💡 Why This Matters

🌍 Real World

Database administrators regularly use VACUUM to keep PostgreSQL databases efficient by removing dead rows and reclaiming disk space.

💼 Career

Understanding VACUUM is essential for roles like database administrator, backend developer, and data engineer to maintain database health and performance.

1

Create a sample table and insert data

Create a table called orders with columns order_id (integer primary key) and customer_name (text). Insert these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie').

PostgreSQL

-- Create the orders table and insert initial rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Delete a row to create dead tuples

Delete the row where order_id is 2 from the orders table to create dead tuples.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

INSERT INTO orders (order_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Delete the row with order_id 2
-- Your code here

Need a hint?

Use DELETE FROM orders WHERE order_id = 2; to remove the row.

3

Check dead tuples count using pg_stat_user_tables

Query pg_stat_user_tables to select relname and n_dead_tup for the orders table to see the number of dead tuples.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

INSERT INTO orders (order_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

DELETE FROM orders WHERE order_id = 2;

-- Query dead tuples count for orders table
-- Your code here

Need a hint?

Use SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders'; to check dead tuples.

4

Run VACUUM to clean dead tuples

Run the VACUUM command on the orders table to clean up dead tuples and reclaim space.

PostgreSQL

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

INSERT INTO orders (order_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

DELETE FROM orders WHERE order_id = 2;

SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';

-- Run VACUUM on orders table
-- Your code here

Need a hint?

Use VACUUM orders; to clean dead tuples in the orders table.