Maîtrisez les outils et bonnes pratiques DevOps - DevOps Knowledge Hub Maîtrisez les outils et bonnes pratiques DevOps - DevOps Knowledge Hub

Mise en œuvre des stratégies d'indexation JSONB de PostgreSQL pour des requêtes de style NoSQL haute performance

Choisissez des index JSONB PostgreSQL pour les requêtes de contenance, de recherche par clé, de filtres par plage et les conceptions relationnelles hybrides.

Implémentation de Stratégies d'Indexation JSONB dans PostgreSQL pour des Requêtes Haute Performance de Type NoSQL

L'indexation JSONB dans PostgreSQL est utile lorsque votre table possède des attributs flexibles mais que vos requêtes doivent rester rapides. JSONB fonctionne bien pour les métadonnées de produits, les charges utiles d'événements, les paramètres de profil et autres données semi-structurées, à condition de choisir des index qui correspondent aux opérateurs que vous utilisez réellement.

JSONB vs JSON

JSON (Stockage Texte)

  • Stocke la représentation textuelle exacte
  • Insertion plus rapide (pas de traitement)
  • Requêtes plus lentes
  • Préserve les espaces blancs et l'ordre des clés
  • Pas de classe d'opérateur GIN native JSONB ; les index d'expression sur les valeurs extraites restent possibles

JSONB (Stockage Binaire)

  • Stocké sous forme binaire décomposée
  • Léger surcoût à l'insertion
  • Généralement plus rapide à interroger que json, surtout avec index
  • Pas de préservation des espaces blancs
  • Prend en charge plusieurs stratégies d'indexation utiles
  • Recommandé pour la plupart des données JSON interrogeables

Opérations JSONB de Base

Configuration de la Table

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Insertion de données d'exemple
INSERT INTO products (name, metadata) VALUES
    ('Laptop', '{"brand": "Dell", "price": 999, "specs": {"ram": 16, "cpu": "i7"}}'),
    ('Phone', '{"brand": "Apple", "price": 899, "specs": {"ram": 6, "storage": 128}}'),
    ('Tablet', '{"brand": "Samsung", "price": 599, "specs": {"ram": 8, "storage": 256}}');

Opérateurs de Requête

-- Accès par clé : ->
SELECT name, metadata->'brand' AS brand FROM products;

-- Accès sous forme de texte : ->>
SELECT name, metadata->>'brand' AS brand_text FROM products;

-- Accès imbriqué
SELECT name, metadata->'specs'->>'ram' AS ram FROM products;

-- Vérification d'existence de clé : ?
SELECT * FROM products WHERE metadata ? 'brand';

-- Vérification d'existence d'une clé quelconque : ?|
SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'manufacturer'];

-- Vérification d'existence de toutes les clés : ?&
SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'price'];

-- Contenance : @>
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- Contenu par : <@
SELECT * FROM products WHERE '{"brand": "Apple"}' <@ metadata;

Stratégies d'Indexation JSONB

1. Index GIN (Index Inversé Généralisé)

Méthode d'indexation par défaut et la plus polyvalente.

-- Créer un index GIN sur toute la colonne JSONB
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Prend en charge les opérateurs : @>, ?, ?|, ?&
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata ? 'brand';

Taille de l'index vs performance des requêtes :

  • Taille d'index plus grande
  • Excellent pour les requêtes de contenance
  • Bon pour les vérifications d'existence de clés
  • Mises à jour plus lentes (tout le JSON est réindexé)

2. Index GIN avec jsonb_path_ops

Optimisé uniquement pour les requêtes de contenance.

CREATE INDEX idx_products_metadata_ops ON products USING GIN (metadata jsonb_path_ops);

-- Prend en charge uniquement : @>
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata @> '{"specs": {"ram": 16}}';

-- NON pris en charge : ?, ?|, ?&
-- SELECT * FROM products WHERE metadata ? 'brand';  -- N'utilisera pas l'index

Avantages :

  • Souvent plus petit que l'index GIN JSONB par défaut
  • Souvent plus rapide pour les requêtes de contenance
  • Meilleur pour les charges de travail avec beaucoup d'écritures

Quand l'utiliser :

  • Utilisation principalement de l'opérateur @>
  • Besoin d'index plus petits
  • Taux d'insertion/mise à jour élevés

