How can I ensure the PostgreSQL connection is closed after each Dataflow Gen2 run in Fabric Pipeline

[Davide_Esposito]

I'm using a Fabric Pipeline that runs a notebook to generate a list of dates. For each date, I invoke a Dataflow Gen2 activity using a ForEach loop. The Dataflow connects to a PostgreSQL database and retrieves one day of data using the passed parameter.

I was told I must ensure the database connection is closed after each query to avoid issues with connection pooling or lingering sessions. However, I can't find any documentation or setting that explicitly confirms this behavior.

My question is:

• Does each Dataflow Gen2 execution (triggered in a pipeline loop) automatically close the PostgreSQL connection after it finishes?

• Is there any setting, parameter, or command I can use to explicitly force the connection to close after each run?

• Are there best practices or monitoring options to confirm that connections are properly released?

Thanks in advance for any guidance or links to official documentation!

[v-tsaipranay]

Hi @Davide_Esposito ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @Davide_Esposito ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay]

Hi @Davide_Esposito ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[Davide_Esposito]

I'm using a Fabric Pipeline that runs a notebook to generate a list of dates. For each date, I invoke a Dataflow Gen2 activity using a ForEach loop. The Dataflow connects to a PostgreSQL database and retrieves one day of data using the passed parameter.

I was told I must ensure the database connection is closed after each query to avoid issues with connection pooling or lingering sessions. However, I can't find any documentation or setting that explicitly confirms this behavior.

My question is:

• Does each Dataflow Gen2 execution (triggered in a pipeline loop) automatically close the PostgreSQL connection after it finishes?

• Is there any setting, parameter, or command I can use to explicitly force the connection to close after each run?

• Are there best practices or monitoring options to confirm that connections are properly released?

Thanks in advance for any guidance or links to official documentation!

[lbendlin]

That depends on how your dataflow is designed, specifically what the destination is.  Usually PowerQuery only runs a single query against the data source and then immediately closes the connection.

You can also consider adding the CommandTimeout settings if that is available for the PostGres connector.

[v-tsaipranay]

Hi @Davide_Esposito ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

To address your questions:

1. Automatic Connection Closure in Dataflow Gen2:
Yes, each execution of a Dataflow Gen2 triggered within a Fabric pipeline (including inside a ForEach loop) establishes a new connection to the source (in this case, PostgreSQL), executes the query, and automatically releases the connection once the activity completes. This is managed internally by the Fabric runtime and aligns with standard data movement engine behavior.

2. Explicit Control Over Connection Closure:
At present, there is no exposed setting or parameter within the Dataflow Gen2 interface to manually force-close a connection. However, connections are scoped per execution context and are disposed of after the job ends which includes both success and failure paths.

3. Best Practices and Monitoring:

• While Fabric does not expose direct connection lifecycle logs, you can monitor connection activity and session duration on the PostgreSQL side using system views like pg_stat_activity.
• It is recommended to use short-running queries and ensure parameter folding is in place, which helps Dataflow push filters (such as the date) to the source, reducing query load and connection time.
• Avoid running excessive parallel executions (e.g., high concurrency in ForEach) that might exceed PostgreSQL’s connection limits. If needed, introduce batching or throttling in the loop.

Connections from Dataflow Gen2 are automatically released after execution. While there’s no explicit close command, you don’t need to manually manage connections in your current setup. For assurance, monitor your PostgreSQL instance and optimize your loop’s concurrency as needed.

Hope this helps. Please reach out for further assistance.

Thank you.

[Davide_Esposito]

'm using a Fabric Pipeline that runs a notebook to generate a list of dates. For each date, I invoke a Dataflow Gen2 activity using a ForEach loop. The Dataflow connects to a PostgreSQL database and retrieves one day of data using the passed parameter.

I was told I must ensure the database connection is closed after each query to avoid issues with connection pooling or lingering sessions. However, I can't find any documentation or setting that explicitly confirms this behavior.

My question is:

• Does each Dataflow Gen2 execution (triggered in a pipeline loop) automatically close the PostgreSQL connection after it finishes?

• Is there any setting, parameter, or command I can use to explicitly force the connection to close after each run?

• Are there best practices or monitoring options to confirm that connections are properly released?

Thanks in advance for any guidance or links to official documentation!