0Pricing
SQL Academy · Lezione

OLTP vs OLAP

Database transazionali e analitici.

OLTP vs OLAP è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 1 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Cosa sono OLTP e OLAP?

I database non sono tutti uguali. Due carichi di lavoro fondamentalmente diversi hanno influenzato il modo in cui progettiamo e gestiamo i database: OLTP (Online Transaction Processing) e OLAP (Online Analytical Processing).

Comprendere la differenza è essenziale per chiunque lavori con i dati. La scelta corretta tra OLTP e OLAP determina la velocità delle query, i costi di archiviazione e l'architettura complessiva del sistema dati.

OLTP: progettato per le transazioni

I sistemi OLTP gestiscono un volume elevato di operazioni brevi e rapide: inserimenti, aggiornamenti ed eliminazioni che riflettono eventi aziendali in tempo reale. Tra gli esempi figurano l'inserimento di un ordine, l'elaborazione di un pagamento o l'aggiornamento di un record cliente.

Le proprietà principali di OLTP sono: bassa latenza per operazione, elevata concorrenza e forte coerenza. Ogni transazione deve rispettare i requisiti ACID per proteggere l'integrità dei dati.

-- 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: progettato per l'analisi

I sistemi OLAP sono ottimizzati per query complesse che analizzano grandi quantità di dati storici per rivelare tendenze, schemi e riepiloghi. Gli analisti aziendali e i data scientist usano OLAP per rispondere a domande come: «Quali sono state le vendite totali per regione lo scorso trimestre?»

Le query OLAP spesso aggregano milioni di righe e coinvolgono più join tra tabelle dei fatti e delle dimensioni. La velocità delle singole scritture passa in secondo piano; contano invece la capacità di lettura e la flessibilità delle query.

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

Confronto diretto

Il modo più semplice per ricordare la distinzione è pensare a chi usa ciascun sistema e a come lo usa:

  • OLTP: utilizzato dai backend delle applicazioni; migliaia di utenti concorrenti; ogni query accede a poche righe.
  • OLAP: utilizzato dagli analisti e dagli strumenti di reporting; meno query concorrenti, ma ciascuna analizza milioni di righe.

Questi schemi di accesso contrapposti portano a progettazioni dello schema, strategie di indicizzazione e persino scelte hardware molto diverse.

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

Progettazione dello schema: normalizzato o denormalizzato

I database OLTP prediligono gli schemi normalizzati (3NF o superiore) per eliminare la ridondanza e rendere efficienti le scritture. Ogni entità risiede nella propria tabella, riducendo i dati coinvolti in ogni transazione.

I database OLAP prediligono gli schemi denormalizzati — in particolare gli schemi a stella e a fiocco di neve — in cui i dati sono pre-uniti e ridondanti. In questo modo si eliminano i join costosi al momento dell'interrogazione e si consente ai motori di archiviazione colonnare di analizzare i dati più velocemente.

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

Le strategie di indicizzazione sono diverse

I sistemi OLTP fanno ampio affidamento sugli indici B-tree delle chiavi primarie ed esterne per consentire ricerche rapide di singole righe e join efficienti all'interno di una transazione.

I sistemi OLAP traggono vantaggio dagli indici bitmap, dall'archiviazione colonnare e dal partizionamento. Analizzare un'intera colonna (ad esempio, tutti gli importi delle vendite) è molto più efficiente quando i dati sono archiviati per colonna anziché per riga.

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

Concorrenza e gestione dei lock

I sistemi OLTP devono gestire migliaia di scritture simultanee senza conflitti. I database usano il blocco a livello di riga e MVCC (Multi-Version Concurrency Control), in modo che le operazioni di lettura non blocchino mai quelle di scrittura e viceversa.

Le query OLAP sono prevalentemente di sola lettura. I lock raramente costituiscono un problema, ma le scansioni di lunga durata possono consumare una quantità significativa di CPU e I/O. La maggior parte dei data warehouse esegue l'OLAP su un sistema separato, alimentato da processi ETL batch o CDC (Change Data Capture) provenienti dalla sorgente 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: collegare OLTP e OLAP

