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

[pabloa98 <pabloa98@xxxxxxxxx>]

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

On Sun, 22 Mar 2020 at 17:54, pabloa98 <pabloa98@xxxxxxxxx> wrote:

So the question may actually be:

How do we improve our locking code, so we don't have to spawn millions
of sequences?

What is the locking method you are using?

I am not using locking with the million sequence solution. I do not want something that locks because the problems described below

I prefer the solution generates a gap (skip a couple of numbers) and not using locks.

If you can cope with gaps, that's a good thing.  That means that some kind of usage of sequences would be compatible with your application.

Indeed

 

 

> The lock part is because we solved a similar problem with a counter by
> row locking the counter and increasing it in another part of the
> database. The result is that all the queries using that table are queued
> by pair (group, element) that is not that bad because we are not
> inserting thousands of rows by second. Still is killing cluster
> performance (but performance is still OK from the business point of
> view). The problem using locks is that they are too sensitive to
> developer errors and bugs. Sometimes connected clients aborts and the
> connection is returned to the pool with the lock active until the
> connection is closed or someone unlocks the row. I would prefer to have
> something more resilient to developers/programming errors, if possible.
>

Now I read this paragraph, I realize I was not clear enough.

I am saying we do not want to use locks because of all the problems described.

Cool, that means you have been thinking through similar considerations to what others have in mind, and it doesn't sound like there are dramatically different understandings.

Let's circle back to the schema that you provided...

CREATE TABLE counter(

group INT NOT NULL,

element INT NOT NULL,

seq_number INT NOT NULL default 0,

CONSTRAINT PRIMARY KEY (group, element)

);

That's not a table using the sequential values; that's what you imagining you could have as a way of referencing the sequences, right?

I would represent it slightly differently...

create table counter (

   group integer not null,

   element integer not null,

   sequence_name name,

   primary key (group, element)

);

Arguably, there's no need for sequence_name altogether, as it's never directly referenced by anything.

And then have a function that might fire upon creation of new entries in this table.

create or replace function generate_sequence (i_group integer, i_element integer) returns name

as $$

declare

  c_seqname name;

  c_query text;

begin

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

   c_query := 'create sequence if not exists ' || c_seqname || ';';

   execute c_query;

   update counter set sequence_name = c_seqname where group = i_group and element = i_element;

   return c_seqname;

end

$$ language plpgsql;

You'd need a trigger function to put onto the table that runs this function; that is left as an exercise for the reader.

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_ || ');';

   execute c_query into c_seqval;

   return c_seqval;

end

$$ language plpgsql;

Again, that just grabs a nextval(); you'd need to execute this inside a trigger function called ON INSERT on any of the tables that need sequence values assigned.

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

I like this approach.

When I have a medium number of sequence I will report how it behaves. It will take some time though.

Pablo