Beberapa Hal Baru yang Saya Pelajari Saat Menjalankan SQLite

• SQLite bisa dipakai di lingkungan production untuk situs web kecil, tetapi ketika semakin banyak pekerjaan ditangani oleh Django ORM, saya merasakan kompleksitas operasional database ini
• Pencarian FTS5 pada tabel berisi 4.000 baris awalnya memakan waktu 5 detik, tetapi setelah menjalankan ANALYZE turun menjadi sekitar 0,05 detik, yang menunjukkan bahwa statistik query planner bisa sangat memengaruhi performa
• Jika DELETE dalam jumlah besar memakan waktu lebih dari 5 detik, operasi tulis lain juga bisa timeout dan bahkan membuat worker serta VM berhenti, jadi pekerjaan pembersihan dibagi menjadi batch kecil
• Untuk backup saya memakai VACUUM INTO dan restic, tetapi sempat mengalami penghentian karena OOM, dan kini juga sedang mencoba Litestream untuk backup inkremental yang lebih efisien
• Tabel dapat dipisah ke beberapa file SQLite, dan Mess with DNS dinilai cocok dijalankan dengan SQLite selama 4 tahun sejak 2022

---

Menjalankan SQLite di situs Django

• Saya memilihnya sebagai database untuk situs Django dengan merujuk pada berbagai tulisan yang menunjukkan bahwa SQLite juga bisa dipakai di situs production berskala kecil
• SQLite juga merupakan database yang kompleks dan tetap memerlukan pengetahuan operasional; dalam proyek ini, saya menyerahkan lebih banyak pekerjaan daripada sebelumnya melalui Django ORM
• Mengikuti berbagai rekomendasi, saya mengaktifkan mode WAL sejak awal
• Ini adalah situs web keempat saya yang menggunakan SQLite

ANALYZE yang mengubah performa pencarian

• Query full-text search menggunakan SQLite FTS5 pada tabel berisi 4.000 baris memakan waktu 5 detik
• Setelah menjalankan ANALYZE, query yang sama turun menjadi sekitar 0,05 detik, sampai-sampai tidak perlu diselidiki lebih jauh
• Saya tidak sempat memastikan masalah query plan apa yang sebenarnya terjadi, dan menduga situasinya mirip dengan kompleksitas waktu kuadratik yang tidak disengaja
• ANALYZE menghasilkan informasi statistik seperti jumlah baris tabel sehingga membantu query planner membuat pilihan yang lebih baik
• Saya juga masih belum terbiasa membaca query plan

Pembersihan data dan batas satu penulis

• Saat menghapus banyak baris yang tidak diperlukan, seperti task yang sudah selesai dari django-tasks-db, muncul masalah berantai
  • Perintah pembersihan yang menangani banyak baris berjalan lebih dari 5 detik
  • Selama itu, operasi tulis database dari worker lain melewati batas 5 detik yang disetel dan mengalami timeout
  • Worker yang gagal menulis crash, dan VM juga ikut berhenti
• Saya belum memastikan alasan pasti DELETE berjalan lambat, dan juga mempertimbangkan kemungkinan karena banyak kode Python dijalankan di dalam transaksi
• Sekarang saya membagi pekerjaan pembersihan ke batch kecil agar tiap query database tidak melebihi 5 detik
• Dari pengalaman ini saya jadi paham mengapa database seperti Postgres, yang dapat menangani beberapa operasi tulis sekaligus, dibutuhkan
• Ke depannya saya juga mempertimbangkan untuk menurunkan situs saat pekerjaan seperti ini berjalan dan melakukan maintenance terjadwal, tetapi alurnya belum disiapkan

Query ORM dan skala data saat ini

• Sejauh ini, saat membuat query yang saya inginkan dengan Django ORM, saya tidak memeriksa performanya secara khusus, tetapi selain masalah ANALYZE, sebagian besar berjalan normal
• Database ini kecil, sekitar 10.000 baris, dan kemungkinan akan tetap kecil ke depannya

Cara backup SQLite

• Saya sudah mencoba dua pendekatan untuk backup SQLite: restic dan Litestream
• Pekerjaan backup biasanya diawasi dengan dead man's switch, tetapi tampaknya saya belum benar-benar menguji proses pemulihannya

