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

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

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

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

Hint

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

Practice

1. What is the main purpose of the VACUUM command in PostgreSQL?

easy

A. To backup the database

B. To create new tables automatically

C. To clean up old, deleted data and free space

D. To increase the size of the database

Solution

1. Step 1: Understand what happens to deleted data

   When rows are deleted or updated, old versions remain and take space.

2. Step 2: Role of VACUUM

   VACUUM cleans these old rows to free space and keep the database efficient.

3. Final Answer:

   To clean up old, deleted data and free space -> Option C

4. Quick Check:

   VACUUM cleans old data = A [OK]

Hint: VACUUM removes old data to keep DB fast [OK]

Common Mistakes:

• Thinking VACUUM creates tables
• Confusing VACUUM with backup
• Believing VACUUM increases DB size

2. Which of the following is the correct syntax to run a basic VACUUM on a table named users?

easy

A. VACUUM TABLE users;

B. VACUUM users;

C. RUN VACUUM ON users;

D. CLEAN users VACUUM;

Solution

1. Step 1: Recall PostgreSQL VACUUM syntax

   The correct command is simply VACUUM followed by the table name.

2. Step 2: Check each option

   VACUUM users; matches the correct syntax: VACUUM users;

3. Final Answer:

   VACUUM users; -> Option B

4. Quick Check:

   Correct VACUUM syntax = C [OK]

Hint: VACUUM followed by table name, no TABLE keyword [OK]

Common Mistakes:

• Adding TABLE keyword incorrectly
• Using RUN or CLEAN commands
• Wrong order of keywords

3. Consider a table orders where many rows were deleted recently. After running VACUUM on it, what is the expected effect?

medium

A. The table size on disk will increase

B. The database will create a backup of the table

C. The table will be locked and unavailable during VACUUM

D. The deleted rows' space will be freed for reuse

Solution

1. Step 1: Understand what VACUUM does after deletes

   VACUUM frees space taken by deleted rows so it can be reused.

2. Step 2: Check other options

   VACUUM does not increase size, lock table fully (that's VACUUM FULL), or create backups.

3. Final Answer:

   The deleted rows' space will be freed for reuse -> Option D

4. Quick Check:

   VACUUM frees deleted space = D [OK]

Hint: VACUUM frees space from deleted rows, not increase size [OK]

Common Mistakes:

• Confusing VACUUM with VACUUM FULL locking
• Thinking VACUUM increases disk size
• Assuming VACUUM creates backups

4. You run VACUUM FULL on a large table but notice the table remains locked for a long time. What is the best way to fix this issue?

medium

A. Use regular VACUUM instead of VACUUM FULL

B. Restart the PostgreSQL server immediately

C. Run VACUUM FULL more frequently

D. Drop and recreate the table

Solution

1. Step 1: Understand locking behavior of VACUUM FULL

   VACUUM FULL locks the entire table, causing long waits on large tables.

2. Step 2: Choose a better approach

   Regular VACUUM does not lock the table fully and is better for large tables.

3. Final Answer:

   Use regular VACUUM instead of VACUUM FULL -> Option A

4. Quick Check:

   VACUUM FULL locks table; regular VACUUM doesn't = A [OK]

Hint: Use regular VACUUM to avoid long table locks [OK]

Common Mistakes:

• Restarting server unnecessarily
• Running VACUUM FULL more often without need
• Dropping table instead of vacuuming

5. You have a large table products with frequent updates and deletes. You want to reclaim disk space without locking the table for a long time. Which approach is best?

hard

A. Run regular VACUUM products; frequently and schedule VACUUM FULL during maintenance windows

B. Run VACUUM FULL products; daily during peak hours

C. Never run VACUUM and rely on autovacuum only

D. Drop and recreate the table every week

Solution

1. Step 1: Understand the impact of VACUUM FULL

   VACUUM FULL reclaims space but locks the table, so running it during peak hours is bad.

2. Step 2: Best practice for large tables with frequent changes

   Run regular VACUUM often to keep space reusable and schedule VACUUM FULL only during low-traffic times.

3. Final Answer:

   Run regular VACUUM products; frequently and schedule VACUUM FULL during maintenance windows -> Option A

4. Quick Check:

   Regular VACUUM often + VACUUM FULL off-peak = B [OK]

Hint: Use regular VACUUM often; VACUUM FULL only off-peak [OK]

Common Mistakes:

• Running VACUUM FULL during busy times
• Ignoring regular VACUUM
• Dropping table unnecessarily