DBA: ສາມາດຈັດລະບຽບ synchronization ແລະການນໍາເຂົ້າ

ສໍາລັບການປຸງແຕ່ງສະລັບສັບຊ້ອນຂອງຊຸດຂໍ້ມູນຂະຫນາດໃຫຍ່ (ແຕກຕ່າງກັນ ຂະບວນການ ETL: ການນໍາເຂົ້າ, ການແປງແລະ synchronization ກັບແຫຼ່ງພາຍນອກ) ມັກຈະມີຄວາມຕ້ອງການ "ຈື່" ຊົ່ວຄາວແລະດໍາເນີນການຢ່າງໄວວາ ບາງສິ່ງບາງຢ່າງ voluminous.

ວຽກງານທົ່ວໄປຂອງປະເພດນີ້ມັກຈະມີສຽງຄ້າຍຄືນີ້: "ຢູ່​ທີ່​ນີ້ ພະແນກບັນຊີ unloaded ຈາກທະນາຄານລູກຄ້າ ການຈ່າຍເງິນທີ່ໄດ້ຮັບຄັ້ງສຸດທ້າຍ, ທ່ານຈໍາເປັນຕ້ອງໄດ້ອັບໂຫລດພວກມັນຢ່າງໄວວາໄປຫາເວັບໄຊທ໌ແລະເຊື່ອມໂຍງພວກມັນກັບບັນຊີຂອງທ່ານ."

ແຕ່ເມື່ອປະລິມານຂອງ "ບາງສິ່ງບາງຢ່າງ" ນີ້ເລີ່ມວັດແທກເປັນຫຼາຍຮ້ອຍ megabytes, ແລະການບໍລິການຕ້ອງສືບຕໍ່ເຮັດວຽກກັບຖານຂໍ້ມູນ 24x7, ຜົນຂ້າງຄຽງຫຼາຍຢ່າງເກີດຂື້ນທີ່ຈະທໍາລາຍຊີວິດຂອງເຈົ້າ.

ເພື່ອຈັດການກັບພວກມັນໃນ PostgreSQL (ແລະບໍ່ພຽງແຕ່ຢູ່ໃນນັ້ນ), ທ່ານສາມາດນໍາໃຊ້ການເພີ່ມປະສິດທິພາບບາງຢ່າງທີ່ຈະຊ່ວຍໃຫ້ທ່ານສາມາດປຸງແຕ່ງທຸກສິ່ງທຸກຢ່າງໄດ້ໄວຂຶ້ນແລະມີການບໍລິໂພກຊັບພະຍາກອນຫນ້ອຍ.

1. ໄປສົ່ງຢູ່ໃສ?

ທໍາອິດ, ໃຫ້ເຮົາຕັດສິນໃຈວ່າພວກເຮົາສາມາດອັບໂຫລດຂໍ້ມູນທີ່ພວກເຮົາຕ້ອງການ "ດໍາເນີນການ."

1.1. ຕາຕະລາງຊົ່ວຄາວ (TEMPORARY TABLE)

ໃນຫຼັກການ, ສໍາລັບຕາຕະລາງຊົ່ວຄາວ PostgreSQL ແມ່ນຄືກັນກັບອື່ນໆ. ເພາະສະນັ້ນ, ໂຊກຊະຕາເຊັ່ນ "ທຸກສິ່ງທຸກຢ່າງມີຢູ່ໃນຄວາມຊົງຈໍາເທົ່ານັ້ນ, ແລະມັນສາມາດສິ້ນສຸດໄດ້". ແຕ່ຍັງມີຄວາມແຕກຕ່າງທີ່ສໍາຄັນຈໍານວນຫນຶ່ງ.

"namespace" ຂອງທ່ານເອງສໍາລັບແຕ່ລະການເຊື່ອມຕໍ່ກັບຖານຂໍ້ມູນ

ຖ້າສອງເຊື່ອມຕໍ່ພະຍາຍາມເຊື່ອມຕໍ່ໃນເວລາດຽວກັນ CREATE TABLE x, ຫຼັງຈາກນັ້ນຜູ້ໃດຜູ້ຫນຶ່ງຈະໄດ້ຮັບແນ່ນອນ ຄວາມ​ຜິດ​ພາດ​ບໍ່​ເປັນ​ເອ​ກະ​ລັກ​ ວັດຖຸຖານຂໍ້ມູນ.

ແຕ່ຖ້າທັງສອງພະຍາຍາມປະຕິບັດ CREATE TEMPORARY TABLE x, ຫຼັງຈາກນັ້ນທັງສອງຈະເຮັດມັນເປັນປົກກະຕິ, ແລະທຸກຄົນຈະໄດ້ຮັບ ສຳເນົາຂອງເຈົ້າ ໂຕະ. ແລະ​ຈະ​ບໍ່​ມີ​ຫຍັງ​ຢູ່​ໃນ​ທົ່ວ​ໄປ​ລະ​ຫວ່າງ​ເຂົາ​ເຈົ້າ​.

