Monitor PostgreSQL Table Bloat and Trigger Vacuum

Hey team, Darian here. Let’s talk about a classic time-sink: manually checking for PostgreSQL table bloat. I used to spend a good chunk of my Monday mornings digging through database logs and running queries by hand to spot performance drags. It was tedious, and honestly, a poor use of my time. So, I automated it.

I put together a simple Python script that does the heavy lifting. It finds bloated tables and triggers a `VACUUM` for me. This little piece of automation has probably saved me a hundred hours over the last couple of years and kept our applications running smoothly. Let’s build it together; it’s easier than you think.

Prerequisites

Before we dive in, make sure you have the following ready:

• Python 3: You should have a modern version of Python installed.
• PostgreSQL Database Access: You’ll need credentials for a user who can connect to the database and has permission to run `VACUUM` and query system catalogs.
• Required Python Libraries: We’ll need a couple of packages. In your project’s virtual environment, you’ll want to install them. The command you’d typically run is `pip install psycopg2-binary python-dotenv`.

The Guide: From Detection to Action

Step 1: Setting Up Your Project

First, find a good spot for your project. I’ll skip the standard virtualenv setup since you likely have your own workflow for that. Let’s jump straight to the files. Inside your project directory, create two files:

• monitor_bloat.py: This will be our main script.
• config.env: This is where we’ll safely store our database credentials, keeping them out of the code.

Step 2: Configuring Your Credentials

Let’s edit the config.env file. This is crucial for security—we never hardcode secrets directly in our scripts. Your file should look like this:

DB_NAME=your_database_name
DB_USER=your_username
DB_PASSWORD=your_secret_password
DB_HOST=your_database_host
DB_PORT=5432

Just replace the placeholder values with your actual database connection details.

Step 3: The Bloat Detection Query

This is the heart of our script. The following SQL query is a well-regarded method for estimating table bloat. It compares the actual physical size of the table on disk to the estimated size based on column types and row count. The difference is our bloat.

We’ll embed this query directly into our Python script.

-- This query is adapted from the official PostgreSQL wiki.
-- It calculates the estimated bloat for tables in the current database.
SELECT
  current_database(),
  schemaname,
  tablename,
  ROUND((
    (
      (
        (
          pg_total_relation_size(schemaname || '.' || tablename) -
          (
            (
              (SELECT reltuples FROM pg_class WHERE oid = (schemaname || '.' || tablename)::regclass) *
              (
                SELECT
                  SUM(
                    CASE WHEN attname = 'ctid' THEN 0 ELSE st.stawidth END
                  )
                FROM pg_attribute AS a
                JOIN pg_statistic AS st ON a.attrelid = st.starelid AND a.attnum = st.staattnum
                WHERE a.attrelid = (schemaname || '.' || tablename)::regclass AND a.attnum > 0
              )
            )
          )
        ) / pg_total_relation_size(schemaname || '.' || tablename)
      ) * 100
    )
  )::numeric, 2) AS bloat_percentage,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size
FROM pg_stat_user_tables
WHERE pg_total_relation_size(schemaname || '.' || tablename) > 10485760 -- Only check tables larger than 10MB
ORDER BY bloat_percentage DESC;

Step 4: The Python Automation Script

Now, let’s write the Python logic in monitor_bloat.py. This script will connect to the database, run our query, and then trigger a vacuum on any tables that cross our defined bloat threshold.

import os
import psycopg2
from dotenv import load_dotenv

# --- Configuration ---
# Load credentials from our config.env file
load_dotenv('config.env')

DB_NAME = os.getenv('DB_NAME')
DB_USER = os.getenv('DB_USER')
DB_PASSWORD = os.getenv('DB_PASSWORD')
DB_HOST = os.getenv('DB_HOST')
DB_PORT = os.getenv('DB_PORT')

# Set your bloat threshold. If a table's bloat is above this, we'll vacuum it.
BLOAT_THRESHOLD_PERCENT = 25.0

