Re: Slow alter sequence with PG10.1

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
https://www.postgresql.org/message-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