0Pricing
SQL Academy · Leçon

OLTP ou OLAP

Bases de données transactionnelles ou analytiques.

OLTP ou OLAP est une leçon SQL Academy gratuite sur CoddyKit. Ceci est la leçon 1 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Academy, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Academy comprend 4 leçons au total.

Que sont OLTP et OLAP ?

Les bases de données ne conviennent pas toutes à tous les usages. Deux charges de travail fondamentalement différentes ont influencé la manière dont nous concevons et exploitons les bases de données : OLTP (traitement des transactions en ligne) et OLAP (traitement analytique en ligne).

Comprendre cette différence est essentiel pour tout spécialiste des données. Le bon choix entre OLTP et OLAP détermine la rapidité des requêtes, le coût du stockage et l’architecture globale de votre système de données.

OLTP : conçu pour les transactions

Les systèmes OLTP gèrent un grand nombre d’opérations courtes et rapides : des insertions, des mises à jour et des suppressions qui correspondent aux événements métier en temps réel. Il peut s’agir de passer une commande, de traiter un paiement ou de mettre à jour une fiche client.

Les principales caractéristiques d’OLTP sont une faible latence par opération, une forte concurrence et une cohérence stricte. Chaque transaction doit respecter les propriétés ACID afin de préserver l’intégrité des données.

-- OLTP example: inserting a new order
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1042, 88, 3, CURRENT_DATE);

-- Immediately update inventory
UPDATE inventory
SET stock = stock - 3
WHERE product_id = 88;

OLAP : conçu pour l’analyse

Les systèmes OLAP sont optimisés pour les requêtes complexes qui parcourent de grandes quantités de données historiques afin de faire ressortir des tendances, des schémas et des synthèses. Les analystes métier et les scientifiques des données utilisent OLAP pour répondre à des questions telles que : « Quelles ont été nos ventes totales par région au dernier trimestre ? »

Les requêtes OLAP agrègent souvent des millions de lignes et impliquent plusieurs jointures entre des tables de faits et de dimensions. La rapidité des écritures individuelles est secondaire ; ce qui compte, c’est le débit de lecture et la souplesse des requêtes.

-- OLAP example: total sales by region for Q1 2024
SELECT
    d.region,
    SUM(f.sales_amount) AS total_sales,
    COUNT(f.order_id)   AS order_count
FROM fact_sales f
JOIN dim_date   dd ON f.date_key   = dd.date_key
JOIN dim_store  d  ON f.store_key  = d.store_key
WHERE dd.year = 2024
  AND dd.quarter = 1
GROUP BY d.region
ORDER BY total_sales DESC;

Comparer les deux systèmes côte à côte

La manière la plus simple de retenir la différence consiste à réfléchir à qui utilise chaque système et à la manière dont il l’utilise :

  • OLTP : utilisé par les couches serveur des applications ; des milliers d’utilisateurs simultanés ; chaque requête porte sur quelques lignes.
  • OLAP : utilisé par les analystes et les outils de génération de rapports ; moins de requêtes simultanées, mais chacune parcourt des millions de lignes.

Ces modes d’accès opposés entraînent des conceptions de schéma, des stratégies d’indexation et même des choix de matériel très différents.

-- OLTP: lookup a single customer's latest order (row-level access)
SELECT o.order_id, o.order_date, o.total_amount
FROM orders o
WHERE o.customer_id = 1042
ORDER BY o.order_date DESC
LIMIT 1;

-- OLAP: monthly revenue trend over the past year (aggregate scan)
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount)               AS revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Conception de schéma : normalisé ou dénormalisé

Les bases de données OLTP privilégient les schémas normalisés (3NF ou plus) afin d’éliminer la redondance et de rendre les écritures efficaces. Chaque entité réside dans sa propre table, ce qui réduit la quantité de données manipulées par transaction.

Les bases de données OLAP privilégient les schémas dénormalisés — en particulier les schémas en étoile et en flocon — où les données sont préalablement jointes et redondantes. Cela élimine les jointures coûteuses au moment de la requête et permet aux moteurs de stockage en colonnes de parcourir les données plus rapidement.

-- Normalized OLTP design (3NF)
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name        VARCHAR(100),
    email       VARCHAR(150) UNIQUE
);

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date  DATE,
    total       NUMERIC(10,2)
);

