Slow alter sequence with PG10.1

why are you not using CACHE clause which is precisely for the purpose :

cache
The optional clause CACHE cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default.

On Mon, Jan 22, 2018 at 8:24 AM, Michael Krüger <michael@kruegers.email>
wrote:

Dear community,

I'm using PG10.1 on CentOS Linux release 7.4.1708 (Core) after upgrading
it from PG9.6.6. My application heavily uses sequences and requires
different increments of sequence numbers, e.g. a range of 100, 1000 or 5000
numbers, so it is not possible to set a fixed increment on a sequence that
can be used by my application.

With PG10.1 the performance has dropped seriously so that my application
becomes unusable. After investigating different aspects, I was able to
isolate the issue to be related to the sequences in Postgres 10.1.

Below shows a simple test script showing the problem:

​[...]​

On my computer I tried this code on PG9.6.6 and it executed in roughly 3
seconds.
When running it on PG10.1 it takes over 7 minutes.

Further investigation showed that the problem is related to ALTER
SEQUENCE...

I can't believe that PG10.1 was changed that dramatically without
providing a workaround or a way to switch to the old PG9.6 performance, at
least I can't find anything in the documentation.

Is this a bug?

​Without testing/confirming I'd be inclined to agree that this is a
regression for an unusual usage of sequences. Work was done to make
typical use cases of sequences more feature-full and it is quite possible
the added effort involved hurts your specific scenario. I'd expect a
hacker to eventually pick this up, confirm the observation, and provide
feedback. This seems like sufficient amount of detail to get the ball
rolling.

David J.

I also confirm this problem:

Running Michael's script on 10.1 takes 314 seconds instead of 2.3
seconds on 9.6.5.

Moreover adding some timing shows that on 10.1 the iteration execution
time grows linearly with each iteration. (!!)

If we remove ALTER SEQUENCE, the difference is only 2.5 times (5 seconds
for 10.1 and 2 - for 9.6.5), and the linear growth effect is not observed.

Removing advisory locks saves ~ 200ms in both cases, and still 9.6.5.
seems faster.

Ivan Panchenko

Postgres Professional
the Russian PostgreSQL Company
+79104339846

22.01.2018 21:55, David G. Johnston пишет:

Michael Krüger wrote:

I'm using PG10.1 on CentOS Linux release 7.4.1708 (Core) after upgrading it from PG9.6.6.
My application heavily uses sequences and requires different increments of sequence numbers,
e.g. a range of 100, 1000 or 5000 numbers, so it is not possible to set a fixed increment
on a sequence that can be used by my application.

With PG10.1 the performance has dropped seriously so that my application becomes unusable.
After investigating different aspects, I was able to isolate the issue to be related to
the sequences in Postgres 10.1.

Below shows a simple test script showing the problem:
-- 1) Create a sequence
CREATE SEQUENCE my_sequence_1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE START WITH 1 CYCLE;

