Adobe sequence overflow

[KevinQU4]

Hello,

Could someone tell me what happens when a sequence ID reaches its maximum value and that sequence is not cyclic?

What can be done when this happens?

Best regards,

[SushantTrimukheD · Accepted solution]

Hi @KevinQU4,

If you’ve created a custom sequence key for your custom schema, it’s possible that the sequence has been fully exhausted. This can lead to database errors such as “duplicate key” violations during insert operations. Such issues can disrupt processes like workflow executions, data imports, or delivery logging essentially halting any operation that relies on unique IDs (e.g., broadlog or delivery tracking).

To address this issue:

Proactive Measures:

• Reduce data retention periods for high-volume tables (for example, lowering broadlog retention from the default 180 days to better align with your send volumes).

• This helps prevent rapid ID consumption and delays sequence exhaustion.

Immediate Resolution:

• You can reset the sequence to a negative value using a command such as:
  ALTER SEQUENCE <sequence_name> RESTART WITH -2147483647;

• This extends the usable range to approximately 4 billion IDs without requiring schema changes.
• However, please consult Adobe Support before executing this, as there are minor side effects to consider — particularly regarding the transition where the zero value is ignored.

Best Practices:

• Assign dedicated sequences to custom or high-volume tables using the pkSequence attribute in the schema.

• This isolates ID consumption from shared sequences (e.g., xtkNewId).

• Implement monitoring workflows to track remaining ID capacity versus expected volume.

Additional Tip – Sequence Key Gap Analysis:

• In cases where records are frequently inserted and deleted, gaps in sequence keys can accumulate.

• Performing a gap analysis can help identify large unused key ranges.

• Once identified, you can reset the sequence to start from the beginning of that largest gap to reuse available IDs efficiently for a period of time.

Thanks,
Sushant Trimukhe

[kquintin]

Hello SushantTrimukheD,

Thank you for your very comprehensive response.

I will take your recommendations and wise advice into account.

In the event that the limit is reached (even starting from a negative ID), and we complete a full loop, what actions can we take to resolve the situation?

Best regards,

[SushantTrimukheD]

Hi @kquintin,

If the sequence key reaches its maximum value — even for negative IDs — the recommended approach is to create a new schema with a custom sequence key usingint64, and then migrate all existing data into this new schema.
Do not copy over the old primary key (sequence key) values, as the new schema will automatically generate new ones.

Alternatively, you can consider a schema extension approach by adding a supplementary int64 field instead of replacing the existing primary key.
For example, add a new attribute such as:

<attribute name="extendedId" type="int64" autopk="true"/>

This attribute would have its own dedicated sequence. You can populate it for existing records through a one-time workflow that auto-generates new values, and then use this new field for all future inserts.

Note: Alternate approach hasn’t been tested, so it should first be validated in a lower environment before applying to production.

Thanks

Sushant Trimukhe