Extensions utiles : pg_trgm, PostGIS & co

Prérequis : cours précédents (index).

Pourquoi

Réinventer la recherche floue ou la géo en applicatif, c'est lent et faux. Postgres s'étend : quelques CREATE EXTENSION résolvent des classes entières de problèmes, dans la base, avec ses index.

Concepts

• pg_trgm : recherche approximative (trigrammes) : WHERE name % 'dupont' (similarité) + index GIN/GiST. Adieu les LIKE %…% qui scannent tout.
• PostGIS : LA référence géo (POINT, distances, ST_DWithin, index GiST). Rayon de 5 km sur 10M de points en millisecondes.
• uuid-ossp / pgcrypto : gen_random_uuid() pour des ids publics non devinables (vs serial exposant le volume !).
• pg_stat_statements : déjà vu (DevOps 6.2) — rappel : sans elle, pas de méthode perf.

Exemple : recherche tolérante

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);
SELECT name FROM users WHERE name % 'martin' ORDER BY similarity(name, 'martin') DESC LIMIT 10;
-- 'martain', 'martine' trouvés : fautes de frappe absorbées

Cas réel

Recherche « qui marche pas » en ILIKE %terme% sur 1M de lignes : 3 s + 100 % CPU. pg_trgm + GIN : 15 ms + tolérance aux fautes. Les users ont cru à « une IA » — c'était un index.

Pièges

• Extension installée mais pas CREATE EXTENSION dans la base (c'est par base !) : « function does not exist » mystérieux.
• PostGIS sans index GiST : aussi lent que le calcul applicatif. L'extension sans l'index = la moitié du gain.
• Ids serial exposés en public (/users/1245) : volume devinable + énumération. UUID publics, serial internes.

À retenir

pg_trgm (flou), PostGIS (géo + GiST), UUID publics, stat_statements partout.