adf postgresql linked service connector settings

Question

We need to update our ADF postgresql connector to the newer version from the legacy version and in order to make it work we've had to add some settings. Can anyone tell me if these settings could have a negative impact? Our pipelines were failing before adding these additional settings.

I'm wanting to be sure I understand before deploying this to production.

Thank you!

            "pooling": false,
            "connectionTimeout": 600,
            "commandTimeout": 0,
            "noResetOnClose": true

Answer 1

Hi JB,

Thanks for reaching out to Microsoft Q&A.

I have tried to summarise all the parameters that you want to be analysed. The decision can be yours but it is always better to test these parameters in lower env before deploying in prod to avoid such scenarios.

1. "pooling": false:
   • Impact: Disabling connection pooling means that each operation will open a new connection to the PostgreSQL server rather than reusing a pool of connections. This can increase the overhead associated with opening and closing connections, particularly in high-throughput scenarios, which may reduce performance. However, it may also reduce resource usage if you're only making occasional queries.
   • Recommendation: If your pipelines have low concurrency and short durations, disabling pooling might not cause significant issues. However, for high-frequency workloads, pooling is typically recommended.
2. "connectionTimeout": 600 (secs):
   • Impact: A connection timeout of 600 seconds (10 minutes) means that the service will wait for up to 10 minutes to establish a connection before timing out. This is a relatively long wait time, and while it provides flexibility for environments with intermittent connectivity, it could delay failure detection if there are genuine connection issues.
   • Recommendation: Consider reducing this if you don't expect such long delays in connection establishment, especially if most of your connections are stable.
3. "commandTimeout": 0:
   • Impact: Setting the command timeout to 0 means there is no limit to how long a command can run before timing out. While this prevents premature timeouts for long-running queries, it could lead to issues if queries hang indefinitely, especially if there are blocking conditions or network delays.
   • Recommendation: It may be safer to set this to a reasonable value to avoid runaway processes, depending on your use case.
4. "noResetOnClose": true:
   • Impact: This setting prevents the reset of the connection when it is closed. It can improve performance by avoiding the overhead of resetting state (e.g., transaction isolation level, session variables) each time a connection is closed. However, it could also lead to inconsistent behavior if the connection is reused without a proper reset.
   • Recommendation: This setting could be beneficial in high-throughput scenarios, but you need to ensure that your pipeline logic doesn’t rely on the reset behavior for each connection.
5. Performance: Disabling pooling and allowing infinite command execution could impact the responsiveness and scalability of your pipelines, especially under heavy load.
6. Stability: Setting connection and command timeouts too generously can mask underlying issues, leading to delayed error detection or indefinite waits.
7. Resource Utilization: Some of these settings might increase the burden on your PostgreSQL server

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.