SQLite'ta hızlı ve güvenli toplu INSERT: transaction'lar, WAL ve iOS

SQLite her yerde: iOS ve Android uygulamalarında, masaüstü araçlarında, test suite'lerinde, edge cihazlarda ve pek çok .NET servisinde. Aynı zamanda yavaş göstermesi en kolay veritabanlarından biri. Birkaç yüz bin satırı tek tek INSERT ile eklersen, bir iki saniye sürmesi gereken bir import dakikalara uzayabilir. Yanlış bir PRAGMA ile hızlandırırsan da kazandığın zamanı bir sonraki elektrik kesintisinde bozulmuş bir dosyayla ödeyebilirsin.

Bu yazıda toplu INSERT'in varsayılan olarak neden yavaş olduğunu, bunu düzelten teknikleri, sıkça önerilen "turbo" ayarların gerçek bedelini ve bunların iOS'ta SQLite C API, Core Data ve SwiftData ile nasıl uygulandığını, yanında bir .NET versiyonuyla birlikte anlatıyorum.

Varsayılan davranış: her INSERT kendi transaction'ı

Transaction'ı sen açmazsan SQLite her statement'ı kendi transaction'ına sarar. Kalıcı bir commit, verinin diske gerçekten yazıldığının onayını beklemek demek. SQLite FAQ bunu yıllardır anlatıyor: SQLite saniyede çok sayıda INSERT çalıştırabilir ama dönen disklerde saniyede yalnızca birkaç düzine transaction yapabilir. Bu maddeye 2024'te eklenen notta, bugünkü SSD'lerde rakamların çok daha yüksek olduğu söyleniyor. Ama ana fikir aynı: işleri tek transaction'da toplamak, commit maliyetini her satır için ayrı ayrı ödememeni sağlıyor.

İkinci maliyet parse etmek. Örneğin her satır için sprintf ile yeni bir SQL metni üretip sqlite3_exec çağırırsan, SQLite o SQL'i her seferinde baştan bytecode'a derler. Üstelik SQL injection'a bir tırnak işareti uzaklıkta olursun.

Toplu INSERT'i hızlandıran üç teknik

İşin büyük kısmını üç teknik yapıyor:

1. Batch'in etrafında tek transaction. En büyük kazanç açık ara bu.
2. Prepared statement'ı tekrar kullan. sqlite3_prepare_v2 bir kez, sonra döngüde bind/step/reset.
3. Önce yükle, sonra index'le. Her ikincil index, her satır için ayrıca güncellenen bir B-tree daha demek. Boş bir tabloya tek seferlik import yapıyorsan index'i en sonda oluşturmak genelde daha ucuzdur.

Swift'te C API'yi doğrudan kullanarak bu desen şöyle görünüyor. iOS'ta da macOS'ta da aynı şekilde çalışır:

import SQLite3

// SQLite'a bağlanan string'i kopyalamasını söyler (Swift string'leri geçicidir)
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

struct Stop { let routeId: String; let stopNo: Int; let time: String }

enum SQLiteError: Error { case failed(String) }

func check(_ rc: Int32, _ db: OpaquePointer?, expected: Int32 = SQLITE_OK) throws {
    guard rc == expected else {
        throw SQLiteError.failed(String(cString: sqlite3_errmsg(db)))
    }
}

func bulkInsert(_ db: OpaquePointer?, _ rows: [Stop]) throws {
    let sql = "INSERT INTO stops (route_id, stop_no, time) VALUES (?1, ?2, ?3)"
    var stmt: OpaquePointer?
    try check(sqlite3_prepare_v2(db, sql, -1, &stmt, nil), db)
    defer { sqlite3_finalize(stmt) }

    try check(sqlite3_exec(db, "BEGIN IMMEDIATE", nil, nil, nil), db)
    do {
        for row in rows {
            sqlite3_bind_text(stmt, 1, row.routeId, -1, SQLITE_TRANSIENT)
            sqlite3_bind_int64(stmt, 2, Int64(row.stopNo))
            sqlite3_bind_text(stmt, 3, row.time, -1, SQLITE_TRANSIENT)

            try check(sqlite3_step(stmt), db, expected: SQLITE_DONE)
            sqlite3_reset(stmt)
            sqlite3_clear_bindings(stmt)
        }
        try check(sqlite3_exec(db, "COMMIT", nil, nil, nil), db)
    } catch {
        sqlite3_exec(db, "ROLLBACK", nil, nil, nil)
        throw error
    }
}

