ื“ื™ ื’ืขื–ื•ื ื˜ ืคื•ืŸ ื™ื ื“ืขืงืกื™ื– ืื™ืŸ PostgreSQL ื“ื•ืจืš ื“ื™ ืื•ื™ื’ืŸ ืคื•ืŸ ืึท Java ื“ืขื•ื•ืขืœืึธืคึผืขืจ

ื”ื™

ืžื™ื™ึทืŸ ื ืึธืžืขืŸ ืื™ื– ื•ื•ืึทื ื™ืึท ืื•ืŸ ืื™ืš ื‘ื™ืŸ ืึท Java ื“ืขื•ื•ืขืœืึธืคึผืขืจ. ืขืก ื›ืึทืคึผืึทื ื– ืึทื– ืื™ืš ืึทืจื‘ืขื˜ ืึท ืคึผืœืึทืฅ ืžื™ื˜ PostgreSQL - ื‘ืึทืฉื˜ืขื˜ื™ืงืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก, ืึธืคึผื˜ื™ืžื™ื–ื™ื ื’ ื“ื™ ืกื˜ืจื•ืงื˜ื•ืจ, ืคืึธืจืฉื˜ืขืœื•ื ื’ ืื•ืŸ ืคึผืœื™ื™ื™ื ื’ ืึท ื‘ื™ืกืœ ื“ื‘ืึท ืื•ื™ืฃ ื“ื™ ื•ื•ื™ืงืขื ื“ื–.

ืœืขืฆื˜ื ืก ืื™ืš ื”ืึธื‘ืŸ ื˜ื™ื™ื“ื™ื“ ืขื˜ืœืขื›ืข ื“ืึทื˜ืึทื‘ื™ื™ืกื™ื– ืื™ืŸ ืื•ื ื“ื–ืขืจ ืžื™ืงืจืึธืกืขืจื•ื•ื™ืกืขืก ืื•ืŸ ื’ืขืฉืจื™ื‘ืŸ ืึท ื–'ืื‘ื ื‘ื™ื‘ืœื™ืึธื˜ืขืง ืคึผื’-ืื™ื ื“ืขืงืก-ื’ืขื–ื•ื ื˜, ื•ื•ืึธืก ืžืื›ื˜ ื“ืขื ืึทืจื‘ืขื˜ ื’ืจื™ื ื’ืขืจ, ืกืึทื•ื•ืขืก ืžื™ืจ ืฆื™ื™ื˜ ืื•ืŸ ื”ืขืœืคึผืก ืžื™ืจ ื•ื™ืกืžื™ื™ื“ืŸ ืขื˜ืœืขื›ืข ืคึผืจืึธืกื˜ ืžื™ืกื˜ื™ื™ืงืก ื’ืขืžืื›ื˜ ื“ื•ืจืš ื“ืขื•ื•ืขืœืึธืคึผืขืจืก. ืขืก ืื™ื– ื“ื™ ื‘ื™ื‘ืœื™ืึธื˜ืขืง ื•ื•ืึธืก ืžื™ืจ ื•ื•ืขืœืŸ ืจืขื“ืŸ ื•ื•ืขื’ืŸ ื”ื™ื™ึทื ื˜.

ื“ื™ ื’ืขื–ื•ื ื˜ ืคื•ืŸ ื™ื ื“ืขืงืกื™ื– ืื™ืŸ PostgreSQL ื“ื•ืจืš ื“ื™ ืื•ื™ื’ืŸ ืคื•ืŸ ืึท Java ื“ืขื•ื•ืขืœืึธืคึผืขืจ

ืึธืคึผืœื™ื™ืงืขื ื•ื ื’

ื“ื™ ื”ื•ื™ืคึผื˜ ื•ื•ืขืจืกื™ืข ืคื•ืŸ โ€‹โ€‹PostgreSQL ืžื™ื˜ ื•ื•ืึธืก ืื™ืš ืึทืจื‘ืขื˜ ืื™ื– 10. ืึทืœืข ื“ื™ SQL ืงื•ื•ื™ืจื™ื– ืื™ืš ื ื•ืฆืŸ ื–ืขื ืขืŸ ืื•ื™ืš ื˜ืขืกื˜ืขื“ ืื•ื™ืฃ ื•ื•ืขืจืกื™ืข 11. ื“ื™ ืžื™ื ื™ืžื•ื ื’ืขืฉื˜ื™ืฆื˜ ื•ื•ืขืจืกื™ืข ืื™ื– 9.6.

ืคึผืจืขื”ื™ืกื˜ืึธืจื™

