ç§ã®æèŠã§ã¯ã以åã®ãªãªãŒã¹ãšã¯ç°ãªããPostgreSQL 12 ã«ã¯ 12 ã€ã XNUMX ã€ã®é©æ°çãªæ©èœ (ããŒãã£ã·ã§ãã³ã°ãã¯ãšãªäžŠååŠçãªã©) ãå«ãŸããŠããŸããã ç§ã¯ãã€ãŠãPostgreSQL XNUMX ã®äž»ãªæ©èœã¯å®å®æ§ã®åäžã§ãããšåè«ãèšããŸããã ããžãã¹ã®éèŠãªããŒã¿ã管çãããšãã«å¿ èŠãªããšã¯ããã§ã¯ãªãã§ãããã?
ããããPostgreSQL 12 ã¯ããã§çµããã§ã¯ãããŸãããæ°æ©èœãšæ¹åã«ãããã¢ããªã±ãŒã·ã§ã³ã®ããã©ãŒãã³ã¹ãåäžããŸãã ããšã¯ã¢ããã°ã¬ãŒãããã ãã§ã!
(ããã§ãããã€ã³ããã¯ã¹ãåæ§ç¯ãããããããŸãããããã®ãªãªãŒã¹ã§ã¯ããããŸã§ã»ã©æããããŸããã)
PostgreSQL ãã¢ããã°ã¬ãŒãããŠãäžå¿
èŠãªæéããããã«ããã«å€§å¹
ãªæ¹åã享åã§ããã®ã¯çŽ æŽãããããšã§ãã æ°å¹Žåãç§ã¯ PostgreSQL 9.4 ãã PostgreSQL 10 ãžã®ã¢ããã°ã¬ãŒããã¬ãã¥ãŒããPostgreSQL 10 ã®ã¯ãšãªäžŠååŠçã®åäžã«ããã¢ããªã±ãŒã·ã§ã³ãã©ã®ããã«é«éåãããã確èªããŸããããããŠæãéèŠãªããšã¯ãã»ãšãã©äœãå¿
èŠãšãããªãã£ããšããããšã§ã (æ§æãã©ã¡ãŒã¿ãèšå®ããã ãã§ãã) max_parallel_workers
).
åæããŸããã¢ããã°ã¬ãŒãçŽåŸã«ã¢ããªã±ãŒã·ã§ã³ã®åäœãåäžãããšäŸ¿å©ã§ãã PostgreSQL ã«ã¯ãŠãŒã¶ãŒãå¢ããŠãããããç§ãã¡ã¯ãŠãŒã¶ãŒãæºè¶³ãããããã«éåžžã«åªåããŠããŸãã
ã§ã¯ãPostgreSQL 12 ãžã®ç°¡åãªã¢ããã°ã¬ãŒãã§ã©ã®ããã«æºè¶³ã§ããã®ã§ãããã? ä»ããæããŸãã
ã€ã³ããã¯ã¹äœæã®å€§å¹ ãªæ¹å
ã€ã³ããã¯ã¹ãäœæããªããã°ãããŒã¿ããŒã¹ã¯çºå±ããŸããã æ
å ±ãçŽ æ©ãèŠã€ããã«ã¯ä»ã«ã©ãããã°ããã§ãããã? PostgreSQL ã®åºæ¬çãªã€ã³ããã¯ã¹ ã·ã¹ãã ã¯æ¬¡ã®ããã«åŒã°ããŸãã
åçŽã«æŒç®åã䜿çšããŸã CREATE INDEX ON some_table (some_column)
ãPostgreSQL ã¯ãåžžã«å€ãæ¿å
¥ãæŽæ°ãåé€ããäžæ¹ã§ãã€ã³ããã¯ã¹ãææ°ã®ç¶æ
ã«ä¿ã€ããã«å€ãã®äœæ¥ãè¡ããŸãã ãŸãã§éæ³ã®ããã«ããã¹ãŠãèªåçã«æ©èœããŸãã
ããããPostgreSQL ã€ã³ããã¯ã¹ã«ã¯ XNUMX ã€åé¡ããããŸãã
PostgreSQL 12 ã§ã¯ãB ããªãŒ ã€ã³ããã¯ã¹ã®ããã©ãŒãã³ã¹ãå€§å¹ ã«åäžããTPC-C ãªã©ã®ãã³ãããŒã¯ã䜿çšããå®éšã§ã¯ã䜿çšãããã¹ããŒã¹ãå¹³å 40% åæžãããããšã瀺ãããŠããŸãã ã€ã³ããã¯ã¹ãã¯ããã«å°ãããããB ããªãŒ ã€ã³ããã¯ã¹ã®ç¶æ (ã€ãŸãæžã蟌ã¿æäœ) ã ãã§ãªããããŒã¿ã®ååŸã«ãè²»ããæéãççž®ãããŸããã
ããŒãã«ãã¢ã¯ãã£ãã«æŽæ°ããã¢ããªã±ãŒã·ã§ã³ - é垞㯠OLTP ã¢ããªã±ãŒã·ã§ã³ (
äžéšã®ã¢ããã°ã¬ãŒãæŠç¥ã§ã¯ããããã®å©ç¹ã掻çšããããã« B ããªãŒ ã€ã³ããã¯ã¹ãåæ§ç¯ããå¿
èŠããããŸã (äŸ:
PostgreSQL 12 ã§ã¯ãã€ã³ããã¯ã¹äœæã€ã³ãã©ã¹ãã©ã¯ãã£ã«ã¯ä»ã«ãæ¹è¯ç¹ããããŸãã éæ³ããã£ãããäžã€ã®ããš -
PostgreSQL 12 ã§ã¯ãã€ã³ããã¯ã¹æ§ç¯äžã« GiSTãGINãããã³ SP-GiST ã€ã³ããã¯ã¹ã«ãã£ãŠäœæããã WAL ã¬ã³ãŒãã®ãªãŒããŒããããåæžãããŸããã ããã«ãããããã€ãã®å ·äœçãªå©ç¹ãåŸãããŸããWAL ã¬ã³ãŒããå æãããã£ã¹ã¯é åãæžããçœå®³åŸ©æ§ããã€ã³ãã€ã³ã¿ã€ã ãªã«ããªæãªã©ã«ããŒã¿ãããéãåçãããŸãã ã¢ããªã±ãŒã·ã§ã³ã§ãã®ãããªã€ã³ããã¯ã¹ã䜿çšããå Žå (ããšãã°ãPostGIS ããŒã¹ã®å°ç空éã¢ããªã±ãŒã·ã§ã³ã§ã¯ GiST ã€ã³ããã¯ã¹ãé »ç¹ã«äœ¿çšãããŸã)ãããã¯ããŠãŒã¶ãŒåŽã§äœã®åªåãå¿ èŠãšããã«ãšã¯ã¹ããªãšã³ã¹ãå€§å¹ ã«åäžããããã XNUMX ã€ã®æ©èœã§ãã
ããŒãã£ã·ã§ãã³ã° - ãã倧ãããããè¯ããããéã
PostgreSQL 10ã®å°å
¥
PostgreSQL 12 ã§ã¯ãç¹ã«ããŒãã«å ã«æ°åã®ããŒãã£ã·ã§ã³ãããå ŽåãããŒãã£ã·ã§ãã³ã° ã·ã¹ãã ã®ããã©ãŒãã³ã¹ãå€§å¹ ã«åäžããŸããã ããšãã°ãã¯ãšãªãäœåãã®ããŒãã£ã·ã§ã³ãæã€ããŒãã«å ã®å°æ°ã®ããŒãã£ã·ã§ã³ã«ã®ã¿åœ±é¿ãäžããå Žåãã¯ãšãªã¯ã¯ããã«é«éã«å®è¡ãããŸãã ããã©ãŒãã³ã¹ãåäžããã®ã¯ããã®çš®ã®ã¯ãšãªã ãã§ã¯ãããŸããã ãŸããè€æ°ã®ããŒãã£ã·ã§ã³ãæã€ããŒãã«ã«å¯Ÿãã INSERT æäœãããã«é«éã§ãããã«ãæ°ã¥ãã§ãããã
ã䜿çšããŠããŒã¿ãèšé²ãã
ãããã®å©ç¹ã®ãããã§ãPostgreSQL ã§ã¯ããã«å€§ããªããŒã¿ ã»ãããä¿åããç°¡åã«ååŸã§ããããã«ãªããŸãã ãããŠããªãã®åŽã«ã¯äœã®åªåããããŸããã æç³»åããŒã¿ã®èšé²ãªã©ãã¢ããªã±ãŒã·ã§ã³ã«å€ãã®ããŒãã£ã·ã§ã³ãããå Žåãç°¡åãªã¢ããã°ã¬ãŒãã§ããã©ãŒãã³ã¹ãå€§å¹ ã«åäžããŸãã
ããã¯æ£ç¢ºã«ã¯ãã¢ããã°ã¬ãŒãããŠã楜ãã¿ãã ããããšããæ¹åã§ã¯ãããŸããããPostgreSQL 12 ã§ã¯ããŒãã£ã·ã§ã³åãããããŒãã«ãåç §ããå€éšããŒãäœæã§ãããããããŒãã£ã·ã§ã³åãå¿«é©ã«è¡ãããšãã§ããŸãã
WITH ã¯ãšãªãå€§å¹ ã«æ¹åãããŸãã
æ
SQL ã®åå¿è 㯠CTE ã奜ãã§äœ¿çšããããšããããããŸãããç¹å®ã®æ¹æ³ã§ CTE ãäœæãããšãåœä»€åããã°ã©ã ãäœæããŠããããã«æããããŸãã å人çã«ã¯ããããã®ã¯ãšãªãæžãæããŠåé¿ããã®ã奜ãã§ãã без CTE ãé«ããçç£æ§ãåäžãããŸãã ä»ã§ã¯ãã¹ãŠãç°ãªããŸãã
PostgreSQL 12 ã§ã¯ãç¹å®ã®ã¿ã€ãã® CTE ãå¯äœçšãªãã§ã€ã³ã©ã€ã³åã§ããŸã (SELECT
)ããªã¯ãšã¹ãã®çµããè¿ã㧠XNUMX åã ã䜿çšãããŸãã æžãçŽãã CTE ã¯ãšãªã远跡ãããšãã»ãšãã©ããã®ã«ããŽãªã«åé¡ãããŸãã ããã«ãããéçºè
ã¯æ確ãªã³ãŒããäœæããé«éã«å®è¡ã§ããããã«ãªããŸãã
ããã«ãPostgreSQL 12 ã¯ãäœãããªããŠã SQL å®è¡èªäœãæé©åããŸãã ããããä»ã¯ãã®ãããªã¯ãšãªãæé©åããå¿ èŠã¯ãªãã§ãããããPostgreSQL ãã¯ãšãªã®æé©åã«åãçµã¿ç¶ããŠããããšã¯çŽ æŽãããããšã§ãã
ãžã£ã¹ãã€ã³ã¿ã€ã (JIT) - çŸåšã¯ããã©ã«ãã§ã
PostgreSQL 12 ã·ã¹ãã ã®ãµããŒãä»ã
PostgreSQL 12 ã§ã¯ JIT ãããã©ã«ãã§æå¹ã«ãªã£ãŠãããããããã©ãŒãã³ã¹ã¯èªç¶ã«åäžããŸãããJIT ãå°å ¥ããã PostgreSQL 11 ã§ã¢ããªã±ãŒã·ã§ã³ããã¹ãããŠãã¯ãšãªã®ããã©ãŒãã³ã¹ã枬å®ãã調æŽãå¿ èŠãã©ããã確èªããããšããå§ãããŸãã
PostgreSQL 12 ã®æ®ãã®æ°æ©èœã«ã€ããŠã¯ã©ãã§ãã?
PostgreSQL 12 ã«ã¯ãæšæº SQL/JSON ã«ãŒãåŒã䜿çšã㊠JSON ããŒã¿ãæ€æ»ããæ©èœããããã©ã¡ãŒã¿ãŒã䜿çšããå€èŠçŽ èªèšŒãŸã§ãåªããæ°æ©èœãå€æ°ãããŸãã clientcert=verify-full
ãã³ã©ã ãªã©ãäœæããŸããã å¥èšäºã«ããã®ã«ååã§ãã
PostgreSQL 10 ãšåæ§ã«ãPostgreSQL 12 ã¯ã¢ããã°ã¬ãŒãçŽåŸããå šäœçãªããã©ãŒãã³ã¹ãåäžããŸãã ãã¡ãããç¬èªã®ãã¹ãèšå®ããããšãã§ããŸããPostgreSQL 10 ã§è¡ã£ãããã«ãæ¹åãæå¹ã«ããåã«ãæ¬çªã·ã¹ãã ã§åæ§ã®æ¡ä»¶ã§ã¢ããªã±ãŒã·ã§ã³ããã¹ãããŠãã ãããPostgreSQL 12 ããã§ã«äºæ³ãããå®å®ããŠãããšããŠãããã¹ããæ ããªãã§ãã ãããã¢ããªã±ãŒã·ã§ã³ãéçšç°å¢ã«ãªãªãŒã¹ããåã«ã培åºçã«ãã¹ãããŸãã
åºæïŒ habr.com