Does ordering by auto-incrementing PK ensure chronological order?

Is it safe to assume that ordering by an auto-incrementing PK in Postgres will order the records chronologically, from a creation standpoint? I have a many to many relationship, from which I don't need to track anything else except the relationship itself and some form of order of creation. I am trying to decide whether I need to incorporate a timestamp column for this, or whether I can reutilize the existing auto-incrementing PK column for that same purpose.

Assuming by "auto-incrementing" you mean the Postgres SERIAL pseudo-type, the short answer is "not always".

SERIAL columns are implemented using standard SQL sequences, which might generate out-of-order values when used by multiple concurrent sessions if the CACHE parameter is set to something more than 1. The manual states this:


[A]lthough multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.

Leaving CACHE value at 1 might hurt performance in an environment with high concurrency, so you will have to choose what is more important to you.

In my view, trying to repurpose SERIAL for tracking order of creation of records is not a good idea, because it potentially creates a maintenance problem: since the sequence's only promise is to provide some unique incrementing value to each nextval request, relying on its behaviour under one circumstance might cause your application to behave unpredictably when the situation changes, e.g. the sequence implementation details are modified in a future Postgres version, or changes in your workload require you to increase the CACHE setting. Using a proper timestamp for orderging is both safer and semantically clearer.

StackExchange Database Administrators Q#266405, answer score: 16