Long-running data acquisition needs explicit progress, repeatable writes, and recovery paths across requests, workers, and storage.
This article documents the architecture and execution of a production-grade data acquisition and ingestion system that processed:
- 30M+ menu records
- 352K business listings
- 2.4M menu item categories
- ~70GB of structured data
- Multi-day continuous execution
- About 2% initial failure rate, handled through separate retry pipelines
The figures describe this project’s scope and execution. The failure results distinguish initial acquisition attempts from later retry passes; they are not a benchmark for other workloads.
The architecture prioritized control, observability, and reliability.
Design Philosophy
From day one, the system was designed around three principles:
- Predictable behavior beats raw speed
- Failed work must remain inspectable and retryable
- Data correctness matters more than volume
At this scale, any weak assumption eventually breaks.
High-Level Architecture
The system consisted of four tightly coordinated layers:
- Frontend execution layer (JavaScript)
- Request/session & networking layer
- Processing & normalization layer
- Persistence & indexing layer (MySQL)
Each layer was isolated to prevent cascading failure while remaining fully observable.
Frontend Execution Layer (Raw JavaScript)
One of the most critical components was the frontend execution logic.
Instead of browser automation frameworks, I used raw JavaScript execution, injected and executed via controlled code snippets.
Execution characteristics:
- Pure JavaScript (no frameworks)
- Batch-based execution
- Recursive resolution until completion
- Explicit state tracking
- Zero reliance on UI rendering
Why this mattered:
- Minimal overhead
- Fine-grained control over execution
- Faster iteration
- Easier recovery on partial failures
Each batch processed a known subset of tasks and recursively continued until all targets were resolved.
This ensured:
- Visibility into unresolved tasks
- Progress tracked through explicit state
- Restarts from recorded checkpoints
Distributed Network & Proxy Layer
Network reliability was handled through a distributed proxy architecture.
Infrastructure setup:
- 20+ lightweight VPS instances (DigitalOcean)
- Each VPS acted as a controlled outbound proxy
- Health checks disabled unstable nodes
- Traffic distribution balanced across workers
This architecture provided:
- Predictable network behavior
- Isolation of network failures
- Stable long-running execution
- Easy replacement of unhealthy nodes
The system favored many small, disposable nodes over complex centralized infrastructure.
Concurrency Model & Thread Coordination
The core execution ran with:
- 12 concurrent worker threads
- Shared task queues
- Centralized state tracking
- Coordinated lifecycle management
The real challenge:
Every worker thread was:
- Actively reading tasks
- Processing complex relational data
- Writing to the same MySQL database
This required:
- Strict transaction boundaries
- Idempotent writes
- Collision-safe schema design
- Careful index strategy
Concurrency was tuned to avoid:
- Write contention
- Long-held locks
- Memory exhaustion
- Deadlocks
With InnoDB, the concern is lock contention and deadlocks between concurrent transactions. InnoDB does not need lock escalation; its transaction model uses row-level locking and consistent reads.
Native Session Handling
Workers reused session state where the acquisition workflow required it.
Session strategy:
- Sessions persisted per worker lifecycle
- Automatic invalidation on error signals
- Controlled recreation logic
- Scoped reuse across deep navigation paths
Benefits:
- Reduced repeated negotiation overhead
- Improved request consistency
- Lower failure rates
- Faster overall throughput
Stateless execution was deliberately avoided where it harmed efficiency.
Data Modeling & Schema Normalization
The dataset was deeply relational and required strict normalization.
Core entities:
- Business listings
- Menus
- Menu categories
- Menu items
- Add-ons
- Variants
- Pricing rules
- Metadata
- Geospatial attributes
Each entity was:
- Modeled independently
- Assigned stable identifiers
- Linked via foreign keys
- Designed for incremental processing
This enabled:
- Deduplication
- Partial reprocessing
- Safe retries
- Long-term maintainability
Geolocation & Spatial Indexing
Location accuracy was a first-class requirement.
Geospatial handling:
- Latitude & longitude normalized per listing
- Precision validation during ingestion
- Spatial indexes applied at the database level
- Optimized queries for radius-based search
This allowed:
- Accurate geographic filtering
- Fast proximity queries
- Scalable location-based operations
Geolocation was engineered as infrastructure, not metadata.
High-Throughput Persistence Layer (MySQL + Raw PHP)
At this scale, the write path determines success or failure.
Persistence strategy:
- Raw PHP write layer
- No heavy ORM abstractions
- Batch inserts where possible
- Deferred index creation
- Controlled transaction scopes
- Streaming data writes to limit memory usage
Why raw PHP:
- Explicit control over batches and transaction scopes
- Direct visibility into write behavior
- Control over memory use during long runs
The explicit write path made batch behavior, transaction scope, and memory usage easier to inspect and tune.
Failure Detection, Retry & Recovery
Failures were treated as normal operating conditions.
Recovery strategy:
- Every request outcome logged
- Failed tasks persisted separately
- Independent retry pipelines
- Multiple retry passes supported
- Idempotent writes ensured safe reprocessing
Results:
- Initial failure rate: ~2%
- Post-retry unresolved failures: near zero
The system could be:
- Stopped
- Restarted
- Partially rerun using recorded progress and idempotent write paths to avoid deliberately reapplying completed work.
Observability & Control
Visibility was essential for multi-day execution.
Observability features:
- Structured logging
- Per-thread metrics
- Progress checkpoints
- Failure categorization
- Throughput monitoring
Logs and checkpoints supported investigation and intervention during long runs.
Why This Architecture Worked
This system succeeded because it prioritized:
- Engineering discipline
- Controlled complexity
- Explicit state management
- Reliable persistence
- Recovery-first thinking
No single component was extraordinary — the composition was.
Final Results
- 70GB+ of structured data
- Millions of normalized records
- Accurate geospatial indexing
- Multi-day uninterrupted execution
- Recoverable, restart-safe pipeline
The useful result was a pipeline whose progress, failed work, and write behavior could be inspected and managed during long runs.
Why This Matters for Real Businesses
Businesses face similar challenges when:
- Aggregating external data
- Processing large datasets
- Running long batch jobs
- Scaling ingestion pipelines
- Maintaining data integrity under load
This project demonstrates how engineering-first thinking enables scale — without unnecessary infrastructure or fragile shortcuts.
Put the ideas into practice.
Work through your development process, architecture, or next engineering challenge with me.
Explore one-to-one mentorship