ืขืก ืึทืœืข ืื ื’ืขื”ื•ื™ื‘ืŸ ื›ึผืžืขื˜ ืึท ื™ืึธืจ ืฆื•ืจื™ืง ืžื™ื˜ ืึท ืกื™ื˜ื•ืึทืฆื™ืข ื•ื•ืึธืก ืื™ื– ื’ืขื•ื•ืขืŸ ืžืึธื“ื ืข ืคึฟืึทืจ ืžื™ืจ: ื“ื™ ืงืึทืžืคึผืขื˜ื™ื˜ื™ื•ื• ืฉืึทืคื•ื ื’ ืคื•ืŸ ืึทืŸ ืื™ื ื“ืขืงืก ืื•ื™ืก ืคื•ืŸ ื“ื™ ื‘ืœื•ื™ ื’ืขืขื ื“ื™ืงื˜ ืžื™ื˜ ืึท ื˜ืขื•ืช. ื“ืขืจ ืื™ื ื“ืขืงืก ืืœื™ื™ืŸ ืื™ื–, ื•ื•ื™ ื’ืขื•ื•ืขื ืœื™ืš, ืคืืจื‘ืœื™ื‘ืŸ ืื™ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืื™ืŸ ืืŸ ืื•ืžื’ื™ืœื˜ื™ืงื™ื™ื˜. ืงืœืึธืฅ ืึทื ืึทืœื™ืกื™ืก ื’ืขื•ื•ื™ื–ืŸ ืึท ื“ื•ื—ืง temp_file_limit. ืื•ืŸ ืžื™ืจ ื’ืฒืขืŸ ืึทืฐืขืง... ืึทืจืฒึทืŸ ื˜ื™ืคึฟืขืจ, ื”ืึธื‘ ืื™ืš ืึทื ื˜ื“ืขืงื˜ ืึท ื’ืึทื ืฆืŸ ื‘ื™ื ื˜ืœ ืคึผืจืึธื‘ืœืขืžืขืŸ ืื™ืŸ ื“ืขืจ ื“ืึทื˜ืึทื‘ืึทืกืข ืงืึธื ืคึฟื™ื’ื•ืจืึทืฆื™ืข ืื•ืŸ, ืึทืจืฑืคึฟื’ืจืฒึทื ื“ื ื“ื™ืง ื“ื™ ืึทืจื‘ืœ, ืึธื ื’ืขื”ื•ื™ื‘ืŸ ืคึฟืึทืจืจื™ื›ื˜ืŸ ืžื™ื˜ ืึท ืคึฟื™ื ืงืœ ืื™ืŸ ื“ื™ ืืฑื’ืŸ.

ืคึผืจืึธื‘ืœืขื ืื™ื™ื ืขืจ - ืคืขืœื™ืงื™ื™ึทื˜ ืงืึทื ืคื™ื’ื™ืขืจื™ื™ืฉืึทืŸ

ืžื™ืกื˜ืึธืžืข ืึทืœืขืžืขืŸ ืื™ื– ืฉื™ื™ืŸ ืžื™ื“ ืคื•ืŸ ื“ื™ ืžืขื˜ืึทืคืึธืจ ื•ื•ืขื’ืŸ Postgres, ื•ื•ืึธืก ืงืขื ืขืŸ ื–ื™ื™ืŸ ืœื•ื™ืคืŸ ืื•ื™ืฃ ืึท ืงืึทื•ื•ืข ืคืึทื‘ืจื™ืงืึทื ื˜, ืึธื‘ืขืจ ... ื“ื™ ืคืขืœื™ืงื™ื™ึทื˜ ืงืึทื ืคื™ื’ื™ืขืจื™ื™ืฉืึทืŸ ื˜ืึทืงืข ืจื™ื™ื–ืึทื– ืึท ื ื•ืžืขืจ ืคื•ืŸ ืคืจืื’ืขืก. ืื™ืŸ ืึท ืžื™ื ื™ืžื•ื, ืขืก ืื™ื– ื•ื•ืขืจื˜ ืฆื• ื‘ืึทืฆืึธืœืŸ ื•ืคืžืขืจืงื–ืึทืžืงื™ื™ึทื˜ maintenance_work_mem, temp_file_limit, statement_timeout ะธ lock_timeout.

ืื™ืŸ ืื•ื ื“ื–ืขืจ ืคืึทืœ maintenance_work_mem ืื™ื– ื’ืขื•ื•ืขืŸ ื“ื™ ืคืขืœื™ืงื™ื™ึทื˜ 64 ืžื‘, ืื•ืŸ temp_file_limit ืขืคึผืขืก ืึทืจื•ื 2 ื’ื™ื’ืื‘ื™ื™ื˜ - ืžื™ืจ ืคืฉื•ื˜ ื”ืึธื‘ืŸ ื ื™ืฉื˜ ื’ืขื ื•ื’ ื–ื›ึผืจื•ืŸ ืฆื• ืฉืึทืคึฟืŸ ืึทืŸ ืื™ื ื“ืขืงืก ืื•ื™ืฃ ืึท ื’ืจื•ื™ืก ื˜ื™ืฉ.

ื“ืขืจื™ื‘ืขืจ, ืื™ืŸ ืคึผื’-ืื™ื ื“ืขืงืก-ื’ืขื–ื•ื ื˜ ืื™ืš ื”ืึธื‘ ื’ืขื–ืืžืœื˜ ืึท ืกืขืจื™ืข ืฉืœื™ืกืœ, ืื™ืŸ ืžื™ื™ืŸ ืžื™ื™ื ื•ื ื’, ื“ื™ ืคึผืึทืจืึทืžืขื˜ืขืจืก ื•ื•ืึธืก ื–ืึธืœ ื–ื™ื™ืŸ ืงืึทื ืคื™ื’ื™ืขืจื“ ืคึฟืึทืจ ื™ืขื“ืขืจ ื“ืึทื˜ืึทื‘ื™ื™ืก.

ืคึผืจืึธื‘ืœืขื ืฆื•ื•ื™ื™ - ื“ื•ืคึผืœื™ืงืึทื˜ ื™ื ื“ืขืงืกื™ื–

