Analyze the data on the Database landing page to access key statistics about the PostgreSQL database in your system such as the most frequently run queries, index usage, cache effectiveness, and connection tracking. With this information, you can investigate issues with databases in your system, and optimize how your applications use the databases.

Pega Predictive Diagnostic Cloud (PDC) monitors how your applications use the databases in your systems and provides statistics that help you instantly assess the most important parameters, such as table size, and the number of rows and live indexes.

Database issue resolution

PDC provides you with information about changes in database size over time, database usage history, and queries and connection details, which helps you to investigate and resolve issues with your databases.

You can find this information on the Metrics tab, which consists of the following sections:

• Database space used
  Shows changes in your database size over the last 30 days.
• Top tables based on rows and usage
  Lists tables with the most active rows and data size.
• Top tables based on index count and unused indexes
  Lists tables with the most indexes and most used indexes, which illustrates changes in table usage over time.
• Top scanned indexes and distribution of connections
  Shows the most frequently scanned indexes and the status of current connections to your database.
• Active queries and Connection details
  Lists query and connection details for each table, for example, process ID, duration, state, and number of connections.

As a best practice, you should analyze any large changes in indexes usage. If you notice a spike, check if the application was recently updated. Changes in index usage might mean, for example, that the application writes more data, or the data is kept for a longer period, or the application uses the data in different ways than before the update.

Similarly, you should monitor changes in the size of your database. Drastic decrease might occur, for example, after a database cleanup, but could also mean that your application is unable to access parts of the database. Conversely, an unexpected spike in database size might indicate a memory leak.

You should also periodically check the Active queries and Connection details tabs. Here you can see if there are ongoing queries that are unable to finish, which can negatively impact performance of your applications.

Database optimization

Databases are often the lowest throughput points in your system. Optimizing your databases directly improves the performance of your applications.

To help you optimize your databases, PDC provides the following information:

• The Tables tab shows table statistics for each schema in your system and displays the following information:
  • The Table rows and index scan trend chart, which shows the amount of changed indexes and frequently accessed data over time.
  • The Dead and live rows trend chart, which shows dead and live rows over time.
  • A list of tables in a schema, with additional details for each table.
• The Query stats tab shows how often the system runs specific queries in your databases. You can check which queries the system uses more often than others and their run-time length.
• The Disk Hit ratio tab displays information on how many times each table reads information from the cache and from the disk. A higher cache hit % improves the performance of the database.
• The Database Settings tab displays information on all configuration parameters that affect the behavior of the databases in your system.

Indexes

Indexes are helpful in making the database faster to use. However, keeping indexes requires resources. To save resources and optimize your system, you can use the information about indexes that PDC provides to reduce the number of indexes that the system does not use often.

Live and dead rows

Live rows are rows that are currently in use, from which you can reference and analyze data by using a query. Dead rows are deleted rows without data, that are marked for reuse by your data source when you use a write command, such as INSERT or UPDATE. When accessing data from the database, your applications scan all rows in the table, both live and dead. If your database contains too many dead rows, this state can negatively impact performance of your applications. As a best practice, configure your database to periodically perform an automatic vacuum process. If you notice a substantial spike in dead rows, you can also manually start the vacuum process.

For more information about optimizing Postgres SQL databases, see the Postgres SQL documentation.

The following figure shows different functionalities of the Database landing page in PDC: