Skip to content
Why Adding Indexes Fails at Scale: Relational Big Data Architecture Patterns
Databases 9 min read

Why Adding Indexes Fails at Scale: Relational Big Data Architecture Patterns

This chapter from 'Architecture Fundamentals' details relational big data architecture patterns: sharding strategies, query optimization avoiding unbounded scans, async import/export with streaming I/O, idempotency, and a bottleneck-first evolution checklist that prioritizes product-level constraints over premature scaling.

Cilantro Loves Linux
Cilantro Loves Linux
Cilantro Loves Linux

Core Overview

Facing slow queries and large file processing, simply adding indexes or increasing threads only temporarily alleviates symptoms. This chapter focuses on massive queries, imports, and exports in relational databases. The core idea is to break large tasks into controllable small tasks via pagination, sharding, batch I/O, and parallel processing to reduce per-operation resource usage, and use async tasks so users don't wait on long connections.

Big data handling isn't about blindly adding threads. Architects must first optimize product query scope and data model, then SQL and indexes, and only then consider parallel and distributed approaches. Otherwise more threads just push pressure faster toward database, disk, or network bottlenecks.

Key Knowledge Breakdown

10.1 Sharding Processing Mindset

Massive tasks can be split into data shards, each processed by an independent execution unit, with results aggregated afterward. The sharding key must stably partition ranges, avoid overlaps and gaps, and keep data evenly distributed. The process must record total count, completed count, failure count, and cursors to support pause, resume, and retry.

Parallelism depends on the slowest resource. CPU-intensive work, database reads, network transfer, and file writes need different strategies. Measure to find the throughput inflection point rather than treating thread count as an unlimited performance knob.

10.2 Big Data Queries

The book distinguishes two optimization directions: front-to-back (first restrict product query scope, then design storage structures and algorithms — fundamental but larger change) and back-to-front (start with SQL, indexes, database parameters — quick wins but may hit a ceiling).

Query design should avoid unbounded conditions, deep pagination, cross-shard sorting, and online large aggregations. Solutions include explicit time ranges, business primary key queries, cursor pagination, precomputation, archiving, and read models. Always examine execution plans and real data distribution before optimizing.

10.3 Big Data Imports

Import covers file reading, unit validation, business association validation, and batch insertion. The full flow can run asynchronously, tracking progress and errors by row number or shard. Repeated imports are controlled via task identifiers, business unique keys, and idempotency rules.

Performance optimization focuses on streaming reads, batch validation, preloading dictionaries or mappings, batch writes, and reasonable parallelism. If each unit triggers a remote database query, growing data volume creates a classic N+1 bottleneck.

10.4 Big Data Exports

Export should use async tasks, paginated or cursor-based reads, and streaming file writes to avoid loading all data into memory. Simple formats like TXT and CSV suit massive data better than complex styled tables; huge results can be split into multiple files, compressed, and given download expiry and access permissions.

If stable ordering is required, sorting semantics must be preserved across sharding, merging, and writing. Export is also a high-risk data leakage entry point: restrict scope, audit operations, and mask sensitive fields.

Architecture Diagrams

The first diagram illustrates an async data task platform; the second shows the optimization sequence starting from product and data model.

Mermaid diagram
Mermaid diagram
Mermaid diagram
Mermaid diagram

Personal Reflections

The term "relational big data" reflects the book's era; today more options exist for analytical queries. Yet separating query, import, and export remains valuable because their bottlenecks and failure modes differ — they cannot be lumped as "database can't handle it."

R&D platform data accumulates over time from pipelines, tests, artifacts, releases, and audit logs. A key reminder: part of the performance problem is decided at the product rule stage. Allowing cross-project full-history queries with arbitrary sorting and synchronous export lets a single request dictate the resource ceiling.

I won't jump to sharding just because data grows. First confirm whether the slowness is in query, storage, file generation, or network transfer, then decide on indexes, read models, archiving, async tasks, or sharding. Skipping measurement and picking an architecture upfront trades a clear problem for long-term complexity.

Practical Implementation Checklist

Define max file size, max row count, max query range, and timeout policies.

Make tasks async; provide progress, error details, cancellation, and result download.

Choose a stable sharding key; record cursors, shard status, and retry counts.

Use streaming I/O and batch database operations; cap memory usage.

Preload reusable validation data to avoid per-row remote queries.

Imports use unique keys and idempotency rules; exports enforce permissions, masking, and audit.

Measure database, CPU, memory, disk, and network bottlenecks with real data distribution.

Evolution Strategy

First restrict unbounded requests and optimize indexes, then introduce async tasks. Only after single-machine batch processing hits a clear bottleneck should sharding and parallelism be added. If query and transactional models conflict sharply, build a dedicated read model or archive database instead of piling complex queries onto the core transactional database.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

Shardingquery optimizationbig data architecturerelational databasedata importdata exportasync processingbottleneck analysis
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.