Freight Management and Logistics
Improving Search Query Runtime to Increase User Adoption.
Our client’s new load and carrier search platform could not keep up with demand, adversely affecting user adoption as customers abandoned their searches.
2nd Watch evaluated our client’s infrastructure, recommended platform enhancements, and assisted with implementing modifications.
The maximum number of potential concurrent users surpassed our client’s goal, while the query runtime decreased dramatically.
A major freight management and logistics organization’s load and carrier search platform was running slow queries. This was negatively impacting customer experience and impeding user adoption. Our client did not have the experience to address the multiple factors slowing down their platform, so they sought 2nd Watch’s help to resolve these critical performance issues.
This freight management and logistics organization was experiencing large search volume through their new load and carrier search platform and hoped to continue expanding user adoption. However, they were also facing growing customer frustration as their application was running queries extremely slowly. Customers expected much faster performance and would abandon searches out of frustration. Our client risked a decline in their user adoption rates and even a potential net decrease in users.
Our client’s GraphQL and PostgreSQL database configuration was the primary cause of their unsatisfactory platform performance, but they lacked the AWS and PostgreSQL experience necessary to remedy these problems:
- Thousands of concurrent requests
- Denormalized transactional design
- Minimal indexing
This client needed 2nd Watch’s assistance to analyze their server configurations, database and application code, and AWS resources. They then wanted our team’s recommendations on platform enhancements, as well as help implementing the modifications to improve query runtime. This would ultimately alleviate customer frustration and boost user adoption to ensure our client stayed competitive.
The 2nd Watch team evaluated our client’s existing infrastructure and made recommendations to ensure proper scaling of the PostgreSQL database based on load testing requirements.
This involved multiple phases of evaluation and recommendations:
- Reviewed indexes and queries and then made recommendations or directly made modifications to improve performance of the search application
- Developed indexing strategy and recommendations for BTree, Gist, and Geospatial indexes
- Performed hypothesis testing of new indexes to determine effectiveness on performance
- Optimized user-defined functions causing performance issues
- Assisted with deploying the recommended index and code changes
- Altered RDS server and Hasura configurations to add read replicas, allowing for horizontal and vertical scaling
- Provided support for horizontal scaling through the use of read replicas and Aurora
- Experimented with DDL changes to normalize the database model
- Provided recommendations and value-adds for normalizing the data model
2nd Watch’s solution dramatically improved the performance of our client’s load and carrier search application. We achieved the primary requirement from the business team, which was to allow more than 25,000 simultaneous users, and relieved a major bottleneck to expanding user adoption.
In fact, the maximum number of concurrent users increased from approximately 2,000 to more than 60,000. This project also reduced the “worst case” query runtime from 40 seconds to less than 50 milliseconds.