İki detay önemli. SQLITE_TRANSIENT gerekli, çünkü Swift'in string köprüsü yalnızca çağrı süresince yaşıyor, yani SQLite'ın değeri kopyalaması lazım. BEGIN IMMEDIATE ise yazma kilidini en başta alıyor, böylece batch'in ortasında busy hatasıyla karşılaşmıyorsun.

Microsoft.Data.Sqlite ile .NET tarafı da aynı kalıpta. Microsoft'un bulk insert sayfası da aynısını söylüyor: SQLite'ın özel bir toplu yükleme API'si yok, transaction kullan ve tek bir parametreli command'ı tekrar tekrar çalıştır.

using Microsoft.Data.Sqlite;

static void BulkInsert(SqliteConnection connection, IEnumerable<Stop> rows)
{
    using var transaction = connection.BeginTransaction();

    using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText =
        "INSERT INTO stops (route_id, stop_no, time) VALUES ($route, $stop, $time)";

    var route = command.Parameters.Add("$route", SqliteType.Text);
    var stop  = command.Parameters.Add("$stop",  SqliteType.Integer);
    var time  = command.Parameters.Add("$time",  SqliteType.Text);

    foreach (var row in rows)
    {
        route.Value = row.RouteId;
        stop.Value  = row.StopNo;
        time.Value  = row.Time;
        command.ExecuteNonQuery();
    }

    transaction.Commit();
}

record Stop(string RouteId, int StopNo, string Time);

"Turbo" ayarların bedeli

Toplu yükleme için sıkça PRAGMA synchronous=OFF ve PRAGMA journal_mode=MEMORY öneriliyor. Yazmayı hızlandırdıkları doğru. Ama takas "son birkaç satırı kaybedebilirim"den daha büyük. PRAGMA dokümantasyonuna göre:

• synchronous=OFF ile uygulamanın çökmesi sorun değil. Ama veri diske ulaşmadan işletim sistemi çökerse ya da cihazın gücü giderse veritabanı dosyası bozulabilir.
• journal_mode=MEMORY ile rollback journal RAM'de tutuluyor. Uygulama bir transaction'ın ortasında çökerse veritabanı dosyası büyük ihtimalle bozuluyor.
• journal_mode=OFF daha da kötü: atomik commit ve rollback'i tamamen ortadan kaldırıyor.

Açılışta bir cache dosyasını yeniden üreten bir masaüstü aracında bu kabul edilebilir, dosyayı her zaman yeniden üretebilirsin. Kullanıcının notlarını tutan bir telefonda ise kabul edilemez. iOS uygulamanı her an sonlandırabilir, pil de her an bitebilir. Önerim şu: bu ayarları yalnızca sıfırdan yeniden oluşturabileceğin, atılabilir veritabanlarında kullan ve iş bitince geri kapat.

Modern varsayılanlar: WAL + NORMAL

Varsayılan olarak önerdiğim ayar şu:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

Anlamı doğrudan sqlite.org/wal.html ve PRAGMA dokümanından:

• WAL modunda okuyanlar yazanı, yazan da okuyanları bloklamaz. Dokümanın kendi ifadesiyle bu "çoğunlukla doğru": bazı nadir durumlarda yine SQLITE_BUSY alabilirsin, o yüzden busy timeout'u ayarlı tut.
• WAL ile synchronous=NORMAL bozulmaya karşı güvenli ve her zaman tutarlı.
• Synchronous ayarı ne olursa olsun, transaction'lar uygulama çökmelerinde kalıcı.
• Vazgeçtiğin şey şu: WAL + NORMAL ile commit edilmiş bir transaction, elektrik kesintisi ya da işletim sistemi çökmesi sonrasında geri alınabilir. En son commit'leri kaybedersin ama veritabanı sağlam kalır.
• journal_mode=WAL kalıcı: bir kez ayarlarsın, dosya yeniden açıldığında da WAL modunda kalır. synchronous ise bağlantı başına geçerli, her bağlantı açışında tekrar ayarlaman gerekir.

Çoğu uygulama için doğru takas bu: bozulma yok, uygulama çökmesinde veri kaybı yok, elektrik kesintisinde de yalnızca son commit'ler için küçük bir risk var. Bir commit'in elektrik kesintisinden mutlaka sağ çıkması gerekiyorsa (bir ödeme makbuzu gibi), o bağlantıda synchronous=FULL kullan.

iOS tarafı

iOS geliştiricilerinin çoğu sqlite3_* fonksiyonlarını hiç doğrudan çağırmıyor. Ama yine de SQLite kullanıyorlar.

