Elasticsearch Search Engine Integration (PageSpeed Score 29 → 69)

Background & Technical Challenge

As the Beautyhaul content catalog expanded, legacy search and article listing endpoints relied on heavy MySQL SQL LIKE queries across multiple joined tables. Under high concurrent traffic, these queries locked read replicas and degraded the Google PageSpeed Performance score down to 29.

The goal was to shift public article data retrieval entirely from MySQL to Elasticsearch 8, powered by instant SQS + Lambda real-time updates and a 2x daily CLI fail-safe backup.

Technical Solution & Sync Architecture

1. Elasticsearch as Primary Article Reader

• Replaced direct relational database queries with Elasticsearch 8 text search and document retrieval.
• Public web and mobile API endpoints fetch indexed articles with sub-15ms query latency.

2. Real-Time Event Sync + Fail-Safe CLI Backup

• Real-Time Pipeline: Admin CMS article updates dispatch instant AWS SQS messages to AWS Lambda, upserting content to Elasticsearch in real-time.
• Fail-Safe CLI Backup Pipeline (2x Daily):
  • Morning Run: Full article re-sync processing 100 articles per loop.
  • Afternoon Run: Delta article re-sync processing updated articles of the day.
  • Serves as an automated fail-safe backup if SQS/Lambda encounter cloud network blips.

// CLI Fail-Safe Batch Upsert Loop (100 Articles per Iteration)
for {
    // 1. Fetch 100 articles from MySQL using cursor pagination / timestamp filter
    articles, err := fetchArticleBatch(db, lastID, batchSize) // batchSize = 100
    if len(articles) == 0 || err != nil {
        break // Batch complete
    }

    // 2. Perform bulk upsert to Elasticsearch blog index
    err = esClient.BulkUpsert(ctx, "beautyhaul_blog_articles", articles)
    
    // 3. Advance cursor for next batch iteration
    lastID = articles[len(articles)-1].ID
}

Measured Performance Impact

• Google PageSpeed Performance Score: Boosted from 29 to 69 (+40 points gain).
• Search Query Latency: Reduced response time to sub-15ms.
• Database Offloading: Eliminated 85% of text search load from MySQL RDS instances.