Hello I’m working on a workflow that imports a large number of records into PostgreSQL, and I’m trying to find the most efficient way to handle bulk inserts without impacting database performance.
A simplified example looks like this:
INSERT INTO workflow_jobs (tenant_id, job_id, status)
VALUES
(‘tenant_001’, 1001, ‘pending’),
(‘tenant_001’, 1002, ‘pending’),
(‘tenant_001’, 1003, ‘pending’);
As the number of records grows into the tens or hundreds of thousands, I’m wondering what approaches work best in production.
Do you use multi-row INSERT statements, COPY, or another method?
At what point do you switch from INSERT to COPY?
How do you balance insert speed with indexes and constraints?
Any PostgreSQL tuning tips that have significantly improved bulk import performance?
I’d love to hear what has worked well for others running PostgreSQL in production, especially alongside 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.)
Hi @Decoure_Ryan For small to medium datasets, multi-row INSERT statements are usually sufficient. However, for very large imports, COPY is generally the fastest and most efficient option.
Recommended approach
Use multi-row INSERT for smaller batches.
Use COPY when importing hundreds of thousands or millions of rows.
Example: COPY workflow_jobs (tenant_id, job_id, status)
FROM ‘/path/to/data.csv’
WITH (FORMAT csv, HEADER true);
Best practices
Import data in batches instead of one row at a time.
Keep indexes to a minimum during large imports if possible, then rebuild them afterward.
Run ANALYZE after the import so PostgreSQL updates its query planner statistics.
Monitor disk I/O, CPU, and memory during large imports.
In production, COPY is typically the preferred method for bulk loading data because it’s significantly faster than executing thousands of individual INSERT statements
Hi @Niffzy thanks for the reply
Do you load data directly into the production table, or do you import it into a staging table first and validate it before moving it into the final table?
Hi @Decoure_Ryan
Stage first. Create it as CREATE UNLOGGED TABLE workflow_jobs_stage (LIKE workflow_jobs), load into that, validate, then promote in one statement. UNLOGGED skips WAL writes entirely, which is where most of the load cost sits, and the only downside is that the table gets truncated after a crash, which does not matter for data you can re-pull.
For the load, Execute Query runs once per incoming item, so 100k items means 100k round trips no matter how the statement is written. Collapse the batch into one item with an Aggregate node (All Item Data, Put Output in Field set to data), then pass the whole thing as a single parameter:
INSERT INTO workflow_jobs_stage (tenant_id, job_id, status)
SELECT tenant_id, job_id, status
FROM jsonb_to_recordset($1::jsonb) AS t(tenant_id text, job_id int, status text);
With Query Parameters in the node Options set to {{ [ JSON.stringify($json.data) ] }}. n8n sanitises that value, so you never build SQL strings by hand, and one parameter holds up to 1 GB.
Then promote and clear:
INSERT INTO workflow_jobs SELECT * FROM workflow_jobs_stage ON CONFLICT DO NOTHING;
TRUNCATE workflow_jobs_stage;
In production, I usually prefer loading data into a staging table first, especially for large imports. This lets me validate the data, remove duplicates, and check for errors before moving it into the main table.
Once the data is verified, I insert or merge it into the production table inside a transaction. That way, only clean and validated data reaches the live system.
This approach also makes it easier to retry failed imports and reduces the risk of corrupting production data if something goes wrong during the import process.