ืื•ื ื“ื–ืขืจ ื“ืึทื˜ืึทื‘ื™ื™ืกื™ื– ืœืขื‘ืŸ ืื•ื™ืฃ SSD ื“ืจื™ื™ื•ื•ื–, ืื•ืŸ ืžื™ืจ ื ื•ืฆืŸ HA-ืงืึทื ืคื™ื’ื™ืขืจื™ื™ืฉืึทืŸ ืžื™ื˜ ืงื™ื™ืคืœ ื“ืึทื˜ืŸ ืกืขื ื˜ืขืจืก, ื‘ืขืœ ื‘ืึทืœืขื‘ืึธืก ืื•ืŸ n- ื ื•ืžืขืจ ืคื•ืŸ ืจืขืคึผืœืึทืงืึทื–. ื“ื™ืกืง ืคึผืœืึทืฅ ืื™ื– ืึท ื–ื™ื™ืขืจ ื•ื•ืขืจื˜ืคื•ืœ ืžื™ื˜ืœ ืคึฟืึทืจ ืื•ื ื“ื–; ืขืก ืื™ื– ื ื™ื˜ ื•ื•ื™ื™ื ื™ืงืขืจ ื•ื•ื™ื›ื˜ื™ืง ื•ื•ื™ ืคืึธืจืฉื˜ืขืœื•ื ื’ ืื•ืŸ ืงืคึผื• ืงืึทื ืกืึทืžืฉืึทืŸ. ื“ืขืจื™ื‘ืขืจ, ืคื•ืŸ ืื™ื™ืŸ ื–ื™ื™ื˜, ืžื™ืจ ื“ืึทืจืคึฟืŸ ื™ื ื“ืขืงืกื™ื– ืคึฟืึทืจ ืฉื ืขืœ ืœื™ื™ืขื ืขืŸ, ืื•ืŸ ืคื•ืŸ ื“ื™ ืื ื“ืขืจืข ื”ืึทื ื˜, ืžื™ืจ ื˜ืึธืŸ ื ื™ืฉื˜ ื•ื•ืขืœืŸ ืฆื• ื–ืขืŸ ื•ืžื ื™ื™ื˜ื™ืง ื™ื ื“ืขืงืกื™ื– ืื™ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก, ื•ื•ื™ื™ึทืœ ื–ื™ื™ ืขืกืŸ ืคึผืœืึทืฅ ืื•ืŸ ืคึผืึทืžืขืœืขืš ื“ื™ ืึทืคึผื“ื™ื™ื˜ื™ื ื’ ืคื•ืŸ ื“ืึทื˜ืŸ.

ืื•ืŸ ืื™ืฆื˜, ื•ื•ื™ื™ืœ ื’ืขื–ื•ื ื˜ ืึทืœืฅ ืคืึทืจืงืจื™ืคึผืœื˜ ื™ื ื“ืขืงืกื™ื– ืื•ืŸ ื•ื•ื™ื™ืœ ื’ืขื ื•ื’ ื’ืขื–ืขืŸ ื‘ืืจื™ื›ื˜ืŸ ืคื•ืŸ ืึธืœืขื’ ื‘ืึทืจื˜ื•ื ืึธื•ื•, ืื™ืš ื‘ืึทืฉืœืึธืกืŸ ืฆื• ืึธืจื’ืึทื ื™ื–ื™ืจืŸ ืึท "ื’ืจื•ื™ืก" ืจื™ื™ื ื™ืงื•ื ื’. ืขืก ืคืืจืงืขืจื˜ ืื•ื™ืก ืึทื– ื“ืขื•ื•ืขืœืึธืคึผืขืจืก ื˜ืึธืŸ ื ื™ื˜ ื•ื•ื™ ืฆื• ืœื™ื™ืขื ืขืŸ ื“ื™ื™ื˜ืึทื‘ื™ื™ืก ื“ืึทืงื™ื•ืžืขื ื˜ื™ื™ืฉืึทืŸ. ื–ื™ื™ ื˜ืึธืŸ ื ื™ื˜ ื•ื•ื™ ืขืก ื–ื™ื™ืขืจ ืคื™ืœ. ื•ื•ื™ื™ึทืœ ืคื•ืŸ ื“ืขื, ืฆื•ื•ื™ื™ ื˜ื™ืคึผื™ืฉ ืขืจืจืึธืจืก ืื•ื™ืคืฉื˜ื™ื™ืŸ - ืึท ืžืึทื ื™ื•ืึทืœื™ ื‘ืืฉืืคืŸ ืื™ื ื“ืขืงืก ืื•ื™ืฃ ืึท ืขืจืฉื˜ื™ืง ืฉืœื™ืกืœ ืื•ืŸ ืึท ืขื ืœืขืš "ืžืึทื ื•ืึทืœ" ืื™ื ื“ืขืงืก ืื•ื™ืฃ ืึท ื™ื™ื ืฆื™ืง ื–ื™ื™ึทืœ. ื“ืขืจ ืคืึทืงื˜ ืื™ื– ืึทื– ื–ื™ื™ ื–ืขื ืขืŸ ื ื™ืฉื˜ ื“ืืจืฃ - Postgres ื•ื•ืขื˜ ื˜ืึธืŸ ืึทืœืฅ ื–ื™ืš. ืึทื–ืึท ื™ื ื“ืขืงืกื™ื– ืงืขื ืขืŸ ื–ื™ื™ืŸ ื‘ืขืฉืึธืœืขื ืื•ื™ืกื’ืขืžืขืงื˜, ืื•ืŸ ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก ื”ืึธื‘ืŸ ืืจื•ื™ืก ืคึฟืึทืจ ื“ืขื ืฆื•ื•ืขืง duplicated_indexes.

ืคึผืจืึธื‘ืœืขื ื“ืจื™ื™ึท - ื™ื ื˜ืขืจืกืขืงื˜ื™ื ื’ ื™ื ื“ื™ืกื™ื–

