An unexpected find that freed 20GB of unused index space in PostgreSQL
Partial indexes are amazing but you have to keep in mind some pecularities.<p>If your query doesn&#x27;t contain a proper match with the WHERE clause of the index - the index will not be used. It is easy to forget about it or to get it wrong in subtle ways. Here is an example from work.<p>There was an event tracing structure which contained the event severity_id. Id values 0-6 inclusive are user facing events. Severity 7 and up is debug events. In practice all debug events were 7 and there were no other values above 7. This table had a partial index with WHERE severity_id &lt; 7.
I tracked down a performance regression, when an ORM (due to programmer error) generated WHERE severity_id != 7. The database is obviously not able to tell that there will never be any values above 7 so the index was not used slowing down event handling. Turning the query to match &lt; 7 fixes the problem. The database might also not be able to infer that the index can be indeed used, for example when prepared statements are involved WHERE severity_id &lt; ?. The database will not be able to tell that all bindings of ? will satisfy &lt; 7 so will not use the index (unless you are running PG 12, then that might depend on the setting of plan_cache_mode[1] but I have not tested that yet).<p>Another thing is that HOT updates in PostgreSQL can&#x27;t be performed if the updated field is indexed but that also includes being part of a WHERE clause in a partial index. So you could have a site like HN and think that it would be nice to index stories WHERE vote &gt; 100 to quickly find more popular stories. That index however would nullify the possiblity of a hot update when the vote tally would be updated. Again, not a problem but you need to know the possible drawbacks.<p>That said, they are great when used for the right purpose. Kudos to the author for a nice article!<p>[1] - <a href="https:&#x2F;&#x2F;postgresqlco.nf&#x2F;doc&#x2F;en&#x2F;param&#x2F;plan_cache_mode&#x2F;" rel="nofollow">https:&#x2F;&#x2F;postgresqlco.nf&#x2F;doc&#x2F;en&#x2F;param&#x2F;plan_cache_mode&#x2F;</a>