SQLite professionell optimieren: PRAGMAs, Indizes und Partial Indexes (Teil 28)

SQLite ist die Standarddatenbank für Desktop-Anwendungen mit Entity Framework Core. Ohne gezielte Konfiguration arbeitet SQLite mit sehr konservativen Einstellungen, die für Produktionsanwendungen oft unnötig langsam sind. In diesem Artikel zeige ich, wie EchoPlay seine SQLite-Datenbank auf professionellem Niveau konfiguriert — von PRAGMAs über Indizes bis zu Partial Indexes.

Warum SQLite-Defaults nicht reichen

SQLite wurde als eingebettete Datenbank für maximale Sicherheit entwickelt. Die Standardeinstellungen sind deshalb extrem vorsichtig: Nach jedem Commit wird der Schreibvorgang mit einem vollständigen Dateisystem-Sync (PRAGMA synchronous = FULL) abgesichert, und der In-Memory-Cache ist auf nur 2 MB begrenzt. Für eine Desktop-App mit tausenden Datensätzen verschenkt das erhebliche Performance.

PRAGMAs — die Stellschrauben von SQLite

PRAGMAs sind spezielle Befehle, die das Verhalten der SQLite-Engine konfigurieren. Sie werden nach dem Öffnen einer Verbindung gesetzt — nicht im Connection-String, weil SQLite dort nur wenige Optionen unterstützt.

Write-Ahead-Log (WAL)

PRAGMA journal_mode = WAL;

Im Standard-Modus schreibt SQLite Änderungen zuerst in eine Rollback-Datei und dann in die eigentliche Datenbank. Im WAL-Modus werden Änderungen in eine separate Log-Datei geschrieben. Der große Vorteil: Leser und Schreiber blockieren sich nicht mehr gegenseitig. Wenn im Hintergrund ein Scan importiert, kann das Dashboard gleichzeitig Daten lesen — ohne Wartezeit.

Synchronous-Modus und Cache

PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -65536;    -- 64 MB (negative Werte = Kilobytes)
PRAGMA mmap_size = 134217728;  -- 128 MB
PRAGMA foreign_keys = ON;

Statt nach jedem Commit einen vollständigen Dateisystem-Sync durchzuführen, reicht in Kombination mit WAL der NORMAL-Modus. SQLite garantiert auch hier vollständige Crash-Recovery — nur bei gleichzeitigem Stromausfall und Betriebssystem-Absturz ginge im schlimmsten Fall ein einzelner Commit verloren. Der Cache hält häufig gelesene Datenbankseiten im RAM, und Memory-Mapped I/O erlaubt SQLite, die Datenbankdatei direkt aus dem Betriebssystem-Cache zu lesen. Ein Detail, das Einsteiger überrascht: SQLite erzwingt Fremdschlüssel-Constraints standardmäßig nicht. Ohne foreign_keys = ON kann man Episoden für nicht-existierende Serien anlegen.

EF-Core-Interceptor für PRAGMAs

PRAGMAs müssen bei jeder neuen Verbindung gesetzt werden. In EF Core geht das am saubersten über einen DbConnectionInterceptor. Dieser fängt das Verbindungs-Öffnen ab und führt die PRAGMAs aus, bevor EF Core die erste Abfrage sendet:

public sealed class SqlitePragmaInterceptor : DbConnectionInterceptor
{
    public override void ConnectionOpened(
        DbConnection connection, ConnectionEndEventData eventData)
    {
        using DbCommand command = connection.CreateCommand();
        ExecutePragma(command, "PRAGMA journal_mode = WAL;");
        ExecutePragma(command, "PRAGMA synchronous = NORMAL;");
        ExecutePragma(command, "PRAGMA cache_size = -65536;");
        ExecutePragma(command, "PRAGMA foreign_keys = ON;");
    }

    private static void ExecutePragma(DbCommand command, string pragma)
    {
        command.CommandText = pragma;
        command.ExecuteNonQuery();
    }
}

Die Registrierung erfolgt beim Aufbau des DI-Containers:

services.AddDbContext<EchoPlayDbContext>(options =>
    options.UseSqlite($"Data Source={dbPath}")
           .AddInterceptors(new SqlitePragmaInterceptor()));

Indizes — die wichtigste Performance-Maßnahme

Ohne Indizes durchsucht SQLite bei jeder Abfrage die gesamte Tabelle — ein sogenannter Full-Table-Scan. Bei 50.000 Episoden ist das spürbar langsam. Ein Index ist eine sortierte Kopie bestimmter Spalten, die SQLite erlaubt, Datensätze direkt zu finden statt zu suchen.

Ein einfacher Index beschleunigt Abfragen auf eine einzelne Spalte. Wenn eine Abfrage regelmäßig nach mehreren Spalten filtert oder sortiert, lohnt sich ein Komposit-Index. Die Reihenfolge der Spalten ist entscheidend — sie muss der Abfrage entsprechen:

// GetSubscribedAsync(): WHERE IsSubscribed = true ORDER BY Title
builder.HasIndex(s => new { s.IsSubscribed, s.Title });

Der Index (IsSubscribed, Title) kann sowohl das Filtern als auch das Sortieren übernehmen — SQLite muss weder die Tabelle scannen noch nachträglich sortieren.

Partial Indexes: Nur relevante Daten indizieren

Ein Partial Index enthält nur einen Teil der Datensätze. Das spart Speicher und beschleunigt die Pflege des Index bei Schreibvorgängen. In EchoPlay verwende ich Partial Indexes an zwei Stellen. Die meisten Abfragen interessieren sich nur für aktive Datensätze (IsDeleted = 0) — der Index enthält also nur diese. Für die Datenbankbereinigung gibt es einen separaten Index, der nur soft-gelöschte Datensätze enthält:

// Aktive Serien: nur nicht-gelöschte indizieren
builder.HasIndex(s => new { s.IsSubscribed, s.Title })
       .HasFilter("IsDeleted = 0");

// Purge: nur gelöschte indizieren
builder.HasIndex(s => new { s.IsDeleted, s.DeletedAt })
       .HasFilter("IsDeleted = 1");

PRAGMA optimize beim App-Shutdown

SQLite sammelt während einer Sitzung Statistiken darüber, welche Abfragen wie oft ausgeführt werden. Mit PRAGMA optimize am Ende der Sitzung aktualisiert SQLite seine internen Indizes und den Query-Planer basierend auf diesen Statistiken. Das verbessert die Abfrageplanung bei jedem nachfolgenden App-Start:

await context.Database.ExecuteSqlRawAsync("PRAGMA optimize;");