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.
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.
The most straightforward fix is to enable autocommit mode before executing VACUUM.
For psycopg2 (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):
import psycopg
conn = psycopg.connect("dbname=mydb user=postgres", autocommit=True)
conn.execute("VACUUM FULL ANALYZE")
conn.close()If you need more control over isolation levels:
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.
If you cannot enable autocommit, commit any pending transactions first:
COMMIT;
VACUUM FULL ANALYZE;Or in application code:
# 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.
With SQLAlchemy, bypass the ORM transaction handling:
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.
In production, avoid running VACUUM from your application. Instead:
1. Use autovacuum (enabled by default):
ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor = 0.05);2. Schedule with cron or a separate job:
0 2 * * * /usr/bin/psql -U postgres -d mydb -c "VACUUM FULL ANALYZE;" 2>&1 | logger3. Use a maintenance window with a dedicated connection pool that has autocommit enabled.
VACUUM is intentionally non-transactional because it requires exclusive locks and must persist changes to the physical database files immediately. PostgreSQL's autovacuum daemon handles routine maintenance automatically, so VACUUM FULL is rarely needed in production. If autovacuum is running too frequently, tune its parameters (autovacuum_naptime, autovacuum_vacuum_scale_factor, autovacuum_vacuum_cost_delay) rather than running manual VACUUM. For tables with GIN indexes, VACUUM also completes pending index insertions. When using connection pooling (PgBouncer, pgpool), ensure your VACUUM connections use session mode, not transaction mode.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL