[HN Gopher] Understanding database indexes in PostgreSQL ___________________________________________________________________ Understanding database indexes in PostgreSQL Author : mrpotato Score : 94 points Date : 2023-05-17 17:42 UTC (5 hours ago) (HTM) web link (blog.mastermind.dev) (TXT) w3m dump (blog.mastermind.dev) | felipelalli wrote: | [flagged] | fabian2k wrote: | That was more detailed than I expected, a lot of posts on this | topic tend to be more superficial. I suspect the BRIN index might | need a bit of a stronger disclaimer, as far as I understand you | really want to use that only for ordered data, and in those cases | it is exceptionally good at its job. But it is a lot worse if | that condition is not met. The post mentions this a bit, but with | very soft language. | | I disagree a bit with "Don't index columns if the table has | little data", mostly because it doesn't matter in those cases. If | the table is tiny the index is also very cheap (unless it's | something really weird like a tiny table that is written at a | very high frequency). And "little data" is just not specific | enough for people to make decisions unless they already have a | very good intuition on when the query planner would use such an | index. | | A rather important part that isn't mentioned about multi-column | indexes is which kinds of query can use them. That is probably | not obvious if you never read about them in detail, but it's | really important to know when defining them. | cl0ckt0wer wrote: | I've seen a lot of performance gain when services don't cache | correctly and are constantly going into small tables to get a | record. Just be mindful of the write slowdowns it can cause. | aidos wrote: | I use multi-column indexes for things like, "find me the most | recent version of this template for this customer". | | I really wish pg had a way to do partial indexes with limits so | I could create a partial index that stores, for example, only | the most recent version of something (I find this comes up a | lot). | throwaway8384j wrote: | I haven't used then myself, but Postgres supports partitions. | If you have large amounts of data, you could partition by a | date range. | | https://www.postgresql.org/docs/current/ddl- | partitioning.htm... | rchowe wrote: | Also that the order of columns matters in multi-column indices | (although some index types can technically use any column), so | the first column in the list should be something like a | partition key or something that will slice down the data a lot. | paulddraper wrote: | Wow, this is an excellent article. | | Two really nice nuggets: how to detect unused and bloated | indices. ___________________________________________________________________ (page generated 2023-05-17 23:00 UTC)