On Wed, 2021-09-29 at 11:26 +0200, Peter J. Holzer wrote: > I discovered this technique back in my Oracle days but it dropped out of > my toolbox when I switched to PostgreSQL. Recently I had reason to > revisit it, so I thought I should share it (trivial though it is). > > So the solution is to use a single sequence for all the id columns. > > Possible drawbacks: > > * The ids will grow faster, and they will be large even on small > tables. It may be a bit irritating if you have a table with just 5 > rows and the ids are 5, 6, 7, 12654, 345953. That's why use use "bigint". > * Bottleneck? Using a single sequence was said to be a performance > bottleneck in Oracle. I didn't notice a performance difference then > and I doubt it would be one in PostgreSQL, but if in doubt, measure! That's no problem if you define the sequence with a CACHE value above 1, so that not every "nextval" call hits the sequence. > * Doesn't work with IDENTIY - those columns always use implicit > sequences. Right. > * currval() is pretty useless with a global sequence. But I basically > never use that anyway. Same here. I think identity columns are a Good Thing, particularly when CREATED ALWAYS, and I don't see the advantage of a database-wide unique identifier. But if it gives you a warm fuzzy feeling, go fot it :^) Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
