Hi everyone
I’m using PostgreSQL with a self-hosted n8n instance, and as my workflows and tenants grow, I’m starting to think more about long-term database performance.
Right now everything works well, but I know that as execution history, workflow data, and application tables get larger, queries can become slower if they’re not optimized properly.
For example, I often query data by tenant_id and status: SELECT *
FROM workflow_jobs
WHERE tenant_id = ‘tenant_001’
AND status = ‘pending’
ORDER BY created_at ASC
LIMIT 100;
I’m wondering what experienced teams are doing in production to keep PostgreSQL fast as data grows.
Some questions I have:
What indexes have made the biggest difference for you?
Have you used table partitioning for large tables?
At what point did you start archiving old data?
How do you identify slow queries before they become a problem?
Any PostgreSQL tuning tips that noticeably improved performance with n8n?
Describe the problem/error/question
What is the error message (if any)?
Please share your workflow
(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)
Hello @Decoure_Ryan A good starting point is to optimize queries before changing your infrastructure.
Common approach Add indexes to columns you query often (e.g. tenant_id, status, created_at)
Use EXPLAIN ANALYZE to find slow queries
Archive or clean up old execution data regularly
Monitor query performance as your data grows
For example:EXPLAIN ANALYZE
SELECT *
FROM workflow_jobs
WHERE tenant_id = ‘tenant_001’
AND status = ‘pending’;
This helps you see whether PostgreSQL is using your indexes efficiently.
As your database grows you can also Partition very large tables
Create indexes based on common query patterns
Schedule database maintenance
SELECT * FROM workflow_jobs
WHERE tenant_id = 'tenant_001' AND status = 'pending'
ORDER BY created_at ASC LIMIT 100;
A single-column index on tenant_id is not enough. PostgreSQL would have to filter all rows for that tenant and then sort them by date in memory.
The Solution: Composite Index You should create a composite index that matches the filter-and-sort pattern of your query:
CREATE INDEX idx_workflow_jobs_tenant_status_created
ON workflow_jobs (tenant_id, status, created_at ASC);
Why this works: PostgreSQL can jump directly to the specific tenant, then directly to the “pending” status, and because the index is already ordered by created_at, it can simply read the first 100 rows and stop. This reduces the operation from a “Scan and Sort” to a “Direct Seek.”
2)As your workflow_jobs or n8n’s execution_entity tables grow into the millions of rows, indexes become massive and slower to maintain.
When to do it: When a table exceeds 10GB–50GB or when you frequently delete old data. Recommended Approach: Declarative Partitioning by Range (Time) Partition your tables by month or week (e.g., workflow_jobs_2026_07).
Performance Gain: Queries for “recent” jobs only scan the current month’s partition (Partition Pruning), ignoring years of old data.
Maintenance Gain: Instead of running a slow DELETE FROM ... WHERE created_at < '2025-01-01', which creates massive “bloat” and triggers heavy VACUUM processes, you can simply DROP TABLE workflow_jobs_2025_01. This is instantaneous and recovers disk space immediately.
3)Don’t let your operational database become a data warehouse.
n8n Internal Data: Ensure you have configured the environment variables EXECUTIONS_DATA_PRUNE=true and EXECUTIONS_DATA_MAX_AGE (e.g., 168 hours for 7 days).
Custom Data (workflow_jobs): Implement a “Hot/Cold” architecture.
Hot Store (Postgres): Keep only the last 30–90 days of jobs.
Cold Store (S3/BigQuery/Separate DB): Move completed/cancelled jobs older than 90 days to a cheaper storage solution via a scheduled n8n workflow.
For n8n specifically, the execution_entity table is the one that grows fastest and causes the most pain. A few things that made the biggest difference in production:
Indexes that actually matter for n8n’s query patterns:
CREATE INDEX CONCURRENTLY idx_exec_status_started ON execution_entity (status, started_at);
CREATE INDEX CONCURRENTLY idx_exec_workflow_started ON execution_entity (workflow_id, started_at DESC);
The default index on id alone doesn’t help the execution list queries that n8n fires on every page load.
Pruning built-in: Use EXECUTIONS_DATA_PRUNE=true + EXECUTIONS_DATA_MAX_AGE=168 (hours) in your env to let n8n clean up on its own. Without this, the table grows unbounded and that’s where most people hit the wall.
For your multi-tenant workflow_jobs table: a composite index on (tenant_id, status, created_at) covers that specific query pattern directly - avoid three separate indexes, the composite is faster for that WHERE + ORDER BY combo.
Identify slow queries before they hurt: Enable log_min_duration_statement = 1000 in Postgres and pipe it into pgBadger or just grep the logs. n8n doesn’t expose slow query metrics natively, so you need Postgres-level visibility.
Thanks Optimizing queries before scaling the infrastructure is a great point. I’ll definitely look more into EXPLAIN ANALYZE and composite indexing. Appreciate the advice