PostgreSQL

Indexes

• Types
  • b-tree default, for inequality, like 'xx%', order by
  • hash useful for equality predicate
  • gist are a family of indexes that are suitable for nearest neighbor kind searches, e.g. sp-gist
  • GIN are inverted indexes store values only once with compressed list of matching row locations
  • BRIN or Block Range INdex store summaries about the values stored in consecutive physical blocks
• partial indexes are sparse indexes that index only partial data
• expression indexes are index on column expressions
• global indexes cover more than one table, mainly for partitioned child tables

Perf

• Heap-Only Tuple (HOT) updates: When an UPDATE doesn't change any columns used by indexes, and row doesn't need to be relocated due to the data page having enough space, drastically cuts down the IOs required for the update.