4 ms·

While that's technically true, it isn't in any practical way. What K/V API does CockroachDB offer you as a user? The closest you can get is: CREATE TABLE kv (k

While that's technically true, it isn't in any practical way. What K/V API does CockroachDB offer you as a user? The closest you can get is: CREATE TABLE kv (key STRING PRIMARY KEY, value STRING); But calling that a "key-value" store is disingenuous at best.

deleted 6y ago
[deleted]
jordanlewis 6y ago
Hi Taylor! What are the salient differences in your mind? Under the hood, CockroachDB executes writes to and reads from such a table in the same way that you would against a key-value store. You can explore this for yourself with the "kv trace" functionality of CockroachDB's SQL shell, which logs of all of the KV API calls that a SQL query emits: $ ./cockroach demo # Welcome to the CockroachDB demo database! # # You are connected to a temporary, in-memory CockroachDB cluster of 1 node. # ... # demo@127.0.0.1:26257/test> CREATE TABLE kv (k STRING PRIMARY KEY, v STRING); CREATE TABLE Time: 5ms total (execution 5ms / network 0ms) demo@127.0.0.1:26257/test> \set auto_trace=on,kv demo@127.0.0.1:26257/test> INSERT INTO kv VALUES('a', 'b'); INSERT 1 Time: 2ms total (execution 2ms / network 0ms) timestamp | age | message | tag | location | operation | span --------------------------------------+-----------------+--------------------------------------------------+------------------------------------------------------------+-----------------------------------------+------------------+------- 2020-12-17 23:17:46.626696+00:00:00 | 00:00:00.001123 | CPut /Table/53/1/"a"/0 -> /TUPLE/2:2:Bytes/b | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/row/writer.go:207 | flow | 6 2020-12-17 23:17:46.626754+00:00:00 | 00:00:00.001181 | querying next range at /Table/53/1/"a"/0 | [n1,client=127.0.0.1:49216,hostssl,user=demo,txn=dcce3954] | kv/kvclient/kvcoord/range_iter.go:159 | dist sender send | 8 2020-12-17 23:17:46.626792+00:00:00 | 00:00:00.001219 | r36: sending batch 1 CPut, 1 EndTxn to (n1,s1):1 | [n1,client=127.0.0.1:49216,hostssl,user=demo,txn=dcce3954] | kv/kvclient/kvcoord/dist_sender.go:1851 | dist sender send | 8 2020-12-17 23:17:46.627281+00:00:00 | 00:00:00.001708 | fast path completed | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/plan_node_to_row_source.go:145 | flow | 6 2020-12-17 23:17:46.627322+00:00:00 | 00:00:00.001749 | rows affected: 1 | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/conn_executor_exec.go:622 | exec stmt | 4 (5 rows) Time: 1ms total (execution 1ms / network 0ms) demo@127.0.0.1:26257/test> SELECT * FROM kv WHERE k = 'a'; k | v ----+---- a | b (1 row) Time: 6ms total (execution 6ms / network 0ms) timestamp | age | message | tag | location | operation | span --------------------------------------+-----------------+----------------------------------------+------------------------------------------------------------+-----------------------------------------+------------------+------- 2020-12-17 23:17:54.402735+00:00:00 | 00:00:00.003116 | Scan /Table/53/1/"a"{-/#} | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/row/kv_batch_fetcher.go:337 | materializer | 7 2020-12-17 23:17:54.402763+00:00:00 | 00:00:00.003144 | querying next range at /Table/53/1/"a" | [n1,client=127.0.0.1:49216,hostssl,user=demo,txn=d30bcbc9] | kv/kvclient/kvcoord/range_iter.go:159 | dist sender send | 9 2020-12-17 23:17:54.404565+00:00:00 | 00:00:00.004946 | r36: sending batch 1 Scan to (n1,s1):1 | [n1,client=127.0.0.1:49216,hostssl,user=demo,txn=d30bcbc9] | kv/kvclient/kvcoord/dist_sender.go:1851 | dist sender send | 9 2020-12-17 23:17:54.405091+00:00:00 | 00:00:00.005472 | fetched: /kv/primary/'a'/v -> /'b' | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/colfetcher/cfetcher.go:888 | materializer | 7 2020-12-17 23:17:54.405895+00:00:00 | 00:00:00.006276 | rows affected: 1 | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/conn_executor_exec.go:622 | exec stmt | 4 (5 rows) Time: 1ms total (execution 1ms / network 0ms) demo@127.0.0.1:26257/test> I'll draw your attention to two lines in particular. Here's the put: 2020-12-17 23:17:46.626696+00:00:00 | 00:00:00.001123 | CPut /Table/53/1/"a"/0 -> /TUPLE/2:2:Bytes/b | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/row/writer.go:207 | flow | 6 And here's the get: 2020-12-17 23:17:54.402735+00:00:00 | 00:00:00.003116 | Scan /Table/53/1/"a"{-/#} | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/row/kv_batch_fetcher.go:337 | materializer | 7 These operations (`CPut` and `Scan`) are KV operations that you'd be able to run yourself against any key-value store. CockroachDB doesn't give you access to those operations directly, but crafting your queries in this way is really not significantly different.
- jolux 6y ago
  I think it’s the “under the hood” that’s the important part here. The salient difference is that calling it a key-value store eliminates the business reason for using it.

deleted 6y ago

[deleted]

jordanlewis 6y ago

Hi Taylor! What are the salient differences in your mind? Under the hood, CockroachDB executes writes to and reads from such a table in the same way that you would against a key-value store. You can explore this for yourself with the "kv trace" functionality of CockroachDB's SQL shell, which logs of all of the KV API calls that a SQL query emits: $ ./cockroach demo # Welcome to the CockroachDB demo database! # # You are connected to a temporary, in-memory CockroachDB cluster of 1 node. # ... # demo@127.0.0.1:26257/test> CREATE TABLE kv (k STRING PRIMARY KEY, v STRING); CREATE TABLE Time: 5ms total (execution 5ms / network 0ms) demo@127.0.0.1:26257/test> \set auto_trace=on,kv demo@127.0.0.1:26257/test> INSERT INTO kv VALUES('a', 'b'); INSERT 1 Time: 2ms total (execution 2ms / network 0ms) timestamp | age | message | tag | location | operation | span --------------------------------------+-----------------+--------------------------------------------------+------------------------------------------------------------+-----------------------------------------+------------------+------- 2020-12-17 23:17:46.626696+00:00:00 | 00:00:00.001123 | CPut /Table/53/1/"a"/0 -> /TUPLE/2:2:Bytes/b | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/row/writer.go:207 | flow | 6 2020-12-17 23:17:46.626754+00:00:00 | 00:00:00.001181 | querying next range at /Table/53/1/"a"/0 | [n1,client=127.0.0.1:49216,hostssl,user=demo,txn=dcce3954] | kv/kvclient/kvcoord/range_iter.go:159 | dist sender send | 8 2020-12-17 23:17:46.626792+00:00:00 | 00:00:00.001219 | r36: sending batch 1 CPut, 1 EndTxn to (n1,s1):1 | [n1,client=127.0.0.1:49216,hostssl,user=demo,txn=dcce3954] | kv/kvclient/kvcoord/dist_sender.go:1851 | dist sender send | 8 2020-12-17 23:17:46.627281+00:00:00 | 00:00:00.001708 | fast path completed | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/plan_node_to_row_source.go:145 | flow | 6 2020-12-17 23:17:46.627322+00:00:00 | 00:00:00.001749 | rows affected: 1 | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/conn_executor_exec.go:622 | exec stmt | 4 (5 rows) Time: 1ms total (execution 1ms / network 0ms) demo@127.0.0.1:26257/test> SELECT * FROM kv WHERE k = 'a'; k | v ----+---- a | b (1 row) Time: 6ms total (execution 6ms / network 0ms) timestamp | age | message | tag | location | operation | span --------------------------------------+-----------------+----------------------------------------+------------------------------------------------------------+-----------------------------------------+------------------+------- 2020-12-17 23:17:54.402735+00:00:00 | 00:00:00.003116 | Scan /Table/53/1/"a"{-/#} | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/row/kv_batch_fetcher.go:337 | materializer | 7 2020-12-17 23:17:54.402763+00:00:00 | 00:00:00.003144 | querying next range at /Table/53/1/"a" | [n1,client=127.0.0.1:49216,hostssl,user=demo,txn=d30bcbc9] | kv/kvclient/kvcoord/range_iter.go:159 | dist sender send | 9 2020-12-17 23:17:54.404565+00:00:00 | 00:00:00.004946 | r36: sending batch 1 Scan to (n1,s1):1 | [n1,client=127.0.0.1:49216,hostssl,user=demo,txn=d30bcbc9] | kv/kvclient/kvcoord/dist_sender.go:1851 | dist sender send | 9 2020-12-17 23:17:54.405091+00:00:00 | 00:00:00.005472 | fetched: /kv/primary/'a'/v -> /'b' | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/colfetcher/cfetcher.go:888 | materializer | 7 2020-12-17 23:17:54.405895+00:00:00 | 00:00:00.006276 | rows affected: 1 | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/conn_executor_exec.go:622 | exec stmt | 4 (5 rows) Time: 1ms total (execution 1ms / network 0ms) demo@127.0.0.1:26257/test> I'll draw your attention to two lines in particular. Here's the put: 2020-12-17 23:17:46.626696+00:00:00 | 00:00:00.001123 | CPut /Table/53/1/"a"/0 -> /TUPLE/2:2:Bytes/b | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/row/writer.go:207 | flow | 6 And here's the get: 2020-12-17 23:17:54.402735+00:00:00 | 00:00:00.003116 | Scan /Table/53/1/"a"{-/#} | [n1,client=127.0.0.1:49216,hostssl,user=demo] | sql/row/kv_batch_fetcher.go:337 | materializer | 7 These operations (`CPut` and `Scan`) are KV operations that you'd be able to run yourself against any key-value store. CockroachDB doesn't give you access to those operations directly, but crafting your queries in this way is really not significantly different.

jolux 6y ago
I think it’s the “under the hood” that’s the important part here. The salient difference is that calling it a key-value store eliminates the business reason for using it.