in our company we consider to use Camunda. We did some performance tests with Camunda using SQL server 2019 and PostgreSQL 11.
We are using REST API and .NET client. We use mainly external tasks and fetch and lock approach.
We observed much better performance with PostgreSQL. The better performance is probably caused by using much effective SQL commands with PosgreSQL than with SQL server.
When we started like 100 processes per second the performance of SQL server was very bad very quickly. Performance with PostgreSQL was stable for several days.
Main DB engine used in our company is SQL server and it would be very hard to get permission to use PostgreSQL.
Is it possible for you guys focus on improving SQL server performance?
Currently queries for SQL server are using row_number and rank functions to get subset of data e.g. external tasks. It is slow and not scaling well.
LIMIT is used by Postgre which provides much better performance.
SQL server 2012 supports OFFSET and FETCH which do the same like LIMIT in PostgreSQL.
This you can see in DbSqlSessionFactory when you look for method
Thanks ahead for your answer.
All the best.