Architecting a Free, Highly Available Database for 25M+ Records: An Open-Source Blueprint using MySQL Galera, Typesense, and RabbitMQ

Scaling enterprise-grade platforms to handle tens of millions of records usually leads to massive monthly bills. Relying on proprietary cloud infrastructure—like AWS Aurora Multi-Master, managed Elasticsearch, or Algolia—means scaling your costs linearly with your data volume. At 25 million records, license and API costs alone can easily exceed thousands of dollars per month.

This challenge is not unique to e-commerce product catalogs. Whether you are building a B2B SaaS multi-tenant directory, a real-time IoT device registry, a high-throughput content management system, or a massive asset tracking platform, you will inevitably hit the limits of a single-node relational database.

Fortunately, there is a better way. By leveraging fully free, open-source software (FOSS) and standard virtual machines, you can build a highly available, write-optimized database tier paired with a sub-50ms search engine.

In this deep dive, we explore the exact architectural blueprint to achieve this using MySQL Galera Cluster for multi-master storage, Typesense for fast fuzzy search, and RabbitMQ for reliable sync queuing.

1. The Bottleneck: Scaling Ingestion Without High License Fees

Ingesting millions of updates, state modifications, or scraper inputs per day creates two conflicting resource demands:

1. High-Frequency Writes: Continuous database insertions that degrade traditional indexes and lock database tables.
2. Search Queries: User and API search queries requiring typo-tolerance, full-text matches, and complex filtering over the same tables.

If you attempt this on a single server, performance collapses. If you scale using proprietary cloud features, your billing explodes.

To solve this, we split the architecture into a Write-Optimized Storage Tier and a Read-Optimized Search Tier using 100% free, self-hosted software.

2. The Database Tier: Zero-Cost Multi-Master Clustering with Galera

To handle thousands of concurrent write operations without database locking or expensive cloud hosting, we configure a MySQL Galera Cluster.

Unlike standard master-slave setups where a single write node remains a single point of failure, Galera provides a synchronous, multi-master replication model. Every node in the cluster can accept write operations. This is ideal for distributed IoT data collectors, B2B multi-tenant applications, or high-volume scrapers feeding a central repository.

To guarantee partition tolerance and avoid split-brain scenarios, a minimum of 3 nodes is required to maintain a cluster quorum.

Core Cluster Configuration (my.cnf)

Below is a production-grade configuration pattern for setting up your Galera nodes:

ini[mysqld]
binlog_format=ROW
default-storage-engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_doublewrite=1

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="catalog_cluster"
wsrep_cluster_address="gcomm://10.0.0.1,10.0.0.2,10.0.0.3"
wsrep_sst_method=mariabackup

# Sync parameters
wsrep_provider_options="gcache.size=2G; evs.keepalive_period=PT3S"

Using a load balancer like ProxySQL (which is also free and open-source), we distribute incoming write queries across all active database nodes. If one node fails, ProxySQL immediately redirects traffic with zero downtime.

For more details on clustering mechanisms, refer to the Galera Cluster Documentation.

3. The Search Tier: High-Performance Search with Typesense (FOSS)

Using relational databases to search through millions of rows of text is highly inefficient. Instead, we offload this workload to Typesense, a free, open-source, in-memory search engine written in C++.

At 25M+ records, managed search APIs charge excessive monthly volume fees. Self-hosting a Typesense cluster on standard instances delivers identical sub-50ms query speeds for only the cost of the raw server memory, regardless of whether you are querying e-commerce items, SaaS users, content documents, or IoT device IDs.

Schema Definition & Querying in Typesense

First, define the search schema:

javascriptconst schema = {
  name: 'catalog_items',
  fields: [
    { name: 'title', type: 'string' },
    { name: 'description', type: 'string' },
    { name: 'category', type: 'string', facet: true },
    { name: 'is_active', type: 'bool', facet: true },
    { name: 'popularity', type: 'int32' }
  ],
  default_sorting_field: 'popularity'
};

When a user searches the index, the frontend queries Typesense directly:

