Catatan Saya tentang Desain Skema Postgres GitLab (2022)

Catatan Saya tentang Desain Skema Postgres GitLab

• Dengan mempelajari skema Postgres GitLab, saya ingin membandingkannya dengan skema yang saya rancang sendiri dan mempelajari praktik terbaik dari definisi skema GitLab.
• GitLab adalah platform DevOps open source, alternatif GitHub, dan dapat di-host sendiri.

Penggunaan Tipe Kunci Utama yang Tepat

• Saat database masih kecil, hal ini tidak terlalu terlihat, tetapi seiring pertumbuhan, kunci utama memengaruhi penggunaan ruang penyimpanan, kecepatan tulis, dan kecepatan baca.
• Dari 573 tabel, GitLab menggunakan tipe kunci utama bigserial pada 380 tabel, serial4 pada 170 tabel, dan 23 sisanya menggunakan kunci utama komposit.

Penggunaan ID Internal dan Eksternal

• Praktik yang baik adalah tidak mengekspos kunci utama ke dunia luar.
• GitLab menggunakan baik id (ID internal) maupun iid (ID eksternal) pada tabel seperti issues, ci_pipelines, deployments, dan epics.

Penggunaan tipe data text dan Constraint CHECK

• Skema GitLab menggunakan character varying(n) dan text, tetapi lebih sering menggunakan tipe text.
• Tipe text tidak memiliki batasan panjang, dan batasan panjangnya didefinisikan dengan menggunakan CHECK.

Konvensi Penamaan

• Semua tabel menggunakan bentuk jamak dan memberi ruang nama dengan menggunakan awalan nama modul.
• Nama tabel dan kolom mengikuti aturan snake_case.

Penggunaan Zona Waktu pada Timestamp

• GitLab menggunakan timestamp with timezone dan timestamp without timezone.
• Untuk pekerjaan sistem, GitLab menggunakan timestamp without timezone, dan untuk pekerjaan pengguna menggunakan timestamp with timezone.

Constraint Kunci Asing

• GitLab menggunakan constraint kunci asing pada sebagian besar tabel, tetapi pada beberapa tabel seperti audit_events, abuse_reports, web_hooks_logs, dan spam_logs, GitLab tidak menggunakannya.

Partisi pada Tabel Besar

• GitLab melakukan partisi pada tabel-tabel yang dapat membengkak agar performa query meningkat.

Dukungan Kasus Penggunaan Pencarian LIKE dengan Trigram dan gin_trgm_ops

• GitLab menggunakan indeks GIN (Generalized Inverted Index) untuk melakukan pencarian yang efisien.

Penggunaan jsonb

• Skema GitLab menggunakan tipe data jsonb di beberapa tabel.

Tip Lainnya

• Tabel yang dapat diperbarui menggunakan field audit seperti updated_at, sedangkan pada tabel log yang tidak dapat diperbarui field tersebut tidak digunakan.
• Enum disimpan sebagai smallint alih-alih character varying untuk menghemat ruang.

Pendapat GN⁺:

• Desain skema GitLab memberikan wawasan mendalam tentang desain basis data dan memuat pelajaran penting tentang optimasi skema untuk sistem berskala besar.
• Karena GitLab bersifat open source, keputusan desain skema semacam ini memberi contoh praktis yang dapat diadopsi pengembang lain untuk proyek mereka.
• Hal yang bisa dipelajari dari skema GitLab adalah bahwa pemilihan tipe data, strategi pengindeksan, partisi, dan penggunaan constraint kunci asing sangat memengaruhi kinerja dan pemeliharaan basis data sehingga perlu dipertimbangkan dengan cermat.

[GN⁺]

Komentar Hacker News

