Waarom leidt het toevoegen van deze vlag aan een samengestelde index soms tot prestatievermindering voor actieve-records queries in vergelijking met een partiële indexstrategie bij het implementeren van een soft-delete patroon met een boolean vlag, en hoe verschilt de selectiviteitsschatting van de queryplanner tussen deze benaderingen?

Antwoord op de vraag

Geschiedenis van de vraag

Soft-delete patronen zijn ontstaan als een alternatief voor harde verwijdering voor audittrails en gegevensherstel. Vroege implementaties gebruikten eenvoudige boolean vlaggen of tijdstempels, maar ontwikkelaars ondervonden al snel prestatievermindering toen deze vlaggen werden opgenomen in standaard B-tree indexen. Het probleem werd prominent met de wijdverspreide adoptie van PostgreSQL partiële indexen en SQL Server gefilterde indexen in het midden van de jaren 2000, die het mogelijk maakten alleen actieve records te indexeren. Het begrijpen van selectiviteitsschatting – hoe de queryplanner het percentage rijen voorspelt dat aan een voorwaarde voldoet – werd cruciaal bij het vergelijken van volledige samengestelde indexen versus partiële indexstrategieën.

Het probleem

Wanneer een soft-delete vlag (bijv. is_deleted) wordt toegevoegd aan een samengestelde index zoals (is_deleted, user_id, created_at), kan de database-optimalisator de rijselectiviteit verkeerd inschatten voor queries die filteren WHERE is_deleted = false. Als 90% van de rijen actief is, kan de optimalisator ervoor kiezen een sequentiële scan uit te voeren in plaats van een indexscan, of omgekeerd, als de distributie scheef is, kan het de index ongepast bevoordelen. Partiële indexen (WHERE is_deleted = false) slaan alleen actieve rijen op, waardoor een hoge selectiviteit wordt gegarandeerd, maar standaard samengestelde indexen slaan alle rijen op, wat leidt tot indexbloat en onduidelijke kardinaliteitsschattingen wanneer statistieken de soft-delete distributie niet nauwkeurig weergeven.

De oplossing

Implementeer partiële indexen (in PostgreSQL) of gefilterde indexen (in SQL Server) die soft-gewiste rijen volledig uitsluiten, gecombineerd met aparte indexen voor verwijderde gegevens indien nodig. Voor MySQL of databases zonder partiële indexering, gebruik een samengestelde index met de soft-delete vlag als de leidende kolom alleen als de actieve dataset klein is; anders, partitioneer de tabel op basis van de verwijderstatus. Analyseer expliciet tabelstatistieken na bulkverwijderingen om verouderde histogrammen te voorkomen. Gebruik bij het opvragen van actieve records de exacte voorwaarde van de definitie van de partiële index (WHERE is_deleted = false) om ervoor te zorgen dat de optimalisator de relevantie van de index herkent.

Codevoorbeeld

-- PostgreSQL: Partiële index voor alleen actieve records
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server: Gefilterde index equivalente
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- Query die de partiële index benut
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

Situatie uit het leven

Een SaaS-platform dat 10 miljoen gebruikersrecords beheert, ondervond ernstige vertragingen in hun admin-dashboard bij het filteren van actieve gebruikers op basis van de creatiedatum. Aanvankelijk gebruikten ze een samengestelde index (is_deleted, created_at) op PostgreSQL, in de veronderstelling dat dit WHERE is_deleted = false ORDER BY created_at queries zou versnellen. Echter, toen de dataset groeide tot 80% soft-gewiste historische rekeningen, begonnen de queries 8-12 seconden te duren omdat de planner de kosten van het scannen van de opgeblazen index onderschatte.

Oplossing A: Behoud de samengestelde index en forceer indexgebruik met hints. Deze benadering maakte gebruik van SET enable_seqscan = off of queryplan hints om indexgebruik af te dwingen. Hoewel het specifieke queries tijdelijk verbeterde, creëerde het onderhoudsschuld en dwong het vaak suboptimale plannen voor andere toegangspatronen wanneer de datadistributie verschoof. De oplossing loste ook het onderliggende indexbloat-probleem niet op, wat leidde tot verhoogde opslag- en VACUUM overhead.

