PostgreSQL TOAST Stratégia: Prečo Váš JSON Stĺpec Zabíja Výkon Queries

TOAST bol pre mna neviditelny, kym storage nevybuchol. “Naša API odpoveď je pomalá aj keď query trvá 5ms.” Potom si všimnete: tabuľka má JSONB stĺpec s 50KB dokumentmi. TOAST fetchuje zo separátneho storage pre každý riadok.

TOAST (The Oversized-Attribute Storage Technique) ukladá veľké hodnoty separátne. Pochopenie toho môže 10x zrýchliť vaše queries.

Testované na: PostgreSQL 16.1, tabuľky so 100K+ riadkami, JSONB stĺpce 10-100KB

Ako TOAST Funguje

Storage Threshold

Predvolená veľkosť stránky: 8KB
TOAST threshold: ~2KB per hodnota stĺpca

Ak hodnota > 2KB:
1. Skomprimuj hodnotu (ak komprimovateľná)
2. Ak stále > 2KB: ulož do separátnej TOAST tabuľky
3. Hlavná tabuľka uloží pointer na TOAST dáta

Štruktúra TOAST Tabuľky

-- Hlavná tabuľka
CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    customer_id int,
    order_data jsonb  -- Veľký JSON uložený v TOAST
);

-- PostgreSQL automaticky vytvorí:
-- pg_toast.pg_toast_<oid> - TOAST data tabuľka
-- pg_toast.pg_toast_<oid>_index - TOAST index

-- Skontroluj TOAST tabuľku
SELECT relname, reltoastrelid::regclass
FROM pg_class
WHERE relname = 'orders';

TOAST Stratégie

-- EXTENDED (default pre varlena typy)
-- Povoľuje kompresiu aj out-of-line storage
ALTER TABLE orders ALTER COLUMN order_data SET STORAGE EXTENDED;

-- EXTERNAL
-- Žiadna kompresia, out-of-line storage
-- Rýchlejšie pre pred-komprimované dáta (už komprimovaný JSON)
ALTER TABLE orders ALTER COLUMN order_data SET STORAGE EXTERNAL;

-- MAIN
-- Kompresia, vyhni sa out-of-line ak možné
-- Dobré pre dáta ktoré vždy potrebuješ
ALTER TABLE orders ALTER COLUMN order_data SET STORAGE MAIN;

-- PLAIN
-- Žiadna kompresia, žiadne out-of-line (zlyhá ak príliš veľké)
-- Pre dáta ktoré musia zmestiť do main tuple
ALTER TABLE orders ALTER COLUMN small_data SET STORAGE PLAIN;

Problém s Výkonom

SELECT * s TOAST

-- Každý riadok fetchuje z TOAST tabuľky
SELECT * FROM orders WHERE customer_id = 123;

-- Za scénou:
-- 1. Nájdi riadok v hlavnej tabuľke: 0.1ms
-- 2. Fetch TOAST dáta pre order_data: 2-10ms per riadok!
-- 3. Dekomprimuj ak potrebné: 0.5ms

-- 100 riadkov × 5ms = 500ms len pre TOAST fetche!

Benchmark: TOAST Dopad

-- Vytvor testovaciu tabuľku
CREATE TABLE orders_test (
    id serial PRIMARY KEY,
    customer_id int,
    status text,
    order_data jsonb  -- 50KB priemerne
);

-- Vlož 100K riadkov s veľkým JSON
INSERT INTO orders_test (customer_id, status, order_data)
SELECT
    random() * 1000,
    'active',
    jsonb_build_object(
        'items', (SELECT jsonb_agg(jsonb_build_object(
            'product_id', i,
            'name', repeat('x', 100),
            'price', random() * 100
        )) FROM generate_series(1, 100) i)
    )
FROM generate_series(1, 100000);

-- Query s TOAST fetch
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders_test WHERE customer_id = 42;
-- Execution Time: 850ms
-- Buffers: shared hit=12000 (veľa TOAST reads)

-- Query bez TOAST stĺpca
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, status FROM orders_test WHERE customer_id = 42;
-- Execution Time: 8ms
-- Buffers: shared hit=120

Riešenia

1. Nepoužívaj SELECT *

-- ZLE: Fetchuje všetky TOAST dáta
SELECT * FROM orders WHERE customer_id = 123;

-- DOBRE: Fetchuj len čo potrebuješ
SELECT id, customer_id, status, created_at
FROM orders WHERE customer_id = 123;

-- Fetch veľký stĺpec len keď potrebný
SELECT order_data FROM orders WHERE id = 456;

2. Použi EXTERNAL pre Pred-komprimované Dáta

-- Ak ukladáš už komprimované dáta (gzipped JSON, obrázky)
ALTER TABLE documents
ALTER COLUMN compressed_data SET STORAGE EXTERNAL;

-- Preskočí PostgreSQL kompresiu (rýchlejší insert/read)
-- Stále ukladá out-of-line ak veľké

3. Zváž Separátnu Tabuľku

-- Namiesto jednej širokej tabuľky
CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    customer_id int,
    status text,
    order_data jsonb  -- Veľké!
);

-- Rozdeľ na dve tabuľky
CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    customer_id int,
    status text
);

CREATE TABLE order_details (
    order_id bigint PRIMARY KEY REFERENCES orders(id),
    order_data jsonb
);