3. Index d'Expression sur des Clés Spécifiques

Indexer des chemins JSONB spécifiques pour des performances maximales.

-- Indexer une valeur texte spécifique
CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));

-- Indexer une valeur numérique spécifique
CREATE INDEX idx_products_price ON products (((metadata->>'price')::NUMERIC));

-- Indexer une valeur imbriquée
CREATE INDEX idx_products_ram ON products (((metadata->'specs'->>'ram')::INTEGER));

-- Les requêtes utilisent ces index efficacement
SELECT * FROM products WHERE metadata->>'brand' = 'Apple';
SELECT * FROM products WHERE (metadata->>'price')::NUMERIC > 500;

Avantages :

  • Taille d'index la plus petite
  • Performance de requête la plus rapide pour des champs spécifiques
  • Avantages de l'index B-tree standard (requêtes de plage, tri)

Quand l'utiliser :

  • Champs spécifiques fréquemment interrogés
  • Besoin de requêtes de plage ou de tri
  • Surcharge d'index minimale souhaitée

4. Index Partiels

Indexer uniquement un sous-ensemble pertinent de données.

-- Indexer uniquement les produits avec la clé 'brand'
CREATE INDEX idx_products_branded ON products USING GIN (metadata)
    WHERE metadata ? 'brand';

-- Indexer uniquement les produits chers
CREATE INDEX idx_expensive_products ON products USING GIN (metadata)
    WHERE (metadata->>'price')::NUMERIC > 500;

-- Indexer les spécifications d'une marque spécifique
CREATE INDEX idx_apple_specs ON products USING GIN (metadata->'specs')
    WHERE metadata->>'brand' = 'Apple';

Avantages :

  • Index beaucoup plus petits
  • Mises à jour plus rapides pour les lignes non indexées
  • Besoins de stockage réduits

5. Index Composites

Combiner JSONB avec des colonnes régulières.

-- Indexer catégorie + métadonnées JSONB
CREATE TABLE products_v2 (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50),
    metadata JSONB
);

CREATE INDEX idx_products_v2_category ON products_v2 (category);
CREATE INDEX idx_products_v2_metadata_gin ON products_v2 USING GIN (metadata jsonb_path_ops);

-- Requête efficace combinant les deux
SELECT * FROM products_v2 
WHERE category = 'electronics' 
  AND metadata @> '{"brand": "Apple"}';

Tests de Performance

Configuration du Jeu de Données de Test

CREATE TABLE test_jsonb (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Insérer 1 million d'enregistrements
INSERT INTO test_jsonb (data)
SELECT jsonb_build_object(
    'user_id', generate_series(1, 1000000),
    'name', 'User ' || generate_series(1, 1000000),
    'age', (random() * 60 + 18)::INTEGER,
    'city', (ARRAY['NYC', 'LA', 'Chicago', 'Houston', 'Phoenix'])[floor(random() * 5 + 1)],
    'active', random() > 0.5,
    'metadata', jsonb_build_object(
        'score', (random() * 1000)::INTEGER,
        'level', floor(random() * 10 + 1)
    )
);

Comparer Différents Types d'Index

-- Sans index
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- Attendez-vous à un parcours séquentiel sur une grande table.

-- Index GIN
CREATE INDEX idx_gin ON test_jsonb USING GIN (data);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- Attendez-vous à un parcours d'index bitmap lorsque le planificateur l'estime utile.

-- GIN jsonb_path_ops
DROP INDEX idx_gin;
CREATE INDEX idx_gin_ops ON test_jsonb USING GIN (data jsonb_path_ops);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- Utilise souvent un index plus petit que la classe d'opérateur GIN JSONB par défaut.

-- Index d'expression
DROP INDEX idx_gin_ops;
CREATE INDEX idx_city ON test_jsonb ((data->>'city'));
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data->>'city' = 'NYC';
-- Souvent l'option la plus légère pour une clé fréquemment filtrée.

Les temps exacts dépendent du matériel, des paramètres PostgreSQL, de la largeur des lignes, de l'état du cache et de la distribution des données. Considérez EXPLAIN (ANALYZE, BUFFERS) de votre propre charge de travail comme source de vérité.

Modèles de Requêtes Avancés

Opérations sur les Tableaux

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO users (profile) VALUES
    ('{"name": "Alice", "tags": ["developer", "golang", "postgresql"]}'),
    ('{"name": "Bob", "tags": ["designer", "figma", "ui"]}');

-- Vérifier si le tableau contient un élément
SELECT * FROM users WHERE profile->'tags' ? 'golang';

-- Vérifier si le tableau contient l'un des éléments
SELECT * FROM users WHERE profile->'tags' ?| ARRAY['golang', 'python'];

-- Vérifier si le tableau contient tous les éléments
SELECT * FROM users WHERE profile->'tags' ?& ARRAY['developer', 'golang'];

-- Développer les éléments du tableau
SELECT 
    id,
    profile->>'name' AS name,
    jsonb_array_elements_text(profile->'tags') AS tag
FROM users;

Agrégation et Regroupement

-- Compter par champ JSONB
SELECT 
    data->>'city' AS city,
    COUNT(*) AS user_count
FROM test_jsonb
GROUP BY data->>'city'
ORDER BY user_count DESC;

-- Moyenne d'un champ numérique imbriqué
SELECT 
    data->>'city' AS city,
    AVG((data->'metadata'->>'score')::INTEGER) AS avg_score
FROM test_jsonb
GROUP BY data->>'city';

-- Agréger des objets JSONB
SELECT 
    data->>'city' AS city,
    jsonb_agg(data->'name') AS user_names
FROM test_jsonb
WHERE (data->>'age')::INTEGER > 30
GROUP BY data->>'city';

Recherche en Texte Intégral dans JSONB

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content JSONB
);

