Postgres indexes make your application fast. And while one option is to analyze each of your relational database queries with pg_stat_statements to see where you should add indexes… an alternative fix (and a quick one at that) could be to add indexes to each and every database table—and every column—within your database. To make this easy for you, here’s a query you can run that will create the Disclaimer: Adding an index to every column of every table is not a best practice for production databases. And it’s certainly not something we recommend at Citus Data, where we take scaling out Postgres and database performance very seriously. But indexing all the things is a fun “what if” thought exercise that is well worth thinking about, to understand the value of indexes in Postgres. What if you indexed all the things? With that disclaimer out of the way, onto my advice of how to index all the things as a way to understand your database’s performance. What if you really did index all the things in your Postgres database?Yes, we cheated and didn’t do every combination of every column, we thought this might be sufficient enough
Based on our Citus Data multi-tenant tutorial you’d then get something like this as the result:
If you need a schema just to test this one, you can use our multi-tenant tutorial as a starting point. Now in the case of the tutorial app and schema after adding the above indexes everything will be snappy. For larger production systems, you might consider the concurrent index version, which will be roughly 2-3 times slower in creation but won’t hold a long lock on the table while it’s creating. For the concurrent index version:
Indexing all the things in Postgres is a horrible idea, but…Stepping back. For those unfamiliar, every index you add to your Postgres database will slow down your write throughput for the table it exists on. A cool thing about Postgres, though, is that it will track all sorts of interesting data on when indexes are used and not. Similar to how we can get insights from With this query, we can see all of our indexes sorted by size. We can also see if there are unused indexes:
This coupled with our first query can now let us easily index all columns, and just wait a few hours or days to see if the indexes are leveraged. Then we can come back and cleanup all the unused indexes. The result is you have a perfectly performing database. If you prefer an approach to optimizing your database performance that is more reasonable than indexing all the things, consider this Postgres post as a starting point or give this talk by Andrew Kane on how Postgres could index itself a watch. And if you’re running into scaling issues due to performance feel free to contact my team at Citus as we’d be happy to chat. Written by Craig Kerstiens Former Head of Cloud at Citus Data. Ran product at Heroku Postgres. Countless conference talks on Postgres & Citus. Loves bbq and football. |