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

Ruby Unconf 2019

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.