Introduction

A prominent financial institution, with over 30 years of operational history, faced significant challenges in maintaining query performance due to the vast volume of transactional data accumulated over two decades. While regulatory requirements made the organization store this historical data, only the most recent five years were routinely queried. Hexacluster implemented a strategic data partitioning and archiving solution, significantly enhancing query performance while ensuring full compliance with regulatory mandates.

Background

This financial institution, operating for over three decades, processes millions of transactions daily. As part of regulatory compliance, the organisation is required to retain 20 year's worth of transactional data. However, the continuous growth in data volume led to progressive slower query responses, adversely affecting business operations and decision-making processes.

Challenges

The organization faced several key challenges:

• Degraded Query Performance: The accumulation of vast amounts of transactional data resulted in slow query responses, particularly when accessing older records. Despite the requirement to retain 20 years of data, only the last five years were essential for day-to-day operations.
• Operational Efficiency: The sluggish query performance directly impacted the organisation's ability to make timely decisions, thereby hindering operational efficiency.
• High Cost: Storing this large amount of data requires large volume of storage disks. With SSD’s and NVME this cost can multiply into enormous amount.

Solution/Implementation

Hexacluster implemented an effective solution that involved strategically partitioning and archiving the financial institution's transactional data. The transaction table was partitioned by year, with the last five years of data retained in active partitioned table and older data moved to archive partitioned table.

To manage and maintain partitions, this approach utilized the well know partition extension in PostgreSQL called pg_partman. It supports both Range based and List based partitioning techniques. To further enhance query performance, appropriate indexes were created for the active partitions, ensuring that frequently accessed data could be retrieved more swiftly.

In addition to partitioning, data older than five years was transferred to an archive table in a different schema and a different Tablespace, making it accessible on-demand. This reduced the load on the disk (tablespace) containing active partitions and improved the speed of queries involving recent data. The application was also updated to build queries that could dynamically access both active and archived data, ensuring seamless access to historical records when needed.

To move the partitions from one tablespace to another, we used the Attach and Detach partition features in PostgreSQL and then we used pg_repack to move the detached tables from one tablespace to another with minimal locking.

The implementation of different tablespace also helped the customer reduce the Cost that they were spending on SSD and the overall load (IOPS) that was incurred on the SSD’s. Since the data more than five years was not accessed frequently, we recommended the Customer to use a cheaper storage rather than NVME/SSD.

The implementation process was carried out in four key phases. First, a thorough analysis of the existing data and query patterns was conducted to identify performance bottlenecks. Following this, the transaction table was partitioned, and an archive table was created in a different schema to store historical data, and the query performance further optimized using Indexing techniques. All of this exercise was performed after a rigorous testing and validation to ensure that the solution meets both performance expectations and compliance requirements

Results/Outcomes

The implementation led to several positive outcomes as follows.

• Improved Query Performance: Queries involving recent data now gets executed 800% faster on average, significantly enhancing the institution's operational efficiency.
• Regulatory Compliance Maintained: The institution continues to meet its 20-year data retention requirements, ensuring full regulatory compliance.
• Enhanced Operational Efficiency: The faster query performance has resulted in quicker decision-making processes, positively impacting daily operations.
• Cost Reduction: By moving the historical data to a different disk, we cut their expenditure incurred due to IOPS by 60-70%.

Conclusion

This case study illustrates how Hexacluster’s strategic data partitioning and archiving solution effectively resolved performance challenges without compromising on regulatory compliance. The success of this implementation provides a blueprint for other organizations facing similar data management issues, offering a clear pathway to an enhanced operational efficiency and data compliance.