How do we store and query JSON event payloads at TB scale without blowing up costs?

Most teams only realize their JSON strategy is broken when dashboards slow to a crawl, storage doubles every few months, and “quick investigations” on TBs of event payloads time out or silently get abandoned. The good news: you don’t have to denormalize everything into a row store or turn off half your events to survive. With the right schema and storage engine, you can keep rich JSON payloads, scan billions of events in milliseconds, and keep costs under control.

Quick Answer: Store raw JSON event payloads in a column-oriented engine like ClickHouse, but don’t query them as blobs. Normalize hot fields into typed columns, use JSONExtract/JSONExtractKeysAndValues and JSON/Object types for semi-structured data, and lean on columnar compression plus tiered storage. That combination lets you run millisecond queries on TBs of JSON while dramatically reducing storage and compute costs.

Why This Matters

JSON event payloads are the backbone of product analytics, observability, security, and user behavior tracking. As volume grows from GBs to TBs and beyond, naive JSON storage (e.g., blobs in row stores or object storage) becomes a tax on every team:

• SREs can’t drill into incidents fast enough.
• Product teams stop asking questions because queries are too slow or expensive.
• Finance sees cloud bills dominated by “logs” and “analytics” line items.

Columnar databases like ClickHouse flip this equation: they compress JSON-heavy workloads aggressively and execute queries with vectorized scans instead of row-by-row parsing. You keep the fidelity of raw events without paying row-store prices for every additional property you add.

Key Benefits:

• Blazing fast queries on TBs of JSON: Columnar storage and vectorized execution let you aggregate and filter billions of events with millisecond results—even when the payloads are large.
• Cost effective at high scale: Best-in-class compression ratios and tiered storage (hot SSD + cold object storage) keep storage and compute costs in check as your event volume grows.
• Developer friendly, flexible schema: Start with schemaless JSON ingestion, then incrementally promote frequently queried properties into typed columns using simple SQL, without downtime.

Core Concepts & Key Points

Concept	Definition	Why it's important
Columnar JSON storage	Storing event metadata and extracted JSON fields in a column-oriented database like ClickHouse, while keeping the raw JSON payload for full fidelity.	Separates “hot” queryable fields from “cold” rarely-used data, enabling high compression and fast scans without losing raw data.
Schema-on-read with promotion	Ingest JSON with minimal upfront schema, then iteratively promote frequently queried JSON keys into proper typed columns.	Lets teams move quickly while still converging toward an efficient schema as access patterns become clear.
Tiered & lifecycle-aware storage	Using partitioning, TTL, and tiered storage to keep recent data on fast disks and age older events into cheaper object storage.	Keeps costs predictable at TB–PB scale while preserving the ability to query historical events when needed.

How It Works (Step-by-Step)

At TB scale, storing and querying JSON efficiently comes down to a clear separation of concerns:

1. Model the event shell, not every nested field
2. Ingest JSON payloads efficiently into ClickHouse
3. Iteratively optimize hot paths without breaking ingestion

1. Model the event shell

Instead of creating one column per JSON property up front, define a narrow “event shell” table for the stable, high-cardinality dimensions you always query on:

CREATE TABLE events
(
    event_time     DateTime64(3) CODEC(Delta, ZSTD),
    event_date     Date          ALIAS toDate(event_time),
    event_type     LowCardinality(String),
    user_id        String,
    session_id     String,
    source         LowCardinality(String),
    -- Raw JSON payload as string
    payload        String CODEC(ZSTD)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_time, user_id, event_type);

Why this works:

• Columnar storage compresses event_type, source, and timestamps very well.
• The payload column is stored once per event and compressed aggressively with ZSTD.
• Partitioning by toYYYYMM(event_date) keeps partitions at manageable cardinality and aligns with retention (months), rather than over-partitioning by user or event type.

You now have a scalable, cost-efficient landing zone for JSON events—without endless schema debates.

2. Ingest JSON payloads efficiently

At TB scale, the main operational killer isn’t JSON itself—it’s ingestion patterns that fragment your storage engine.

In ClickHouse, every insert becomes one or more parts in a MergeTree table. Too many tiny parts lead to “Too many parts” errors and unstable performance. The remedy is simple:

• Batch inserts: Target 10,000–100,000 rows per insert (minimum 1,000).
• Use async inserts where appropriate:

SET async_insert = 1;
SET wait_for_async_insert = 0;

• Monitor parts and merges:

SELECT
    table,
    count() AS parts,
    sum(rows) AS total_rows,
    sum(bytes_on_disk) / 1e9 AS gb_on_disk
FROM system.parts
WHERE active AND database = 'events_db'
GROUP BY table
ORDER BY parts DESC;

If you see hundreds of thousands of parts for your events table, you’re under-batching. Fixing that almost always has more impact than any JSON-specific tuning.

3. Query JSON with schema-on-read

Once events are landing reliably, you can start reading JSON properties on demand using ClickHouse’s JSON functions.

For ad-hoc exploration:

SELECT
    event_type,
    JSONExtractString(payload, 'page'),
    JSONExtractUInt(payload, 'status_code') AS status_code,
    count() AS c
FROM events
WHERE
    event_date >= today() - 1
    AND event_type = 'http_request'
    AND status_code >= 500
GROUP BY
    event_type,
    status_code,
    JSONExtractString(payload, 'page')
ORDER BY c DESC
LIMIT 20;

Under the hood:

• Columnar filtering uses event_date and event_type to prune most data before JSON parsing.
• JSONExtract* operates only on the remaining payload strings.

This pattern is perfect when:

• You’re exploring new JSON keys.
• You don’t yet know which fields will be queried frequently.
• Queries are occasional, not on every dashboard refresh.

