Search Postgresql Archives

Re: Using a single sequence for all tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux