Re: Could postgres12 support millions of sequences? (like 10 million)

On Sun, Mar 22, 2020 at 5:36 PM Christopher Browne <cbbrowne@gmail.com> wrote:

Then, on any of the tables where you need to assign sequence values, you'd need to run an "after" trigger to do the assignment.  The function that finds the sequence value is kind of analagous:

create or replace function get_next_counter (i_group integer, i_element integer) returns integer -- or bigint?

as $$

declare

  c_seqname name;

  c_query text;

  c_seqval integer;

begin

   c_seqname := 'obj_counter_' || i_group || '_'  || i_element;

   c_query := 'select nextval(' || quote_ident( c_seqname_ || ');';

or

c_query := format('select nextval(%I);', c_seqname);

You're probably calling get_next_counter() millions of times, so perhaps that code gets expanded directly into place in the trigger function.

not tested but something like:

execute format('select nextval("obj_counter_%s_%s");', i_group, i_element) into strict c_seqval;

or, more paranoidly:

execute format('select nextval(%I);', format('obj_counter_%s_%s', i_group, i_element)) into strict c_seqval;

David J.