Build an ETL pipeline with n8n on a VPS

The pipeline in this guide pulls orders from a paginated REST API once a night, cleans them in a Code node and upserts them into a Postgres table on the same VPS. It runs on n8n 2.x with the current node names, so no Function node, no Cron node and no Spreadsheet File node. The finished workflow is seven nodes long: Schedule Trigger, HTTP Request, Code, Loop Over Items, Execute Sub-workflow, Postgres and a Slack node for the summary. It moves about 50,000 rows a night on a 2 vCPU, 4 GB box without touching queue mode, which is more than most teams need from an ETL job in n8n.

I run this shape for our own billing exports and it has been stable since I moved it to 2.x in January. What changed compared to the 1.x version is the Code node, which now executes on a task runner by default, and the publish step, since a Schedule Trigger only fires once the workflow is published. Save alone does nothing.

Step 1: Schedule Trigger and GENERIC_TIMEZONE

Add a Schedule Trigger, pick Days as the interval and set the hour. The trigger uses the workflow timezone if one is set in the workflow settings and the instance timezone otherwise, and the instance default is America/New_York until you change it. Set both of these in your .env before you rely on any schedule:

GENERIC_TIMEZONE=Europe/Paris
TZ=Europe/Paris

Short digression, because it cost me a duplicate run once: a job scheduled at 02:30 in a DST timezone either runs twice or not at all on the two changeover nights of the year. I schedule nightly ETL at 03:30 or later and the problem goes away. Cron syntax is available under Custom (Cron) if the fixed intervals don't fit.

Step 2: HTTP Request with pagination and batching

Set the method and URL, add your credential, then open Options and add Pagination, which the n8n cookbook page on HTTP Request pagination covers with short working expressions. The two modes are "Update a Parameter in Each Request" for page-number APIs and "Response Contains Next URL" for APIs that hand you a link to the next page.

Response Contains Next URL

Put an expression like {{ $response.body.next }} in the Next URL field, adjusted to wherever your API puts the link. $response holds the body, headers and status code of the previous page and $pageCount starts at zero, so {{ $pageCount + 1 }} is the value you want for an API whose first page is page one. The node also has a setting for when pagination is complete; I stop on an empty response for cursor APIs and on a fixed page limit while testing so a bad expression can't loop forever.

Items per Batch and Batch Interval

Batching is a separate option on the same node for the case where it receives many items and fires one request per item: "Items per Batch" sets how many go out per batch and "Batch Interval" is the pause in milliseconds between batches, 0 meaning none. Page size is a query parameter, not a pagination setting. Turn on Send Query Parameters, add limit or whatever the API calls it and set it as high as the API allows; 200 per page is 250 requests for 50,000 rows.

Step 3: Transform rows in the Code node

The Code node in 2.x runs on a task runner, not inside the main n8n process. You don't configure anything for that on a single instance, the runner is on by default in internal mode. What changes for your code is that $env is blocked unless N8N_BLOCK_ENV_ACCESS_IN_NODE=false is set, and require('crypto') only works with NODE_FUNCTION_ALLOW_BUILTIN=crypto on the runner. Neither is needed for a normal transform. This one dedupes on the order ID, normalises the email and converts the total to integer cents:

const seen = new Set();
const out = [];

for (const item of $input.all()) {
  const o = item.json;
  if (seen.has(o.id)) continue;
  seen.add(o.id);

  const email = String(o.customer_email || '').trim().toLowerCase();
  if (!email) continue;

  out.push({
    json: {
      order_id: o.id,
      email,
      total_cents: Math.round(Number(o.total) * 100),
      currency: (o.currency || 'EUR').toUpperCase(),
      placed_at: new Date(o.created_at).toISOString(),
      source: 'shop-api',
    },
  });
}

return out;

Mode stays on "Run Once for All Items". The Code node reference covers the per-item mode and the Python variant, which needs external task runners with the n8nio/runners sidecar. Returning an array of objects with a json key is the whole contract.