-- Bežné queries nedotýkajú veľké dáta
SELECT id, customer_id, status FROM orders WHERE customer_id = 123;

-- Veľké dáta len keď potrebné
SELECT o.*, od.order_data
FROM orders o
JOIN order_details od ON od.order_id = o.id
WHERE o.id = 456;

4. Použi JSONB Partial Indexing

-- Indexuj špecifické JSON cesty
CREATE INDEX idx_orders_status ON orders ((order_data->>'status'));

-- Query používa index, nepotrebuje plný TOAST fetch
SELECT id FROM orders WHERE order_data->>'status' = 'shipped';

5. Normalizuj Hot Fields

-- Namiesto queryovania JSON pre bežné filtre
SELECT * FROM orders WHERE order_data->>'status' = 'active';

-- Pridaj regular stĺpec pre hot fields
ALTER TABLE orders ADD COLUMN status text;
UPDATE orders SET status = order_data->>'status';
CREATE INDEX idx_orders_status ON orders(status);

-- Teraz queries sú rýchle
SELECT id, status FROM orders WHERE status = 'active';

Monitoring TOAST

Skontroluj Veľkosť TOAST

-- Veľkosť TOAST tabuľky per tabuľka
SELECT
    c.relname AS table_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
    pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
WHERE c.relkind = 'r'
AND c.reltoastrelid != 0
ORDER BY pg_relation_size(c.reltoastrelid) DESC
LIMIT 10;

TOAST Štatistiky

-- TOAST access štatistiky
SELECT
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_tup_ins,
    n_tup_upd
FROM pg_stat_all_tables
WHERE relname LIKE 'pg_toast_%'
ORDER BY seq_tup_read DESC
LIMIT 10;

Storage Stratégia Stĺpca

-- Skontroluj aktuálnu storage stratégiu
SELECT
    a.attname,
    t.typname,
    CASE a.attstorage
        WHEN 'p' THEN 'PLAIN'
        WHEN 'e' THEN 'EXTERNAL'
        WHEN 'm' THEN 'MAIN'
        WHEN 'x' THEN 'EXTENDED'
    END AS storage
FROM pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
WHERE a.attrelid = 'orders'::regclass
AND a.attnum > 0;

TOAST a Indexy

Index-Only Scans Blokované

-- Toto nepoužije index-only scan ak order_data je TOAST'd
CREATE INDEX idx_orders_customer ON orders(customer_id) INCLUDE (order_data);

-- PostgreSQL nemôže vrátiť TOAST'd dáta len z indexu
-- Musí navštíviť heap pre detoasting

Covering Index pre Malé Stĺpce

-- Funguje pre non-TOAST stĺpce
CREATE INDEX idx_orders_customer_covering
ON orders(customer_id)
INCLUDE (status, created_at);  -- Len malé stĺpce

-- Môže použiť index-only scan
EXPLAIN SELECT status, created_at FROM orders WHERE customer_id = 123;
-- -> Index Only Scan

TOAST Kompresia

Skontroluj Kompresný Pomer

-- Skutočná vs uložená veľkosť
SELECT
    id,
    pg_column_size(order_data) AS stored_size,
    length(order_data::text) AS raw_size,
    round(100.0 * pg_column_size(order_data) / length(order_data::text), 1) AS compression_pct
FROM orders
LIMIT 10;

Kedy Vypnúť Kompresiu

-- Už komprimované dáta (obrázky, pred-komprimovaný JSON)
ALTER TABLE media ALTER COLUMN image_data SET STORAGE EXTERNAL;

-- Náhodné/šifrované dáta (aj tak sa nekomprimujú)
ALTER TABLE secrets ALTER COLUMN encrypted_data SET STORAGE EXTERNAL;

Checklist

## TOAST Optimalizácia

### Analýza
- [ ] Identifikuj tabuľky s veľkými TOAST veľkosťami
- [ ] Nájdi queries robiace SELECT * na TOAST tabuľkách
- [ ] Skontroluj frekvenciu TOAST prístupov v pg_stat_all_tables

### Query Optimalizácia
- [ ] Nahraď SELECT * špecifickými stĺpcami
- [ ] Fetchuj veľké stĺpce len keď potrebné
- [ ] Pridaj regular stĺpce pre hot JSON fields

### Storage Stratégia
- [ ] EXTENDED: komprimovateľné dáta (default)
- [ ] EXTERNAL: pred-komprimované dáta
- [ ] MAIN: vždy-potrebné medium dáta
- [ ] Zváž rozdelenie tabuľky pre veľmi veľké stĺpce

### Monitoring
- [ ] Sleduj veľkosti TOAST tabuliek
- [ ] Monitoruj TOAST sequential scans
- [ ] Alert na nadmerný rast TOAST

Záver

TOAST je skrytý kým nezabije tvoj výkon:

1. **Nikdy SELECT *** na tabuľkách s veľkými stĺpcami
2. Použi EXTERNAL pre pred-komprimované dáta
3. Rozdeľ tabuľky keď veľké stĺpce sú zriedka potrebné
4. Normalizuj hot fields z JSON

Tvoj 50KB JSON stĺpec v listing query je tvoj problém.

Súvisiace články

• PostgreSQL HOT Updates - Storage optimalizácia
• UUIDv4 vs ULID vs TSID - Index efektivita