Database Optimization for n8n at Scale

Who this is for: Ops engineers and platform architects running n8n in production who need to keep the execution database fast, lean, and cost‑effective. We cover this in detail in the n8n Performance & Scaling Guide.

---

Quick Diagnosis

1. Index execution_entity on status + finishedAt and on workflowId.
2. Prune executions older than 30 days each night.
3. Archive finished runs to external storage (e.g., S3) via the n8n Export API or a small Node.js batch script.
4. Monitor table size, index bloat, and query latency with built‑in PostgreSQL/SQLite views.

---

Why Your n8n DB Is Slowing Down ?

Symptom	Likely DB Cause
UI hangs on “Executions” page	execution_entity > 10 M rows, missing finishedAt index
High CPU on DB server	No composite index on workflowId + status
Disk fills up quickly	No retention policy – logs kept forever

If any of these appear, apply the relevant section below. Adding the right indexes and pruning old rows resolves the majority of performance complaints. If you encounter any database migration strategies resolve them before continuing with the setup.

---

1. Execution Data Model Overview

Column	Type	Purpose
id	UUID	Primary key
workflowId	UUID	FK to workflow_entity
status	VARCHAR(20)	success, error, running
startedAt	TIMESTAMP	Run start
finishedAt	TIMESTAMP	Run end (NULL while running)
data	JSONB	Full node I/O (often huge)

EEFA note – The data column drives storage bloat. Keep raw execution data only as long as needed for debugging.

---

2. Indexing Strategies for Immediate Speed Gains

If you encounter any multi instance sync resolve them before continuing with the setup.

2.1 Essential PostgreSQL Indexes

Create the indexes that the UI queries most often:

-- Composite index for UI filters
CREATE INDEX IF NOT EXISTS idx_execution_status_finished
ON execution_entity (status, finishedAt DESC);

-- Index to speed up workflow‑execution joins
CREATE INDEX IF NOT EXISTS idx_execution_workflow
ON execution_entity (workflowId);

The primary key already exists, so no extra work is required.

2.2 SQLite & MySQL Equivalents

DB	Index DDL
SQLite	CREATE INDEX idx_execution_status_finished ON execution_entity (status, finishedAt DESC);
MySQL (InnoDB)	ALTER TABLE execution_entity ADD INDEX idx_execution_status_finished (status, finishedAt DESC);

EEFA – SQLite does not support descending indexes; the ascending index still speeds up range scans.

2.3 Index Maintenance Checklist

Action	Frequency	Command
Re‑index after massive deletes	Weekly (or > 20 % rows removed)	REINDEX TABLE execution_entity;
Vacuum to reclaim space (PostgreSQL)	Daily	VACUUM (FULL, ANALYZE) execution_entity;
Refresh planner statistics	Daily	ANALYZE execution_entity;

---

3. Pruning Old Execution Records

3.1 Retention Policy Design

Tier	Age	What to keep
Hot	≤ 7 days	Full execution data (debugging)
Warm	8‑30 days	Metadata only (clear data)
Cold	> 30 days	Archive externally, then delete

3.2 Nightly Prune Script (PostgreSQL)

Save the following as /usr/local/bin/n8n-prune.sh and schedule it with cron (0 2 * * * /usr/local/bin/n8n-prune.sh).

#!/usr/bin/env bash
set -euo pipefail

# DB connection (adjust env vars as needed)
export PGHOST="${PGHOST:-localhost}"
export PGUSER="${PGUSER:-n8n}"
export PGDATABASE="${PGDATABASE:-n8n}"
export PGPASSWORD="${PGPASSWORD:-your_password}"

# 1️⃣ Strip raw JSON for the warm tier
psql -c "
  UPDATE execution_entity
  SET data = NULL
  WHERE finishedAt < NOW() - INTERVAL '7 days' AND finishedAt >= NOW() - INTERVAL '30 days';
"

# 2️⃣ Delete cold‑tier rows (after successful archive)
psql -c "
  DELETE FROM execution_entity
  WHERE finishedAt < NOW() - INTERVAL '30 days';
"

EEFA – Run the UPDATE … SET data = NULL **before** the hard delete to minimise I/O and prevent PostgreSQL bloat.

3.3 SQLite Prune Command

DELETE FROM execution_entity
WHERE finishedAt < datetime('now','-30 days');

SQLite lacks partial JSON updates; for a warm tier you must copy the table to a new one without the data column. If you encounter any cache layer implementation resolve them before continuing with the setup.

---

4. Archiving Executions to an External Store

4.1 Using n8n’s Built‑in Export API

The Export API returns a JSON payload for a single execution. Pipe it straight to S3:

