LSA Data Excellence: Performance considerations when retrieving data in modern Pega
New in Pega 8.5 is the ability for Data Pages classified as Queryable (sourced by a Report Definition of the same class as the Data Page, amongst other criteria) to be pageable. This allows DX API v2 to be able to retrieve Data Page contents in a paged manner, rather than needing to source the entire list.
For Data Pages that do not meet the definition of Queryable (including all Data Pages on versions 8.4 or earlier), in particular those sourcing data from other Systems of Record, work with your client's architects to have paging-friendly data retrieval APIs so as to optimize data retrieval responsiveness for your Pega applications and their users.
Report Definitions in Pega Infinity also have the ability to prefer sourcing a result set by doing an Elastic Search index query instead of a SQL SELECT to the database. This will likely be more performant when dealing with large data sets, at the cost of the index not reflecting data updates that have been very recently committed. For reporting requirements involving large data sets, such as abstract visualizations like a "heat map" of COVID infections on a world map, showing results correct as of one minute ago is likely tolerable.
Any data retrieval operation acting on a relational database should assure the tables holding the data include indexes which align with the filtering criteria used in the retrieval. When direct access to the database is not available, database index management can be managed from Dev Studio using the Schema Tools landing page (provided you have the PegaRULES:DatabaseAdministrator role - or one that depends on it - on your Access Group).