Hi Stefan
As @Reguël Wermelinger pointed out above, there is no one single right answer to your question, as Ivy is flexible which translates into "timtoady" - there is more than one way to do it. Thus, we cannot offer an all-emcompassing solution.
However, here are a few pointers that might help:
- The system database contains both master data (like some application configurations and details, users, roles, ...) and more volatile process data. It may also contain business data. Master data has a different lifecycle than process data; business data is on yet another kind of lifecycle, if present at all.
- most of our tables contain createdAt and modifiedAt timestamp attributes which allow you to create size and growth statistics for the system database tables.
- business data stored using the Ivy API is residing in table IWA_BusinessData as well as in the ElasticSearch indices.
It largely depends on your configuration how much of the business data is duplicated in these ElasticSearch indices.
Because you state the size of the database, let me add an unrelated hint:
Make sure that your database indices are well maintained!
Some DBMSes offer automatic index maintenance. They will refresh indices once a certain percentage of the table rows have changed. For one of the big boys, this threshold is by default at 20%. These types of automations may backfire.
If you work intensively with processes, it won't take long until these 20% will not be attained anymore, and thus your indices will not get maintained. Unmaintained indices mean bad query performance.
Thus, assert that your indices are maintained on a schedule and that the maintenance job runs even if just a small portion of the table data have been changed.
added, 29.2.2024:
I have documented more details on maintaining your system database in this article:
https://community.axonivy.com/d/373-the-axon-ivy-system-database-handle-with-care