From: | pabloa98 <pabloa98(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Christopher Browne <cbbrowne(at)gmail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Rob Sargent <robjsargent(at)gmail(dot)com>, "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Could postgres12 support millions of sequences? (like 10 million) |
Date: | 2020-03-23 05:37:35 |
Message-ID: | CAEjudX6dTZcMLXviJ9d5D0_cMEZ-HQNNU4WCxzVBHfigtH9G5g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Mar 22, 2020 at 6:58 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Sun, Mar 22, 2020 at 5:36 PM Christopher Browne <cbbrowne(at)gmail(dot)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.
>
>
I will add this to the previous solution.
Thank you all for all the ideas and suggestions.
I hope there will be int he future sequence data type and support and
optimizations of sequences in postgresql to deal with a lot of them. They
will be very useful no only for me :) but problems like monitoring and
counting things by the zillions.
Pablo
From | Date | Subject | |
---|---|---|---|
Next Message | pinker | 2020-03-23 10:24:48 | Loading 500m json files to database |
Previous Message | Sonam Sharma | 2020-03-23 05:36:32 | Postgres cluster setup |