Tips Praktik Terbaik untuk Memuat Data Massal PostgreSQL – Terkadang, database PostgreSQL perlu mengimpor data dalam jumlah besar dalam satu atau sedikit langkah. Ini umumnya dikenal sebagai impor data massal di mana sumber data biasanya satu atau beberapa file besar. Proses ini terkadang sangat lambat.
Tips Praktik Terbaik untuk Memuat Data Massal PostgreSQL
pgsql – Ada banyak alasan untuk kinerja yang buruk: indeks, pemicu, kunci asing, kunci primer GUID, atau bahkan Write Ahead Log (WAL) semuanya dapat menyebabkan penundaan.
Pada artikel ini, kami akan membahas beberapa tip praktik terbaik untuk mengimpor data secara massal ke database PostgreSQL. Namun, mungkin ada situasi di mana tidak satu pun dari tip ini akan menjadi solusi yang efisien. Kami menyarankan pembaca mempertimbangkan pro dan kontra dari metode apa pun sebelum menerapkannya.
Tip 1: Ubah Tabel Target ke Mode Tidak Masuk
Untuk PostgreSQL 9.5 dan yang lebih baru, tabel target dapat diubah terlebih dahulu menjadi UNLOGGED, kemudian diubah kembali menjadi LOGGED setelah data dimuat:
Mode UNLOGGED memastikan PostgreSQL tidak mengirimkan operasi penulisan tabel ke Write Ahead Log (WAL). Ini dapat membuat proses pemuatan menjadi sangat cepat. Namun, karena operasi tidak dicatat, data tidak dapat dipulihkan jika terjadi kerusakan atau pematian server yang tidak bersih selama pemuatan. PostgreSQL akan secara otomatis memotong tabel yang belum dicatat setelah dimulai ulang.
Juga, tabel yang tidak dicatat tidak direplikasi ke server siaga. Dalam kasus seperti itu, replikasi yang ada harus dihapus sebelum pemuatan dan dibuat ulang setelah pemuatan. Bergantung pada volume data di node utama dan jumlah standby, waktu untuk membuat ulang replikasi mungkin cukup lama, dan tidak dapat diterima oleh persyaratan ketersediaan yang tinggi.
Baca Juga : Keuntungan dan Kerugian Pengindeksan Dalam SQL
Kami merekomendasikan praktik terbaik berikut untuk memasukkan data secara massal ke dalam tabel yang tidak tercatat:
- Membuat cadangan tabel dan data sebelum mengubahnya ke mode tidak tercatat
- Membuat ulang replikasi apa pun ke server siaga setelah pemuatan data selesai
- Menggunakan sisipan massal yang tidak dicatat untuk tabel yang dapat diisi ulang dengan mudah (mis. tabel pencarian besar atau tabel dimensi)
Tip 2: Jatuhkan dan Buat Ulang Indeks
Indeks yang ada dapat menyebabkan penundaan yang signifikan selama penyisipan data massal. Ini karena setiap baris ditambahkan, entri indeks yang sesuai juga harus diperbarui.
Sebaiknya jatuhkan indeks di tabel target jika memungkinkan sebelum memulai penyisipan massal, dan buat ulang indeks setelah pemuatan selesai. Sekali lagi, membuat indeks pada tabel besar dapat memakan waktu, tetapi umumnya akan lebih cepat daripada memperbarui indeks selama pemuatan.
Mungkin bermanfaat untuk sementara waktu meningkatkan parameter konfigurasi maintenance_work_mem sebelum membuat indeks. Memori kerja yang meningkat dapat membantu membuat indeks lebih cepat.
Pilihan lain untuk bermain aman adalah membuat salinan tabel target di database yang sama dengan data dan indeks yang ada. Tabel yang baru disalin ini kemudian dapat diuji dengan penyisipan massal untuk kedua skenario: indeks drop-and-recreate, atau memperbaruinya secara dinamis. Metode yang menghasilkan kinerja yang lebih baik dapat diikuti untuk tabel langsung.
Tip 3: Jatuhkan dan Buat Ulang Kunci Asing
Seperti indeks, batasan kunci asing juga dapat memengaruhi kinerja pemuatan massal. Ini karena setiap kunci asing di setiap baris yang disisipkan harus diperiksa keberadaan kunci utama yang sesuai. Di belakang layar, PostgreSQL menggunakan trigger untuk melakukan pengecekan. Saat memuat sejumlah besar baris, pemicu ini harus diaktifkan untuk setiap baris, menambah beban tambahan.
Kecuali dibatasi oleh aturan bisnis, kami menyarankan untuk membuang semua kunci asing dari tabel target, memuat data dalam satu transaksi, lalu membuat ulang kunci asing setelah melakukan transaksi.
Sekali lagi, meningkatkan parameter konfigurasi maintenance_work_mem dapat meningkatkan kinerja pembuatan ulang batasan kunci asing.
Tip 4: Nonaktifkan Pemicu
Pemicu INSERT atau DELETE (jika proses pemuatan juga melibatkan penghapusan catatan dari tabel target) dapat menyebabkan penundaan pemuatan data massal. Ini karena setiap pemicu akan memiliki logika yang perlu diperiksa dan operasi yang perlu diselesaikan tepat setelah setiap baris DIMASUKKAN atau DIHAPUS.
Kami menyarankan untuk menonaktifkan semua pemicu di tabel target sebelum memuat data secara massal dan mengaktifkannya setelah pemuatan selesai. Menonaktifkan SEMUA pemicu juga menyertakan pemicu sistem yang memberlakukan pemeriksaan batasan kunci asing.
Tip 5: Gunakan Perintah SALIN
Kami merekomendasikan penggunaan perintah COPY PostgreSQL untuk memuat data dari satu atau beberapa file. COPY dioptimalkan untuk pemuatan data massal. Ini lebih efisien daripada menjalankan sejumlah besar pernyataan INSERT atau bahkan INSERT multi-nilai.
Manfaat lain menggunakan COPY meliputi:
- Ini mendukung impor file teks dan biner
- Sifatnya transaksional
- Ini memungkinkan menentukan struktur file input
- Itu dapat memuat data secara kondisional menggunakan klausa WHERE