Mengurangi bloat dalam tabel dan indeks dengan ekstensi pg_repack

• Pembuatan awal tabel log - Tabel log dibuat untuk merekam perubahan yang terjadi selama salinan awal data, seperti yang ditunjukkan pada contoh berikut:

  postgres=>\dt+ repack.log_*
  List of relations
  -[ RECORD 1 ]-+----------
  Schema        | repack
  Name          | log_16490
  Type          | table
  Owner         | postgres
  Persistence   | permanent
  Access method | heap
  Size          | 65 MB
  Description   |

• swap-and-dropFase terakhir.

Rekomendasi berikut berlaku saat Anda menghapus bloat dari tabel dan indeks menggunakan ekstensi: pg_repack

• Lakukan pengemasan ulang selama jam non-bisnis atau melalui jendela pemeliharaan untuk meminimalkan dampaknya terhadap kinerja aktivitas database lainnya.

• Pantau sesi pemblokiran selama aktivitas pembangunan kembali dan pastikan tidak ada aktivitas pada tabel asli yang berpotensi memblokirpg_repack, khususnya selama swap-and-drop fase akhir ketika memerlukan kunci eksklusif pada tabel asli. Untuk informasi selengkapnya, lihat Mengidentifikasi apa yang memblokir kueri.

  Ketika Anda melihat sesi pemblokiran, Anda dapat menghentikannya menggunakan perintah berikut setelah mempertimbangkan dengan cermat. Ini membantu dalam kelanjutan pg_repack untuk menyelesaikan pembangunan kembali:

  SELECT pg_terminate_backend(pid);

• Saat menerapkan perubahan yang masih harus dibayar dari tabel pg_repack's log pada sistem dengan tingkat transaksi yang sangat tinggi, proses penerapan mungkin tidak dapat mengikuti tingkat perubahan. Dalam kasus seperti itu, tidak pg_repack akan dapat menyelesaikan proses penerapan. Untuk informasi selengkapnya, lihat Memantau tabel baru selama pengemasan ulang. Jika indeks sangat membengkak, solusi alternatif adalah melakukan pengemasan ulang indeks saja. Ini juga membantu VACUUM siklus pembersihan indeks untuk menyelesaikan lebih cepat.

  Anda dapat melewati fase pembersihan indeks menggunakan manual VACUUM dari Postgre SQL versi 12, dan dilewati secara otomatis selama autovacuum darurat dari Postgre versi 14. SQL Ini membantu VACUUM menyelesaikan lebih cepat tanpa menghilangkan kembung indeks dan hanya dimaksudkan untuk situasi darurat seperti mencegah sampul. VACUUM Untuk informasi selengkapnya, lihat Menghindari kembung dalam indeks di Panduan Pengguna Amazon Aurora.

• Tabel harus memiliki PRIMARY KEY atau tidak batasan-nolUNIQUE.

• Versi ekstensi harus sama untuk klien dan server.

• Pastikan bahwa RDS instance memiliki FreeStorageSpace lebih dari ukuran total tabel tanpa kembung. Sebagai contoh, pertimbangkan ukuran total tabel termasuk TOAST dan indeks sebagai 2TB, dan total kembung dalam tabel sebagai 1TB. Yang dibutuhkan FreeStorageSpace harus lebih dari nilai yang dikembalikan oleh perhitungan berikut:

  2TB (Table size) - 1TB (Table bloat) = 1TB

  Anda dapat menggunakan kueri berikut untuk memeriksa ukuran total tabel dan gunakan pgstattuple untuk mendapatkan kembung. Untuk informasi selengkapnya, lihat Mendiagnosis tabel dan indeks kembung di Panduan Pengguna Amazon Aurora

  SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;

  Ruang ini direklamasi setelah selesainya kegiatan.

• Pastikan RDS instance memiliki kapasitas komputasi dan IO yang cukup untuk menangani operasi pengemasan ulang. Anda dapat mempertimbangkan untuk meningkatkan kelas instance untuk keseimbangan kinerja yang optimal.

• Ukuran database ditingkatkan dengan ukuran total tabel dikurangi kembung, hingga swap-and-drop fase repack. Anda dapat memantau laju pertumbuhan ukuran database, menghitung kecepatan pengemasan ulang, dan memperkirakan secara kasar waktu yang diperlukan untuk menyelesaikan transfer data awal.

  Sebagai contoh, pertimbangkan ukuran total tabel sebagai 2TB, ukuran database sebagai 4TB, dan total kembung dalam tabel sebagai 1TB. Nilai ukuran total database yang dikembalikan oleh perhitungan pada akhir operasi repack adalah sebagai berikut:

  2TB (Table size) + 4 TB (Database size) - 1TB (Table bloat) = 5TB

  Anda dapat memperkirakan secara kasar kecepatan operasi pengemasan ulang dengan mengambil sampel laju pertumbuhan dalam byte antara dua titik waktu. Jika tingkat pertumbuhan 1GB per menit, dibutuhkan 1000 menit atau 16,6 jam kira-kira untuk menyelesaikan operasi pembuatan tabel awal. Selain pembuatan tabel awal, pg_repack juga perlu menerapkan perubahan yang masih harus dibayar. Waktu yang dibutuhkan tergantung pada tingkat penerapan perubahan yang sedang berlangsung ditambah perubahan yang masih harus dibayar.

  catatan

  Anda dapat menggunakan pgstattuple ekstensi untuk menghitung kembung dalam tabel. Untuk informasi selengkapnya, lihat pgstattuple.

• Jumlah baris dalam tabel pg_repack's log, di bawah skema repack mewakili volume perubahan yang menunggu untuk diterapkan ke tabel baru setelah pemuatan awal.

  Anda dapat memeriksa tabel pg_repack's log pg_stat_all_tables untuk memantau perubahan yang diterapkan pada tabel baru. pg_stat_all_tables.n_live_tupmenunjukkan jumlah catatan yang tertunda untuk diterapkan ke tabel baru. Untuk informasi selengkapnya, lihat pg_stat_all_tables.

  postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%';
          
  -[ RECORD 1 ]---------
  relname    | log_16490
  n_live_tup | 2000000
          

• Anda dapat menggunakan pg_stat_statements ekstensi untuk mengetahui waktu yang dibutuhkan oleh setiap langkah dalam operasi pengemasan ulang. Ini sangat membantu dalam persiapan untuk menerapkan operasi pengemasan ulang yang sama di lingkungan produksi. Anda dapat menyesuaikan LIMIT klausa untuk memperluas output lebih lanjut.

  postgres=>SELECT
       SUBSTR(query, 1, 100) query,
       round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes
   FROM
       pg_stat_statements
   WHERE
       query ILIKE '%repack%'
   ORDER BY
       total_exec_time DESC LIMIT 5;
          
   query                                                                 | total_exec_time_in_minutes
  -----------------------------------------------------------------------+----------------------------
   CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) |                     6.8627
   INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1           |                     6.4150
   SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)                    |                     0.5395
   SELECT repack.repack_drop($1, $2)                                     |                     0.0004
   SELECT repack.repack_swap($1)                                         |                     0.0004
  (5 rows)