Sharding for speed, merging for cost: Mixpanel's dynamic file strategy

At Mixpanel, we serve customers with vastly different data profiles. Some send us thousands of events per second, while others generate hundreds of events per hour. Traffic patterns vary too—some customers experience daily peaks, others see monthly surges. Despite this diversity, all events are stored in our proprietary ARB format, organized into files.

To optimize query performance, we partition events using a two-dimensional strategy: by user_id (sharding) and by time (daily files). This creates one file per shard per day.

Why daily partitioning? Customer data accumulates continuously. Without time-based separation, files would grow unbounded, eventually overwhelming our system’s ability to load and analyze them. More importantly, customers typically query data within specific time windows—the last 7 days, a particular month, or today’s activity. Daily partitioning lets us scan only the relevant files for each query rather than processing the entire dataset.

Why shard by user_id? Well-distributed sharding is critical for query speed. By spreading user data across multiple shards, we parallelize query processing and prevent any single file from becoming a bottleneck. When customers experience unexpected traffic spikes, we “upshard” their data, redistributing it across additional shards to maintain performance.

This raises an obvious question: if more shards improve speed, why not give every customer maximum sharding from the start?

The answer lies in how our system processes queries. Since files are the fundamental unit of processing, file size consistency matters more than sheer shard count. Every query and background process operates at the file level, meaning each read incurs computational cost, and each write operation (deletion plus new file creation) adds overhead.

This creates a natural tension: we want enough shards to keep queries fast, but not so many that we create excessive small files. The ideal configuration minimizes the number of files while maintaining sufficient data distribution for performance—a balance that varies significantly across our customer base.

Since each file represents one shard for one day, we have two levers to pull: adjust the number of shards, or change the time window each file represents.

Why Fewer Shards Isn’t the Answer

For customers with consistently low data volumes, having fewer shards seems logical. However, this approach has significant drawbacks:

• Upsharding is expensive: If a customer’s volume grows over time, we’d need to upshard them—an extremely costly operation. This forces us to provision substantial buffer capacity in our initial sharding strategy. Additionally, during the period before upsharding is completed, customers suffer from degraded query performance due to insufficient shard distribution.

• Downsharding is expensive: If a customer’s volume decreases or we discover we’ve over-provisioned their shards, we’d need to reduceing shard counts—an operation just as costly as upsharding.

• Changing data volume over time: When a customer has periodic volume spikes—say, one surge per month that justifies their shard count—we’re locked into maintaining those shards even though they’re underutilized most of the time. Downsharding would cause performance issues during their monthly peaks.

Multi-Day Files: A Better Alternative

Instead of changing shard count, we can merge multiple days into a single file. This approach reduces the total number of files per shard while preserving the sharding strategy that makes queries fast. The result: we maintain query performance through proper data distribution, but now each file contains more data, improving the efficiency of file-level operations without the risks and costs of dynamic resharding.

With thousands of customers and constantly shifting traffic patterns, manually configuring date ranges for each project wasn’t feasible. Manual management would create operational overhead, increase the risk of human error, and require us to limit configuration options to keep the system maintainable.

Instead, we developed an automated system that dynamically determines optimal date ranges for multi-day files. The algorithm runs periodically, analyzing file sizes across each day within a project’s shard. When it detects files below a defined threshold, it consolidates adjacent date ranges in that shard until files reach an ideal size.

This approach immediately surfaced a new problem: backfilling historical data. When customers load significant amounts of past data, files spanning wide date ranges become challenging to update efficiently. We needed the algorithm to work in both directions—not only merging date ranges when appropriate, but also splitting them when files exceed capacity.

We also extended our multi-day algorithm to proactively reduce file counts for recent days with incoming data. The algorithm analyzes the past 90 days—excluding the most recent week where data is still arriving—and identifies the shortest multi-day range in use. This range then becomes the template for projecting future file date ranges. Since newer days continue receiving data, we disable interval merging for the most recent week while still allowing file splits to accommodate incoming volume.

The result is an adaptive system that maintains optimal file sizes across diverse traffic patterns. Customers with weekly or monthly traffic peaks benefit from fewer files without sacrificing query speed. As data volumes fluctuate, the system responds automatically: consolidating files during low-traffic periods and partitioning them when growth demands it.

By transitioning from single-day to multi-day files, we achieved an ~88% reduction in total file count. This dramatic decrease substantially lowered the costs associated with file-level operations—both reads and writes—while maintaining query performance. Rethinking our fundamental storage unit proved to be a powerful lever for improving both system efficiency and cost effectiveness at scale.