INSERT INTO documents (content) VALUES
    ('{"title": "PostgreSQL Guide", "body": "Learn PostgreSQL JSONB indexing"}'),
    ('{"title": "NoSQL vs SQL", "body": "Comparison of database paradigms"}');

-- Créer un index de recherche textuelle
CREATE INDEX idx_documents_fts ON documents 
    USING GIN (to_tsvector('english', content->>'body'));

-- Recherche en texte intégral
SELECT * FROM documents
WHERE to_tsvector('english', content->>'body') @@ to_tsquery('PostgreSQL & indexing');

Mettre à Jour les Champs JSONB

-- Mettre à jour tout le JSONB
UPDATE products SET metadata = '{"brand": "HP", "price": 1099}'::JSONB
WHERE id = 1;

-- Mettre à jour une clé spécifique avec jsonb_set
UPDATE products 
SET metadata = jsonb_set(metadata, '{price}', '1199')
WHERE id = 1;

-- Mettre à jour une valeur imbriquée
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,ram}', '32')
WHERE id = 1;

-- Ajouter une nouvelle clé
UPDATE products
SET metadata = metadata || '{"warranty": "2 years"}'::JSONB
WHERE id = 1;

-- Supprimer une clé
UPDATE products
SET metadata = metadata - 'warranty'
WHERE id = 1;

-- Supprimer une clé imbriquée
UPDATE products
SET metadata = metadata #- '{specs,storage}'
WHERE id = 1;

Meilleures Pratiques de Conception de Schéma

1. Extraire les Champs Fréquemment Interrogés

Mauvais : Tout dans JSONB

CREATE TABLE orders_bad (
    id SERIAL PRIMARY KEY,
    data JSONB  -- Contient : order_date, customer_id, status, total, items...
);

-- Requête lente
SELECT * FROM orders_bad 
WHERE data->>'status' = 'pending'
  AND (data->>'order_date')::DATE > '2024-01-01';

Bon : Approche hybride

