ืงืึทื ืืื ืืืื ื ืื ืืขืืข ืคืื โโืจืขืงืึธืจืืื ื ืืจืืืก ืืึทืื ืกืืจืืื ืืืืคืฉืืืื ืืืจื , ืืื ืืขื ืืืจ ืืืขืื ืงืืงื ืืื ืื ืืืขืื ืืื ืืืึธืก ืืืจ ืงืขื ืขื ืจืขืืืฆืืจื ืื "ืืฉืืืืช" ืืจืืืก ืคืื ืื ืกืืึธืจื ืืื PostgreSQL, ืืื ืืืืขืจ ืคึผืจืึทื ืืืืฃ ืกืขืจืืืขืจ ืคืึธืจืฉืืขืืื ื.
ืืืจ ืืืขืื ืจืขืื ืืืขืื TOAST ืกืขืืืื ืืก ืืื ืืึทืื ืึทืืืื ืืึทื ื. "ืืื ืืืจืืฉื ืืืืขื," ืื ืืขืืืึธืืก ืืืขื ื ืืฉื ืฉืคึผืึธืจื ืฆื ืคืืืข ืจืขืกืืจืกื, ืึธืืขืจ ืึธื ืืึธืืืคืืฆืืจื ืื ืึทืคึผืืึทืงืืืฉืึทื ืงืึธื.
ืึธืืขืจ, ืืื ืืืขืจ ืืขืจืคืึทืจืื ื ืืื ืืขืืืขื ืืืืขืจ ืคึผืจืึธืืืงืืืื ืืื ืืขื ืึทืืืื ื, ืืืืึทื ืื ืกืืึธืจืืืืฉ ืคืื ืึผืืขื ืงืืื ืืึธื ืืืึธืจืื ื ืืืื ืืืึทื ื ืึทืืืจ ืืื ืืขืจืกืื ืก ืฆืืืขืื-ืืืืื ืืื ืืขืจืืื ืขื ืคืื ืจืขืงืึธืจืืขื ืืึทืื. ืืื ืืืื ืืืจ ืืขื ื ืืืึทื ืืขืจืื ื ืืื ืืืจ ืงืขื ืขื ืืขืจื ืขื ืื ืืึทืืึทืืืืก ืฆื ืฉืจืืึทืื ืฆื ืืืกืง ืึทื ืฉืืึธื 200 ืื / s ืืึทืื ืืื ืคืื - ืืืืข ืืื ืืขืจ ืงืึทืฅ.
ืงืืืื ืกืืงืจืืฅ ืคืื ืืจืืืก ืืึทืื
ืืืจื ืึทืจืืขื ืคึผืจืึธืคืื , ืืื ืคืืืขื ืงืขืกืืืืขืจ ืฆื ืืื ืคืื ืื ืืขืจ ืืขืงืกื ืคึผืึทืงืึทืืืฉืึทื.
ืืื ืืื ื ืืืขืืขื ืก ืืึทืืึทืืืืก ืืืจ ืืึธื ืืืึธืจ ืืื ืึท ืืึทืืื-ืงืึธืืคึผืึธื ืขื ื ืคึผืจืึธืืืงื ืืื ืงืึธืืคึผืืขืงืก ืืึทืื ืกืืจืึทืงืืฉืขืจื, ืืขืืึธืื ืงืืืืจืื ืคึฟืึทืจ ืืึทืงืกืืืื ืคืึธืจืฉืืขืืื ื ืงืขืจ ืืืืก ืืึทื ืฅ ืืื ืืึธืก . ืึทืืื ืืขืจ ืืึทื ื ืคืื ืืขืืขืจ ืืืื ืืืึทืฉืคึผืื ืคืื ืึท ืืงืฉื ืึธืืขืจ ืื ืจืืืึทืืืื ื ืืืจืืคืืจืื ื ืคึผืืึทื ืืื ืื ืงืืึธืฅ ืืืึธืก ืงืืื ืฆื ืืื ืื ืืืจื ืก ืืืืก ืฆื ืืืื "ืืื ืืืจืืฉื ืืืืขื" ืืึทื ืฅ ืืจืืืก.
ืืื ืก ืงืืง ืืื ืื ืกืืจืืงืืืจ ืคืื ืืืื ืขืจ ืคืื ืื ืืืฉื ืืื ืืืึธืก ืืืจ ืฉืจืืึทืื "ืจืื" ืืึทืื - ืืึธืก ืืื, ืืึธ ืืื ืืขืจ ืึธืจืืืื ืขื ืืขืงืกื ืคืื ืื ืงืืึธืฅ ืคึผืึธืืืฆืืข:
CREATE TABLE rawdata_orig(
pack -- PK
uuid NOT NULL
, recno -- PK
smallint NOT NULL
, dt -- ะบะปัั ัะตะบัะธะธ
date
, data -- ัะฐะผะพะต ะณะปะฐะฒะฝะพะต
text
, PRIMARY KEY(pack, recno)
);ื ืืืคึผืืฉ ืฆืืืื (ืฉืืื ืกืขืงืฉืึทื ื, ืคืื ืงืืจืก, ืึทืืื ืืึธืก ืืื ืึท ืึธืคึผืืืืืื ื ืืืกืืขืจ), ืืื ืื ืืขืจืกื ืืืืืืืง ืืึทื ืืื ืื ืืขืงืกื. ืืื ืืึทื ืฅ ืืืึทืืืืึทื ืึทืก.
ืฆืืจืืงืจืืคื ืึทื ืื "ืืฉืืืืช" ืืจืืืก ืคืื ืืืื ืจืขืงืึธืจื ืืื ืึท ืคึผื ืงืขื ื ืืฉื ืคืึทืจื ืขืืขื ืืขืจ ืืื ืืืื ืืืึทื ืคืื ืืึทืื, ืึธืืขืจ ืื "ืืึทืืืฉืืงืึทื" ืืจืืืก ืืื ืืึธืจ ืึทื ืืขืจืฉ. ืฆื ืฉืจืืึทืื ืึท ืืืึธืืืืขืืจืืง ืืืขืจื (ืืืึทืจืืฉืึทืจ / ืืขืงืกื / ืืืืขืึท) ืฆื ืึท ืคืขืื, ื ืืฆื :
PostgreSQL ื ืืฆื ืึท ืคืึทืจืคืขืกืืืงื ืืืึทื ืืจืืืก (ืืืคึผืืงืื 8 ืงื), ืืื ืืื ื ืืฉื ืืึธืื ืืืคึผืืขืก ืฆื ืฉืคึผืึทื ืงืืืคื ืืืขืืขืจ. ืืขืจืืืขืจ, ืขืก ืืื ืืืืืขืืืขื ืฆื ืืืืึทื ืงืจืึธื ืืืืขืจ ืืจืืืก ืคืขืื ืืืึทืืืขืก. ืฆื ืืึทืงืืืขื ืืขื ืืึทืืจืขื ืขืฆืื ื, ืืจืืืก ืคืขืื ืืืึทืืืขืก ืืขื ืขื ืงืึทืืคึผืจืขืกื ืืื / ืึธืืขืจ ืฉืคึผืึทืืื ืืืืฃ ืงืืืคื ืืฉืืืืช ืฉืืจืืช. ืืขื ืืึทืคึผืึทื ื ืึทื ื ืึธืืืืกื ืืืจื ืืขืจ ืืึทื ืืฆืขืจ ืืื ืืื ืึท ืงืืืื ืคึผืจืึทื ืืืืฃ ืจืืึฟ ืกืขืจืืืขืจ ืงืึธื. ืืขืจ ืืืคึฟื ืืื ืืืงืื ื ืืื TOAST ...
ืืื ืคืึทืงื, ืคึฟืึทืจ ืืขืืขืจ ืืืฉ ืืื "ืคึผืึทืืขื ืืฉืึทืื ืืจืืืก" ืคืขืืืขืจ, ืืืืืึธืืึทืืืฉ ืืขืืขืจ "ืืจืืืก" ืจืขืงืึธืจื ืืื 2KB ืกืขืืืึทื ืฅ:
TOAST(
chunk_id
integer
, chunk_seq
integer
, chunk_data
bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);
ืึทื ืืื, ืืืื ืืืจ ืืึธืื ืฆื ืฉืจืืึทืื ืึท ืฉืืจืืงื ืืื ืึท "ืืจืืืก" ืืืขืจื data, ืืขืืึธืื ืืขืจ ืขืืขืก ืจืขืงืึธืจืืื ื ืืืขื ืคึผืึทืกืืจื ื ืื ืืืืื ืฆื ืื ืืืืคึผื ืืืฉ ืืื ืืืึทื ืคึผืง, ืึธืืขืจ ืืืื ืฆื TOAST ืืื ืืืึทื ืคึผืง.
ืจืืืืกืื ื ืืึธืึทืกื ืืฉืคึผืขื
ืึธืืขืจ ืจืืึฟ ืคืื ืืื ืืืขืจ ืจืขืงืึธืจืืก ืืขื ืขื ื ืึธื ื ืืฉื ืึทืืื ืืจืืืก, ืืึธื ืคึผืึทืกืืง ืืื 8KB - ืืื ืึทืืื ืงืขื ืืื ืฉืคึผืึธืจื ืืขืื ืืืืฃ ืืขื?
ืืึธืก ืืื ืืื ืื ืึทืืจืืืืื ืงืืื ืฆื ืืื ืืืขืจ ืืืืฃ ืืื ืื ืืืฉ ืืืึทื:
- ืขืงืกืืขื ืืขื ืึทืืึทืื ืืืืืข ืงืึทืืคึผืจืขืฉืึทื ืืื ืืึทืืื ืืขืจ ืกืืึธืจืืืืฉ. ืืืก ื ืึธืจืืึทื ืึธืคึผืฆืืข ืคึฟืึทืจ ืจืืึฟ TOAST ืืขืืึธืจืืืง ืืึทืื ืืืืคึผืก. ืขืก ืขืจืฉืืขืจ ืคืจืืืื ืฆื ืืืจืืคืืจื ืงืึทืืคึผืจืขืฉืึทื, ืืขืืึธืื ืกืืึธืจื ืขืก ืึทืจืืืก ืื ืืืฉ ืืืื ืื ืจืืืขืจื ืืื ื ืึธื ืฆื ืืจืืืก.
- ืืืืคึผื ืึทืืึทืื ืงืึทืืคึผืจืขืฉืึทื ืึธืืขืจ ื ืืฉื ืืึทืืื ืืขืจ ืกืืึธืจืืืืฉ. (ืืื ืคืึทืงื, ืืึทืืื ืืขืจ ืกืืึธืจืืืืฉ ืืืขื ื ืึธื ืืืื ืืืจืืืขืงืึธืื ืคึฟืึทืจ ืึทืืึท ืฉืคืืืื, ืึธืืขืจ ืืืืื ืืื ืึท ืืขืฆืืข ืจืืืึธืจื, ืืืขื ืขืก ืืื ืงืืื ืื ืืขืจืข ืืืขื ืฆื ืืึทื ืฉืจืืืคึผื ืื ืฉืืจืืงื ืึทืืื ืึทื ืขืก ืคืืฅ ืืืืฃ ืื ืืืึทื.)
ืืื ืคืึทืงื, ืืึธืก ืืื ืคึผืื ืงื ืืืึธืก ืืืจ ืืึทืจืคึฟื ืคึฟืึทืจ ืื ืืขืงืกื - ืงืึธืืคึผืจืขืก ืขืก ืืื ืคืื ืืื ืืขืืืขื, ืืื ืืืื ืขืก ืืื ื ืืฉื ืคึผืึทืกืืง, ืฉืืขืื ืขืก ืืื TOAST. ืืขื ืงืขื ืขื ืืืื ืืขืืื ืืืืึทื ืืืืฃ ืื ืคืืืขื, ืืื ืืืื ืืึทืคึฟืขื:
ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;ืืื ืฆื ืึธืคึผืฉืึทืฆื ืื ืืืืจืงืื ื
ืืื ื ืื ืืึทืื ืืืืคื ืขื ืืขืจืื ืืขื ืืขืืขืจ ืืึธื, ืืืจ ืงืขื ืขื ื ืืฉื ืคืึทืจืืืืึทืื ืึทืืกืึธืืื ื ืืืขืจื, ืึธืืขืจ ืืื ืงืึธืจืขืื ืืขืจืืื ืขื ืงืืขื ืขืจืขืจ ืืืืื ืืืจ ืืขืฉืจืืื ืขืก ืึทืจืึธืคึผ ืืื TOAST - ืึทืืื ืคืื ืืขืกืขืจ. ืืืขืจ ืืึธ ืืื ืึท ืืขืคืึทืจ - ืืืึธืก ืืจืขืกืขืจ ืื "ืืฉืืืืช" ืืึทื ื ืคืื ืืขืืขืจ ืืืื ืจืขืงืึธืจื, ืื "ืืจืืืืขืจ" ืืขืจ ืืื ืืขืงืก ืืืขืจื, ืืืืึทื ืืืจ ืืึธืื ืฆื ืืขืงื ืืขืจ ืืืขืืขืจ ืคืื ืืึทืื.
ืึธืคึผืืืืืื ื ืืืืืขืจ ืขื ืืขืจืื ืืขื:
heap = 37GB (39%)
TOAST = 54GB (57%)
PK = 4GB ( 4%)
ืึธืคึผืืืืืื ื ื ืึธื ืขื ืืขืจืื ืืขื:
heap = 37GB (67%)
TOAST = 16GB (29%)
PK = 2GB ( 4%)ืืื ืคืึทืงื, ืืืจ ืื ืืขืืืืื ืฉืจืืืื ืฆื TOAST 2 ืืื ืืืืื ืืงืขืจ ืึธืคื, ืืืึธืก ืึทื ืืึธืืืื ื ืื ืืืืื ืื ืืืกืง, ืึธืืขืจ ืืืื ืื ืงืคึผื:
ืืื ืืืขื ืืืืขืจืงื ืื ืืืจ ืืขื ืขื ืืืื ืืขืืืืจื ืงืืขื ืขืจ ืืื "ืืืืขื ืขื" ืืขื ืืืกืง, ื ืืฉื ืืืืื "ืฉืจืืืื" - ืืืืื ืืืขื ืืขื ืืืืื ืืจืืื ื ืจืขืงืืจื ืืื ื ืืืฉ, ืืืจืคื ืืืจ ืืืื "ืืืืขื ืขื" ืืืื ืคืื ืขื ืืืื ืคืื ืืขืื ืืื ืืขืงืก ืืื ืฆื ืืืฉืืืืขื ืืืื ืฆืืงืื ืคึฟื ืฉืืขืืข ืืื ืืื.
ืืืขืจ ืงืขื ืขื ืืขืื ืืขืืื ื ืืืืฃ PostgreSQL 11
ื ืึธื ืึทืคึผืืืืืื ื ืฆื PG11, ืืืจ ืืึทืฉืืึธืกื ืฆื ืคืึธืจืืขืฆื "ืืื ืื ื" TOAST ืืื ืืืืขืจืงื ืึทื ืกืืึทืจืืื ื ืคืื ืืขื ืืืขืจืกืืข ืื ืคึผืึทืจืึทืืขืืขืจ :
ืื TOAST ืคึผืจืึทืกืขืกืื ื ืงืึธื ืคืืจื ืืืืื ืืืขื ืื ืจืืืขืจื ืืืขืจื ืฆื ืืืื ืกืืึธืจื ืืื ืื ืืืฉ ืืื ืืจืขืกืขืจ ืืื TOAST_TUPLE_THRESHOLD ืืืืขืก (ืืืืฉืึทืืืึทืื 2 ืงื). ืื TOAST ืงืึธื ืืืขื ืงืึธืืคึผืจืขืก ืืื / ืึธืืขืจ ืืึทื ืคืขืื ืืืึทืืืขืก ืืืืก ืคืื ืื ืืืฉ ืืื ืื ืจืืืขืจื ืืืขืจื ืืืขืจื ืืืืื ืืงืขืจ ืืื TOAST_TUPLE_TARGET ืืืืขืก (ืืืึทืืืขื ืืืขืจื, ืืืื ืืืืฉืึทืืืึทืื 2 ืงื) ืึธืืขืจ ืื ืืจืืืก ืงืขื ืขื ื ืื ืืืื ืจืืืืกื.
ืืืจ ืืึทืฉืืึธืกื ืึทื ืื ืืึทืื ืืืึธืก ืืืจ ืืืืฉืึทืืืึทืื ืืึธืื ืืื ืึธืืขืจ "ืืืืขืจ ืงืืจืฅ" ืึธืืขืจ "ืืืืขืจ ืืึทื ื", ืึทืืื ืืืจ ืืึทืฉืืึธืกื ืฆื ืืึทืืจืขื ืขืฆื ืืื ืฆื ืื ืืื ืืืื ืืขืืืขื ืืืขืจื:
ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);ืืึธืืืจ ืืขื ืืื ืื ื ืืึทืข ืกืขืืืื ืืก ืึทืคืขืงืืึทื ืืืกืง ืืึธืืืื ื ื ืึธื ืจืืงืึทื ืคืืืืขืจืืืฉืึทื:
ื ืืฉื ืฉืืขืื! ืืืจืืฉื ืืืืขื ืื ืจืื ืฆื ืื ืืืกืง ืืื ืืืงืจืืกื ืืขืขืจืขื 1.5 ืืื, ืืื ืืขืจ ืืืกืง "ืคืึทืจื ืืืขื" ืืื 20 ืคึผืจืึธืฆืขื ื! ืืืขืจ ืืคึฟืฉืจ ืืึธืก ืขืคืขืก ืึทืคืขืงืืึทื ืื ืงืคึผื?
ืขืก ืืื ืืืืฉ ื ืืฉื ืขืจืืขืจ ืืขืืืืจื. ืืึธืืฉ, ืขืก ืืื ืฉืืืขืจ ืฆื ืจืืืืขืจ ืืืื ืืคืืื ืึทืืึท ืืืึทืืืืื ื ืึธื ืงืขื ืขื ื ืืฉื ืืึทืคึผื ืื ืืืจืืฉื ืืืืขื ืงืคึผื ืืึทืกืข ืืขืืขืจ 5%.
ืืืจื ืืืืฉื ืื ืขืจืืขืจ ืคืื ืื ืืขืจืืื ืขื, ืื ืกืึทืืึทืงื ... ืขื ืืขืจืื ืืขื!
ืืื ืืืจ ืืืืกื, ืึท ืคึผืขื ื ืกืึทืืืขืก ืึท ืจืืื, ืืื ืืื ืืื ืืืขืจ ืกืืึธืจืืืืฉ ืืืึทืืืืื ืขืก ืืื ืืืขืื 10 ืื / ืืืืฉ ืืคืืื ืึท ืืืกื ืึทืคึผืืึทืืึทืืืืฉืึทื ืงืขื ืขื ืืขืื ืึท ืืื ื ืืฅ. ืืขืจืืืขืจ, ืืืจ ืืึทืฆืึธืื ืืคืืขืจืงืืึทืืงืืึทื ืฆื ืื ืืฉืืืืช ืกืืจืืงืืืจ ืคืื ืืื ืืืขืจ ืืึทืื - ืืื ืคึผืื ืงื "ืกืืึทืงื" ืคืขืืืขืจ ืืื ืื ืจืขืงืึธืจื ืืขืืขืจ ืคืื ืื ืืืฉื.
ืืืืึทื ืืืืึทื ืคืื ืืึธืก ืืื ืืืืึทื ืคืึธืจืืืก :
ืคืืืข ืึทืจืงืึทืืขืงืืฉืขืจื ืฆืืฉืืขืื ืืึทืื ืึทืืืื ืืึทื ื ืืืืฃ ืืึทืฉืื ืืืึธืจื ืืึทืื ืืจืื. ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืืืืฃ ืึท 32-ืืืกื ืงืก86 ืกืืกืืขื, ืื ืืึทืืืฉืขืจื (ืื ืืึทืืืฉืขืจ ืืืคึผ, 4 ืืืืขืก) ืืืขื ืืืื ืึทืืืื ื ืืืืฃ ืึท 4-ืืืืข ืืืึธืจื ืืจืขื ืขืฅ, ืืื ืืืขื ืืึธืคึผื ืคึผืื ืืืขืืงืืึทื ืคืืึธืืืื ื ืคืื ื ื ืืืขืจื (ืืึธืคึผื ืคึผืื ืืืขืืงืืึทื ืคืืึธืืืื ื ืคืื ื, 8 ืืืืขืก). ืืื ืืืืฃ ืึท 64-ืืืกื ืกืืกืืขื, ืืึธืคึผื ืืืึทืืืขืก ืืืขื ืืืื ืึทืืืื ื ืฆื 8-ืืืืข ืืืึธืจื ืืึทืื ืืจืื. ืืึธืก ืืื ืื ืื ืืขืจ ืกืืื ืคึฟืึทืจ ืื ืงืึทืืคึผืึทืืึทืืืืึทืื.
ืจืขืื ืฆื ืึทืืืื ืืึทื ื, ืื ืืจืืืก ืคืื ืึท ืืืฉ ืจืืืขืจื ืืขืคึผืขื ืืก ืืืืฃ ืื ืกืืจ ืคืื ืื ืคืขืืืขืจ. ืืืืฉืึทืืืึทืื ืืขื ืืืืจืงืื ื ืืื ื ืืฉื ืืืืขืจ ืืืืขืจืงื, ืึธืืขืจ ืืื ืขืืืขืืข ืงืึทืกืขืก ืขืก ืงืขื ืคืืจื ืฆื ืึท ืืึทืืืืืืง ืคืึทืจืืจืขืกืขืจื ืืื ืืจืืืก. ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืืืื ืืืจ ืืืฉื ืืฉืึทืจ (1) ืืื ืื ืืึทืืืฉืขืจ ืคืขืืืขืจ, ืขืก ืืืขื ืืืคึผืืงืื ืืืื 3 ืืืืขืก ืืืืืกืืึทื ืฆืืืืฉื ืืื.
ืืึธืืืจ ืึธื ืืืืื ืืื ืกืื ืืขืืืฉ ืืึธืืขืืก:
SELECT pg_column_size(ROW(
'0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 ะฑะฐะนั
SELECT pg_column_size(ROW(
'2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 ะฑะฐะนัืคึฟืื ืืืึทื ืขื ืืขืงืืืขื ืึท ืคึผืึธืจ ืขืงืกืืจืข ืืืืขืก ืืื ืืขืจ ืขืจืฉืืขืจ ืคืึทื? ืขืก ืืื ืคึผืฉืื - 2-ืืืืข ืกืืึธืืื ื ืึทืืืื ื ืืืืฃ 4-ืืืืข ืืจืขื ืขืฅ ืืืืืขืจ ืื ืืืืึทืืขืจ ืคืขืื, ืืื ืืืขื ืขืก ืืื ืื ืืขืฆืืข, ืขืก ืืื ืืึธืจื ืืฉื ืืื ื ืื ืืึทืจืคึฟื ืฆื ืืึทื ืจืืืขื.
ืืื ืืขืึธืจืืข, ืึทืืฅ ืืื ืืื ืืื ืืืจ ืงืขื ืขื ืจืืขืจืืื ืืืฉ ืื ืคืขืืืขืจ ืืื ืืืจ ืืืืื. ืืึธืืืจ ืงืึธื ืืจืึธืืืจื ืขืก ืืืืฃ ืคืึทืงืืืฉ ืืึทืื ืืื ืืขื ืืืึทืฉืคึผืื ืคืื ืืืื ืขืจ ืคืื ืื ืืืฉื, ืื ืืขืืืขื ืึธืคึผืืืืืื ื ืคืื ืืืึธืก ืึทืงืืึทืคึผืืื 10-15 ืื.
ืขืจืฉื ืกืืจืืงืืืจ:
CREATE TABLE public.plan_20190220
(
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: pack uuid NOT NULL,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: recno smallint NOT NULL,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: host uuid,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: ts timestamp with time zone,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: exectime numeric(32,3),
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: duration numeric(32,3),
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: bufint bigint,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: bufmem bigint,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: bufdsk bigint,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: apn uuid,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: ptr uuid,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: dt date,
CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)ืึธืคึผืืืืืื ื ื ืึธื ืืฉืึทื ืืื ื ืืืึทื ืกืืจ - ืคึผืื ืงื ืืขืืืืงืขืจ ืคืขืืืขืจ, ื ืึธืจ ืึทื ืืขืจืฉ ืกืืจ:
CREATE TABLE public.plan_20190221
(
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: dt date NOT NULL,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: ts timestamp with time zone,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: pack uuid NOT NULL,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: recno smallint NOT NULL,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: host uuid,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: apn uuid,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: ptr uuid,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: bufint bigint,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: bufmem bigint,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: bufdsk bigint,
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: exectime numeric(32,3),
-- ะฃะฝะฐัะปะตะดะพะฒะฐะฝะฐ from table plan: duration numeric(32,3),
CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)
ืื ืืึทื ืฅ ืืึทื ื ืคืื ืื ืึธืคึผืืืืืื ื ืืื ืืืฉืืืกื ืืืจื ืื ื ืืืขืจ ืคืื "ืคืืงืื" ืืื ืืขืคึผืขื ืืก ืืืืื ืืืืฃ ืคืื ืืจืืืกื ืืืง ืคึผืจืึทืกืขืกืึทื, ืึทืืื ืืึธืื ืืื ืื ืืืืื ืื ืืจืืืก ืคืื ืืขืจ ืงืืคึผืข (pg_relation_size) ืืืื ืื ื ืืืขืจ ืคืื ืจืขืงืึธืจืืก ืืื ืขืก - ืืึธืก ืืื, ืืืจ ืืึทืงืืืขื ืืืจืืฉื ืืืืขื ืืจืืืก ืคืื ืคืึทืงืืืฉ ืกืืึธืจื ืจืขืงืึธืจื:
ืืื ืืก 6% ืืึทื ื, ืืจืืืก !
ืึธืืขืจ ืึทืืฅ ืืื ืืึธื ื ืื ืึทืืื ืจืึธืืขืืืข - ื ืึธื ืึทืืข, ืืื ืื ืืขืงืกืื ืืืจ ืงืขื ืขื ื ืืฉื ืืืืฉื ืื ืกืืจ ืคืื ืคืขืืืขืจ, ืืื ืืขืจืืืขืจ "ืืืื" (pg_total_relation_size) ...
... ื ืึธื ืืึธ ืืืื ืืขืจืืืขืืืขื 1.5%ืึธื ืืฉืึทื ืืื ื ืึท ืืืื ืฉืืจื ืคืื ืงืึธื. ืื ืื!
ืืื ืืึธื ืึทื ืื ืืืืื ืึธืคึผืฆืืข ืคึฟืึทืจ ืขืจืืื ืืืฉืื ื ืคืขืืืขืจ ืืื ื ืืฉื ืืขืจ ืคืึทืงื ืึทื ืขืก ืืื ืื ืืขืจืกื ืึธืคึผืืืืึทื. ืืืืึทื ืืืจ ืืึธื ื ืืฉื ืืืขืื ืฆื "ืจืืึทืกื" ืขืืืขืืข ืืืึทืงืก ืคืื ืคืขืืืขืจ ืคึฟืึทืจ ืขืกืืขืืืฉ ืกืืืืช - ืืืฉื, ืึท ืคึผืึธืจ (pack, recno), ืืืึธืก ืืื ืื ืคึผืง ืคึฟืึทืจ ืืขื ืืืฉ.
ืืื ืึทืืืขืืืื, ืืึทืฉืืืืขื ืื "ืืื ืืืื" ืึธืจืืขื ืื ื ืคืื ืคืขืืืขืจ ืืื ืึท ืืึทื ืฅ ืคึผืฉืื "ืืจืื ืงืจืึทืคื" ืึทืจืืขื. ืืขืจืืืขืจ, ืืืจ ืงืขื ืขื ืืึทืงืืืขื ืืคืืื ืืขืกืขืจ ืจืขืืืืืึทืื ืคืื ืืืื ืืึทืื ืืื ืืื ืืืขืจ - ืคึผืจืึผืืื ืขืก!
ืืงืืจ: www.habr.com