[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)