When Do You Actually Need Apache Pinot?
Before getting into the installation, let me share a real story. Last year I was responsible for the analytics system of an e-commerce platform. We started with plain MySQL, and the “daily revenue” dashboard query ran in 3–5 seconds — acceptable. But once the data hit 50 million rows, that number jumped to 30–40 seconds. Users were constantly complaining, the PM’s deadline was closing in, and I had to find a solution fast.
I considered migrating the database. I’d been through a 100GB migration from MySQL to PostgreSQL before — three days of planning and one full day of execution. But this time, PostgreSQL wasn’t the answer either, because the core problem was OLAP (Online Analytical Processing), not OLTP. That’s when I found Apache Pinot.
Apache Pinot is a distributed OLAP datastore developed by LinkedIn and open-sourced in 2015. LinkedIn, Uber, and Walmart use it for user-facing analytics — dashboards that end users see directly and that cannot afford more than a second of wait time.
Why is Pinot faster? The architecture is fundamentally different:
- Columnar storage: reads only the columns needed instead of entire rows, significantly reducing I/O
- Star-tree index: pre-aggregates data by dimension, making GROUP BY queries 10–100x faster
- Real-time + batch ingestion: accepts data from Kafka or CSV/JSON/Parquet files
- Horizontal scaling: add servers without downtime
Need to query 1 billion rows and return results in under 100ms? That’s exactly the problem Pinot was designed to solve.
Installing Apache Pinot
Installing with Docker (Recommended for Dev/Test)
For local dev/test, Docker is much more convenient — no need to install Java separately or configure the classpath. Assuming Docker and Docker Compose are already installed, create a docker-compose.yml file:
version: '3.7'
services:
zookeeper:
image: zookeeper:3.5.6
hostname: zookeeper
ports:
- "2181:2181"
environment:
ZOOKEEPER_CLIENT_PORT: 2181
ZOOKEEPER_TICK_TIME: 2000
pinot-controller:
image: apachepinot/pinot:1.2.0
command: "StartController -zkAddress zookeeper:2181"
restart: unless-stopped
ports:
- "9000:9000"
depends_on:
- zookeeper
pinot-broker:
image: apachepinot/pinot:1.2.0
command: "StartBroker -zkAddress zookeeper:2181"
restart: unless-stopped
ports:
- "8099:8099"
depends_on:
- pinot-controller
pinot-server:
image: apachepinot/pinot:1.2.0
command: "StartServer -zkAddress zookeeper:2181"
restart: unless-stopped
ports:
- "8098:8098"
depends_on:
- pinot-controllerdocker-compose up -d
# Check that containers are running
docker-compose psWait about 30–60 seconds for the Controller to fully start. Then open your browser at http://localhost:9000 — you’ll see the Pinot UI.
Installing with Binary (Production)
# Download Pinot 1.2.0
wget https://downloads.apache.org/pinot/apache-pinot-1.2.0/apache-pinot-1.2.0-bin.tar.gz
tar -xvf apache-pinot-1.2.0-bin.tar.gz
cd apache-pinot-1.2.0-bin
# Start ZooKeeper
bin/pinot-admin.sh StartZookeeper -zkPort 2181 &
# Start Controller
bin/pinot-admin.sh StartController \
-zkAddress localhost:2181 \
-controllerPort 9000 &
# Start Broker
bin/pinot-admin.sh StartBroker -zkAddress localhost:2181 &
# Start Server
bin/pinot-admin.sh StartServer -zkAddress localhost:2181 &Detailed Configuration
Pinot needs three things to run: a Schema (data structure), a Table Config (storage and index configuration), and Data Ingestion (how to load data). The examples below use an e-commerce orders dataset — easy to visualize and close to real-world use cases.
Step 1: Create the Schema
The schema defines the data structure with three column types:
- dimensionFieldSpecs: for filtering/grouping (category, user_id…)
- metricFieldSpecs: for aggregation (revenue, count…)
- dateTimeFieldSpecs: the time column — required
Create the file orders_schema.json:
{
"schemaName": "orders",
"dimensionFieldSpecs": [
{"name": "order_id", "dataType": "STRING"},
{"name": "user_id", "dataType": "STRING"},
{"name": "product_category", "dataType": "STRING"},
{"name": "status", "dataType": "STRING"},
{"name": "payment_method", "dataType": "STRING"}
],
"metricFieldSpecs": [
{"name": "revenue", "dataType": "DOUBLE"},
{"name": "quantity", "dataType": "INT"},
{"name": "discount", "dataType": "DOUBLE"}
],
"dateTimeFieldSpecs": [
{
"name": "order_timestamp",
"dataType": "TIMESTAMP",
"format": "1:MILLISECONDS:EPOCH",
"granularity": "1:MILLISECONDS"
}
]
}curl -X POST \
"http://localhost:9000/schemas" \
-H "Content-Type: application/json" \
-d @orders_schema.jsonStep 2: Create the Table Config with Indexes
Index configuration is where Pinot truly sets itself apart from relational databases. Same data, same query — but the right index gives you 50ms, the wrong one gives you 5 seconds. Create the file orders_table.json:
{
"tableName": "orders",
"tableType": "OFFLINE",
"segmentsConfig": {
"timeColumnName": "order_timestamp",
"timeType": "MILLISECONDS",
"replication": "1",
"schemaName": "orders"
},
"tableIndexConfig": {
"loadMode": "MMAP",
"invertedIndexColumns": ["product_category", "status", "payment_method"],
"starTreeIndexConfigs": [
{
"dimensionsSplitOrder": ["product_category", "status"],
"skipStarNodeCreationForDimensions": [],
"functionColumnPairs": ["SUM__revenue", "COUNT__*"],
"maxLeafRecords": 10000
}
],
"rangeIndexColumns": ["revenue", "order_timestamp"]
},
"tenants": {
"broker": "DefaultTenant",
"server": "DefaultTenant"
},
"metadata": {}
}The three most commonly used index types, each serving a different query pattern:
- invertedIndexColumns: lightning-fast exact match lookups —
WHERE status = 'COMPLETED'runs in O(1) - starTreeIndexConfigs: pre-aggregates by dimension upfront, making GROUP BY queries 10–100x faster than a full scan
- rangeIndexColumns: fast range queries —
WHERE revenue BETWEEN 500000 AND 2000000
curl -X POST \
"http://localhost:9000/tables" \
-H "Content-Type: application/json" \
-d @orders_table.jsonStep 3: Load Data (Batch Ingestion)
Create a sample CSV file sample_orders.csv:
order_id,user_id,product_category,status,payment_method,revenue,quantity,discount,order_timestamp
ORD001,USER100,Electronics,COMPLETED,CREDIT_CARD,1500000,1,0,1720310400000
ORD002,USER101,Fashion,COMPLETED,BANK_TRANSFER,350000,2,50000,1720310460000
ORD003,USER102,Electronics,PENDING,MOMO,2200000,1,100000,1720310520000
ORD004,USER103,Food,COMPLETED,CASH,85000,3,0,1720310580000Create the ingestion job spec ingestion_job.yaml:
executionFrameworkSpec:
name: 'standalone'
segmentGenerationJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentGenerationJobRunner'
segmentTarPushJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentTarPushJobRunner'
jobType: SegmentCreationAndTarPush
inputDirURI: '/tmp/pinot-data/input'
outputDirURI: '/tmp/pinot-data/output'
overwriteOutput: true
pinotFSSpecs:
- scheme: file
className: org.apache.pinot.spi.filesystem.LocalPinotFS
recordReaderSpec:
dataFormat: 'csv'
className: 'org.apache.pinot.plugin.inputformat.csv.CSVRecordReader'
configClassName: 'org.apache.pinot.plugin.inputformat.csv.CSVRecordReaderConfig'
tableSpec:
tableName: 'orders'
schemaURI: 'http://localhost:9000/schemas/orders'
tableConfigURI: 'http://localhost:9000/tables/orders'
pinotClusterSpecs:
- controllerURI: 'http://localhost:9000'# Copy CSV to the correct directory
mkdir -p /tmp/pinot-data/input
cp sample_orders.csv /tmp/pinot-data/input/
# Run ingestion
bin/pinot-admin.sh LaunchDataIngestionJob \
-jobSpecFile ingestion_job.yamlTesting and Monitoring
Running Test SQL Queries
Pinot supports standard SQL. Open the Pinot UI at http://localhost:9000, go to the Query Console tab, and try:
-- Revenue by category
SELECT
product_category,
SUM(revenue) AS total_revenue,
COUNT(*) AS order_count,
AVG(revenue) AS avg_order_value
FROM orders
WHERE status = 'COMPLETED'
GROUP BY product_category
ORDER BY total_revenue DESC
LIMIT 10Or query via the REST API:
curl -X POST \
"http://localhost:8099/query/sql" \
-H "Content-Type: application/json" \
-d '{
"sql": "SELECT product_category, SUM(revenue) as total FROM orders GROUP BY product_category LIMIT 10"
}'The response includes a timeUsedMs field — this is the actual query time. With a properly configured star-tree index, this number typically stays under 50ms, even with millions of rows.
Monitoring with Python
import requests
def query_pinot(sql: str, broker_url: str = "http://localhost:8099") -> dict:
response = requests.post(
f"{broker_url}/query/sql",
headers={"Content-Type": "application/json"},
json={"sql": sql},
timeout=10
)
result = response.json()
time_ms = result.get("timeUsedMs", 0)
rows = result.get("resultTable", {}).get("rows", [])
print(f"Query time: {time_ms}ms | Rows returned: {len(rows)}")
return result
# Benchmark with real data
result = query_pinot("""
SELECT
status,
COUNT(*) AS cnt,
SUM(revenue) AS total_revenue
FROM orders
GROUP BY status
ORDER BY cnt DESC
""")
# Print results
columns = result["resultTable"]["dataSchema"]["columnNames"]
for row in result["resultTable"]["rows"]:
print(dict(zip(columns, row)))Checking Cluster Health
# Health check — returns "OK" if the cluster is healthy
curl http://localhost:9000/health
# List all created tables
curl http://localhost:9000/tables
# Segment info for the orders table
curl http://localhost:9000/segments/orders
# Broker metrics (Prometheus format)
curl http://localhost:8099/metrics | grep pinot_brokerIf you run into errors, check the logs for debugging:
# Docker
docker logs pinot-controller --tail 50
docker logs pinot-server --tail 50
# Binary
tail -f logs/pinot-controller.log
tail -f logs/pinot-server.logThe most common mistake for newcomers is forgetting to configure the star-tree index. Without it, Pinot still runs — but query times spike as data grows. Seeing slower-than-expected performance? Review your tableIndexConfig and make sure you have the right dimension/metric pairs in functionColumnPairs.
MySQL and PostgreSQL excel at OLTP — transactions, updates, and deletes on individual rows. Pinot excels at OLAP — fast reads over large datasets without worrying about write throughput. Different problems, different tools. With the setup above, you can benchmark on your own data right away — on the same 50 million rows, the difference between MySQL and Pinot typically goes from 30 seconds down to under 1 second.