-- Denormalized OLAP fact table (star schema)
CREATE TABLE fact_sales (
    sale_id      BIGINT PRIMARY KEY,
    customer_key INT,
    date_key     INT,
    product_key  INT,
    region       VARCHAR(50),
    category     VARCHAR(50),
    amount       NUMERIC(12,2)
);

Les stratégies d’indexation diffèrent

Les systèmes OLTP s’appuient largement sur des index en arbre B sur les clés primaires et étrangères afin d’effectuer rapidement des recherches sur une seule ligne et des jointures efficaces au sein d’une transaction.

Les systèmes OLAP tirent profit des index bitmap, du stockage en colonnes et du partitionnement. Parcourir une colonne entière (par exemple, tous les montants des ventes) est bien plus efficace lorsque les données sont stockées colonne par colonne plutôt que ligne par ligne.

-- OLTP: B-tree index for fast order lookup by customer
CREATE INDEX idx_orders_customer
    ON orders (customer_id);

-- OLTP: compound index for range queries
CREATE INDEX idx_orders_date_customer
    ON orders (order_date, customer_id);

-- OLAP: partition fact table by year to prune scan
CREATE TABLE fact_sales_2024
    PARTITION OF fact_sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Concurrence et verrouillage

Les systèmes OLTP doivent gérer des milliers d’écritures concurrentes sans conflits. Les bases de données utilisent le verrouillage au niveau des lignes et le MVCC (contrôle de la concurrence multiversion), afin que les lectures ne bloquent jamais les écritures, et inversement.

Les requêtes OLAP sont principalement en lecture seule. Le verrouillage pose rarement problème, mais les parcours de longue durée peuvent consommer beaucoup de CPU et d’entrées-sorties. La plupart des entrepôts de données exécutent les traitements OLAP sur un système distinct, alimenté par des traitements ETL par lots ou par CDC (capture des modifications des données) depuis la source OLTP.

-- OLTP: explicit transaction with row-level lock
BEGIN;

SELECT balance
FROM accounts
WHERE account_id = 7
FOR UPDATE;

UPDATE accounts
SET balance = balance - 200
WHERE account_id = 7;

COMMIT;

ETL : faire le lien entre OLTP et OLAP

Comme OLTP et OLAP reposent sur des conceptions incompatibles, les organisations utilisent des flux ETL (extraction, transformation et chargement) pour copier et remodeler les données de la base transactionnelle vers l’entrepôt analytique selon une planification donnée (chaque nuit, chaque heure ou presque en temps réel).

Le processus ETL transforme les lignes OLTP normalisées en enregistrements dénormalisés de faits et de dimensions, tout en appliquant la logique métier nécessaire (par exemple, la conversion des devises ou la segmentation des clients).

-- Simplified ETL INSERT from OLTP orders into OLAP fact table
INSERT INTO fact_sales (
    customer_key,
    date_key,
    product_key,
    amount
)
SELECT
    dc.customer_key,
    dd.date_key,
    dp.product_key,
    o.total_amount
FROM orders o
JOIN dim_customer dc ON dc.source_customer_id = o.customer_id
JOIN dim_date     dd ON dd.calendar_date       = o.order_date
JOIN dim_product  dp ON dp.source_product_id   = o.product_id
WHERE o.order_date = CURRENT_DATE - INTERVAL '1 day'
  AND o.order_id NOT IN (SELECT source_order_id FROM fact_sales);

Schémas de requêtes OLAP courants

Les requêtes OLAP impliquent presque toujours des agrégations (SUM, COUNT, AVG), un regroupement selon plusieurs dimensions et un filtrage par plages de dates ou par catégories. Ce sont les éléments fondamentaux des tableaux de bord et des rapports métier.

Les fonctions de fenêtrage sont particulièrement puissantes dans les charges OLAP : elles permettent de comparer les chiffres de chaque période à ceux de la période précédente sans recourir à une auto-jointure.

-- Year-over-year revenue comparison using a window function
SELECT
    dd.year,
    dd.quarter,
    SUM(f.amount)                                          AS revenue,
    LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
                             ORDER BY dd.year)             AS prev_year_revenue,
    ROUND(
        100.0 * (SUM(f.amount) -
                 LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
                                         ORDER BY dd.year))
        / NULLIF(LAG(SUM(f.amount)) OVER (PARTITION BY dd.quarter
                                          ORDER BY dd.year), 0)
    , 2)                                                   AS yoy_pct_change
