OLTP a OLAP
Bazy transakcyjne a analityczne
OLTP a OLAP to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 1 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.
Czym są OLTP i OLAP
Bazy danych nie są rozwiązaniem uniwersalnym. Dwa zasadniczo różne rodzaje obciążenia ukształtowały sposób projektowania i obsługi baz danych: OLTP (Online Transaction Processing) oraz OLAP (Online Analytical Processing).
Zrozumienie tej różnicy jest niezbędne dla każdego specjalisty ds. danych. Właściwy wybór między OLTP i OLAP wpływa na szybkość zapytań, koszt przechowywania danych oraz ogólną architekturę systemu danych.
OLTP: systemy stworzone do obsługi transakcji
Systemy OLTP obsługują dużą liczbę krótkich, szybkich operacji — wstawiania, aktualizowania i usuwania danych odzwierciedlających zdarzenia biznesowe w czasie rzeczywistym. Przykładami są składanie zamówienia, przetwarzanie płatności lub aktualizowanie danych klienta.
Najważniejsze cechy OLTP to małe opóźnienia pojedynczych operacji, wysoka współbieżność i silna spójność. Każda transakcja musi być zgodna z ACID, aby chronić integralność danych.
-- 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: systemy stworzone do analizy
Systemy OLAP są zoptymalizowane pod kątem złożonych zapytań, które skanują duże ilości danych historycznych, aby ujawniać trendy, wzorce i podsumowania. Analitycy biznesowi i analitycy danych używają OLAP do odpowiadania na pytania takie jak: „Jaka była łączna wartość naszej sprzedaży według regionów w ubiegłym kwartale?”
Zapytania OLAP często agregują miliony wierszy i obejmują wiele złączeń między tabelami faktów i wymiarów. Szybkość pojedynczych operacji zapisu ma mniejsze znaczenie; najważniejsze są przepustowość odczytu i elastyczność zapytań.
-- 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;Porównanie obu systemów
Najłatwiej zapamiętać tę różnicę, zastanawiając się, kto korzysta z każdego systemu i w jaki sposób:
- OLTP: używany przez backendy aplikacji; tysiące współbieżnych użytkowników; każde zapytanie dotyka kilku wierszy.
- OLAP: używany przez analityków i narzędzia raportowe; mniej współbieżnych zapytań, ale każde z nich skanuje miliony wierszy.
Te przeciwstawne wzorce dostępu prowadzą do znacznie różniących się projektów schematów, strategii indeksowania, a nawet wyboru sprzętu.
-- 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;Projektowanie schematu: znormalizowany a zdenormalizowany
Bazy danych OLTP preferują znormalizowane schematy (3NF lub wyższy), aby wyeliminować redundancję i usprawnić zapis. Każda encja znajduje się we własnej tabeli, co ogranicza ilość danych modyfikowanych w ramach jednej transakcji.
Bazy danych OLAP preferują zdenormalizowane schematy — zwłaszcza schematy gwiazdy i płatka śniegu — w których dane są wstępnie połączone i nadmiarowe. Eliminuje to kosztowne złączenia w czasie wykonywania zapytania i pozwala silnikom przechowywania kolumnowego szybciej skanować dane.
-- 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)
);Różne strategie indeksowania
Systemy OLTP w dużym stopniu polegają na indeksach B-tree dla kluczy głównych i obcych, aby umożliwić szybkie wyszukiwanie pojedynczych wierszy oraz wydajne złączenia w ramach transakcji.
Systemy OLAP korzystają z indeksów bitmapowych, przechowywania kolumnowego i partycjonowania. Skanowanie całej kolumny (np. wszystkich wartości sprzedaży) jest znacznie wydajniejsze, gdy dane są przechowywane kolumnami, a nie wierszami.
-- 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');Współbieżność i blokowanie
Systemy OLTP muszą obsługiwać tysiące jednoczesnych zapisów bez konfliktów. Bazy danych używają blokowania na poziomie wierszy oraz MVCC (Multi-Version Concurrency Control), dzięki czemu odczyty nigdy nie blokują zapisów i odwrotnie.
Zapytania OLAP są w przeważającej mierze przeznaczone tylko do odczytu. Blokowanie rzadko stanowi problem, ale długotrwałe skanowanie może zużywać znaczne ilości procesora i operacji wejścia-wyjścia. Większość hurtowni danych uruchamia OLAP w oddzielnym systemie zasilanym przez wsadowy ETL lub CDC (Change Data Capture) ze źródła 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: łączenie OLTP i OLAP
Ponieważ OLTP i OLAP mają niezgodne projekty, organizacje uruchamiają potoki ETL (Extract, Transform, Load), aby zgodnie z harmonogramem kopiować i przekształcać dane z bazy transakcyjnej do hurtowni analitycznej (co noc, co godzinę lub niemal w czasie rzeczywistym).
Proces ETL przekształca znormalizowane wiersze OLTP w zdenormalizowane rekordy faktów i wymiarów, stosując po drodze logikę biznesową (np. przeliczanie walut i segmentację klientów).
-- 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);Typowe wzorce zapytań OLAP
Zapytania OLAP niemal zawsze obejmują agregacje (SUM, COUNT, AVG), grupowanie według wielu wymiarów oraz filtrowanie według zakresów dat lub kategorii. Są to podstawowe elementy pulpitów nawigacyjnych i raportów biznesowych.
Funkcje okna są szczególnie przydatne w obciążeniach OLAP — pozwalają porównywać wartości dla każdego okresu z wartościami poprzedniego okresu bez złączenia tabeli z samą sobą.
-- 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: zacieranie granic
Nowoczesne systemy, takie jak TiDB, SingleStore i PostgreSQL + rozszerzenia kolumnowe, implementują HTAP (Hybrid Transactional/Analytical Processing). Ich celem jest obsługa obu rodzajów obciążeń w jednym silniku, co pozwala uniknąć złożoności operacyjnej związanej z utrzymywaniem oddzielnych systemów OLTP i OLAP.
HTAP osiąga ten cel, przechowując dane jednocześnie w dwóch formatach: wierszowym na potrzeby zapisów transakcyjnych oraz kolumnowym na potrzeby odczytów analitycznych, a oba formaty są automatycznie synchronizowane.
-- 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;Wybór odpowiedniego systemu
Wybór między OLTP, OLAP a HTAP zależy przede wszystkim od rodzaju obciążenia:
- Jeśli tworzą Państwo aplikację rejestrującą zdarzenia w czasie rzeczywistym — proszę użyć bazy danych OLTP (PostgreSQL, MySQL, SQL Server).
- Jeśli tworzą Państwo warstwę raportowania na podstawie danych historycznych — proszę użyć hurtowni OLAP (BigQuery, Redshift, Snowflake, ClickHouse).
- Jeśli potrzebują Państwo obu rodzajów obciążeń i zależy Państwu na prostocie operacyjnej — proszę rozważyć rozwiązania HTAP.
Wiele architektur produkcyjnych korzysta z obu rozwiązań: baza OLTP pełni funkcję systemu ewidencji, a oddzielna hurtownia danych służy do analiz; systemy te łączy potok 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;Sprawdzenie wiedzy
Proszę sprawdzić swoją wiedzę na temat najważniejszych różnic między systemami OLTP i OLAP.
Podsumowanie lekcji
OLTP a OLAP — najważniejsze informacje:
- OLTP obsługuje transakcje w czasie rzeczywistym: szybkie, współbieżne zapisy na poziomie wierszy z gwarancjami ACID.
- OLAP obsługuje obciążenia analityczne: złożone agregacje na dużych historycznych zbiorach danych z użyciem zdenormalizowanych schematów.
- Projekt schematu zależy od rodzaju obciążenia — schemat znormalizowany (3NF) stosuje się w OLTP, a schemat gwiazdy lub płatka śniegu w OLAP.
- Potoki ETL łączą oba systemy, ładując przekształcone dane OLTP do hurtowni analitycznej.
- Systemy HTAP próbują obsługiwać oba rodzaje obciążeń w jednym silniku, korzystając z podwójnego przechowywania wierszowego i kolumnowego.
Wybór właściwej architektury od samego początku pozwala uniknąć kłopotliwych migracji w przyszłości i zapewnia wykonywanie zapytań z szybkością oczekiwaną przez użytkowników.
Często zadawane pytania
Czy lekcja „OLTP a OLAP” jest bezpłatna?
Tak — pełny tekst „OLTP a OLAP” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.
Co nauczysz się w „OLTP a OLAP”?
Bazy transakcyjne a analityczne Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć SQL Academy?
Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 1 z 4.
Ile czasu zajmuje lekcja „OLTP a OLAP”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?
Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.