Deep Dive into Database Sharding Strategies

21. Proper Indexing

    • Use Clustered, Non-Clustered, and Covering Indexes.

    • Example: Searching CustomerName on 10M records → index reduces query from 20 sec → 200 ms.

22. Partitioning Large Tables

    • Split data into smaller logical chunks.

    • Example: Transactions table partitioned by year/month.

23. Sharding & Horizontal Scaling

    • Distribute records across multiple databases.

    • Example: Users with ID ranges split across multiple DBs.

24. Read/Write Replicas

    • Read-heavy queries go to replicas, write operations to primary.

25. *Query Optimization (Avoid SELECT )

    • Fetch only required columns.

    • Example: Instead of SELECT * FROM Orders, use SELECT OrderID, Amount.

26. Stored Procedures & Execution Plans

    • Use compiled procedures to avoid re-parsing queries.

27. Batch Processing for Large Updates

    • Update/delete records in chunks of 1000 instead of all at once.

28. Caching Frequently Accessed Data

    • Store hot data in Redis/Memory cache.

    • Example: Exchange rates cached for 10 mins.