Google+ Followers

vrijdag 6 november 2015

Waarom ik PostgreSQL leuker vindt dan MySQL: Snel testdata genereren.

Stel je wilt een query testen en je hebt een tabel nodig met 40.000 records. In MySQL kun je dat het snelst doen door een tabel te maken met één record en vervolgens dat record te kopieren via INSERT INTO tabel SELECT * FROM tabel; wat twee records oplevert en als je het nog eens doet 4 dan 8,16 etc, tot je na 15 keer op 32.000 zit. Probleem: dan heb je 32.000 dezelfde records, dus je moet in je SELECT ook nog iets doen om de inhoud van de data te veranderen zodat je data een beetje zinnig is voor een performance test. In elk van de 15 iteraties moet je die data weer bijstellen en gedoe gedoe gedoe. Het komt dan ook veel voor dat programmeurs een scriptje schrijven dat 40.000 rijen aanmaakt, wat tijd kost om te schrijven en uit te voeren.

In PostgreSQL kan het allemaal een stuk eenvoudiger, zoals gewoonlijk, via GENERATE_SERIES().

De GENERATE_SERIES() functie doet wat de naam zegt; het genereert een serie. Dat kan een serie getallen zijn:

SELECT GENERATE_SERIES(5, 20, 1);

Genereert een serie van 5 tot en met 10  in stapjes van 1.


Het kan ook met datums:

SELECT GENERATE_SERIES('2010-01-01'::timestamptz, '2010-01-10'::timestamptz, interval '1 hour');

Dat genereert een lijst van datums beginnende op 2010-01-01 00:00:00 en eindigend op 2010-01-10 00:00:00, in stapjes van één uur.


Het aanmaken van 40.000 records is hiermee ineens kinderspel:

INSERT INTO tabel SELECT id  FROM GENERATE_SERIES(1,40000) AS id;

En als elk record een eigen datum moet krijgen dan kun je die ook laten berekenen via het id:

INSERT INTO tabel SELECT id, '2010-01-01'::TIMESTAMP + id * INTERVAL '1 hour'
FROM GENERATE_SERIES(1,40000) AS id;

Om het iets realistischer te maken kun je er met RANDOM() nog wat willekeur in brengen:
INSERT INTO tabel SELECT id, '2010-01-01'::TIMESTAMP + RANDOM() * INTERVAL '1 month'
FROM GENERATE_SERIES(1,40000) AS id;

Deze vult de 40.000 records met willekeurige datums die vallen tussen 2010-01-01 00:00:00 en 2010-02-01 00:00:00.


Willekeurige strings toevoegen gaat kan natuurlijk ook. Je kunt bijvoorbeeld volledig willekeurige strings opbouwen uit een characterset:

SELECT id
, '2010-01-01'::TIMESTAMP + RANDOM() * INTERVAL '1 month'
, (SELECT string_agg(x, '')
  FROM (
    SELECT substr('          abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', floor(random() * 46)::integer,1)
    FROM generate_series(1, (140*random())::int + id * 0)
  ) AS y(x)) as g
FROM GENERATE_SERIES(1,40000) AS id;

Dit ziet er ingewikkeld uit maar de substr() pakt gewoon 1 letter uit de lange string waar het hele alphabet in staat, met tien spaties om wat meer kans op een spate te geven. De 140*random() zorg voor een willekeurige lengte en de 'id*0' is een truukje om te zorgen dat de subquery niet wordt weg-geoptimaliseerd door de queryplanner.

Het uitvoeren van die laatste query duurt op mijn Intel -i7 laptop 1.8 seconden, dus snel genoeg om onderdeel te maken van een unittest.