tPostgresqlOutput update slow

[Anonymous]

Hello,

I noticed that using an update with a tPostgresqlOutput is so much slower than an update with tPostgresqlRow.

Unfortunately, I have a few queries that can't be used in a row (because windows functions can't be used in an update).

Is this known and are there some workarounds ?

My Talend is TOS 6.5.1, PostgreSQL 11.6 installed on RedHat, used that job both on my PC (W10) and on the server (RHEL 7) with the same results.

Best regards,

Damien

[Anonymous]

Hi,

    It depends on various factors like incoming data volume, availability of index on the key column which is used for update etc. Could you please try to use Bulk components available in Talend to increase the data load process?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Anonymous]

Hello Nikhil,

Thanks for your answer. I just tried replacing tPostgresqlOutput with tPostgresqlOutputBulkExec but it made me remember why I had trouble with it.

First you can't use it remotely since you load from a file on the machine (duh) but even locally, I can't make it work because it doesn't have the permission to open the file (even though I chmod 777'd it).

If I had to guess, I'd say it's because the postgres user on the machine isn't allowed to go into the directory where the file is, but it's no certainty.

Damien

[Anonymous · Accepted solution]

Hi,

   I would recommend to talk to system admins to give you correct permissions to read the data file generated by Postgres Bulk component as it is the right method to process data for large data volumes.

   Many of our enterprise customers are following the Bulk loading method and the system admins of those enterprises give right access permissions so that Talend can process the interim data files generated as part of Bulk process.

   tPostgresOutput is meant to handle small delta data operations only. In worst case, if your sys admins are not ready for the access, you will have to add a index to the key column which will be used by the Talend Output component during update.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Anonymous]

Hello again Nikhil,

Adding an index did the trick, big thanks for your valuable help.

Damien