ืจื•ื‘ึฟ ืึธื ื”ื™ื™ื‘ืขืจ ื“ืขื•ื•ืขืœืึธืคึผืขืจืก ืฉืึทืคึฟืŸ ื™ื ื“ืขืงืกื™ื– ืื•ื™ืฃ ืึท ืื™ื™ืŸ ื–ื™ื™ึทืœ. ื‘ื™ืกืœืขื›ื•ื•ื™ื™ึทื–, ื•ื•ืขืŸ ื–ื™ื™ ื”ืึธื‘ืŸ ื“ื•ืจื›ื’ืขืงืึธื›ื˜ ื“ืขื ื’ืขืฉืขืคื˜, ืžืขื ื˜ืฉืŸ ืึธื ื”ื™ื™ื‘ืŸ ืฆื• ืึทืคึผื˜ืึทืžื™ื™ื– ื–ื™ื™ืขืจ ืคึฟืจืื’ืŸ ืื•ืŸ ืœื™ื™ื’ืŸ ืžืขืจ ืงืึธืžืคึผืœื™ืฆื™ืจื˜ ื™ื ื“ืขืงืกื™ื– ื•ื•ืึธืก ืึทื ื˜ื”ืึทืœื˜ืŸ ืขื˜ืœืขื›ืข ืฉืคืืœื˜ืŸ. ื“ืึธืก ืื™ื– ื•ื•ื™ ื™ื ื“ืขืงืกื™ื– ืื•ื™ืฃ ืฉืคืืœื˜ืŸ ื“ืขืจืฉื™ื™ึทื ืขืŸ A, ื + ื‘, ื+ื‘+C. ืื•ืŸ ืื–ื•ื™ ื•ื•ื™ื™ื˜ืขืจ. ื“ื™ ืขืจืฉื˜ืข ืฆื•ื•ื™ื™ ืคื•ืŸ ื“ื™ ื™ื ื“ื™ืกื™ื– ืงืขื ืขืŸ ื–ื™ื™ืŸ ื‘ืขืฉืึธืœืขื ืืจืœื ื’ืขื•ื•ืืจืคืŸ, ื•ื•ื™ื™ึทืœ ื–ื™ื™ ื–ืขื ืขืŸ ืคึผืจืขืคื™ืงืก ืคื•ืŸ ื“ื™ ื“ืจื™ื˜. ื“ืึธืก ืื•ื™ืš ืกืึทื•ื•ืขืก ืึท ืคึผืœืึทืฅ ืคื•ืŸ ื“ื™ืกืง ืคึผืœืึทืฅ ืื•ืŸ ืขืก ื–ืขื ืขืŸ ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก ืคึฟืึทืจ ื“ืขื ื™ื ื˜ืขืจืกืขืงื˜ืขื“_ื™ื ื“ืขืงืกืขืก.

ืคึผืจืึธื‘ืœืขื ืคื™ืจ - ืคืจืขืžื“ ืฉืœื™ืกืœืขืŸ ืึธืŸ ื™ื ื“ืขืงืกื™ื–

Postgres ืึทืœืึทื•ื– ืื™ืจ ืฆื• ืฉืึทืคึฟืŸ ืคืจืขืžื“ ืฉืœื™ืกืœ ืงืึทื ืกื˜ืจื™ื™ื ืฅ ืึธืŸ ืกืคึผืขืฆื™ืคื™ืฆื™ืจืŸ ืึท ื‘ืึทืงื™ื ื’ ืื™ื ื“ืขืงืก. ืื™ืŸ ืคื™ืœืข ืกื™ื˜ื•ืึทื˜ื™ืึธื ืก ื“ืึธืก ืื™ื– ื ื™ืฉื˜ ืึท ืคึผืจืึธื‘ืœืขื, ืื•ืŸ ืงืขืŸ ื ื™ืฉื˜ ืืคื™ืœื• ื‘ืึทืฉื™ื™ึทืžืคึผืขืจืœืขืš ื–ื™ืš ... ืคึฟืึทืจ ื“ืขืจ ืฆื™ื™ื˜ ...

ื“ืึธืก ืื™ื– ื’ืขื•ื•ืขืŸ ื“ื™ ื–ืขืœื‘ืข ืžื™ื˜ ืื•ื ื“ื–: ืขืก ืื™ื– ื ืึธืจ ืึทื– ืื™ืŸ ืขื˜ืœืขื›ืข ืคื•ื ื˜ ืื™ืŸ ืฆื™ื™ื˜, ืึท ืึทืจื‘ืขื˜, ืคืœื™ืกื ื“ื™ืง ืœื•ื™ื˜ ืึท ืคึผืœืึทืŸ ืื•ืŸ ืงืœื™ืจื™ื ื’ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืคื•ืŸ ืคึผืจื•ื‘ื™ืจืŸ ืึธืจื“ืขืจืก, ืื ื’ืขื”ื•ื™ื‘ืŸ ืฆื• ื–ื™ื™ืŸ "ืฆื•ื’ืขืœื™ื™ื’ื˜" ืฆื• ืื•ื ื“ื– ื“ื•ืจืš ื“ื™ ื‘ืขืœ ื‘ืึทืœืขื‘ืึธืก. ืงืคึผื• ืื•ืŸ ื™ืึธ ื–ืขื ืขืŸ ื•ื•ื™ืกื˜, ืจื™ืงื•ื•ืขืก ืกืœืึธื•ื“ ืึทืจืึธืคึผ ืื•ืŸ ื–ืขื ืขืŸ ื˜ื™ื™ืžื“ ืื•ื™ืก, ื“ื™ ื“ื™ื ืกื˜ ืื™ื– ื’ืขื•ื•ืขืŸ ืคื™ื ืฃ ื”ื•ื ื“ืขืจื˜. ืฉื ืขืœ ืึทื ืึทืœื™ืกื™ืก pg_stat_activity ื’ืขื•ื•ื™ื–ืŸ ืึทื– ืคึฟืจืื’ืŸ ื•ื•ื™:

delete from <table> where id in (โ€ฆ)

