PostgreSQL Vacuum and Vacuum full are not two different processes

-

 PostgreSQL’s VACUUM and VACUUM FULL are not separate processes but rather different operational modes of the same maintenance command. Here’s why:

Core Implementation

Both commands share the same underlying codebase and are executed through the vacuum_rel() function in PostgreSQL’s source code (src/backend/commands/vacuum.c). The key distinction lies in the FULL option, which triggers additional steps:

  • Standard VACUUM:
    • Removes dead tuples (obsolete rows) and marks space reusable within PostgreSQL
    • Updates the visibility map to optimize future queries
    • Runs concurrently with read/write operations
  • VACUUM FULL:
    • Rewrites the entire table into a new disk file, compressing it and reclaiming space for the operating system
    • Rebuilds all indexes and requires an ACCESS EXCLUSIVE lock, blocking other operations

Key Differences in Behavior

AspectStandard VACUUMVACUUM FULL
Space ReclamationInternal reuse onlyOS-level space release
LockingNon-blockingFull table lock
Performance ImpactLightweight, incrementalHeavy, resource-intensive
Use CaseRoutine maintenanceSevere table bloat remediation

 

Why They Aren’t Separate Processes

  • Shared Code Path: Both use the same core logic for dead-tuple identification and cleanup. VACUUM FULL adds a table-rewrite step by calling cluster_rel()
  • Configuration Integration: Parameters like autovacuum_vacuum_scale_factor apply to both, and autovacuum workers handle standard VACUUM by default
  • Unified Command Structure: The FULL option is a modifier rather than a standalone tool, as seen in the SQL syntax:

When to Use Each

  • Standard VACUUM: Daily maintenance to prevent bloat from MVCC dead tuples
  • VACUUM FULL: Rarely, for extreme cases where table size has grown uncontrollably due to long-unvacuumed updates/deletes

In summary, while their outcomes differ significantly, VACUUM and VACUUM FULL are part of a single maintenance framework, differentiated primarily by the aggressiveness of space reclamation and locking behavior.

Comments

Post a Comment

Popular posts from this blog

PostgreSQL - Architecture

-
Image
When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The other processes that the postgres process starts are responsible for the ongoing work of the database.  PostgreSQL`s architecture is based on a client/server communication. In the server side there is a server process that manages the files, database operations, connection and so on.. On the client side there is a tool/program which can be a graphical tool (gui)  like pgAdmin or a command line tool(cli) like psql  that is repnsible for connection the database and all...
Read more

POSTGRESQL DATABASE MAINTENANCE

-
  Routine backup of daily database maintenance work , regular “clean up” database, periodic log file management check_postgres can be used to detect database health and report abnormal conditions 1. Daily cleaning PostgreSQL database requires periodic cleaning and maintenance. For many installations, it is sufficient to have the automatic cleanup daemon perform cleanup 1.1. Basic knowledge of cleaning The PostgreSQL VACUUM command must periodically process each table for several reasons: 1.  Restore or reuse the disk space occupied by updated or deleted rows. 2.  Update the data statistics used by the PostgreSQL query planner. 3.  Update the visibility map, it can speed up the scan using only the index. 4.  Protect old data from being lost due to transaction ID rewinding or multi-transaction ID rewinding. There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space but runs slower. VACUUM FULL requires an exclusive l...
Read more

PostgreSQL - Backup & Point In Time Recovery

-
Image
  In this post you will learn about how to restore a PostgreSQL database in Point in time. Open source databases are taking over other conventional Database softwares at very high speed. For me personally, I really like the way PostgreSQL is designed and it’s simple architecture. I have tried to write it down every possible steps which will help you to restore your Postgres database. Below Steps will be same, but few commands may vary with any other backup tool. PITR PostgreSQL Allow you to restore database to a specific moment in time PITR. It make use of live database files and WAL files. This method can only backup and restore the whole cluster, for individual database use pg_dump. It can be done when the DB is online. Step 1. If archive logs not enable then enable it, otherwise go to Step3. Create Directory for archive logs mkdir /postgres/postgres12.2/wal_archives Make changes in postgresql.conf Postgresql.conf is present under your data directory, in my case it is in /postgre...
Read more