The runner has a task timeout of 300 seconds by default (N8N_RUNNERS_TASK_TIMEOUT) and a payload cap of 1 GiB. A 50,000 row array of small objects finishes in well under a second, and regex-heavy parsing over free text is the case where splitting the work with Loop Over Items beats raising the timeout.

Step 4: Load into Postgres with Insert or Update

The Postgres node has six operations: Delete, Execute Query, Insert, Insert or Update, Select and Update. Insert or Update is the upsert. Pick the schema and table from the list, set Mapping Column Mode to "Map Automatically" so the incoming field names are matched to columns and set the column n8n should match on, which for this pipeline is order_id. Under Options, Query Batching has three values: "Single Query" for one statement covering all items, "Independently" for one statement per item and "Transaction" for all statements in one transaction that rolls back on any failure. I use Transaction for loads. A half-written batch is worse than a failed one because the next run can't tell which rows made it.

The table needs a unique constraint on the match column or the upsert has nothing to conflict on. Underneath, the node runs Postgres' own INSERT ... ON CONFLICT; Execute Query with a hand-written statement works too when you need control over which columns update.

CREATE TABLE orders (
  order_id    bigint PRIMARY KEY,
  email       text NOT NULL,
  total_cents integer NOT NULL,
  currency    char(3) NOT NULL,
  placed_at   timestamptz NOT NULL,
  source      text NOT NULL,
  synced_at   timestamptz NOT NULL DEFAULT now()
);

"Replace Empty Strings with NULL" is in the same Options list and I turn it on for anything coming from a CSV, because an empty string in a numeric column fails the whole transaction. The plain Insert operation has a "Skip on Conflict" option that Insert or Update lacks, for when the first write should win.

Loop Over Items and a sub-workflow for 50,000 rows

n8n nodes process every input item they receive, so on a small run you don't need Loop Over Items at all: Code hands 2,000 rows to Postgres and Postgres writes them. At 50,000 rows the whole array sits in memory as the execution's data, so the run gets broken into pieces that each become their own execution.

Batch size on a 4 GB VPS

Loop Over Items (the node that used to be called Split In Batches) takes a Batch Size and has two outputs, "loop" for each batch and "done" once everything has passed through. I set 500. On a 4 GB server the n8n container sits around 600 MB RSS during the run at that size and the run finishes in roughly six minutes for 48,000 rows. At 5,000 per batch it's faster by a minute and RSS crosses 1.2 GB, which leaves less headroom for whatever else is on the box.

Execute Sub-workflow per batch

Connect the "loop" output to an Execute Sub-workflow node in "Run once with all items" mode, pointing at a second workflow that starts with the trigger shown on the canvas as "When Executed by Another Workflow" and contains only the Postgres node. Leave "Wait for Sub-Workflow Completion" on so failures propagate. The n8n docs on memory suggest this exact split, and the n8n performance tuning guide goes through the other knobs like NODE_OPTIONS heap size if you'd rather not restructure. Each batch is now a separate execution whose memory is released when it finishes, and the parent only holds the batch it's on.

CSV files: Extract from File, Convert to File and the Compression node

For a CSV dropped on SFTP or emailed in, the Spreadsheet File node from old tutorials is gone and the replacement is two nodes. Extract from File has an "Extract From CSV" operation with a Header Row toggle, a Delimiter field, Starting Line, Max Number of Rows and Encoding, reading from the binary field named data by default. Convert to File goes the other way, with "Convert to CSV" and "Convert to XLSX" among its operations.

Zipped drops go through the Compression node's Decompress operation first, which handles .zip, .gz, .tar and .tgz. There's a limit on decompressed size: 2 GiB by default today, controlled by N8N_COMPRESSION_NODE_MAX_DECOMPRESSED_SIZE_BYTES, and the n8n 3.0 breaking changes page says the default drops to 256 MiB in October 2026 with the zip entry limit going from 5,000 to 1,000. If your nightly file is bigger than that, set the variable explicitly now so the 3.0 upgrade doesn't break the run.

