Under udviklingen af QuestGame har jeg arbejdet med modelleringen af vores PostgreSQL-database gennem Entity Framework Core. En central del af arbejdet har været at definere domæneentiteter, deres relationer og hvordan disse omsættes til databasetabeller.
I den forbindelse begyndte jeg at undersøge, hvordan databasen håndterer opslag, og hvilken betydning indeksering har for systemets ydeevne.
Et databaseindeks kan sammenlignes med stikordsregisteret i en bog. I stedet for at gennemgå samtlige sider kan man hurtigt finde frem til det relevante indhold. Tilsvarende kan et indeks hjælpe databasen med at finde bestemte rækker uden nødvendigvis at gennemlæse hele tabellen.
Men indekser er ikke gratis. De optager lagerplads og skal vedligeholdes, når data indsættes, opdateres eller slettes.
Derfor opstod spørgsmålet: Hvilke indeks opretter EF Core allerede for os, hvilke typer findes der, og hvornår giver det mening selv at tilføje flere?
Undersøgelse af vores eksisterende database
Jeg begyndte med at gennemgå vores AppDbContext og de migrations, som EF Core har genereret.
Det viste sig, at en betydelig del af vores indeksering allerede håndteres automatisk.
EF Core identificerer blandt andet fremmednøgler gennem domænemodellen og vores relationskonfiguration. Som udgangspunkt opretter frameworket indeks på disse fremmednøgler.
Et konkret eksempel er relationen mellem Quest og QuestItem.
Hvert QuestItem tilhører en bestemt Quest gennem QuestId. EF Core har derfor genereret indekset IX_QuestItems_QuestId, som understøtter opslag efter de QuestItems, der tilhører en bestemt Quest.
Tilsvarende har vi indeks på eksempelvis Quests.CreatedByUserId og QuestSessionItems.QuestSessionId.
Primærnøglerne er også indekseret. Her er det PostgreSQL, som automatisk opretter et unikt indeks, når en primærnøgle defineres.
Derudover har vi selv konfigureret enkelte indeks gennem Fluent API.
Et eksempel er QuestSessions.JoinCode, hvor vi anvender HasIndex(...).IsUnique().
Dette indeks understøtter hurtige opslag, når en spiller tilslutter sig en session med en spilkode. Samtidig sikrer den unikke begrænsning, at to sessions ikke kan have samme kode.
Et andet eksempel findes i QuestSessionStudents, hvor kombinationen af QuestSessionId og StudentId har et unikt, sammensat indeks. Det forhindrer, at den samme elev registreres flere gange i samme session.
Dermed er indeksering ikke udelukkende et spørgsmål om performance, men også om dataintegritet.
Er flere indeks nødvendigvis bedre?
Undersøgelsen viste, at vores datamodel allerede giver anledning til en række relevante indeks.
Det betyder dog ikke, at alle forespørgsler automatisk er optimerede.
EF Core kan identificere relationer, men frameworket kan ikke nødvendigvis forudsige alle de søgemønstre, som applikationen senere får brug for.
Samtidig afhænger nytten af et indeks af, hvordan databasen faktisk anvendes.
Hvis en tabel kun indeholder ganske få rækker, kan en almindelig gennemlæsning være hurtigere end at anvende et indeks. PostgreSQLs query planner vurderer derfor selv, hvilken fremgangsmåde der forventes at være mest effektiv.
Flere indeks betyder heller ikke nødvendigvis bedre performance. Hvert ekstra indeks øger omkostningerne ved skriveoperationer.
Det fik mig til at betragte indeksering som en afvejning mellem læsehastighed, skriveomkostninger og systemets konkrete behov frem for noget, man ukritisk bør tilføje til alle kolonner.
Forskellige indekstyper og deres anvendelse
PostgreSQL understøtter flere indekstyper, som er udviklet til forskellige former for forespørgsler.
- B-tree er standardtypen og den mest relevante i størstedelen af vores nuværende datamodel. Den fungerer godt til lighedssøgninger, intervalsøgninger og sortering. Det er eksempelvis relevant ved opslag på ID’er, fremmednøgler og spilkoder.
- Hash-indeks er særligt beregnet til lighedssøgninger, men understøtter ikke intervalsøgninger. I vores tilfælde er B-tree generelt mere alsidig.
- GIN-indeks er velegnede til data med flere søgbare elementer, eksempelvis arrays, JSONB og fuldtekstsøgning.
- GiST-indeks understøtter mere specialiserede søgninger, herunder geografiske og rumlige forespørgsler gennem PostGIS.
- SP-GiST understøtter ligeledes specialiserede søgestrukturer, eksempelvis partitionering af rumlige data.
- Endelig findes BRIN-indeks, som kan være særligt effektive på meget store tabeller, hvor værdierne følger en bestemt fysisk rækkefølge, eksempelvis tidsstemplede data.
For QuestGame er især B-tree og GiST interessante.
Vi anvender allerede et eksplicit GiST-indeks på Locations.Coordinates, som er konfigureret gennem HasIndex(...).HasMethod("gist").
Dette forbereder databasen på geografiske søgninger, eksempelvis hvis vi senere ønsker en funktion, hvor spillere kan finde offentlige Quests i nærheden.
Vi har dog fravalgt at oprette et tilsvarende GiST-indeks på QuestSessionLocations.Coordinates.
Årsagen findes i vores nuværende valideringslogik.
Når en spiller forsøger at åbne et QuestItem, kender backend allerede det konkrete QuestSessionItemId. Systemet finder derfor først det pågældende item og beregner derefter afstanden mellem spillerens GPS-position og den tilknyttede lokation gennem PostGIS.
Vi søger altså ikke blandt tusindvis af geografiske positioner efter dem, som ligger inden for en bestemt radius.
Derfor er et rumligt indeks på sessionens koordinater ikke nødvendigt for dette opslag.
Fra teori til fremtidige beslutninger
Undersøgelsen har givet mig en bedre forståelse af, hvorfor indeksering bør tage udgangspunkt i applikationens faktiske forespørgsler.
Vores eksisterende B-tree-indeks understøtter allerede mange af de operationer, som MVP’en skal udføre.
Samtidig har vi med GiST-indekset på Locations forberedt databasen på funktionalitet, som endnu ikke indgår i den nuværende implementering.
Hvis vi senere udvider QuestGame med geografisk søgning, bliver det relevant at undersøge, hvordan indekset påvirker forespørgslernes ydeevne.
Her kan PostgreSQLs EXPLAIN ANALYZE anvendes til at undersøge eksekveringsplaner og måle, om et indeks faktisk giver en forbedring.
Det vil gøre det muligt at træffe beslutninger på baggrund af konkrete målinger frem for antagelser.
Konklusion
Arbejdet har vist mig, at indeksering er tæt forbundet med både datamodellering, applikationslogik og databaseperformance.
EF Core håndterer allerede en betydelig del af vores indeksering gennem konventioner, især omkring primærnøgler og relationer, mens PostgreSQL håndterer de underliggende indeksstrukturer.
Men EF Core kan ikke alene afgøre, hvilke yderligere indeks der er hensigtsmæssige.
For QuestGame vurderer jeg, at vores nuværende indeksering er et fornuftigt udgangspunkt. Vi har relevante B-tree-indeks til relationer og opslag, unikke indeks til at beskytte dataintegriteten og et GiST-indeks, som understøtter en mulig fremtidig udvidelse.
Min vigtigste læring er derfor, at god indeksering ikke handler om at oprette flest mulige indeks, men om at forstå, hvordan data tilgås, og kun optimere, hvor der er et konkret behov.