Poiché OLTP e OLAP hanno progettazioni incompatibili, le organizzazioni eseguono pipeline ETL (Extract, Transform, Load) per copiare e rimodellare i dati dal database transazionale al data warehouse analitico secondo una pianificazione prestabilita (ogni notte, ogni ora o quasi in tempo reale).

Il processo ETL trasforma le righe OLTP normalizzate in record denormalizzati di fatti e dimensioni, applicando al contempo la logica aziendale (ad esempio, la conversione valutaria e la segmentazione dei clienti).

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

Pattern tipici delle query OLAP

Le query OLAP prevedono quasi sempre aggregazioni (SUM, COUNT, AVG), raggruppamenti su più dimensioni e filtri per intervalli di date o categorie. Questi sono gli elementi fondamentali di dashboard e report aziendali.

Le funzioni finestra sono particolarmente potenti nei carichi di lavoro OLAP: consentono di confrontare i dati di ciascun periodo con quelli del periodo precedente senza ricorrere a un self-join.

-- 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: ridurre la distinzione

I sistemi moderni come TiDB, SingleStore e PostgreSQL + estensioni colonnari implementano HTAP (Hybrid Transactional/Analytical Processing). L'obiettivo è gestire entrambi i carichi di lavoro con un unico motore, evitando la complessità operativa derivante dalla gestione di sistemi OLTP e OLAP separati.

HTAP raggiunge questo obiettivo archiviando i dati simultaneamente in due formati: un row store per le scritture transazionali e un column store per le letture analitiche, mantenuti automaticamente sincronizzati.

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

Scegliere il sistema giusto

La scelta tra OLTP e OLAP (o HTAP) dipende dal carico di lavoro principale:

  • Se sta creando un'applicazione che registra eventi in tempo reale, utilizzi un database OLTP (PostgreSQL, MySQL, SQL Server).
  • Se sta creando un livello di reporting sui dati storici, utilizzi un data warehouse OLAP (BigQuery, Redshift, Snowflake, ClickHouse).
  • Se ha bisogno di entrambi e desidera semplificare la gestione operativa, valuti le opzioni HTAP.

Molte architetture di produzione utilizzano entrambi: un database OLTP come sistema di riferimento e un data warehouse separato per l'analisi, collegati da una pipeline 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;

Verifica delle conoscenze

Verifichi la Sua comprensione delle principali differenze tra i sistemi OLTP e OLAP.

Riepilogo della lezione

OLTP e OLAP — concetti chiave:

  • OLTP gestisce carichi di lavoro transazionali in tempo reale: scritture rapide e simultanee a livello di riga, con garanzie ACID.
  • OLAP gestisce carichi di lavoro analitici: aggregazioni complesse su grandi insiemi di dati storici, utilizzando schemi denormalizzati.
  • La progettazione dello schema segue il carico di lavoro: normalizzato (3NF) per OLTP, a stella o a fiocco di neve per OLAP.
  • Le pipeline ETL collegano i due sistemi, caricando nel data warehouse analitico i dati OLTP trasformati.
  • I sistemi HTAP tentano di gestire entrambi i carichi di lavoro con un unico motore, utilizzando un'archiviazione simultanea per righe e colonne.

Scegliere l'architettura giusta fin dall'inizio evita migrazioni difficili in seguito e garantisce che le query vengano eseguite alla velocità prevista dagli utenti.

Domande Frequenti

La lezione «OLTP vs OLAP» è gratuita?

Sì — il testo completo di «OLTP vs OLAP» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «OLTP vs OLAP»?

Database transazionali e analitici. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 1 di 4.

Quanto tempo richiede la lezione «OLTP vs OLAP»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. OLTP vs OLAP
  2. Tabelle dei fatti e delle dimensioni
  3. Schemi a stella e a fiocco di neve
  4. Scrivere query analitiche
← Torna a SQL Academy