Conquering the "Four Mountains": Qingdao Yuno's Distributed Database Upgrade Strategy on OceanBase for Pharma Retail

Note: This article is based on a real-world case study shared by Qingdao Yuno Network Information Co., Ltd.

Pharma Retail Membership Services: Delivering Personalized, Precision Care

With the rapid advancement of big data and cloud computing, efficiently managing membership data has become crucial for pharma retail enterprises looking to transform and upgrade. As a pioneer in China’s pharma retail sector, Qingdao Yuno Network Information Co., Ltd. (hereafter “Qingdao Yuno”) has served the industry for 22 years. During its own digital transformation, the company faced significant challenges with traditional databases and ultimately chose to migrate to a distributed database solution.

Our company’s product portfolio consists of three core pillars:

• Enterprise management software, such as ERP systems that help businesses operate efficiently;
• New retail operations tools, including a membership management system (CRM), an order management center (OMS), and private-domain e-commerce malls. These enable full-lifecycle management from public-domain customer acquisition to private-domain conversion;
• Specialized pharma industry solutions, including a medical insurance prescription center, cloud clinic, chronic disease management system, and DTP (Direct-to-Patient) pharmacy services—precisely addressing the unique needs of the pharmaceutical sector.

Together, these three pillars form a comprehensive, professional, and innovative product matrix that delivers personalized, precision services to users.

Alongside the evolving business models in pharma retail, our membership service scenarios have progressed through three distinct stages.

In the 1.0 era, the business model was product-centric. Enterprises focused on product management, category management, and procurement-sales-inventory processes, while ensuring compliance with GSP (Good Supply Practice) regulatory requirements. This was when ERP systems first emerged. The industry then gradually transitioned to the 2.0 era.

The 2.0 era was store-centric. Enterprises no longer focused solely on products themselves. Since the primary service scenario shifted to physical pharmacies, businesses needed to attract members to visit stores. This stage emphasized store location strategy and service quality optimization—the defining features of the 2.0 era.

Today, the pharma industry has entered the 3.0 era, characterized by a user-centric approach. Although outsiders may not immediately perceive it, the pharma industry is undergoing profound change. In recent years, medical insurance policy adjustments and broader economic pressures have created significant challenges: sales growth has slowed, and many pharmacies have seen declines in revenue, average transaction value, and foot traffic.

Against this backdrop, enterprises are placing greater emphasis on membership services and committed to delivering refined operations. This shift has given rise to an omnichannel retail model that requires businesses to continuously optimize the member experience to keep pace with market changes.

In the 1.0 and 2.0 eras, services centered primarily on offline pharmacies. In the 3.0 era, service touchpoints have expanded significantly across both time and space. Beyond in-store interactions, we now serve members through online channels including B2C platforms, O2O platforms, e-commerce sites, and company-built private-domain malls. Previously, retail interactions were limited to in-store visits. In the 3.0 era, post-visit service has become equally important and is now key to competitive differentiation and finding new growth opportunities.

In the 3.0 era, our ability to provide post-visit service depends on detailed member profiles. These profiles capture all key information affecting a user’s health and medication, including age, gender, current medications, allergies, chronic disease status and type, and current health metrics for chronic disease patients.

By analyzing member profiles and personas, we can deliver real-time medication guidance and reminders at specific moments after members leave the store.

For example, when a chronic disease patient’s health metrics show abnormalities, the system automatically triggers a follow-up task for timely intervention and guidance. For members needing prescription refills, we send reminders when their medication is running low. This way, members continue to receive professional care even after leaving the store.

Beyond professional services, we integrate marketing strategies such as coupons—ensuring treatment effectiveness while providing tangible savings. Through continuous data accumulation, this new retail model has created a comprehensive member tagging and persona system, enabling truly personalized and precision service.

The member tagging and persona system allows us to understand members deeply and deliver refined service. We capture data across every dimension—offline purchases, online browsing behavior, health articles read, official account posts viewed—and analyze it all to build comprehensive member profiles. This includes common disease tags, chronic disease tags, user behavior tags, subjective notes from staff interactions, member activity levels, and value contribution models.

With this data foundation, we deliver precise service both online and offline. Online, this means precision marketing and targeted services. Offline, it means recommending complementary medications based on each member’s health status and current prescriptions. This is the core of our membership service model. As data has accumulated, both its precision and volume have grown dramatically: tens of millions of member records, hundreds of millions of transaction records, and tens of millions of user behavior records. However, querying and utilizing such massive datasets has introduced significant database challenges.

The “Four Mountains” Facing Databases in Pharma Retail

In pharma retail membership scenarios, databases face four major challenges that are common across the industry:

• Performance: In complex user filtering scenarios, queries can become slow or even fail to complete, directly impacting data processing efficiency and user experience.
• Efficiency with large tables: Structural changes or data migrations on large MySQL tables during business updates are complex and time-consuming, increasing maintenance costs and workload.
• Storage cost: As data volumes grow, storage requirements and costs increase continuously. We must control costs while ensuring data integrity.
• Data timeliness: Modern enterprises demand rapid data processing. After business changes, teams need to quickly see data effects to adjust operational decisions. Ensuring data timeliness and availability is a critical database management challenge.

Currently, we face three core database problems:

Problem 1: Inflexible Database Architecture Limiting SaaS Applications

MySQL’s rigid architecture limits its flexibility, making it difficult to adapt to the varying needs of enterprise and SMB SaaS services. Our SME clients vary greatly in scale—from dozens to hundreds of stores. The current architecture struggles with resource allocation, leading to frequent tenant data conflicts that seriously impact SaaS application stability and reliability.

Problem 2: Rapidly Growing Data Volumes and Insufficient Query Performance

