Development

Automating Postgres Indexing with Dexter & HypoPG

A database index is a function of any database engine which allows it to find the records you are looking for without having to check every row. You manually build them, but once they’re there, your database will make the decision whether or not to use it.

4 min
May 9, 2019
Andrew Hooker
Senior Developer

Database indexes are important; they can make or break the performance of whatever you’re building. They’re also easy to overlook in development, as problems often don’t present themselves until you start loading up the database with data.

What is a database index in SQL?

A database index is a function of any database engine which allows it to find the records you are looking for without having to check every row. You manually build them, but once they’re there, your database will make the decision whether or not to use it.

When are database indexes useful?

Database indexes are a small concise lookup for your tables. They’re similar to the table of contents in a book; if you add an index, then instead of having to flip through the pages to find what you’re looking for, you get a page number and can go straight to the right place. Postgres takes this one step further and can use the index for all sorts of queries (joining tables, doing math, etc.)

Why I don't just index everything?

People have written books about the performance of indexes, and I’m not going to go into it here, beyond to say: each index does require maintenance. Any time you insert/update/delete a record it has to be updated. They take up disk space, and the database tries to keep them in memory, so they’ll enlarge and slow down your backups. Long story short, while you want to have the right indexes, you don’t want to index every column.

How can we improve database indexing?

While an experienced developer can often identify where indexes are needed, it’d be much better if we could automate it. Luckily, we don’t have to do a ton of work to pull this off; there’s a series of tools we can use to make this easy.

database index

Automate indexing for an SQL database

While our usage of these tools actually just lives at the top layer, it’s important to start at the bottom to understand the pieces.

First up: Postgres

Whether this is the right database choice for you is a whole different blog post, but it’s essential for this post, as the tooling is Postgres specific.

Next: HypoPG

HypoPG lets you create hypothetical indexes and run queries against it, to determine whether they help your query. You don’t need to understand this, but you do have to have it installed into whatever Postgres instance you’re going to run this on.

Finally: Dexter

(Technically, ruby is required to install this, but that’s a dependency, so I don’t count it). Dexter is the star of this show and does all the hard work. You feed your database logs into it, and it finds slow queries and works to figure out what indexes might help. It uses HypoPG to test the query to ensure that it actually improves things.

See it in action

Here’s an example of what Dexter can tell me. I dropped all the indexes from an app just to get a better example; then I fire up the app, and click around. It recommends indexes which it thinks will improve things.

Dexter code index

You can also add a --create flag, and it will create the indexes for you:

Dexter code index

Of course, that doesn’t play the best with our rails app, so instead, I convert the recommendations to a migration:

Rails app migration code

To write this post, I dropped all the indexes from my app. This left a fairly simple page completing in 4,954ms. With the indexes added, it finished in 1,151ms.

Things to watch out for:

Installation challenges

Installing this was a bit of a challenge. Once it’s installed, you have to add the extension to the database you want to analyze. This impacts the schema, so if you don’t remove it when you’re done, it gets added to your schema files by Rails anytime you migrate. This causes issues if you don’t have HypoPG installed everywhere you’re running the app (including your CI server)

Need access to database and logs

In order for this to work, you need access to both the database to test against and logs of actual usage. If you’re using a hosted database, this can pose issues. The documentation does provide instructions on how to work around it, but your mileage may vary.

Non-optimal recommendations

Some of the indexes it recommends may not actually make sense. It also doesn’t necessarily have situational awareness so there may be a composite index that would work better than what it is recommending.

Takeaways

Dexter is a great tool to expose indexes you might be missing. I wouldn’t bother with it for daily use, but it’s certainly something that can find some performance gains if you’re not hyper-vigilant about adding indexes on your own. It would also be a great tool in the arsenal when taking over a project to make sure that things were running smoothly. Like so many tools, it should be used to make recommendations, but as indexes also have consequences, I’d be wary of using it in automated mode, rather than manually adding its suggestions.

Resources to learn more about database indexes

Actionable UX audit kit

  • Guide with Checklist
  • UX Audit Template for Figma
  • UX Audit Report Template for Figma
  • Walkthrough Video
By filling out this form you agree to receive our super helpful design newsletter and announcements from the Headway design crew.

Create better products in just 10 minutes per week

Learn how to launch and grow products less chaos.

See what our crew shares inside our private slack channels to stay on top of industry trends.

By filling out this form you agree to receive a super helpful weekly newsletter and announcements from the Headway crew.