github/postgresql-optimization

Que fait postgresql-optimization ?

Assistant de développement spécifique à PostgreSQL, axé sur les fonctionnalités uniques, les types de données avancés et les capacités exclusives de PostgreSQL. Couvre les opérations JSONB, les types tableaux, les types personnalisés, les types géométriques et de plage, la recherche en texte intégral, les fonctions de fenêtre et l'écosystème des extensions PostgreSQL.

Fonctionnalités principales

Opérations JSONB

• Manipulation de documents JSON semi-structurés avec les opérateurs ->, ->>, #>, #>>
• Indexation avec des index GIN pour des recherches rapides
• Fonctions comme jsonb_each, jsonb_object_keys, jsonb_set, jsonb_strip_nulls
• Requêtes avec @>, ?, ?|, ?& pour la vérification de présence et de contenance

Types tableaux

• Déclaration de colonnes comme TEXT[], INTEGER[][]
• Accès aux éléments avec la syntaxe array[1]
• Fonctions et opérateurs : ANY, ALL, unnest, array_agg, array_cat, array_length
• Index GIN sur les colonnes de type tableau

Types personnalisés (CREATE TYPE)

• Création de types composites CREATE TYPE adresse AS (rue TEXT, ville TEXT, code_postal TEXT)
• Types énumérés CREATE TYPE statut_commande AS ENUM ('en_attente', 'expédié', 'livré')
• Types de plage personnalisés

Types géométriques et de plage

• Types natifs : point, line, lseg, box, path, polygon, circle
• Types de plage : int4range, int8range, numrange, tsrange, daterange
• Fonctions de plage : upper, lower, isempty, overlaps, contains
• Extension PostGIS pour les données géospatiales avancées

Recherche en texte intégral

• Vecteurs de texte avec to_tsvector et requêtes avec to_tsquery
• Opérateur @@ pour la correspondance
• Classement par pertinence avec ts_rank et ts_rank_cd
• Index GIN sur les colonnes tsvector
• Dictionnaires et configurations de recherche linguistique

Fonctions de fenêtre

• Fonctions de fenêtre : ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
• Fonctions d'agrégation avec fenêtre : SUM() OVER (...), AVG() OVER (...)
• Clauses PARTITION BY, ORDER BY, ROWS/RANGE
• Fonctions de fenêtre personnalisées avec WINDOW

Extensions PostgreSQL

• PostGIS : données géospatiales
• hstore : paires clé-valeur
• pg_trgm : similarité de texte et recherche floue
• uuid-ossp : génération d'UUID
• pgcrypto : fonctions cryptographiques
• pg_stat_statements : analyse des performances
• citext : chaînes insensibles à la casse
• ltree : types de données pour hiérarchies

Utilisation recommandée

Ce skill est idéal pour :

• Optimiser des requêtes PostgreSQL existantes
• Concevoir des schémas de base de données utilisant les types avancés
• Implémenter la recherche en texte intégral
• Travailler avec des données JSON semi-structurées
• Analyser des séries temporelles avec les fonctions de fenêtre
• Étendre les capacités de PostgreSQL avec des extensions

Exemples de code

-- Exemple JSONB
SELECT * FROM produits WHERE donnees @> '{"categorie": "electronique"}';

-- Exemple type tableau
SELECT * FROM utilisateurs WHERE 'admin' = ANY(roles);

-- Exemple recherche textuelle
SELECT titre, ts_rank(vecteur, requete) AS pertinence
FROM documents, to_tsquery('français & recherche') AS requete
WHERE vecteur @@ requete
ORDER BY pertinence DESC;

-- Exemple fonction de fenêtre
SELECT employe_id, departement, salaire,
  RANK() OVER (PARTITION BY departement ORDER BY salaire DESC) AS rang
FROM employes;