Cross Shard Query Optimization Scheme under the Strategy of Sharding and Table Partitioning in Payment System Database

A Structured Digest of the IMRAD Framework for Distributed Payment Database Query Optimization

· International Journal of Multimedia Computing· 2026· DOI 10.38007/ijmc.2026.070205

The core problem

Payment systems generate massive volumes of transactional data that must be stored and queried with low latency and high reliability. As data grows, single-node databases become bottlenecks. Sharding distributes data across multiple nodes, while table partitioning divides large tables into smaller, manageable pieces. However, cross-shard queries—those that need to join or aggregate data from multiple shards—introduce significant performance challenges due to network overhead, data shuffling, and coordination costs. This paper addresses the problem of optimizing cross-shard queries in payment system databases that employ both sharding and table partitioning. The introduction motivates the need for a hybrid strategy that leverages partitioning within shards to reduce the amount of data transferred across shards during query execution. Key research questions include: How can partitioning schemas be designed to minimize cross-shard data movement? What query planning and routing techniques can reduce latency? And how can the scheme be evaluated in terms of throughput and response time? The proposed optimization aims to improve query performance while maintaining data consistency and sys

Innovation

The paper reports experimental results from a prototype implementation evaluated on a simulated payment system workload. The experiments compare the proposed cross-shard query optimization scheme against baseline approaches: (1) naive sharding without partition-aware optimization, and (2) sharding with basic partition pruning. The evaluation metrics include query response time, throughput, and network traffic. Results show that the proposed scheme reduces average cross-shard query latency by up to 45% compared to the naive approach and by 25% compared to basic partition pruning. Throughput improvements are similarly significant, with up to 60% higher queries per second under high load. Network traffic is reduced by approximately 50% due to predicate pushdown and partition pruning. The results also demonstrate that the cost model accurately selects efficient execution plans, with the optimizer choosing the optimal plan in over 90% of test cases. Scalability tests indicate that the scheme maintains performance as the number of shards increases, with sub-linear growth in query latency. The paper includes detailed graphs and tables showing performance under varying data sizes, query ty

Payment systems generate massive volumes of transactional data that must be stored and queried with low latency and high reliability. As data grows, single-node databases become bottlenecks. Sharding distributes data across multiple nodes, while table partitioning divides large tables into smaller, manageable pieces. However, cross-shard queries—those that need to join or aggregate data from multiple shards—introduce significant performance challenges due to network overhead, data shuffling, and coordination costs. This paper addresses the problem of optimizing cross-shard queries in payment system databases that employ both sharding and table partitioning. The introduction motivates the need for a hybrid strategy that leverages partitioning within shards to reduce the amount of data transferred across shards during query execution. Key research questions include: How can partitioning schemas be designed to minimize cross-shard data movement? What query planning and routing techniques can reduce latency? And how can the scheme be evaluated in terms of throughput and response time? The proposed optimization aims to improve query performance while maintaining data consistency and system scalability.

The methodology proposes a cross-shard query optimization scheme that integrates sharding and table partitioning. The approach consists of several components: (1) a data placement strategy that partitions tables within each shard based on query access patterns, (2) a query routing and planning module that decomposes cross-shard queries into subqueries targeting specific partitions, and (3) a cost model that estimates the cost of alternative execution plans considering network transfer, local processing, and result merging. The cost model can be expressed as:

Why it matters

The discussion interprets the results and explores implications for payment system database design. The key insight is that table partitioning within shards can significantly reduce cross-shard data movement by enabling finer-grained pruning and parallel processing. The partition-aware optimizer effectively exploits data locality, leading to lower latency and higher throughput. However, the scheme introduces additional complexity in query planning and metadata management. The paper discusses trade-offs between partitioning granularity and maintenance overhead; too many partitions can increase planning time and metadata size, while too few may not yield sufficient pruning. The authors also address limitations: the evaluation is based on a simulated workload, and real-world payment systems may have different access patterns and consistency requirements. Future work includes extending the scheme to handle dynamic workload changes, integrating machine learning for adaptive partitioning, and testing on production-scale deployments. The discussion also compares the proposed approach with related work in distributed query optimization, such as dynamic query routing and adaptive join strategies, highlighting the novelty of combining sharding and partitioning in a unified cost-based framework. Overall, the analysis suggests that the proposed scheme offers a practical and effective solution for optimizing cross-shard queries in payment system databases, with potential for broader application in other distributed data-intensive domains.

Who should read this

CS practitioners and researchers

Opening member content…