Hub de Connaissances DevOps
Hub de Connaissances DevOps

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

Maîtrisez les stratégies d'indexation JSONB de PostgreSQL pour des performances de type NoSQL. Guide complet couvrant les index GIN, les index d'expression, l'optimisation des requêtes et les modèles de conception de schémas hybrides.

28 vues

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

Introduction

PostgreSQL JSONB (JSON Binary) combine la flexibilité des bases de données orientées documents avec la puissance du SQL relationnel. Contrairement au stockage JSON traditionnel, le format JSONB est stocké sous une forme binaire décomposée qui permet une indexation et des requêtes efficaces. Ce guide couvre les stratégies d'indexation JSONB avancées pour atteindre des performances de type NoSQL tout en maintenant les garanties ACID.

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 support d'indexation

JSONB (Stockage Binaire)

  • Stocké sous forme binaire décomposée
  • Légère surcharge à l'insertion
  • Requêtes beaucoup plus rapides
  • Ne préserve pas les espaces blancs
  • Support complet de l'indexation
  • Recommandé pour la plupart des cas d'utilisation

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 en tant que texte : ->>
SELECT name, metadata->>'brand' AS brand_text FROM products;

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

-- Vérifier l'existence d'une clé : ?
SELECT * FROM products WHERE metadata ? 'brand';

-- Vérifier si l'une des clés existe : ?|
SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'manufacturer'];

-- Vérifier si toutes les clés existent : ?&
SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'price'];

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

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

Stratégies d'indexation JSONB

1. Index GIN (Generalized Inverted Index)

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);

-- Supporte 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 importante
- Excellent pour les requêtes de confinement (containment)
- 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 confinement.

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

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

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

Avantages :
- Taille d'index réduite de 50 à 70%
- Requêtes de confinement plus rapides
- Meilleur pour les charges de travail intensives en écriture

Quand l'utiliser :
- Utilisation principale 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

Indexation de chemins JSONB spécifiques pour une performance maximale.

-- Indexer une valeur textuelle 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 minimale
- Performance de requête la plus rapide pour des champs spécifiques
- Avantages des index B-tree standard (requêtes par plage, tri)

Quand l'utiliser :
- Champs spécifiques fréquemment interrogés
- Besoin de requêtes par plage ou de tris
- Volonté de minimiser la surcharge de l'index

4. Index partiels

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

-- Indexer uniquement les produits ayant 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_category_metadata ON products_v2 (category, metadata jsonb_path_ops);

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

Comparaison des performances

Configuration du jeu de données de test

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

-- Insertion d'un 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)
    )
);

Benchmark de différents types d'index

-- Sans index
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- Résultat : ~200ms, Seq Scan

-- Index GIN
CREATE INDEX idx_gin ON test_jsonb USING GIN (data);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- Résultat : ~5ms, Bitmap Index Scan

-- 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"}';
-- Résultat : ~3ms, Bitmap Index Scan (index 40% plus petit)

-- 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';
-- Résultat : ~2ms, Bitmap Index Scan (index le plus petit)

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 l'é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 groupement

-- 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égation d'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 plein texte dans du 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 de texte
CREATE INDEX idx_documents_fts ON documents 
    USING GIN (to_tsvector('english', content->>'body'));

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

Mise à jour des 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 en conception de schéma

1. Extraire les champs fréquemment interrogés

Mauvais : Tout dans le 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 le JSONB

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

-- Informations utilisateur répétées dans chaque log
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 un ID

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

3. Utiliser des contraintes sur le 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 la performance 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 scans 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 fragmentation (bloat) de l'index

CREATE EXTENSION pgstattuple;

SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    100 - pgstatindex(indexrelid).avg_leaf_density AS bloat_pct
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND indexname LIKE '%jsonb%';

Migration de NoSQL vers PostgreSQL JSONB

De 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()

# Migration des données
for doc in mongo_collection.find():
    # Supprimer l' _id de MongoDB
    doc.pop('_id', None)

    # Insertion 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. Utiliser JSONB, pas JSON : Le format binaire permet l'indexation
  2. Choisir le bon type d'index : GIN pour la flexibilité, index d'expression pour des champs spécifiques
  3. Conception de schéma hybride : Extraire les champs fréquemment interrogés dans des colonnes
  4. Éviter l'imbrication profonde : Garder la structure JSONB raisonnablement plate
  5. Utiliser des contraintes : Valider la structure et les valeurs JSONB
  6. Surveiller les modèles de requêtes : Créer des index basés sur l'usage réel
  7. Maintenance régulière : VACUUM et REINDEX pour une performance optimale
  8. Benchmark avant la production : Tester avec des volumes de données réalistes
  9. Envisager le partitionnement : Pour les tables JSONB très volumineuses
  10. Documenter votre schéma : Maintenir une documentation de la structure JSONB attendue

Conclusion

PostgreSQL JSONB offre la flexibilité du NoSQL avec la puissance du SQL :

  • Schéma flexible : Stockage de données semi-structurées
  • Requêtes rapides : Multiples stratégies d'indexation
  • Garanties ACID : Support transactionnel complet
  • Opérateurs riches : Capacités de requêtage puissantes
  • Approche hybride : Combinaison avec la conception relationnelle

Commencez par des index GIN pour un usage général, optimisez avec des index d'expression pour les requêtes spécifiques à fort trafic, et maintenez une conception de schéma hybride pour une performance optimale.