_tracking lacks sub-second data precision on time

[bryanlandia]

Problem:
data analytics teams may need to differentiate between events at the sub-second level but _tracking uses a simple DateTime field.

Suggestion:
Consider using at least a DateTime64(3) for milliseconds, as the tracking logs themselves have microsecond specificity.

ALTER TABLE _tracking ADD COLUMN time_ms DateTime64(3);
ALTER TABLE _tracking UPDATE time_ms = toDateTime64(time, 3) WHERE 1;
ALTER TABLE _tracking DROP COLUMN time;
ALTER TABLE _tracking RENAME COLUMN time_ms TO time;

then same thing for the events table

I believe the Vector parse_timestamp function as written should include the milliseconds from tracking log entries, so they should be available.

[bryanlandia]

On a very busy server it turns out that we were getting duplicate time values in _tracking even using millisecond specificity. Had to change it to a DateTime64(4). Many of the duplicates were from automated health check requests, but not all were.

[bryanlandia]

I'd just use DateTime64(6) as a DateTime64 takes 8 bytes regardless of specificity. You can go up to 9 decimal places for seconds, but the tracking logs themselves only go to microseconds.

[bryanlandia]

Some more info. You will need to also update the Vector Clickhouse sink with...

[sinks.clickhouse]
...
# support time field with microseconds
date_time_best_effort = true
encoding.timestamp_format = "rfc3339"

As of Vector 0.34 it will support encoding.timestamp_format "unix_us" which should also work without requiring date_time_best_effort.

See also vectordotdev/vector#5797

[Danyal-Faheem]

Hey Bryan, sorry for the late response. There was a maintainership change in progress.

I've looked into the issue you mentioned and the PR you created (thank you so much for that, by the way).

I can see how making this change would allow for finer granular control over the analytics. However, we are also concerned about the performance impact it might have on existing users. I noticed that you mentioned you tried this out on a server with 1.2B rows. I would be very interested to know if you have any time metrics for that upgrade process.

[bryanlandia]

Hi @Danyal-Faheem No problem and sorry for my own delay! I do have some notes on that upgrade process and will try to get those to you in the next day or two.