How to fix VACUUM cannot run inside a transaction block in PostgreSQL

PostgreSQLBEGINNERMEDIUM

VACUUM is a PostgreSQL maintenance command that cannot run inside a transaction block. This error occurs when your connection has an active transaction. Enable autocommit mode or commit before running VACUUM.

What this error means

PostgreSQL requires VACUUM to run in autocommit mode because it performs maintenance operations that cannot be rolled back. The VACUUM command removes dead rows and optimizes table storage. When your database connection has an active transaction (the default in most ORMs and drivers), PostgreSQL blocks VACUUM to prevent inconsistent states. This is by design and not a bug—VACUUM is a non-transactional operation.

Typical symptoms

How to fix "VACUUM cannot run inside a transaction block"

1Enable autocommit for your database connection

The most straightforward fix is to enable autocommit mode before executing VACUUM.

For psycopg2 (Python):

python

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
conn.autocommit = True
cur = conn.cursor()
cur.execute("VACUUM FULL ANALYZE")
conn.close()

For psycopg3 (newer Python driver):

python

import psycopg

conn = psycopg.connect("dbname=mydb user=postgres", autocommit=True)
conn.execute("VACUUM FULL ANALYZE")
conn.close()

2Use psycopg2.extensions for finer control (Python)

If you need more control over isolation levels:

python

import psycopg2
import psycopg2.extensions

conn = psycopg2.connect("dbname=mydb user=postgres")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("VACUUM FULL ANALYZE")
conn.close()

This explicitly sets the connection to autocommit mode.

3Commit active transactions before VACUUM

If you cannot enable autocommit, commit any pending transactions first:

sql

COMMIT;
VACUUM FULL ANALYZE;

Or in application code:

python

# Using SQLAlchemy
from sqlalchemy import text

with engine.connect() as conn:
    conn.commit()  # Commit any pending transaction
    conn.execute(text("VACUUM FULL ANALYZE"))
    conn.commit()

Note: SQLAlchemy may still wrap this in a transaction. See the next step for a better approach.

4Use raw connection for VACUUM in SQLAlchemy

With SQLAlchemy, bypass the ORM transaction handling:

python

from sqlalchemy import create_engine, text

engine = create_engine("postgresql://user:password@localhost/dbname")

# Get a raw DBAPI connection with autocommit
raw_conn = engine.raw_connection()
raw_conn.autocommit = True
cur = raw_conn.cursor()
cur.execute("VACUUM FULL ANALYZE")
raw_conn.close()

This bypasses SQLAlchemy's transaction handling entirely.

5Schedule VACUUM separately in production

In production, avoid running VACUUM from your application. Instead:

1. Use autovacuum (enabled by default):

sql

ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor = 0.05);

2. Schedule with cron or a separate job:

bash

0 2 * * * /usr/bin/psql -U postgres -d mydb -c "VACUUM FULL ANALYZE;" 2>&1 | logger

3. Use a maintenance window with a dedicated connection pool that has autocommit enabled.

How to fix VACUUM cannot run inside a transaction block in PostgreSQL

What this error means

Typical symptoms

Common causes

How to fix "VACUUM cannot run inside a transaction block"

Advanced notes

Related errors

Official resources & further reading