Thread: Using a single sequence for all tables

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

PostgreSQL makes it easy to generate unique ids. Just declare the column
as SERIAL (or IDENTITY GENERATED in newer versions). One problem with
this approach is that the ids are only unique within the table, not
across the database. If you accidentally use an id from one table to
access another table, chances are that a row with that id will actually
exist - it just won't be the row you wanted to access.

So the solution is to use a single sequence for all the id columns.

Instead of

    create table t1(
        id serial primary key,
        data text
    );

    create table t2(
        id serial primary key,
        t1 int references t1,
        data text
    );

you write

    create sequence global_seq;

    create table t1(
        id int primary key default nextval('global_seq'),
        data text
    );
    create table t2(
        id int primary key default nextval('global_seq'),
        t1 int references t1,
        data text
    );

Then you insert data just like you normally would with INSERT ...
RETURNING id. The difference is just that if you accidentally do an
UPDATE or delete wit an id from another table, it won't do anything.

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.
 * 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!
 * Doesn't work with IDENTIY - those columns always use implicit
   sequences.
 * currval() is pretty useless with a global sequence. But I basically
   never use that anyway.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 2021-09-29 11:42:42 +0200, Tobias Meyer wrote:
>     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.
[...]
>
> * you will have to pay attention to rollover/overflow situations more quickly. 

I'd subsume this under "ids will grow faster".

But I would actually see that as an advantage:

* All your ids have the same type, so either 32 bit is enough for
  everything or you use 64 bit (do the latter if in doubt).
* You only have to monitor a single sequence to see if you are close to
  an overflow, not dozens of them.
* You will probably be more aware that this might be a problem.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

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

On 2021-09-29 10:21:00 -0600, Michael Lewis wrote:
> If your processes somehow allow updates on the wrong table, then fix that.

If your processes somehow allow inserting duplicate keys, then fix that
(so unique key constraints are unnecessary).

If your process somehow allows deletion of records which are still
referenced, then fix that (so foreign key constraints are unnecessary).

Many tools we use are defensive in nature. In a perfect world, they
would not be needed. However, users and programmers do make mistakes, so
we have tools (like constraints) to prevent those mistakes from
destroying data. The "don't reuse ids" trick is an additional tool to
catch mistakes. Not the most important (as I wrote I haven't used in
PostgreSQL before (although I've been using PostgreSQL for 7 years
now)), but it would have helped me catch a bug earlier last week which
is why I revisited it (Yes, I caught the bug during testing. But a bit
too close to going live for comfort.).

> If you run out of space in whatever value range you choose initially, the pain
> to upgrade to a type that allows larger values would seem to be very large.

I may have to do that before I retire (even without global ids). One of
my databases now has ids in the range of 500E6, and I guess it might
reach 2E9 over the next decade or so.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"