Advanced PostgreSQL performance tuning and monitoring

Having discussed why Postgres performance tuning matters, and how to write better queries to optimize performance, it’s time to look beyond the basics towards more advanced techniques in Postgres performance tuning and monitoring.

I’ll share how you can use tools like pg_stat_statements for query analysis, implement advanced strategies like bloom filters and indexes on expressions to optimize data retrieval, and explain why you should use user journeys in benchmarking instead of microbenchmarks.

Tools to identify poor Postgres performance

There are many tools we reach for, and metrics we pay attention to, when trying to figure out whether a client’s Postgres database is not performing as well as it should. Let’s start with some of our favorite tools:

pg_stat_activity

pg_stat_activity includes details about the state of each backend process, the query being executed, and the duration of the query. You can infer poor performance from long-running queries, a high number of idle connections, and processes waiting for locks.

pg_top

pg_top displays real-time statistics about database processes, including CPU and memory usage, query execution times, and the number of active connections. When you see high CPU or memory usage, together with slow query execution times, it’s an indicator of poor performance.

pg_stat_statements

pg_stat_statements collects and aggregates statistics about SQL queries executed in the database. It provides insights into which queries are consuming the most resources, including execution time, number of calls, and I/O usage. Analyzing these statistics can help identify inefficient queries, frequent table scans, and queries causing significant I/O load.

Metrics to identify poor Postgres performance

Tools can only take you so far if you don’t know how to interpret the results. We rely on a checklist of metrics in assessing performance problems. In our previous article, we covered query performance, so we’ll look at Postgres metrics you might not be familiar with, but can take you further. We’ll go over locking and contention, index usage, buffer and cache metrics, though there are many, many more metrics you can use to optimize Postgres database performance.

Locking and contention metrics

Lock waits: Frequent or long lock waits can indicate contention issues, where multiple queries are trying to access the same resources simultaneously.

Deadlocks: Deadlocks occur when two or more transactions are waiting on each other to release locks, causing a standstill.

Index usage metrics

Index scans: As we’ve previously discussed in detail, efficient index usage speeds up query execution. Monitoring the number of index scans with pg_stat_user_tables helps ensure that queries are utilizing indexes effectively.

Sequential scans: High sequential scan usage can indicate missing or underutilized indexes. Sequential scans read entire tables, which is slower than using indexes. Try tracking sequential scans to identify tables that need indexing and reduce the overall query execution time.

Buffer and cache metrics

Cache hit ratio: The cache hit ratio indicates the percentage of data requests served from memory rather than disk. A high cache hit ratio means most data is being retrieved from the cache, which is much faster. If you see a low ratio, you’ll need to look into better cache management or more memory. Ideally, you should see

Shared buffer usage: Shared buffers are a key part of Postgres’ memory management and are used to cache data pages. A high utilization of shared buffers indicates efficient memory use.

Benchmarking high performance Postgres: Advanced strategies

Without knowing how to benchmark a Postgres database, it’s hard to tailor our approach to improving performance. But measuring success might be even harder than the technical lift of tuning and monitoring databases. We’ll first go over some benchmarking concepts and then discuss how the tools fit into the performance tuning process.

Two ways to benchmark Postgres performance

Typically, most benchmarking proceeds in two ways: synthetic and microbenchmarking. We think synthetic and user journeys are a better combination:

Synthetic benchmarking

Synthetic benchmarking involves creating artificial workloads to simulate different usage scenarios. This helps evaluate Postgres performance under controlled conditions: you can test specific queries, operations, or configurations to identify potential bottlenecks and optimize performance.

Real-world example: Imagine you work for an e-commerce company where you want to test how the database handles search functionality under heavy load. The way you would do this with synthetic benchmarking is to generate a synthetic workload (with a tool like pgbench) to execute a variety of search queries repeatedly and measure the response times.

In fact, this scenario was extensively written about by Luke Demi, one of our expert consultants, during his time at Coinbase. The team generated synthetic “crypto mania” to load test their MongoDB application to make sure it remained robust during actual periods of high demand. (You can read more about the fascinating details here or check out our interview with him.)

Benchmarking user journeys

Benchmarking user journeys measures performance from a business perspective. Instead of just benchmarking individual queries, you look at entire workflows like new user sign-ups, order processing, or report generation. This approach provides a more holistic view of database performance, in contrast to microbenchmarks, which focus on individual operations or queries, provide a limited view and may miss broader performance issues that impact user experience.

Real-world example: At the same e-commerce company, instead of using microbenchmarks like measuring the performance of a single product search query, you instead benchmark the entire user journey. You would script and simulate the entire user experience, from user authentication to order confirmation, and analyze how the database performs at various stages for different numbers of simulated users.

This makes sure you catch performance bottlenecks everywhere that could affect the user experience, since a slow checkout page might just make the difference between a sale and a closed tab, which is ultimately what matters to the business.

8 advanced Postgres performance tuning techniques

Let’s now turn to practical strategies to improve Postgres performance. Here, we share the top 8 techniques we turn to in our work as database specialists.

1. Optimizing complex queries

As we covered in more detail in our previous article, complex queries, especially those involving multiple joins, can be a significant performance bottleneck. Optimizing queries involves understanding how Postgres works and paying attention to things like the efficient uses of indexes.

2. Solving big joins

You can reduce the need for joins through denormalization and breaking joins in the application. With denormalization, you add redundant data to speed up read operations. By storing data in fewer tables, you reduce the need for joins which improves query speed.

Breaking down the logic is another way to avoid overly complex joins in the database. Try to retrieve the data in smaller, simple queries before combining it in the application.

3. Avoiding foreign keys

Foreign keys create locks and slow down write operations. Instead, remove foreign keys to reduce locking, which will improve write performance. The trade-off, however, is that the application must then ensure that the relationships between tables are valid, since the database won’t automatically validate that, which leads to more complexity at the application level.

4. Avoid triggers

Triggers can cause locks and impact performance. It’s better to handle this in the application layer than in the database, as it reduces the risk of contention

5. Build to support eventual consistency

Eventual consistency allows for more flexible and faster data operations, since you don’t need to ensure immediate consistency across all nodes. If your application can tolerate eventual consistency, it can significantly improve performance.

6. Advanced indexing

Consider using partial indexes, indexes on expressions, and bloom filters. Partial indexes cover a subset of data, which is ideal for frequently queried subsets as it reduces index size. Create indexes on expressions rather than just columns to speed up queries that use functions. Bloom filters are great for indexing large sets of data with many unique values, which helps filter out non-matching rows and reduces the overall amount of data to be processed.

It’s a good idea to remove unused or duplicate indexes since indexing uses up disk space and slows down write operations. You can use pg_stat_user_indexes to identify and clean up those indexes.

7. Query execution plan and nodes

Find the nodes in the query execution plan to identify where the most time is being spent.

8. Query parallelism

Finally, parallel query execution is really important for Postgres performance as it helps leverage multi-core processors. Your application and database need to be configured to allow for parallel writes and reads. Stay away from requiring serialized writes and read-after-write semantics, since this will only limit parallelism and thus, performance.

3 advanced Postgres performance monitoring techniques

Before, or after, you’ve implemented any of the eight strategies above, it’s a good idea to think about how you can monitor your database’s performance to measure and maintain improvements. Here, we look at the top 3 advanced monitoring techniques to catch problems sooner than later:

Monitoring solutions

Cloud providers often come with built-in monitoring solutions. For example, AWS CloudWatch tracks database metrics such as CPU usage, disk I/O, and memory utilization. Third-party tools like Datadog and Grafana are highly effective: Datadog and Grafana provide detailed analytics and real-time monitoring capabilities as well as allowing you to build your own dashboards.

Custom dashboards

For maximum flexibility, you can build your own dashboard to monitor the KPIs specific to your application’s needs. Maybe you’re only interested in things like query latency, throughput, and resource utilization. You can build something that focuses on tracking the p99 latency metric, which measures the maximum latency experienced by 99% of all requests, and thus covers performance for almost all users. Tools like Grafana can integrate data from various sources to pull these KPIs together.

Alerts

Setting up alerts to detect performance anomalies is a great way to become more proactive towards database performance management. But you need to configure them in a way that you only flag true issues, spikes in CPU usage, unexpected drops in throughput, and high disk I/O. If you have too many alerts set at lower thresholds, you risk losing the signal in all the noise. For example, prioritize metrics that matter the most to the end user’s experience, like p99 latency.

Continuous PostgreSQL performance tuning and monitoring

Optimizing databases is hard and at times, tedious, work that can be a challenging experience. As our three-part series on Postgres concludes, I hope that sharing more of what a database specialist turns to when tuning real-world Postgres databases has shed some light on what can help (or hinder) database performance.

Try experimenting with the tools and techniques here, and let us know what you think. We’ve just barely scratched the surface of what’s possible when it comes to optimizing Postgres databases. If you’re looking for a database performance expert with decades of experience, we’re here to help.

Our team of experts brings decades of experience with modernizing Postgres and other databases – we’ve seen all kinds of Postgres optimization challenges and scaled applications that served millions of users at Nextdoor, Clubhouse, and Coinbase.

If you’re facing poor Postgres performance today and want to have a more scalable and efficient database, there are a few ways to learn more:

• Book a call to learn how we can best help level up your Postgres application today
• Engage our team in an extensive review of your Postgres setup and receive a customized optimization strategy