ืื™ืŸ ื“ืขื ืคืึทืœ, ืคื•ืŸ ืงื•ืจืก, ืขืก ืื™ื– ื’ืขื•ื•ืขืŸ ืึทืŸ ืื™ื ื“ืขืงืก ืœื•ื™ื˜ id ืื™ืŸ ื“ื™ ืฆื™ืœ ื˜ื™ืฉ, ืื•ืŸ ื–ื™ื™ืขืจ ื•ื•ื™ื™ื ื™ืง ืจืขืงืึธืจื“ืก ื–ืขื ืขืŸ ืื•ื™ืกื’ืขืžืขืงื˜ ืœื•ื™ื˜ ื“ื™ ืฆื•ืฉื˜ืึทื ื“. ืขืก ื’ืขื•ื•ืขืŸ ื•ื•ื™ ืึทืœืฅ ื–ืึธืœ ืึทืจื‘ืขื˜ืŸ, ืึธื‘ืขืจ, ื•ื•ื™ื™, ืขืก ื”ืื˜ ื ื™ืฉื˜.

ื“ืขืจ ื•ื•ื•ื ื“ืขืจืœืขืš ืื™ื™ื ืขืจ ื’ืขืงื•ืžืขืŸ ืฆื• ื“ื™ ื”ื™ืœืฃ ื“ืขืจืงืœืขืจืŸ ืึทื ืึทืœื™ืกื™ืก ืื•ืŸ ื’ืขื–ืื’ื˜ ืึทื– ืื™ืŸ ืึทื“ื™ืฉืึทืŸ ืฆื• ื•ื™ืกืžืขืงืŸ ืจืขืงืึธืจื“ืก ืื™ืŸ ื“ื™ ืฆื™ืœ ื˜ื™ืฉ, ืขืก ืื™ื– ืื•ื™ืš ืึท ืจืขืคืขืจืขื ื˜ืฉืึทืœ ืึธืจื ื˜ืœืขื›ืงื™ื™ึทื˜ ื˜ืฉืขืง, ืื•ืŸ ืื•ื™ืฃ ืื™ื™ื ืขืจ ืคื•ืŸ ื“ื™ ืคึฟืึทืจื‘ื•ื ื“ืขื ืข ื˜ื™ืฉืŸ ื“ืขื ื˜ืฉืขืง ืคื™ื™ืœื– ืกืึทืงื•ื•ืขื ื˜ืฉืึทืœ ื™ื‘ืขืจืงื•ืงืŸ ืจืขื›ื˜ ืฆื• ื“ืขืจ ืžืึทื ื’ืœ ืคื•ืŸ ืึท ืคึผืึทืกื™ืง ืื™ื ื“ืขืงืก. ืื–ื•ื™ ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก ืื™ื– ื’ืขื‘ื•ื™ืจืŸ foreign_keys_without_index.

ืคึผืจืึธื‘ืœืขื ืคื™ื ืฃ - ื ื•ืœ ื•ื•ืขืจื˜ ืื™ืŸ ื™ื ื“ืขืงืกื™ื–

ื“ื•ืจืš ืคืขืœื™ืงื™ื™ึทื˜, Postgres ื™ื ืงืœื•ื“ื– ื ืึทืœ ื•ื•ืึทืœื•ืขืก ืื™ืŸ ื‘ื˜ืจื™ ื™ื ื“ืขืงืกื™ื–, ืึธื‘ืขืจ ื–ื™ื™ ื–ืขื ืขืŸ ื™ื•ื–ืฉืึทื•ื•ืึทืœื™ ื ื™ื˜ ื“ืืจืฃ ื“ืึธืจื˜. ื“ืขืจื™ื‘ืขืจ, ืื™ืš ืคืœื™ื™ืกื™ืง ืคึผืจื•ื‘ื™ืจืŸ ืฆื• ื•ื•ืึทืจืคืŸ ืื•ื™ืก ื“ื™ ื ื•ืœื– (ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก indexes_with_null_values), ืงืจื™ื™ื™ื˜ื™ื ื’ ืคึผืึทืจื˜ื™ื™ื™ืฉ ื™ื ื“ืขืงืกื™ื– ืื•ื™ืฃ ื ืึทืœืึทื‘ืึทืœ ืฉืคืืœื˜ืŸ ืœื•ื™ื˜ ื˜ื™ืคึผ where <A> is not null. ืื™ืŸ ื“ืขื ื•ื•ืขื’ ืื™ืš ืื™ื– ื’ืขื•ื•ืขืŸ ื‘ื™ื›ื•ืœืช ืฆื• ืจืขื“ื•ืฆื™ืจืŸ ื“ื™ ื’ืจื™ื™ืก ืคื•ืŸ ืื™ื™ื ืขืจ ืคื•ืŸ ืื•ื ื“ื–ืขืจ ื™ื ื“ืขืงืกื™ื– ืคื•ืŸ 1877 ืžื‘ ืฆื• 16 ืงื‘. ืื•ืŸ ืื™ืŸ ืื™ื™ื ืขืจ ืคื•ืŸ ื“ื™ ืกืขืจื•ื•ื™ืกืขืก, ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ื’ืจื™ื™ืก ืื™ื– ื“ื™ืงืจื™ืกื˜ ืื™ืŸ ื’ืึทื ืฅ ืžื™ื˜ 16% (ื“ื•ืจืš 4.3 ื’ื™ื’ืื‘ื™ื™ื˜ ืื™ืŸ ืึทื‘ืกืึธืœื•ื˜ ื ื•ืžืขืจืŸ) ืจืขื›ื˜ ืฆื• ื“ืขืจ ื™ืงืกืงืœื•ื–ืฉืึทืŸ ืคื•ืŸ ื ืึทืœ ื•ื•ืึทืœื•ืขืก ืคื•ืŸ ื“ื™ ื™ื ื“ืขืงืกื™ื–. ืจื™ื–ื™ืง ืกื™ื™ื•ื•ื™ื ื’ื– ืื™ืŸ ื“ื™ืกืง ืคึผืœืึทืฅ ืžื™ื˜ ื–ื™ื™ืขืจ ืคึผืฉื•ื˜ ืžืึธื“ื™ืคื™ืงืึทื˜ื™ืึธื ืก. ๐Ÿ™‚

