Panduan Bertahan Hidup Postgres untuk Startup

 (hatchet.run)
3 poin oleh GN⁺ 4 jam lalu | 1 komentar | Bagikan ke WhatsApp
  • Berdasarkan masalah yang dialami Hatchet selama 2 tahun di produksi, panduan ini merangkum prinsip operasional bertahap mulai dari desain skema dan kueri awal hingga penulisan berskala besar dan migrasi tabel
  • Untuk pembacaan cepat, selaraskan indeks dengan ORDER BY, tetapi karena query planner dapat memilih sequential scan berdasarkan statistik dan biaya, bandingkan estimasi dengan eksekusi aktual menggunakan EXPLAIN ANALYZE
  • Performa dan stabilitas penulisan bergantung pada transaksi singkat, mengunci hanya baris yang diperlukan, CREATE INDEX CONCURRENTLY, dan connection pooling; dalam pengukuran Hatchet, batch processing meningkatkan throughput sekitar 10 kali
  • Di lingkungan dengan frekuensi penulisan tinggi, pengaturan default autovacuum mungkin tidak dapat mereklamasi dead tuple dan transaction ID tepat waktu; jika mencapai transaction ID wraparound, downtime besar dapat terjadi
  • Saat skala membesar, manfaatkan antrean kerja berbasis FOR UPDATE SKIP LOCKED, partisi, trigger, dan batch backfill, tetapi harus mampu mengendalikan SQL secara langsung di luar abstraksi ORM

Pembaca sasaran dan batasan ORM

  • Ini adalah panduan yang disusun agar developer yang memahami konsep dasar SQL, baris, tabel, dan indeks dapat menangani masalah Postgres di produksi
  • Manual Postgres memang komprehensif, tetapi sulit dijadikan rujukan cepat saat terjadi insiden, sehingga panduan ini memadatkan pengalaman operasional Hatchet selama 2 tahun
  • Prinsip-prinsipnya tetap berlaku meski menggunakan ORM, tetapi semakin besar skalanya, banyak optimasi hanya bisa dilakukan dengan keluar dari lapisan abstraksi dan menulis SQL secara langsung
    • Fitur seperti Prisma TypedSQL memungkinkan penggunaan ORM bersama SQL langsung
    • Hatchet yang berbasis Go menggunakan sqlc, yang menyediakan perilaku serupa
    • Untuk lingkungan tempat Claude menulis kueri, supabase/agent-skills direkomendasikan

Desain skema yang sulit diubah

  • Setelah deployment, perubahan skema adalah hal yang paling sulit, jadi setelah membuat draf tabel dan primary key, desain harus diiterasi sambil menulis kueri yang dibutuhkan aplikasi
  • Dalam proses desain, pastikan cara tabel akan digunakan dengan pertanyaan berikut
    • Mana yang lebih sering, pembacaan atau penulisan
    • Filter apa yang paling sering digunakan saat membaca
    • Kolom apa yang paling sering diperbarui
  • 1NF, 2NF, dan 3NF dari normalisasi basis data bisa diterapkan, tetapi bentuk normal kadang berbenturan dengan efisiensi kueri atau kemudahan penggunaan yang dibutuhkan untuk pengembangan cepat
    • Dalam beberapa situasi, memasukkan data ke kolom jsonb lebih sederhana
  • Aturan praktis yang diterapkan dalam desain skema adalah sebagai berikut
    • Untuk primary key, gunakan integer auto-increment berupa identity column atau UUID bawaan Postgres
    • Identity column sedikit lebih cepat daripada bigserial
    • Untuk waktu, selalu gunakan timestamptz
    • Semua tabel memiliki primary key
    • Gunakan foreign key dengan cascade delete pada tabel bervolume rendah yang membutuhkan konsistensi dan akurasi, tetapi berhati-hatilah di lingkungan bervolume tinggi

