Soft Delete past: Prečo is_deleted zabíja tvoju databázu (a čo s tým)

Soft delete vyzera nevinne, kym sa ti neroztopia indexy. “Nikdy nemažeme dáta, len nastavíme deleted_at.” Toto som počul pred 8 rokmi a zdalo sa mi to ako rozumný prístup. O 3 roky neskôr som strávil týždeň ladením databázy, ktorá mala 60% “zmazaných” riadkov a queries trvali 10x dlhšie než na začiatku.

Soft delete je najčastejší architektonický anti-pattern, ktorý všetci akceptujeme bez premýšľania. V tomto článku ukážem prečo, a čo s tým.

Testované na: PostgreSQL 14-16, tabuľky od 1M do 50M riadkov. Všetky benchmarky som spúšťal na reálnych dátach.

Prečo soft delete vyzerá ako dobrý nápad

Na začiatku projektu má soft delete samé výhody:

• Audit trail - vidíš čo bolo zmazané a kedy
• Undo funkcionalita - používateľ si rozmyslel? Obnovíš jedným UPDATE
• Referenčná integrita - FK sa nerozbijú
• Právne požiadavky - GDPR retenčné obdobie

-- Zdanlivo nevinný stĺpec
ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMP NULL;

-- "Mazanie" = update
UPDATE orders SET deleted_at = NOW() WHERE id = 123;

-- Select = pridaj WHERE
SELECT * FROM orders WHERE deleted_at IS NULL;

Kde to začne bolieť

Problém 1: Indexy obsahujú mŕtve dáta

Každý index na tabuľke obsahuje AJ zmazané riadky. Ak máš 10M riadkov a 6M je “zmazaných”:

-- Tento index má 10M entries, nie 4M
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Query musí preskenovať celý index a potom filtrovať
SELECT * FROM orders
WHERE user_id = 123 AND deleted_at IS NULL;

Riešenie? Partial index:

CREATE INDEX idx_orders_user_id_active ON orders(user_id)
WHERE deleted_at IS NULL;

Ale teraz musíš mať KAŽDÝ index ako partial. A čo keď zabudneš?

Problém 2: Unique constraints nefungujú

-- Používateľ má unikátny email
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

-- Zmažeš používateľa
UPDATE users SET deleted_at = NOW() WHERE email = '[email protected]';

-- Nový používateľ chce ten istý email - FAIL!
INSERT INTO users (email) VALUES ('[email protected]');
-- ERROR: duplicate key value violates unique constraint

Riešenie? Partial unique index:

CREATE UNIQUE INDEX users_email_unique_active ON users(email)
WHERE deleted_at IS NULL;

Ale pozor - teraz môžeš mať 10 zmazaných používateľov s tým istým emailom. Je to OK?

Problém 3: Foreign keys sú komplikované

-- Order odkazuje na user
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Čo keď zmažeš usera?
UPDATE users SET deleted_at = NOW() WHERE id = 123;

-- Orders stále odkazujú na "zmazaného" usera
-- Je to bug alebo feature?

Problém 4: Query planner sa trápi

PostgreSQL query planner odhaduje selektivitu na základe štatistík. Ak máš 60% zmazaných riadkov:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND deleted_at IS NULL;

Planner môže:

• Podhodnotiť počet riadkov (ak štatistiky nie sú aktuálne)
• Zvoliť zlý join type
• Ignorovať partial index

Benchmark: Soft delete vs Partitioning

Vytvoril som test s 10M riadkami, 50% označených ako deleted:

Setup

