Apache Pinot's Flexible Storage Architecture

Apache Pinot is a real-time OLAP database optimized for low-latency queries on large-scale data. Its storage architecture supports three deployment models:

1. Coupled Mode: Storage is directly attached to compute nodes, providing maximum performance for all queries (typically 10-50ms) but at higher cost for data retention, as you need to provision storage capacity based on your total dataset size.
2. Deep Storage Mode: All data resides in object stores like Amazon S3, reducing storage costs dramatically (up to 70-80% compared to local storage). While traditional systems might suffer significant performance degradation when accessing S3 data, Pinot's intelligent indexing still delivers queries in the 100-500ms range, transferring only ~25KB instead of entire multi-GB segments.
3. Tiered Storage (Hybrid Mode) : The best of both worlds - frequently accessed "hot" data remains on local storage for maximum performance (10-50ms queries), while "cold" data moves to cost-effective S3 while still maintaining good performance (100-500ms range). This model optimizes both cost and performance, offering 50-70% storage cost reduction with minimal latency impact for most analytical workloads.

Pick the model that matches your latency budget and cost constraints.

Pinot's Indexing Strategy

Multiple index types minimize the data fetched from S3, reducing bandwidth costs and keeping queries fast. These four indexes are central to S3 tiered storage optimization:

Dictionary Index

This index maps string values to compact numeric IDs (e.g., "safari" → 1, "chrome" → 0). Instead of storing repeated string values, Pinot stores these small integer IDs, reducing storage requirements and improving lookup speed. For example, in a column with millions of "safari" values, Pinot only stores the ID "1" instead of the full string, with a single mapping in the dictionary.

Inverted Index

An inverted index provides a direct mapping from column values to the row IDs where those values appear. When Pinot sees a filter like browser = 'safari', it immediately looks up which rows contain that value without scanning the entire column. This transforms WHERE conditions from full scans into simple lookups, making filtering near-instantaneous.

Forward Index

The forward index stores the actual data values for each column, but in a highly optimized format. For numeric and dictionary-encoded columns, these values are stored as tightly packed arrays. When Pinot identifies matching rows using the inverted index, it can retrieve just those specific values from the forward index without reading the entire column.

Bloom Filter

For high-cardinality columns, Bloom filters provide a probabilistic way to quickly determine if a value might exist in a segment. If the filter indicates a value definitely doesn't exist, Pinot can skip loading that segment entirely. This is particularly powerful for large datasets split across many segments.

Understanding the Columnar Storage Advantage

Pinot's columnar storage format is fundamental to its S3/Deep Storage optimization strategy. Rather than storing data row-by-row (as in traditional databases), Pinot stores each column separately. This means when executing a query that only needs certain columns (like clicks in our example), Pinot reads only those specific columns from storage, not entire rows.

This columnar approach, combined with row alignment (where Row 2 in each column refers to the same logical record), enables Pinot to fetch minimal data while maintaining the relationships between fields.

The Complete Query Optimization Process

When a query like SELECT SUM(clicks) FROM events WHERE browser='safari' runs against data in S3:

1. Pinot first loads small metadata and index files (~20KB total)
2. The dictionary index identifies that "safari" corresponds to ID 1
3. The inverted index shows that browser="safari" exists in rows 1 and 2
4. Pinot calculates the exact byte offsets for the clicks values in rows 1 and 2
5. It issues a precise S3 range request for only those bytes (~5KB)
6. It computes SUM(5 + 8) = 13 and returns the result

This entire process completes in under 100ms, while transferring only ~25KB instead of the full 2GB segment - an 80,000x reduction in data transfer!

Additional Index Types

Pinot supports several other index types for specialized query patterns:

• Range Index : Optimizes range queries like clicks > 7 by mapping value ranges to row IDs
• Star-Tree Index : Pre-aggregates data for common group-by and aggregation queries
• JSON Index : Enables efficient filtering on nested JSON fields
• Text Index : Supports full-text search capabilities for text fields
• Geospatial Index : Optimizes location-based queries and proximity searches

Additional Performance Optimization for Multi-Segment Queries

Apache Pinot also parallelizes processing across segments. When a query spans multiple segments, Pinot's query planner fetches and processes all segments simultaneously, rather than sequentially. This parallel execution strategy ensures query response times remain nearly constant regardless of how many segments contain the relevant data, making Pinot's performance highly predictable even as data volumes grow.

Real-World Impact

This indexing and storage strategy is well suited for:

• User-facing analytics where sub-second response is critical
• Cost-effective retention of historical data without performance penalties
• Operational dashboards with high query volumes on large datasets
• Time-series applications where queries span both hot and cold data

When to Choose Pinot