Kueri baca dan indeks

  • Model sederhana untuk memahami SELECT yang cepat adalah bahwa Postgres menemukan satu baris dengan cepat melalui indeks, atau membaca semua baris dalam tabel dengan sequential scan (seq scan)
  • Untuk pencarian satu baris yang cepat, gunakan struktur berikut
    • Indeks eksplisit
    • Unique constraint, yang merupakan bentuk khusus dari indeks
    • Primary key, yang otomatis diindeks oleh Postgres
  • Indeks default menggunakan btree, dan bisa dipahami seperti tabel terpisah yang menyimpan data dalam bentuk yang dioptimalkan untuk pencarian
    • Waktu pencarian baris kira-kira log(n), dengan n adalah jumlah baris dalam tabel
  • Jika indeks tidak dapat digunakan, sequential scan dijalankan, tetapi database modern memuat baris ke memori dengan cepat, sehingga pada tabel dengan kurang dari 20 ribu baris prosesnya bisa selesai hampir seketika

Join dan indeks komposit

  • Target internal join umumnya harus menggunakan primary key; jika tidak, mungkin ada masalah pada desain skema atau normalisasi
  • Perlakukan klausa ON seperti klausa WHERE, dan gunakan indeks yang sesuai pada kondisi join
  • Query daftar pada tabel besar cenderung menjadi kueri pertama yang terasa lambat di aplikasi
    • Jika memfilter dan mengurutkan berdasarkan organisasi dan waktu pembuatan sekaligus, indeks komposit dapat digunakan
CREATE INDEX CONCURRENTLY idx_documents_org_created
    ON documents (organization_id, created_at DESC);
  • Dalam kueri kompleks, aturan praktisnya adalah menempatkan kolom ORDER BY di bagian terakhir indeks dan menyamakan arah pengurutannya
    • Postgres memindai btree dua arah, sehingga DESC pada satu kolom bisa tidak bermakna, tetapi pada indeks komposit sebaiknya tetap diselaraskan
    • Detail perilaku indeks descending dapat dilihat di materi terkait

Penulisan, locking, dan migrasi

  • Syarat pertama penulisan yang sukses adalah menjaga transaksi tetap singkat
    • Jika tidak ada alasan khusus, jangan memanggil layanan eksternal di tengah transaksi
  • Syarat kedua adalah hanya mengunci baris yang diperlukan
    • Saat baris diperbarui, baris tersebut terkunci sampai transaksi di-commit
    • Semakin besar beban sistem, semakin terasa pula dampak locking
  • Jika menjalankan CREATE INDEX biasa pada tabel besar yang sudah ada, tabel akan terkunci sehingga insert dan update terblokir; karena itu selalu gunakan CREATE INDEX CONCURRENTLY
  • Kemampuan migrasi skema yang baik meningkatkan kecepatan pengembangan iteratif dan uptime
    • Sebisa mungkin hindari penghapusan atau removal kolom, dan lakukan perubahan dengan cara menambahkan
    • Jika memungkinkan, jalankan di dalam transaksi agar bisa menangani rollback dan penerapan parsial
    • Untuk pendekatan yang lebih maju, migrasi expand and contract dapat digunakan
  • Migrasi harus terlebih dahulu dinilai apakah akan memblokir semua penulisan
    • Pembuatan indeks tanpa CONCURRENTLY dapat memblokir semua penulisan dan menyebabkan downtime
    • Operasi ALTER TABLE harus ditinjau ulang, dan penambahan check constraint pada tabel besar juga dapat memblokir penulisan
    • Menambahkan check constraint sebagai NOT VALID dapat menghindari pemblokiran tersebut

Manajemen koneksi

  • Semua kueri dan transaksi menggunakan koneksi database, dan koneksi memiliki biaya CPU dan memori yang besar sehingga harus dipertahankan lama
  • Sering membuat dan menghapus koneksi membuang sumber daya
    • Connection storm, yaitu banyak koneksi baru yang terjadi bersamaan, dapat menimbulkan masalah yang sulit di-debug terkait locking internal Postgres
  • Pertimbangkan terlebih dahulu connection pooler eksternal seperti pgbouncer; jika tidak bisa digunakan, jadikan connection pool in-memory sebagai alternatif
    • Hatchet menggunakan pgxpool untuk Go karena tidak bisa berasumsi database pengguna memakai pooler eksternal

