PostgreSQL: Is using SELECT nextval generator thread safe, in harsh multiuser environments?

Question

I mean like thousands users in time updating values in database?

Answer 1

Yes, nextval is safe to use from multiple concurrently operating transactions. That is its purpose and its reason for existing.

That said, it is not actually "thread safe" as such, because PostgreSQL uses a multi-processing model not a multi-threading model, and because most client drivers (libpq, for example) do not permit more than one thread at a time to interact with a single connection.

You should also be aware that while nextval is guaranteed to return distinct and increasing values, it is not guaranteed to do so without "holes" or "gaps". Such gaps are created when a generated value is discarded without being committed (say, by a ROLLBACK) and when PostgreSQL recovers after a server crash.

While nextval will always return increasing numbers, this does not mean that your transactions will commit in the order they got IDs from a given sequence in. It's thus perfectly normal to have something like this happen:

Start IDs in table: [1 2 3 4]
1st tx gets ID 5 from nextval()
2nd tx gets ID 6 from nextval()
2nd tx commits:     [1 2 3 4 6]
1st tx commits:     [1 2 3 4 5 6]

In other words, holes can appear and disappear.

Both these anomalies are necessary and unavoidable consequences of making one nextval call not block another.

If you want a sequence without such ordering and gap anomalies, you need to use a gapless sequence design that permits only one transaction at a time to have an uncommitted generated ID, effectively eliminating all concurrency for inserts in that table. This is usually implemented using SELECT FOR UPDATE or UPDATE ... RETURNING on a counter table.

Search for "PostgreSQL gapless sequence" for more information.