Nieuwsbrief februari 2022

Omgaan met historische data, het is een onderwerp dat vroeger of later aan de orde komt. Hopelijk vind je het interessant iets te lezen over de uitdagingen die daarbij optreden en over twee strategieën voor twee klassen van tabellen.

Wil je mijn nieuwsbrief niet ontvangen, laat het me weten.

Met hartelijke groet,

Paul van Oordt

Omgaan met historische data

Vragen die regelmatig terugkomen in mijn praktijk: Wanneer is het tijd historische data uit de database te verwijderen? En: Hoe kan dat efficiënt worden gedaan?

Voor tabellen met tientallen miljoenen rijen kan het verwijderen van een aanzienlijke deel van de data, al of niet als onderdeel van een verplaatsing, een lastige aangelegenheid zijn. Niet alleen zijn de delete-statements duur en vaak blocking, ook kunnen er issues ontstaan met foreign keys die naar de data verwijzen. En wanneer de gearchiveerde data geraadpleegd moet worden, werken de bestaande applicaties en code niet meer. De data bevindt zich immers in een andere database.

Database-grootte kan een probleem worden, bijvoorbeeld door backups die steeds langer duren. Omdat het verwijderen van oude data dus vaak lastig en duur is, is het logisch eerst te zoeken naar makkelijker manieren om de database te verkleinen. Bijvoorbeeld het verwijderen van redundante of ongebruikte indexes of het reclaimen van vrije ruimte die onstaan is door deletes of updates. Ook is het goed om op de hoogte te zijn van de manieren om backups te versnellen, dan wel te verkleinen, zoals het gebruik van differential of filegroup backups, striping en backup-compressie. En uiteraard is ook compressie van de data zelf iets om te onderzoeken.

Ook query-performance kan een reden zijn om tabellen te willen verkeinen, al staat historische data die in queries niet wordt aangeraakt in principe niet in de weg. Wel is het zo dat de indexering echt goed moet zijn. Hoe groter een tabel, hoe groter uiteraard het effect van een 'foute' query die een volledige table- of index scan doet.

Hieronder beschrijf ik twee strategieën om bestaande tabellen te verkleinen, bruikbaar voor twee verschillende klassen van tabellen.

'Primaire' tabellen

Onder 'primaire' tabellen versta ik hier tabellen die verweven zijn in het netwerk van foreign keys en die voor allerlei business events op grote schaal gelezen en gemodificeerd worden, en daarom meestal ook een aanzienlijk aantal indexes bevatten. Voorbeelden van dit soort tabellen zijn 'klant', 'product', 'order', 'factuur' etc.

De aanpak om deze tabellen te verkleinen begint ermee data te markeren als historisch. Dat kan bijvoorbeeld met een not-nullable bit kolom 'archived'. Vooruitlopend op de eventuele verwijdering van die data, worden queries herschreven zodat ze per default alleen niet-gearchiveerde data doorzoeken. Dat kan door systematisch de conditie archived = 0 toe te voegen. Het wijzigen van de queries is vrij eenvoudig wanneer stored procedures gebruikt zijn. Eventueel kan ook een view met dit filter worden gebruikt. Het filter moet wel de harde waarde "0" bevatten, en dus geen parameter als in archived = @archived. Bestaande indexes kunnen dan worden gefiltered op archived = 0 en worden daarmee veel kleiner. (Al wordt er gefilterd op archived, een index met die kolom als key heeft geen enkele zin, aangezien deze totaal niet selectief is.) Je hebt nu queries die minder data aanraken en dus sneller zijn, en bovendien veel kleinere indexes. Je kunt nu zelfs aan deze kleinere indexes makkelijk nog included kolommen toevoegen om queries nog veel verder te versnellen. Als bonus zijn de gemarkeerde regels ook nog sneller te verwijderen, indien dat alsnog nodig wordt geacht. Immers er behoeven voor die regels minder indexes te worden bijgewerkt.

Logdata

De andere categorie tabellen noem ik gemakshalve 'logdata'. Het gaat om tabellen waar over het algemeen geen foreign keys naar wijzen. De data wordt meestal ook maar weinig ge-queried en er zijn geen of maar een paar indexes. Verder kan de oude data vaak echt weg, en hoeft niet verplaatst te worden.

Voor deze data bestaat alleen het probleem van de dure en blokkerende delete-statements. In dit scenario is table partitioning ideaal. In een tabel waarin de data en de indexes gepartitioneerd zijn, kan bijvoorbeeld een maand aan data instantaan, als metadata-operatie worden verwijderd. Het is aanbevolen partitionering bij de aanmaak van de tabel al te configureren, en niet te wachten tot het probleem zich voordoet. Het is namelijk lastig (duur en blocking) een bestaande grote tabel achteraf te partitioneren. In de praktijk is die vooruitziende blik er niet altijd. Gelukkig zijn er dan nog oplossingen denkbaar waarbij geleidelijk een gepartitioneerde versie van de tabel wordt ingefaseerd, terwijl de oude ongepartitioneerde versie wordt uitgefaseerd.

Het omgaan met historische data, iets om verder te onderzoeken? Ik sta je graag bij.

Nu ook vanuit Antwerpen

Ik werk graag op locatie bij de klant, met een paar mensen in een zaaltje met een groot scherm, of samen achter een laptop. Omdat mijn opdrachten meestal maar één of enkele dagen duren, doe ik niet moeilijk over reistijden. Toch goed om te weten dat ik sinds een paar maanden regelmatig in Antwerpen verblijf, en ook vanuit daar graag naar je toe kom.

Dank voor het lezen.

februari 2022; de nieuwsbrief verschijnt enkele keren per jaar; aanmelden / afmelden