èšäºã®ç¿»èš³ã¯ã³ãŒã¹ã®åŠçåãã«ç¹å¥ã«çšæãããŸãã
PostgreSQL ãšæ¥ç¶åºæã®æžã蟌ã¿äžè²«æ§èšå®
Compose ã§ã¯å€ãã®ããŒã¿ããŒã¹ãæ±ã£ãŠããããããã®æ©èœãšæ¬ ç¹ã«ã€ããŠããã«è©³ããç¥ãæ©äŒãåŸãããŸãã æ°ããããŒã¿ããŒã¹ã®æ©èœãæããããã«ãªããšãç§ãã¡ãé·å¹Žäœ¿ã£ãŠããããæçããããŒã«ã«åæ§ã®æ©èœãããã°ã©ããªã«çŽ æŽãããã ãããšèããããšããããŸãã ç§ã PostgreSQL ã«æåŸ
ããŠããæ°æ©èœã® XNUMX ã€ã¯ãã¯ã©ã¹ã¿ãŒå
šäœã«ãããæ¥ç¶ããšã®æ§æå¯èœãªæžã蟌ã¿äžè²«æ§ã§ããã ãããŠãçµå±ã®ãšãããç§ãã¡ã¯ãã§ã«ãããæã£ãŠããã®ã§ãä»æ¥ã¯ãããã©ã®ããã«äœ¿çšã§ãããã«ã€ããŠã®æ
å ±ãå
±æããããšæããŸãã
ãªãå¿ èŠãªã®ã§ããïŒ
ã¯ã©ã¹ã¿ãŒãã©ã®ããã«åäœãããã¯ãã¢ããªã±ãŒã·ã§ã³ã«ãã£ãŠç°ãªããŸãã ããšãã°ãè«æ±æžæ¯æãã¢ããªãèããŠã¿ãŸãããã ã¯ã©ã¹ã¿ãŒå šäœã§ XNUMX% ã®äžè²«æ§ãå¿ èŠãšãªããããããŒã¿ããŒã¹ããã¹ãŠã®å€æŽãè¡ãããã®ãåŸ æ©ããããã«åæã³ããããæå¹ã«ããå¿ èŠããããŸãã ãã ããã¢ããªã±ãŒã·ã§ã³ãæ¥éã«æé·ããŠãããœãŒã·ã£ã« ãããã¯ãŒã¯ã®å Žåã¯ãXNUMX% ã®äžè²«æ§ãããé«éãªå¿çã奜ãã§ãããã ãããå®çŸããã«ã¯ãã¯ã©ã¹ã¿ãŒã§éåæã³ãããã䜿çšããŸãã
劥åç¹ãæºãã
ããŒã¿ã®äžè²«æ§ãšããã©ãŒãã³ã¹ã®éã§ãã¬ãŒããªããè¡ãå¿ èŠããããŸãã PostgreSQL ã¯ãããã©ã«ãæ§æãäºæž¬å¯èœã§ãããäºæãã¬äºæ ãçºçããªããããäžè²«æ§ããé ããããŸãã 次ã«ã劥åç¹ãèŠãŠã¿ãŸãããã
ãã¬ãŒããªã 1: ããã©ãŒãã³ã¹
PostgreSQL ã¯ã©ã¹ã¿ãŒã«äžè²«æ§ãå¿ èŠãªãå Žåã¯ãéåæã§å®è¡ã§ããŸãã æžã蟌ã¿ã¯ã¯ã©ã¹ã¿ãŒ ãªãŒããŒã«å¯ŸããŠè¡ãããæŽæ°ã¯æ°ããªç§åŸã«ãã®ã¬ããªã«ã«éä¿¡ãããŸãã PostgreSQL ã¯ã©ã¹ã¿ãŒã«äžè²«æ§ãå¿ èŠãªå Žåã¯ãåæããŠå®è¡ããå¿ èŠããããŸãã æžã蟌ã¿ã¯ã¯ã©ã¹ã¿ãŒ ãªãŒããŒã«å¯ŸããŠè¡ãããã¯ã©ã¹ã¿ãŒ ãªãŒããŒã¯æŽæ°ãã¬ããªã«ã«éä¿¡ããåã¬ããªã«ãæžã蟌ãã ããšã®ç¢ºèªãåŸ ã£ãŠãããæžã蟌ã¿ãéå§ããã¯ã©ã€ã¢ã³ãã«æåããæšã®ç¢ºèªãéä¿¡ããŸãã ãããã®ã¢ãããŒãã®å®è³ªçãªéãã¯ãéåææ¹åŒã§ã¯ XNUMX ã€ã®ãããã¯ãŒã¯ ããããå¿ èŠã§ããã®ã«å¯Ÿããåææ¹åŒã§ã¯ XNUMX ã€ã®ãããã¯ãŒã¯ ããããå¿ èŠã§ããããšã§ãã
ãã¬ãŒããªã 2: äžè²«æ§
ããã 1 ã€ã®ã¢ãããŒãã§ãªãŒããŒã倱æããå Žåã®çµæãç°ãªããŸãã äœæ¥ãéåæã§å®è¡ãããå Žåããã®ãããªãšã©ãŒãçºçããå Žåããã¹ãŠã®ã¬ã³ãŒããã¬ããªã«ã«ãã£ãŠã³ããããããããã§ã¯ãããŸããã ã©ãããã倱ãããŸããïŒ ã¢ããªã±ãŒã·ã§ã³èªäœãšã¬ããªã±ãŒã·ã§ã³ã®å¹çã«ãã£ãŠç°ãªããŸãã Compose ã¬ããªã±ãŒã·ã§ã³ã§ã¯ãã¬ããªã«å ã®æ å ±éããªãŒããŒããã 1 MB å°ãªãå Žåãã¬ããªã«ããªãŒããŒã«ãªãããšãã§ããªããªããŸããã€ãŸããéåææäœäžã«æ倧 XNUMX MB ã®ã¬ã³ãŒãã倱ãããå¯èœæ§ããããŸãã
ããã¯åæã¢ãŒãã§ã¯çºçããŸããã ãªãŒããŒã§é害ãçºçãããšããªãŒããŒã§ç¢ºèªãããæžã蟌ã¿ã¯ã¬ããªã«ã§ã確èªãããå¿ èŠãããããããã¹ãŠã®ã¬ããªã«ãæŽæ°ãããŸãã ãããäžè²«æ§ã§ãã
åæåäœã¯ãäžè²«æ§ãšããã©ãŒãã³ã¹ã®ãã¬ãŒããªãã«ãããŠäžè²«æ§ãæããã«æå©ãªèª²éã¢ããªã±ãŒã·ã§ã³ã§ã¯çã«ããªã£ãŠããŸãã ãã®ãããªã¢ããªã±ãŒã·ã§ã³ã«ãšã£ãŠæãéèŠãªã®ã¯ãæå¹ãªããŒã¿ã§ãã 次ã«ããªã¯ãšã¹ãã«ã§ããã ãæ©ãå¿çããŠãŠãŒã¶ãŒã®æ³šæãåŒãç¶ããããšãäž»ãªã¿ã¹ã¯ã§ãããœãŒã·ã£ã« ãããã¯ãŒã¯ã«ã€ããŠèããŠã¿ãŸãããã ãã®å Žåããããã¯ãŒã¯ ããããå°ãªããã³ãããã®åŸ ã¡æéãå°ãªãããã©ãŒãã³ã¹ãåªå ãããŸãã ãã ããèæ ®ããå¿ èŠãããã®ã¯ããã©ãŒãã³ã¹ãšäžè²«æ§ã®éã®ãã¬ãŒããªãã ãã§ã¯ãããŸããã
ãã¬ãŒããªã 3: ã¯ã©ãã·ã¥
é害æã«ã¯ã©ã¹ã¿ãŒãã©ã®ããã«åäœããããç解ããããšã¯éåžžã«éèŠã§ãã XNUMX ã€ä»¥äžã®ã¬ããªã«ã«é害ãçºçããç¶æ³ãèããŠã¿ãŸãããã ã³ããããéåæã§åŠçãããå ŽåããªãŒããŒã¯ã¬ããªã«ã倱ãããã®ãåŸ ããã«æ©èœãç¶ããŸããã€ãŸããæžã蟌ã¿ãåãå ¥ããŠåŠçããŸãã ã¬ããªã«ãã¯ã©ã¹ã¿ãŒã«æ»ããšããªãŒããŒã«è¿œãã€ããŸãã åæã¬ããªã±ãŒã·ã§ã³ã§ã¯ãã¬ããªã«ãå¿çããªãå ŽåããªãŒããŒã«ã¯éžæã®äœå°ããªããã¬ããªã«ãã¯ã©ã¹ã¿ãŒã«æ»ããæžã蟌ã¿ãåãå ¥ããŠã³ãããã§ããããã«ãªããŸã§ã³ããã確èªãåŸ ã¡ç¶ããŸãã
ãã©ã³ã¶ã¯ã·ã§ã³ããšã« XNUMX ã€ã®æ¥ç¶ã§ãã?
ãã¹ãŠã®ã¢ããªã±ãŒã·ã§ã³ã«ã¯ãäžè²«æ§ãšããã©ãŒãã³ã¹ã®ç°ãªãã¿ã€ãã®çµã¿åãããå¿ èŠã§ãã ãã¡ããããããå®å šã«äžè²«æ§ããããšç§ãã¡ãæ³åããŠããæéæ¯æãã¢ããªããã»ãšãã©äžæçãªãœãŒã·ã£ã« ãããã¯ãŒãã³ã° ã¢ããªã®å Žåã¯å¥ã§ãã ä»ã®ãã¹ãŠã®å Žåã§ã¯ãäžéšã®æäœã¯åæçã§ãªããã°ãªãããäžéšã®æäœã¯éåæçã§ãªããã°ãªããªãå ŽåããããŸãã ãã£ããã«éä¿¡ãããã¡ãã»ãŒãžãã³ãããããããŸã§ã·ã¹ãã ãåŸ æ©ãããããªãå ŽåããããŸãããåãã¢ããªã±ãŒã·ã§ã³ã§æ¯æããåŠçãããå Žåã¯åŸ æ©ããå¿ èŠããããŸãã
ãã¡ãããããããã¹ãŠã®æ±ºå®ã¯ã¢ããªã±ãŒã·ã§ã³éçºè ã«ãã£ãŠè¡ãããŸãã åã¢ãããŒãããã€äœ¿çšãããã«ã€ããŠé©åãªæ±ºå®ãäžãããšã¯ãã¯ã©ã¹ã¿ãŒãæ倧éã«æŽ»çšããã®ã«åœ¹ç«ã¡ãŸãã éçºè ãæ¥ç¶ãšãã©ã³ã¶ã¯ã·ã§ã³ã® SQL ã¬ãã«ã§ããããåãæ¿ããããšãã§ããããšãéèŠã§ãã
å®éã«ã³ã³ãããŒã«ã確ä¿ãã
ããã©ã«ãã§ã¯ãPostgreSQL ã¯äžè²«æ§ãæäŸããŸãã ããã¯ãµãŒããŒãã©ã¡ãŒã¿ã«ãã£ãŠå¶åŸ¡ãããŸã synchronous_commit
ã ããã©ã«ãã§ã¯ãã®äœçœ®ã«ãããŸã on
ã§ãããä»ã«ã XNUMX ã€ã®ãªãã·ã§ã³ããããŸãã local
, remote_write
ãŸã㯠off
.
ãã©ã¡ãŒã¿ãèšå®ããå Žå off
ããŒã«ã« ã·ã¹ãã äžã§ãã£ãŠãããã¹ãŠã®åæã³ããããåæ¢ãããŸãã local ãã©ã¡ãŒã¿ãŒã¯ããŒã«ã« ã·ã¹ãã ã®åæã¢ãŒããæå®ããŸãããã¬ããªã«ãžã®æžã蟌ã¿ã¯éåæã§å®è¡ãããŸãã Remote_write
ããã«è©³ããèšãã°ãã¬ããªã«ãžã®æžã蟌ã¿ã¯éåæã§è¡ãããŸãããã¬ããªã«ãæžã蟌ã¿ãåãå
¥ããŠããã£ã¹ã¯ã«æžã蟌ãã§ããªãå Žåã«ã¯ãæžã蟌ã¿ãè¿ãããŸãã
å©çšå¯èœãªéžæè¢ã®ç¯å²ãèæ
®ããŠè¡åãéžæããŸãã on
â ãããã¯åæé²é³ã§ããéžæããŸãã local
ããŒã«ã«ã®ã³ãããã¯åæãããŸãŸã«ããŠããããã¯ãŒã¯çµç±ã§éåæã³ããããå®è¡ããŸãã
ããŠããããèšå®ããæ¹æ³ãããã«èª¬æããŸããã次ã®ããã«èšå®ãããšæ³åããŠãã ããã synchronous_commit
в local
ãµãŒããŒçšã ãã©ã¡ãŒã¿ãå€æŽã§ããªããèããŸãã synchronous_commit
ãããŠããããå¯èœã§ããã ãã§ãªãããããè¡ãæ¹æ³ã XNUMX ã€ããããšãå€æããŸããã XNUMX ã€ç®ã¯ãæ¥ç¶ã®ã»ãã·ã§ã³ã次ã®ããã«èšå®ããããšã§ãã
SET SESSION synchronous_commit TO ON;
// Your writes go here
ã»ãã·ã§ã³å
ã§ã®åŸç¶ã®æžã蟌ã¿ã¯ãã¹ãŠãæ¥ç¶ãããã¯ã©ã€ã¢ã³ãã«è¯å®çãªçµæãè¿ãåã«ãã¬ããªã«ãžã®æžã蟌ã¿ã確èªããŸãã ãã¡ããèšå®ãå€æŽããªãéã synchronous_commit
ãŸãã äžéšãçç¥ããããšãã§ããŸã SESSION
ããã©ã«ãå€ã«ãªããããã³ãã³ãã«å«ããŸãã
XNUMX çªç®ã®æ¹æ³ã¯ãåäžãã©ã³ã¶ã¯ã·ã§ã³ã®åæã¬ããªã±ãŒã·ã§ã³ã確å®ã«ååŸãããå Žåã«é©ããŠããŸãã å€ãã® NoSQL äžä»£ã®ããŒã¿ããŒã¹ã«ã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®æŠå¿µãååšããŸããããPostgreSQL ã«ã¯ãã©ã³ã¶ã¯ã·ã§ã³ã®æŠå¿µãååšããŸãã ãã®å Žåããã©ã³ã¶ã¯ã·ã§ã³ãéå§ããŠããèšå®ããŸãã synchronous_commit
в on
ãã©ã³ã¶ã¯ã·ã§ã³ã®ãšã³ããªãå®è¡ããåã COMMIT
ä»»æã®ãã©ã¡ãŒã¿å€ã䜿çšããŠãã©ã³ã¶ã¯ã·ã§ã³ãã³ãããããŸã synchronous_commit
ãã ããä»ã®éçºè
ãæžã蟌ã¿ãéåæã§ã¯ãªãããšãç解ã§ããããã«ãå€æ°ãäºåã«èšå®ããããšãæåã§ãã
BEGIN;
SET LOCAL synchronous_commit TO ON;
// Your writes go here
COMMIT;
ããŒã¿ããŒã¹ãæ¥ç¶ãããã¯ã©ã€ã¢ã³ãã«è¯å®çãªå¿çãè¿ãåã«ããã¹ãŠã®ãã©ã³ã¶ã¯ã·ã§ã³ã®ã³ããããã¬ããªã«ã«æžã蟌ãŸããããšã確èªãããããã«ãªããŸãã
PostgreSQL ã®æ§æ
ãããŸã§ã¯ã次ã®ãã㪠PostgreSQL ã·ã¹ãã ãæ³åããŠããŸããã synchronous_commit
ã«ã€ã³ã¹ããŒã«ãããŠããŸã local
ã ããããµãŒããŒåŽã§çŸå®çã«ããã«ã¯ãXNUMX ã€ã®ãµãŒããŒæ§æãªãã·ã§ã³ãèšå®ããå¿
èŠããããŸãã ãã XNUMX ã€ã®ãã©ã¡ãŒã¿ synchronous_standby_names
ãã€ã«ãªã£ããæ¬é ãçºæ®ããã ãã synchronous_commit
å
¥ããŸã on
ã ã©ã®ã¬ããªã«ãåæã³ãããã«é©æ Œã§ãããã決å®ãã次ã®ããã«èšå®ããŸãã *
ããã¯ããã¹ãŠã®ã¬ããªã«ãé¢ä¿ããŠããããšãæå³ããŸãã ãããã®å€ã¯éåžžã次ã®ããã«èšå®ãããŸãã
synchronous_commit = local
synchronous_standby_names='*'
ãã©ã¡ãŒã¿ãèšå®ããããšã§ synchronous_commit
æå³ã« local
ã§ã¯ãããŒã«ã« ãã£ã¹ã¯ãåæãç¶æããã·ã¹ãã ãäœæããŸããããããã¯ãŒã¯ ã¬ããªã«ã®ã³ãããã¯ããã©ã«ãã§éåæã«ãªããŸãã ãã¡ãããäžã«ç€ºããããã«ããããã®ã³ããããåæããããšã«æ±ºããå Žåã¯å¥ã§ãã
éçºããã©ããŒããŠããå Žå
ããå°ãèšèã...
ã»ãã® XNUMX é±éåãªããPostgreSQL ãããã»ã©çŽ°ãã埮調æŽããã®ã¯äžå¯èœã ãšç§ã¯èšã£ãŠããã§ãããã ãã®ãšããCompose ãã©ãããã©ãŒã ããŒã ã®ã¡ã³ããŒã§ãã Kurt ãããã®ãããªæ©äŒãååšãããšäž»åŒµããŸããã 圌ã¯ç§ã®å察æèŠããªã ããPostgreSQL ã®ããã¥ã¡ã³ãã§æ¬¡ã®ããšãèŠã€ããŸããã
ãã®èšå®ã¯ãã€ã§ãå€æŽã§ããŸãã ãã©ã³ã¶ã¯ã·ã§ã³ã®åäœã¯ãã³ãããæã«æå¹ãªèšå®ã«ãã£ãŠæ±ºãŸããŸãã ãããã£ãŠãäžéšã®ãã©ã³ã¶ã¯ã·ã§ã³ã¯åæçã«ã³ãããããä»ã®ãã©ã³ã¶ã¯ã·ã§ã³ã¯éåæçã«ã³ãããããããšãå¯èœã§ããã䟿å©ã§ãã ããšãã°ãXNUMX ã€ã匷å¶ããã«ã¯ multistatement
ãã©ã¡ãŒã¿ã®ããã©ã«ãå€ãéã®å Žåã«éåæã§ã³ããããããã©ã³ã¶ã¯ã·ã§ã³ãèšå®ããŸãã SET LOCAL synchronous_commit TO OFF
ãã©ã³ã¶ã¯ã·ã§ã³äžã
æ§æãã¡ã€ã«ã«å¯Ÿãããã®å°ããªå€æŽã«ããããŠãŒã¶ãŒãäžè²«æ§ãšããã©ãŒãã³ã¹ãå¶åŸ¡ã§ããããã«ãªããŸããã
åºæïŒ habr.com