Core Data. Apple'ın Technical Q&A QA1809 notuna göre SQLite store, iOS 7 ve OS X Mavericks'ten beri varsayılan journal modu olarak WAL kullanıyor. .sqlite dosyanın yanında -wal ve -shm dosyalarını görmenin sebebi bu. O nottan çıkan pratik ders şu: asla yalnızca ana dosyayı yedekleme ya da taşıma. Üçünü birlikte kopyala, daha iyisi NSPersistentStoreCoordinator'ın migration API'lerini kullan.

Toplu import için NSBatchInsertRequest (iOS 13+) her satır için bir managed object oluşturmadan doğrudan persistent store'a yazıyor:

import CoreData

func importStops(_ stops: [StopDTO], container: NSPersistentContainer) async throws {
    let context = container.newBackgroundContext()
    try await context.perform {
        var index = 0
        let request = NSBatchInsertRequest(entityName: "Stop") { (row: NSMutableDictionary) -> Bool in
            guard index < stops.count else { return true }   // true = bitti
            let s = stops[index]
            row["routeId"] = s.routeId
            row["stopNo"] = s.stopNo
            row["time"] = s.time
            index += 1
            return false
        }
        request.resultType = .objectIDs
        let result = try context.execute(request) as? NSBatchInsertResult
        let ids = result?.result as? [NSManagedObjectID] ?? []
        NSManagedObjectContext.mergeChanges(
            fromRemoteContextSave: [NSInsertedObjectsKey: ids],
            into: [container.viewContext])
    }
}

Bedeli şu: managed object context'i atladığı için alışık olduğun save() mekanizmasını kullanmıyorsun (özellikle relationship'ler batch insert'in zayıf noktası). Ayrıca yukarıdaki gibi merge etmediğin sürece bellekteki nesnelerin yeni satırlardan haberi olmuyor.

SwiftData. Apple'ın dokümantasyonu varsayılan store'u, altta Core Data kullanan bir store olarak tanımlıyor. Yani altta yine SQLite var, diskte de aynı -wal ve -shm dosyaları duruyor. Büyük import'larda kural aynı: arka plandaki bir ModelContext üzerinden ekle (genelde bir @ModelActor ile) ve her nesneden sonra değil, her batch'ten sonra bir kez kaydet.

GRDB. C interop'u kendin yazmadan SQL ile çalışmak istersen GRDB kendini uygulama geliştirmeye odaklı bir SQLite araç seti olarak tanımlıyor. DatabasePool veritabanını WAL modunda açıyor, write blokları da bir transaction içinde çalışıyor. Yani yukarıdaki tavsiyeler varsayılan davranış haline geliyor. Core Data ya da SwiftData'ya kıyasla bir bağımlılık eklemeye değip değmeyeceği ise ne kadar SQL yazmak istediğine bağlı.

Yaygın tuzaklar

• Kılık değiştirmiş "satır başına transaction". Döngünün içinde context.save() ya da SaveChanges() çağırmak bütün emeği boşa çıkarır.
• Main thread'de dev tek transaction. Tek transaction hızlıdır ama 500 bin satırlık bir import yine de arayüzü dondurur. İşi arka planda yap. İlerleme göstermen ya da belleği sabit tutman gerekiyorsa da işi parçalara böl (örneğin transaction başına birkaç bin satır).
• sqlite3_reset'i unutmak. Bu durumda sqlite3_bind_* çağrıları SQLITE_MISUSE döner ve bunu neredeyse kimse kontrol etmez. Güncel SQLite bir sonraki step'te statement'ı kendisi resetlediği için, bir önceki satırın değerlerini sessizce tekrar eklemiş olabilirsin.
• Yalnızca .sqlite dosyasını kopyalamak. WAL modunda commit edilmiş veri hâlâ -wal dosyasında duruyor olabilir.
• Bir türlü kapatılmayan turbo ayarlar. "Sadece import için" eklenen bir pragma kolayca production'a kadar gidebilir.

Kontrol listesi

• Batch tek ve açık bir transaction içinde (başka yazanlar olabiliyorsa BEGIN IMMEDIATE).
• Her satır için tekrar kullanılan tek bir prepared ya da parametreli statement.
• Yeni tablolarda ikincil index'ler toplu yüklemeden sonra.
• journal_mode=WAL bir kez, synchronous=NORMAL her bağlantıda.
• synchronous=OFF / journal_mode=MEMORY yalnızca yeniden üretilebilen, atılabilir veritabanlarında.
• iOS'ta: background context, büyük import'larda NSBatchInsertRequest, ve store'un üç dosyasının birlikte yedeklenmesi.

Temeller değişmedi: tek transaction, tek prepared statement, index'ler en sonda. Değişen şu: artık hızın büyük kısmını veren ve veritabanı dosyasını pilin insafına bırakmayan güvenli bir varsayılanımız var, WAL + NORMAL.