ืคึผืจืึธื‘ืœืขื ื–ืขืงืก - ืคืขืœืŸ ืคื•ืŸ ืขืจืฉื˜ื™ืง ืฉืœื™ืกืœืขืŸ

ืจืขื›ื˜ ืฆื• ื“ืขืจ ื ืึทื˜ื•ืจ ืคื•ืŸ ื“ื™ ืžืขืงืึทื ื™ื–ืึทื MVCC ืื™ืŸ Postgres ืึทื–ืึท ืกื™ื˜ื•ืึทืฆื™ืข ืื™ื– ืžืขื’ืœืขืš ื‘ืœืึธื–ืŸื•ื•ืขืŸ ื“ื™ ื’ืจื™ื™ืก ืคื•ืŸ ื“ื™ื™ืŸ ื˜ื™ืฉ ืื™ื– ื’ืจืึธื•ื™ื ื’ ืจืึทืคึผืึทื“ืœื™ ืจืขื›ื˜ ืฆื• ืึท ื’ืจื•ื™ืก ื ื•ืžืขืจ ืคื•ืŸ ื˜ื•ื™ื˜ ืจืขืงืึธืจื“ืก. ืื™ืš ื”ืื‘ ื ืื™ื•ื• ื’ืขื’ืœื™ื™ื‘ื˜ ืื– ื“ืืก ื•ื•ืขื˜ ืื•ื ื– ื ื™ืฉื˜ ืกื˜ืจืืฉืขืŸ, ืื•ืŸ ื“ืืก ื•ื•ืขื˜ ื ื™ืฉื˜ ืคืืกื™ืจืŸ ืžื™ื˜ ืื•ื ื–ืขืจ ื‘ืื–ืข, ื•ื•ื™ื™ืœ ืžื™ืจ, ื•ื•ืื•!!!, ื–ืขื ืขืŸ ื ืืจืžืืœืข ื“ืขื•ื•ืขืœืึธืคึผืขืจืก... ื•ื•ื™ ื ืืจื™ืฉ ืื•ืŸ ื ืื™ื•ื• ืื™ืš ื‘ื™ืŸ ื’ืขื•ื•ืขืŸ...

ืื™ื™ืŸ ื˜ืึธื’, ืื™ื™ืŸ ื•ื•ื•ื ื“ืขืจืœืขืš ืžื™ื’ืจืึทื˜ื™ืึธืŸ ื’ืขื ื•ืžืขืŸ ืื•ืŸ ื“ืขืจื”ื™ื™ึทื ื˜ื™ืงื˜ ืึทืœืข ื“ื™ ืจืขืงืึธืจื“ืก ืื™ืŸ ืึท ื’ืจื•ื™ืก ืื•ืŸ ืึทืงื˜ื™ื•ื•ืœื™ ื’ืขื•ื•ื™ื™ื ื˜ ื˜ื™ืฉ. ืžื™ืจ ื‘ืึทืงื•ืžืขืŸ +100 ื’ื™ื’ืื‘ื™ื™ื˜ ืฆื• ื“ื™ ื˜ื™ืฉ ื’ืจื™ื™ืก ืื•ื™ืก ืคื•ืŸ ื“ื™ ื‘ืœื•ื™. ืขืก ืื™ื– ื’ืขื•ื•ืขืŸ ืึท ืคืึทืจืฉื™ืœื˜ืŸ ื‘ื•ืฉื”, ืึธื‘ืขืจ ืื•ื ื“ื–ืขืจ ืžื™ืกืึทื“ื•ื•ืขื ื˜ื•ืจืขืก ื”ืึธื‘ืŸ ื ื™ืฉื˜ ืกื•ืฃ ื“ืึธืจื˜. ื ืึธืš 15 ืฉืขื” ืฉืคึผืขื˜ืขืจ ื“ื™ ืึทื•ื˜ืึธื•ื•ืึทืงื•ื•ื ืื•ื™ืฃ ื“ืขื ื˜ื™ืฉ ืื™ื– ื’ืขื•ื•ืขืŸ ืงืœืึธืจ, ืึทื– ื“ื™ ื’ืฉืžื™ื•ืช ืึธืจื˜ ื•ื•ืึธืœื˜ ื ื™ืฉื˜ ืฆื•ืจื™ืงืงื•ืžืขืŸ. ืžื™ืจ ืงืขืŸ ื ื™ืฉื˜ ื”ืึทืœื˜ืŸ ื“ื™ ื“ื™ื ืกื˜ ืื•ืŸ ืžืึทื›ืŸ ื•ื•ืึทืงื•ื•ื ืคื•ืœ, ืึทื–ื•ื™ ืžื™ืจ ื‘ืึทืฉืœืึธืกืŸ ืฆื• ื ื•ืฆืŸ pg_repack. ืื•ืŸ ื“ืขืžืึธืœื˜ ืขืก ืคืืจืงืขืจื˜ ืื•ื™ืก ืึทื– pg_repack ื˜ื•ื˜ ื ื™ืฉื˜ ื•ื•ื™ืกืŸ ื•ื•ื™ ืฆื• ืคึผืจืึธืฆืขืก ื˜ื™ืฉืŸ ืึธืŸ ืึท ืขืจืฉื˜ื™ืง ืฉืœื™ืกืœ ืึธื“ืขืจ ืื ื“ืขืจืข ืื™ื™ื’ื ืืจื˜ื™ืงื™ื™ื˜ ืงืึทื ืกื˜ืจื™ื™ื ืฅ, ืื•ืŸ ืื•ื ื“ื–ืขืจ ื˜ื™ืฉ ื”ืื˜ ื ื™ืฉื˜ ื”ืึธื‘ืŸ ืึท ืขืจืฉื˜ื™ืง ืฉืœื™ืกืœ. ืื–ื•ื™ ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก ืื™ื– ื’ืขื‘ื•ื™ืจืŸ ื˜ืึทื‘ืœืขืก_without_primary_key.