CREATE TABLE orders_good (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    metadata JSONB,  -- Champs moins fréquemment interrogés
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE INDEX idx_orders_status ON orders_good(status);
CREATE INDEX idx_orders_date ON orders_good(order_date);

-- Requête rapide
SELECT * FROM orders_good
WHERE status = 'pending' AND order_date > '2024-01-01';

2. Normaliser les Données Répétées

Mauvais : Dénormalisé dans JSONB

CREATE TABLE logs_bad (
    id SERIAL PRIMARY KEY,
    log_data JSONB
);

-- Informations utilisateur répétées dans chaque journal
INSERT INTO logs_bad (log_data) VALUES
    ('{"user": {"id": 1, "name": "Alice", "email": "[email protected]"}, "action": "login"}'),
    ('{"user": {"id": 1, "name": "Alice", "email": "[email protected]"}, "action": "view_page"}');

Bon : Référence avec ID

CREATE TABLE logs_good (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    action VARCHAR(50),
    metadata JSONB,  -- Données spécifiques à l'action uniquement
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3. Utiliser des Contraintes sur JSONB

CREATE TABLE products_constrained (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    metadata JSONB,
    
    -- S'assurer que des clés spécifiques existent
    CONSTRAINT metadata_has_brand CHECK (metadata ? 'brand'),
    
    -- S'assurer que le prix est positif
    CONSTRAINT positive_price CHECK ((metadata->>'price')::NUMERIC > 0),
    
    -- S'assurer que la structure imbriquée existe
    CONSTRAINT has_specs CHECK (metadata ? 'specs')
);

Surveillance et Optimisation

Analyser les Performances des Requêtes JSONB

-- Activer le chronométrage
\timing on

-- Analyser la requête
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- Vérifier l'utilisation de l'index
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'products';

Identifier les Index Manquants

-- Trouver les tables avec des parcours séquentiels sur les colonnes JSONB
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_read
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND tablename IN (
      SELECT tablename 
      FROM information_schema.columns 
      WHERE data_type = 'jsonb'
  )
ORDER BY seq_scan DESC;

Vérification de la Taille de l'Index

SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND indexname LIKE '%jsonb%';

Pour les index GIN, commencez par la taille de l'index, le nombre de parcours, le coût d'écriture et les plans de requête avant de décider de le reconstruire.

Migration de NoSQL vers PostgreSQL JSONB

MongoDB vers PostgreSQL

// Document MongoDB
db.products.find({
    "brand": "Apple",
    "specs.ram": { $gte: 8 }
})

-- Équivalent PostgreSQL
SELECT * FROM products
WHERE metadata @> '{"brand": "Apple"}'
  AND (metadata->'specs'->>'ram')::INTEGER >= 8;

Créer un Script de Migration

import psycopg2
from pymongo import MongoClient
import json

# Connexion à MongoDB
mongo_client = MongoClient('mongodb://localhost:27017/')
mongo_db = mongo_client['mydb']
mongo_collection = mongo_db['products']

# Connexion à PostgreSQL
pg_conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="password"
)
pg_cursor = pg_conn.cursor()

# Migrer les données
for doc in mongo_collection.find():
    # Supprimer l'_id MongoDB
    doc.pop('_id', None)
    
    # Insérer dans PostgreSQL
    pg_cursor.execute(
        "INSERT INTO products (name, metadata) VALUES (%s, %s)",
        (doc.get('name'), json.dumps(doc))
    )

pg_conn.commit()
pg_cursor.close()
pg_conn.close()

Résumé des Meilleures Pratiques

  1. Utilisez JSONB, pas JSON : Le format binaire permet l'indexation
  2. Choisissez le bon type d'index : GIN pour la flexibilité, index d'expression pour des champs spécifiques
  3. Conception de schéma hybride : Extrayez les champs fréquemment interrogés dans des colonnes
  4. Évitez l'imbrication profonde : Gardez la structure JSONB raisonnablement plate
  5. Utilisez des contraintes : Validez la structure et les valeurs JSONB
  6. Surveillez les modèles de requêtes : Créez des index en fonction de l'utilisation réelle
  7. Maintenance régulière : VACUUM et REINDEX pour des performances optimales
  8. Évaluez avant la production : Testez avec des volumes de données réalistes
  9. Envisagez le partitionnement : Pour les très grandes tables JSONB
  10. Documentez votre schéma : Maintenez une documentation de la structure JSONB attendue

À Retenir

Commencez par un index GIN par défaut lorsque vous avez besoin de recherches flexibles de contenance et d'existence de clés. Utilisez jsonb_path_ops pour les charges de travail axées sur la contenance, et utilisez des index d'expression lorsqu'une clé JSONB devient un filtre ou un champ de tri fréquent. Si un champ est central pour votre application, promouvez-le en tant que colonne réelle.