Database Optimization and Migration – Project Examples

Database optimization and migration examples on this page cover data modeling, PostgreSQL performance work, advanced MySQL queries and procedures, automated data processing, and SQL Server environment deployment. Each project explains the problem, the scope of the solution and the result without disclosing how the solution was built.

For the service scope and available forms of collaboration, see database design, optimization and migration. You can select a specific reference below.

Competitor, results, and cup database

Completed in Q2 2023 and expanded in Q2 2024.

Competitor, race, and results database connected to Joomla modules

Problem

The Serbian Open Water Swimming Federation ran its competitions on an existing Joomla website, but the data on competitors, races, results, and cups was not connected into a single whole. Competitor registration, scoring, and the display of previous and current competitions required manual work, and certificates and notifications were prepared separately.

Solution

The goal was to build a database that allows efficient management of competitions, results, and all related information, and to fit it into the existing Joomla website through custom modules:

  • a database for competitors, races, results, cups, and other related records;
  • Joomla modules for displaying results, scoring, logs, and reports;
  • an improved registration section for competitors entering upcoming races;
  • HTML templates for certificates and emails;
  • integration with the existing Joomla installation, with no move to a new website;
  • a connection to the CacaTajka system for data management.

Technologies used

Result

Scoring is automated through connected databases, race registration has been improved, and previous and current competitions are displayed on the website from the same data.

Client feedback

★★★★★5/5

“Dejan Dozet worked on the website of the Serbian Open Water Swimming Federation, improving competitor registration for upcoming races and automating scoring by connecting databases and displaying previous and current competitions. All praise for the work, dedication, and professionalism!”

PostgreSQL database optimization

Completed in Q3 2022 and expanded in Q4 2022.

Problem

The client's company used a PostgreSQL database tailored to its specific operational needs. Functionally everything worked, but speed and responsiveness left a lot to be desired. The database ran on a dedicated server, and slowdowns during operations were becoming an increasingly noticeable problem.

Solution

The goal was to keep all existing functions and the look of the system while improving speed and responsiveness:

  • performance analysis: PostgreSQL-specific diagnostic tools were used to find the parts of the system causing slowdowns;
  • SQL query optimization: complex queries were refined and key columns indexed so data could be retrieved faster;
  • server-level tuning: the PostgreSQL configuration on the dedicated server was adjusted for optimal database performance;
  • data architecture revisions: targeted changes were made to the data structure without disrupting core functionality;
  • a consistent look: all improvements were made in the back end, while the interface users see stayed the same.

Technologies used

  • PostgreSQL: the relational database that was optimized.
  • PgAdmin and other diagnostic tools: performance analysis and optimization.
  • Server configuration tools: fine-tuning the dedicated server for PostgreSQL.

Result

The database was optimized at the level of queries, indexes, structure, and server, while users kept the same interface and the same way of working.

MySQL queries and procedures for data science

Completed in Q4 2022 and expanded in Q3 2023.

Problem

The client is developing a data science application that analyzes the performance of short-term vacation rental properties, with the ultimate goal of predicting which types of houses perform best. The engineering team, led by the company's founder, himself a database engineer, was looking for extra help with advanced queries and with work the team did not have time for. The task description also hinted at more complex requirements.

Solution

The goal was to enhance the application with advanced MySQL queries and procedures that give deeper insight into property performance and prepare data for predictions:

  • stored procedures that use CTE Update queries to rank records by performance into quartiles and deciles;
  • efficient queries, written together with carefully placed indexes, for better performance;
  • static report tables, prepared so the web application can read them at high speed.

Technologies used

  • MySQL: the database management system in which the advanced queries, stored procedures, and application performance optimization were built.

Result

The application receives data ranked by performance and report tables ready for fast reading, providing a basis for comparing properties and making predictions.

SQL hospitality revenue statistics

Completed in Q4 2020 and expanded in Q2 2021.

Problem

The client had monthly revenue data for several properties, organized by property and month, but needed to look at it by season. The challenge was to aggregate and group the data so that monthly revenue was reliably assigned to the right seasonal groups, with more complex details that the job description did not state explicitly.

Solution

The goal was to create an SQL procedure that categorizes and aggregates monthly revenue into seasonal categories, enabling more advanced analysis:

  • data grouping: monthly revenue from different properties and months is grouped into cohesive seasonal blocks;
  • automatic processing: the procedure runs on its own, without manual intervention, so data is continuously updated and analyzed;
  • scalability: the procedure accepts new properties and longer data periods.

Technologies used

Result

Seasonal statistics come from repeatable processing that grows along with the data, and the client completed the tasks as planned.

Client feedback

★★★★★5/5

“Dejan is an invaluable member of our team, working on complex database queries and procedures. We completed the tasks we needed to and will be hiring him again!”

Automated SQL Server deployment

Completed in Q4 2020 and expanded in Q3 2021.

Problem

The client needed SQL Server Developer edition to be deployed silently, without clicking through installation screens, and to be ready for work right away: with SQL Server Agent enabled and a profile created for sending mail from the database. The server had to be accessible exclusively through a single SQL authentication, and the SMTP credentials were to be entered later.

Solution

A C# script was built that automates the entire deployment:

  • silent installation of SQL Server Developer edition, in the background and without manual steps;
  • SQL Server Agent enabled right at deployment;
  • a Database Mail profile, created and ready for the SMTP details to be entered later;
  • restricted access: the server is accessible only through the intended SQL authentication.

Technologies used

Result

SQL Server for development is deployed the same way every time, with the agent enabled, the mail profile prepared, and access restricted.

Separate case study: GeoPackage to SQL Server migration

Geospatial data migration has a distinct technical and search intent, so it remains a separate case study. The C# application transfers data from SQLite-based GeoPackage (.gpkg) files to Microsoft SQL Server while handling different geospatial types, checking data integrity, and logging errors.

Read the complete GeoPackage to SQL Server migration case study.

If you need to design a new structure, improve existing query performance, or migrate data safely, see my database development and optimization service.