Sedikit lebih dari sebulan yang lalu di Moskow, konferensi terbesar komunitas pasca-gree PGConf.Russia 2019 diadakan, yang mempertemukan lebih dari 700 orang di Moscow State University. Kami memutuskan untuk mengirim video dan transkrip laporan terbaik.
Presentasi Ivan Frolkov tentang kesalahan tipikal ketika bekerja dengan PostgreSQL tercatat sebagai yang terbaik di konferensi, jadi kita akan memulainya.
Untuk kenyamanan, kami memecah dekripsi menjadi dua bagian. Pada artikel ini, kita akan berbicara tentang penamaan yang tidak konsisten, tentang kendala, tentang di mana lebih baik untuk berkonsentrasi logika - dalam database atau dalam aplikasi. Bagian kedua akan menangani penanganan kesalahan, akses bersamaan, operasi yang tidak dapat dibatalkan, CTE dan JSON.
Di perusahaan kami, saya terlibat dalam dukungan pelanggan pada masalah yang terkait dengan aplikasi, yaitu, saya membantu dalam kasus masalah dengan koneksi, dengan optimasi kueri dan hal-hal serupa lainnya. Saya telah melihat cukup banyak aplikasi yang paling beragam. Apa yang tidak saya lihat! Mungkin lebih dari yang kita inginkan. Bagian dari apa yang akan saya sampaikan tidak hanya berlaku untuk PostgreSQL, tetapi juga untuk basis data apa pun, tetapi terutama untuk PostgreSQL.
Kesimpulan utama yang saya dapat menarik dari apa yang saya lihat agak tidak terduga: pada kenyataannya, aplikasi apa pun dengan ketekunan yang tepat dapat dibuat untuk bekerja. Ada proyek yang luar biasa (saya tidak bisa menyebutkan semua perusahaan tempat kami bekerja) di mana aplikasi yang bahkan lebih hebat membuat tabel oleh jutaan orang. Itu terlihat seperti ini: pada hari Senin, sistem bekerja dengan baik, dan pada hari Jumat itu praktis tidak berfungsi. Pada akhir pekan, mereka meluncurkan VACUUM FULL, dan pada hari Senin itu berfungsi dengan baik lagi. Ternyata Anda dapat mengolok-olok PostgreSQL seperti ini, dan semua ini akan hidup dan berfungsi selama beberapa waktu. Kamerad lain melakukan hal yang aneh: semuanya dibangun di atas pemicu padanya, tidak ada prosedur sama sekali. Artinya, sebagian besar tabel tidak dapat disentuh, sesuatu tidak dapat dilakukan, tetapi pangkalan ini juga hidup.
Dia menjelaskannya seperti ini: “pangkalan bergerak dari satu kondisi yang konsisten ke kondisi lainnya yang konsisten. Jika saya mengunggah kembali data, itu akan rusak. Tetapi karena saya memiliki pemicu dan kunci unik, saya tidak dapat memutar kembali data. " Pendekatannya liar, tetapi pada saat yang sama masuk akal. Mungkin itu perlu dilakukan secara berbeda, tetapi juga perlu memperhitungkan fitur-fitur pelanggan. Kesalahan pertama yang akan saya bicarakan adalah:
Ini adalah contoh nyata yang saya temui. Pada slide, Anda melihat bagaimana entitas yang sama dinamai dalam kolom yang berbeda. Orang juga bisa dengan spasi. Objek lain juga disebut tidak konsisten. Jika Anda perlu mengambil sesuatu di meja lain, maka Anda perlu melihat apa namanya, apakah itu sama. Jika Anda memiliki id_user dan user_id di tabel yang sama, pekerjaan dimulai dengan penelitian: apa artinya semua itu.
Untuk klien lain, semua objek diberi nama seperti ini: dua huruf, lalu lima digit. Saya harus mengatakan bahwa itu bukan "1C". Mengapa mereka melakukan ini - saya tidak tahu: tidak ada logika dalam hal ini, tapi itu urusan saya untuk mengoptimalkan kueri.
Contoh lain: bagian dari nama-nama dalam bahasa Rusia, sebagian dalam non-Rusia, tetapi dengan semacam aksen Rusia. Ini membuat pemahaman menjadi sulit dan menciptakan kesalahan baru. Saya sendiri mencoba memberi nama kolom seolah-olah saya mengandalkan layanan, yang mana dari nama kolom ini secara otomatis akan membuat nama kolom normal dalam beberapa laporan. Dalam kehidupan nyata, sayangnya, tidak terlalu berhasil untuk menyebutkan nama secara konsisten - termasuk milik saya. Ini khususnya sulit dengan pengembangan kolektif. Tetapi kita harus berusaha.
Alasan penting lainnya untuk memberi nama secara berurutan: nama objek tersedia melalui permintaan untuk metadata, yaitu, nama juga data. Anda akan dapat menulis permintaan dan memilih, katakanlah, semua gambar - secara umum, semua gambar - dari database.
Metadata yang jelas sangat nyaman. Terutama ketika Anda mempertimbangkan masalah khas dengan dokumentasi - dan menurut pengalaman saya, dokumentasi biasanya tidak ada, tidak lengkap, atau salah, atau keduanya: karena tugas menulis dokumentasi yang baik sebanding dalam kompleksitasnya dengan tugas menulis kode itu sendiri. Jadi lebih baik bila kodenya mendokumentasikan diri. Dan penamaan objek yang logis dan konsisten berkontribusi pada hal ini, dan ketika ada sesuatu yang tidak jelas, Anda harus menulis kode snippet dan melihat cara kerjanya. Sekali tidak ada apa-apa, dua tidak ada, tetapi ketika Anda melakukannya sepanjang hari, itu melelahkan.
Kasus sebenarnya: organisasi yang sangat serius tempat kami bekerja memiliki basis - alur kerja di Oracle. Kami memindahkannya ke Postgres. Salah satu syarat kontrak adalah bahwa kami mengenakan KUNCI ASING. Mereka tidak ada di sana dan, sayangnya, kami tidak dapat memaksakan mereka: ternyata meja memiliki banyak baris "kiri", dan tidak ada yang tahu apa yang harus dilakukan dengan mereka, termasuk pelanggan.
Ketika Anda tidak perlu melihat progress bar, tetapi bekerja dengan dokumen untuk membayar uang, maka situasinya menyedihkan. Sangat membantu ketika, di bawah kontrak, programmer membayar kesalahan sendiri, dan diharapkan jumlahnya besar - kemudian pencerahan terjadi dalam beberapa menit, mungkin lima belas. Kendala segera muncul, segera semuanya mulai diperiksa.
Anda bahkan tidak membayangkan (yah, mungkin seseorang sudah membayangkan) betapa lebih nyamannya menangani kasus ketika pembayaran gagal, daripada saat pembayaran berlalu, tetapi tidak di sana. Apalagi jika jumlahnya besar. Ini dari pengalaman pribadi.
Di sisi lain, orang sering dapat mendengar bahwa kendala mengurangi kinerja. Ya, memang, tetapi jika Anda ingin memiliki data yang benar, tidak ada pilihan lain untuk Anda. Jika Anda memiliki aplikasi yang memperhitungkan jumlah kunjungan ke toko oleh pelanggan, maka mungkin ada ketidakakuratan yang tidak akan memengaruhi statistik terutama, dan jika kami menghitung uang, kendala diperlukan.
Nama kendala biasanya dihasilkan oleh ORM atau sistem, dan biasanya tidak ada yang secara khusus mengganggu batasan nama - tetapi sia-sia! Ketika Anda terus memproses kesalahan, dengan nama kendala, Anda dapat memberikan pesan yang jelas kepada pengguna, mengklasifikasikan kesalahan tersebut dan memberi tahu Anda apakah akan mencoba melakukan operasi lagi, atau apakah operasi ini tidak lagi diperlukan, atau tidak dapat diulangi lagi.
Hal lain yang belum saya lihat, tetapi sangat saya rekomendasikan: untuk semua operasi audit keuangan yang penting (dan bukan hanya keuangan), harus ada setidaknya dua. Faktanya adalah bahwa cepat atau lambat Anda akan menemukan sesuatu untuk diubah dalam kode, dan mungkin saja Anda melanggar salah satu cek. Maka yang kedua akan menyelamatkan Anda. Jika Anda melakukan tiga, itu tidak buruk juga.
Pertanyaan yang sering muncul: di mana memeriksa kebenaran data. Di klien atau di server? Menurut pendapat saya, jelas bahwa Anda perlu memeriksa di sana dan di sana. Anda memiliki kesalahan di klien, maka server tidak
akan hilang, atau Anda memiliki kesalahan di server, maka setidaknya klien akan membantu untuk melacaknya. Pertanyaannya agak bisa diperdebatkan, dan kami dengan lancar beralih ke topik: di mana menyimpan logika dasar: dalam aplikasi atau dalam database?
Lebih mudah dalam database karena, menurut pengalaman saya, bisnis secara rutin mengeluarkan perubahan mendesak: hapus atau sisipkan ini dan itu segera. Jika Anda memiliki logika dalam kode yang dikompilasi, maka Anda perlu mengumpulkan, menyebarkan, melihat apa yang terjadi. Seringkali ini tidak mungkin. Dalam database, ini lebih nyaman. Tapi ada pepatah terkenal: programmer Fortran berpengalaman menulis dalam bahasa Fortran dalam bahasa apa pun. 80 persen dari kode server ditulis dalam gaya yang sepenuhnya prosedural: kita memiliki fungsi "get_user ()" dan mengembalikan tipe "pengguna", dan jika "get_list_users ()", maka ia mengembalikan array "pengguna". Benar-benar lebih mudah untuk menulis hal-hal seperti itu di Java daripada di SQL atau pgsql.
Di sisi lain: mengapa Anda memerlukan fungsi "get_user ()"? Anda hanya membawanya dalam sebuah tabel atau tampilan. Karena Anda memiliki basis data relasional, Anda perlu menulis, menurut saya, relasional. Penting, pertama, untuk menentukan dengan jelas data apa yang sedang kami kerjakan: jika data kami adalah sampah atau setengah sampah, maka hasilnya akan sesuai, dan mungkin tidak boleh dibunuh. Jika data penting bagi kami, jika itu berupa uang, properti, atau operasi legal, maka kendala diperlukan dan semakin banyak semakin baik. Saya ulangi: lebih baik tidak melakukan operasi daripada melakukannya dengan salah. Dan jangan menulis kode prosedural dalam database relasional: Anda akan sangat menyesalinya.
Saya melihat meja dengan 30 ribu baris (produk), di mana permintaan "menunjukkan daftar barang yang relevan" dieksekusi selama sekitar satu detik. Rupanya, mereka berhasil membuat skema database "indah dan kompleks". Secara pribadi, saya pikir jika Anda melakukan sesuatu yang sangat rumit, maka kemungkinan besar Anda melakukan sesuatu yang salah atau Anda benar-benar memiliki tugas yang sangat, sangat sulit. Jika Anda memiliki semacam toko atau aplikasi reguler untuk akuntan, maka tidak mungkin ada hubungan yang sangat kompleks antara entitas.
Ketika saya memulai karir profesional saya, tabel dalam file DBF 60 megabyte dalam sistem perbankan tampak sangat besar, dan sekarang 60 megabita sama sekali tidak ada - perangkat keras lebih baik, perangkat lunak lebih baik, semuanya bekerja lebih cepat, tetapi pertanyaannya tetap: di mana Anda mendapatkan begitu banyak data? Basis yang sangat besar dan bengkak biasanya menjadi begitu karena arsip. Dalam setiap DBMS dan PostgreSQL, banyak upaya telah dilakukan untuk memastikan operasi kompetitif aplikasi yang konsisten. Arsip kemungkinan besar tidak berubah, dan sebagian besar kemampuan DBMS untuk bekerja dengannya tidak diperlukan sama sekali. Perlu dipikirkan untuk mengeluarkannya dari DBMS.
Sesekali dengan semacam juling komis, mereka mengajukan pertanyaan: apakah PostgreSQL akan menarik basis volume ini dan itu. Tapi di sini pertanyaannya sendiri aneh: Anda dapat memasukkan data ke dalam basis data sebanyak yang Anda suka, selama ada cukup ruang disk, maka banyak yang akan berbohong. Pertanyaannya adalah, misalnya, bagaimana cara membuat cadangan arsip dalam petabyte, tempat Anda menyimpan cadangan lengkap dan berapa banyak Anda akan mencopotnya. Saya sangat curiga bahwa setidaknya sebagian persyaratan volume ini terkait dengan keinginan penjual peralatan untuk menjual lebih banyak kepada Anda.
Jika Anda menyimpan dokumen dalam database, Anda tidak mungkin memprosesnya di sana: spreadsheet Excel tentu saja dapat dimodifikasi di server, tetapi ini adalah pekerjaan yang aneh. Kemungkinan besar file seperti itu akan dibaca secara umum saja. Lebih baik menyimpan tautan ke dokumen, dan menyimpannya di penyimpanan eksternal. Pada akhirnya, Anda dapat menyimpan tanda tangan digital dari tabel - sehingga tidak berubah (jika Anda memutuskan masalah legislatif yang relevan).
Pengamatan lain: jika Anda tidak memiliki beberapa bisnis mega-mega, bukan semacam, misalnya, perusahaan federal, maka Anda tidak mungkin memiliki basis yang sangat besar. Jika Anda tidak menyimpan video di dalamnya, tentu saja.
Alasan lain bahwa basis datanya besar adalah indeks yang tidak perlu. Pangkalan tanpa indeks saya tidak bertemu, tetapi cukup sering saya bertemu pangkalan di mana beberapa indeks pada kolom yang sama dalam urutan yang sama. Pangkalan memungkinkan Anda untuk melakukan ini. Saat Anda membuat indeks, silakan lihat apakah itu menggandakan indeks yang sudah ada. Anda dapat melihat indeks mana yang tidak diperlukan dengan melihat pg_stat_user_indexes untuk melihat seberapa aktif indeks digunakan. Mungkin dia tidak dituntut sama sekali.
Saya menemukan sebuah situasi (omong-omong, khas), ketika meja yang sangat besar tidak dipartisi. Di semua DBMS, tabel besar dipartisi dengan baik, tetapi dalam PostgreSQL ini terutama benar karena VACUUM favorit kami. Saya menyarankan agar tabel partisi dimulai dengan 100 gigabyte. Mungkin mulai dari 50. Saya melihat tabel terabyte yang tidak dipartisi, dan mereka hidup, bagaimanapun, pada SSD. Tapi ini agak banyak, akan lebih baik untuk memotongnya.
Dan satu pengamatan lagi: hampir semua database volume besar hanya menambahkan arsip. Langsung, perubahan data jarang ditemukan di database seperti itu. Penentu dengan apa yang Anda miliki - jika arsip, maka Anda dapat berpikir tentang bagaimana membawanya ke suatu tempat. Dan, omong-omong, Anda dapat memberikan akses ke sana dari database. Maka aplikasi tidak perlu diubah: tidak ada yang akan berubah untuk itu.
Beberapa pengamatan ini berasal dari kategori "lebih baik menjadi kaya dan sehat daripada miskin dan sakit." Seringkali, pertama, ada kode warisan. Kedua, sesuatu yang tidak terduga terjadi, mereka tidak memikirkan sesuatu, dan ternyata semuanya tidak seindah yang kita inginkan. Namun demikian: jangan terlalu pintar. Ingatlah bahwa jika Anda sangat cerdas, kemungkinan besar Anda melakukan sesuatu yang salah.
[Bersambung.]