• Backup dengan restic

  • Saya membuat salinan database dengan VACUUM INTO, mengompresnya dengan gzip, lalu mengunggahnya ke S3
  • Dengan restic saya membuat backup, memeriksa snapshot, menghapus backup lama, dan melakukan pembersihan
  • Backup kadang berhenti karena OOM dan meninggalkan lock, sehingga saya juga menjalankan restic unlock

  sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
  gzip /tmp/calendar.sqlite
  restic -r s3://s3.amazonaws.com/some_bucket/ unlock
  restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
  restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
  restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
  restic -r s3://s3.amazonaws.com/some_bucket/ prune
  

• Backup inkremental dengan Litestream

  • Saya mulai memakai Litestream untuk menghindari penghentian karena OOM pada backup restic dan menguji efisiensi backup inkremental
  • Setelah menulis file konfigurasi, replikasi dijalankan dengan perintah berikut

  litestream replicate -config litestream.yml
  

  • Saya menetapkan retention: 400h untuk menyimpan riwayat database selama jangka waktu tertentu, tetapi belum memastikan apakah ini bekerja sesuai maksud
  • Saya melakukan backup ke AWS, tetapi karena membuat kredensial di konsol cukup merepotkan, saya juga mempertimbangkan pindah ke layanan kompatibel S3 lain

Menggunakan beberapa file database SQLite

• Proyek saat ini hanya memakai satu database, tetapi di Mess with DNS, tabel yang tidak perlu disimpan bersama dipisahkan ke 3 file database
• Saya menilai pemisahan ini membantu dari sisi operasional
• Mess with DNS telah berjalan dengan SQLite selama 4 tahun sejak 2022, dan untuk proyek itu berpindah dari Postgres ke SQLite adalah pilihan yang baik

Fitur dasar yang baru disadari saat operasional

• Saya pertama kali memakai SQLite di proyek web pada 2022, tetapi baru kali ini mengetahui keberadaan ANALYZE
• Bahkan fitur dasar dari teknologi yang kita pakai pun terus dipelajari selama bertahun-tahun lewat pengalaman operasional nyata

[GN⁺]

Pendapat di Hacker News

• Dengan menggunakan mode .expert SQLite, Anda bisa sedikit menunda hari ketika harus belajar membaca rencana kueri: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
  Jika menganalisis SELECT * FROM x1 WHERE a=? AND b>?;, ia memberi tahu indeks yang direkomendasikan seperti CREATE INDEX x1_idx_000123a7 ON x1(a, b);, lalu setelah indeks itu dibuat dan dianalisis lagi, ia menunjukkan bahwa tidak diperlukan indeks baru
  Bahkan di database “sungguhan” seperti Postgres, pekerjaan pembersihan biasanya disarankan untuk dibagi menjadi batch kecil. Hanya saja pada skala kecil, fakta bahwa pekerjaan itu tidak efisien tidak terlalu terlihat, jadi pendekatan di artikel asli ternyata lebih benar daripada yang dikira

  • Di beberapa database, jika 10 juta baris dihapus, data undo dalam jumlah yang sama juga akan dicatat. Di Oracle, jika ini tidak dicadangkan dan dikosongkan tepat waktu, ruang disk untuk archive log bisa habis
    Commit yang sering dapat membantu, tetapi saat membersihkan database besar secara berkala, partitioning adalah yang paling efektif. Menghapus partisi tertua hampir langsung selesai
    Namun, tidak jelas mengapa VM mati dalam pernyataan “worker tidak bisa menulis ke database, terjadi crash, lalu VM dimatikan”. Di sini VM tampaknya berarti mesin virtual tempat sistem operasi berjalan
  • Pada MySQL berskala besar yang menggunakan replikasi berbasis baris, UPDATE atau DELETE yang mengubah jutaan baris harus dijalankan dalam batch. Sebab jika diproses dengan satu kueri, jutaan baris yang diperbarui harus dikirim sekaligus ke semua replika
  • Tampak mirip dengan EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html
    EXPLAIN biasa umumnya mencetak bytecode yang terlalu panjang dari yang diperlukan, tetapi EXPLAIN QUERY PLAN menampilkan rencana yang diringkas
  • Saya penasaran apakah fakta bahwa “ketidakefisienan pekerjaan itu tidak terlalu terlihat” dianggap sebagai kelebihan

