topic Improving the use of a PostgreSQL-based database as a target in Qlik Replicate

Improving the use of a PostgreSQL-based database as a target

Vinicius_Luiz — Mon, 27 Nov 2023 19:01:58 GMT

Hi everyone!!

When using PostgreSQL as a target, we are facing some challenges related to CDC performance.

When the number of transactions reaches thousands, the CDC takes longer than expected compared to replications carried out in other DBMS (Oracle, for example).

In other words, transactions that take around 10 seconds to be applied using Oracle as a target, take around 2 minutes to be applied using PostgreSQL as a target.

Do you have knowledge about configurations in PostgreSQL to support a large volume of data update?

Re: Improving the use of a PostgreSQL-based database as a target

boaz_newman — Tue, 28 Nov 2023 08:14:59 GMT

Hello

The first thing to do is to check if the db is optimized, perhaps it needs tables and indexes rebuild, and also regularly updating the db statistics.

Regards

Boaz

Re: Improving the use of a PostgreSQL-based database as a target

SushilKumar — Tue, 28 Nov 2023 08:44:43 GMT

Hello team,

To add more onto Boaz Suggestion. Any target database is no different from the Source database. Qlik Replicate Works in the Same Way as any other Application connects with the Database to apply Changes fetched from the Source db. to Apply changes via DML.

It will Follow the same principle. It Submit the Querry as an Application does, and Server generate execution plan or profile is to execute the DML changes faster.

And to make Changes faster one should follow the Suggestion or recommendation by Database help document.

hope this helps.

Regards,

Sushil Kumar

Re: Improving the use of a PostgreSQL-based database as a target

Vinicius_Luiz — Tue, 28 Nov 2023 20:20:09 GMT

Hi Boaz.

Thanks for the contribution!

We always create tables with indexes and primary keys corresponding to the source database. Furthermore, we carried out an analysis of the PostgreSQL database and found that table statistics are being properly computed by PostgreSQL's internal procedures.

I was researching some settings in the database to optimize transaction performance and found parameters such as: maintenance_work_mem, checkpoint_timeout, max_connections. Have you had experience adjusting these database parameters?

Re: Improving the use of a PostgreSQL-based database as a target

boaz_newman — Wed, 29 Nov 2023 09:56:33 GMT

Sorry, I have no experience optimizing a Postgres db.

There are two significant points that you mentioned:

1. "When the number of transactions reaches thousands..." - why are there so many concurrent transactions on the target db?

2. "transactions that take around 10 seconds to be applied using Oracle as a target, take around 2 minutes to be applied using PostgreSQL as a target" - this indicates that there is a big difference between the Oracle target and the Postgres one. There could be many reasons for that:

Differences between the resources (server, cpu, memory, etc.)

Differences between the db configuration and optimization.

Differences between Oracle and Postgres

Anyway, as my colleague mentioned, Replicate sends SQL executable statements to the target (in bulk apply mode, by default) the rest is up to the target.

Regards

boaz

Re: Improving the use of a PostgreSQL-based database as a target

Vinicius_Luiz — Wed, 29 Nov 2023 17:03:24 GMT

Hello

1. I referred to the number of operations performed, I'm sorry. Some of our tables have a reasonably high volume of updates.

2. Yes, we are considering improving the server, CPU, memory, etc. However, before that we are researching PostgreSQL database configurations that could improve performance.

Re: Improving the use of a PostgreSQL-based database as a target

Dana_Baldwin — Thu, 30 Nov 2023 15:35:42 GMT

Hi @Vinicius_Luiz

In conjunction with checking the target performance you might consider engaging our Professional Services team to ensure your Replicate task is tuned properly for your use case. You can contact your account manager to initiate this process.

Thanks,

Dana

Re: Improving the use of a PostgreSQL-based database as a target

GELS — Tue, 07 Jan 2025 09:29:53 GMT

We do have a similar problem , can you share what was the solution applied to resolve this?

Oracle syncing faster whereas postgresql taking longer time to sync CDC

Re: Improving the use of a PostgreSQL-based database as a target

DesmondWOO — Fri, 10 Jan 2025 09:36:14 GMT

Hi @GELS ,

When using PostgreSQL as the target, there are additional steps before writing changes to a temporary table:

Changes are written into a CSV file.
Replicate uploads the CSV file to PostgreSQL.
The CSV file data is copied to a temporary table.

It could be a reason why the performance is not as good as that of other databases. You can enable tracing on TARGET_APPLY to identify which steps consumed the most time.

Regards,
Desmond

Re: Improving the use of a PostgreSQL-based database as a target

Dana_Baldwin — Fri, 10 Jan 2025 15:53:10 GMT

Hi @GELS

In addition to @DesmondWOO suggestion, you can also elevate logging for performance to trace which will confirm if the latency you are seeing is on the source, target, or Replicate server.

If you would like help tuning the task to perform as well as it can please consider asking about Professional Services help (fee based) with your Account Manager.

If it is slow due to an error condition, or applying changes in one-by-one mode, please open a support case.

Thanks,

Dana