"ທໍາລາຍຕົນເອງ" ເມື່ອຕັດການເຊື່ອມຕໍ່

ເມື່ອການເຊື່ອມຕໍ່ຖືກປິດ, ຕາຕະລາງຊົ່ວຄາວທັງຫມົດຈະຖືກລຶບອັດຕະໂນມັດ, ດັ່ງນັ້ນດ້ວຍຕົນເອງ DROP TABLE x ບໍ່ມີຈຸດໃດຍົກເວັ້ນ ...

ຖ້າທ່ານກໍາລັງເຮັດວຽກຜ່ານ pgbouncer ໃນຮູບແບບການເຮັດທຸລະກໍາ, ຫຼັງຈາກນັ້ນຖານຂໍ້ມູນຍັງສືບຕໍ່ເຊື່ອວ່າການເຊື່ອມຕໍ່ນີ້ຍັງເຮັດວຽກຢູ່, ແລະໃນນັ້ນຕາຕະລາງຊົ່ວຄາວນີ້ຍັງມີຢູ່.

ດັ່ງນັ້ນ, ພະຍາຍາມສ້າງມັນອີກເທື່ອຫນຶ່ງ, ຈາກການເຊື່ອມຕໍ່ທີ່ແຕກຕ່າງກັນກັບ pgbouncer, ຈະເຮັດໃຫ້ເກີດຄວາມຜິດພາດ. ແຕ່ນີ້ສາມາດຫລີກລ້ຽງໄດ້ໂດຍການໃຊ້ CREATE TEMPORARY TABLE IF NOT EXISTS x.

ແທ້ຈິງແລ້ວ, ມັນກໍ່ດີກວ່າທີ່ຈະບໍ່ເຮັດແນວນີ້, ເພາະວ່າຫຼັງຈາກນັ້ນທ່ານສາມາດ "ທັນທີທັນໃດ" ຊອກຫາຂໍ້ມູນທີ່ຍັງເຫຼືອຈາກ "ເຈົ້າຂອງກ່ອນ". ແທນທີ່ຈະ, ມັນດີກວ່າທີ່ຈະອ່ານຄູ່ມືແລະເຫັນວ່າເມື່ອສ້າງຕາຕະລາງມັນເປັນໄປໄດ້ທີ່ຈະເພີ່ມ ON COMMIT DROP - ນັ້ນແມ່ນ, ເມື່ອການເຮັດທຸລະກໍາສໍາເລັດ, ຕາຕະລາງຈະຖືກລຶບອັດຕະໂນມັດ.

ບໍ່ມີການຈໍາລອງ

ເນື່ອງຈາກວ່າພວກເຂົາເປັນພຽງແຕ່ການເຊື່ອມຕໍ່ສະເພາະ, ຕາຕະລາງຊົ່ວຄາວບໍ່ໄດ້ຖືກ replicated. ແຕ່ ນີ້ກໍາຈັດຄວາມຕ້ອງການສໍາລັບການບັນທຶກຂໍ້ມູນສອງເທົ່າ ໃນ heap + WAL, ດັ່ງນັ້ນ INSERT/UPDATE/DELETE ເຂົ້າໄປໃນມັນໄວກວ່າ.

ແຕ່ເນື່ອງຈາກຕາຕະລາງຊົ່ວຄາວຍັງເປັນຕາຕະລາງ "ເກືອບທໍາມະດາ", ມັນບໍ່ສາມາດສ້າງຢູ່ໃນແບບຈໍາລອງໄດ້. ຢ່າງຫນ້ອຍສໍາລັບໃນປັດຈຸບັນ, ເຖິງແມ່ນວ່າ patch ທີ່ສອດຄ້ອງກັນໄດ້ແຜ່ລາມເປັນເວລາດົນນານ.

1.2. ບໍ່ໄດ້ບັນທຶກຕາຕະລາງ

ແຕ່ສິ່ງທີ່ທ່ານຄວນເຮັດ, ສໍາລັບຕົວຢ່າງ, ຖ້າທ່ານມີຂະບວນການ ETL ທີ່ຫຍຸ້ງຍາກບາງຢ່າງທີ່ບໍ່ສາມາດປະຕິບັດໄດ້ພາຍໃນຫນຶ່ງທຸລະກໍາ, ແຕ່ວ່າທ່ານຍັງມີ pgbouncer ໃນຮູບແບບການເຮັດທຸລະກໍາ? ..

ຫຼືການໄຫຼເຂົ້າຂອງຂໍ້ມູນມີຂະຫນາດໃຫຍ່ດັ່ງນັ້ນ ບໍ່ມີແບນວິດພຽງພໍໃນການເຊື່ອມຕໍ່ດຽວ ຈາກຖານຂໍ້ມູນ (ອ່ານ, ຂະບວນການຫນຶ່ງຕໍ່ CPU)?..