Error workflow, retries and the Error Trigger

Create a separate workflow that starts with an Error Trigger and posts to Slack or email. Then in the ETL workflow open Options, then Settings and pick it under Error workflow. The Error Trigger receives execution.id, execution.url, workflow.name, error.message and lastNodeExecuted. It only fires for published workflows that fail on their own; a manual run won't trigger it, so test the alert by publishing and letting the schedule hit a deliberately broken credential.

Per-node retries live in each node's settings tab: a retry-on-fail toggle, a number of tries and a wait between them. The HTTP Request node gets three tries with a few seconds between; the Postgres node stays at one try in Transaction mode so a real constraint error surfaces at once.

Idempotency and a checkpoint row

Upserting on order_id already makes a re-run safe. What it doesn't do is make a re-run cheap, because you fetch everything every night. The fix is a checkpoint: store the newest placed_at you loaded and pass it as a query filter next time.

Data Tables are a decent home for that. They're built into n8n, read and written with the Data Table node (Get, Insert, Update, Upsert, If Row Exists) and capped at 200 MiB per instance by default via N8N_DATA_TABLES_MAX_SIZE_BYTES. One catch from the docs: a Code node can't reach a Data Table, so the read and the update happen in Data Table nodes before and after the run. A plain Postgres table works just as well.

Execution data settings so 50k-row runs don't bloat the database

Every execution stores its full data by default, on success and on failure, so the entire 50,000 row array lands in n8n's database each night, plus every sub-workflow batch as its own execution. Set these on the instance:

EXECUTIONS_DATA_SAVE_ON_SUCCESS=none
EXECUTIONS_DATA_SAVE_ON_ERROR=all
EXECUTIONS_DATA_SAVE_ON_PROGRESS=false
EXECUTIONS_DATA_PRUNE=true
EXECUTIONS_DATA_MAX_AGE=168
EXECUTIONS_DATA_PRUNE_MAX_COUNT=50000

Those are the values the n8n docs use as their own example. Pruning is on by default in 2.x with a 14 day age, so the change is the shorter window and, more importantly, not saving successful runs. Workflows can override the save settings in their own settings panel, which is what I do: instance-wide success data is kept for a day and the ETL workflow saves nothing on success. The soft delete and hard delete timing are in the guide to pruning n8n executions, along with the SQLite detail that pruned space is reused, not returned to the OS. Postgres handles that through autovacuum without you doing anything.

n8n ETL throughput on a 2 vCPU VPS

The numbers from the pipeline above, measured on a 2 vCPU, 4 GB VPS with NVMe, n8n 2.38 and Postgres 17 in the same compose project (n8n's own database is on that Postgres too, for the reasons in the PostgreSQL vs SQLite comparison): extract of 48,000 rows at 200 per page took about 2 minutes 40 seconds and was bound by the API's response time, not by n8n. Transform in the Code node was under a second. Load in batches of 500 through the sub-workflow with Query Batching on Transaction took a little over 3 minutes. Total wall clock a bit over six minutes, and the n8n container's peak RSS was around 600 MB with the runner included.

I haven't run the Postgres node in Independently mode at this size, so how much slower one statement per row gets past 100,000 rows I can't tell you; Transaction has been fast enough that I never needed to find out. Past a few hundred thousand rows a night, or when several pipelines overlap, the n8n queue mode setup with Redis workers is where the sub-workflow batches start running in parallel instead of in sequence, and Postgres moves to its own server.

If you're setting up a fresh box for this, the n8n VPS template deploys with Docker and Caddy on Ubuntu, and the 4 GB tier is the one these numbers came from.. anything smaller runs the same pipeline at a batch size of 200.