Oplossing B: Maak aparte partiële indexen voor actieve en verwijderde records. Het implementeren van CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false verminderde de indexgrootte met 80% en stelde de planner in staat om nauwkeurig 2 miljoen actieve rijen tegenover 8 miljoen verwijderde te schatten. Queries daalden naar 40 ms, maar vereisten refactoring van alle applicatiequeries om ervoor te zorgen dat de is_deleted = false voorwaarde expliciet bleef en niet verpakt werd in functies of abstracties achter views die de voorwaarde verduisterden.

Het team koos voor Oplossing B omdat deze duurzame prestatiewinsten bood zonder het onderhoud van query-hints. Het resultaat was een reductie van 95% in query-latentie en de eliminatie van periodieke VACUUM bloatproblemen veroorzaakt door de eerder te grote samengestelde index. Monitoring bevestigde consistente responstijden van minder dan een seconde voor de belangrijkste gebruiksscenario's van het dashboard.

Wat kandidaten vaak missen

Hoe beïnvloedt de aanwezigheid van NULL-waarden in een soft-delete tijdstempelkolom (met NULL voor actief, tijdstempel voor verwijderd) het gebruik van partiële indexen versus benaderingen met een boolean vlag?

Bij het gebruik van een nullable deleted_at tijdstempel hebben partiële indexen zoals WHERE deleted_at IS NULL problemen met de behandeling van NULL-indexeerbaarheid door PostgreSQL. In tegenstelling tot boolean vlaggen waar = false expliciet en sargable is, vereisen IS NULL voorwaarden dat de planner de toepasselijkheid van de index herkent, wat kan falen als de query parameterized statements gebruikt waarbij de planner niet kan bewijzen dat de parameter NULL zal zijn. Bovendien veroorzaken updates waarbij deleted_at = CURRENT_TIMESTAMP indexbloat in de partiële index voor actieve records wanneer rijen worden verwijderd, terwijl updates van boolean vlaggen de bit omdraaien maar binnen een volledige samengestelde index blijven. De nullable benadering vereist frequentere ANALYZE oproepen en zorgvuldige overweging van de indexfill-factoren om de hoge omloop van wijzigingen in de verwijderstatus aan te pakken.

Waarom kan een dekkende index die soft-delete kolommen bevat, langzamere schrijfbewerkingen dan verwacht veroorzaken, zelfs bij een lage verwijderfrequentie?

Dekkende indexen (die de INCLUDE-clausule in PostgreSQL 11+ of SQL Server gebruiken) die is_deleted toevoegen om table-lookups te vermijden, verslechteren daadwerkelijk de schrijfprestaties omdat elke soft-delete handeling (een UPDATE) meerdere indexstructuren moet aanpassen. Wanneer een gebruiker soft-gewist wordt, moet de database de oude indexvermelding als dood markeren in de actieve partiële index, een nieuwe vermelding invoegen in eventuele indexen voor verwijderde records, en de heap pointers van de dekkende index bijwerken. Kandidaten missen vaak dat partiële indexen deze omloop isoleren – alleen de specifieke partiële indexen voor actieve of verwijderde staten worden aangepast – terwijl dekkende indexen op de hoofdtafel de primaire indexstructuur moeten bijwerken, ongeacht de soft-delete status, wat schrijfversterking creëert die de transactiedoorvoer beïnvloedt.

Wanneer negeert de query-optimalisator een partiële index voor soft-gewiste gegevens, zelfs als de query expliciet filtert voor verwijderde records?

Als de partiële index is gedefinieerd als WHERE is_deleted = true voor auditqueries, maar de applicatie een voorbereide verklaring gebruikt met een parameter $1 voor zowel actieve als verwijderde queries, kan PostgreSQL een generiek plan cachen dat de partiële index voor de specifieke true staat niet herkent. Dit gebeurt omdat voorbereide statements plannen genereren voordat parameterwaarden zijn gebonden, en de optimalisator kan niet bewijzen dat $1 = true altijd aan de indexpredicate zal voldoen. Kandidaten missen dat dynamische SQL of recompilatie-hints (OPTION (RECOMPILE) in SQL Server, uitvoeren met letterlijke waarden in PostgreSQL) vereist zijn om ervoor te zorgen dat de planner de concrete waarde ziet en deze matcht met de predicate van de partiële index, in plaats van te vertrouwen op generieke plannen die standaard op sequentiële scans terugvallen vanwege de onzekerheid van de parameterwaarde.