Odoo PostgreSQL CPU Spikes on account_move_line Deletions

Troubleshooting Guide: PostgreSQL CPU Spikes on account_move_line Deletions

This note explains why deleting from account_move_line spikes CPU and provides step-by-step instructions to fix it natively in PostgreSQL.

🛑 The Problem

When executing DELETE FROM account_move_line WHERE id IN ( ? ), PostgreSQL triggers internal system checks (RI_FKey_check_relation) for every table referencing account_move_line. If those referencing columns are not indexed, PostgreSQL performs a Sequential Full Table Scan across dozens of tables for every single ID deleted, melting your CPU.

🛠️ Step-by-Step Resolution

Diagnose the Bottleneck

Verify which exact foreign key constraint is causing the slow deletion. Run this simulation inside your PostgreSQL console:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM account_move_line WHERE id IN (1,2,3);
ROLLBACK; -- Crucial: Prevents actual deletion during test

What to look for: Scroll to the bottom under the Triggers section. Look for the RI_FKey_check_relation taking up the highest execution percentage. It will name the offending table.

List Information of Missing Foreign Keys

SELECT 
    cl.relname AS table_name,
    att.attname AS column_name,
    pg_size_pretty(pg_total_relation_size(cl.oid)) AS total_disk_size,
    s.n_live_tup AS estimated_row_count,
    s.n_dead_tup AS dead_rows_bloat,
    (SELECT count(*) FROM pg_index WHERE indrelid = cl.oid) AS current_index_count
FROM pg_constraint c
JOIN pg_attribute att ON att.attnum = ANY(c.conkey) AND att.attrelid = c.conrelid
JOIN pg_class cl ON cl.oid = c.conrelid
JOIN pg_stat_user_tables s ON s.relid = cl.oid
WHERE c.confrelid = 'account_move_line'::regclass
AND NOT EXISTS (
    SELECT 1 
    FROM pg_index i 
    WHERE i.indrelid = c.conrelid 
    AND att.attnum = ANY(i.indkey)
)
ORDER BY s.n_live_tup DESC;

🔍 Key Metrics to Look Out For

Estimated Row Count (estimated_row_count): Tables at the top of this list are your primary bottleneck. If a table has over 1,000,000 rows and lacks an index, every single DELETE statement forces PostgreSQL to scan all 1 million+ rows sequentially.
Total Disk Size (total_disk_size): Tells you how much data PostgreSQL has to read into memory from the disk to scan or build the index. High disk size + high rows = severe disk I/O bottlenecks.
Dead Rows Bloat (dead_rows_bloat): Accumulation of deleted/updated records waiting for a vacuum. If this number is high, your sequential scans are running even slower because Postgres is reading dead data. You should run VACUUM ANALYZE table_name; on these tables before creating indexes.
Current Index Count (current_index_count): PostgreSQL tables slow down on writes if they have too many indexes. If a table already has 8+ indexes, adding another will speed up your deletions but slightly slow down future INSERT or UPDATE queries on that specific table.

Generate All Missing Foreign Key Indexes

Instead of finding them one by one, run this meta-query. It scans the system catalogs to find all foreign keys pointing to account_move_line that lack an index and generates the exact fix scripts:

SELECT 
    'CREATE INDEX CONCURRENTLY IF NOT EXISTS ' || cl.relname || '_' || att.attname || '_idx ON ' || cl.relname || ' (' || att.attname || ');' AS create_index_statement
FROM pg_constraint c
JOIN pg_attribute att ON att.attnum = ANY(c.conkey) AND att.attrelid = c.conrelid
JOIN pg_class cl ON cl.oid = c.conrelid
WHERE c.confrelid = 'account_move_line'::regclass
AND NOT EXISTS (
    SELECT 1 
    FROM pg_index i 
    WHERE i.indrelid = c.conrelid 
    AND att.attnum = ANY(i.indkey)
);

Apply the Generated Script safely

Copy the outputs from Step 2 and execute them. Follow these strict rules to avoid production downtime:

Do not wrap them in a transaction: CREATE INDEX CONCURRENTLY will fail inside a BEGIN ... COMMIT block. Run each statement individually.
Execute during low-traffic hours: The CONCURRENTLY flag keeps Odoo tables unlocked so users can work, but scanning the tables will temporarily use CPU.

Monitor the Index Build Progress

If your database is large, the index creation might take a few minutes. Monitor it in real-time with this query:

SELECT 
    phase, 
    round(100.0 * blocks_done / nullif(blocks_total, 0), 2) AS percent_done,
    blocks_done, 
    blocks_total FROM pg_stat_progress_create_index;

Clean Up Statistics

Once the indexes are built and your bulk deletions are finished, refresh the PostgreSQL planner statistics to ensure future queries stay fast:

VACUUM ANALYZE account_move_line;

💡 Best Practices for Deleting

Batch Your Queries: Do not pass thousands of IDs into a single IN clause. Limit batches to 100–500 IDs per transaction.
Avoid Deletions if Possible: In Odoo's design, the preferred method is to Cancel the parent account_move and reset it to Draft rather than hard-deleting the database rows.

this note asissted by AI

repodevs/postgresql_cpu_spikes_note.md

Select an option

No results found