Re: [Proposal] global sequence implemented by snowflake ID

On 11/30/23 11:56, Amit Kapila wrote:
> On Thu, Nov 30, 2023 at 6:48 AM Michael Paquier <michael@paquier.xyz> wrote:
>>
>> On Tue, Nov 28, 2023 at 02:23:44PM +0530, Amit Kapila wrote:
>>> 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/
>>
>> Thanks for pointing this out.  I've read through the patch proposed by
>> Tomas and both are independent things IMO.  The logical decoding patch
>> relies on the SEQ_LOG records to find out which last_value/is_called
>> to transfer, which is something directly depending on the in-core
>> sequence implementation.  Sequence AMs are concepts that cover much
>> more ground, leaving it up to the implementor to do what they want
>> while hiding the activity with a RELKIND_SEQUENCE (generated columns
>> included).
>>
> 
> Right, I understand that implementation-wise and or concept-wise they
> are different. It is more about the use case, see below.
> 
>> To put it short, I have the impression that one and the other don't
>> really conflict, but just cover different ground.  However, I agree
>> that depending on the sequence AM implementation used in a cluster
>> (snowflake IDs guarantee unicity with their machine ID), replication
>> may not be necessary because the sequence implementation may be able
>> to ensure that no replication is required from the start.
>>

I certainly do agree solutions like UUID or SnowflakeID may be a better
choice for distributed systems (especially in active-active case),
because there's no internal state to replicate. That's what I'd use for
such systems, I think.

As for implementation/replication, I haven't checked the code, but I'd
imagine the AM should be able to decide whether something needs to be
replicated (and how) or not. So the traditional sequences would
replicate, and the alternative sequences would not replicate anything.

> 
> This was the key point that I wanted to discuss or hear opinions
> about. So, if we wish to have some sort of global sequences then it is
> not clear to me what benefits will we get by having replication of
> non-global sequences. One thing that comes to mind is replication
> covers a subset of use cases (like help in case of failover or
> switchover to subscriber) and till the time we have some
> implementation of global sequences, it can help users.
> 

What are you going to do about use cases like using logical replication
for upgrade to the next major version? Or applications that prefer (or
have to) use traditional sequences?


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company