Query planner dan statistik

  • Kueri kompleks dengan banyak join atau campuran beberapa metode join tidak dapat diselesaikan hanya dengan menambahkan indeks
    • Indeks itu sendiri juga memiliki overhead, sehingga tidak boleh ditambahkan tanpa batas
  • Query planner mengubah SQL menjadi operasi internal database dan menentukan apakah indeks akan digunakan, tetapi karena informasinya terbatas, ia bisa gagal memilih rencana optimal
  • Informasi yang digunakan planner adalah statistik tabel dan dapat dilihat dari pg_stats
SELECT *
FROM pg_stats
WHERE tablename = 'mytable';
  • Statistik dikumpulkan saat ANALYZE dan juga diperbarui saat autovacuum berjalan
    • Meningkatkan frekuensi autovacuum juga menjaga statistik kueri tetap terbaru
    • Salah satu penyebab umum kueri berperilaku keliru adalah frekuensi analisis yang kurang
  • Menilai kueri secara sederhana berdasarkan ada tidaknya sequential scan dapat mengurangi micro-optimization yang justru meningkatkan ketidakpastian planner
    • Jika pencarian berpusat pada primary key dan indeks, planner lebih mudah memilih rencana

Analisis execution plan dan sequential scan

  • Beberapa penyedia, seperti Google CloudSQL, mengambil sampel kueri dan menyimpan kueri lambat, tetapi tidak semua layanan mendukungnya
  • EXPLAIN ANALYZE benar-benar menjalankan kueri dan membandingkan jumlah baris estimasi berdasarkan statistik tabel dengan jumlah baris yang benar-benar dipindai
    • Berhati-hatilah di produksi karena kueri aktual akan dijalankan
    • Untuk hanya melihat rencana tanpa menjalankan kueri, gunakan EXPLAIN tanpa ANALYZE
  • Rencana detail dapat disimpan sebagai JSON lalu divisualisasikan di explain.dalibo.com
psql -XqAt -f explain.sql -d $DATABASE_URL > analyze.json
  • Jika sequential scan tetap terjadi meski statistik dan indeks normal, planner mungkin menghitung bahwa biaya sequential scan lebih rendah
    • Indeks disimpan terpisah dari heap yang berisi data tabel sebenarnya, sehingga ada biaya untuk membaca kembali banyak baris yang ditemukan melalui indeks dari heap
    • Jika kueri tidak bisa direstrukturisasi secara besar, terima sequential scan tersebut atau pertimbangkan partisi

Penulisan massal dan batch processing

  • Setiap kueri memiliki overhead berupa round-trip database, waktu mendapatkan koneksi dari connection pool aplikasi, dan waktu pemrosesan Postgres
    • Locking internal Postgres juga bisa menjadi bottleneck di lingkungan throughput tinggi
  • Menggabungkan beberapa baris dalam satu kueri dapat mengurangi biaya-biaya tersebut
    • Cara paling sederhana adalah mengirim beberapa kueri sekaligus ke server sebagai transaksi implisit
    • Di Go, SendBatch dari pgx dapat digunakan
  • Di Hatchet, batch processing meningkatkan throughput sekitar 10 kali, dan optimasi insert tambahan dirangkum dalam panduan insert Postgres cepat

