วิธีคัดลอกฐานข้อมูล SQLite ระหว่างคอมพิวเตอร์ให้เร็วขึ้น

• อธิบายวิธีคัดลอก ฐานข้อมูล SQLite ระหว่างคอมพิวเตอร์ให้เร็วขึ้น
• ดัชนีของฐานข้อมูลเป็นสาเหตุหลักที่ทำให้ความเร็วในการคัดลอกช้าลง
• สามารถใช้คำสั่ง .dump ของ SQLite เพื่อดัมพ์ฐานข้อมูลออกมาเป็นไฟล์ข้อความได้
• ไฟล์ข้อความมีขนาดเล็กกว่าฐานข้อมูลต้นฉบับ และเมื่อบีบอัดจะยิ่งเล็กลงอีก
• วิธีนี้ช่วยให้คัดลอกฐานข้อมูลขนาดใหญ่ได้เร็วและเสถียรมากขึ้น

---

วิธีคัดลอกฐานข้อมูล SQLite ระหว่างคอมพิวเตอร์ให้เร็วขึ้น

• อธิบายวิธีคัดลอก ฐานข้อมูล SQLite ที่เก็บอยู่บนเซิร์ฟเวอร์ระยะไกลมายังคอมพิวเตอร์ในเครื่อง
• ในช่วงเริ่มต้นของโปรเจ็กต์ สามารถคัดลอกแบบง่าย ๆ ด้วยคำสั่ง rsync ได้
• เมื่อฐานข้อมูลมีขนาดใหญ่ขึ้น ความเร็วในการคัดลอกจะช้าลงและความน่าเชื่อถือลดลง

สร้างดัมพ์ฐานข้อมูลเป็นไฟล์ข้อความ

• SQLite สามารถใช้คำสั่ง .dump เพื่อดัมพ์ฐานข้อมูลออกมาเป็นไฟล์ข้อความได้
• ไฟล์ข้อความนี้ประกอบด้วยคำสั่ง SQL และอาจมีขนาดเล็กกว่าฐานข้อมูลต้นฉบับ
• ดัชนีจะถูกย่อให้อยู่ในรูปบรรทัดเดียวในไฟล์ข้อความ จึงช่วยประหยัดพื้นที่จัดเก็บได้

ประหยัดพื้นที่จัดเก็บด้วยการบีบอัด

• ไฟล์ข้อความเมื่อบีบอัดแล้วจะมีขนาดเล็กลงอีก
• ตัวอย่างเช่น หากฐานข้อมูล SQLite ต้นฉบับมีขนาด 3.4GB ไฟล์ข้อความที่บีบอัดด้วย gzip จะลดลงเหลือ 240MB
• การดาวน์โหลดไฟล์ข้อความที่บีบอัดแล้วทำให้การคัดลอกฐานข้อมูลเร็วขึ้นอย่างมาก

คำสั่ง ssh+rsync แบบใหม่

• สร้างไฟล์ข้อความที่บีบอัดด้วย gzip บนเซิร์ฟเวอร์ คัดลอกมายังคอมพิวเตอร์ในเครื่อง แล้วประกอบฐานข้อมูลขึ้นใหม่
• สร้างไฟล์ข้อความที่บีบอัดบนเซิร์ฟเวอร์: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
• คัดลอกไฟล์มายังคอมพิวเตอร์ในเครื่อง: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
• แตกไฟล์บีบอัดและประกอบฐานข้อมูลใหม่ จากนั้นลบไฟล์ในเครื่อง

ดัมพ์ฐานข้อมูลเป็นแหล่งคัดลอกที่เสถียร

• หากมีการอัปเดตระหว่างการคัดลอกฐานข้อมูล rsync อาจสร้างไฟล์ฐานข้อมูลที่ไม่ถูกต้องได้
• การสร้าง text dump ช่วยแก้ปัญหานี้ด้วยการเตรียมแหล่งข้อมูลสำหรับคัดลอกที่เสถียร
• วิธีนี้ช่วยประหยัดเวลาเมื่อทำงานกับฐานข้อมูลขนาดใหญ่ และทำให้การดาวน์โหลดเร็วขึ้นและเชื่อถือได้มากขึ้น

