Inserting 1,000,000+ rows into PostgreSQL table on RDS

Four Stars

Inserting 1,000,000+ rows into PostgreSQL table on RDS

I am trying to insert over 1 million rows into a PostgreSQL table on RDS using the tDBOutputBulkExec but get an error that indicates that I need to be a superuser.  Is there another option within Talend to do this?  I tried the tDBOutput, but with the number of rows that I'm dealing with, it appears that it will run too long.


Accepted Solutions
Seven Stars

Re: Inserting 1,000,000+ rows into PostgreSQL table on RDS

Hi JHohler,

 

With tPostgresOutput in the advanced setting of the component, you can increase the batch size (avoid commit downtime) and enable parallel insertion.

 

Alternatively, if your organisation allows it. You could

  • put your data into S3 bucket with Talend
  • do import from there using Data Pipeline (create a template into AWS)
  • then use Talend to kick off the schedule at the end of the copy to S3.

https://stackoverflow.com/questions/45090748/loading-data-from-s3-to-postgresql-rds

 


All Replies
Seven Stars

Re: Inserting 1,000,000+ rows into PostgreSQL table on RDS

Hi JHohler,

 

With tPostgresOutput in the advanced setting of the component, you can increase the batch size (avoid commit downtime) and enable parallel insertion.

 

Alternatively, if your organisation allows it. You could

  • put your data into S3 bucket with Talend
  • do import from there using Data Pipeline (create a template into AWS)
  • then use Talend to kick off the schedule at the end of the copy to S3.

https://stackoverflow.com/questions/45090748/loading-data-from-s3-to-postgresql-rds

 

Four Stars

Re: Inserting 1,000,000+ rows into PostgreSQL table on RDS

Setting the batch size did the trick.  I didn't see that option because it doesn't appear if you have your action on data set to 'Insert or Update' or 'Update or Insert'.

 

Thank You

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download