Lightning Talk: Get the most out of your postgres instance with pghero

by Pratvrirash

In this lightning talk at Ruby Unconf 2019, Pratvrirash discusses how to effectively utilize PG Hero to monitor and optimize PostgreSQL database performance. The presentation begins with an introduction to Postgres and the problems many developers encounter when monitoring their instances for slow queries and errors. Pratvrirash shares experiences from his work at Big Binary, detailing a case study of a transport management system client in the USA that faced performance issues due to slow queries.

Key points discussed during the talk include:
- Introduction to PG Hero: A tool that allows easy monitoring of PostgreSQL queries and database performance by simply adding it to a Gemfile and installing it.
- Dashboard Features: PG Hero's dashboard highlights critical metrics such as slow indexes, duplicate indexes, and invalid indexes, emphasizing the importance of identifying these issues to maintain optimal database performance.
- Index Management: The talk discusses how duplicate indexes can lead to performance bottlenecks, specifically affecting write throughput. PG Hero assists in suggesting necessary index migrations to improve query performance.
- Monitoring Queries: The live demo of PG Hero showcases how it tracks query statistics, including execution times and their impact on database resources, enabling users to take informed actions based on real-time data.
- Database Growth Management: Another critical feature addressed is monitoring table sizes and growth, which aids in schema management and proactive maintenance.
- Configuration Insights: The Explain section provides valuable insights into the database setup, helping users fine-tune configurations for improved performance.

In conclusion, Pratvrirash emphasizes the importance of timely database monitoring and management to prevent performance degradation, advocating for the adoption of PG Hero as a straightforward and effective tool for developers managing Postgres instances.

00:00:07.680 Hello, my name is Praj and I am from India.

00:00:10.519 So, how many of you use Postgres in production right now?

00:00:13.280 And how many of you actually monitor your instance for slow queries, errors, or other problems?

00:00:16.400 Today, I am going to introduce you to a tool called PG Hero, which we use on a daily basis at my company.

00:00:19.039 I work at a company called Big Binary, and we have a client that operates a transport management system in the USA.

00:00:21.439 Our application experiences a lot of traffic, and we faced issues where our database was slowing down due to many slow queries.

00:00:26.599 As a solution, we installed PG Hero. The dashboard only requires you to add PG Hero to your Gemfile and install it, after which it will start monitoring queries. This is PG Hero's sample dashboard, which shows the number of slow indexes, duplicate indexes, and invalid indexes.

00:01:03.079 One common problem is that whenever you add an index to the database, you may not consider whether it duplicates an older index or if it's invalid. This oversight can lead to decreased write throughput because the database continuously creates keys for duplicate indexes. Therefore, it’s essential to monitor these aspects to ensure your read and write queries do not slow down. PG Hero not only suggests which indexes you should add but also monitors slow queries and has a function that provides suggestions for new indexes that could enhance the speed of your reads.

00:01:43.799 Now, I’m going to show you a live demo of our instance. Here is the live PG Hero dashboard from our client’s application. You can see the warnings it provides, which we need to act upon. For instance, there are duplicate indexes, and PG Hero suggests a migration to rectify this, allowing you to improve your reads and writes.

00:02:10.640 There is also a section for monitoring queries. This section displays many statistics, indicating which queries are running the longest, their average execution time, and the total time they are consuming. You can analyze these queries and take necessary actions based on the insights provided.

00:02:39.400 Additionally, there is a space section to monitor the size and growth of your database tables. This helps you to make informed decisions about when to change the schema and take appropriate action. There’s also a section called Explain, which provides insights into your configuration.

00:03:06.720 In conclusion, it’s crucial to monitor your database health and the queries running on your instance so that you can prevent performance degradation. Thank you very much for your time.