How to add (and update) an attribute to an entity that has a lot of records in the database?

1
For performance reasons we would like to add an attribute (long) to one of our core entities and update it to a value based on a different entity. However there are 33 million records of this entity in the database and running a maintenance script (batch update) using the ProcessQueue, is taking days to complete even when running on multiple threads. Also on our Test enviroment this migration is causing the database to grow beyond its limits.  Performing the same migration on a local database in plain SQL (using a temporary table and doing a replace of the original one), is taking just a couple of hours. However we can not allow our production evironment to be down for that amount of time. Besides it would require us to stop the environment, download the backup, perform maintenance scripts on a local machine, create a backup and upload it to the production enviroment. As of earlier experiences this is not a route we would like to go.  So is there an other way to perform this migration in an acceptable amount of time??? 
asked
3 answers
4

Hi Sander,

This is a difficult process that I have had to go through a few times. Here are some tips to help you.

First of all, when adding the attribute make sure that there is no default value set. If you set the default value to 0 or some other value then the database synchronization in the Mendix cloud will time out during startup.

Second, instead of using the process queue and Mendix microflows, do the migration using SQL, which is much faster. Here is how to get a database connection in Mx 6

// make sure to add the jdbc driver for postgresql in userlib

String url = "jdbc:postgresql://$HostName:$PortNumber/$DatabaseName";
JDBCDataStoreConfiguration jdbcSetting = Core.getConfiguration().getDataStoreConfiguration();
url  = url.replaceAll("\\$HostName", jdbcSetting.getHostName())
				.replaceAll("\\$PortNumber", ""+jdbcSetting.getPortNumber())
				.replaceAll("\\$DatabaseName", jdbcSetting.getDatabaseName())
DriverManager.getConnection(url);

With this connection you can execute SQL commands directly on the cloud database.

Finally, you need to find a way to prevent people from using the app while the database operation is running. You can do this most easily by preventing users from logging in. If you use an SSO you can usually deactivate it with a constant so that only local users i.e. MxAdmin can log in.

Hope this helps,

Andrej

answered
0

Have you considered addingthe attribute in a new entity with a 1-1 relation with your current entity?

answered
0

It may not work in your case but one approach could be to make the update less urgent by finding the records that are most likely to be accessed (or make code update records when they are requested). And then update the rest of the records as a background process that does not require downtime

answered