-- 2) Create a function that allows to request a number range
CREATE OR REPLACE FUNCTION multi_nextval(

[...]

perform pg_advisory_lock(lock_id);
execute 'ALTER SEQUENCE ' || use_seqname || ' INCREMENT BY ' || use_increment::text;
reply := nextval(use_seqname);
execute 'ALTER SEQUENCE ' || use_seqname || ' INCREMENT BY 1';
perform pg_advisory_unlock(lock_id);

[...]

Further investigation showed that the problem is related to ALTER SEQUENCE...

I can't believe that PG10.1 was changed that dramatically without providing
a workaround or a way to switch to the old PG9.6 performance, at least I
can't find anything in the documentation.

Is this a bug?

See https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3d79013b970d4cc336c06eb77ed526b44308c03e

Make ALTER SEQUENCE, including RESTART, fully transactional.

Previously the changes to the "data" part of the sequence, i.e. the
one containing the current value, were not transactional, whereas the
definition, including minimum and maximum value were. That leads to
odd behaviour if a schema change is rolled back, with the potential
that out-of-bound sequence values can be returned.

To avoid the issue create a new relfilenode fork whenever ALTER
SEQUENCE is executed, similar to how TRUNCATE ... RESTART IDENTITY
already is already handled.

This fixed a bug introduced in v10 by this change:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1753b1b027035029c2a2a1649065762fafbf63f3

Add pg_sequence system catalog

Move sequence metadata (start, increment, etc.) into a proper system
catalog instead of storing it in the sequence heap object. This
separates the metadata from the sequence data. Sequence metadata is now
operated on transactionally by DDL commands, whereas previously
rollbacks of sequence-related DDL commands would be ignored.

Previous to that change, ALTER SEQUENCE modified the values in the
sequence data file in place --- different from an UPDATE to a regular
table, which would write a new table row.

Consequently, the changes ALTER SEQUENCE were not rolled back in
9.6 and before.

After the change, the kinds of ALTER SEQUENCE that changed the values
in pg_sequence were transactional, but ALTER SEQUENCE ... RESTART,
which changes "last_value", which is stored in the data file, was not.

See the example in
/messages/by-id/20170522154227.nvafbsm62sjpbxvd@alap3.anarazel.de

The solution was to create a new filenode whenever ALTER SEQUENCE is run,
which is the cause for the performance regression.

Now maybe the fix is really not perfect, but what else could be done?
Introducing row versions for sequences is wrong, because all transactions
have to see the same value for a sequence, otherwise it could not
serve its purpose.

I think that having transactional ALTER SEQUENCE is worth the price
of a slowdown for ALTER SEQUENCE.

I'd say that your function abuses ALTER SEQUENCE, and it would be better to
rewrite it.

The best solution if you need a gap-less batch of sequence values is
in my opinion to use a sequence with defined START WITH 10000 INCREMENT BY 10000
and get the starting value for the next batch of 10000 with
SELECT nextval('seq') - 9999.
That will waste some values if you don't need all 10000 values, but it
is very efficient and does not require a lock at all.

Yours,
Laurenz Albe

On 01/22/2018 07:24 AM, Michael Krüger wrote:

Dear community,

I'm using PG10.1 on CentOS Linux release 7.4.1708 (Core) after upgrading
it from PG9.6.6. My application heavily uses sequences and requires
different increments of sequence numbers, e.g. a range of 100, 1000 or
5000 numbers, so it is not possible to set a fixed increment on a
sequence that can be used by my application.

With PG10.1 the performance has dropped seriously so that my application
becomes unusable. After investigating different aspects, I was able to
isolate the issue to be related to the sequences in Postgres 10.1.

Below shows a simple test script showing the problem:
-- 1) Create a sequence
CREATE SEQUENCE my_sequence_1 INCREMENT BY 1 MINVALUE 1 NO MAXVALUE
START WITH 1 CYCLE;

-- 2) Create a function that allows to request a number range
CREATE OR REPLACE FUNCTION multi_nextval(
use_seqname text,
use_increment integer)
    RETURNS bigint
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE
    reply int8;
    lock_id int4;
BEGIN
    SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname =
split_part(use_seqname, '.', 2);
    perform pg_advisory_lock(lock_id);
    execute 'ALTER SEQUENCE ' || use_seqname || ' INCREMENT BY ' ||
use_increment::text;
    reply := nextval(use_seqname);
    execute 'ALTER SEQUENCE ' || use_seqname || ' INCREMENT BY 1';
    perform pg_advisory_unlock(lock_id);
    return reply - use_increment + 1;
END;
$BODY$;

Not entirely sure I understand how the batching above works, still maybe
something like this:

CREATE OR REPLACE FUNCTION public.multi_nextval(use_seqname text,
use_increment integer)
RETURNS bigint
LANGUAGE plpgsql
AS $function$
DECLARE
reply int8;
lock_id int4;
seq_idx int8 :=nextval(use_seqname);
BEGIN
SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname =
split_part(use_seqname, '.', 2);
perform pg_advisory_lock(lock_id);
perform setval(use_seqname, seq_idx + use_increment, 't');
reply := nextval(use_seqname);
perform pg_advisory_unlock(lock_id);
return reply;
END;
$function$