• Saya mempertanyakan mengapa praktik untuk tidak mengekspos primary key ke luar itu diperlukan. Jika request pada akhirnya memang harus diautentikasi, saya juga tidak tahu apa nilainya mencegah ID ditebak
  Jika hanya dengan ID yang ditebak seseorang bisa melakukan sesuatu yang berguna tanpa autentikasi dan otorisasi, berarti sudah ada bagian lain yang rusak parah, dan seharusnya fokus ke sana daripada menambahkan kompleksitas yang tidak perlu pada skema. Menyembunyikan competitive intelligence agar pesaing tidak bisa memperkirakan jumlah pelanggan mungkin ada nilainya, tetapi rasanya GitLab tidak terlalu memedulikan hal itu. Keputusan GitLab soal id + iid tampaknya lebih mungkin didorong oleh kebutuhan performa query daripada pencegahan tebakan ID internal

  • Benar, tetapi ID yang bisa ditebak dapat membuat kerentanan keamanan menjadi mengerikan atau jauh lebih buruk
    Jika UUID diekspos ke pengguna, bahkan dengan kerentanan yang sama, penyerang harus menebak UUID sehingga jauh lebih sulit, dan mungkin membutuhkan sumber sekunder. Kalaupun terjadi kebocoran data, masih ada waktu untuk merespons dan volume kebocorannya bisa dihitung. Sebaliknya, jika ID berurutan, masalahnya bisa langsung membesar menjadi kebocoran penuh, dan menjadi insiden besar yang wajib dilaporkan ke otoritas pengawas privasi. Ini adalah defense in depth yang seharusnya tidak perlu berguna, tetapi dalam praktiknya ada perangkat lunak buruk yang benar-benar berhasil ditembus dengan cara seperti ini
  • Seperti disebutkan di artikel, ini lebih dekat ke competitive intelligence daripada keamanan. ID auto-increment sederhana mengungkap total jumlah record atau laju pertumbuhan sebuah tabel
    Jika primary key id pada tabel issue diekspos, saat membuat issue di sebuah proyek nilainya tidak dimulai dari 1, sehingga mudah menebak berapa banyak issue yang ada di seluruh GitLab
  • Istilah security theater terlalu sering disalahgunakan. Keamanan bisa dan memang seharusnya terdiri dari banyak lapisan, dan jika satu lapisan seperti autentikasi jebol, lapisan lainnya tidak boleh menjadi mudah diakses
    Tentu saja, jika sesuatu bisa dilakukan hanya dengan ID yang ditebak tanpa autentikasi dan otorisasi, itu memang masalah besar. Namun jika pada titik itu tidak ada lapisan lain, permainannya sudah selesai. Bug tidak memberi tahu lebih dulu sebelum muncul, terutama bug yang halus. Ketika bug seperti itu muncul, jika setidaknya ID sudah dibuat tidak dapat ditebak, Anda akan bersyukur karena terhindar dari situasi ketika semua akun pengguna di sistem bisa diakses dengan mudah
  • Bug juga bisa terjadi pada kontrol akses. ID yang tidak dapat ditebak membuat sebagian bug semacam itu jauh lebih sulit dieksploitasi
    Tentu saja yang harus diprioritaskan lebih dulu adalah memastikan kontrol akses yang benar, tetapi ID yang tidak dapat ditebak bisa menjadi pembeda antara bencana mengerikan dan nyaris celaka. Jika UUID tidak cocok, bisa juga memakai ID database auto-increment lalu mengenkripsinya; dengan lapisan perangkat lunak yang tepat, ID terenkripsi pun hampir bisa bekerja secara otomatis
  • Ini hanya perbedaan istilah kecil, tetapi menurut saya ini lebih tepat disebut competitive intelligence daripada “business intelligence”, yang biasanya berarti pemanfaatan data internal perusahaan. https://en.wikipedia.org/wiki/Competitive_intelligence

