{"id":79039,"date":"2020-04-23T19:43:26","date_gmt":"2020-04-23T17:43:26","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql"},"modified":"2020-04-23T19:43:26","modified_gmt":"2020-04-23T17:43:26","slug":"ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sv\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","title":{"rendered":"Sparar en slant p\u00e5 stora volymer i PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Forts\u00e4tter \u00e4mnet att spela in stora datastreamar som lyftes <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/497008\/\">i den tidigare artikeln om partitionering<\/a><\/noindex>, h\u00e4r kommer vi att unders\u00f6ka s\u00e4tt att <b>minska den \"fysiska\" storleken p\u00e5 den lagrade<\/b> i PostgreSQL, och deras p\u00e5verkan p\u00e5 serverprestanda.<\/p>\n<p>Vi kommer att prata om <b>TOAST-inst\u00e4llningar och datajustering<\/b>. \"I genomsnitt\" kommer dessa metoder att spara inte s\u00e4rskilt mycket resurser, men \u2014 helt utan att modifiera applikationskoden.<\/p>\n<p><img decoding=\"async\" alt=\"Sparar en slant p\u00e5 stora volymer i PostgreSQL\" src=\"\/wp-content\/uploads\/2020\/04\/4d43b9b43edb10c4c8f13159c7dd1eac.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nDock har v\u00e5r erfarenhet varit mycket produktiv p\u00e5 detta omr\u00e5de, eftersom lagringen av n\u00e4stan vilken \u00f6vervakning som helst i sin natur \u00e4r <b>st\u00f6rsta delen append-only<\/b> ur synvinkel av de skrivna data. Och om du \u00e4r intresserad av hur man kan f\u00e5 databasen att skriva till disk ist\u00e4llet f\u00f6r <b>200MB\/s<\/b> halverat \u2014 v\u00e4nligen l\u00e4s vidare.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>Sm\u00e5 hemligheter om stora data<\/h2>\n<p>\nGenom arbetsprofilen <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/487380\/\">f\u00f6r v\u00e5r tj\u00e4nst<\/a><\/noindex>, f\u00e5r den regelbundet in fr\u00e5n loggarna <b>textpaket<\/b>.<\/p>\n<p>Och eftersom <noindex><a rel=\"nofollow\" href=\"https:\/\/sbis.ru\/all_services\">komplexa SBIS<\/a><\/noindex>, vars databaser vi \u00f6vervakar, \u2014 \u00e4r en flerkomponentprodukt med komplexa datastukturer, s\u00e5 blir \u00e4ven f\u00f6rfr\u00e5gningarna <b>f\u00f6r att uppn\u00e5 maximal prestanda<\/b> ganska <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/486072\/\">\"multivolymer\" med komplex algoritmisk logik<\/a><\/noindex>. S\u00e5 \u00e4ven volymen av varje enskild instans av en f\u00f6rfr\u00e5gan eller den resulterande k\u00f6rplanen i loggen vi f\u00e5r \u00e4r \"i genomsnitt\" ganska stor.<\/p>\n<p>L\u00e5t oss titta p\u00e5 strukturen av en av tabellerna d\u00e4r vi skriver \"r\u00e5data\" \u2014 det vill s\u00e4ga den ursprungliga texten direkt fr\u00e5n loggposten:<\/p>\n<pre><code class=\"sql\">CREATE TABLE rawdata_orig(\n  pack -- PK\n    uuid NOT NULL\n, recno -- PK\n    smallint NOT NULL\n, dt -- sektionens nyckel\n    date\n, data -- det viktigaste\n    text\n, PRIMARY KEY(pack, recno)\n);<\/code><\/pre>\n<p>\nEn typisk s\u00e5dan tabell (redan partitionerad, s\u00e5klart, d\u00e4rf\u00f6r \u00e4r detta \u2014 en sektionmall), d\u00e4r det viktigaste \u00e4r\u6587\u672c. Ibland ganska omfattande.<\/p>\n<p>Kom ih\u00e5g att den \"fysiska\" storleken p\u00e5 en post i PG inte kan \u00f6verstiga en databasida, men den \"logiska\" storleken \u2014 \u00e4r n\u00e5got helt annat. F\u00f6r att skriva ett stort v\u00e4rde i ett f\u00e4lt (varchar\/text\/bytea) anv\u00e4nds <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/storage-toast\">TOAST-teknologin<\/a><\/noindex>:<\/p>\n<blockquote><p>PostgreSQL \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442 \u0444\u0438\u043a\u0441\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0440\u0430\u0437\u043c\u0435\u0440 \u0441\u0442\u0440\u0430\u043d\u0438\u0446\u044b (\u043e\u0431\u044b\u0447\u043d\u043e 8 \u041a\u0411), \u0438 \u043d\u0435 \u043f\u043e\u0437\u0432\u043e\u043b\u044f\u0435\u0442 \u043a\u043e\u0440\u0442\u0435\u0436\u0430\u043c \u0437\u0430\u043d\u0438\u043c\u0430\u0442\u044c \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0441\u0442\u0440\u0430\u043d\u0438\u0446. \u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u043d\u0435\u043f\u043e\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u0445\u0440\u0430\u043d\u0438\u0442\u044c \u043e\u0447\u0435\u043d\u044c \u0431\u043e\u043b\u044c\u0448\u0438\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u043f\u043e\u043b\u0435\u0439 \u043d\u0435\u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e. \u0414\u043b\u044f \u043f\u0440\u0435\u043e\u0434\u043e\u043b\u0435\u043d\u0438\u044f \u044d\u0442\u043e\u0433\u043e \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u044f \u0431\u043e\u043b\u044c\u0448\u0438\u0435 \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u043f\u043e\u043b\u0435\u0439 \u0441\u0436\u0438\u043c\u0430\u044e\u0442\u0441\u044f \u0438\/\u0438\u043b\u0438 \u0440\u0430\u0437\u0431\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u043d\u0430 \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0444\u0438\u0437\u0438\u0447\u0435\u0441\u043a\u0438\u0445 \u0441\u0442\u0440\u043e\u043a. \u042d\u0442\u043e \u043f\u0440\u043e\u0438\u0441\u0445\u043e\u0434\u0438\u0442 \u043d\u0435\u0437\u0430\u043c\u0435\u0442\u043d\u043e \u0434\u043b\u044f \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f \u0438 \u043d\u0430 \u0431\u043e\u043b\u044c\u0448\u0443\u044e \u0447\u0430\u0441\u0442\u044c \u043a\u043e\u0434\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0430 \u0432\u043b\u0438\u044f\u0435\u0442 \u043d\u0435\u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e. \u042d\u0442\u043e\u0442 \u043c\u0435\u0442\u043e\u0434 \u0438\u0437\u0432\u0435\u0441\u0442\u0435\u043d \u043a\u0430\u043a TOAST &#8230;<\/p><\/blockquote>\n<p>\nFaktiskt, f\u00f6r varje tabell med \"potentiellt stora\" f\u00e4lt skapas automatiskt <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/storage-toast#STORAGE-TOAST-ONDISK\">en par-tabell med \"skivor\"<\/a><\/noindex> av varje \"stor\" post i segment om 2KB:<\/p>\n<pre><code class=\"sql\">TOAST(\n  chunk_id\n    integer\n, chunk_seq\n    integer\n, chunk_data\n    bytea\n, PRIMARY KEY(chunk_id, chunk_seq)\n);<\/code><\/pre>\n<p>\nDet vill s\u00e4ga om vi beh\u00f6ver skriva en rad med ett \"stort\" v\u00e4rde <code>data<\/code>, s\u00e5 kommer den verkliga posten att ske <b>inte bara i den prim\u00e4ra tabellen och dess PK, utan \u00e4ven i TOAST och dess PK<\/b>.<\/p>\n<h4>Minska p\u00e5verkan av TOAST<\/h4>\n<p>\nMen de flesta poster \u00e4r inte s\u00e5 stora, <b>i 8KB b\u00f6r de f\u00e5 plats<\/b> \u2014 hur kan vi spara p\u00e5 detta?..<\/p>\n<p>H\u00e4r kommer hj\u00e4lp av attributet <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/storage-toast#STORAGE-TOAST-ONDISK\"><code>STORAGE<\/code><\/a><\/noindex> hos tabellens kolumn:<\/p>\n<blockquote>\n<ul>\n<li><b>EXTENDED<\/b> till\u00e5ter b\u00e5de komprimering och separat lagring. Detta <b>\u00e4r den standardalternativ<\/b> f\u00f6r de flesta datatyper som \u00e4r kompatibla med TOAST. F\u00f6rst g\u00f6rs ett f\u00f6rs\u00f6k att komprimera, sedan - lagras utanf\u00f6r tabellen, om raden fortfarande \u00e4r f\u00f6r stor.<\/li>\n<li><b>MAIN<\/b> till\u00e5ter komprimering men inte separat lagring. (Faktiskt, separat lagring kommer att utf\u00f6ras f\u00f6r s\u00e5dana kolumner, men endast <b>som en sista \u00e5tg\u00e4rd<\/b>, n\u00e4r det inte finns n\u00e5got annat s\u00e4tt att minska raden s\u00e5 att den f\u00e5r plats p\u00e5 sidan.)<\/li>\n<\/ul>\n<\/blockquote>\n<p>Faktiskt \u00e4r detta precis vad vi beh\u00f6ver f\u00f6r text \u2014 <b>komprimera maximalt, och om det verkligen inte g\u00e5r in \u2014 flytta till TOAST<\/b>. Detta kan g\u00f6ras direkt \"i farten\", med ett kommando:<\/p>\n<pre><code class=\"sql\">ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;<\/code><\/pre>\n<p><\/p>\n<h4>Hur utv\u00e4rdera effekten<\/h4>\n<p>\nEftersom datam\u00e4ngden f\u00f6r\u00e4ndras varje dag kan vi inte j\u00e4mf\u00f6ra absoluta siffror, men i relativa termer \u00e4r ju <b>ju mindre andel<\/b> vi skriver till TOAST \u2014 desto b\u00e4ttre. Men h\u00e4r finns en risk \u2013 ju mer \"fysisk\" volym varje enskild post har, desto \"bredare\" blir indexet, eftersom det m\u00e5ste t\u00e4cka ett st\u00f6rre antal datakoder.<\/p>\n<p>Sektion <b>f\u00f6re \u00e4ndringar<\/b>:<\/p>\n<pre><code class=\"plaintext\">heap  = 37GB (39%)\nTOAST = 54GB (57%)\nPK    =  4GB ( 4%)\n<\/code><\/pre>\n<p>\nSektion <b>efter \u00e4ndringar<\/b>:<\/p>\n<pre><code class=\"plaintext\">heap  = 37GB (67%)\nTOAST = 16GB (29%)\nPK    =  2GB ( 4%)<\/code><\/pre>\n<p>\nFaktiskt har vi <b>b\u00f6rjat skriva till TOAST dubbelt s\u00e5 s\u00e4llan<\/b>, vilket lett till att b\u00e5de disken och CPU:n har blivit avlastade:<\/p>\n<p><img decoding=\"async\" alt=\"Sparar en slant p\u00e5 stora volymer i PostgreSQL\" src=\"\/wp-content\/uploads\/2020\/04\/547485eff9c6491ffe4d59e5c81f656d.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<img decoding=\"async\" alt=\"Sparar en slant p\u00e5 stora volymer i PostgreSQL\" src=\"\/wp-content\/uploads\/2020\/04\/6bd3b18146c20959693f961e41fff447.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nJag vill p\u00e5minna om att vi ocks\u00e5 har b\u00f6rjat \"l\u00e4sa\" disken mindre, inte bara \"skriva\" \u2014 eftersom n\u00e4r en post ska infogas i en tabell m\u00e5ste en del av tr\u00e4det f\u00f6r varje index \"l\u00e4sa\" f\u00f6r att best\u00e4mma dess framtida position.<\/p>\n<h2>Vem m\u00e5r bra av PostgreSQL 11<\/h2>\n<p>\nEfter uppgraderingen till PG11 besl\u00f6t vi att forts\u00e4tta \"justeringen\" av TOAST och noterade att en inst\u00e4llning blev tillg\u00e4nglig f\u00f6r konfiguration fr\u00e5n och med denna version. <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/11\/storage-toast#STORAGE-TOAST-ONDISK\"><code>toast_tuple_target<\/code><\/a><\/noindex>:<\/p>\n<blockquote><p>Koden f\u00f6r TOAST-processingen aktiveras endast n\u00e4r storleken p\u00e5 str\u00e4ngv\u00e4rdet som ska lagras i tabellen \u00f6verskrider TOAST_TUPLE_THRESHOLD byte (vanligtvis 2 KB). TOAST-koden kommer att komprimera och\/eller flytta f\u00e4ltv\u00e4rden utanf\u00f6r tabellen tills str\u00e4ngv\u00e4rdet \u00e4r mindre \u00e4n TOAST_TUPLE_TARGET byte (en variabel storlek, oftast ocks\u00e5 2 KB) eller tills volymreducering inte l\u00e4ngre \u00e4r m\u00f6jlig.<\/p><\/blockquote>\n<p>Vi kom fram till att v\u00e5ra data vanligtvis antingen \u00e4r \"mycket korta\" eller \"v\u00e4ldigt l\u00e5nga\", s\u00e5 vi beslutade att h\u00e5lla oss till det minimala m\u00f6jliga v\u00e4rdet:<\/p>\n<pre><code class=\"sql\">ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);<\/code><\/pre>\n<p>\nL\u00e5t oss se hur de nya inst\u00e4llningarna p\u00e5verkade diskbelastningen efter omkonfigureringen:<\/p>\n<p><img decoding=\"async\" alt=\"Sparar en slant p\u00e5 stora volymer i PostgreSQL\" src=\"\/wp-content\/uploads\/2020\/04\/ccaa4879e413a566b362d01582eb8c19.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nInte illa! Den genomsnittliga <b>diskk\u00f6en minskade<\/b> ungef\u00e4r med 1,5 g\u00e5nger, och \"bel\u00e4ggningen\" p\u00e5 disken minskade med cirka 20%! Men kanske det p\u00e5verkar CPU:n p\u00e5 n\u00e5got s\u00e4tt?<\/p>\n<p><img decoding=\"async\" alt=\"Sparar en slant p\u00e5 stora volymer i PostgreSQL\" src=\"\/wp-content\/uploads\/2020\/04\/f7026e5809853d35c6fb7a8376b9f369.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nI alla fall blev det definitivt inte s\u00e4mre. \u00c4ven om det \u00e4r sv\u00e5rt att avg\u00f6ra, eftersom dessa volymer \u00e4nd\u00e5 inte kan h\u00f6ja den genomsnittliga CPU-belastningen \u00f6ver <b>5%<\/b>.<\/p>\n<h2>Byter man platser p\u00e5 summorna... f\u00f6r\u00e4ndras summan!<\/h2>\n<p>\nSom bekant sparar en k\u00e5k en rubel, och med v\u00e5ra lagringsvolymer p\u00e5 omkring <b>10TB\/m\u00e5nad<\/b> kan \u00e4ven en liten optimering ge ganska bra vinster. D\u00e4rf\u00f6r uppm\u00e4rksammade vi den fysiska strukturen av v\u00e5ra data \u2014 hur exakt <b>\"f\u00e4lten\" ligger inom varje post<\/b> i varje tabell.<\/p>\n<p>Eftersom p\u00e5 grund av <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/postgrespro\/blog\/444536\/\">datajustering<\/a><\/noindex> detta direkt <noindex><a rel=\"nofollow\" href=\"https:\/\/docs.gitlab.com\/ee\/development\/ordering_table_columns.html\">p\u00e5verkar den resulterande volymen<\/a><\/noindex>:<\/p>\n<blockquote><p>M\u00e5nga arkitekturer f\u00f6ruts\u00e4tter datajustering vid maskinordningsgr\u00e4nser. Till exempel, p\u00e5 ett 32-bitarssystem x86 kommer heltal (typ integer, som tar 4 byte) att justeras enligt 4-byteordningsgr\u00e4nser, precis som flyttal med dubbel precision (typ double precision, 8 byte). Och p\u00e5 ett 64-bitarssystem kommer double-v\u00e4rden att justeras enligt 8-byteordningsgr\u00e4nser. Det h\u00e4r \u00e4r ytterligare en orsak till inkompatibilitet.<\/p>\n<p>P\u00e5 grund av justeringen beror storleken p\u00e5 tabellraden p\u00e5 ordningen av f\u00e4lten. Vanligtvis \u00e4r denna effekt inte mycket m\u00e4rkbar, men i vissa fall kan den leda till en betydande \u00f6kning av storleken. Till exempel, om man blandar f\u00e4lten av typen char(1) och integer, kommer det vanligtvis att g\u00e5 f\u00f6rlorade 3 byte mellan dem.<\/p><\/blockquote>\n<p>\nL\u00e5t oss b\u00f6rja med syntetiska modeller:<\/p>\n<pre><code class=\"sql\">SELECT pg_column_size(ROW(\n  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid\n, 0::smallint\n, '2019-01-01'::date\n));\n-- 48 byte\n\nSELECT pg_column_size(ROW(\n  '2019-01-01'::date\n, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid\n, 0::smallint\n));\n-- 46 byte<\/code><\/pre>\n<p>\nVarifr\u00e5n kom de extra 2 byten i det f\u00f6rsta fallet? Det \u00e4r enkelt \u2014 <b>2-byte smallint justeras till en 4-byte gr\u00e4ns<\/b> f\u00f6re n\u00e4sta f\u00e4lt, och n\u00e4r det st\u00e5r sist beh\u00f6ver man inte justera n\u00e5got.<\/p>\n<p>I teorin \u00e4r allt bra och man kan flytta f\u00e4lten hur som helst. L\u00e5t oss kontrollera p\u00e5 verkliga data som ett exempel fr\u00e5n en av tabellerna vars dagliga sektion tar upp 10-15GB.<\/p>\n<p>Den ursprungliga strukturen:<\/p>\n<pre><code class=\"sql\">CREATE TABLE public.plan_20190220\n(\n-- \u00c4rvt fr\u00e5n tabell plan:  pack uuid NOT NULL,\n-- \u00c4rvt fr\u00e5n tabell plan:  recno smallint NOT NULL,\n-- \u00c4rvt fr\u00e5n tabell plan:  host uuid,\n-- \u00c4rvt fr\u00e5n tabell plan:  ts timestamp with time zone,\n-- \u00c4rvt fr\u00e5n tabell plan:  exectime numeric(32,3),\n-- \u00c4rvt fr\u00e5n tabell plan:  duration numeric(32,3),\n-- \u00c4rvt fr\u00e5n tabell plan:  bufint bigint,\n-- \u00c4rvt fr\u00e5n tabell plan:  bufmem bigint,\n-- \u00c4rvt fr\u00e5n tabell plan:  bufdsk bigint,\n-- \u00c4rvt fr\u00e5n tabell plan:  apn uuid,\n-- \u00c4rvt fr\u00e5n tabell plan:  ptr uuid,\n-- \u00c4rvt fr\u00e5n tabell plan:  dt date,\n  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),\n  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),\n  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)\n)\nINHERITS (public.plan)<\/code><\/pre>\n<p>\nSektionen efter att kolumnerna har bytt ordning \u2014 precis <b>samma f\u00e4lt, bara en annan ordning<\/b>:<\/p>\n<pre><code class=\"sql\">CREATE TABLE public.plan_20190221\n(\n-- \u00c4rvt fr\u00e5n tabell plan:  dt date NOT NULL,\n-- \u00c4rvt fr\u00e5n tabell plan:  ts timestamp with time zone,\n-- \u00c4rvt fr\u00e5n tabell plan:  pack uuid NOT NULL,\n-- \u00c4rvt fr\u00e5n tabell plan:  recno smallint NOT NULL,\n-- \u00c4rvt fr\u00e5n tabell plan:  host uuid,\n-- \u00c4rvt fr\u00e5n tabell plan:  apn uuid,\n-- \u00c4rvt fr\u00e5n tabell plan:  ptr uuid,\n-- \u00c4rvt fr\u00e5n tabell plan:  bufint bigint,\n-- \u00c4rvt fr\u00e5n tabell plan:  bufmem bigint,\n-- \u00c4rvt fr\u00e5n tabell plan:  bufdsk bigint,\n-- \u00c4rvt fr\u00e5n tabell plan:  exectime numeric(32,3),\n-- \u00c4rvt fr\u00e5n tabell plan:  duration numeric(32,3),\n  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),\n  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),\n  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)\n)\nINHERITS (public.plan)<\/code><\/pre>\n<p>\nDen totala volymen av sektionen best\u00e4ms av antalet \"fakta\" och beror endast p\u00e5 externa processer, s\u00e5 l\u00e5t oss dela storleken p\u00e5 heap (<code>pg_relation_size<\/code>) p\u00e5 antalet poster i den \u2014 s\u00e5 vi f\u00e5r <b>den genomsnittliga storleken p\u00e5 en verklig lagrad post<\/b>:<\/p>\n<p><img decoding=\"async\" alt=\"Sparar en slant p\u00e5 stora volymer i PostgreSQL\" src=\"\/wp-content\/uploads\/2020\/04\/06be2d7d70d223e7678f9a478e4c293f.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<b>Minus 6% volym<\/b>, bra!<\/p>\n<p>Men allt \u00e4r givetvis inte s\u00e5 positivt \u2014 f\u00f6r <b>i indexen kan vi inte \u00e4ndra ordningen p\u00e5 f\u00e4lten<\/b>, och d\u00e4rf\u00f6r \"i stort\" (<code>pg_total_relation_size<\/code>)\u2026<\/p>\n<p><img decoding=\"async\" alt=\"Sparar en slant p\u00e5 stora volymer i PostgreSQL\" src=\"\/wp-content\/uploads\/2020\/04\/8beff38e5034fc0d40665b75cb3b3625.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n\u2026 \u00e4nd\u00e5 h\u00e4r <b>sparade vi 1.5%<\/b>, utan att \u00e4ndra en enda kodrad. Precis s\u00e5!<\/p>\n<p><img decoding=\"async\" alt=\"Sparar en slant p\u00e5 stora volymer i PostgreSQL\" src=\"\/wp-content\/uploads\/2020\/04\/10f4a2151465a38bf45823a5d360302b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>Jag p\u00e5pekar att den ovanst\u00e5ende arrangemanget av f\u00e4lten \u2014 inte n\u00f6dv\u00e4ndigtvis den mest optimala. Eftersom vissa f\u00e4ltblock vill man inte \"bryta\" av estetiska sk\u00e4l \u2014 till exempel paret <code>(pack, recno)<\/code>, som \u00e4r PK f\u00f6r denna tabell.<\/p>\n<p>I stort sett \u00e4r definitionen av den \"minimala\" arrangemanget av f\u00e4lt en ganska enkel \"brute force\"-uppgift. S\u00e5 ni kan p\u00e5 era data f\u00e5 resultat till och med b\u00e4ttre \u00e4n v\u00e5ra \u2014 ge det ett f\u00f6rs\u00f6k!<br \/>\n<br \/>K\u00e4lla: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/498292\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u044f \u0442\u0435\u043c\u0443 \u0437\u0430\u043f\u0438\u0441\u0438 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043f\u043e\u0442\u043e\u043a\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445, \u043f\u043e\u0434\u043d\u044f\u0442\u0443\u044e \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0435\u0439 \u043f\u0440\u043e \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435, \u0432 \u044d\u0442\u043e\u0439 \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0438\u043c \u0441\u043f\u043e\u0441\u043e\u0431\u044b, \u043a\u043e\u0442\u043e\u0440\u044b\u043c\u0438 \u043c\u043e\u0436\u043d\u043e \u0443\u043c\u0435\u043d\u044c\u0448\u0438\u0442\u044c \u00ab\u0444\u0438\u0437\u0438\u0447\u0435\u0441\u043a\u0438\u0439\u00bb \u0440\u0430\u0437\u043c\u0435\u0440 \u0445\u0440\u0430\u043d\u0438\u043c\u043e\u0433\u043e \u0432 PostgreSQL, \u0438 \u0438\u0445 \u0432\u043b\u0438\u044f\u043d\u0438\u0435 \u043d\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u0441\u0435\u0440\u0432\u0435\u0440\u0430. \u0420\u0435\u0447\u044c \u043f\u043e\u0439\u0434\u0435\u0442 \u043f\u0440\u043e \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0438 TOAST \u0438 \u0432\u044b\u0440\u0430\u0432\u043d\u0438\u0432\u0430\u043d\u0438\u0435 \u0434\u0430\u043d\u043d\u044b\u0445. \u00ab\u0412 \u0441\u0440\u0435\u0434\u043d\u0435\u043c\u00bb \u044d\u0442\u0438 \u0441\u043f\u043e\u0441\u043e\u0431\u044b \u043f\u043e\u0437\u0432\u043e\u043b\u044f\u0442 \u0441\u044d\u043a\u043e\u043d\u043e\u043c\u0438\u0442\u044c \u043d\u0435 \u0441\u043b\u0438\u0448\u043a\u043e\u043c \u043c\u043d\u043e\u0433\u043e \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0432, \u0437\u0430\u0442\u043e \u2014 \u0432\u043e\u043e\u0431\u0449\u0435 \u0431\u0435\u0437 \u043c\u043e\u0434\u0438\u0444\u0438\u043a\u0430\u0446\u0438\u0438 \u043a\u043e\u0434\u0430 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f. \u041e\u0434\u043d\u0430\u043a\u043e, [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":79040,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-79039","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.4 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u044f \u0442\u0435\u043c\u0443 \u0437\u0430\u043f\u0438\u0441\u0438 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043f\u043e\u0442\u043e\u043a\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445, \u043f\u043e\u0434\u043d\u044f\u0442\u0443\u044e \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0435\u0439 \u043f\u0440\u043e \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435, \u0432 \u044d\u0442\u043e\u0439 \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0438\u043c \u0441\u043f\u043e\u0441\u043e\u0431\u044b, \u043a\u043e\u0442\u043e\u0440\u044b\u043c\u0438 \u043c\u043e\u0436\u043d\u043e.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/sv\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.4\" \/>\n\t\t<meta property=\"og:locale\" content=\"sv_SE\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u042d\u043a\u043e\u043d\u043e\u043c\u0438\u043c \u043a\u043e\u043f\u0435\u0435\u0447\u043a\u0443 \u043d\u0430 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043e\u0431\u044a\u0435\u043c\u0430\u0445 \u0432 PostgreSQL | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u044f \u0442\u0435\u043c\u0443 \u0437\u0430\u043f\u0438\u0441\u0438 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043f\u043e\u0442\u043e\u043a\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445, \u043f\u043e\u0434\u043d\u044f\u0442\u0443\u044e \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0435\u0439 \u043f\u0440\u043e \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435, \u0432 \u044d\u0442\u043e\u0439 \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0438\u043c \u0441\u043f\u043e\u0441\u043e\u0431\u044b, \u043a\u043e\u0442\u043e\u0440\u044b\u043c\u0438 \u043c\u043e\u0436\u043d\u043e.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/sv\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-04-23T17:43:26+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-04-23T17:43:26+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Spara pengar p\u00e5 stora volymer i PostgreSQL | ProHoster","description":"Som en fortsatt diskussion om registrering av stora datam\u00e4ngder, som togs upp i den f\u00f6reg\u00e5ende artikeln om partitionering, kommer vi att utforska metoder f\u00f6r hur man kan.","canonical_url":"https:\/\/prohoster.info\/sv\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sv_SE","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u042d\u043a\u043e\u043d\u043e\u043c\u0438\u043c \u043a\u043e\u043f\u0435\u0435\u0447\u043a\u0443 \u043d\u0430 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043e\u0431\u044a\u0435\u043c\u0430\u0445 \u0432 PostgreSQL | ProHoster","og:description":"\u041f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u044f \u0442\u0435\u043c\u0443 \u0437\u0430\u043f\u0438\u0441\u0438 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043f\u043e\u0442\u043e\u043a\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445, \u043f\u043e\u0434\u043d\u044f\u0442\u0443\u044e \u043f\u0440\u0435\u0434\u044b\u0434\u0443\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0435\u0439 \u043f\u0440\u043e \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435, \u0432 \u044d\u0442\u043e\u0439 \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0438\u043c \u0441\u043f\u043e\u0441\u043e\u0431\u044b, \u043a\u043e\u0442\u043e\u0440\u044b\u043c\u0438 \u043c\u043e\u0436\u043d\u043e.","og:url":"https:\/\/prohoster.info\/sv\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-04-23T17:43:26+00:00","article:modified_time":"2020-04-23T17:43:26+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"79039","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 16:46:33","updated":"2022-09-28 06:02:32","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts\/79039","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/comments?post=79039"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts\/79039\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media\/79040"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media?parent=79039"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/categories?post=79039"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/tags?post=79039"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}