Databases are essential components of nearly all software applications, and PostgreSQL® ranks among the most widely used of all relational databases. However, relational databases are often implicated as the source of application performance bottlenecks, with two of the most common causes of database performance issues being improper configurations and unoptimized queries. Identifying a database performance issue can be cumbersome, requiring extensive time and effort. For this reason, database observability platforms serve as crucial tools for quickly identifying and resolving these issues. 

This article is the first in a two-part series on PostgreSQL optimization. In this first part, we’ll focus on PostgreSQL configurations to fine-tune database performance. Along the way, we’ll look at how SolarWinds® Observability helps to identify the configuration aspects that require optimization. 

Considerations When Configuring Your PostgreSQL Database 

Configuring your PostgreSQL database must be approached from both a hardware and a software perspective, each of which is critical. 

Modern software applications will likely utilize cloud databases, meaning users should also consider cost savings. Poor configurations can lead to high latency and, more importantly, outages that directly affect end users. In light of this, you should be aware of the important configuration considerations available to improve performance significantly while still keeping your costs low. 

CPU and memory usage 

Optimizing your database server’s CPU and memory allocation is one of the most fundamental hardware aspects. Allocate too little, and your application will inevitably suffer from poor performance, disruptions, and perhaps a total outage. Conversely, allocating too much will dramatically increase your expenses. This concern is especially relevant when using cloud-based databases, which are increasingly becoming the industry standard.  

Number of required instances and instance types 

Whether your application is read-heavy, write-heavy, or both, consider configuring your database server as a cluster of instances dedicated to either read or write operations. When using this type of architecture, replication from the writer instances to the reader instances can be configured as synchronous or asynchronous. As described in the CAP theorem, synchronous replication guarantees strong consistency, but it impacts performance. Conversely, asynchronous replication guarantees high performance but leads to eventual consistency, which may or may not be acceptable, depending on the nature of your application.  

Consider properly configuring horizontal autoscaling when working with cloud-based databases based on metrics, such as CPU rate and number of current connections. This step ensures an optimal number of instances, even when traffic to your application fluctuates rapidly.   

Maximum number of concurrent connections 

Unlike the above configurations, this configuration isn't hardware related. It focuses on optimizing PostgreSQL's ability to handle multiple concurrent connections from users or applications. This configuration prevents connection timeouts and performance degradation when the database is overwhelmed with connection requests.  

Setting the maximum number of connections through the max_connections parameter appropriately strikes a balance between resource utilization and responsiveness, ensuring that the database can accommodate the required workload without sacrificing performance or stability. High-traffic applications are essential for maintaining an optimal user experience where concurrent connections are efficiently managed.  

It’s important to note that each connection consumes memory, so ensure that your application code is optimal before increasing this parameter. This will help you to avoid connections being held unnecessarily for extended periods.  

Autovacuum configuration 

Data updates in a PostgreSQL table don't technically refresh the existing data. Instead, PostgreSQL marks the row with the old data as obsolete before inserting a new row with the updated data. Without a clean-up process to remove stale data, the table size would increase endlessly. In PostgreSQL, this clean-up process is called vacuuming.  

Vacuuming can be executed manually but is almost always automated. The autovacuum daemon is the process that does this. However, if not configured properly, it can cause performance issues that are often difficult to identify. Proper configuration of the autovacuum process largely depends on the volume and frequency of data updates.   

To improve efficiency, you can fine-tune the autovacuum settings, such as the autovacuum_vacuum_cost_limit and autovacuum_vacuum_scale_factor. For example, increasing the vacuum cost limit speeds up cleanup at the expense of more I/O usage while adjusting the vacuum scale factor ensures the process triggers appropriately based on table size. Additionally, you can customize autovacuum thresholds on a per-table basis for workloads with uneven update patterns.  

Optimizing Your PostgreSQL Configuration Using SolarWinds Observability 

To make informed decisions about any of these considerations, you will need the relevant data points. This data is most easily obtained through a robust database observability solution, such as SolarWinds Observability.  

How to connect your PostgreSQL instances to SolarWinds Observability 

Creating a connection between your PostgreSQL database and SolarWinds Observability is the precondition to getting observability data flowing. SolarWinds can collect observability data from various cloud-based and on-premises PostgreSQL databases. 

