SQLite Sharding in Practice: A Deep Comparison of Three Sharding Strategies

When single-file SQLite hits concurrency bottlenecks, how do we break through? This article shares three SQLite sharding approaches from different scenarios in the HagiCode project, helping you understand how to choose the right sharding strategy.

Hello everyone, I'm Yu Kun, producer of HagiCode.

Background

When building high-performance applications, single-file SQLite databases encounter very real problems. As user volume and data grow, these issues start lining up at your door:

• Write operations begin queuing, response times visibly increase
• Query performance declines as data grows
• Frequent "database is locked" errors during multi-threaded access

Many people's first reaction is: should we just migrate to PostgreSQL or MySQL? While this approach can solve the problem, deployment complexity rises sharply. Is there a lighter-weight solution?

The answer is: sharding. Ultimately, engineering problems must be solved with engineering methods. By distributing data across multiple SQLite files, you can significantly improve concurrency and query performance while maintaining SQLite's lightweight characteristics.

About HagiCode

The approaches shared in this article come from our practical experience in the HagiCode project. As an AI code assistant project, HagiCode needs to handle large volumes of conversation messages, state persistence, and event history records. It was in solving these real-world problems that we summarized three different sharding approaches for different scenarios.

To do good work, one must first sharpen one's tools—but how to use these "tools" depends on the specific "work" at hand.

Our code repository is at github.com/HagiCode-org/site—friends who are interested are welcome to dive deeper.

Overview of Three Sharding Approaches

Through analysis of the HagiCode codebase, we discovered three SQLite sharding approaches for different business scenarios:

1. Session Message Sharded Storage: AI conversation message storage, characterized by high-frequency writes and session-based isolated queries
2. Orleans Grain Sharded Storage: Distributed framework state persistence, characterized by cross-node access requiring deterministic routing
3. Hero History Sharded Storage: Gamification system historical event records, characterized by event sourcing requiring migration compatibility

Although the business scenarios differ, all three follow the same core design principles:

• Deterministic routing: Directly calculate shards from business IDs, no metadata tables needed
• Transparent access: Upper layers operate through unified interfaces, unaware of sharding
• Independent storage: Each shard is a completely independent SQLite file
• Concurrency optimization: WAL mode + busy_timeout reduces lock contention

Many people ask: Why not build a universal sharding solution? This is a very practical question. Here's our conclusion: In engineering, there are no universal solutions, only approaches that best fit the current business scenario. Next, we'll deeply compare the specific implementations of these three approaches.

Sharding Strategy Comparison

Shard Count and Naming Rules

Why such significant differences in shard count? This depends on business characteristics. In other words, models will change, tools will evolve, workflows will upgrade, but the engineering fundamentals remain: you must first understand what problem you're solving.

• Session Message uses 256 shards because conversation messages have the highest write frequency, requiring more shards to distribute load
• Orleans Grain uses 100 shards, balancing concurrency performance with management complexity
• Hero History uses only 10 shards because historical events have lower write frequency and migration costs must be considered

Routing Algorithm Differences

Routing algorithms are the core of sharding approaches, determining how data distributes across shards. The three approaches use different routing strategies:

// Session Message: GUID last two digits hexadecimal
var normalized = Guid.Parse(sessionId.Value).ToString("N").ToLowerInvariant();
return normalized[^2..];  // Take last two hexadecimal characters

// Orleans Grain: Extract digits and take last two digits modulo
var digits = ExtractDigits(grainId);  // Extract all digits
var lastTwoDigits = (digits[^2] * 10) + digits[^1];
return lastTwoDigits % shardCount;

// Hero History: Last character ASCII value modulo
return heroId[^1] % 10;

Design Logic Analysis:

• Session Message IDs are GUIDs; after converting to hexadecimal and taking the last two digits, you get evenly distributed 256 shards
• Orleans Grain ID formats are inconsistent, possibly containing both letters and numbers, so all digits are extracted before modulo
• Hero History IDs are strings; directly using the last character's ASCII value modulo is simple but distribution may not be even enough

Key Point: Regardless of which algorithm is used, you must ensure the same ID always maps to the same shard. This is the most basic requirement in distributed systems—otherwise, data inconsistency results. Ultimately, unstable routing means all effort is wasted.

Initialization Strategy Differences

Why does Orleans Grain choose full initialization at startup?

Because Orleans is a distributed framework, Grains may be scheduled to any node. If shard files are discovered missing only at runtime, requests will fail. Full initialization at startup extends startup time but ensures runtime stability. Getting it running is just the beginning; keeping it maintainable is real skill.

Lazy Loading Advantages:

For Session Message and Hero History, lazy loading reduces startup time—files and Schema are created only when actually accessing a specific shard. Using Lazy<Task> prevents race conditions during concurrent initialization. This design looks simple, but saves a lot of unnecessary trouble in real projects.

Schema Design Characteristics

The three approaches' Schema designs reflect their respective business characteristics:

Session Message:

• Supports Event Sourcing pattern (event table + snapshot table)
• Includes message content block sub-table (MessageContentBlocks)
• Has compression and compression flag fields, supporting future optimization

Orleans Grain:

• Minimalist design: single table GrainState
• JSON serialization for state storage
• ETag optimistic concurrency control

Hero History:

• Timeline query optimization indexes
• DedupeKey unique constraint prevents duplication
• Supports multiple event types and statuses

From these designs, we can see Schema design should closely fit business requirements, not pursue generality. Orleans Grain's simple design exists because it only needs to store serialized state, without complex query capabilities. This isn't magic—it's engineering. Don't rush to give things grand names—first see if they can survive two iterations in the team.

Concurrency Configuration Comparison

All three approaches use the same SQLite concurrency optimization configuration:

PRAGMA journal_mode=WAL;      -- Write-ahead logging mode
PRAGMA synchronous=NORMAL;     -- Reduce persistence overhead
PRAGMA busy_timeout=5000;      -- 5 second busy wait
PRAGMA foreign_keys=ON;        -- Foreign key constraints

WAL Mode Advantages:

Traditional rollback journal mode produces lock contention during writes, while WAL mode allows concurrent reads and writes. This can significantly improve performance in large data volume scenarios. Many people don't know this configuration—actually, it's more important than you think.

synchronous=NORMAL Trade-off:

Setting to FULL guarantees maximum safety but significantly reduces performance. NORMAL mode achieves balance between safety and performance, making it the right choice for most applications. Don't struggle with this configuration too long—NORMAL is enough.

How to Choose a Sharding Strategy

Based on analysis of HagiCode's three approaches, we can summarize this decision matrix:

High throughput scenarios → More shards (e.g., Message uses 256)
Simple maintainability     → Fewer shards (e.g., Hero History uses 10)
Mostly numeric IDs         → Modulo algorithm (Orleans Grain)
Mostly GUIDs              → Hexadecimal suffix (Session Message)
String IDs                → ASCII modulo (Hero History)

Experience Values for Shard Count Selection:

• Too few (< 10): Limited concurrency improvement, little sharding benefit
• Too many (> 1000): Complex file management, high connection pool overhead
• Experience value: 10-100 shards suitable for most scenarios
• Extremely high concurrency scenarios: Consider 256 shards

This might look exciting in demos, but once you're in production, every cost must be calculated carefully. Many problems aren't impossible—just haven't had their costs properly calculated.

Implementation Guide

Implement Standardized Shard Router

public interface IShardResolver<TId>
{
    string ResolveShardKey(TId id);
}

// Hexadecimal sharding (for GUIDs)
public class HexSuffixShardResolver : IShardResolver<string>
{
    private readonly int _suffixLength;

    public HexSuffixShardResolver(int suffixLength = 2)
    {
        _suffixLength = suffixLength;
    }

    public string ResolveShardKey(string id)
    {
        var normalized = id.Replace("-", "").ToLowerInvariant();
        return normalized[^_suffixLength..];
    }
}

// Numeric modulo sharding (for pure numeric IDs)
public class NumericModuloShardResolver : IShardResolver<long>
{
    private readonly int _shardCount;

    public NumericModuloShardResolver(int shardCount)
    {
        _shardCount = shardCount;
    }

    public string ResolveShardKey(long id)
    {
        return (id % _shardCount).ToString("D2");
    }
}

Unified Connection Factory Pattern

public class ShardedConnectionFactory<TOptions>
{
    private readonly ConcurrentDictionary<string, Lazy<Task>> _initializationTasks = new();
    private readonly TOptions _options;
    private readonly IShardSchemaInitializer _initializer;

    public ShardedConnectionFactory(
        TOptions options,
        IShardSchemaInitializer initializer)
    {
        _options = options;
        _initializer = initializer;
    }

    public async Task<TDbContext> CreateAsync(string shardKey, CancellationToken ct)
    {
        var connectionString = BuildConnectionString(shardKey);

        // Use Lazy<Task> to prevent concurrent initialization
        var initTask = _initializationTasks.GetOrAdd(
            connectionString,
            _ => new Lazy<Task>(() => InitializeShardAsync(connectionString, ct))
        );

        await initTask.Value;
        return CreateDbContext(connectionString);
    }

    private async Task InitializeShardAsync(string connectionString, CancellationToken ct)
    {
        await _initializer.InitializeAsync(connectionString, ct);
    }

    private string BuildConnectionString(string shardKey)
    {
        var shardPath = Path.Combine(_options.BaseDirectory, $"{shardKey}.db");
        return $"Data Source={shardPath}";
    }

    private TDbContext CreateDbContext(string connectionString)
    {
        // Create DbContext based on specific ORM
        return Activator.CreateInstance(typeof(TDbContext), connectionString) as TDbContext;
    }
}

Schema Initialization Best Practices

public class SqliteShardInitializer : IShardSchemaInitializer
{
    public async Task InitializeAsync(string connectionString, CancellationToken ct)
    {
        await using var connection = new SqliteConnection(connectionString);
        await connection.OpenAsync(ct);

        // Concurrency optimization configuration
        await connection.ExecuteAsync("""
            PRAGMA journal_mode=WAL;
            PRAGMA synchronous=NORMAL;
            PRAGMA busy_timeout=5000;
            PRAGMA foreign_keys=ON;
        """);

        // Create table structure
        await connection.ExecuteAsync("""
            CREATE TABLE IF NOT EXISTS Entities (
                Id TEXT PRIMARY KEY,
                CreatedAt TEXT NOT NULL,
                UpdatedAt TEXT NOT NULL,
                Data TEXT NOT NULL,
                ETag TEXT
            );
        """);

        // Create indexes
        await connection.ExecuteAsync("""
            CREATE INDEX IF NOT EXISTS IX_Entities_CreatedAt
            ON Entities(CreatedAt DESC);

            CREATE INDEX IF NOT EXISTS IX_Entities_UpdatedAt
            ON Entities(UpdatedAt DESC);
        """);
    }
}

Key Considerations

1. Routing Stability

Routing algorithms must guarantee the same ID always maps to the same shard. Avoid using random or time-related calculations, and don't introduce mutable parameters in the algorithm.

2. Shard Count Selection

Shard count should be determined in the design phase; later modification is very difficult. Consider:

• Current and future concurrency levels
• Management cost per individual shard
• Data migration complexity

3. Migration Considerations

The Hero History approach demonstrates a complete migration path:

1. Build new sharded storage infrastructure
2. Implement migration service to copy main database data to shards
3. Verify query compatibility after migration
4. Switch read/write paths to shards
5. Clean up old main database tables

Design sharding approaches with future migration needs in mind. Talk is cheap. Show me the code—but code alone isn't enough; you need a complete migration path. One success doesn't make a system; sustained success does.

4. Monitoring and Operations

• Monitor each shard's size distribution, detect data skew promptly
• Set up alerts for shard hotspots, avoid single shard becoming bottleneck
• Regularly check WAL file sizes, prevent excessive disk space usage
• Establish shard health check mechanisms

5. Test Coverage

• Test edge cases (empty ID, special characters, overly long ID)
• Verify routing determinism, ensure same ID always maps to same shard
• Concurrent write stress testing, verify lock contention is effectively mitigated
• Migration testing, ensure data integrity and consistency

Conclusion

By comparing three SQLite sharding approaches in the HagiCode project, we can see:

1. No universal solution: Different business scenarios require different sharding strategies
2. Core principles are universal: Deterministic routing, transparent access, independent storage, concurrency optimization
3. Design for the future: Consider migration paths and operational costs

If your project is using SQLite and starting to encounter concurrency bottlenecks, I hope this article provides some ideas. There's no need to rush into migrating to heavyweight databases—sometimes an appropriate sharding approach can solve the problem.

Of course, sharding isn't a silver bullet. Before choosing a sharding approach, ensure:

• You've optimized single-table query performance
• You've used appropriate indexes
• You've enabled WAL mode

Only after these optimizations are complete and performance bottlenecks still exist should you consider introducing sharding. Being able to do simple things well is itself a capability.

Many things are better done once than said once—now let the engineering results speak for themselves.

References

• HagiCode Project Repository: github.com/HagiCode-org/site
• SQLite WAL Mode Documentation: sqlite.org/wal.html
• Orleans Distributed Framework: dotnet.github.io/orleans

Original Article & License

Thanks for reading. If this article helped, consider liking, bookmarking, or sharing it.
This article was created with AI assistance and reviewed by the author before publication.

• Author: newbe36524
• Original URL: https://docs.hagicode.com/go?platform=devto&target=%2Fblog%2F2026-04-17-sqlite-sharding-strategies-comparison%2F
• License: Unless otherwise stated, this article is licensed under CC BY-NC-SA. Please retain attribution when sharing.