autovacuum dan transaction ID wraparound

  • autovacuum bertanggung jawab membersihkan dead tuple dan mengelola transaction ID, dan di lingkungan dengan frekuensi penulisan tinggi, pengaturannya mungkin perlu disesuaikan
  • Tuple adalah satu versi baris yang disimpan di sistem berkas
    • Meski baris diperbarui atau dihapus, versi lama tetap ada sampai semua transaksi yang dimulai sebelumnya di-commit atau di-rollback
    • Versi yang tidak lagi dapat dibaca oleh transaksi mana pun disebut dead tuple
  • Jika kecepatan penulisan terlalu tinggi, autovacuum tidak dapat mengejar laju pembuatan dead tuple sehingga kondisi database dapat memburuk dengan cepat
  • Jika saat memeriksa proses aktif di pg_stat_activity kueri autovacuum sudah berjalan sekitar 1 jam atau lebih, pertimbangkan perubahan pengaturan
  • Jika semua transaction ID habis sebelum autovacuum mereklamasinya, transaction ID wraparound terjadi dan dapat berujung pada downtime besar

Pembengkakan tabel dan indeks

  • Postgres menyimpan baris dalam halaman 8KB di disk, dan jika baris baru tidak bisa dimasukkan ke halaman yang ada, halaman baru dibuat
  • Jika halaman menjadi kosong sebagian setelah dead tuple direklamasi, terjadi table bloat sehingga penggunaan disk dapat meningkat besar
    • Pencegahan terbaik adalah menyesuaikan autovacuum sebelum terjadi bloat
    • Untuk tabel yang sudah bloat, ekstensi seperti pg_repack dapat digunakan
    • VACUUM FULL bawaan hampir tidak pernah menjadi pilihan yang baik
    • Postgres 19 direncanakan menambahkan REPACK...CONCURRENTLY untuk repacking tabel secara concurrent, tetapi Hatchet belum mengujinya
  • Index bloat juga merupakan bentuk khusus dari table bloat dan dapat dikurangi dengan pengaturan autovacuum yang tepat
    • Untuk indeks yang sudah bloat, perintah bawaan REINDEX INDEX CONCURRENTLY dapat digunakan

Pemrosesan concurrent berbasis FOR UPDATE SKIP LOCKED

  • FOR UPDATE SKIP LOCKED mencadangkan baris yang dipilih untuk transaksi saat ini tanpa mengganggu kueri lain
  • Hatchet menggunakannya untuk antrean kerja, dan dalam satu kueri dapat mengunci pekerjaan yang sedang menunggu lalu mengubah statusnya menjadi RUNNING
WITH eligible_tasks AS (
    SELECT *
    FROM tasks
    WHERE status = 'QUEUED'
    ORDER BY id ASC
    FOR UPDATE SKIP LOCKED
    LIMIT 100
)
UPDATE tasks
SET status = 'RUNNING'
FROM eligible_tasks
WHERE tasks.id = eligible_tasks.id
RETURNING tasks.*;
  • Ini juga berguna saat memperbarui baris yang saling independen secara concurrent atau saat beberapa instance aplikasi mengelola lease suatu objek
    • Hatchet menggunakannya untuk mendistribusikan tenant lease ke beberapa engine

Partisi

  • Partisi bawaan Postgres membagi tabel berdasarkan nilai baris seperti timestamp atau hash
  • Pada data time-series dan data pekerjaan historis Hatchet, ini memberikan manfaat berikut
    • Autovacuum dapat berjalan secara independen pada tiap partisi, sehingga kapasitas pemrosesan autovacuum untuk tabel dapat diperbesar
    • Data lama dapat dihapus hampir seketika dengan melepas tabel partisi, bukan menghapus per baris
  • Jika pada tahap perencanaan Postgres gagal menghapus partisi yang tidak diperlukan, kueri baca dapat mengalami overhead
    • Rilis Postgres terbaru telah memperbaiki partition pruning
    • Pengalaman operasional Hatchet dirangkum dalam artikel partisi Postgres