4. Promote hot JSON fields into columns

As patterns emerge—fields like status_code, country, or feature_flag show up in every dashboard—the cost of parsing them from JSON on each query adds up.

At this point, you can add real columns and backfill (if needed):

ALTER TABLE events
    ADD COLUMN status_code UInt16 DEFAULT JSONExtractUInt(payload, 'status_code'),
    ADD COLUMN country LowCardinality(String) DEFAULT JSONExtractString(payload, 'country');

New rows will automatically populate these columns from payload via the DEFAULT expression.

Dashboard queries can now avoid JSON parsing entirely:

SELECT
    toStartOfMinute(event_time) AS minute,
    status_code,
    count() AS requests
FROM events
WHERE
    event_time >= now() - INTERVAL 1 HOUR
    AND event_type = 'http_request'
GROUP BY
    minute,
    status_code
ORDER BY minute, status_code;

If you need historical consistency, you can backfill with an ALTER TABLE ... UPDATE or by rewriting partitions—but do this carefully and in chunks, as large mutations can be heavy. In managed ClickHouse Cloud, you monitor progress via system.mutations:

SELECT
    database,
    table,
    mutation_id,
    command,
    is_done,
    create_time,
    latest_failed_part
FROM system.mutations
WHERE table = 'events'
ORDER BY create_time DESC;

5. Keep long-tail JSON flexible

Even after promotion, you’ll still have a long tail of rarely-used JSON properties. Instead of exploding columns for every key, you can use:

• JSON or Object-typed columns for semi-structured data when available, or
• Continue storing them in the payload string and using JSONExtract only when needed.

This keeps the schema manageable while still giving you full-fidelity access when investigative queries require it.

6. Control costs with lifecycle & tiered storage

At TB scale, the biggest line items are:

• Storage for historical events.
• Compute for queries that scan large windows of time.

ClickHouse gives you lifecycle primitives that keep both under control:

• Partition by time (e.g., toYYYYMM(event_date)) to group data by retention boundaries.
• Use TTL for automatic retention:

ALTER TABLE events
    MODIFY TTL event_date + INTERVAL 90 DAY
    DELETE;

• Combine TTL with tiered storage so recent partitions stay on fast SSD and older ones move to cheaper object storage (e.g., S3), without manual orchestration.

This design lets you:

• Keep 7–30 days of hot data for dashboards and alerts.
• Maintain months or years of cold data at a fraction of the cost, still queryable when needed.

Common Mistakes to Avoid

• Storing JSON as blobs in row stores:
  Row-oriented databases parse every row and carry every column through execution. At TB scale, this makes wide JSON payloads painfully slow and expensive to query. Prefer columnar storage with predicate pushdown and selective JSON parsing.

• Over-partitioning by high-cardinality keys:
  Partitioning by user_id or session_id explodes the number of partitions and hurts merges and query performance. Follow ClickHouse’s guidance: partition primarily for lifecycle (e.g., monthly by date) and keep cardinality per partition in the 100–1,000 range.

• Tiny, frequent inserts:
  Streaming one row at a time into a MergeTree creates too many parts and destabilizes the cluster. Always batch to at least 1,000 rows per insert, preferably 10,000–100,000, and monitor system.parts for early warning.

• Mutating TBs of data in one shot:
  Massive ALTER TABLE ... UPDATE on TB-scale tables can run for a long time and impact performance. Break them into time-bound ranges (e.g., by month) and watch system.mutations to avoid surprises.

Real-World Example

At a previous organization, we ingested 3–5 TB/day of JSON-heavy HTTP logs and product events. The initial Elasticsearch-based stack made every new dimension a cost decision: adding a field to the mapping meant more storage and slower queries, and aggregations on nested JSON were increasingly painful.

We moved the workload to ClickHouse with a pattern very close to the one above:

• A narrow events shell table with timestamps, IDs, and a compressed JSON payload.
• Batching 50,000–100,000 events per insert to keep parts healthy.
• Partitioning by month, with a 30-day hot retention on SSD and longer history in object storage.
• Gradual promotion of hot fields like status_code, customer_tier, and feature_flag into typed columns.

The result:

• Investigative queries on billions of events dropped from 30–90 seconds to single-digit milliseconds to low seconds.
• Storage footprint fell by ~50–70% thanks to columnar compression and the ability to keep most JSON in a single compressed column.
• We stopped arguing about which event properties we could “afford” to log. We logged them all and only promoted fields when they became operationally important.

Pro Tip: Before you promote a JSON field into its own column, confirm it’s truly hot using system.query_log:

SELECT
    JSONExtractString(query, 'value') AS sample_query,
    count() AS uses
FROM system.query_log
WHERE
    event_date >= today() - 7
    AND query LIKE '%JSONExtractString(payload, \'country\')%'
GROUP BY sample_query
ORDER BY uses DESC
LIMIT 10;

If a JSONExtract shows up in many queries or dashboards, it’s a strong candidate for promotion into a real column.

Summary

Storing and querying JSON event payloads at TB scale without blowing up costs is less about “magic JSON features” and more about using the right storage model:

• Land events in a column-oriented engine like ClickHouse with a narrow “event shell” plus a compressed JSON payload.
• Ingest in healthy batches and monitor system.parts and system.merges to keep MergeTree stable.
• Use schema-on-read JSON functions for exploration, then incrementally promote hot fields into typed columns.
• Control long-term cost with lifecycle-aware partitioning, TTL, and tiered storage.

This pattern gives you the best of both worlds: millisecond queries over billions of JSON-rich events and a cost profile that doesn’t explode as your payloads and traffic grow.

Next Step

Get Started