Work

AI-Driven Database Sharding Strategies for Nationwide Prescription Drug Monitoring Programs

Year: 2026
Type: article
Abstract: Prescription Drug Monitoring Programs (PDMPs) are databases of prescription information that are used to assist in the monitoring of controlled substances. As more data accumulates and cross-shard queries increase, traditional database architecture can easily lead to an imbalanced workload, increased cross-shard queries, and delayed responses to jurisdictional needs. The objective of the study is to share a national PDMP data management system based on artificial intelligence (AI). It has a hierarchical partitioning structure, with geographic jurisdiction as the lowest (first) level in the hierarchy, and the patient identifier is hashed within each jurisdiction. Historical retrieval is supported by temporal indexing, and risk classifications are metadata rather than shards. Short-term (Days) workload predictions are made using query activity and resource metrics with a regression model named XGBoost. The forecasts determine where hotspots will be found and where they should be placed. Access was governed through jurisdiction-aware query routing, role- and attribute-based access controls, and audit logging. Rebalancing is performed with hysteresis and migration costs to minimise data movement during rebalancing. The methodology is based on a comparative evaluation using synthetically generated PDMP-like data in a PostgreSQL-Citus environment. Static and adaptive configurations are evaluated based on query latency, query throughput, shard-load distribution, forecasting error, hotspot detection and rebalancing overhead. The framework offers methods to handle PDMP data at scale. No quantitative improvement in performance or clinical outcome is claimed, as there are no operational measurements. More testing with workloads and approved operational data is required before deployment.
Language: English
Open Access status: bronze
OpenAlex ID: W7236641093
FWCI:
Cites: 23
Cited by:
Related to: 10