We are thinking about storing timestamps for e.g. created_at with a nanoseconds precision e.g. 2019-03-06 09:21:19.942988 (current we store it with 3 digits less e.g. 2019-03-06 09:21:19.942) We mainly need this for sorting for very fast imports where multiple inserts can be at the same millisecond. So we wrote code (java) which makes sure that the last three digits are unique and monotonic increasing (given they are calculated on the same host/instance/vm).
My questions are:
What are the side effects of such longer timestamps? We have an index on the created_at column which is used e.g. for range queries (start BETWEEN end).
We now want put an order by created_at to make sure that values are sorted increasing (insertion order).
Or should we just store this as an additional field just for sorting and leave the created_at as it (yyyy-MM-dd HH:mm:ss.SSS -> 2019-03-06 09:21:19.942 instead of 2019-03-06 09:21:19.942988)?