• Dari sudut pandang orang yang menangani database, ini membuat frustrasi untuk dibaca, dan rasanya ingin langsung mencari penyebabnya lalu memperbaikinya. Kalau itu tabel yang hanya berisi 10 ribu baris, full table scan seharusnya sangat cepat, apalagi SQLite yang berjalan di dalam proses pada server fisik yang sama
  Solusi yang langsung terlintas tentu saja “buat indeks”. Penghapusan yang lambat kemungkinan besar adalah masalah N+1 klasik yang sering dialami pengguna ORM sebelum memahami interaksi database internalnya, dan saya berharap Julia menulis artikel lanjutan

• Karena membuat kredensial di konsol AWS terlalu merepotkan, beberapa tahun lalu saya membuat alat yang hanya menyelesaikan masalah itu
  uvx s3-credentials create my-existing-s3-bucket
  Perintah ini mencetak kredensial baca-tulis yang cakupannya dibatasi hanya untuk bucket tersebut. Hak akses bisa makin dibatasi dengan --read-only, --write-only, atau dibuat hanya bisa membaca dan menulis key yang diawali prefix tertentu dengan --prefix foo/bar
  Saya juga pernah menggunakannya bersama Restic dan Cloudflare R2, dan bekerja sangat baik

  • Cara yang lebih umum untuk menangani layanan AWS yang rumit adalah mempelajari sedikit Terraform, lalu menyerahkan sisanya ke LLM. Ini juga membuat pembongkaran nanti jauh lebih mudah tanpa perlu mengingat resource apa saja yang dibuat
  • Ada juga alat lama yang mirip, yaitu https://litestream.io/
  • Ini alat yang sangat bagus, tampaknya layak diserap oleh AWS CLI. Namun saya penasaran kapan kredensial hanya-tulis diperlukan

• Di era LLM, saya makin menghargai tulisan Julia. Proses eksplorasi yang jujur adalah penawar bagi tulisan hasil generasi otomatis yang sok percaya diri seolah tahu segalanya

• Backup dijalankan seperti berikut:
  OUT="${i}.sql.zst"
PART="${OUT}.part"
sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
mv "${PART}" "${OUT}"
  Jika writer menggunakan WAL, ini tidak akan memblokirnya, dan menghasilkan dump yang rasio kompresinya bagus sekaligus mudah disinkronkan. Database Home Assistant berukuran 1,8 GB, tetapi dump terkompresinya 286 MB, dan saya memperkirakan sekitar 90% konten yang dibuat setiap hari sama

  • Saya penasaran apa yang dibackup di Home Assistant. Backup bawaan terlalu besar, jadi saya hanya menyisakan konfigurasi dan mengecualikan video, cache, serta repositori yang diunduh HACS, tetapi saya tidak tahu apakah ada hal penting yang terlewat
    Saya juga penasaran apakah database itu menjadi sebesar itu karena catatan deret waktu yang disimpan dalam jangka panjang
  • VACUUM INTO, .backup, sqlite3_rsync, dan Litestream juga tidak memblokir writer. .backup menggunakan API backup
  • Di database live, saya pernah terkunci sekali karena .dump, lalu beralih ke .backup. Meski begitu, cara menulis ke .part lalu mv memang rapi