ืื™ืŸ ื“ืขืจ ื‘ื™ื‘ืœื™ืึธื˜ืขืง ื•ื•ืขืจืกื™ืข 0.1.5 ื“ื™ ืคื™ื™ื™ืงื™ื™ื˜ ืฆื• ื–ืึทืžืœืขืŸ ื“ืึทื˜ืŸ ืคื•ืŸ ื‘ืœืึธื•ื˜ื™ื ื’ ืคื•ืŸ ื˜ื™ืฉืŸ ืื•ืŸ ื™ื ื“ืขืงืกื™ื– ืื•ืŸ ืจื™ืกืคึผืึทื ื“ ืฆื• ืขืก ืื™ืŸ ืึท ื‘ื™ื™ึทืฆื™ื™ึทื˜ื™ืง ืฉื˜ื™ื™ื’ืขืจ ืื™ื– ืฆื•ื’ืขื’ืขื‘ืŸ.

ืคึผืจืึธื‘ืœืขืžืก ื–ื™ื‘ืŸ ืื•ืŸ ืึทื›ื˜ - ื ื™ื˜ ื’ืขื ื•ื’ื™ืง ื™ื ื“ืขืงืกื™ื– ืื•ืŸ ืึทื ื™ื•ื–ื“ ื™ื ื“ืขืงืกื™ื–

ื“ื™ ืคืืœื’ืขื ื“ืข ืฆื•ื•ื™ื™ ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก ื–ืขื ืขืŸ: ื˜ืึทื‘ืœืขืก_ืžื™ื˜_ืžื™ืกื™ื ื’_ื™ื ื“ืขืงืกืขืก ะธ unused_indexes โ€“ ืืจื•ื™ืก ืื™ืŸ ื–ื™ื™ืขืจ ืœืขืฆื˜ ืคืึธืจืขื ืœืขืคื™ืขืจืขืš ืœืขืฆื˜ื ืก. ื“ื™ ืคื•ื ื˜ ืื™ื– ืึทื– ื–ื™ื™ ืงืขืŸ ื ื™ืฉื˜ ื ืึธืจ ื–ื™ื™ืŸ ื’ืขื ื•ืžืขืŸ ืื•ืŸ ืฆื•ื’ืขื’ืขื‘ืŸ.

ื•ื•ื™ ืื™ืš ืฉื•ื™ืŸ ื’ืขืฉืจื™ื‘ืŸ, ืžื™ืจ ื ื•ืฆืŸ ืึท ืงืึทื ืคื™ื’ื™ืขืจื™ื™ืฉืึทืŸ ืžื™ื˜ ืขื˜ืœืขื›ืข ืจืขืคึผืœืึทืงืึทื–, ืื•ืŸ ื“ื™ ืœื™ื™ืขื ืขืŸ ืžืึทืกืข ืื•ื™ืฃ ืคืึทืจืฉื™ื“ืขื ืข ืžื—ื ื•ืช ืื™ื– ืคืึทื ื“ืึทืžืขื ื˜ืึทืœื™ ืึทื ื“ืขืจืฉ. ื•ื•ื™ ืึท ืจืขื–ื•ืœื˜ืึทื˜, ื“ื™ ืกื™ื˜ื•ืึทืฆื™ืข ื˜ื•ืจื ืก ืื•ื™ืก ืึทื– ืขื˜ืœืขื›ืข ื˜ื™ืฉืŸ ืื•ืŸ ื™ื ื“ืขืงืกื™ื– ืื•ื™ืฃ ืขื˜ืœืขื›ืข ืžื—ื ื•ืช ื–ืขื ืขืŸ ืคึผืจืึทืงื˜ืึทืงืœื™ ื ื™ืฉื˜ ื’ืขื ื™ืฆื˜, ืื•ืŸ ืคึฟืึทืจ ืึทื ืึทืœื™ืกื™ืก ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื–ืึทืžืœืขืŸ ืกื˜ืึทื˜ื™ืกื˜ื™ืง ืคื•ืŸ ืึทืœืข ืžื—ื ื•ืช ืื™ืŸ ื“ืขื ืงื ื•ื™ืœ. ื‘ืึทืฉื˜ืขื˜ื™ืง ืกื˜ืึทื˜ื™ืกื˜ื™ืง ื“ืึธืก ืื™ื– ืื•ื™ืš ื ื™ื™ื˜ื™ืง ืคึฟืึทืจ ื™ืขื“ืขืจ ื‘ืึทืœืขื‘ืึธืก ืื™ืŸ ื“ืขื ืงื ื•ื™ืœ; ืื™ืจ ืงืขื ืขืŸ ื ื™ืฉื˜ ื˜ืึธืŸ ื“ืึธืก ื‘ืœื•ื™ื– ืื•ื™ืฃ ื“ื™ ื‘ืขืœ.

