Best Practice for Migrating 500k+ Inactive Users Out of sys_user Without Losing Referenceability

[MohsinH]

Hi ServiceNow Community,

We have approximately 650,000 user records in the 'sys_user' table, of which around 600,000 are inactive and no longer require login or user-related platform features (roles, authentication, tracking, etc.).

However, we still need to retain these users for reference purposes (historical records, approvals, task ownership, auditing). Because of this, we cannot delete or archive them via the standard archival feature.

Our goals:
- Remove these users from the active user pool to reduce load on 'sys_user'
- Avoid extending 'sys_user' (to prevent inherited login/user behavior)
- Preserve referenceability of these users in custom applications

We are considering creating a custom table that holds these archived users and migrating the relevant fields from 'sys_user'. After that, we'd deactivate or anonymize the original records.

Questions:
1. What is the recommended approach for migrating such a large volume of records (600k+) efficiently?
2. Is scripting (e.g., background script or scheduled job) the most scalable way to handle this, or are there ServiceNow-supported tools like Transform Maps or ETL better suited for this?
3. How do others validate data integrity post-migration at scale? Are there common patterns for confirming accuracy?
4. Are there any hidden pitfalls or performance considerations when detaching large numbers of users from 'sys_user', even if references are maintained elsewhere?

Thanks in advance, and happy to clarify any details if needed!

[Brad Bowman]

The only way you can retain the reference is to retain the records.  If they're inactive, then clear the Active checkbox on these records.  There's an out of box query Business Rule that hides inactive users from non-admins, so no one will see them when searching reference fields, etc. and since they're inactive, they won't be able to login.

[MohsinH]

Thanks for the suggestion Brad!
You're definitely right that marking users as inactive and relying on the OOB query business rule can hide them from reference fields and prevent logins. That's definitely a clean solution for smaller datasets or when you want to preserve the full user record in place. However, in our case, we’re dealing with 600k inactive users, and that volume is significantly impacting:

Reference performance (for example, sys_user dropdowns and searches)

Reporting and filtering complexity

Overall table size and index efficiency

We're looking to logically archive those users by moving them to a lightweight, custom table that still allows referenceability in our custom apps, but decouples them from 'sys_user' to improve system performance and maintainability. Deactivating users alone keeps them in 'sys_user', which continues to grow and remain central to many platform functions (like ACLs, user criteria, login checks). Our goal is to preserve references, but shift the data load and reduce coupling to core platform logic.

Appreciate the input, it's the standard approach for many use cases, but we're exploring a more scalable design given our volume.