1SQLITE          POSTGRESQL
2────────────────────────────────────────────
3Performance     Good for dev    Production-ready
4Concurrency     Limited         Excellent
5Scalability     Single file     Horizontal OK
6Backup          Copy file       Advanced tools
7Replication     None            Built-in
8Max Size        ~140TB          Unlimited
9Best For        Development     Production
10 
11VERDICT: Always use PostgreSQL for production!

1# docker-compose.yml
2services:
3  postgres:
4    image: postgres:15-alpine
5    container_name: n8n-postgres
6    restart: unless-stopped
7    environment:
8      POSTGRES_USER: n8n
9      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
10      POSTGRES_DB: n8n
11    volumes:
12      - postgres_data:/var/lib/postgresql/data
13      - ./init.sql:/docker-entrypoint-initdb.d/init.sql:ro
14    ports:
15      - "5432:5432"  # Remove in production
16    healthcheck:
17      test: ["CMD-SHELL", "pg_isready -U n8n"]
18      interval: 10s
19      timeout: 5s
20      retries: 5
21 
22volumes:
23  postgres_data:

1-- init.sql
2-- Runs on first startup only
3
4-- Create extension for UUID support
5CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
6
7-- Create additional indexes for performance
8-- (n8n creates its own, but these help)
9
10-- Optional: Create read-only user for reporting
11CREATE USER n8n_readonly WITH PASSWORD 'readonly_password';
12GRANT CONNECT ON DATABASE n8n TO n8n_readonly;
13GRANT USAGE ON SCHEMA public TO n8n_readonly;
14GRANT SELECT ON ALL TABLES IN SCHEMA public TO n8n_readonly;
15ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO n8n_readonly;

1# Ubuntu/Debian
2sudo apt update
3sudo apt install postgresql postgresql-contrib
4 
5# Start service
6sudo systemctl start postgresql
7sudo systemctl enable postgresql
8 
9# Create user and database
10sudo -u postgres psql << EOF
11CREATE USER n8n WITH PASSWORD 'secure_password';
12CREATE DATABASE n8n OWNER n8n;
13GRANT ALL PRIVILEGES ON DATABASE n8n TO n8n;
14EOF

1# /var/lib/postgresql/data/postgresql.conf
2 
3# Memory Settings (Adjust based on RAM)
4# For 4GB RAM server:
5shared_buffers = 1GB            # 25% of RAM
6effective_cache_size = 3GB      # 75% of RAM
7maintenance_work_mem = 256MB
8work_mem = 64MB
9 
10# Connections
11max_connections = 100
12 
13# Write Ahead Log
14wal_buffers = 64MB
15min_wal_size = 1GB
16max_wal_size = 4GB
17checkpoint_completion_target = 0.9
18 
19# Query Planner
20random_page_cost = 1.1          # For SSD
21effective_io_concurrency = 200  # For SSD
22 
23# Logging
24log_min_duration_statement = 1000  # Log slow queries (ms)
25log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '

1services:
2  postgres:
3    image: postgres:15-alpine
4    command:
5      - "postgres"
6      - "-c"
7      - "shared_buffers=256MB"
8      - "-c"
9      - "effective_cache_size=768MB"
10      - "-c"
11      - "maintenance_work_mem=128MB"
12      - "-c"
13      - "checkpoint_completion_target=0.9"
14      - "-c"
15      - "wal_buffers=16MB"
16      - "-c"
17      - "default_statistics_target=100"
18      - "-c"
19      - "random_page_cost=1.1"
20      - "-c"
21      - "effective_io_concurrency=200"
22      - "-c"
23      - "min_wal_size=1GB"
24      - "-c"
25      - "max_wal_size=4GB"
26    # ... rest of config

