IDs, clocks and ordering
Give every machine its own number and let it mint IDs from its own clock, time first, so IDs never collide and still sort by time. Then decide what to do when a clock lies.
Why not use an auto-increment column for IDs?
It works on one database, and it is the right answer until the write rate or the number of data centres grows. Every ID is then a round trip to one row that every writer locks in turn, so that row sets the ceiling for the whole system, and a second data centre either waits across an ocean or needs its own counter. A Snowflake-style ID moves the counter into each process: the node number keeps IDs from different machines apart, and the time field keeps them in rough time order.
What happens to a Snowflake generator when the clock goes backwards?
Time sync can step a server clock back, and if the generator mints an ID at a time it has already used with a sequence it has already used, it hands out a duplicate. The fix is to remember the millisecond of the last ID and refuse to mint below it. For a small step you wait until the clock catches up; for a large one you fail loudly and take the node out of service, because waiting seconds on every request is worse than an error.
Is a UUID good enough as a primary key?
A random UUID (version 4) is unique without any coordination, which is often all you need. It does not sort by time, so a feed or a log keyed by it has to carry a separate timestamp to be ordered, and inserts land at random places in a B-tree index instead of at its end. When the key should also give you time order, use a time-first ID: a Snowflake, a ULID, or a UUID version 7.
What is the difference between a Lamport clock and a vector clock?
A Lamport clock is one counter per node. If one event caused another, the cause has the smaller number, but a smaller number does not prove a cause: two unrelated events can compare either way. A vector clock keeps one counter per node for every node, so it can also say that two events are concurrent, at the price of a stamp that grows with the number of nodes.
Can I sort events from different machines by their timestamps?
Only to within the clock skew, the most two machine clocks can disagree. IDs made on different machines are then k-sorted: sorted by ID, each is out of place by at most the skew. For a feed that is usually fine, as long as a reader does not treat the newest few milliseconds as final. For cause and effect across services, use a logical clock, because a skewed wall clock can stamp a reply before the message it answers.