[GN⁺]

ความคิดเห็นบน Hacker News

• SQLite มีเครื่องมือทางการให้ใช้งาน โดยทำงานในระดับเพจ และให้ฝั่งสำเนาส่งแฮชเข้ารหัสของแต่ละเพจไปยังต้นทาง จากนั้นต้นทางจะส่งเฉพาะเนื้อหาทั้งหมดของเพจที่แฮชไม่ตรงกลับมา
• การคัดลอกไฟล์ฐานข้อมูลที่กำลังใช้งานอยู่สามารถทำให้ข้อมูลเสียหายได้ และสามารถใช้ Litestream เพื่อทำสำเนาอย่างปลอดภัยได้
• วิธีหนึ่งในการคัดลอกฐานข้อมูลข้ามคอมพิวเตอร์คือส่ง seed ไปแล้วละเว้นส่วนที่เหลือ
  • incremental rsync เร็วกว่า แต่ไม่เห็นด้วยกับข้ออ้างที่ว่าการส่งคำสั่ง SQL จะเร็วกว่าการส่งฐานข้อมูล เพราะยังต้องรันคำสั่ง SQL และทำ optimization กับ vacuum อีก
  • มีสถานการณ์ที่ต้อง "สร้างฐานข้อมูลใหม่แบบ incremental" จากไฟล์ CSV แม้ว่าการสร้างฐานข้อมูลใหม่ตั้งแต่ต้นจะเหมาะสมกว่า แต่แค่รัน batch insert ลงในฐานข้อมูลว่างในหน่วยความจำก็ใช้เวลา 30 นาทีแล้ว
• ยูทิลิตี sqlite_rsync ที่เพิ่งออกมาไม่นาน ใช้อัลกอริทึม rsync เวอร์ชันที่ปรับให้เหมาะกับโครงสร้างภายในของฐานข้อมูล SQLite โดยเปรียบเทียบเพจข้อมูลภายในอย่างมีประสิทธิภาพ และซิงก์เฉพาะเพจที่เปลี่ยนแปลงหรือขาดหายไป
• การเก็บเป็นไฟล์ข้อความไม่มีประสิทธิภาพ และใช้ VACUUM INTO ในการบันทึกฐานข้อมูล sqlite
  • คำสั่ง VACUUM เป็นทางเลือกแทน Backup API และทำให้ฐานข้อมูลสำรองที่ได้มีขนาดเล็กที่สุด จึงลด file system I/O ลง
• น่าแปลกที่ไม่ได้ใช้ความสามารถด้านการบีบอัดที่ rsync มีให้ เพราะการบีบอัดด้วย gzip ก่อนส่งอาจเร็วกว่า
• ใน DuckDB สามารถ export เป็น Parquet เพื่อลดขนาดข้อมูลได้ ทำให้ทั้งการส่งและการโหลดเร็วขึ้น
• SQLite มี session extension สำหรับติดตามการเปลี่ยนแปลงของตาราง และสร้าง changeset/patchset ที่ใช้แพตช์ฐานข้อมูล SQLite เวอร์ชันก่อนหน้าได้
• สามารถปรับให้ดีขึ้นได้ด้วยออปชัน --rsyncable ของ gzip แม้จะลดระดับการบีบอัดลงเล็กน้อย แต่ช่วยจำกัดความต่างให้อยู่เป็นจุด ๆ โดยไม่กระทบต่อผลลัพธ์การบีบอัดทั้งหมด
  • สามารถข้ามการบีบอัดผลลัพธ์ dump แล้วให้ rsync คำนวณความต่างระหว่าง dump แบบไม่บีบอัดของก่อนหน้ากับปัจจุบัน จากนั้นค่อยให้ rsync บีบอัด changeset ที่จะส่งผ่านเครือข่าย
• มีประสบการณ์เมื่อปี 2008 ที่ต้องส่งข้อมูลสำรองด้วย Postgres ไปยังหลายเครื่อง และเครือข่ายอิ่มตัวจนต้องใช้ udpcast เพื่อส่งแบ็กอัปไปยังทุกปลายทางพร้อมกันในครั้งเดียว