1-- Core tables created by n8n:
2
3workflow_entity        -- Workflow definitions
4├── id
5├── name
6├── active
7├── nodes (JSON)
8├── connections (JSON)
9└── settings
10
11credentials_entity     -- Encrypted credentials
12├── id
13├── name
14├── type
15├── data (encrypted)
16└── nodesAccess
17
18execution_entity       -- Execution history
19├── id
20├── workflowId
21├── finished
22├── mode
23├── startedAt
24├── stoppedAt
25├── workflowData (JSON)
26└── data (JSON, can be large!)
27
28webhook_entity         -- Registered webhooks
29├── webhookPath
30├── method
31├── node
32└── workflowId

1-- Check total database size
2SELECT pg_size_pretty(pg_database_size('n8n'));
3
4-- Check table sizes
5SELECT 
6    relname AS table_name,
7    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
8    pg_size_pretty(pg_relation_size(relid)) AS data_size,
9    pg_size_pretty(pg_indexes_size(relid)) AS index_size
10FROM pg_catalog.pg_statio_user_tables
11ORDER BY pg_total_relation_size(relid) DESC;
12
13-- execution_entity is usually largest!

1#!/bin/bash
2# backup.sh
3 
4DATE=$(date +%Y%m%d_%H%M%S)
5BACKUP_DIR=/backups
6DB_HOST=postgres
7DB_USER=n8n
8DB_NAME=n8n
9 
10# Create backup
11PGPASSWORD=$POSTGRES_PASSWORD pg_dump \
12  -h $DB_HOST \
13  -U $DB_USER \
14  -d $DB_NAME \
15  -F c \
16  -f "$BACKUP_DIR/n8n_$DATE.dump"
17 
18# Keep only last 7 days
19find $BACKUP_DIR -name "n8n_*.dump" -mtime +7 -delete
20 
21echo "Backup complete: n8n_$DATE.dump"

1#!/bin/bash
2# For full cluster backup (larger, faster restore)
3 
4DATE=$(date +%Y%m%d_%H%M%S)
5BACKUP_DIR=/backups/base
6 
7pg_basebackup \
8  -h postgres \
9  -U replication_user \
10  -D "$BACKUP_DIR/n8n_$DATE" \
11  -Ft \
12  -z \
13  -P

1#!/bin/bash
2# Backup entire volume
3 
4# Stop n8n (optional, for consistency)
5docker compose stop n8n
6 
7# Backup volume
8docker run --rm \
9  -v n8n_postgres_data:/source:ro \
10  -v $(pwd)/backups:/backup \
11  alpine tar czf /backup/postgres_$(date +%Y%m%d).tar.gz -C /source .
12 
13# Restart
14docker compose start n8n

1# crontab -e
2 
3# Daily backup at 2 AM
40 2 * * * /opt/n8n/backup.sh >> /var/log/n8n-backup.log 2>&1
5 
6# Weekly full backup at 3 AM Sunday
70 3 * * 0 /opt/n8n/full-backup.sh >> /var/log/n8n-backup.log 2>&1

1services:
2  backup:
3    image: postgres:15-alpine
4    volumes:
5      - ./backups:/backups
6      - ./backup.sh:/backup.sh:ro
7    environment:
8      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
9    entrypoint: /bin/sh
10    command: >
11      -c "while true; do
12        sleep 86400;
13        /backup.sh;
14      done"
15    depends_on:
16      - postgres

1# Restore to existing database
2PGPASSWORD=$POSTGRES_PASSWORD pg_restore \
3  -h postgres \
4  -U n8n \
5  -d n8n \
6  -c \
7  --if-exists \
8  /backups/n8n_20240115.dump
9 
10# Or to new database
11createdb -h postgres -U n8n n8n_restored
12pg_restore -h postgres -U n8n -d n8n_restored /backups/n8n_20240115.dump