ຫຼືບາງການດໍາເນີນງານແມ່ນດໍາເນີນຕໍ່ໄປ asynchronously ໃນ​ການ​ເຊື່ອມ​ຕໍ່​ທີ່​ແຕກ​ຕ່າງ​ກັນ​?..

ມີ​ພຽງ​ແຕ່​ທາງ​ເລືອກ​ທີ່​ນີ້ - ສ້າງຕາຕະລາງທີ່ບໍ່ແມ່ນຊົ່ວຄາວ. Pun, ແລ້ວ. ນັ້ນ​ແມ່ນ:

• ສ້າງຕາຕະລາງ "ຂອງຂ້ອຍເອງ" ທີ່ມີຊື່ແບບສຸ່ມສູງສຸດເພື່ອບໍ່ໃຫ້ຕັດກັບໃຜ
• ສານສະກັດຈາກ: ຕື່ມຂໍ້ມູນໃຫ້ເຂົາເຈົ້າຈາກແຫຼ່ງພາຍນອກ
• ການປ່ຽນແປງ: ປ່ຽນໃຈເຫລື້ອມໃສ, ຕື່ມໃສ່ໃນຊ່ອງເຊື່ອມຕໍ່ທີ່ສໍາຄັນ
• Load: poured ຂໍ້ມູນພ້ອມທີ່ຈະເຂົ້າໄປໃນຕາຕະລາງເປົ້າຫມາຍ
• ລຶບຕາຕະລາງ "ຂອງຂ້ອຍ" ແລ້ວ

ແລະໃນປັດຈຸບັນ - ແມງວັນໃນຢາຂີ້ເຜິ້ງໄດ້. ໃນ​ຄວາມ​ເປັນ​ຈິງ, ການຂຽນທັງຫມົດໃນ PostgreSQL ເກີດຂຶ້ນສອງຄັ້ງ - ທໍາອິດໃນ WAL, ຫຼັງຈາກນັ້ນເຂົ້າໄປໃນຕາຕະລາງ / ດັດຊະນີ. ທັງຫມົດນີ້ແມ່ນເຮັດເພື່ອສະຫນັບສະຫນູນ ACID ແລະການເບິ່ງເຫັນຂໍ້ມູນທີ່ຖືກຕ້ອງລະຫວ່າງ COMMIT'nutty ແລະ ROLLBACK'ການເຮັດທຸລະກໍາ null.

ແຕ່​ພວກ​ເຮົາ​ບໍ່​ຈໍາ​ເປັນ​ຕ້ອງ​ນີ້​! ພວກເຮົາມີຂະບວນການທັງຫມົດ ມັນປະສົບຜົນ ສຳ ເລັດຢ່າງສົມບູນຫຼືບໍ່.. ມັນບໍ່ສໍາຄັນວ່າຈະມີການເຮັດທຸລະກໍາລະຫວ່າງກາງຫຼາຍປານໃດ - ພວກເຮົາບໍ່ສົນໃຈ "ສືບຕໍ່ຂະບວນການຈາກກາງ", ໂດຍສະເພາະໃນເວລາທີ່ມັນບໍ່ຊັດເຈນວ່າມັນຢູ່ໃສ.

ເພື່ອເຮັດສິ່ງນີ້, ນັກພັດທະນາ PostgreSQL, ກັບຄືນໄປບ່ອນໃນຮຸ່ນ 9.1, ໄດ້ນໍາສະເຫນີເຊັ່ນ: ບໍ່ໄດ້ບັນທຶກຕາຕະລາງ:

ດ້ວຍຕົວຊີ້ບອກນີ້, ຕາຕະລາງຖືກສ້າງເປັນ unlogged. ຂໍ້​ມູນ​ທີ່​ຂຽນ​ໃສ່​ຕາ​ຕະ​ລາງ​ທີ່​ບໍ່​ຖືກ​ບັນ​ທຶກ​ບໍ່​ໄດ້​ຜ່ານ​ບັນ​ທຶກ​ການ​ຂຽນ​ລ່ວງ​ຫນ້າ (ເບິ່ງ​ບົດ​ທີ 29​)​, ເຮັດ​ໃຫ້​ຕາ​ຕະ​ລາງ​ດັ່ງ​ກ່າວ​ເປັນ​. ເຮັດວຽກໄວກວ່າປົກກະຕິ. ຢ່າງໃດກໍ່ຕາມ, ພວກເຂົາບໍ່ມີພູມຕ້ານທານກັບຄວາມລົ້ມເຫຼວ; ໃນກໍລະນີຂອງເຊີບເວີລົ້ມເຫລວຫຼືປິດສຸກເສີນ, ຕາຕະລາງທີ່ບໍ່ໄດ້ເຂົ້າສູ່ລະບົບ ຕັດອັດຕະໂນມັດ. ນອກຈາກນັ້ນ, ເນື້ອໃນຂອງຕາຕະລາງ unlogged ບໍ່ໄດ້ສຳເນົາ ເພື່ອຮັບໃຊ້ເຄື່ອງແມ່ຂ່າຍ. ດັດສະນີໃດນຶ່ງທີ່ສ້າງຂຶ້ນໃນຕາຕະລາງທີ່ບໍ່ໄດ້ເຂົ້າສູ່ລະບົບຈະຖືກຍົກເລີກໂດຍອັດຕະໂນມັດ.

