Re: [Proposal] global sequence implemented by snowflake ID

On Thu, Nov 23, 2023 at 4:15 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Thu, Nov 23, 2023 at 10:18:59AM +0000, Hayato Kuroda (Fujitsu) wrote:
> > * Implement as a variant of sequence access method. I found that sequence AM was
> > proposed many years ago [5], but it has not been active now. It might be a
> > fundamental way but needs a huge works.
>
> Well, that's what I can call a timely proposal. I've been working
> this week on a design for sequence AMs, while considering the cases
> that the original thread wanted to handle (spoiler: there are a lot of
> pieces in the original patch that are not necessary, other parts are
> incorrect like dump/restore), what you are trying to do here, and more
> complex scenarios in terms of globally-distributed sequences.
>

It is interesting to see you want to work towards globally distributed
sequences. I think it would be important to discuss how and what we
want to achieve with sequences w.r.t logical replication and or
active-active configuration. There is a patch [1] for logical
replication of sequences which will primarily achieve the failover
case, i.e. if the publisher goes down and the subscriber takes over
the role, one can re-direct connections to it. Now, if we have global
sequences, one can imagine that even after failover the clients can
still get unique values of sequences. It will be a bit more flexible
to use global sequences, for example, we can use the sequence on both
nodes at the same time which won't be possible with the replication of
sequences as they will become inconsistent. Now, it is also possible
that both serve different use cases and we need both functionalities
but it would be better to have some discussion on the same.

Thoughts?

[1] - https://commitfest.postgresql.org/45/3823/

--
With Regards,
Amit Kapila.