Is it a bad idea using a single sequence generator in PostgreSQL?

Question

This is a generic question about PostgreSQL sequence performance, for if they could be a bottleneck in high-write-concurrency databases, when choosing use one instead of one per table.

I outlined performance and bottleneck because I am perfectly aware that PostgreSQL sequences are safe, as explained elsewhere and in PostgreSQL: Is using SELECT nextval generator thread safe, in harsh multiuser environments?. Also, I don't mind the holes the sequence may generate.

I have a number of tables (< 60), each one with its own surrogate primary key id, declared as

id int generated by default as identity

Most of the tables holding public entities are detailed by a subset of shared generic tables. For example, tables as product, category, post or user are detailed by table text, that holds all the varchars of a public object for the different languages on the application.

As the id columns of all those public entities may have the same value, I have to use the pair

object_id    int not null,
object_type  tobject not null,

to uniquely refer text to the public entities it details.

It is clear than having id columns with unique values, coming from a single sequence, would allow me to remove the object_type columns from all of the shared tables and the SQL code of the application. It would also simplify (ad hopefully accelerate) indexing on shared tables.

As I am planning to increase the number of shared tables, my question is how the sequence generators perform on high-write concurrency. Will a single sequence generator be a bottleneck when many concurrent insert into try to issue a nextval? Would it be better to stay with one per table?

Answer 1

Not commenting in your design, a single sequence will be no problem.

Sequences are optimized for concurrency, and if nextval actually threatens to be a bottleneck, you can change the sequence to use CACHE n for n > 1. Then every call to the sequence actually gets the next n values, which are cached by the database session.

Make sure to use bigint, not integer as the data type, so that you cannot run out of sequence values.