ໃນສັ້ນ ມັນຈະໄວຂຶ້ນຫຼາຍ, ແຕ່ຖ້າເຄື່ອງແມ່ຂ່າຍຂອງຖານຂໍ້ມູນ "ຕົກ", ມັນຈະເປັນສິ່ງທີ່ບໍ່ພໍໃຈ. ແຕ່ມັນເກີດຂຶ້ນເລື້ອຍໆເທົ່າໃດ, ແລະຂະບວນການ ETL ຂອງທ່ານຮູ້ວິທີທີ່ຈະແກ້ໄຂຢ່າງຖືກຕ້ອງ "ຈາກກາງ" ຫຼັງຈາກ "ຟື້ນຟູ" ຖານຂໍ້ມູນບໍ?

ຖ້າບໍ່, ແລະກໍລະນີຂ້າງເທິງແມ່ນຄ້າຍຄືກັບຂອງທ່ານ, ໃຫ້ໃຊ້ UNLOGGEDແຕ່ບໍ່ເຄີຍ ຢ່າເປີດໃຊ້ຄຸນສົມບັດນີ້ຢູ່ໃນຕາຕະລາງທີ່ແທ້ຈິງ, ຂໍ້ມູນຈາກທີ່ຮັກແພງກັບທ່ານ.

1.3. ON COMMIT { Delete ROWS | ຫຼຸດ}

ການກໍ່ສ້າງນີ້ຊ່ວຍໃຫ້ທ່ານສາມາດກໍານົດພຶດຕິກໍາອັດຕະໂນມັດໃນເວລາທີ່ການເຮັດທຸລະກໍາສໍາເລັດໃນເວລາສ້າງຕາຕະລາງ.

ກ່ຽວກັບ ON COMMIT DROP ຂ້າພະເຈົ້າໄດ້ຂຽນແລ້ວຂ້າງເທິງ, ມັນສ້າງ DROP TABLE, ແຕ່ມີ ON COMMIT DELETE ROWS ສະຖານະການແມ່ນຫນ້າສົນໃຈຫຼາຍ - ມັນຖືກສ້າງຂຶ້ນຢູ່ທີ່ນີ້ TRUNCATE TABLE.

ເນື່ອງຈາກວ່າໂຄງສ້າງພື້ນຖານທັງຫມົດສໍາລັບການເກັບຮັກສາຄໍາອະທິບາຍ meta ຂອງຕາຕະລາງຊົ່ວຄາວແມ່ນຄືກັນກັບຕາຕະລາງປົກກະຕິ, ຫຼັງຈາກນັ້ນ. ການສ້າງແລະການລຶບຕາຕະລາງຊົ່ວຄາວຢ່າງຕໍ່ເນື່ອງເຮັດໃຫ້ "ອາການບວມ" ຮ້າຍແຮງຂອງຕາຕະລາງລະບົບ pg_class, pg_attribute, pg_attrdef, pg_depend,…

ຕອນນີ້ຈິນຕະນາການວ່າທ່ານມີພະນັກງານເຊື່ອມຕໍ່ໂດຍກົງກັບຖານຂໍ້ມູນ, ເຊິ່ງເປີດການເຮັດທຸລະກໍາໃຫມ່ໃນທຸກໆວິນາທີ, ສ້າງ, ຕື່ມຂໍ້ມູນ, ຂະບວນການແລະລຶບຕາຕະລາງຊົ່ວຄາວ ... ຈະມີຂີ້ເຫຍື້ອຫຼາຍເກີນທີ່ສະສົມຢູ່ໃນຕາຕະລາງລະບົບ, ແລະ. ນີ້ຈະເຮັດໃຫ້ເກີດການຫ້າມລໍ້ພິເສດສໍາລັບແຕ່ລະການດໍາເນີນງານ.

ໂດຍທົ່ວໄປ, ຢ່າເຮັດສິ່ງນີ້! ໃນກໍລະນີດັ່ງກ່າວນີ້, ມັນມີປະສິດທິພາບຫຼາຍ CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ເອົາມັນອອກຈາກວົງຈອນການເຮັດທຸລະກໍາ - ຫຼັງຈາກນັ້ນໃນຕອນເລີ່ມຕົ້ນຂອງແຕ່ລະທຸລະກໍາໃຫມ່, ຕາຕະລາງແມ່ນແລ້ວ ຈະມີຢູ່ (ບັນທຶກການໂທ CREATE), ແຕ່ ຈະຫວ່າງເປົ່າ, ຂໍຂອບໃຈກັບ TRUNCATE (ພວກເຮົາຍັງບັນທຶກການໂທຂອງມັນ) ເມື່ອເຮັດທຸລະກໍາທີ່ຜ່ານມາ.