1# Stop services
2docker compose down
3 
4# Remove old volume
5docker volume rm n8n_postgres_data
6 
7# Create new volume
8docker volume create n8n_postgres_data
9 
10# Restore
11docker run --rm \
12  -v n8n_postgres_data:/target \
13  -v $(pwd)/backups:/backup:ro \
14  alpine sh -c "cd /target && tar xzf /backup/postgres_20240115.tar.gz"
15 
16# Start services
17docker compose up -d

1# n8n environment variables
2EXECUTIONS_DATA_PRUNE=true
3EXECUTIONS_DATA_MAX_AGE=168    # 7 days in hours
4EXECUTIONS_DATA_SAVE_ON_ERROR=all
5EXECUTIONS_DATA_SAVE_ON_SUCCESS=none  # Saves space!
6EXECUTIONS_DATA_SAVE_MANUAL_EXECUTIONS=true

1-- Delete old executions (BE CAREFUL!)
2DELETE FROM execution_entity 
3WHERE "stoppedAt" < NOW() - INTERVAL '30 days';
4
5-- Vacuum to reclaim space
6VACUUM FULL execution_entity;
7
8-- Reindex
9REINDEX TABLE execution_entity;

1-- Check slow queries (enable pg_stat_statements)
2SELECT 
3    query,
4    calls,
5    mean_exec_time,
6    total_exec_time
7FROM pg_stat_statements
8ORDER BY total_exec_time DESC
9LIMIT 10;
10
11-- Check index usage
12SELECT 
13    schemaname,
14    relname,
15    indexrelname,
16    idx_scan,
17    idx_tup_read,
18    idx_tup_fetch
19FROM pg_stat_user_indexes
20WHERE schemaname = 'public'
21ORDER BY idx_scan DESC;
22
23-- Check table bloat
24SELECT 
25    schemaname,
26    tablename,
27    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
28FROM pg_tables
29WHERE schemaname = 'public'
30ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

1-- Custom indexes if needed (check before adding)
2CREATE INDEX IF NOT EXISTS idx_execution_started 
3ON execution_entity ("startedAt" DESC);
4
5CREATE INDEX IF NOT EXISTS idx_execution_workflow 
6ON execution_entity ("workflowId", "startedAt" DESC);
7
8CREATE INDEX IF NOT EXISTS idx_execution_status 
9ON execution_entity ("finished", "startedAt" DESC);

1#!/bin/bash
2# db-health.sh
3 
4PGPASSWORD=$POSTGRES_PASSWORD psql -h postgres -U n8n -d n8n << EOF
5-- Connection count
6SELECT count(*) as active_connections FROM pg_stat_activity;
7 
8-- Database size
9SELECT pg_size_pretty(pg_database_size('n8n')) as db_size;
10 
11-- Execution count last 24h
12SELECT count(*) as executions_24h 
13FROM execution_entity 
14WHERE "startedAt" > NOW() - INTERVAL '24 hours';
15 
16-- Oldest execution
17SELECT MIN("startedAt") as oldest_execution FROM execution_entity;
18EOF

1# Add postgres_exporter
2services:
3  postgres-exporter:
4    image: prometheuscommunity/postgres-exporter
5    environment:
6      DATA_SOURCE_NAME: "postgresql://n8n:${POSTGRES_PASSWORD}@postgres:5432/n8n?sslmode=disable"
7    ports:
8      - "9187:9187"

1# docker-compose.ha.yml
2services:
3  postgres-primary:
4    image: postgres:15-alpine
5    environment:
6      POSTGRES_USER: n8n
7      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
8      POSTGRES_DB: n8n
9    command:
10      - "postgres"
11      - "-c"
12      - "wal_level=replica"
13      - "-c"
14      - "max_wal_senders=3"
15      - "-c"
16      - "max_replication_slots=3"
17    volumes:
18      - postgres_primary:/var/lib/postgresql/data
19 
20  postgres-replica:
21    image: postgres:15-alpine
22    environment:
23      POSTGRES_USER: n8n
24      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
25    depends_on:
26      - postgres-primary
27    # Setup replication (more config needed)