🎯 Intention

Ajouter le bon Index améliorera grandement les performances de vos requêtes SQL. C’est le principal axe d’amélioration pour corriger des problèmes de lenteur avant de songer à une restructuration de la base de donnée

<aside> ✅ Pré-requis

• Point de connaissance : par défaut Postgres crée des Index B-Tree
• ⚠️ Ajouter des index ralenti les opérations d’écriture SQL : INSERT, UPDATE, DELETE
• Avoir vu le standard Arch: Concevoir une base de données saine </aside>

✅ Points clés

<aside> ⌛ Temps indicatif : 1J

</aside>

Cas général

<aside> ⭐

Une bonne pratique est de mettre un index sur les champs requêté avec :

• WHERE

• ORDER

• GROUP BY </aside>

• 📚 Les types d’index

Etape

Visuel / Exemple

🔗 Unique Indexes

• Si votre modèle contient une validation d’unicité, alors cette unicité doit se retrouver aussi en base de donnée

⚠️ Attention : NULL ≠ NULL en SQL, on ne peut pas vérifier l’unicité si une des colonne est potentiellement NULL. Voir le palliatif.

Raison :

Le plus grand besoin derrière les index uniques est d'assurer l'intégrité des données (puisque la plupart des validations d'unicité, y compris celle d'ActiveRecord, n'appliquent rien et sont plus utiles pour avoir un bon message d'erreur et ne pas lever d'exception que pour l'intégrité des données).

Cependant, un bel effet secondaire d'un index unique est également une meilleure performance par rapport à un index non unique.

# Model
class Artisan < ApplicationRecord
	validates :siret, uniqueness: true
end

# Migration
class AddSiretUniqueForArtisans < ActiveRecord::Migration[6.0]
  def change
    add_index :artisans, :siret, unique: true
  end
end

🔗 GIN Index (Generalized Inverted Index)

• Placer un index GIN sur les colonne de type jsonb, array ou hstore

Raison :

Les index GIN sont le choix parfait pour les "valeurs composites" où vous effectuez une requête qui recherche un élément "composite". C'est l'index que vous voudrez probablement utiliser pour les structures de données jsonb, array ou hstore.

# db/migrate/*_create_books.rb
class CreateBooks < ActiveRecord::Migration[6.0]
  def change
    create_table :books do |t|
      t.string :title
      t.string :tags, array: true, default: []
      t.integer :ratings, array: true, default: []

      t.timestamps
    end
    add_index :books, :tags, using: 'gin'
    add_index :books, :ratings, using: 'gin'
  end
end

🔗 Partial Indexes

• Lorsque notre application filtre (90% du temps) sur des objets non-supprimée, non-publiée… Alors il faut placer un index partiel sur les index existant en ajoutant la clause where Voir plus

Raison :

Imaginez que vous ayez des articles dans votre application et que vous souhaitiez ajouter un champ datetime published_at indiquant quand l'article a été publié, puis filtrer les articles publiés par un auteur donné. Nous pouvons très probablement nous attendre à avoir besoin d'un index sur la colonne author_id dans un tel cas.

❌ add_index :articles, [:author_id, :published_at]
❌ add_index :articles, [:author_id, :deleted_at]

🟢 add_index :articles, :author_id, where: "published_at IS NULL"
🟢 add_index :articles, :author_id, where: "deleted_at IS NULL"