Can I use clickhouse as key-value storage?

Question

Is it possible to use clickhouse as key-value storage, were data is regularly overwritten, but rarely read? What engine should I use if this is possible?

Answer 1

ClickHouse isn't built for that use case and it deliberately says that in the home page of its document.

When NOT to use ClickHouse

1. Transactional workloads (OLTP)
2. Key-value access with high request rate
3. Blob or document storage
4. Over-normalized data

However, if the QPS is low, you can still achieve good latency scores for point queries. ClickHouse also provides multiple kinds of Dictionaries that may be better serve as the external key-value storage. There is also a StorageJoin engine that supports joinGet function similar to redis' HGET operation. After this PR you can overwrite the existing keys in StorageJoin.

update

PR is merged. Here is an isolated example.

First populate a StorageJoin table as following:

CREATE TABLE my_fancy_kv_store (s String, x Array(UInt8), k UInt64)
ENGINE = Join(ANY, LEFT, s);

INSERT INTO my_fancy_kv_store VALUES ('abc', [0], 1), ('def', [1, 2], 2);

Then you can use it as a dictionary (key-value):

SELECT joinGet('my_fancy_kv_store', 'x', 'abc');
SELECT joinGet('my_fancy_kv_store', 'k', 'def');