Although we’ve built a member tagging and persona system to drive refined service, accurately filtering target groups from massive member datasets remains a major challenge. We need to identify members requiring medication tracking, users needing refill reminders, and high-value customers requiring real-time follow-up. We also need to push relevant health knowledge to the right members. Achieving this requires combining multi-dimensional member data: attributes, tags, purchase history, and product information.

Poor query performance has become a major bottleneck. During an October visit to a well-known pharma client, they set a clear requirement: all queries, especially complex queries and report generation, must complete within 5 seconds. This high standard poses a significant challenge and underscores the need for an efficient database.

Another bottleneck is limited historical data coverage. Effective member analysis typically requires at least two years of transaction data, ideally three. However, traditional database performance constraints forced us to compromise. One client with massive data volumes experienced severely degraded query performance, so we could only provide one year of data—greatly limiting analysis depth. This problem becomes even more pronounced in complex scenarios.

Problem 3: High Storage Costs from Growing Data Volumes

As a SaaS provider with a growing customer base, we face significant data retention challenges. Long-term subscribers—some using our platform for four or five years—require us to retain their data for industry trend analysis, year-over-year comparisons, and other in-depth analytics. As business data grows continuously, storage costs escalate, becoming one of our most pressing issues.

To address these challenges, we actively explored solutions. Database selection emerged as a key consideration—we hoped to reduce storage costs through better database technology while ensuring efficient data management and analytics.

Database Selection Criteria: Five Key Requirements

We established five core requirements for our new database:

• Flexibility: Ensure proper tenant isolation in SaaS applications, preventing interference between customers.
• Stability: All SaaS applications must run reliably to continuously meet user needs.
• Fast response: Queries over large datasets must complete quickly for a smooth user experience.
• Low cost: The database must be cost-effective to improve overall economics.
• Unified management: Support unified operations to simplify management workflows.

Together, these requirements formed our comprehensive evaluation criteria.

In March 2023, we first encountered OceanBase at a technical conference. Between May and June that year, we conducted detailed validation of OceanBase’s features and performance. OceanBase’s full MySQL compatibility eliminated many of our technical concerns. By September and October, we began using OceanBase in specific scenarios. Today, more and more of our business runs on OceanBase, with its adoption scope continuing to expand.

During the initial migration from MySQL to OceanBase, we encountered some challenges. Thanks to strong support from the OceanBase team, all issues were effectively resolved.

The main problems fell into three categories: compatibility, configuration, and performance optimization.

• Compatibility: OceanBase didn’t support JDBC version 24 at the time, so we downgraded to version 17.
• Optimizer precision: OceanBase’s row-store/column-store optimizer wasn’t always accurate in automatic mode. For complex queries—such as reports involving multi-table joins, multiple subqueries, and complex grouping conditions—we needed to manually analyze query patterns and choose the appropriate storage mode based on primary query columns and operation types.
• Configuration: When enabling column-store mode, certain parallel optimization features required manual activation. We resolved this by setting set global parallel_degree_policy = 'AUTO';.

OceanBase Results: Flexible Architecture, Dramatically Improved Performance

After using OceanBase for over a year, did it effectively solve our three core problems?

• Problem 1: Inflexible database architecture limiting SaaS applications
• Problem 2: Rapidly growing data volumes and insufficient query performance
• Problem 3: High storage costs from growing data volumes

The answer is yes. Here’s how we solved each challenge:

1. Flexible Architecture Supporting Stable SaaS Operations for 1+ Years

OceanBase uses a three-node cluster architecture with multiple Zones. Each Zone contains multiple service nodes, and each node has dedicated resource control units (Units). This architecture enables flexible resource allocation.

We allocate fewer resources for small customers and more for medium and large enterprises. Resources are isolated between tenants, and we can precisely adjust allocations based on customer scale. Large customers receive dedicated resources, while SMBs can opt for shared resources to reduce costs.

2. Column Store Delivers 20x+ Query Performance in Complex Member Filtering

For precise member data filtering, consider a multi-organization enterprise client. Our filtering dimensions start with organizational hierarchy, with members under each organization. For each member, we filter their consumption within specific time windows—including purchase frequency, spending amount, and product types—while excluding members who recently received certain services.

We compared MySQL and OceanBase performance for this use case. MySQL took approximately 18 seconds to query this data, while OceanBase completed it in 0.7 seconds—nearly a 20x improvement in query speed.

3. 60% Storage Compression Ratio with Significant Cost Savings

One of our marketing cloud flow-tracking clients uses OceanBase for data storage. Tracking product flows generates enormous data volumes. This client uses OceanBase for large-scale storage alongside a PostgreSQL system, achieving significant space and cost savings. Under identical data volumes, OceanBase achieved a 60% compression ratio.

Looking Ahead: Expanding OceanBase Across More Product Lines

We have high expectations for OceanBase and have outlined four key areas for future development:

First, we currently use OceanBase primarily for reporting and complex queries. We plan to expand its use into core business workflows—such as marketing campaign planning, execution, and follow-up task assignment—to fully leverage its data processing and analytics capabilities.

Second, as we apply OceanBase to real business scenarios, we look forward to more collaboration opportunities with the OceanBase development team to jointly explore and optimize system performance. We also hope OceanBase will continue providing strong technical support.

Third, we will actively recommend OceanBase to more enterprise clients. Yangtze River Pharmaceutical, Xiamen Luyan Pharmacy, Chongqing Pharmaceutical Group, and numerous Yuno SaaS customers already use OceanBase with excellent results. We hope to bring more users into the OceanBase ecosystem to experience its outstanding performance and stability.

Fourth, beyond membership services, we plan to deploy OceanBase across more product lines in 2025, including the OMS order processing system and operations diagnostic system, enabling more comprehensive data management and analytics.