A new JSON data type for ClickHouse

Original Article ↗ Hacker News Discussion ↗

ClickHouse’s new JSON data type prompts wide interest because it can dynamically map semi-structured JSON into efficient columnar storage, improving query performance without forcing users to predefine schemas or unify mixed types. Commenters compare it with alternatives like DuckDB, Postgres, Pinot and others, generally positioning ClickHouse as a strong choice once data volumes and analytical workloads exceed what traditional OLTP databases handle comfortably. A recurring theme is how to ingest streaming data reliably—often via Kafka, Vector, or HTTP batching—highlighting that operational patterns around data pipelines matter as much as the core database features.

New JSON Type & Performance

  • New JSON type and related internal types (Dynamic, Variant) aim to store JSON in a columnar, typed way instead of as plain strings.
  • Key benefits discussed: automatic column “explosion” from JSON, reduced CPU/IO versus repeatedly parsing strings, and support for mixed/dynamic schemas without predeclaring subtypes.
  • Multiple commenters note similar ideas in Snowflake (VARIANT), SingleStore, Scratchdata/DuckDB, Oracle 23ai, and possibly Google Capacitor.

What “JSON” Means Here

  • Some are confused or skeptical about calling this “JSON,” arguing it’s really an internal storage format, not a new JSON standard.
  • Others clarify: it still ingests/outputs standard JSON; the “new type” is the binary/columnar representation of JSON values, analogous to PostgreSQL/SQLite’s JSONB.
  • Distinction is made between JSON as an encoding vs JSON as a data type (numbers, strings, arrays, objects).

ClickHouse vs Other Databases

  • Widely praised as “criminally underused,” especially once Postgres hits OLAP limits (ad‑hoc aggregates, count distinct, heavy writes, 100s of GB+).
  • Compared to DuckDB: DuckDB often faster per core and great for single-node analytics; ClickHouse seen as better once you need clusters, replication, and long-term growth.
  • Pinot and Doris are suggested alternatives, with Pinot touted for very low-latency “user-facing analytics” and star-tree indexes; there is disagreement over whether ClickHouse was originally single-node or cluster-first.

Scalability & Operational Traits

  • Users report success at scales from hundreds of millions to 100B+ rows with minimal maintenance and good compression.
  • ClickHouse is described as resource-hungry and ill-suited to tightly shared environments (e.g., big nodes inside Kubernetes pods).
  • Some concern about difficulty redistributing data when adding new nodes; remedies are unclear or rely on backup/restore tools.

Ingestion & “Real-Time”

  • Several people struggle with “reliable ingestion” and are put off by Kafka/ZooKeeper complexity.
  • Others report success via:
    • Direct HTTP/driver inserts (often batched).
    • Asynchronous inserts and ReplacingMergeTree to handle duplicates.
    • Buffers/ETL tools like Vector, Fluentd, RudderStack, Tinybird, Fivetran, S3 + refreshable materialized views, or Kafka-compatible systems like Redpanda.
  • Debate over what counts as “real-time”: strict per-event, exactly-once vs micro-batched, near-real-time analytics.

JSON Typing & Elasticsearch Contrast

  • New support for “dynamically changing data” types excites users frustrated with Elasticsearch’s “first-seen type wins” behavior.
  • One proposal (in Elasticsearch context) is type-suffixed field names (e.g., value::int, value::str), but others note this complicates queries and reporting.