FROM fact_sales f
JOIN dim_date dd ON f.date_key = dd.date_key
GROUP BY dd.year, dd.quarter
ORDER BY dd.quarter, dd.year;

HTAP : les frontières s’estompent

Des systèmes modernes comme TiDB, SingleStore et PostgreSQL + extensions columnaires implémentent le HTAP (traitement hybride transactionnel et analytique). Ils cherchent à gérer les deux types de charges dans un moteur unique, en évitant la complexité opérationnelle liée à la maintenance de systèmes OLTP et OLAP distincts.

HTAP y parvient en stockant simultanément les données dans deux formats : un stockage en lignes pour les écritures transactionnelles et un stockage en colonnes pour les lectures analytiques, synchronisés automatiquement.

-- PostgreSQL with cstore_fdw (columnar extension) example
-- Analytical table stored in columnar format
CREATE FOREIGN TABLE fact_sales_columnar (
    date_key     INT,
    product_key  INT,
    region       VARCHAR(50),
    amount       NUMERIC(12,2)
)
SERVER cstore_server
OPTIONS (filename '/data/fact_sales_columnar');

-- Regular OLTP table remains row-based
-- Both can be queried in the same SQL statement
SELECT f.region, SUM(f.amount)
FROM fact_sales_columnar f
GROUP BY f.region;

Choisir le bon système

Le choix entre OLTP et OLAP (ou HTAP) dépend principalement de votre charge de travail :

  • Si vous développez une application qui enregistre des événements en temps réel, utilisez une base de données OLTP (PostgreSQL, MySQL, SQL Server).
  • Si vous développez une couche de production de rapports sur des données historiques, utilisez un entrepôt OLAP (BigQuery, Redshift, Snowflake, ClickHouse).
  • Si vous avez besoin des deux et souhaitez simplifier l’exploitation, évaluez les solutions HTAP.

De nombreuses architectures de production utilisent les deux : une base de données OLTP comme système de référence et un entrepôt de données distinct pour l’analytique, reliés par un flux ETL.

-- Quick diagnostic: check table access pattern
-- High seq_scan relative to idx_scan = analytical (OLAP-like) load
SELECT
    relname              AS table_name,
    seq_scan,
    idx_scan,
    n_live_tup           AS live_rows
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

Vérification des connaissances

Vérifiez votre compréhension des principales différences entre les systèmes OLTP et OLAP.

Récapitulatif de la leçon

OLTP et OLAP — points essentiels :

  • OLTP gère les charges transactionnelles en temps réel : des écritures rapides et concurrentes au niveau des lignes, avec des garanties ACID.
  • OLAP gère les charges analytiques : des agrégations complexes sur de grands ensembles de données historiques, à l’aide de schémas dénormalisés.
  • La conception du schéma dépend de la charge de travail : schéma normalisé (3NF) pour OLTP, schéma en étoile ou en flocon pour OLAP.
  • Les flux ETL font le lien entre les deux systèmes en chargeant les données OLTP transformées dans l’entrepôt analytique.
  • Les systèmes HTAP tentent de prendre en charge les deux types de charges depuis un moteur unique, grâce à un stockage simultané en lignes et en colonnes.

Choisir la bonne architecture dès le départ évite des migrations pénibles par la suite et garantit que vos requêtes s’exécutent à la vitesse attendue par vos utilisateurs.

Questions Fréquemment Posées

La leçon « OLTP ou OLAP » est-elle gratuite ?

Oui — le texte complet de « OLTP ou OLAP » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Academy, passe à CoddyKit PRO. Le cours SQL Academy comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « OLTP ou OLAP » ?

Bases de données transactionnelles ou analytiques. Tu pratiques SQL Academy avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Academy ?

Aucune expérience préalable n'est requise. SQL Academy sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 1 sur 4.

Combien de temps prend la leçon « OLTP ou OLAP » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Academy ?

Oui. Chaque leçon SQL Academy inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

  1. OLTP ou OLAP
  2. Tables de faits et de dimensions
  3. Schémas en étoile et en flocon
  4. Écrire des requêtes analytiques
← Retour à SQL Academy