How to deal with loudest guy-driven decisions in tech: Part II
We started with issues of a wrong tech choice in part I, and now we’ve come to part II where we discuss our approaches to the best solutions. There were two possible solutions to our problems. The first was to keep Ignite and invest efforts to optimize queries and improve cluster configuration options. These include rewriting queries, changing configurations, optimizing indexes, and adding more nodes to the cluster. The main concerns for this approach were related to time and money that had to be put into the changes. Furthermore, we did not have the guarantee that this will achieve desired results. The alternative was to substitute Ignite with some well-known, mature technology. During the testing phase, we found that limited resources and a smaller developer community seriously impacted our capabilities to understand the issues we faced. Having some mature technology with a larger community and plenty of available resources would for sure help us along the way. “When comparing test results, PostgreSQL was 10 to 1000 times faster than Ignite.” After some research, we decided to try out PostgreSQL, which matches the above-mentioned criteria. Migrating to a new database engine is not a trivial task to perform. It requires modifications of the application code which connects to the database and modification of all ETL processes that are connected to the database. We definitely did not want to invest so much effort to find out that our alternative is not working either. That’s why we went back to the list of sampled visualizations that generated most of the stress on the database. The test setup required only a new PostgreSQL database instance, and the production snapshot data we used during the experiments. As SQL syntax between Ignite and PostgreSQL is not the same, we had to modify table DDLs and queries. As we tested only for a subset of the most relevant queries, this step did not take so much time, even when doing it manually. Comparing test results against Ignite showed that PostgreSQL is 10 to 1000 times faster. Also, it stayed stable during the entire test process. It’s important to note that allocated server resources were the same between these two engines, meaning the same infrastructure cost. Having a single server instead of a cluster topology resulted in simpler configuration and administration procedures when compared to Ignite. Promising results like these led us to full-scale testing of the entire application. This is where we get to the heterogeneous database migration. Heterogeneity means that the existing and new databases aren’t the same technology. They differ in schema structure, data types, and SQL syntax. Heterogeneous migration is highly demanding. There are some tools that could help, but neither of them will completely solve the problems without manual interventions (even though some guarantee it). This is especially the case with query translations. Besides that, Ignite is not so widely spread technology, so fitting these tools for it might be challenging. In the end, there is a question of how much time would take us to master them. The first phase of the full-scale migration was assessment where we examined the complexity of database schema and queries, amount of the data we had to transfer, application changes to support the new engine, and the impact on the infrastructure. Starting with the last one, infrastructure, we had no major issues. All systems were hosted on the Amazon Cloud so setting up a new database was simple. Besides new development and production environments, we created two additional experimental environments – one for a PostgreSQL-based application, and the other for an Ignite-based one. The idea was simple. The only way to test if the migration process was successful was to compare visualizations with the existing application. As the development environment is always changing (in terms of data and features), we needed stable, isolated environments to experiment with. Once we had our environments in place, we could start with the migration. Firstly, we had to create schemas and tables specified by DDLs. This step is known as schema migration. As the number of schemas and tables was not so large, we decided to go with the manual migration approach. Most common changes included different data type namings. “The biggest challenge we faced was related to the query translation process.” The next step was to populate our database with a data snapshot we had on our Ignite experimental environment. For this purpose, we used the Pentaho Data Integration tool. As we used it for ETL development back in the day, we were familiar with its functionalities, which allowed us fast and easy data migration implementation. The application development team did great work on their side. The main activities included changing and configuring the connection pool, and the way the application builds queries on the backend.The biggest challenge our team faced was related to the query translation process. As we said, Ignite and PostgreSQL engines do not have the same SQL dialect. Simply put, some clauses written for Ignite would result in syntax errors when run on PostgreSQL. That’s why we had to translate and validate each query application used. Query Migration Having more than 300 complex queries with a short deadline and limited resources meant we had to work efficiently to finish the project on time. It was clear that we cannot use a manual approach, as we did in the PoC phase, since it can be tedious and error-prone. We had to adapt the approach to something more effective. We started by examining the subset of translated queries from the PoC phase. For this purpose, we used a file comparison feature inside Visual Studio Code. This way we observed patterns. Once we compared all source–translated query pairs, it was clear that the same translation logic is shared between many queries. Ignite-specific clauses were translated to the PostgreSQL ones in the same way. The only difference occurred in the number of used columns, their names, or formatting. Strings and patterns – ring a bell? Of course, regular