# --- The Query to Find Bloated Tables ---
BLOAT_QUERY = """
SELECT
  schemaname,
  tablename,
  bloat_percentage
FROM (
  SELECT
    schemaname,
    tablename,
    ROUND((
      (
        (
          (
            pg_total_relation_size(schemaname || '.' || tablename) -
            (
              (
                (SELECT reltuples FROM pg_class WHERE oid = (schemaname || '.' || tablename)::regclass) *
                (
                  SELECT
                    SUM(
                      CASE WHEN attname = 'ctid' THEN 0 ELSE st.stawidth END
                    )
                  FROM pg_attribute AS a
                  JOIN pg_statistic AS st ON a.attrelid = st.starelid AND a.attnum = st.staattnum
                  WHERE a.attrelid = (schemaname || '.' || tablename)::regclass AND a.attnum > 0
                )
              )
            )
          ) / pg_total_relation_size(schemaname || '.' || tablename)
        ) * 100
      )
    )::numeric, 2) AS bloat_percentage
  FROM pg_stat_user_tables
  WHERE pg_total_relation_size(schemaname || '.' || tablename) > 10485760
) AS bloated_tables
WHERE bloat_percentage >= %s
ORDER BY bloat_percentage DESC;
"""

def check_and_vacuum():
    """Connects to Postgres, finds bloated tables, and vacuums them."""
    conn = None
    try:
        # Establish connection
        conn = psycopg2.connect(
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            host=DB_HOST,
            port=DB_PORT
        )
        print("Successfully connected to the database.")
        conn.autocommit = True  # VACUUM cannot run inside a transaction block
        cur = conn.cursor()

        # Find bloated tables
        print(f"Searching for tables with bloat over {BLOAT_THRESHOLD_PERCENT}%...")
        cur.execute(BLOAT_QUERY, (BLOAT_THRESHOLD_PERCENT,))
        bloated_tables = cur.fetchall()

        if not bloated_tables:
            print("No bloated tables found. All good!")
            return

        print(f"Found {len(bloated_tables)} bloated table(s).")

        # Vacuum each bloated table
        for schema, table, bloat in bloated_tables:
            print(f"  - Table '{schema}.{table}' has {bloat}% bloat. Triggering VACUUM...")
            vacuum_query = f'VACUUM (ANALYZE) "{schema}"."{table}";'
            try:
                cur.execute(vacuum_query)
                print(f"    Successfully vacuumed '{schema}.{table}'.")
            except psycopg2.Error as e:
                print(f"    Error vacuuming table {schema}.{table}: {e}")

        cur.close()

    except psycopg2.OperationalError as e:
        print(f"Connection Error: Could not connect to the database. Details: {e}")
    except Exception as e:
        print(f"An unexpected error occurred: {e}")
    finally:
        if conn is not in None:
            conn.close()
            print("Database connection closed.")

if __name__ == '__main__':
    check_and_vacuum()

Pro Tip: Notice I’m using VACUUM (ANALYZE) and not VACUUM FULL. This is intentional. VACUUM reclaims space for re-use within the table without locking it for extended periods, making it safe to run on a live production system. VACUUM FULL rewrites the entire table and takes an exclusive lock, which can cause significant downtime. I only use `FULL` during a planned maintenance window, and even then, only when absolutely necessary.

Step 5: Scheduling the Script

The final step is to run this script automatically. On a Linux system, a cron job is the perfect tool for this. You don’t want to run this too frequently, as it can add load to the database. Running it once a week during off-peak hours is a good starting point.

A typical cron entry would look like this:

0 2 * * 1 python3 monitor_bloat.py

This example runs the script every Monday at 2:00 AM. Adjust the schedule to whatever makes sense for your application’s traffic patterns.

Common Pitfalls (Where I Usually Mess Up)

• Database Permissions: The number one issue I run into is the user in config.env not having the right permissions. Make sure the user can query the pg_catalog and pg_stat_user_tables views and has permission to run VACUUM on the target tables.
• Connection Strings & Firewalls: A classic “it works on my machine” problem. Double-check that your host, port, and credentials are correct and that any firewalls between your script’s host and the database server allow traffic on the specified port.
• Threshold Too Low: Don’t set your BLOAT_THRESHOLD_PERCENT to something tiny like 5%. A small amount of bloat is normal and expected. Setting the threshold too low will cause the script to run vacuums constantly, adding unnecessary I/O load to your database. I find 20-30% is a reasonable starting point.

Conclusion

And that’s it! You now have a robust, automated system for keeping your PostgreSQL tables lean and performant. This isn’t a silver bullet for all database problems, but it handles one of the most common maintenance tasks, freeing you up to focus on more complex architectural challenges. It’s a simple investment that pays huge dividends in stability and saved time. Let me know if you have any questions.

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions