Managing Primary Key Exhaustion and Index Rotation in Adobe Campaign Classic

In Adobe Campaign Classic, primary keys play a crucial role in managing and organizing data, especially when dealing with large volumes of customer information and campaign data. Not often but it happens that we “run out of the primary keys”. This is when we apply rotation of indexes

Primary keys are unique identifiers for each record in a database, ensuring data integrity and enabling efficient data retrieval and manipulation. They are particularly important in scenarios involving data rotation, where maintaining unique and consistent identifiers is crucial.

Understanding Primary Keys in Adobe Campaign:

• Unique Identifier: A primary key in Adobe Campaign is a field (or a combination of fields) that uniquely identifies each record in a database table. This could be an email address, customer ID, or a unique transaction number.

Most built‑in tables in ACC use an auto‑generated 32‑bit integer as their key; the value comes from a database sequence (often xtkNewId) and is inserted automatically when new records are created

• Data Integrity: Primary keys prevent duplicate records and maintain data accuracy, which is essential for effective campaign management and analysis.
• Indexing: They are often used for indexing, making data retrieval faster and more efficient, especially in large datasets.

Why sequences can run out of IDs

A 32‑bit signed integer provides about 2.14 billion possible positive values. When the sequence used to generate primary keys reaches its maximum value (~2 147 483 647), it cycles back to 0. If older records are still in the table, the sequence will begin generating IDs that already exist, causing unique‑key violations. On high‑volume tables such as broadLog and trackingLog, this exhaustion can happen quickly. Adobe’s data‑model guidelines warn that a customer sending six billion emails a year with a log retention of 180 days would consume all available IDs in about four months. Once the sequence cycles, the platform cannot insert new records and delivery preparation fails, effectively stopping campaign execution.

Preventive measures

1. Monitor ID consumption. ACC’s default sequence (xtkNewId) is shared by many tables. You can define dedicated sequences for high‑volume tables by specifying the pkSequence attribute in the schema. Monitoring ID usage through a workflow or database script helps identify when a sequence is approaching its limits.
2. Limit log retention. Delivery and tracking logs are purged automatically after 180 days by default. Adobe recommends keeping fewer than one billion records (about 50 % of the available IDs) in broad and tracking log tables. If your volume is high, reduce the retention period to 90 days or export older logs to a data warehouse. Custom tables are not purged automatically, so you must build your own cleanup workflows.
3. Avoid unnecessary logging. High‑frequency workflows that generate many interim logs can accelerate sequence consumption. Simplify workflows where possible, avoid excessive logging and use continuous deliveries rather than creating many one‑off deliveries.
4. Use negative IDs for custom sequences. When creating custom tables, always assign a dedicated sequence rather than sharing the default one. By default, a custom sequence ranges from +1 000 to +2.1 billion; you can enable negative IDs to double the addressable space (≈4 billion values). Crossing from negative to positive will skip 0 because ACC ignores records with a primary‑key value of 0.

What to do when the sequence exhausts

Sometimes the sequence cycles despite preventive measures—for example, when logs were not purged early enough. Adobe’s maintenance documentation notes that once the sequence exceeds roughly 2.14 billion, it resets to 0 and issues begin. If this occurs, there are two options:

1. Purge enough old data so that new IDs will not collide with existing ones. This may require exporting historical records and deleting them from ACC.
2. Rotate the sequence into the negative range. This technique avoids collision by issuing negative IDs, which do not conflict with the positive IDs already in the table. Adobe’s application‑objects guide explicitly states that if the sequence has already cycled, “the best solution is to switch to negative IDs, starting from –2 147 483 648”. A community thread on broadLog sequence exhaustion provides the SQL commands to perform this rotation:

-- Reset the sequence to start at the minimum negative value
ALTER SEQUENCE <sequence_name>
    MINVALUE -2147483647
    MAXVALUE 2147483647
    RESTART WITH -2147483647
    CACHE -2147483647
    CYCLE;

-- Ensure the sequence increments by 1
ALTER SEQUENCE <sequence_name> INCREMENT BY 1;

Running these statements in your database resets the sequence so that the next generated primary key will be –2 147 483 647. The sequence will then count upward towards zero and into the positive range. Because ACC skips the value 0, one ID is lost when crossing from negative to positive. This rotation technique has been reported to work successfully in the past on many projects.

However, you should only execute it after backing up the database and verifying that no existing records use negative primary keys. Coordination with your database administrator and, if possible, Adobe support is strongly recommended.

Best practices for ongoing health

• Regularly monitor sequences. Establish monitoring that alerts administrators when a sequence value approaches a threshold (for example, 1.5 billion). At that point you can adjust retention or prepare to rotate the sequence.
• Design for scalability. Use numeric keys and dedicated log tables for each custom recipient table. Avoid creating overly large tables with many columns and maintain relational integrity using numeric IDs.
• Implement purge workflows for custom tables. ACC’s standard cleanup only removes tracking and broad logs. All other tables require custom purge processes.

Sources

1. experienceleague.adobe.com

Please leave this field empty

Oh hi there
I have a FREE e-book for you.

Sign up now to get an in-depth analysis of Adobe and Salesforce Marketing Clouds!

We don’t spam! Read our privacy policy for more info.

Check your inbox or spam folder to confirm your subscription.

Share With Others