Cockroachdb support and performance tests

Hi all

BTW great work on the 6.3.0 release, a lot of forward moving changes and performance improvements.

I am actually using the very same performance tests to test cockroachdb 1.1 on digital ocean and thought to write this while I wait for the results. Note cockroachdb uses the postgres wire protocol and has a lot of syntax overlap which helps quite a bit.

I had to make some minor changes to the postgres database scripts to get it to run. The primary issues are:

  • foreign keys have to have an index
  • you have to first create the index before creating the foreign key (found a few of these especially for CMMN)
  • doesn’t support the timestamp(6) syntax (which isn’t consistently used across the ddl scripts)
  • liquibase doesn’t have official support for it and there are a few niggles and the jdbc driver returns product as Postgresql. Extension is possible.

I am also not 100% sure on the processes around database updates as to how this could become a more permanent member. I don’t see liquibase files for the main process engine tables for example, and not sure how the distribution sql is generated, didn’t see a maven task or something along those lines.

So I would appreciate any input on what would suit the community from here. And if you want to get hold of the performance tests to compare with the performance blog post, let me know. (Mine are still running, but so far no Exceptions…)

[UPDATE]
And just as I posted this, my first Exception, but seems to be a connection pool issue and nothing flowable related. Hikari may be using some unsupported functionality here when trying to valid connection. Will look into this…

Hi,

Thanks, also for your contributions for 6.3.0.
Joram did a thorough test with cockroach db some time back (http://www.jorambarrez.be/blog/2016/11/02/running-flowable-on-cockroachdb/). The latest tryout was a year back. Some changes were needed to the Flowable schema indeed.

If we can fully support Cockroach db then we would add it to our QA environment and make sure the scripts are updated for each release and fully tested. The process engine, idm engine and the different service modules (like variable and job), use a handwritten sql upgrade approach. For the newer engines and the UI apps we use Liquibase because that’s a lot more convenient.

If we can make Cockroach db run against the full Flowable test suite then we would definitely be very interested in adding support for this database.

Best regards,

Tijs

Yeah, I’ve seen Joram’s blog post. They have added a lot more features since then, and they are keen to get it working with the common ORM’s like hibernate which is a good sign.

See this issue for liquibase support, which is currently where it seems the most work is needed for flowable:

It seems that the strategy they are following is to make it as postgres compatible as possible, using the standard postgres jdbc driver which returns postgres understood versions and product name. So this creates challenges in the meantime as one can’t use std metadata approaches to differentiate them.

So following that strategy one could work on flowable’s postgres support to make the schema and sql to be compatible with both. Kind of lowest common denominator.

Maybe as a start I could just start modifying incompatible DDL scripts, which so far has been limited to the things I mentioned above. Then one can see how much mileage can be gotten from that.

The only other issues so far are:

  • jdbc4 connection validity checking doesn’t work, so need to set Hikari connectionTestQuery
  • Liquibase startup exception trying to get reserved words, which doesn’t stop anything from working. So one either needs to silence the logger or to create a liquibase database extension with higher priority than postgres one, and a way to query the database to know which it is. Or flowable can make a custom choice of database implementation based on some flowable specific setting instead of liquibase detection.

Actually an issue with liquibase is that you cannot create a foreign key in a transaction with alter table because the index must exist prior to the transaction: “foreign key requires an existing index on columns”

So the only way to do that is in multiple changesets, or one could create the foreign key as part of the create table statement which creates an implicit index.