关于KSQL查询与表存储位置及机制的技术咨询

Awesome question—let’s unpack this step by step because how KSQL handles storage ties directly into designing efficient streaming pipelines with it.

Where are tables and query results stored? #

First off, KSQL doesn’t act as a standalone database—it leans entirely on Kafka as its single source of truth for raw data. Here’s the breakdown:

• Non-aggregated tables/streams: If you create a table directly from a Kafka topic without any aggregation (e.g., CREATE TABLE users (id INT PRIMARY KEY, name STRING) WITH (KAFKA_TOPIC='users_topic', VALUE_FORMAT='JSON');), KSQL doesn’t store any extra data. It just creates a logical mapping to the underlying Kafka topic. When you query it, KSQL reads directly from the original topic’s partitions.
• Aggregated tables/materialized views: For tables built with aggregations (like GROUP BY, COUNT, SUM), KSQL needs to maintain state to track the aggregated values. This state is stored locally on the KSQL server’s disk using RocksDB (the default storage engine), and to ensure fault tolerance, this state is also replicated to an internal Kafka "changelog" topic. If a KSQL instance restarts, it can rebuild the state from this changelog topic.
• Query results:
  • Push queries (the default for streaming results, e.g., SELECT * FROM users EMIT CHANGES;): Results are streamed directly to your client (CLI, API, etc.) and aren’t persisted unless you explicitly write them to a new Kafka topic using CREATE STREAM AS SELECT or CREATE TABLE AS SELECT.
  • Pull queries (one-time state lookups, e.g., SELECT COUNT(*) FROM user_orders WHERE user_id=123;): Results are fetched from the local RocksDB state store and returned immediately—no persistent storage of the result happens by default.

Does KSQL use pointers or replicate events when creating tables? #

This depends on the type of table you’re creating:

• Non-aggregated tables: KSQL uses a logical "pointer" mechanism. It doesn’t copy any events from the original Kafka topic. Instead, it references the topic’s partitions directly whenever you run a query. The table is just a schema layer on top of the existing Kafka data.
• Aggregated tables/materialized views: Here, KSQL doesn’t replicate the original events, but it does store the computed aggregated state (as mentioned earlier, in RocksDB and the changelog topic). The raw events remain in the source Kafka topic—only the processed state is stored separately.
• Explicit result writing: If you use CREATE STREAM AS SELECT or CREATE TABLE AS SELECT to output query results to a new Kafka topic, this counts as replicating data (the processed results are written to the new topic). But this is a deliberate choice, not the default behavior.

To wrap it up: KSQL avoids replicating raw Kafka data unless you explicitly tell it to. For most cases, it either references the original topic directly or stores only the necessary computed state for aggregations.

内容的提问来源于stack exchange，提问作者Maurizio Cimino