How we reduced database costs from $687 to $134

Project overview

‍Restimo integrates all food delivery channels, POS and deliverymen in one place. It provides order integration, menu management and business reporting on one screen and allows you to view orders from all sales channels simultaneously. The user has access to two views - the order list view and the order details view.

‍

Since launching the app, the number of new orders is growing linearly and with it the amount of data that needs to be stored and processed. 

‍

The system is increasingly loaded, but its load is intermittent due to its operation in one time zone - the daytime load is much higher than at night, with peaks around 2pm and 7pm.

This results in several issues, like excessive load of the system and insufficient speed of reading and writing the main data of the application during peak hours.

We used multiple metrics to understand the system's performance, but in this case the most important one was the database CPU utilization.

This metric indicates how much of the CPU's processing capacity is being utilized at a given point in time.

High CPU utilization may indicate that the CPU is working close to its maximum capacity, which could lead to performance issues such as slower response times or system lag.

‍

Problems

• Excessive load of the system
• Slow speed of reading and writing the main application data during peak hours
• Consuming high processing power that is not needed at night
• High server costs

‍

Solutions

• Optimizing the data structure
• Change of loading technique
• Introducing data caching 
• ORM modifications

‍

Challenges

1. Introducing lazy loading strategy

Only basic data is loaded and visible in the order list view. The rest of the data is loaded when entering the detailed order view.

With this change, much less data was needed at one time. This resulted in a much lower server load.

‍

2. Caching on the frontend (Tanstack Query) 

This significantly improved the speed and responsiveness of the user interface. 

Storing copies of data (order information) directly on the client side, was to minimize the number of queries to the server and speed up access to the data.

Combined with lazy loading, it resulted in about 4x reduced load during peak hours.

‍

3. Index additional columns used in order list queries (order status, restaurant ID, and so on).

Indexing columns means creating a structure that allows you to quickly search data based on the values in specific columns.

Indexes are created on these specific columns, which greatly speeds up the process of searching and filtering data related to orders.

‍

4. Caching order details on the backend (Redis)

Storing certain data in the server's memory is intended to provide quick access to that data, rather than querying the main database with each request. 

This optimisation technique has improved the performance of the application by minimizing the number of queries to the data source.

Along with indexing additional columns, this resulted in another 4x reduction in peak load.

‍

5. Modifications of the TypeORM library

The code generated by TypeORM to communicate with the database has been reviewed and optimized, for example by applying more specific and lighter functions.

Changes to the ORM affect performance, query structure and the overall quality of communication between the application and the database.

‍

6. Simplification of the database structure

We flatten the structure from three tables to one basic table (ORDER) with additional JSON columns (customer, customer address, positions, position.modifiers, courier).

The courier, customer, address, position, and modifiers data were never used without the order data. By storing all the data in a single table, order-related queries can be more concise because there is no need to join additional tables.

That means an increase in the efficiency of order-related operations and it resulted in another 3x reduction in peak load.

‍

Goals 

• Faster loading of the user interface
• Optimization of processing power usage
• Ensuring the system is ready for further data growth

‍

Achievements 

• Stabilized base infrastructure:

CPU 6-11%

• The need for processing power reduced: 

Lowering the tier from t4g.2xlarge to t4g.large

• Database cost reduction (AWS RDS):

From 687$ per 100k new orders in September 2022

To 134$ per 100k new orders in December 2023

‍