PostgreSQL default connection limit of 100 might cause issues for larger systems

[SzymonPobiega]

Describe the suggested improvement

Is your improvement related to a problem? Please describe.

PostgreSQL has a default limit of number of concurrent connection -- 100. This limit seems to be caused by the implementation decision to represent each connection as a separate process. For the context, SQL Server's default limit is 32K.

The limit may cause issues even when not all endpoints are processing messages at the same time as the connection pooling implemented by the npgsql ADO.NET driver causes the connections to linger around (by default for 5 minutes) even if no longer used.

Each endpoint will use up to maximum concurrency + 1 connections.

The limit affects larger systems and is more visible when using the full Particular Platform as each of the receivers run by ServiceControl, that is:

• Error queue
• Audit queue
• ServiceControl main queue
• Monitoring queue

will use up to 11 connections.

Potential workarounds

Besides increasing the maximum number of connections, another workaround is to lower the Connection Idle Lifetime from the default of 5 minutes to e.g. 30 seconds. That option is advised especially for the Particular Platform tools which experience frequent bursts in number of messages and it is important that they dial down on connection usage as soon as the burst is over.

Additional Context

No response