• Ungkapan yang ada hanya seperti “tidak menyelidiki lebih lanjut”, “tebakan terbaik”, “mungkin hal-hal lain juga?”, “mungkin banyak kode Python dijalankan di dalam transaksi”, jadi menurut saya tulisan ini tidak punya isi yang substansial. Kadang penulis bahkan membuat dugaan yang keliru tanpa benar-benar mencari tahu atau menelitinya
  Sebagai pengguna Debian, alasan saya tidak membuka hasil pencarian terkait Linux yang mengarah ke forum Ubuntu juga karena terlalu banyak dugaan yang salah. Sebaliknya, meski Arch sangat berbeda dari Debian, Arch Wiki yang ditulis oleh orang-orang berpengetahuan biasanya saya periksa

  • Julia Evans memiliki pengetahuan teknis yang sangat luas, dan sangat piawai menghilangkan kesan mistis dari teknologi serta membantu orang memahami seperti apa proses pemecahan masalah yang nyata
    Tulisan ini tidak berpura-pura sebagai penjelasan dari pakar SQLite terbaik di dunia, dan sejak judulnya, “mempelajari beberapa hal tentang mengoperasikan SQLite”, ekspektasinya sudah ditetapkan dengan jelas
    Di seluruh tulisan Julia terasa pesan bahwa kita bisa memahami masalah dengan cara sederhana dan membangun pengetahuan tanpa harus mengetahui semuanya atau berpura-pura tahu. Membagikan apa yang sudah diketahui sejauh ini sejelas mungkin adalah sikap yang baik
  • Menurut saya tulisan ini bagus karena dengan baik menunjukkan situasi pengguna umum yang cerdas yang memakai teknologi tersebut. Fokus penulis adalah mengoperasikan situs web, dan masalah seperti ini adalah bagian yang sering menjebak pengguna sehari-hari yang tidak mengurusi satu alat tertentu sepanjang hari
    Saya juga kemarin saat membuat satu fitur memakai 2 bahasa pemrograman, 2 sistem build, penyedia cloud, alat manajemen rahasia, framework komunikasi klien-server yang kompleks lintas dua bahasa, sistem kontrol versi, editor, dan alat CI. Di luar itu masih bisa digali lagi sistem operasi dan versi runtime, database, reverse proxy, cache, hingga logika domain
    Kalau mendalami setiap topik yang ditemui, tidak ada pekerjaan yang akan selesai, jadi seperti penulisnya, kita harus memilih masalah yang akan difokuskan
  • Arch Wiki adalah salah satu sumber Linux terbaik. Saat memakai Mint saya selalu mencarinya, tetapi setelah benar-benar memakai Arch, saya malah lebih jarang membukanya dibanding saat masih memakai Mint

• Masalah DELETE dapat dengan mudah diredakan lewat penghapusan batch, jeda di antara batch, dan cara mengambil rowid terlebih dahulu dengan SELECT. SELECT tidak menyebabkan pemblokiran
  Jika data ditambahkan secara berurutan ke tabel yang sama, kemungkinan besar data itu juga disimpan di file dalam urutan yang mirip, jadi menghapusnya dalam urutan tersebut atau urutan terbalik bisa membuatnya lebih cepat. Hasilnya akan berbeda tergantung media penyimpanan dan kondisi lain

  • Pra-pemuatan ID baris terbukti sangat efektif bukan hanya di SQLite, tetapi juga di klaster Aurora MySQL atau Postgres berskala besar. SELECT bisa diarahkan ke replika, dan tekanan memori indeks akibat pemfilteran baris memberi beban besar pada CPU dan buffer cache database
    Jika sulit mengecualikan data yang tidak diperlukan dari jalur pemrosesan utama dengan cara seperti pruning partisi, ini adalah strategi yang sangat kuat

• Menggali database sedikit lebih dalam daripada tingkat yang saat ini nyaman atau tuntutan pekerjaan tetap merupakan cara yang baik untuk meningkatkan kemampuan. Banyak pengembang web sering tersendat di depan alat database, dan saya juga mengalami hal serupa pada beberapa teknologi operasional seperti K8s
  Mengetahui bagaimana SQL diterjemahkan menjadi operasi membaca dan menulis data di disk sangat membantu untuk menilai secara intuitif pendekatan mana yang masuk akal. Mekanisme locking database juga perlu dipahami bersama-sama
  Dengan pengetahuan seperti ini, kemungkinan untuk panik saat bahkan COUNT sederhana di Postgres tidak cepat selesai akan berkurang

• sqlite_stat1 dan sqlite_stat4 menyimpan berbagai informasi statistik tentang distribusi nilai indeks, dan query planner memperkirakan selektivitas serta kegunaan indeks berdasarkan informasi tersebut
  sqlite_stat1 hanya menyediakan jumlah record dalam indeks dan rata-rata jumlah record per nilai, sedangkan jika diaktifkan, sqlite_stat4 juga menyimpan data histogram

• Litestream sangat menarik, dan saya berhasil menjalankannya dengan S3 sebagai backend. Ini bisa membuat banyak sekali aplikasi yang menggunakan SQLite nyaris tidak bergantung pada status sistem file
  Status S3 jauh lebih mudah dikelola, dan backup serta sinkronisasi juga ditangani oleh penyedianya