Optimisation des fonctions pour bosser avec de gros volumes de données

Quand on parle d’optimisation de fonctions dans PostgreSQL, on pense généralement à deux trucs clés : l’indexation et le partitionnement. Ces deux techniques t’aident à traiter de gros volumes de données plus vite, en évitant des calculs inutiles et en allant chercher les données pile là où il faut. On va voir ça en détail.

Les index dans le monde des bases de données, c’est comme les index dans les bouquins. Quand tu cherches une info dans un livre, tu ne lis pas toutes les pages à la suite. Tu ouvres l’index, tu trouves le sujet et tu vas direct à la bonne page. Les index dans PostgreSQL font à peu près pareil.

Création d’index

Les index se créent avec la commande CREATE INDEX. Voilà un exemple simple :

-- On crée un index sur la colonne id de la table users pour accélérer la recherche
CREATE INDEX idx_users_id ON users (id);

Maintenant, si tu fais une requête du genre :

SELECT * FROM users WHERE id = 42;

PostgreSQL va utiliser l’index créé pour trouver la ligne qu’il te faut super vite.

Exemple : Optimisation d’une fonction avec des index

Imaginons qu’on a une fonction qui sélectionne les infos de commandes dans la table orders pour un utilisateur :

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE user_id = user_id;
END; 
$$ LANGUAGE plpgsql;

Si la table orders contient des millions de lignes, la fonction va ramer. La solution ? On crée un index sur user_id :

CREATE INDEX idx_orders_user_id ON orders (user_id);

Maintenant, la requête dans la fonction sera beaucoup plus rapide, car PostgreSQL va utiliser l’index pour chercher les lignes.

Types d’index

PostgreSQL gère plusieurs types d’index, mais les plus courants, c’est B-TREE et GIN. Voilà un petit comparatif :

Si tu veux creuser les index, va jeter un œil à la doc officielle PostgreSQL.

Partitionnement des données

Si les index servent à accélérer la recherche, le partitionnement, c’est une méthode pour découper une table en plus petits « morceaux » (partitions). C’est super utile quand t’as une table énorme.

Imagine que t’as une table orders qui stocke les commandes des 10 dernières années. Si tu fais une requête pour trouver les commandes du dernier mois, PostgreSQL va quand même scanner toute la table, et ça coûte cher. Le partitionnement règle ce souci en découpant les données, par exemple par année.

Créer une table partitionnée

Voilà comment tu peux créer une table partitionnée :

-- On crée la table orders comme partition parent
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- On crée des tables enfants pour chaque année
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

Maintenant, si tu fais une requête du genre :

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

PostgreSQL va direct piger qu’il doit chercher seulement dans la table orders_2023, au lieu de tout scanner.

Utiliser le partitionnement dans les fonctions

Imaginons qu’on a une fonction qui sélectionne les commandes pour une année donnée. Grâce au partitionnement, les requêtes dans la fonction seront plus rapides, car PostgreSQL va bosser avec la table enfant qui va bien.

CREATE OR REPLACE FUNCTION get_orders_by_year(year INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE order_date >= make_date(year, 1, 1) 
      AND order_date < make_date(year + 1, 1, 1);
END;
$$ LANGUAGE plpgsql;

Cas pratiques

1. Cas d’indexation

Recherche sur des chaînes : si t’as une table avec des produits et que tu cherches souvent par nom, crée un index sur le champ name :

CREATE INDEX idx_products_name ON products (name);

Accélérer le tri : si tu fais souvent des tris par date dans tes requêtes, crée un index :

CREATE INDEX idx_orders_date ON orders (order_date);

2. Cas de partitionnement

Données historiques : si ta table contient des données avec un timestamp, le partitionnement par jour, mois ou année va vraiment accélérer les requêtes.

Données géographiques : si ta table contient des données par pays, crée des partitions pour chaque pays.

Erreurs potentielles et comment les éviter

Plein de devs font l’erreur de créer trop d’index. Résultat : les insertions et updates sont plus lentes, parce que PostgreSQL doit mettre à jour les index à chaque modif de la table. Conseil : crée des index seulement sur les champs que tu utilises souvent dans les conditions ou le tri.

Autre erreur classique : un mauvais partitionnement. Si tu fais trop de petites partitions (genre par jour au lieu de par mois), tu risques d’avoir des soucis de gestion et des surcoûts.