javascript// Perform a typo-tolerant, filtered search
const results = await typesenseClient.collections('catalog_items').documents().search({
  q: 'sensor controller',
  query_by: 'title, description',
  filter_by: 'category: = electronics && is_active: true',
  sort_by: 'popularity:desc',
  per_page: 20
});

To learn more about advanced indexing strategies, visit the Typesense Documentation.

4. The Sync Engine: Building a Reliable Event-Driven Queue

Because the data is split between Galera (source of truth) and Typesense (read engine), we need a reliable sync engine. We achieve this using a queue architecture built on RabbitMQ and Celery.

+---------------+     Write     +------------------------+
| Ingestion App | ------------> | MySQL Galera Cluster   |
+---------------+               +------------------------+
        |                                   
        | Publish Event                     
        v                                   
+---------------+                       
|   RabbitMQ    |                       
+---------------+                       
        |                                   
        | Consume Task                      
        v                                   
+---------------+     Upsert    +------------------------+
| Celery Worker | ------------> | Typesense Search Index |
+---------------+               +------------------------+

Reliable Ingestion Mechanics

1. Publisher Confirms & Durability: When the application writes an update to MySQL, it publishes a message to RabbitMQ with delivery_mode=2 (persistent message storage). This guarantees the message is saved to disk even if the message broker restarts.
2. Dead-Letter Exchanges (DLX): If a worker fails to process an update (e.g., due to a temporary network timeout), the message is routed to a Dead-Letter Queue instead of being lost, enabling automated retries.
3. Idempotency & Race Condition Protection: Because network messages can arrive out-of-order, every ingestion update includes a database version or timestamp. When the Celery worker attempts to upsert to Typesense, it checks that the incoming message version is greater than the version currently stored in the search index.

python# Idempotent Celery task processing updates safely
@app.task(bind=True, max_retries=3)
def sync_record_to_typesense(self, record_id, updated_data, version_timestamp):
    try:
        # Check existing version in Typesense to prevent out-of-order writes
        existing = typesense.collections('catalog_items').documents(record_id).retrieve()
        if existing and existing.get('version') >= version_timestamp:
            return # Ignore stale out-of-order message
            
        # Perform the upsert
        typesense.collections('inventory').documents().upsert({
            'id' : record_id,
            'version': version_timestamp,
            **updated_data
        })
    except Exception as exc:
        raise self.retry(exc=exc, countdown=5)

For more details on building resilient messaging systems, refer to the RabbitMQ Reliability Guide.

5. Frequently Asked Questions

Why not use Elasticsearch?

Elasticsearch is a powerful open-source search engine, but it is resource-intensive. It requires significant JVM memory configuration and high-end hardware, which increases your overall infrastructure costs. Typesense is a lightweight alternative written in C++ that uses CPU and RAM much more efficiently, making it far cheaper to host.

How does Galera protect against data loss if a node crashes?

Galera uses synchronous replication, meaning a write transaction is only completed when all active nodes in the cluster confirm they have received and written the transaction. If one node goes offline, the remaining nodes continue serving traffic instantly without losing any data.

Can this setup scale for SaaS log analytics or IoT telemetry?

Yes. Galera can scale to handle millions of writes, and Typesense is heavily optimized for search speeds across tens of millions of records. However, for timeseries telemetry that requires massive analytical aggregations, a dedicated open-source timeseries database (like TimescaleDB or ClickHouse) is often a better complement to this stack.

6. Strategic Takeaways

You do not need to pay expensive licensing fees or commit to proprietary cloud lock-in to scale a large application.

• FOSS Scales Effectively: Using free software like Galera, Typesense, and RabbitMQ on standard virtual machines gives you full control over your infrastructure with zero licensing fees.
• Workload Decoupling: Decoupling write operations from read/search operations prevents database bottlenecks and increases overall platform reliability.
• Message Durability: Protect your data sync pipeline from failures by configuring message persistence and idempotent task execution.

By using this open-source blueprint, you can scale to 25 million records and beyond while keeping your monthly hosting costs minimal.