-- Tabuľka so soft delete
CREATE TABLE orders_soft (
    id SERIAL PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    created_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- Partitioned tabuľka
CREATE TABLE orders_partitioned (
    id SERIAL,
    user_id INT,
    amount DECIMAL(10,2),
    created_at TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE
) PARTITION BY LIST (is_deleted);

CREATE TABLE orders_active PARTITION OF orders_partitioned
    FOR VALUES IN (FALSE);
CREATE TABLE orders_deleted PARTITION OF orders_partitioned
    FOR VALUES IN (TRUE);

Výsledky

Riešenie: Partitioning alebo Archive tabuľka

Možnosť 1: Partitioning (PostgreSQL 10+)

-- Nová partitioned tabuľka
CREATE TABLE orders_new (
    id SERIAL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    is_archived BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (id, is_archived)
) PARTITION BY LIST (is_archived);

-- Aktívna partícia - tu sú všetky indexy
CREATE TABLE orders_active PARTITION OF orders_new
    FOR VALUES IN (FALSE);

-- Archívna partícia - minimálne indexy
CREATE TABLE orders_archived PARTITION OF orders_new
    FOR VALUES IN (TRUE);

-- Indexy len na aktívnej partícii
CREATE INDEX idx_orders_active_user ON orders_active(user_id);
CREATE INDEX idx_orders_active_created ON orders_active(created_at);

Možnosť 2: Separate Archive tabuľka

-- Hlavná tabuľka - len aktívne záznamy
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Archívna tabuľka - rovnaká štruktúra + metadata
CREATE TABLE orders_archive (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT NOW(),
    archived_by VARCHAR(100)
);

-- Mazanie = presun
CREATE OR REPLACE FUNCTION archive_order(order_id INT, archived_by VARCHAR)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders_archive (id, user_id, amount, created_at, archived_by)
    SELECT id, user_id, amount, created_at, archived_by
    FROM orders WHERE id = order_id;

    DELETE FROM orders WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;

Migrácia z is_deleted bez downtime

Fáza 1: Pridaj novú štruktúru

-- Vytvor archive tabuľku
CREATE TABLE orders_archive (LIKE orders INCLUDING ALL);
ALTER TABLE orders_archive ADD COLUMN archived_at TIMESTAMP DEFAULT NOW();

-- Trigger pre nové "delety"
CREATE OR REPLACE FUNCTION archive_on_soft_delete()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
        INSERT INTO orders_archive
        SELECT *, NOW() FROM orders WHERE id = NEW.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_archive_on_delete
    AFTER UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION archive_on_soft_delete();

Fáza 2: Presun existujúcich (batched)

BATCH_SIZE = 10000

def migrate_deleted_orders():
    while True:
        with transaction():
            # Nájdi batch na migráciu
            result = db.execute("""
                WITH to_archive AS (
                    SELECT id FROM orders
                    WHERE deleted_at IS NOT NULL
                    LIMIT %s
                    FOR UPDATE SKIP LOCKED
                )
                INSERT INTO orders_archive
                SELECT o.*, NOW()
                FROM orders o
                JOIN to_archive t ON o.id = t.id
                RETURNING id
            """, [BATCH_SIZE])

            if result.rowcount == 0:
                break

            # Zmaž z hlavnej tabuľky
            db.execute("""
                DELETE FROM orders
                WHERE id IN (SELECT id FROM to_archive)
            """)

        time.sleep(0.1)  # Daj DB oddýchnuť

Fáza 3: Uprac a odstráň stĺpec

-- Keď sú všetky zmazané riadky presunuté
ALTER TABLE orders DROP COLUMN deleted_at;

-- Drop trigger
DROP TRIGGER tr_archive_on_delete ON orders;
DROP FUNCTION archive_on_soft_delete();

Kedy je soft delete OK

Soft delete nie je vždy zlý. Je OK keď:

1. Málo zmazaných - menej než 10% riadkov
2. Krátka retenčná doba - mažeš natvrdo po 30 dňoch
3. Malé tabuľky - tisíce, nie milióny riadkov
4. Potrebuješ undo - ale len krátkodobo

Checklist pred použitím soft delete

## Soft Delete Checklist

### Pred implementáciou
- [ ] Aký je očakávaný pomer zmazaných vs aktívnych riadkov?
- [ ] Ako dlho budú zmazané riadky existovať?
- [ ] Potrebujem UNIQUE constraints na tabuľke?
- [ ] Mám foreign keys z iných tabuliek?

### Ak áno k soft delete
- [ ] Všetky indexy sú PARTIAL (WHERE deleted_at IS NULL)
- [ ] UNIQUE constraints sú tiež partial
- [ ] Mám background job na hard delete starých záznamov
- [ ] Monitoring veľkosti "mŕtvych" dát

### Alternatívy
- [ ] Zvážil som partitioning?
- [ ] Zvážil som separate archive tabuľku?
- [ ] Zvážil som event sourcing pre audit trail?

Záver

Soft delete je ako technický dlh, ktorý platíš každý deň. Na začiatku je lacný, ale úroky rastú exponenciálne s veľkosťou tabuľky.

Moje odporúčanie: Pre nové projekty používaj partitioning alebo archive tabuľku od začiatku. Pre existujúce projekty naplánuj migráciu skôr než neskôr - čím viac dát, tým bolestivejšie.

FAQ

Čo ak potrebujem audit trail?

Použi event sourcing alebo separate audit log tabuľku. Audit a aktívne dáta nemusia byť v tej istej tabuľke.

Nepomôže len VACUUM?

VACUUM uvoľní miesto, ale nezmení fakt, že indexy obsahujú aj zmazané riadky. Partial indexy sú jediné riešenie.

Čo s ORM ktorý automaticky pridáva deleted_at?

Väčšina ORM (Django, Rails, Hibernate) podporuje aj custom delete stratégie. Skontroluj dokumentáciu pre “archive” alebo “hard delete” módy.

Súvisiace články

• Zero-downtime migrácie PostgreSQL - Ako bezpečne migrovať veľké tabuľky
• CI/CD pre monorepo - Automatizácia databázových migrácií