Introduction
Enterprise applications frequently manage millions of customer profiles, transaction histories, case records, interaction logs, and operational events. As businesses grow, the volume of application data can exceed 100 million records, creating significant challenges for database performance, query execution, reporting, memory utilization, and application scalability.
In Pega, managing this volume requires more than increasing database capacity or adding application server nodes. The application must be designed to retrieve the right data efficiently, minimize unnecessary processing, and maintain predictable performance as the dataset grows.
Designing Pega applications for 100+ million records requires a combination of efficient data modeling, optimized database queries, appropriate indexing, scalable processing, and disciplined performance monitoring.
This article explores the architectural decisions, Pega-specific capabilities, and optimization strategies that help applications handle large datasets reliably.
1. Understanding the Challenges of Large-Scale Data in Pega
A Pega application that performs well with 100,000 records may behave very differently when the same table contains 100 million records.
Consider an application that stores customer information, transaction history, and case records.
| Data category | Example data | Potential challenge |
| Customer data | Customer profiles and contact details | Slow searches and duplicate lookups |
| Case data | Work objects and assignments | Expensive worklist queries |
| Transaction data | Payments and financial operations | High-volume inserts and updates |
| Interaction history | Calls, emails, and customer events | Rapid table growth |
| Audit data | Historical changes and processing events | Increasing storage and reporting costs |
As these tables grow, queries that were previously inexpensive can become costly.
For example, a report that retrieves 50 matching records from a table containing 100,000 records might perform adequately. The same report can become a bottleneck when it processes a much larger dataset without suitable indexes or selective filtering.
The actual impact depends on the query plan, indexes, data distribution, database platform, hardware, and workload.
Common performance symptoms
Large-scale Pega applications may experience:
- Slow case searches.
- Long-running reports.
- Increased database CPU utilization.
- Excessive database reads.
- High response times during peak traffic.
- Slow data-page loading.
- Large memory consumption during data processing.
- Database lock contention.
- Backlogs in asynchronous processing.
- Timeouts during integrations and bulk operations.
The key architectural principle is to identify which operations become expensive as the dataset grows, rather than assuming that record volume alone is the problem.
2. Design the Data Model Before the Dataset Grows
One of the most important decisions is determining where different categories of data should be stored.
A common mistake is placing every type of information into the same operational table, even when the information has different access patterns and retention requirements.
For example, a customer service application might store:
- Current customer information.
- Active cases.
- Completed cases.
- Transaction history.
- Audit events.
- Reporting summaries.
These datasets serve different purposes and should be modeled according to their expected usage.
Separate operational and historical data
Operational data supports current business processing. Historical data is primarily retained for auditing, analytics, or occasional retrieval.
A conceptual architecture could look like this:
Pega Application
|
+———–+———–+
| | |
v v v
Customer Active Historical
Data Cases Data
| | |
v v v
Operational Database Archive / Analytics
This is a conceptual separation, not a requirement to create three physically independent databases.
Depending on the application, the separation may be implemented through different tables, data classes, retention policies, database schemas, or external data platforms.
Benefits of separating data by purpose
- Reduces the amount of data scanned by common operational queries.
- Makes retention policies easier to implement.
- Helps prevent historical data from unnecessarily affecting operational workloads.
- Supports independent reporting and archival strategies.
- Makes performance bottlenecks easier to isolate.
Important: Moving historical data must preserve required business relationships, audit requirements, and case accessibility. Historical records should not be removed merely to improve query performance.
3. Optimize Pega Class Mapping and Database Tables
Pega uses class definitions and database mappings to determine where application data is stored and how it is accessed.
For large applications, developers should understand the relationship between the class structure and the underlying database tables.
For example, a case class may inherit from a work class, while a data class represents customer information.
Conceptually:
Pega Application
|
+—- Work Classes
| |
| +—- Active Cases
| +—- Completed Cases
|
+—- Data Classes
| |
| +—- Customer
| +—- Transaction
|
+—- History / Audit Data
The actual storage arrangement depends on class mapping, inheritance, and the application’s database configuration.
Avoid unnecessary data duplication
Duplicating large amounts of data across tables can increase storage requirements and create consistency problems.
Instead, determine which attributes must be available for operational processing and which can be retrieved from their authoritative data source.
However, normalization is not an absolute rule for every workload. Carefully selected denormalization or materialized summaries can improve performance when they reduce expensive repeated operations.
The objective is to balance consistency, storage, and query efficiency.
4. Use Database Indexing Strategically
Database indexing is one of the most important considerations when handling 100 million records.
An index helps the database locate matching records without necessarily scanning the entire table.
Consider a customer transaction table containing:
TransactionID
CustomerID
TransactionDate
TransactionStatus
Amount
Suppose users frequently search transactions by customer and date.
A suitable composite index might involve:
(CustomerID, TransactionDate)
Whether this index is effective depends on the database platform, query predicates, column selectivity, ordering requirements, and execution plan.
Choose indexes based on actual queries
Before creating an index, identify:
- The columns used in filtering conditions.
- The columns used for joins.
- The columns used for sorting.
- The frequency of the query.
- The selectivity of the indexed columns.
- The additional cost of maintaining the index during inserts and updates.
For example, an index on a frequently queried customer identifier may be valuable. An index on a low-selectivity status column alone may be less effective, depending on the database and workload.
Composite index considerations
Suppose a report frequently executes a query similar to:
SELECT TransactionID, TransactionDate, Amount
FROM CustomerTransaction
WHERE CustomerID = ?
AND TransactionDate >= ?
ORDER BY TransactionDate DESC;
A composite index beginning with CustomerID, followed by TransactionDate, may support this access pattern.
The database optimizer must still determine whether using the index is beneficial.
Pega-specific consideration: Database indexes for Pega-managed tables should be designed and maintained using supported Pega and database administration practices. Do not assume that adding an index directly to a physical table is always sufficient or appropriate for every deployment model.
5. Avoid Loading Millions of Records into Memory
A critical scalability mistake is attempting to retrieve an entire dataset when the application only needs a small subset.
For example, consider a data page that loads all customer records into memory.
If the table contains 100 million records, this approach can cause excessive database activity, memory pressure, long loading times, and potentially application failures.
Instead, design the application to retrieve only the records required by the current operation.
Use pagination and bounded retrieval
A conceptual example is:
User Requests Customer Search
|
v
Apply Search Criteria
|
v
Execute Database Query
|
v
Retrieve First Page
|
v
Display Matching Results
|
v
Retrieve Additional Pages as Needed
If the user needs 50 records, retrieve a bounded result set rather than loading millions of records.
For large tables, pagination should be implemented carefully.
Offset-based pagination can become expensive when the database must skip a large number of rows. For deep pagination, keyset pagination or another database-appropriate strategy may perform better.
For example, keyset pagination can retrieve the next page using the last observed transaction date and a unique transaction identifier.
SELECT TransactionID, TransactionDate, Amount
FROM CustomerTransaction
WHERE CustomerID = ?
AND (
TransactionDate < ?
OR (
TransactionDate = ?
AND TransactionID < ?
)
)
ORDER BY TransactionDate DESC, TransactionID DESC;
The exact implementation and index requirements depend on the database platform and query design.
Pega implementation considerations
When implementing large searches in Pega:
- Use appropriately filtered reports or data sources.
- Configure table pagination instead of loading every result.
- Avoid unnecessary clipboard loading.
- Return only the fields required by the user interface.
- Use database-side filtering and sorting when supported.
- Avoid repeatedly retrieving the same large dataset.
- Validate how the underlying report or data source executes its query.
Pagination improves scalability only when the underlying data retrieval is also efficient.
6. Design Data Pages for Large Datasets
Data Pages provide a mechanism for retrieving and sharing data within Pega applications.
However, an incorrectly configured Data Page can become a performance bottleneck.
Consider a Data Page that retrieves customer information for multiple case types.
If every request loads a large customer dataset, the application may consume unnecessary resources.
Select the appropriate data scope
Pega Data Pages support different scopes, including:
- Thread.
- Requestor.
- Node.
The correct scope depends on the data, its lifetime, the intended sharing behavior, and the Pega version’s supported configuration.
For example, relatively stable reference data may be suitable for broader reuse, while user-specific information or frequently changing transaction data may require a narrower scope.
Understand refresh behavior
A Data Page can have a refresh strategy based on application requirements.
Important considerations include:
- Whether the data changes frequently.
- Whether stale data is acceptable.
- How expensive the source query is.
- How many requests reuse the page.
- Whether refreshes can create simultaneous load spikes.
A shared Data Page can reduce repeated retrievals, but it should not be used to cache enormous datasets unnecessarily.
Recommended approach
For large-scale data:
- Use parameterized Data Pages for selective retrieval.
- Avoid loading complete tables into a Data Page.
- Choose scope according to data ownership and sharing requirements.
- Configure refresh behavior based on data freshness requirements.
- Monitor database calls and memory usage.
- Verify that cache reuse actually reduces work without returning inappropriate stale data.
7. Optimize Reports and List Views
Reports are a frequent source of performance problems in large Pega applications.
A report that retrieves many records, performs expensive joins, or sorts a large intermediate result can become costly as the underlying tables grow.
Apply selective filtering
Consider a report that retrieves every completed case:
SELECT *
FROM CaseTable
WHERE Status = ‘Resolved’;
If millions of cases satisfy this condition, the database may need to process a large result set.
A more selective report might use a business requirement such as:
SELECT CaseID, CustomerID, ResolvedDate
FROM CaseTable
WHERE Status = ‘Resolved’
AND ResolvedDate >= ?
AND ResolvedDate < ?;
The second query narrows the requested data and returns fewer columns.
The benefit depends on the query plan, available indexes, and the proportion of rows matching the conditions.
Avoid unnecessary columns
Returning every column can increase database I/O, network traffic, and application memory usage.
Retrieve only the fields required by the report or user interface.
Review joins
Reports involving several large tables can become expensive when joins are poorly designed.
Review:
- Join conditions.
- Indexes on join columns.
- One-to-many relationships.
- Duplicate rows.
- Filtering applied before or after joins.
- Sorting and aggregation operations.
Avoid expensive count operations
A total-record count can itself become expensive for a large filtered dataset.
If a user interface displays a result count, determine whether the underlying implementation performs a separate count query and whether the count is actually necessary.
Do not replace an accurate count with an approximate value unless the business requirement permits it.
8. Design Case Management for High Record Volumes
Pega case management introduces additional considerations because cases involve more than a single database record.
Depending on the case type and configuration, processing may involve assignments, history, related data, child cases, and other supporting records.
A high-volume application should therefore consider the complete lifecycle of a case.
Avoid unnecessary case creation
Creating a separate case for every minor event may introduce unnecessary overhead when the event does not require independent workflow management.
However, case granularity should reflect business requirements, auditability, ownership, and lifecycle needs.
Control historical data growth
Completed cases may accumulate over several years.
Define retention and archival strategies based on legal, operational, and business requirements.
For example:
New Case
|
v
Active Processing
|
v
Resolved Case
|
v
Retention Period
|
v
Archive / Approved Retention Action
The archive strategy must preserve access to historical information where required.
Optimize worklists and work queues
Worklists and work queues often support operational queries.
If these queries scan large datasets or retrieve unnecessary assignment information, response times can increase.
Investigate:
- Filtering by assignment status.
- Assignment ownership.
- Work queue selection.
- Relevant indexes.
- Result pagination.
- Query plans.
- The cost of sorting and counting assignments.
A well-designed case lifecycle can reduce unnecessary work throughout the application’s lifetime.
9. Use Asynchronous Processing for Large Workloads
Some operations should not execute entirely within a user-facing request.
Examples include:
- Processing large transaction batches.
- Generating extensive reports.
- Synchronizing external systems.
- Sending high-volume notifications.
- Performing bulk updates.
- Importing customer data.
Executing such operations synchronously can increase request duration and consume application-server threads.
Pega provides asynchronous processing capabilities, including Queue Processors and Job Schedulers, that can support appropriate background workloads.
Queue-based processing
A simplified architecture is:
Incoming Records
|
v
Create Work Items
|
v
Queue Processor
|
+———+———+
| | |
v v v
Worker 1 Worker 2 Worker 3
| | |
+———+———+
|
v
Persist Results
This is a conceptual illustration; actual concurrency and distribution depend on the Pega configuration and infrastructure.
Benefits
- Keeps long-running operations out of interactive requests.
- Supports controlled concurrency.
- Allows retry handling for eligible failures.
- Helps distribute processing across available resources.
- Makes workload monitoring easier.
Important considerations
Background processing does not eliminate the cost of processing large datasets.
A poorly designed queue processor can still overload the database, external APIs, or application servers.
Use bounded batches, appropriate concurrency, error handling, and idempotent processing where necessary.
For large bulk operations, ensure that retries do not accidentally duplicate business transactions.
10. Partition and Archive Data When Appropriate
At 100 million records, data partitioning and archival may become relevant, but neither should be introduced automatically based only on record count.
Partitioning
Database partitioning divides a large table into manageable physical or logical segments according to a database-specific strategy.
Possible partition keys include:
- Transaction date.
- Event date.
- Region.
- Business category.
For example:
Transaction History
|
+—- Partition 2024
|
+—- Partition 2025
|
+—- Partition 2026
This illustration represents a possible date-based strategy, not a required Pega table structure.
Partitioning can improve maintenance and query performance when the database can eliminate irrelevant partitions efficiently.
However, it can also introduce complexity involving indexes, partition keys, uniqueness constraints, and data movement.
Important: Partitioning is a database architecture decision. Confirm that the target database, Pega deployment model, and support requirements permit the proposed design.
Archival
Archival moves eligible historical information to a designated storage location or data platform.
A good archival policy should define:
- Retention duration.
- Eligibility criteria.
- Business and regulatory requirements.
- Required retrieval methods.
- Referential integrity.
- Backup and recovery.
- Deletion and verification procedures.
Archival should be based on an approved retention strategy, not simply on a desire to reduce table size.
11. Choose the Right Integration Strategy
Applications managing 100 million records often exchange data with external systems.
Integration design becomes particularly important when Pega must retrieve or synchronize large datasets.
Avoid one enormous API request
Retrieving millions of records in one REST request can cause:
- Large response payloads.
- Memory pressure.
- Long execution times.
- Gateway timeouts.
- Difficult recovery after failure.
A better approach is to use bounded requests or batch-based synchronization.
For example:
External Data Source
|
v
Request Batch 1
|
v
Process and Persist
|
v
Request Batch 2
|
v
Process and Persist
|
v
Continue Until Complete
Use incremental synchronization
If only a small number of records change between synchronization runs, retrieving the entire dataset repeatedly is inefficient.
Depending on the external system, incremental synchronization can use:
- Change timestamps.
- Sequence numbers.
- Change data capture.
- Event streams.
- Version identifiers.
- Delta APIs.
The implementation must account for late-arriving changes, duplicate events, failed batches, and recovery after interruptions.
Make processing restartable
For long-running synchronization, maintain enough progress information to resume safely.
For example, a batch process might record the last successfully processed sequence number.
A restartable design reduces the need to repeat large amounts of completed work.
12. Monitor Database and Application Performance
A scalable architecture requires continuous measurement.
Without monitoring, developers may discover problems only after users experience timeouts.
Important database metrics
Monitor:
- Query execution time.
- Database CPU utilization.
- Logical and physical reads.
- Index usage.
- Lock waits.
- Deadlocks.
- Connection utilization.
- Slow queries.
- Table and index growth.
Important Pega metrics
Monitor:
- Requestor response times.
- Service and integration response times.
- Clipboard size.
- Data Page loading time.
- Queue Processor backlog.
- Background processing duration.
- Application-server CPU and memory.
- Thread utilization.
- Relevant PDC alerts.
Pega Predictive Diagnostic Cloud (PDC), where available, can help identify performance alerts and problematic rules or requests.
Database-native monitoring should be used alongside Pega diagnostics because an application-level delay may originate in the database or an external dependency.
Correlate metrics
For example:
| Symptom | Possible investigation |
| Slow report | SQL execution plan, indexes, sorting, and result size |
| High database CPU | Expensive queries, excessive concurrency, and workload distribution |
| Large clipboard | Excessive data retrieval and unnecessary page loading |
| Growing queue backlog | Processing throughput, failures, and resource contention |
| Slow Data Page | Source query, external service latency, refresh behavior |
| Intermittent slow requests | Node-level metrics, thread contention, and database waits |
These are diagnostic starting points rather than definitive root causes.
13. Understand the Limits of Horizontal Scaling
Adding application-server nodes can increase processing capacity, but it does not automatically make database queries faster.
Consider the following architecture:
Load Balancer
|
+———+———+
| | |
v v v
Node 1 Node 2 Node 3
| | |
+———+———+
|
v
Database
|
v
High CPU / I/O
If the database is already overloaded, adding more nodes may increase the number of concurrent queries and make the bottleneck worse.
Before scaling horizontally, determine whether the limiting resource is:
- Application-server CPU.
- Application-server memory.
- Database CPU or I/O.
- Database connection capacity.
- External API throughput.
- Lock contention.
- Inefficient queries.
Scale the component that limits throughput, and verify that downstream dependencies can support the increased load.
14. Test with Realistic Data Volumes
An application should not be considered ready for 100 million records simply because it performs well with a small development dataset.
Testing should reproduce realistic data distributions and workloads.
Recommended testing approach
Step 1: Establish a baseline
Measure response times, throughput, database load, and memory consumption with the existing dataset.
Step 2: Increase the dataset
Test progressively larger volumes, using realistic distributions of active records, historical records, and frequently accessed data.
Step 3: Simulate concurrent users
Run workloads that represent expected peak usage.
Step 4: Test complex queries
Include reports, searches, joins, sorting, filtering, and count operations.
Step 5: Test background processing
Measure throughput and backlog behavior under sustained workloads.
Step 6: Test failure recovery
Simulate failed batches, unavailable external services, and restart scenarios.
Step 7: Identify the bottleneck
Determine whether the limiting factor is the application, database, integration layer, or infrastructure.
Step 8: Repeat after optimization
Compare the results against the baseline to establish whether the changes actually improved performance.
The objective is not merely to process 100 million records successfully. It is to meet measurable response-time, throughput, reliability, and recovery requirements at that scale.
15. Common Mistakes to Avoid
Loading the entire dataset into a Data Page
This can create unnecessary database activity and memory pressure.
Better approach: Retrieve bounded, parameterized result sets.
Adding indexes without analyzing queries
Too many or poorly chosen indexes can increase write overhead and storage requirements.
Better approach: Review execution plans and index effectiveness before making changes.
Running bulk processing synchronously
Long-running requests can consume threads and degrade interactive performance.
Better approach: Use suitable asynchronous or batch-processing patterns.
Retrieving unnecessary columns
Large payloads increase database, network, and memory costs.
Better approach: Return only the fields required for the operation.
Using unfiltered reports
Broad queries can produce excessive reads and large result sets.
Better approach: Apply selective filters and validate query execution plans.
Scaling application nodes without checking the database
More nodes may increase contention instead of improving throughput.
Better approach: Identify the actual bottleneck before scaling.
Ignoring retention and archival
Historical records accumulate indefinitely when retention is not managed.
Better approach: Establish approved archival and retention policies.
Testing only with small datasets
Small test environments may hide problems that appear at production scale.
Better approach: Use realistic data volumes and concurrency during performance testing.
16. A Reference Architecture for 100+ Million Records
A high-volume Pega application may use an architecture such as the following:
Users / External Systems
|
v
Load Balancer
|
v
Pega Application Tier
|
+————–+————–+
| | |
v v v
Case Processing Data Access Async Processing
| | |
+————–+————–+
|
v
Data Access Layer
|
+————–+————–+
| | |
v v v
Operational Historical External
Database Data Store Data Sources
|
v
Indexing / Query Optimization
Monitoring:
PDC + Database Metrics + Application Logs
This is a conceptual reference architecture, not a mandatory Pega deployment pattern.
The operational database supports transactional application processing. Historical data may be retained in separate storage, while external data sources support integrations and analytics.
Asynchronous processing handles suitable workloads that do not need to complete within a user-facing request.
The final architecture should be based on business requirements, Pega-supported capabilities, database constraints, data access patterns, and measured workload characteristics.
Conclusion
Designing Pega applications for 100+ million records requires a data-first approach to architecture.
The biggest performance gains often come from ensuring that the application retrieves less data, executes more selective queries, avoids unnecessary processing, and handles large workloads in controlled batches.
Database indexing, efficient Data Pages, optimized reports, asynchronous processing, archival, and realistic load testing all contribute to a scalable design.
However, no single technique guarantees performance at this scale. Every optimization must be validated against the actual database platform, Pega configuration, workload, and business requirements.
The central principle is simple: design every data access operation around the minimum information required to complete the business task.
When this principle is combined with effective monitoring, sound data modeling, and capacity planning, Pega applications are better positioned to support large datasets without sacrificing responsiveness, reliability, or maintainability.