1.4. ມັກ...ລວມທັງ...

ຂ້າພະເຈົ້າໄດ້ກ່າວເຖິງໃນຕອນຕົ້ນວ່າຫນຶ່ງໃນກໍລະນີການນໍາໃຊ້ປົກກະຕິສໍາລັບຕາຕະລາງຊົ່ວຄາວແມ່ນການນໍາເຂົ້າປະເພດຕ່າງໆ - ແລະນັກພັດທະນາເມື່ອຍລ້າໆຄັດລອກບັນຊີລາຍຊື່ຂອງຕາຕະລາງເປົ້າຫມາຍເຂົ້າໃນການປະກາດຊົ່ວຄາວຂອງລາວ ...

ແຕ່ຄວາມຂີ້ຄ້ານແມ່ນເຄື່ອງຈັກຂອງຄວາມຄືບຫນ້າ! ນັ້ນຄືເຫດຜົນ ສ້າງຕາຕະລາງໃຫມ່ "ອີງໃສ່ຕົວຢ່າງ" ມັນ​ສາ​ມາດ​ຈະ​ງ່າຍ​ດາຍ​ຫຼາຍ​:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

ນັບຕັ້ງແຕ່ຫຼັງຈາກນັ້ນທ່ານສາມາດສ້າງຂໍ້ມູນຈໍານວນຫລາຍໃນຕາຕະລາງນີ້, ການຊອກຫາຜ່ານມັນຈະບໍ່ໄວ. ແຕ່ມີການແກ້ໄຂແບບດັ້ງເດີມຕໍ່ເລື່ອງນີ້ - ດັດສະນີ! ແລະ, ແມ່ນແລ້ວ, ຕາຕະລາງຊົ່ວຄາວຍັງສາມາດມີດັດສະນີ.

ນັບຕັ້ງແຕ່, ເລື້ອຍໆ, ດັດຊະນີທີ່ກໍານົດໄວ້ກົງກັນກັບດັດສະນີຂອງຕາຕະລາງເປົ້າຫມາຍ, ທ່ານພຽງແຕ່ສາມາດຂຽນ LIKE target_table INCLUDING INDEXES.

ຖ້າທ່ານຕ້ອງການ DEFAULT-values ​​​​(ຕົວຢ່າງ, ເພື່ອຕື່ມຂໍ້ມູນໃສ່ໃນຄ່າຕົ້ນຕໍ), ທ່ານສາມາດນໍາໃຊ້ LIKE target_table INCLUDING DEFAULTS. ຫຼື​ງ່າຍ​ດາຍ - LIKE target_table INCLUDING ALL - ສຳເນົາຄ່າເລີ່ມຕົ້ນ, ດັດຊະນີ, ຂໍ້ຈຳກັດ,...

ແຕ່ໃນທີ່ນີ້ທ່ານຈໍາເປັນຕ້ອງເຂົ້າໃຈວ່າຖ້າທ່ານສ້າງ ນໍາເຂົ້າຕາຕະລາງທັນທີທີ່ມີດັດສະນີ, ຫຼັງຈາກນັ້ນຂໍ້ມູນຈະໃຊ້ເວລາດົນກວ່າທີ່ຈະໂຫລດກ່ວາຖ້າຫາກວ່າທ່ານທໍາອິດຕື່ມຂໍ້ມູນໃສ່ທັງຫມົດ, ແລະພຽງແຕ່ຫຼັງຈາກນັ້ນມ້ວນເຖິງດັດຊະນີ - ເບິ່ງວ່າມັນເຮັດແນວໃດມັນເປັນຕົວຢ່າງ. pg_dump.

ໂດຍທົ່ວໄປ RTFM!

2. ວິທີການຂຽນ?

ໃຫ້ຂ້າພະເຈົ້າພຽງແຕ່ເວົ້າວ່າ - ໃຊ້ມັນ COPY- flow ແທນ "ຊອງ​" INSERT, ການເລັ່ງເວລາ. ເຈົ້າສາມາດໂດຍກົງຈາກໄຟລ໌ທີ່ສ້າງຂຶ້ນກ່ອນ.

3. ວິທີການປຸງແຕ່ງ?

ດັ່ງນັ້ນ, ໃຫ້ intro ຂອງພວກເຮົາເບິ່ງບາງສິ່ງບາງຢ່າງເຊັ່ນນີ້:

• ທ່ານມີຕາຕະລາງທີ່ມີຂໍ້ມູນລູກຄ້າເກັບຮັກສາໄວ້ໃນຖານຂໍ້ມູນຂອງທ່ານ 1M ບັນທຶກ
• ທຸກໆມື້ລູກຄ້າສົ່ງອັນໃຫມ່ໃຫ້ທ່ານ "ຮູບພາບ" ເຕັມ
• ຈາກປະສົບການທີ່ທ່ານຮູ້ວ່າເປັນບາງຄັ້ງຄາວ ບໍ່ເກີນ 10K ບັນທຶກການປ່ຽນແປງ

ຕົວຢ່າງຄລາສສິກຂອງສະຖານະການດັ່ງກ່າວແມ່ນ ຖານ KLADR — ມີ​ທີ່​ຢູ່​ຈໍາ​ນວນ​ຫຼາຍ​ໃນ​ຈໍາ​ນວນ​ທັງ​ຫມົດ​, ແຕ່​ວ່າ​ໃນ​ການ​ອັບ​ໂຫຼດ​ແຕ່​ລະ​ອາ​ທິດ​ມີ​ການ​ປ່ຽນ​ແປງ​ບໍ່​ຫຼາຍ​ປານ​ໃດ (ການ​ປ່ຽນ​ຊື່​ຂອງ​ການ​ຕັ້ງ​ຖິ່ນ​ຖານ​, ການ​ປະ​ສົມ​ຖະ​ຫນົນ​ຫົນ​ທາງ​, ຮູບ​ລັກ​ສະ​ນະ​ຂອງ​ເຮືອນ​ໃຫມ່​) ເຖິງ​ແມ່ນ​ວ່າ​ໃນ​ລະ​ດັບ​ຊາດ​.

3.1. ຂັ້ນຕອນການຊິ້ງຂໍ້ມູນເຕັມຮູບແບບ

ເພື່ອຄວາມງ່າຍດາຍ, ໃຫ້ເວົ້າວ່າທ່ານບໍ່ຈໍາເປັນຕ້ອງປັບໂຄງສ້າງຂໍ້ມູນ - ພຽງແຕ່ເອົາຕາຕະລາງເຂົ້າໄປໃນຮູບແບບທີ່ຕ້ອງການ, ນັ້ນແມ່ນ:

• ເອົາອອກ ທຸກຢ່າງທີ່ບໍ່ມີຢູ່ແລ້ວ
• ອັບເດດ ທຸກ​ສິ່ງ​ທຸກ​ຢ່າງ​ທີ່​ມີ​ຢູ່​ແລ້ວ​ແລະ​ຈໍາ​ເປັນ​ຕ້ອງ​ໄດ້​ຮັບ​ການ​ປັບ​ປຸງ​
• ໃສ່ ທຸກ​ສິ່ງ​ທຸກ​ຢ່າງ​ທີ່​ຍັງ​ບໍ່​ທັນ​ໄດ້​ເກີດ​ຂຶ້ນ​

ເປັນຫຍັງການປະຕິບັດງານຄວນເຮັດໃນຄໍາສັ່ງນີ້? ເນື່ອງຈາກວ່ານີ້ແມ່ນວິທີການຂະຫນາດຕາຕະລາງຈະຂະຫຍາຍຕົວຫນ້ອຍທີ່ສຸດ (ຈື່ MVCC!).

ລຶບອອກຈາກ dst

ບໍ່, ແນ່ນອນທ່ານສາມາດໄປໄດ້ໂດຍການດໍາເນີນການພຽງແຕ່ສອງຢ່າງ:

• ເອົາອອກ (DELETE) ທຸກ​ສິ່ງ​ທຸກ​ຢ່າງ​ໂດຍ​ທົ່ວ​ໄປ​
• ໃສ່ ທັງ​ຫມົດ​ຈາກ​ຮູບ​ພາບ​ໃຫມ່​

ແຕ່ໃນເວລາດຽວກັນ, ຂອບໃຈ MVCC, ຂະຫນາດຂອງຕາຕະລາງຈະເພີ່ມຂຶ້ນແນ່ນອນສອງເທົ່າ! ການໄດ້ຮັບ +1M ຮູບພາບຂອງບັນທຶກໃນຕາຕະລາງເນື່ອງຈາກການອັບເດດ 10K ແມ່ນຊ້ໍາຊ້ອນດັ່ງນັ້ນ ...

ຕັດ dst

ຜູ້ພັດທະນາທີ່ມີປະສົບການຫຼາຍຮູ້ວ່າແທັບເລັດທັງຫມົດສາມາດເຮັດຄວາມສະອາດໄດ້ລາຄາຖືກ:

• ຈະແຈ້ງ (TRUNCATE) ຕາ​ຕະ​ລາງ​ທັງ​ຫມົດ​
• ໃສ່ ທັງ​ຫມົດ​ຈາກ​ຮູບ​ພາບ​ໃຫມ່​