ื“ืขืจ ืฆื•ื’ืึทื ื’ ื”ืึธื˜ ืื•ื ื“ื– ื“ืขืจืœื•ื™ื‘ื˜ ืฆื• ืจืึทื˜ืขื•ื•ืขืŸ ืขื˜ืœืขื›ืข ื˜ืขื ืก ืคื•ืŸ ื’ื™ื’ืื‘ื™ื™ื˜ ื“ื•ืจืš ืจื™ืžื•ื•ื•ื™ื ื’ ื™ื ื“ืขืงืกื™ื– ื•ื•ืึธืก ื–ืขื ืขืŸ ืงื™ื™ื ืžืึธืœ ื’ืขื•ื•ื™ื™ื ื˜, ื•ื•ื™ ื’ืขื–ื•ื ื˜ ื•ื•ื™ ืฆื• ืœื™ื™ื’ืŸ ืคืขืœื ื“ื™ืง ื™ื ื“ืขืงืกื™ื– ืฆื• ืจืึทืจืขืœื™ ื’ืขื•ื•ื™ื™ื ื˜ ื˜ื™ืฉืŸ.

ื•ื•ื™ ืึท ืจืขื–ื•ืœื˜ืึทื˜

ืคื•ืŸ ืงื•ืจืก, ืื™ืจ ืงืขื ืขืŸ ืงืึทื ืคื™ื’ื™ืขืจ ื›ึผืžืขื˜ ืึทืœืข ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก ื™ืงืกืงืœื•ื–ืฉืึทืŸ ืจืฉื™ืžื”. ื“ืขื ื•ื•ืขื’, ืื™ืจ ืงืขื ืขืŸ ื’ืขืฉื•ื•ื™ื ื“ ื™ื ืกื˜ืจื•ืžืขื ื˜ ื˜ืฉืขืงืก ืื™ืŸ ื“ื™ื™ืŸ ืึทืคึผืœืึทืงื™ื™ืฉืึทืŸ, ืคึผืจืขื•ื•ืขื ื˜ื™ื ื’ ื ื™ื™ึท ืขืจืจืึธืจืก ืคื•ืŸ ืื ื˜ืคืœืขืงื•ื ื’, ืื•ืŸ ื“ืขืžืึธืœื˜ ื‘ื™ืกืœืขื›ื•ื•ื™ื™ึทื– ืคืึทืจืจื™ื›ื˜ืŸ ืึทืœื˜ ืึธื ืขืก.

ืขื˜ืœืขื›ืข ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก ืงืขื ืขืŸ ื–ื™ื™ืŸ ื“ื•ืจื›ื’ืขืงืึธื›ื˜ ืื™ืŸ ืคืึทื ื’ืงืฉืึทื ืึทืœ ื˜ืขืกืฅ ืžื™ื“ ื ืึธืš ืจืึธื•ืœื™ื ื’ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืžื™ื™ื’ืจื™ื™ืฉืึทื ื–. ืื•ืŸ ื“ืึธืก ืื™ื– ื˜ืึธืžืขืจ ืื™ื™ื ืขืจ ืคื•ืŸ ื“ื™ ืžืขืจืกื˜ ืฉื˜ืึทืจืง ืคึฟืขื™ึดืงื™ื™ื˜ืŸ ืคื•ืŸ ืžื™ื™ืŸ ื‘ื™ื‘ืœื™ืึธื˜ืขืง. ื ื‘ื™ื™ืฉืคึผื™ืœ ืคื•ืŸ ื ื•ืฆืŸ ืงืขื ืขืŸ ื–ื™ื™ืŸ ื’ืขืคึฟื•ื ืขืŸ ืื™ืŸ ื“ืขืžืึธ.

ืขืก ืื™ื– ื–ื™ื ืขืŸ ืฆื• ื“ื•ืจื›ืคื™ืจืŸ ื˜ืฉืขืงืก ืคึฟืึทืจ ืึทื ื™ื•ื–ื“ ืึธื“ืขืจ ืคืขืœื ื“ื™ืง ื™ื ื“ืขืงืกื™ื–, ื•ื•ื™ ื’ืขื–ื•ื ื˜ ื•ื•ื™ ืคึฟืึทืจ ื‘ืœืึธื•ื˜ื™ื ื’, ื‘ืœื•ื™ื– ืื•ื™ืฃ ืึท ืคืึทืงื˜ื™ืฉ ื“ืึทื˜ืึทื‘ื™ื™ืก. ื“ื™ ื’ืขื–ืืžืœื˜ ื•ื•ืึทืœื•ืขืก ืงืขื ืขืŸ ื–ื™ื™ืŸ ืจืขืงืึธืจื“ืขื“ ืื™ืŸ ืงืœื™ืงืงื›ืึทื•ืก ืึธื“ืขืจ ื’ืขืฉื™ืงื˜ ืฆื• ื“ื™ ืžืึธื ื™ื˜ืึธืจื™ื ื’ ืกื™ืกื˜ืขื.

ืื™ืš ื”ืึธืคึฟืŸ ื“ืึธืก ื˜ืึทืงืข ืคึผื’-ืื™ื ื“ืขืงืก-ื’ืขื–ื•ื ื˜ ื•ื•ืขื˜ ื–ื™ื™ืŸ ื ื•ืฆื™ืง ืื•ืŸ ืื™ืŸ ืžืึธื ืขืŸ. ืื™ืจ ืงืขื ื˜ ืื•ื™ืš ื‘ื™ื™ืฉื˜ื™ื™ืขืจืŸ ืฆื• ื“ืขืจ ืึทื ื˜ื•ื•ื™ืงืœื•ื ื’ ืคื•ืŸ ื“ืขืจ ื‘ื™ื‘ืœื™ืึธื˜ืขืง ื“ื•ืจืš ืจืขืคึผืึธืจื˜ื™ื ื’ ืคืจืื‘ืœืขืžืขืŸ ื•ื•ืึธืก ืื™ืจ ื’ืขืคึฟื™ื ืขืŸ ืื•ืŸ ืคึฟืึธืจืฉืœืึธื’ืŸ ื ื™ื™ึทืข ื“ื™ืึทื’ื ืึธืกื˜ื™ืงืก.

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’