VACUUM and its importance in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

What does the VACUUM command do in PostgreSQL?

Choose the correct description of what the VACUUM command does in PostgreSQL.

A It deletes all rows from a table permanently without logging.

B It backs up the entire database to an external file.

C It creates indexes on all columns of a table automatically.

D It removes dead tuples and frees storage space to prevent table bloat.

💡 Hint

Think about how PostgreSQL manages storage after updates and deletes.

The VACUUM command cleans up dead tuples left by updates and deletes, freeing space and preventing table bloat.

❓ query_result

intermediate

Result of VACUUM FULL on table size

Given a table users with many deleted rows, what is the expected effect of running VACUUM FULL users;?

A The table size remains the same; only statistics are updated.

B The table size will increase because VACUUM FULL duplicates data.

C The table size on disk will shrink significantly by reclaiming space.

D The table will be locked but size will not change.

💡 Hint

Consider how VACUUM FULL differs from regular VACUUM.

VACUUM FULL rewrites the entire table, compacting it and reducing disk space used.

📝 Syntax

advanced

Identify the correct VACUUM syntax to analyze and clean a table

Which of the following commands correctly performs a VACUUM ANALYZE on the orders table?

A VACUUM ANALYZE orders;

B VACUUM orders ANALYZE;

C ANALYZE VACUUM orders;

D VACUUM TABLE orders ANALYZE;

💡 Hint

Check the order of keywords in the VACUUM command.

The correct syntax is VACUUM ANALYZE table_name; to clean and update statistics.

❓ optimization

advanced

Choosing between VACUUM and VACUUM FULL for performance

You notice a table has grown large due to many updates and deletes. Which statement about using VACUUM versus VACUUM FULL is correct?

A VACUUM FULL is faster and does not lock the table, VACUUM locks the table during operation.

B VACUUM FULL reclaims more space but locks the table, while VACUUM is less intrusive but reclaims less space.

C VACUUM and VACUUM FULL have the same effect and locking behavior.

D VACUUM FULL only updates statistics, VACUUM reclaims space.

💡 Hint

Think about table locking and space reclamation differences.

VACUUM FULL locks the table exclusively but reclaims more space by rewriting it. Regular VACUUM is less disruptive but reclaims less space.

🔧 Debug

expert

Why does autovacuum not reclaim space immediately?

After many deletes, autovacuum runs but the table size on disk does not shrink. Why does this happen?

A Autovacuum only marks dead tuples as reusable but does not shrink the physical file size.

B Autovacuum is disabled by default and does not run automatically.

C Autovacuum deletes rows permanently and shrinks the table immediately.

D Autovacuum locks the table and prevents any space reclamation.

💡 Hint

Consider what autovacuum does versus VACUUM FULL.

Autovacuum cleans dead tuples so space can be reused but does not reduce the physical file size; only VACUUM FULL rewrites and shrinks the table.