ວິ​ທີ​ການ​ແມ່ນ​ປະ​ສິດ​ທິ​ຜົນ​, ບາງຄັ້ງກໍ່ໃຊ້ໄດ້ຫຼາຍ, ແຕ່ມີບັນຫາ ... ພວກເຮົາຈະເພີ່ມບັນທຶກ 1M ເປັນເວລາດົນ, ດັ່ງນັ້ນພວກເຮົາບໍ່ສາມາດທີ່ຈະປ່ອຍໃຫ້ຕາຕະລາງຫວ່າງເປົ່າສໍາລັບເວລານີ້ (ເຊັ່ນດຽວກັບຈະເກີດຂຶ້ນໂດຍບໍ່ມີການຫໍ່ມັນຢູ່ໃນທຸລະກໍາດຽວ).

ຫມາຍ​ຄວາມ​ວ່າ:

• ພວກເຮົາກໍາລັງເລີ່ມຕົ້ນ ການເຮັດທຸລະກໍາທີ່ຍາວນານ
• TRUNCATE ບັງຄັບ ເຂົ້າເຖິງສະເພາະ- ສະກັດ
• ພວກເຮົາເຮັດການແຊກສໍາລັບເວລາດົນນານ, ແລະທຸກຄົນອື່ນໆໃນເວລານີ້ ບໍ່ສາມາດແມ້ແຕ່ SELECT

ບາງສິ່ງບາງຢ່າງບໍ່ດີ ...

ຕາຕະລາງການປ່ຽນແປງ... ແທນຊື່... / ວາງຕາຕະລາງ...

ທາງເລືອກແມ່ນການຕື່ມທຸກສິ່ງທຸກຢ່າງເຂົ້າໄປໃນຕາຕະລາງໃຫມ່ແຍກຕ່າງຫາກ, ແລະຫຼັງຈາກນັ້ນພຽງແຕ່ປ່ຽນຊື່ມັນແທນທີ່ເກົ່າ. ສອງ​ສາມ​ສິ່ງ​ເລັກ​ນ້ອຍ​ທີ່​ຫນ້າ​ກຽດ​:

• ຍັງຄືກັນ ເຂົ້າເຖິງສະເພາະ, ເຖິງແມ່ນວ່າໃຊ້ເວລາຫນ້ອຍລົງຢ່າງຫຼວງຫຼາຍ
• ແຜນ​ການ​ສອບ​ຖາມ​ທັງ​ຫມົດ / ສະ​ຖິ​ຕິ​ສໍາ​ລັບ​ຕາ​ຕະ​ລາງ​ນີ້​ແມ່ນ​ໄດ້​ຖືກ​ປັບ​ໃຫມ່​, ຈໍາເປັນຕ້ອງດໍາເນີນການ ANALYZE
• ກະແຈຕ່າງປະເທດທັງໝົດຖືກແຕກ (FK) ກັບຕາຕະລາງ

ມີແຜ່ນແພ WIP ຈາກ Simon Riggs ທີ່ແນະນໍາໃຫ້ເຮັດ ALTER-ການ​ດໍາ​ເນີນ​ງານ​ເພື່ອ​ທົດ​ແທນ​ຮ່າງ​ກາຍ​ຂອງ​ຕາ​ຕະ​ລາງ​ໃນ​ລະ​ດັບ​ໄຟລ​໌​, ໂດຍ​ບໍ່​ມີ​ການ​ສໍາ​ພັດ​ສະ​ຖິ​ຕິ​ແລະ FK​, ແຕ່​ບໍ່​ໄດ້​ເກັບ​ກໍາ​ກຸ່ມ​.

ລຶບ, ອັບເດດ, ແຊກ

ດັ່ງນັ້ນ, ພວກເຮົາຕົກລົງກ່ຽວກັບທາງເລືອກທີ່ບໍ່ສະກັດກັ້ນສາມການດໍາເນີນງານ. ເກືອບສາມ ... ເຮັດແນວໃດນີ້ປະສິດທິຜົນທີ່ສຸດ?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие

-- вставляем отсутствующие
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. ນໍາເຂົ້າຫຼັງການປຸງແຕ່ງ

ໃນ KLADR ດຽວກັນ, ບັນທຶກທີ່ປ່ຽນແປງທັງຫມົດຈະຕ້ອງດໍາເນີນການເພີ່ມເຕີມໂດຍຜ່ານການປຸງແຕ່ງຫລັງ - ປົກກະຕິ, ເນັ້ນໃສ່ຄໍາຫລັກ, ແລະຫຼຸດລົງໃນໂຄງສ້າງທີ່ກໍານົດໄວ້. ແຕ່​ເຈົ້າ​ຮູ້​ໄດ້​ແນວ​ໃດ - ສິ່ງທີ່ແນ່ນອນປ່ຽນແປງໂດຍບໍ່ມີການ complicating ລະຫັດ synchronization, ໂດຍສະເພາະໂດຍບໍ່ມີການສໍາຜັດກັບມັນທັງຫມົດ?