For a detailed explanation of how to connect SolarWinds Observability to your PostgreSQL instances, follow this guide. 

Viewing CPU and memory utilization 

Keeping track of the CPU and memory utilization of your PostgreSQL instances is imperative. This is particularly important when using burstable cloud-based instances, which are becoming an increasingly common choice. 

To view these metrics of your PostgreSQL instances in SolarWinds Observability, navigate to the left sidebar to select the Database feature. 

From there, navigate to the Resources tab. 

Overview of CPU/Memory utilization in the SolarWinds Observability dashboard 

View load and throughput metric 

To view the average load and throughput of your instances, navigate to the Overview tab. As you click on specific PostgreSQL database instances, you’ll see visualizations of these key metrics, helping you understand whether the instance as provisioned is underutilized or overutilized. 

Overview of average load and throughput in the SolarWinds Observability dashboard 

View connected users and services 

To keep track of users and services connected to a PostgreSQL server, navigate to the Profiler tab and select PostgreSQL Stat Activity Users in the filtering drop-down menu: 

Overview of active connections in the SolarWinds Observability dashboard 

How to determine if your application is read-heavy or write-heavy 

To determine whether the application using your database is read-heavy or write-heavy, inspect which queries are most utilized. In the dashboard, navigate to the Profiler tab and filter by Query Verbs. 

Filtering by query verb in the SolarWinds Observability dashboard 

In the above screenshot, most of the time (86.9%) is spent executing queries that are SELECT statements. This means the database is very read-heavy, and you may benefit from adding additional read replicas. This may also indicate that some of the queries require optimization, which we’ll cover in part two of this series. Modify the filter to show the total count of queries, affected rows, and other data points. 

View the number of active sessions 

To determine whether the maximum number of active sessions requires modification, monitor active session trends by navigating to the Events tab. 

Overview of active sessions in the SolarWinds Observability dashboard 

Viewing PostgreSQL autovacuum metrics 

The Metrics tab contains additional helpful data points related to database activities, such as transactions, connections, and vacuuming. 

Overview of PostgreSQL metrics in the SolarWinds Observability dashboard 

Clicking on a particular metric will display a time series graph showing how the metric value has changed over a specified time range. Metrics such as time_since_last_autovacuum and autovacuum_waiting_activity provide insights into whether the autovacuum process is fully optimized.  

Autovacuum waiting activity metric in the SolarWinds Observability dashboard

Using the SolarWinds Observability Advisor 

SolarWinds Observability also provides an Advisor feature that offers useful suggestions on how to optimize your PostgreSQL instances. This feature can save ample time spent on identifying improvements which can be spent on more effective tasks. To access it, navigate to the Advisors tab. 

SolarWinds Observability Advisor Feature - Summary 

SolarWinds Observability Advisor Feature - Detailed by Advice Categories 

Keep in mind that all of the covered metrics can be filtered by a specific time frame. 

Optimizing Configuration Based on SolarWinds Observability Insights 

As the dashboards from SolarWinds Observability provide you with critical performance insights, you may need to modify your PostgreSQL configurations.  

It’s possible to modify parameter configurations for PostgreSQL in the postgresql.conf file. However, it is important to note that if your database is deployed in the cloud, you will likely be unable to access this file directly. Cloud providers such as AWS® and Azure® restrict access to file systems of managed database servers to meet security standards and ensure that their fully managed services are not impacted by user actions.  

Cloud providers do, however, allow users to modify the postgresql.conf file via a proxy. In AWS, you can apply configurations via Parameter Groups. In Azure, modify the file in the Azure Portal.  

Conclusion 

Properly configuring your PostgreSQL instances can significantly improve the performance of your application while keeping costs to a minimum. Not doing so can potentially lead to poor user experience, service interruptions, and outages that negatively impact revenue and company reputation. A powerful observability tool such as SolarWinds Observability is a must-have to ensure your PostgreSQL instances are optimally configured. 

In this first part of our PostgreSQL performance two-part series, we’ve covered the most important aspects of PostgreSQL configuration. The second of our two-part series will explore query optimization and how SolarWinds Observability can augment this process.