Latest PostgreSQL Trends: Most Time-Consuming Tasks & Important Metrics to Track
Tags: postgres, metrics, database, slowqueries
Published: 2018-10-30 12:07:04

---
title: Latest PostgreSQL Trends: Most Time-Consuming Tasks & Important Metrics to Track
published: true
description: What’s trending in PostgreSQL management? Find out what the most time-consuming PostgreSQL management tasks are, and important metrics to track for performance.
tags: Postgres, Metrics, Database, Slow Queries,
canonical_url: https://scalegrid.io/blog/latest-postgresql-trends-most-time-consuming-tasks-important-metrics-to-track/
---
<link rel="canonical" href="https://scalegrid.io/blog/latest-postgresql-trends-most-time-consuming-tasks-important-metrics-to-track/" />

<a href="https://scalegrid.io/blog/latest-postgresql-trends-most-time-consuming-tasks-important-metrics-to-track/"><img class="alignnone size-full wp-image-3545" src="https://scalegrid.io/blog/wp-content/uploads/2018/10/Latest-PostgreSQL-Trends-Most-Time-Consuming-Tasks-Important-Metrics-to-Track-ScaleGrid-Blog-1.jpg" alt="Latest PostgreSQL Trends: Most Time-Consuming Tasks & Important Metrics to Track - ScaleGrid Blog" width="100%" height="auto" /></a>
<p style="text-align: justify;"><a href="https://www.postgresql.org/" target="_blank">PostgreSQL</a>, the fourth most popular database and DBMS of the Year in <a href="https://db-engines.com/en/blog_post/76" target="_blank">2017</a>, has exploded in popularity amongst the development and database communities across the world. Stealing market share from leaders Oracle, MySQL, and Microsoft SQL Server, <a title="PostgreSQL Hosting &amp; Management" href="https://scalegrid.io/postgresql.html" target="_blank">PostgreSQL hosting</a> is also highly leveraged by new businesses in exciting spaces like IoT, e-commerce, SaaS, analytics, and more.</p>

<h2 style="padding-top: 15px;">So What’s Trending in PostgreSQL Management?</h2>
<p style="text-align: justify;">We attended <a href="https://2018.postgresopen.org/" target="_blank">PostgresOpen</a> in San Francisco last month to uncover the latest trends from the experts themselves. Read the full <a href="https://scalegrid.io/blog/latest-postgresql-trends-most-time-consuming-tasks-important-metrics-to-track/" target="_blank">Latest PostgreSQL Trends</a> post.</p>

<h3 style="padding-top: 10px;">Most Time-Consuming PostgreSQL Management Tasks</h3>
<p style="text-align: justify;">So, what’s eating up your time on the PostgreSQL management front? While there are thousands of tasks involved with managing your PostgreSQL production deployments, managing queries was the strong leader with over 30% from respondents.</p>
 <a href="https://scalegrid.io/blog/wp-content/uploads/2018/10/Most-Time-Consuming-PostgreSQL-Management-Tasks-ScaleGrid-Blog.png"><img class="alignnone size-full wp-image-3545" src="https://scalegrid.io/blog/wp-content/uploads/2018/10/Most-Time-Consuming-PostgreSQL-Management-Tasks-ScaleGrid-Blog.png" alt="Most Time-Consuming PostgreSQL Management Tasks - ScaleGrid Blog" width="1200" height="742" /></a>
<p style="text-align: justify;">Managing space was a distant second with 15% of PostgreSQL users finding it their most difficult task, followed by replication, upgrades, and monitoring. 23% of PostgreSQL users fell into the “All others” category, consisting of tasks like patching, recoveries, partitioning, and migrations.</p>

<h3 style="padding-top: 10px;">Managing PostgreSQL Queries Breakdown</h3>
<p style="text-align: justify;">With the far lead on managing PostgreSQL queries, we dove deeper to see what specific tasks were consuming their time. The results spread across the entire process of managing queries, from structuring at setup to optimizing after analysis.</p>
<a href="https://scalegrid.io/blog/wp-content/uploads/2018/10/Most-Time-Consuming-PostgreSQL-Management-Tasks-Managing-Queries-Breakdown-ScaleGrid-Blog.png"><img class="alignnone size-full wp-image-3544" src="https://scalegrid.io/blog/wp-content/uploads/2018/10/Most-Time-Consuming-PostgreSQL-Management-Tasks-Managing-Queries-Breakdown-ScaleGrid-Blog.png" alt="Managing PostgreSQL Queries Breakdown - Most Time-Consuming PostgreSQL Management Tasks - ScaleGrid Blog" width="1200" height="742" /></a>
<p style="text-align: justify;">To explain this further, let’s start at the beginning of the query management process:</p>

<h4 style="padding-top: 15px;">Query Structure</h4>
<p style="text-align: justify;">The smallest segment, managing query structures, accounted for 22% of responses from PostgreSQL users who selected queries as their most time-consuming management task.</p>
<p style="text-align: justify;">Before getting started, you need to create a PostgreSQL <a href="https://www.postgresql.org/docs/9.4/static/using-explain.html" target="_blank">query plan</a> around your clusters to match your query structure with your data properties. These consist of nodes, ranging from scan nodes at the bottom level for raw row table returns, along with non-table rows such as values.</p>

<h4 style="padding-top: 15px;">Slow Query Analysis</h4>
<p style="text-align: justify;">After you’ve established your structure, the next step is analyzing your queries to identify slow-running queries that can be affecting your application's performance. By default, “slow queries” are defined as queries that take longer than 100ms.</p>

<h4 style="padding-top: 15px;">Optimizing Queries</h4>
<p style="text-align: justify;">Now that you’ve identified your slow queries, the real work begins - optimizing your PostgreSQL queries. Postgres performance tuning can be a gruesome task, but with proper identification and analysis, you can hone in on the bottlenecks and make your necessary query changes and add indexes where needed to improve your execution. Here’s a great <a href="https://www.geekytidbits.com/performance-tuning-postgres/" target="_blank">article</a> on performance tuning queries in PostgreSQL.</p>


<h3 style="padding-top: 10px;">Most Important Metrics to Track for PostgreSQL Performance</h3>
<p style="text-align: justify;">Now that we’ve identified the most time-consuming PostgreSQL management task, let’s take a deeper look into the important metrics PostgreSQL users track to optimize their performance.</p>
<p style="text-align: justify;">The most important PostgreSQL metrics results were significantly more even than the management tasks, resulting in a four-way tie between replication stats, CPU &amp; RAM usage, transactions per second (TPS), and slow queries:</p>
<p style="text-align: justify;"><a href="https://scalegrid.io/blog/wp-content/uploads/2018/10/Most-Important-Metrics-to-Track-for-PostgreSQL-Performance-ScaleGrid-Blog.png"><img class="alignnone size-full wp-image-3543" src="https://scalegrid.io/blog/wp-content/uploads/2018/10/Most-Important-Metrics-to-Track-for-PostgreSQL-Performance-ScaleGrid-Blog.png" alt="Most Important Metrics to Track for PostgreSQL Performance - ScaleGrid Blog" width="1200" height="742" /></a></p>

<h4 style="padding-top: 15px;">Replication Stats</h4>
<p style="text-align: justify;">Monitoring your PostgreSQL replication status is a crucial task to ensure your replications are properly executed and your production deployments remain highly available. The replication process should be customized to best fit your application needs, and continuous endpoint monitoring is the best way to ensure your data is secure and ready for recovery.</p>
<p style="text-align: justify;">It’s important to <a href="https://pgdash.io/blog/monitoring-postgres-replication.html" target="_blank">track metrics</a> on both your standby servers and primary servers. Your standby servers should be monitored for incoming replication and recovery status, and your primary servers should be monitored for outgoing replication and replication slots. If you’re using <a title="Getting Started with PostgreSQL Streaming Replication" href="https://scalegrid.io/blog/getting-started-with-postgresql-streaming-replication/" target="_blank">PostgreSQL streaming replication</a>, replication slots are not always required. Streaming replication ensures the immediate data availability on your standby servers and is ideal for low-TPS servers.</p>

<h4 style="padding-top: 15px;">CPU &amp; RAM Usage</h4>
<p style="text-align: justify;">Tracking your CPU and RAM (memory) usage are crucial metrics to monitor to ensure the health of your PostgreSQL servers. If your CPU usage is too high, your application will experience slowdowns making your users suffer. It’s often a result of poorly optimized queries, or even high query parallelisms. Monitoring RAM is very important to ensure you have enough disk space, and to understand exactly what your RAM is being used for. It’s recommended to have approximately 25% of your memory allocated for shared_buffers. PostgreSQL also defaults working memory buffer size to <a href="https://dzone.com/articles/is-your-postgres-query-starved-for-memory" target="_blank">4MB</a>, which is often too little and results in high execution times.</p>

<h4 style="padding-top: 15px;">Transactions Per Second</h4>
<p style="text-align: justify;">Monitoring the number of transactions per second allows you to determine the load on the system and current throughput. By analyzing this metric, one can decide to scale the system accordingly to achieve the desired throughput. You can also determine how a change in configuration settings or system resources affects the throughput.</p>

<h4 style="padding-top: 15px;">Slow Queries</h4>
<p style="text-align: justify;">Inefficient queries can slow down the PostgreSQL performance even if the system is configured with adequate resources. It’s always a good practice to analyze these inefficient queries and fix them. PostgreSQL provides parameter called <i>log_min_duration_statement</i>. When this is set, it causes the duration of each completed statement to be logged if the statement ran for at least the specified number of milliseconds. Once the slow queries are obtained, you can run EXPLAIN ANALYSE to understand the execution plan. This will enable you to track the issue and optimize the query accordingly. Hence, monitoring the slow queries regularly will avoid the slowness in performance.</p>
<p style="text-align: justify; padding-bottom: 15px;">Find us next week at the <a href="https://postgresconf.org/conferences/SiliconValley2018" target="_blank">PostgresConf Silicon Valley 2018</a> event where we hope to uncover more insights on what’s trending in the PostgreSQL management space. If you have any questions or comments, feel free to share them with us here in our comments or on Twitter at <a href="https://twitter.com/scalegridio" target="_blank">@scalegridio</a>.</p>