Re: [Proposal] global sequence implemented by snowflake ID

On 12/1/23 07:15, Amit Kapila wrote:
> On Thu, Nov 30, 2023 at 5:21 PM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
>>
>> On 11/30/23 11:56, Amit Kapila wrote:
>>
>>>
>>> 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?
>
>
> As per my understanding, they should work as it is when using a global
> sequence. Just for the sake of example, considering we have a
> same-name global sequence on both pub and sub now it should work
> during and after major version upgrades.
>

Sequential IDs have significant benefits too, it's simply not that these
global sequences are universally superior. For example, with sequential
sequences you often get locality, because recent data have about the
same sequence values. With global sequences that's not really the case,
because they are often based on randomness, which massively limits the
access locality. (Yes, some variants may maintain the ordering, others
don't.)

>>
>> Or applications that prefer (or
>> have to) use traditional sequences?
>>
>
> I think we have to suggest them to use global sequence for the use
> cases where they want those to work with logical replication use
> cases. Now, if still users want their existing sequences to work then
> we can probably see if there is a way to provide an option via Alter
> Sequence to change it to a global sequence.
>

I really don't know how that would work e.g. for existing applications
that have already designed the schema long time ago. Or for systems that
use 32-bit sequences - I'm not aware of global sequences that narrow.

regards

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