• Dari 128 juta repositori publik, sebagian besar adalah fork dari repositori lain dan hanya ada untuk membuat pull request ke repositori utama, jadi kecuali ada kesalahan, kemungkinan tidak punya issue
  Proyek mainan kecil atau proyek yang cepat ditinggalkan juga kemungkinan tidak punya issue atau hanya sangat sedikit. Pasti ada proyek dengan ratusan atau ribuan issue, tetapi rata-rata keseluruhan dari 128 juta repositori kemungkinan cukup rendah sehingga tetap berada di bawah batas 2 miliar. Meski begitu, saya setuju bahwa penggunaan tipe 4 byte pada tabel tersebut—tepatnya 31 bit—adalah bom waktu bagi sebagian organisasi, termasuk github.com

  • Saat ini pun masih di bawah batas, dengan 362.107.148 repositori dan 818.516.506 issue dan pull request unik
    https://play.clickhouse.com/play?user=play#U0VMRUNUIHVuaXEoc...
  • Saya rasa keputusan GitHub untuk menjauh dari Rails juga sebagian dipengaruhi oleh kekurangan besar ActiveRecord, yaitu kurangnya dukungan untuk composite primary key
    Kebutuhan mendasar seperti PRIMARY KEY(repo_id, issue_id) menjadi tidak perlu rumit di dalam ActiveRecord, dan perlu jalan memutar dengan memakai unique key bersama primary key terpisah agar sesuai dengan ActiveRecord yang menuntut satu kolom primary key. Primary key UUID juga terlihat seperti jalan memutar, tetapi constraint unik untuk pasangan (repo_id, issue_id) tetap diperlukan, sehingga ukuran database dan overhead meningkat. Secara lebih luas, menurut saya arsitektur MVC monolitik Ruby on Rails yang berbasis pada lapisan model, controller, dan view tunggal juga menimbulkan masalah skalabilitas dan maintainability seiring aplikasi membesar; MVC lebih cocok dalam struktur modular atau berbasis komponen
  • Saya penasaran apakah ada yang tahu pasti apakah GitLab Cloud memakai database multi-tenant, atau database per pengguna, pelanggan, atau organisasi
    Produk yang menyediakan self-hosted sekaligus cloud sering kali lebih menyukai database per pelanggan. Sebab bagian codebase yang dibagikan menjadi jauh lebih sederhana karena dapat menggunakan query yang sama terlepas dari jenis hosting. Jika database-nya per pelanggan, hampir tidak mungkin mendekati batas penggunaan seperti itu, dan jika sampai mendekatinya, self-hosting mungkin lebih cocok
  • Bom waktu itu adalah bom yang dapat dijinakkan dengan migrasi 11 detik
  • Migrasi primary key dari int ke bigint itu memungkinkan. Perlu persiapan dan sedikit kode kustom, tetapi bisa dilakukan tanpa downtime
    Secara umum kami mengelola migrasi besar dengan mengikuti prosedur ini, dengan sedikit modifikasi: http://zemanta.github.io/2021/08/25/column-migration-from-in...
    Foreign key, index, dan constraint secara keseluruhan membuat prosesnya sulit, tetapi bukan tidak mungkin. Dalam kasus saya, migrasi data memakan waktu beberapa jam, tetapi memang tidak perlu cepat. Setahu saya GitLab memiliki alat untuk menjalankan pekerjaan pasca-upgrade agar bisa berfungsi di titik mana pun selama upgrade versi

• Pembahasan soal ukuran penyimpanan kolom UUID kurang meyakinkan. Jika tabel punya 5 kolom lain, selisih 128-bit vs 64-bit tidaklah besar
  Kekhawatiran yang lebih penting adalah performa. UUIDv4 didukung luas, tetapi karena sepenuhnya acak, ia tidak ideal untuk performa indeks. UUIDv7[0] lebih dekat ke Snowflake[1] dan memiliki lokalitas temporal, tetapi implementasinya belum tersebar seluas itu. Pendekatan terpisah lainnya adalah memakai bigserial lalu mengenkripsi kuncinya: https://github.com/abevoelker/gfc64
  Namun cara ini 1) tidak memungkinkan rotasi nilai rahasia, dan 2) begitu bocor, siapa pun bisa membuat estimasi Fermi atas ukuran tabel. Memisahkan ID publik dan ID internal itu merepotkan, dan jika ID publiknya UUIDv4, performa juga dikorbankan. Menurut saya UUIDv7 adalah solusi yang memenuhi paling banyak syarat
  [0]: https://uuid7.com/
  [1]: https://en.wikipedia.org/wiki/Snowflake_ID

  • Masalahnya bukan hanya ukuran satu kolom itu, tetapi juga semua tempat id tersebut dipakai sebagai foreign key, serta ukuran indeks yang diperlukan untuk kolom foreign key tersebut
    Bayangkan nilai seperti ID pengguna yang bisa direferensikan oleh puluhan atau ratusan foreign key di seluruh database
  • Masalahnya adalah 5 kolom lainnya tidak diindeks
    Ada tiga tingkat dalam performa database. 1) Indeks dan data sama-sama muat di memori. 2) Indeks muat di memori, tetapi data tidak. 3) Indeks dan data sama-sama tidak muat di memori. Kondisi nomor 1 bagus, tetapi jika tidak, Anda harus mati-matian mempertahankan nomor 2. Ukuran indeks dua kali lipat membuat itu lebih sulit
  • Primary key database bisa dianggap seperti pointer dasar semacam typedef void*. Ukurannya memengaruhi performa secara menyeluruh: penggunaan memori dan disk, bottleneck throughput, hingga waktu CPU untuk membandingkan key di loop terdalam pada join dan lookup
    Ketika CPU x86-64 baru keluar, dampak performa dari peralihan ke pointer 64-bit begitu besar sehingga x32/ilp32 dibuat, dan itu juga alasan .NET sampai sekarang masih menjadikan “prefer 32-bit” sebagai default. Memakai UUID 128-bit sebagai primary key database adalah kesalahan yang mengerikan
  • UUIDv7 juga bukan obat mujarab. Dalam banyak kasus, kita tidak ingin membocorkan waktu pembuatan suatu resource
    Misalnya, Anda mungkin ingin mengunggah video sebulan sebelum dipublikasikan, tetapi tidak ingin penonton mengetahui fakta itu
  • Ada juga variasi lain dari pendekatan ini: https://pgxn.org/dist/permuteseq/
    Nilai juga bisa dienkripsi saat ditampilkan di URL, email, dan sebagainya: https://wiki.postgresql.org/wiki/Pseudo_encrypt
    Dengan begitu, banyak keunggulan indeks berurutan tetap dipertahankan sekaligus key masih bisa diganti. Namun jika key diganti, bookmark akan rusak, tautan yang pernah dikirim lewat email menjadi tidak valid, dan efeknya pada dasarnya sama seperti mengganti semua nama