Memindahkan data antar tabel besar

  • Migrasi tabel besar yang dimaksud di sini bukan perubahan skema, melainkan pekerjaan memindahkan data massal dari satu tabel ke tabel lain
  • Menyalin tabel sangat besar dalam satu transaksi dapat memakan waktu berjam-jam
    • Transaksi berdurasi panjang menghambat kerja normal autovacuum dan menyebabkan bloat dead tuple
    • Jika penulisan terus terjadi pada tabel lama, data tersebut tidak tercermin di tabel baru
  • Hatchet menjalankan batch backfill besar di luar transaksi, dan penulisan baru setelah migrasi dimulai disalin ke tabel baru dengan trigger Postgres
    • Unique constraint pada primary key digunakan untuk mencegah penulisan duplikat

Bacaan terkait

1 komentar

 
GN⁺ 4 jam lalu
Pendapat di Hacker News

  • Kalau ini database operasional, rasanya hal pertama yang harus dibuat adalah rencana backup dan pemulihan. Ketersediaan tinggi mungkin bisa jadi opsi di tahap awal, tetapi agak mengherankan jika panduan bertahan hidup tidak memuat backup dan pemulihan
    Saya penasaran apakah Barman(https://pgbarman.org/) masih banyak dipakai untuk backup PostgreSQL sekarang

    • Jika bukan ahli PostgreSQL, lebih baik jangan mengoperasikannya sendiri dan gunakan database terkelola seperti RDS. Biaya yang dihemat dengan hosting sendiri sangat kecil dibanding biaya untuk mendapatkan ketersediaan tinggi yang teruji, backup dan pemulihan, pemulihan ke titik waktu tertentu, serta read replica
    • Saya memakai pgBackRest. Ini menyediakan pemulihan ke titik waktu tertentu yang lebih baik daripada solusi backup malam buatan sendiri yang dulu saya pakai, relatif mudah dikonfigurasi untuk backup ke Backblaze B2, dan sejauh ini tidak ada masalah berarti
    • Bagi kebanyakan kasus, cukup menjalankan pg_dump_all dari cron, mengompresnya dengan zstd, lalu menyalinnya ke S3 atau FTP, dan semacamnya. Ketika data membesar, waktu dan biaya backup penuh memang menjadi beban, tetapi dengan cara sederhana ini pun bisa bertahan cukup lama
    • Database yang menjamin durabilitas meski terjadi kegagalan daya bisa dibackup dengan snapshot volume atomik. Untuk mengurangi waktu pemulihan, buat checkpoint terlebih dahulu; untuk mencegah kerusakan data, atomisitas snapshot harus benar-benar terjamin
      Di AWS, kami membackup MongoDB berukuran beberapa TB dengan snapshot EBS untuk menerapkan backup inkremental dan pemulihan yang cepat. Ini tidak mendukung pemulihan ke titik waktu tertentu, tetapi karena bisa diambil sering dalam hitungan jam, cocok sebagai strategi tambahan yang dijalankan bersama alat khusus PostgreSQL
    • Jika sudah mengoperasikan Kubernetes, gunakan saja CloudNativePG

  • Ada beberapa hal yang bisa dilengkapi. Gunakan UUIDv7 daripada UUIDv4 yang umum, dan untuk menghindari deadlock, bukan hanya jumlah baris yang dikunci, tetapi juga urutan penguncian di semua kueri harus diseragamkan secara deterministik seperti id ASC
    Dengan EXPLAIN (GENERIC_PLAN), kueri bisa disalin sambil tetap mempertahankan placeholder parameter, dan Anda juga bisa melihat rencana optimisasi PostgreSQL saat nilai sebenarnya tidak diketahui. Pada tabel kosong atau kecil, SET enable_seqscan = off dapat digunakan untuk memeriksa kemungkinan penggunaan indeks
    Indeks B-tree yang dipakai semua orang secara default itu berat dan mudah membengkak, jadi jika hanya untuk lookup sederhana tanpa pengurutan atau pencarian rentang, indeks hash juga layak dipertimbangkan. Indeks hash unik tidak bisa dibuat, tetapi efek serupa bisa dicapai dengan hash exclusion constraint, dan indeks unik multi-kolom tidak didukung
    Ada baiknya juga mempelajari indeks GIN dan GiST. Bagi pengguna MySQL ini mungkin mengejutkan, tetapi kueri LIKE '%foo%' biasa pun bisa dipercepat tanpa mengubahnya menjadi full-text search

    • Deadlock tidak hanya terjadi ketika himpunan baris yang akan dikunci tidak memiliki ORDER BY yang konsisten, tetapi juga ketika urutan penguncian tabel berbeda. Jika satu transaksi mengunci dengan urutan table_a, table_b, sementara transaksi lain mengunci dengan urutan sebaliknya, deadlock tetap terjadi meski di dalam tiap tabel memakai ORDER BY dan FOR UPDATE
      Secara teori ini jelas, tetapi dalam praktik jauh lebih sulit di-debug karena harus memahami secara global tabel mana saja yang disentuh oleh semua operasi tulis, dan saya pernah benar-benar mengalaminya pada ekstensi tertentu. Saya sedang menguji GIN untuk lookup key-value JSONB, dan peningkatan performanya sangat besar; perbedaan performa antara AND dan OR juga cukup signifikan
    • UUID apa pun, jika dipakai sebagai primary key, biayanya besar karena join primary key sering terjadi dan biasanya manfaatnya kecil. Secara default lebih aman memakai primary key yang bertambah berurutan, lalu jika perlu diekspos ke luar, tambahkan kolom UUIDv4 dengan indeks sekunder. Saya penasaran apakah performa UUIDv7 pada B-tree memang benar-benar lebih baik daripada UUIDv4
    • Kalau sequential scan dimatikan, rasanya PostgreSQL akan memaksa memakai indeks mana pun selama ada setidaknya satu indeks. Jadi sepertinya itu tidak sampai memberi tahu apakah indeksnya tepat atau tidak
    • Untuk alat konversi UUIDv7 dan UUIDv4, https://github.com/ali-master/uuidv47 dan https://github.com/stateless-me/uuidv47 pernah beberapa kali diperkenalkan

  • Saran ini juga bagus, tetapi startup yang pernah saya ajak bekerja sama lebih dulu terbentur masalah organisasi yang letaknya lebih rendah daripada skalabilitas. Sebaiknya jangan gunakan ORM, gunakan primary key yang bertambah berurutan alih-alih field yang bermakna, dan pakai JSONB secara terbatas hanya saat benar-benar diperlukan
    Data sumber harus dibuat append-only, hanya bisa disisipkan, tanpa diubah atau dihapus. Tabel bantu yang didenormalisasi demi performa dan kemudahan boleh diubah, tetapi jangan dijadikan sumber kebenaran
    Gunakan connection pool, tetapi perhatikan jumlah koneksi; jika tidak ada masalah, PgBouncer mungkin belum diperlukan. Hindari transaksi eksplisit jika tidak ada alasan jelas, jangan melakukan pekerjaan lama seperti RPC saat transaksi masih terbuka, dan sebaiknya hampir tidak pernah menggunakan SERIALIZABLE
    Jika memerlukan lock eksplisit seperti SELECT FOR UPDATE, mungkin ada yang salah dengan desainnya. Jangan menciptakan ulang sistem tipe dengan membuat baris dalam satu tabel memiliki banyak makna bergantung pada nilai type int, atau meniru database graf dengan tabel node dan edge yang mereferensikan dirinya sendiri. Sebagian besar bisa diselesaikan dengan tabel ternormalisasi biasa

    • Di backend PHP yang sedang dikerjakan, objek harus diinstansiasi untuk pemeriksaan izin dan sebagainya, jadi ORM sangat berguna. Tanpa ORM, implementasinya tampak membutuhkan jauh lebih banyak pekerjaan; saya penasaran mengapa itu dianggap pilihan buruk
    • Jika gaji developer adalah biaya terbesar, prinsip jangan gunakan ORM cukup diperdebatkan. Di bawah kebutuhan bisnis tabel, tekanan pelanggan, dan anggaran ketat, biaya terus berjalan bahkan saat berdiskusi lama dengan DBA tentang desain yang benar, jadi prinsip menghindari kolom tipe atau struktur bergaya graf juga tidak semudah kedengarannya
    • Untuk startup yang harus cepat meluncurkan produk, ORM adalah pilihan yang cukup baik. Jika memahami jebakan seperti query N+1 dan lazy loading, itu kompromi yang lebih baik daripada membuat sendiri lagi manajemen query dan parameterisasi
      Di awal proyek, saya akan memilih menghabiskan waktu untuk pengembangan produk daripada terlalu memikirkan skema database dan melakukan optimasi prematur
    • Saya pernah memakai SELECT FOR UPDATE dengan berguna di beberapa tempat, jadi penasaran apa masalahnya. Saya juga ingin tahu apakah penggunaan sumber kebenaran append-only membuat lock seperti ini tidak diperlukan
    • Data sumber append-only memang menarik, tetapi pada beberapa sistem yang pernah saya kerjakan, itu akan membuat penyimpanan untuk banyak tabel membengkak demi manfaat yang meragukan. Ini teknik yang berguna, tetapi saya ragu apakah layak dipaksakan di semua tempat
      Sebaliknya, bagaimana dengan pendekatan menjadikan tabel relasional tradisional yang dapat diubah sebagai sumber kebenaran, lalu mencatat log perubahan dengan trigger?

  • Saya tidak suka cascade delete. Sebagian besar developer lebih banyak hidup di lapisan aplikasi seperti Python, Node, atau Go daripada di database, sehingga cascade delete mudah terlihat seperti sihir ketika menghapus baris di tabel A ternyata membuat data di tabel B ikut hilang. Jika salah dikonfigurasi, itu lebih berbahaya, jadi untuk pemeliharaan jangka panjang lebih baik memakai perintah delete yang eksplisit; konsistensi bisa dijaga hanya dengan menggunakan foreign key dengan benar
    Jebakan dan solusi untuk migrasi tabel besar memang benar, tetapi alat seperti pg-osc sudah ada. Seharusnya sesederhana menjalankan satu perintah lalu mengamati dengan tegang selama 24 jam saat data disalin
    Deployment aplikasi dan database harus dipisahkan sejak dini. Karena perubahan skema dan aplikasi tidak bisa dideploy benar-benar bersamaan dalam satu transaksi, setelah masuk produksi perlu membiasakan diri hanya melakukan perubahan skema yang kompatibel ke belakang, seperti membuat kolom baru nullable atau memberi default, serta tidak mengganti nama tabel atau kolom
    Strategi manajemen skema juga harus ditentukan sejak awal. Hindari prosedur deployment di mana developer senior menjalankan DDL secara manual ke DB produksi dari komputernya sendiri; bisa menggunakan alat yang sudah familier seperti Liquibase atau Flyway

    • Saya membuat pgschema, alat manajemen skema deklaratif

  • Query planner mengoptimalkan kasus rata-rata, tetapi untuk aplikasi kadang lebih berguna mengoptimalkan kasus terburuk. Pengguna rata-rata memiliki sedikit baris sehingga hasil keluar di bawah 10 ms dengan indeks tertentu, tetapi untuk pengguna dengan penggunaan berat, query yang sama bisa memakan waktu lebih dari 1 detik bergantung pada parameternya
    Dengan query yang lebih kompleks untuk memaksa jalur indeks lain, performa rata-rata menjadi sedikit lebih lambat, tetapi kasus terburuk juga turun menjadi di bawah 100 ms. Bagi perusahaan, mencegah timeout jauh lebih penting daripada menghemat rata-rata 10 ms

  • SKIP LOCKED berguna untuk antrean kerja berbasis transaksi interaktif, di mana aplikasi membuka transaksi dan mengunci baris selama pekerjaan berlangsung. Pada aplikasi berkinerja tinggi, transaksi semacam ini sendiri bisa dihindari dengan segera memperbarui baris menjadi pending, sehingga SKIP LOCKED tidak diperlukan
    Semakin besar skalanya, semakin perlu mengurangi state yang disimpan di memori database, dan transaksi interaktif juga termasuk state seperti itu. Dalam lingkungan berskala, idempotensi lebih menguntungkan daripada atomisitas

  • Transaksi yang berjalan lama dapat merusak kondisi database, jadi hanya boleh digunakan jika ada alasan kuat. Gunakan idle_in_transaction_session_timeout agar transaksi idle tidak menahan lock atau tuple terlalu lama, dan setel lock_timeout untuk migrasi agar satu DDL tidak menghentikan seluruh sistem
    statement_timeout juga harus disetel agar satu query mahal tidak melumpuhkan sistem

  • Setelah mengoperasikan PostgreSQL pada tahap awal startup, tulisan ini tidak cukup menekankan monitoring dan alert. PostgreSQL memiliki beberapa jenis kegagalan inti yang wajib dihindari, dan alert dapat menangkap risiko lebih awal
    Meski AWS mengirim email bahwa transaction ID hampir wraparound, di startup hal itu mudah terlewat, terutama pada hari seperti Boxing Day. Sinyal yang dipantau AWS harus dihubungkan ke pager, bukan email

  • Ada perbedaan besar yang kurang dikenal dalam implementasi connection pool. Sebagian besar connection pool aplikasi memakai first-in, first-out (FIFO) untuk mengoptimalkan latensi rendah dan ketersediaan koneksi, tetapi karena koneksi terus dijaga tetap hangat, sulit mengurangi koneksi yang tidak perlu
    PgBouncer dan beberapa pooler eksternal memakai last-in, first-out (LIFO) untuk mengoptimalkan jumlah koneksi yang mencapai PostgreSQL dan throughput. Jika koneksi terbaru digunakan ulang lebih dulu, koneksi yang tersisa akan mendingin secara alami dan ditutup
    Untuk aplikasi baru, FIFO sudah cukup, tetapi saat skala membesar, sebaiknya gunakan alat seperti PgBouncer untuk mengurangi ratusan koneksi sekitar 90%. Arsitektur PostgreSQL yang membuat proses untuk tiap koneksi bekerja lebih baik ketika jumlah koneksi lebih sedikit

  • Dalam situasi yang sangat spesifik, melakukan join di memori aplikasi memberikan hasil yang baik. Ada kalanya, demi mengurangi bolak-balik ke database, orang membuat satu kueri tunggal yang dipenuhi JOIN, UNION, dan CASE yang rumit
    Sebagai gantinya, menjalankan beberapa kueri sederhana secara independen lalu menelusuri hasilnya sambil menghubungkan baris terkait dengan map bisa justru lebih menguntungkan, karena rencana kueri menjadi lebih dapat diprediksi meski ada tambahan biaya bolak-balik dan iterasi. Ini hanya digunakan secara terbatas, dan fakta bahwa sebagian ORM bekerja seperti ini secara internal tidak berarti pendekatan ini selalu direkomendasikan

    • Efektivitas pendekatan ini sangat bergantung pada situasi. Jika join menghasilkan produk kartesian penuh yang jauh lebih besar daripada data sumber, mengambil hanya kumpulan sumber lalu menggabungkannya secara lokal dapat mengurangi beban DB dan trafik jaringan
      Namun, inner join yang selektif menghasilkan hasil yang jauh lebih kecil daripada data sumber, sehingga mengambil semua record lalu melakukan irisan dan pemfilteran secara lokal akan jauh lebih mahal. Pada index join, query planner juga dapat memanfaatkan indeks untuk menghindari table scan, sort, dan filtering secara membabi buta
    • Saya juga tahu ada pendekatan dengan membuat dua view lalu melakukan join, alih-alih satu kueri tunggal yang rumit