Upgrade to Mx 7/8+ with float to decimal conversion cause PostgreSQL storage explosion
Good day, This week I was hit by an “exploding” Database (happily in QA ;) ) as we are upgrading the client to Mx 8 (from 6) with a PostgreSQL database that is 95% float data, in like ~30 columns per table. Doing the Mx8 model start then the database “modifications” that is run inside a single transaction, suddenly make the ~150GB DB explode to past 890GB (on our 960GB SSD ;( ) The first step I used to get past these problems, is to convert the Mendix’s modification script’s multiple `ALTER TABLE.. ALTER COLUMN..` statements into a single `ALTER TABLE..` with multiple `ALTER COLUMN..` actions I then found that there are a couple tables that will take time, and as they are not locking each other, I can run them in parallel with some other magic (the words sed, ‘m2ee psql’, & and `bash` piped together ), but for that to work I needed to merge all the multiple line SQL statements into single line statements to make easy GREPping possible. For anybody else hitting this exploding PostgreSQL DB problem, here is my AWK script that I used to do the merging of the multi-line statements and the ALTER TABLEs, on the same table: https://gitlab.com/-/snippets/2138904
We were hit with the exact same problem. During going live we experienced ALTER TABLE for 6 attributes on our largest table. Normally nothing to worry about, but alas, the db-space was used up, no more database connections and everything froze. Only after Mendix increased our db-space and we let the conversion run for an hour extra or so, the system was up again.
This probably had not happened on our test&accp because those got vacuumed because of a database-restore earlier on. Our production has so far never been restored, so also not vacuumed. Therefor test&accp both converted within acceptable limits, production did not.