• Ini mungkin sekadar nitpick kecil, tetapi saya ingin menyoroti bagian text vs varchar
  Penulis menghabiskan banyak kata untuk mencoba membuktikan perbedaan performa yang sebenarnya tidak ada, lalu menyimpulkan bahwa “tidak ada perbedaan performa besar” di antara kedua tipe. Ini topik yang sudah lama tuntas, dan bukan “tidak besar”, melainkan “tidak ada”. Wiki PostgreSQL[1] secara eksplisit menyarankan memakai text kecuali ada alasan yang sangat bagus, dan dokumentasinya[2] juga mengatakan bahwa “untuk banyak tujuan, character varying bertindak seperti domain di atas text”, serta di kotak Tip berwarna hijau menyatakan bahwa “tidak ada perbedaan performa di antara ketiga tipe ini”. Jadi, fakta bahwa GitLab kebanyakan memakai text tampaknya berarti mereka membaca dokumentasi dan merancang skema sesuai PostgreSQL, bukan membuat skema “portabel” yang setengah matang
  [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use...
  [2] https://www.postgresql.org/docs/current/datatype-character.h...

  • Dalam praktiknya, ketika harus memigrasikan skema mengikuti perubahan panjang string yang disimpan, perbedaan performanya menjadi cukup besar
    Mengubah varchar(300) menjadi varchar(200) mengharuskan semua baris ditulis ulang, tetapi memperbarui constraint pada kolom text pada dasarnya hampir gratis dan hanya memerlukan pemindaian seluruh tabel untuk memastikan nilai lama memenuhi constraint baru. Tulisan itu juga mengatakan bahwa menggunakan tipe text dan constraint CHECK membuat evolusi skema lebih mudah dibanding character varying atau varchar(n) saat ada pemeriksaan panjang

• Klaim bahwa foreign key itu mahal sering diulang, tetapi benchmark-nya jarang ada
  Ada banyak cara untuk mengimplementasikannya dengan keliru, tetapi bagaimanapun juga integritas tetap dipaksakan di suatu tempat dalam stack. Untuk memanfaatkan database alih-alih mengimplementasikannya ulang, dibutuhkan pengetahuan dan eksperimen, dan biasanya itu mencegah insiden besar

• Penasaran apakah ada yang pernah merangkum atau memperhatikan perbedaan performa antara GitLab dan GitHub
  Keduanya aplikasi berbasis Rails, tetapi secara umum waktu muat halaman GitLab terasa buruk dibandingkan GitHub

  • Saat memakai GitLab beberapa tahun lalu, masalah performa sisi klien pada pull request besar terasa parah. GitHub juga tidak ideal, tetapi masih menanganinya dengan cukup baik
  • Membandingkan dengan GitHub itu mirip seperti membandingkan Chrome dengan browser lain, bahkan browser berbasis Chromium
    Chrome dan GitHub akan memakai segala macam trik meskipun merugikan pengguna. Misalnya, saya pernah membuka diff hasil merge di GitHub perusahaan, mencari dengan Ctrl F, lalu saat tidak ada hasil saya lanjut menelusuri riwayat Git secara manual, sampai baru pada diff ke-100 saya sadar bahwa file terpenting tersembunyi jauh di dalam. Mungkin karena lebih mudah bagi seseorang untuk memenuhi metrik pemuatan halaman dan mendapat promosi
  • GitHub umumnya stabil dan biasanya cukup cepat, kecuali dua insiden outage tahun lalu. Kalau tidak begitu, saya tidak akan memakai pintasan keyboard
    Ada tulisan dari mantan developer di sini yang mungkin membantu memahami budaya GitLab dan pengabaiannya terhadap performa: https://news.ycombinator.com/item?id=39303323
    Saya sendiri tidak memakai GitLab cukup banyak sampai merasakan masalah performanya, tetapi menurut saya tulisan ini bisa membantu

• Saya selalu penasaran apa arti tambahan huruf I pada variabel CI CI_PIPELINE_IID dan CI_MERGE_REQUEST_IID
  Saya menduga itu pilihan terkait database, dan tulisan ini mengonfirmasinya

• Melihat bahwa “1 quintillion sama dengan 1.000.000.000 billion”, rasanya cukup aneh bahwa biasanya kita hanya memilih antara int32 dan int64. Sepertinya seharusnya ada tipe integer 5 byte yang mendukung kardinalitas sekitar 1 triliun

  • Kalau nilainya tidak dikemas rapat, memilih ukuran yang bukan pangkat dua tidak masuk akal

• Tidak memakai ID auto-increment bisa masuk akal, tetapi saya kurang paham manfaat punya 2 ID, satu untuk internal dan satu untuk eksternal
  Jumlah kolom dan indeks bertambah, selalu harus melakukan lookup lebih dulu, dan saya tidak terpikir skenario keamanan di mana kunci internal diubah sementara kunci eksternal tidak. Apa ada yang saya lewatkan?

  • Saat melakukan sesuatu per proyek, toh informasi yang diperlukan sudah dimiliki. Selain itu, bagi pengguna lebih ramah jika issue di tiap proyek dimulai dari 1 daripada dimulai dari angka seperti 2.700.300.571.325

• Katanya jika memakai tipe UUID v4 native PostgreSQL alih-alih bigserial, ukuran tabel bertambah 25% dan laju insert turun menjadi 25% dari bigserial; saya penasaran kenapa UUIDv4 bisa seburuk itu
  Bukankah UUID hanya angka 128-bit? Apakah biaya pembuatannya sangat mahal, atau apa yang sebenarnya terjadi?

  • UUIDv4 sepenuhnya acak, sedangkan indeks B-tree mengharapkan nilai “condong ke kanan” dengan urutan yang masuk akal
    Karena itu pekerjaan pengindeksan kolom UUIDv4 menjadi lambat, dan inilah motivasi pengembangan UUIDv6 dan UUIDv7
  • Kenaikan ukuran 25% memang benar, tetapi itu kenaikan linear kecil dan dapat diprediksi sebesar 8 byte per baris. Dibandingkan data lain dalam baris, itu bukan hal yang terlalu perlu dikhawatirkan
    Masalah yang lebih besar adalah laju insert. Untuk UUID, laju insert dibatasi oleh jumlah RAM yang tersedia. Integer auto-increment tidak begitu. Integer berkorelasi dengan waktu, tetapi UUID4 acak, sehingga pada skala besar karakteristik performanya berubah secara fundamental. Pada tabel kecil, penalti insert hampir bisa diabaikan, tetapi ketika ukuran indeks B-tree menyentuh batas memori, PostgreSQL tidak bisa menyimpan seluruh B-tree UUID di memori dan harus bergantung pada pergantian halaman disk. Integer auto-increment membuat baris yang berdekatan secara waktu memakai halaman indeks yang sama, sehingga pada beban yang sama tidak perlu menyentuh disk. Begitu mencapai skala ini, perbedaannya bukan penurunan tetap 25%, melainkan jurang performa 25 kali lipat, dan selain migrasi skema, satu-satunya pilihan adalah membeli lebih banyak RAM
  • Menurut saya penyebabnya B-tree. B-tree dan page bekerja lebih baik ketika hanya page terakhir yang banyak dipakai
    UUID menghasilkan banyak write yang tidak terurut, yang berujung pada page bloat
  • Jika urutan sort tersebar acak, lokalitas cache B-tree memburuk. Insert tidak menuju page terakhir, melainkan tersebar ke berbagai tempat
    Lokalitas batch insert juga memburuk saat di-query nanti, sehingga record terkait harus dicari secara acak. Pada akhirnya biaya dibayar baik saat insert maupun saat select berikutnya