ຖ້າພຽງແຕ່ຂະບວນການຂອງທ່ານມີການເຂົ້າເຖິງການຂຽນໃນເວລາທີ່ synchronization, ຫຼັງຈາກນັ້ນທ່ານສາມາດນໍາໃຊ້ trigger ທີ່ຈະເກັບກໍາການປ່ຽນແປງທັງຫມົດສໍາລັບພວກເຮົາ:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ຕອນນີ້ພວກເຮົາສາມາດນຳໃຊ້ triggers ກ່ອນທີ່ຈະເລີ່ມ synchronization (ຫຼືເປີດໃຊ້ພວກມັນຜ່ານ ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

ແລະຫຼັງຈາກນັ້ນພວກເຮົາຄ່ອຍໆສະກັດການປ່ຽນແປງທັງຫມົດທີ່ພວກເຮົາຕ້ອງການຈາກຕາຕະລາງບັນທຶກແລະດໍາເນີນການຜ່ານຕົວຈັດການເພີ່ມເຕີມ.

3.3. ການນໍາເຂົ້າຊຸດເຊື່ອມຕໍ່

ຂ້າງເທິງພວກເຮົາໄດ້ພິຈາລະນາກໍລະນີເມື່ອໂຄງສ້າງຂໍ້ມູນຂອງແຫຼ່ງແລະປາຍທາງແມ່ນຄືກັນ. ແຕ່ຈະເປັນແນວໃດຖ້າການອັບໂຫລດຈາກລະບົບພາຍນອກມີຮູບແບບທີ່ແຕກຕ່າງຈາກໂຄງສ້າງການເກັບຮັກສາໃນຖານຂໍ້ມູນຂອງພວກເຮົາ?

ໃຫ້ເອົາເປັນຕົວຢ່າງການເກັບຮັກສາຂອງລູກຄ້າແລະບັນຊີຂອງພວກເຂົາ, ທາງເລືອກ "ຫຼາຍຕໍ່ຫນຶ່ງ" ແບບຄລາສສິກ:

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

ແຕ່ການດາວໂຫຼດຈາກແຫຼ່ງພາຍນອກມາໃຫ້ພວກເຮົາໃນຮູບແບບຂອງ "ທັງຫມົດໃນຫນຶ່ງ":

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

ແນ່ນອນ, ຂໍ້ມູນລູກຄ້າສາມາດຊ້ໍາກັນໃນສະບັບນີ້, ແລະບັນທຶກຕົ້ນຕໍແມ່ນ "ບັນຊີ":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

ສໍາລັບຮູບແບບ, ພວກເຮົາຈະໃສ່ຂໍ້ມູນການທົດສອບຂອງພວກເຮົາ, ແຕ່ຈື່ - COPY ມີປະສິດທິພາບຫຼາຍຂຶ້ນ!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

ທໍາອິດ, ໃຫ້ເນັ້ນໃສ່ "ການຕັດ" ເຫຼົ່ານັ້ນທີ່ "ຄວາມຈິງ" ຂອງພວກເຮົາອ້າງອີງ. ໃນກໍລະນີຂອງພວກເຮົາ, ໃບແຈ້ງຫນີ້ຫມາຍເຖິງລູກຄ້າ:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

ເພື່ອເຊື່ອມໂຍງບັນຊີກັບ ID ລູກຄ້າຢ່າງຖືກຕ້ອງ, ກ່ອນອື່ນພວກເຮົາຕ້ອງຊອກຫາ ຫຼືສ້າງຕົວລະບຸເຫຼົ່ານີ້. ໃຫ້ເພີ່ມຊ່ອງໃສ່ພາຍໃຕ້ພວກມັນ:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

ໃຫ້ໃຊ້ວິທີການ synchronization ຕາຕະລາງທີ່ອະທິບາຍຂ້າງເທິງດ້ວຍການດັດແກ້ເລັກນ້ອຍ - ພວກເຮົາຈະບໍ່ປັບປຸງຫຼືລຶບສິ່ງໃດໃນຕາຕະລາງເປົ້າຫມາຍ, ເພາະວ່າພວກເຮົານໍາເຂົ້າລູກຄ້າ "append-only":

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

ໃນຄວາມເປັນຈິງ, ທຸກສິ່ງທຸກຢ່າງແມ່ນຢູ່ໃນ invoice_import ຕອນນີ້ພວກເຮົາມີບ່ອນຕິດຕໍ່ທີ່ຕື່ມໃສ່ client_id, ທີ່ພວກເຮົາຈະໃສ່ໃບແຈ້ງຫນີ້.

ແຫຼ່ງຂໍ້ມູນ: www.habr.com