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

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.

Authors

Institutions

Publication Details

Journal
International Journal of Sustainability and Innovation in Engineering
Published
2026-11-18
DOI
https://doi.org/10.56830/ijsie202620
Primary Topic
Advanced Database Systems and Queries
Type
article
Field-Weighted Citation Impact
0.00
Controls
|||
ALL TIME
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
article

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

Karan Radadiya
International Journal of Sustainability and Innovation in Engineering
Advanced Database Systems and Queries
article

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

Karan Radadiya
article en

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.

International Journal of Sustainability and Innovation in EngineeringVol. 4(1)
University of the Cumberlands (US)
Advanced Database Systems and Queries
AI Navigator

Ask Laika to Summarize, Analyze, and Connect papers live on the map.

Summarize Papers & Methodologies

Extract key findings, datasets, and comparative methods across publications.

Benchmark Rankings & Visual Analytics

Rank top research institutions, authors, funders, topics, and journals by Field-Weighted Citation Impact (FWCI) and paper volume with instant charts.

Connect Distant Disciplines

Bridge topological clusters on the map to find hidden collaborative intersections.

Laika access coming soon