Re: Could postgres12 support millions of sequences? (like 10 million) - Mailing list pgsql-general

On 3/19/20 10:31 PM, pabloa98 wrote:
> I see.
> 
> Any suggestion? It should behave like a sequence in the sense that 
> concurrent transitions will get different numbers from this alternative 
> sequence like solution.
> 
> In our case, we will need to do a call nextval('some_seq') (or similar) 
> from different processes no more than twice every minute.
> 
> 
> It would be nice to have a sequence data type. Correct me if I am wrong, 
> but It seems to me that a sequence data type would cost the same or less 
> than the current sequence implementation.
> 
> The schema would be more clear too. We could have a table like:
> 
> CREATE TABLE pair(
> group INT NOT NULL,
> element INT NOT NULL,
> seq SEQUENCE INCREMENT 1
>                START 1
>                CACHE 1
>                MINVALUE 1
>                MAXVALUE 99999999
>          NOT NULL,
> CONSTRAINT PRIMARY KEY (group, element)
> );
> 
> And then:
> 
> INSERT INTO event(group, element, code)
> VALUES (
>           1,
>           1,
>           ( SELECT seq.nextval('code_1_1_seq') FROM pair p WHERE 
> p.group=1 and p.code=1 )
> );
> 
> Or perhaps storing all the sequences in the same table as rows will have 
> the same behavior.

If code is just something to show the sequence of insertion for group, 
element combinations then maybe something like below:

CREATE TABLE event(
group_id INT NOT NULL, --changed as group is reserved word
element INT NOT NULL,
insert_ts timestamptz NOT NULL DEFAULT clock_timestamp(),
PRIMARY KEY(group_id, element, insert_ts)
);


insert into event(group_id, element) VALUES
(1, 1),
(1, 1),
(1, 1),
(2, 1),
(1, 1),
(1, 3),
(1, 1),
(1, 3),
(2, 1),
(2, 1);


select * from event ;
  group_id | element |           insert_ts
----------+---------+--------------------------------
         1 |       1 | 03/20/2020 09:51:12.675926 PDT
         1 |       1 | 03/20/2020 09:51:12.675985 PDT
         1 |       1 | 03/20/2020 09:51:12.675991 PDT
         2 |       1 | 03/20/2020 09:51:12.675996 PDT
         1 |       1 | 03/20/2020 09:51:12.676 PDT
         1 |       3 | 03/20/2020 09:51:12.676004 PDT
         1 |       1 | 03/20/2020 09:51:12.676008 PDT
         1 |       3 | 03/20/2020 09:51:12.676012 PDT
         2 |       1 | 03/20/2020 09:51:12.676016 PDT
         2 |       1 | 03/20/2020 09:51:12.67602 PDT
(10 rows)


select group_id, element, row_number() OVER (partition by (group_id, 
element) order by (group_id, element)) AS code from event;
  group_id | element | code
----------+---------+------
         1 |       1 |    1
         1 |       1 |    2
         1 |       1 |    3
         1 |       1 |    4
         1 |       1 |    5
         1 |       3 |    1
         1 |       3 |    2
         2 |       1 |    1
         2 |       1 |    2
         2 |       1 |    3
(10 rows)

> 
> Pablo
> 
> 
> On Thu, Mar 19, 2020 at 7:56 PM Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
> 
>     Michael Lewis <mlewis@entrata.com <mailto:mlewis@entrata.com>> writes:
>      > On Thu, Mar 19, 2020, 5:48 PM David G. Johnston
>     <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>>
>      > wrote:
>      >> However, one other consideration with sequences: do you care that
>      >> PostgreSQL will cache/pin (i.e., no release) every single
>     sequence you
>      >> touch for the lifetime of the session? (I do not think DISCARD
>     matters here
>      >> but I'm just guessing)
> 
>      > Would you expand on this point or is there someplace specific in the
>      > documentation on this?
> 
>     I think what David is worried about is that a sequence object is a
>     one-row table in PG's implementation.  Thus
> 
>     (1) each sequence requires a dozen or two rows in assorted system
>     catalogs (not sure exactly how many offhand).
> 
>     (2) each sequence uses up 8KB on disk for its table file.
> 
>     (3) each sequence you actually access within a particular session
>     results in creation of relcache and catcache entries in that
>     session's local memory.  I'm not sure offhand how big those are
>     either, but a few KB per sequence would be a reasonable guess.
> 
>     (4) each sequence competes for space in the shared-buffer arena,
>     since its 8K block has to be swapped into there whenever you try
>     to access/increment the sequence's value.
> 
>     This is all mighty inefficient of course, and there's been talk
>     of trying to reduce the per-sequence overhead; but I don't know
>     of anyone actively working on that.  As things stand, I think
>     having millions of sequences would be quite painful performance-
>     wise, especially if your workload were actively hitting a lot
>     of them concurrently.  It would work, for some value of "work",
>     but it wouldn't perform very well.
> 
>     Also, as multiple people mentioned already, this still wouldn't
>     guarantee gap-free sequences of ID values.
> 
>                              regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com