As a first experiment in using cockroach in a production but not critical role I am also interested in how well it might suite time series data. So I found this thread and wondered what results people may have come up with since January and what techniques they used.
Firstly I must say I am a database/SQL novice so even the most basic advice would be welcome.
My idea of time-series is simply series of records containing a series name/ID, a timestamp and a value. There may be a lot of inserts going on but never any updates. Reads would query a range of records for one or more series between given times.
Naively one might simply create a table of such rows and start inserting records. Before I even do that I’m sure it would be terribly slow. What with all those round trips to the three nodes in my cluster.
Looking around the net I find quite a lot of discussion of using SQL as a time-series database. Ways to optimize it. Most of them seem to use specific features of particular databases, MySQL, postgresql, etc. More importantly they seem to be tuned to the ways those db’s work under the hood. I imagine such tuning would be somewhat different for Cockroach.
My simple mind has understood a few simple optimizations:
Batch up writes and do bulk inserts of many rows at a time. One row per value.
Batch up writes and put many values into arrays. One row can then hold many values.
Batch up writes and put them into vectors. As above. I don’t think Cockroach has vectors.
People do odd things like use a different table for each series. Or generally partition things in other ways.
I did not understand dianasaur323 statement above: “The issue with time series right now is that you will need to use something other than just the timestamp as the primary key so that you can shard your inserts across multiple nodes and thereby increase performance.”
Surely cockroach is doing it’s own sharding and replication under the hood? What would be a good primary key? I was just going to use the series ID and timestamp.
Any advice welcome. Something simple to get me started for now.