curl -s -H "Authorization: Bearer $API_KEY" \
  "https://n8n.example.com/rest/executions/$EXEC_ID/export" |
aws s3 cp - "s3://n8n-archive/$EXEC_ID.json"

4.2 Batch Archiver in Node.js

The script below streams executions older than 30 days to S3, then deletes them in the same transaction.

// Setup
const { Client } = require('pg');
const AWS = require('aws-sdk');
const s3 = new AWS.S3();
const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();

// Process batches
const BATCH_SIZE = 500;
let offset = 0;

while (true) {
  const { rows } = await client.query(
    `SELECT id, data FROM execution_entity
     WHERE finishedAt < NOW() - INTERVAL '30 days'
     ORDER BY finishedAt ASC
     LIMIT $1 OFFSET $2`,
    [BATCH_SIZE, offset]
  );
  if (!rows.length) break;

  // Upload each execution to S3
  for (const { id, data } of rows) {
    await s3.putObject({
      Bucket: 'n8n-archive',
      Key: `${id}.json`,
      Body: JSON.stringify(data),
      ServerSideEncryption: 'AES256',
    }).promise();
  }

  // Delete only after successful uploads
  const ids = rows.map(r => `'${r.id}'`).join(',');
  await client.query(`DELETE FROM execution_entity WHERE id IN (${ids});`);
  offset += BATCH_SIZE;
}
await client.end();

Production safeguards

• Wrap each batch in a transaction; roll back on any S3 error.
• Enable S3 versioning to protect against accidental loss.
• Emit a log line per batch to your observability platform (Datadog, Loki, etc.).

4.3 Archiving to a Separate PostgreSQL Instance

-- In the archive DB, create a mirror table
CREATE TABLE execution_archive (LIKE execution_entity INCLUDING ALL);

-- Copy and purge in one go
INSERT INTO archive.execution_archive
SELECT * FROM main.execution_entity
WHERE finishedAt < NOW() - INTERVAL '30 days';

DELETE FROM main.execution_entity
WHERE finishedAt < NOW() - INTERVAL '30 days';

EEFA – Use logical replication for near‑real‑time sync if you need the archive to stay up‑to‑date.

---

5. Monitoring DB Health & Performance

5.1 Core PostgreSQL Metrics

Metric	Query	Alert Threshold
Table size	SELECT pg_total_relation_size(‘execution_entity’);	> 5 GB
Index bloat	SELECT * FROM pgstattuple(‘execution_entity’);	> 30 % dead tuples
Long‑running queries	SELECT * FROM pg_stat_activity WHERE state=’active’ AND now() – query_start > interval ‘5 seconds’;	Any > 5 s
Autovacuum lag	SELECT relname, last_autovacuum FROM pg_stat_user_tables WHERE relname=’execution_entity’;	> 24 h

5.2 Grafana Dashboard (Prometheus Exporter)

scrape_configs:
  - job_name: 'n8n_postgres'
    static_configs:
      - targets: ['localhost:9187']   # postgres_exporter

Add panels for:

• pg_table_size{table="execution_entity"}
• pg_stat_user_indexes{relname="execution_entity"}
• pg_stat_activity{state="active"}

5.3 Alerting Integration

• PagerDuty – Trigger when table size > 5 GB **and** index bloat > 30 %.
• Slack – Send a concise summary to #n8n-ops for any query exceeding 5 seconds.

EEFA – Avoid enabling auto_explain in production; it adds overhead. Use log_min_duration_statement = 2000 (2 s) for sampled logging instead.

---

6. Common Pitfalls & Production‑Grade Fixes

Pitfall	Symptom	Fix
Deleting rows without VACUUM (PostgreSQL)	Disk usage never shrinks	Run VACUUM (FULL) after large deletions or tune autovacuum_vacuum_scale_factor = 0.05.
Archiving only metadata, losing raw data needed for audit	Compliance breach	Store raw JSON in an immutable S3 bucket; keep a 90‑day retention window.
Using SQLite with > 10 M executions	UI timeouts, massive slowdown	Migrate to PostgreSQL with pgloader for zero‑downtime transfer.
Indexes on data (JSONB) without GIN	Slow JSON field queries	Add GIN index: CREATE INDEX ON execution_entity USING GIN (data);
Running prune script as root with wrong credentials	No rows removed, silent failure	Verify PGUSER/PGPASSWORD; log exit codes and stderr.

---

Conclusion

Applying the three‑step workflow index, prune, archive keeps the execution_entity table lean, speeds up UI queries, and prevents runaway disk consumption. Regular index maintenance and vigilant monitoring close the loop, ensuring the database remains responsive under production load. Implement these practices today to guarantee that your n8n instance scales smoothly and remains cost‑effective.