On an older laptop this does the 20000 loops in about 1.6 secs.

-- 3) Loop 20000 times and request 5000 values each time
DO $$
DECLARE
--
  i_index integer;
  i_value bigint;
BEGIN
  FOR i_index IN select * from generate_series(1,20000,1)
  LOOP
    SELECT multi_nextval('my_sequence_1',5000) INTO i_value ;
    if (i_index % 250 = 0) THEN
      raise notice 'Loop: % - NextVal: %', i_index, i_value;
    end if;
  END LOOP;
END$$;

On my computer I tried this code on PG9.6.6 and it executed in roughly 3
seconds.
When running it on PG10.1 it takes over 7 minutes.

Further investigation showed that the problem is related to ALTER
SEQUENCE...

I can't believe that PG10.1 was changed that dramatically without
providing a workaround or a way to switch to the old PG9.6 performance,
at least I can't find anything in the documentation.

Is this a bug?

Thanks in advance,
Michael

--
Email:   michael@kruegers.email
Mobile: 0152 5891 8787

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian and all,

I do not fully understand the reasoning of making sequences transactional
in the first place. As far as I know its also not done on Oracle or SQL
Server, but maybe I'm even wrong on that. What I question is a change in
behavior of existing functionality with such big impact, without config
option to restore old behavior, or maybe to have another variant in place
like:

create sequence [non] [transactional] to at least be able to decide which
variant to use. Maintain the performance of the old behavior, or if
transactions safety is needed, the new behavior with the performance impact
but more safety if needed.

I will try if Adrians proposal does the trick for my application. Sounds
promising, thanks.

Regards,
Michael

Adrian Klaver <adrian.klaver@aklaver.com> schrieb am Mo., 22. Jan. 2018 um
22:29 Uhr:

Hello all,

after changing the function to this:

CREATE OR REPLACE FUNCTION multi_nextval(
use_seqname text,
use_increment integer)
RETURNS bigint
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
reply int8;
lock_id int4;
seq_idx int8;
BEGIN
SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname =
split_part(use_seqname, '.', 2);
perform pg_advisory_lock(lock_id);

seq_idx :=nextval(use_seqname);
perform setval(use_seqname, seq_idx + use_increment - 1, 't');

perform pg_advisory_unlock(lock_id);
return seq_idx;
END;
$BODY$;

I do get a total execution time of Time: 5922,428 ms (00:05,922) - much
better than before.

Is there any drawback to use setval compared to the ALTER SEQUENCE which I
have used before? If not, then this could be the work around to go with as
it has a similar performance to the original function.

I guess - the reason I'm a bit disappointed from the new behavior is that
we have used Postgresql for more than 10 years and it has never let us
down. We have been able to improve our product with every new release of
Postgres. This is the first time for me that a new release of Postgres
caused some severe headaches among our customers.
If you all agree that this changed function should be equivalent to the
original one, then its at least an easy fix.

Thank you all for your fast responses.

Regards,
Michael

Michael Krüger <michael@kruegers.email> schrieb am Mo., 22. Jan. 2018 um
23:11 Uhr:

On 1/22/18 17:11, Michael Krüger wrote:

I do not fully understand the reasoning of making sequences
transactional in the first place.

It was sequence DDL that was made transactional. Sequence use is still
nontransactional.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Mon, Jan 22, 2018 at 3:47 PM, Michael Krüger <michael@kruegers.email>
wrote:

Is there any drawback to use setval compared to the ALTER SEQUENCE which I
have used before? If not, then this could be the work around to go with as
it has a similar performance to the original function.

​Not that I can think of.
​

I guess - the reason I'm a bit disappointed from the new behavior is that
we have used Postgresql for more than 10 years and it has never let us
down. We have been able to improve our product with every new release of
Postgres. This is the first time for me that a new release of Postgres
caused some severe headaches among our customers.

