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?
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.