Portal

Hi Joe,

Here's what we can confirm:

1. Distinguishing custom indexes from xDM-managed indexes

Yes - prefix any custom index you create with USR_. During deployment, xDM's repository comparison process (comparing the current data location schema to the model edition being deployed) explicitly ignores any database object whose name starts with USR_. This is the documented mechanism for keeping user-defined indexes both clearly separated and safe from being flagged or dropped during deployment.

Example:

CREATE INDEX USR_customer_lower_email_idx ON MD_CUSTOMER (lower(EMAIL));

Indexes created by xDM itself follow xDM's own internal naming conventions (tied to the model's physical object names) rather than USR_, so as long as your custom indexes consistently use the USR_ prefix, the two sets stay unambiguous at a glance and in any DDL comparison.
Documentation link: https://www.semarchy.com/doc/semarchy-xdm/xdm/latest/Manage/deploy/deploy-a-model-edition.html#_deploy_a_model_edition

2. Impact of dropping an xDM-generated index before the next validation/deployment

xDM's deployment process works by comparing the model edition's expected schema state against the actual state of the data location, then generating the SQL script needed to reconcile the two. Since an xDM-generated index is part of that expected schema state, dropping it outside of xDM means the next validation/deployment should detect the discrepancy and include the index re-creation in the generated deployment script - the same way it would handle any other missing model-managed object.

That said, we don't have a documented edge case covering every scenario here, so testing this in your local demo environment (as you planned) is the right way to confirm the exact behavior for your version/setup before relying on it in production.

3. Best practices for managing custom indexes in an xDM data hub

• Always prefix custom indexes with USR_ so xDM ignores them during comparison/deployment.
• Never modify or drop xDM-generated indexes directly - manage them only through the model (e.g., via model-level index definitions if you need them versioned and repeatable).
• Keep an inventory of your custom indexes (script them as versioned DDL) so they can be reviewed or reapplied after major schema changes.
• Test index additions/changes in a non-production data location first, and validate a full model deployment afterward to confirm no conflicts.
• Re-evaluate custom indexes after significant model changes (new entities, changed attributes) since underlying table structures can shift.
• For performance tuning, prefer targeted indexes on columns used in custom named queries, business views, or reporting rather than broad indexing, to minimize maintenance overhead on Postgres.
  
  
  

Thanks and Best regards,
Haashim