OK...but I am not surprised as to how this played out. The system doesn't
expect ALTER SEQUENCE to be executed frequently and your example doesn't
argue for it since setval is indeed provided and can handle the situation
where you wish to skip to some other sequence number for subsequent calls.

Disruption of existing code is unavoidable since two of the goals of this
project are innovation and stability. That your customers discovered this
particular instance of disruption is more on you than the project - major
upgrades, especially the first few patch releases, don't end up seeing a
wide variety of unusual setups (like this one) until they hit production
since, as it seems here, people with those unusual setups are not putting
it through its paces during the beta release period.

David J.

On Mon, Jan 22, 2018 at 04:40:54PM -0700, David G. Johnston wrote:

I guess - the reason I'm a bit disappointed from the new behavior is that
we have used Postgresql for more than 10 years and it has never let us
down. We have been able to improve our product with every new release of
Postgres. This is the first time for me that a new release of Postgres
caused some severe headaches among our customers.

OK...but I am not surprised as to how this played out. The system doesn't
expect ALTER SEQUENCE to be executed frequently and your example doesn't
argue for it since setval is indeed provided and can handle the situation
where you wish to skip to some other sequence number for subsequent calls.

Disruption of existing code is unavoidable since two of the goals of this
project are innovation and stability. That your customers discovered this
particular instance of disruption is more on you than the project - major
upgrades, especially the first few patch releases, don't end up seeing a
wide variety of unusual setups (like this one) until they hit production
since, as it seems here, people with those unusual setups are not putting
it through its paces during the beta release period.

David J.

Hi,

I am not trying to be snarky, but it really behooves users to test their
systems with a new release before moving to it and not let their unwitting
customers be their debug team. :(

Regards,
Ken

On 01/22/2018 02:47 PM, Michael Krüger wrote:

Hello all,

after changing the function to this:

CREATE OR REPLACE FUNCTION multi_nextval(
use_seqname text,
use_increment integer)
    RETURNS bigint
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
DECLARE
    reply int8;
    lock_id int4;
    seq_idx int8;
BEGIN
    SELECT oid::int4 INTO lock_id FROM pg_class WHERE relname =
split_part(use_seqname, '.', 2);
    perform pg_advisory_lock(lock_id);

    seq_idx :=nextval(use_seqname);
    perform setval(use_seqname, seq_idx + use_increment - 1, 't');

    perform pg_advisory_unlock(lock_id);
    return seq_idx;
END;
$BODY$;

I do get a total execution time of Time: 5922,428 ms (00:05,922) - much
better than before.

Is there any drawback to use setval compared to the ALTER SEQUENCE which
I have used before? If not, then this could be the work around to go
with as it has a similar performance to the original function.

I guess - the reason I'm a bit disappointed from the new behavior is
that we have used Postgresql for more than 10 years and it has never let
us down. We have been able to improve our product with every new release

Well the nature of major version releases is that they can break
backwards compatibility. This is one of the reasons there is 5 year
community support on versions, time to develop a migration plan. I have
been caught by changes, before e.g. the 8.3 change in casting rules, a
later change that made plpythonu use Python rules for truthfulness
instead of SQL, etc. You seem to have had a run of good luck. Going
forward I would assume a major release will contain breaking changes and
test thoroughly.

of Postgres. This is the first time for me that a new release of
Postgres caused some severe headaches among our customers.
If you all agree that this changed function should be equivalent to the
original one, then its at least an easy fix.

Thank you all for your fast responses.

Regards,
Michael

--
Adrian Klaver
adrian.klaver@aklaver.com

Hello all,

I think a good alternative was found and seems to be working fine. I really
do appreciate all the help and feedback.
Many thanks.

Regards,
Michael

Adrian Klaver <adrian.klaver@aklaver.com> schrieb am Di., 23. Jan. 2018 um
02:12 Uhr: