https://postgres.ai/assets/js/cb62d495.75f3979a.js

1"use strict";(self.webpackChunkdatabase_lab_docs=self.webpackChunkdatabase_lab_docs||[]).push([[21910],{28453(e,t,n){n.d(t,{R:()=>o,x:()=>r});var s=n(96540);const i={},a=s.createContext(i);function o(e){const t=s.useContext(a);return s.useMemo(function(){return"function"==typeof e?e(t):{...t,...e}},[t,e])}function r(e){let t;return t=e.disableParentContext?"function"==typeof e.components?e.components(i):e.components||i:o(e.components),s.createElement(a.Provider,{value:t},e.children)}},84508(e,t,n){n.r(t),n.d(t,{assets:()=>l,contentTitle:()=>r,default:()=>h,frontMatter:()=>o,metadata:()=>s,toc:()=>d});const s=JSON.parse('{"id":"postgres-howtos/database-administration/backup-recovery/how-to-speed-up-bulk-load","title":"How to speed up bulk load","description":"","source":"@site/docs/postgres-howtos/database-administration/backup-recovery/how-to-speed-up-bulk-load.md","sourceDirName":"postgres-howtos/database-administration/backup-recovery","slug":"/postgres-howtos/database-administration/backup-recovery/how-to-speed-up-bulk-load","permalink":"/docs/postgres-howtos/database-administration/backup-recovery/how-to-speed-up-bulk-load","draft":false,"unlisted":false,"editUrl":"https://gitlab.com/postgres-ai/docs/-/edit/master/docs/postgres-howtos/database-administration/backup-recovery/how-to-speed-up-bulk-load.md","tags":[{"inline":true,"label":"intermediate","permalink":"/docs/tags/intermediate"},{"inline":true,"label":"data-loading","permalink":"/docs/tags/data-loading"},{"inline":true,"label":"performance","permalink":"/docs/tags/performance"},{"inline":true,"label":"backup","permalink":"/docs/tags/backup"},{"inline":true,"label":"configuration","permalink":"/docs/tags/configuration"},{"inline":true,"label":"wal","permalink":"/docs/tags/wal"}],"version":"current","frontMatter":{"title":"How to speed up bulk load","sidebar_label":"speed up bulk load","description":"","keywords":["postgresql","speed","bulk","load","intermediate"],"tags":["intermediate","data-loading","performance","backup","configuration","wal"],"difficulty":"intermediate","estimated_time":"5 min"},"sidebar":"baseSidebar","previous":{"title":"use pg_restore","permalink":"/docs/postgres-howtos/database-administration/backup-recovery/how-to-use-pg-restore"},"next":{"title":"Configuration","permalink":"/docs/postgres-howtos/database-administration/configuration/"}}');var i=n(74848),a=n(28453);const o={title:"How to speed up bulk load",sidebar_label:"speed up bulk load",description:"",keywords:["postgresql","speed","bulk","load","intermediate"],tags:["intermediate","data-loading","performance","backup","configuration","wal"],difficulty:"intermediate",estimated_time:"5 min"},r=void 0,l={},d=[{value:"1) COPY",id:"1-copy",level:2},{value:"2) Less frequent checkpoints",id:"2-less-frequent-checkpoints",level:2},{value:"3) Larger buffer pool",id:"3-larger-buffer-pool",level:2},{value:"4) No (or fewer) indexes",id:"4-no-or-fewer-indexes",level:2},{value:"5) No (or fewer) FKs and triggers",id:"5-no-or-fewer-fks-and-triggers",level:2},{value:"6) Avoiding WAL writes",id:"6-avoiding-wal-writes",level:2},{value:"7) Parallelization",id:"7-parallelization",level:2}];function c(e){const t={a:"a",code:"code",h2:"h2",li:"li",ol:"ol",p:"p",ul:"ul",...(0,a.R)(),...e.components};return(0,i.jsxs)(i.Fragment,{children:[(0,i.jsx)(t.p,{children:"If you need to load a lot of data, here are the tips that can help you do it faster."}),"\n",(0,i.jsx)(t.h2,{id:"1-copy",children:"1) COPY"}),"\n",(0,i.jsxs)(t.p,{children:["Use ",(0,i.jsx)(t.code,{children:"COPY"})," to load data, it's optimized for bulk load."]}),"\n",(0,i.jsx)(t.h2,{id:"2-less-frequent-checkpoints",children:"2) Less frequent checkpoints"}),"\n",(0,i.jsxs)(t.p,{children:["Consider increasing ",(0,i.jsx)(t.code,{children:"max_wal_size"})," and ",(0,i.jsx)(t.code,{children:"checkpoint_timeout"})," temporarily."]}),"\n",(0,i.jsx)(t.p,{children:"Changing them does not require a restart."}),"\n",(0,i.jsx)(t.p,{children:"Increased values lead to increased recovery time in case of failure, but the benefit is that checkpoints occur less often,\ntherefore:"}),"\n",(0,i.jsxs)(t.ol,{children:["\n",(0,i.jsx)(t.li,{children:"less stress on disk,"}),"\n",(0,i.jsx)(t.li,{children:"less WAL data is written, thanks to decreased number of full page writes of the same pages (when load happens with\nexisting indexes)."}),"\n"]}),"\n",(0,i.jsx)(t.h2,{id:"3-larger-buffer-pool",children:"3) Larger buffer pool"}),"\n",(0,i.jsxs)(t.p,{children:["Increase ",(0,i.jsx)(t.code,{children:"shared_buffers"}),", if you can."]}),"\n",(0,i.jsx)(t.h2,{id:"4-no-or-fewer-indexes",children:"4) No (or fewer) indexes"}),"\n",(0,i.jsxs)(t.p,{children:["If load happens into a new table, create indexes after data load. When loading into an existing table,\n",(0,i.jsx)(t.a,{href:"/docs/postgres-howtos/performance-optimization/indexing/over-indexing",children:"avoid over-indexing"}),"."]}),"\n",(0,i.jsx)(t.p,{children:"Every additional index will significantly slow down the load."}),"\n",(0,i.jsx)(t.h2,{id:"5-no-or-fewer-fks-and-triggers",children:"5) No (or fewer) FKs and triggers"}),"\n",(0,i.jsx)(t.p,{children:"Similarly to indexes, foreign key constraints and triggers may significantly slow down data load \u2013 consider (re)creating\

1nthem after the bulk load."}),"\n",(0,i.jsxs)(t.p,{children:["Triggers can be disabled via ",(0,i.jsx)(t.code,{children:"ALTER TABLE \u2026 DISABLE TRIGGERS ALL"})," \u2013 however, if triggers support some consistency\nchecks, you need to make sure that those checks are not violated (e.g., run additional checks after data load). FKs are\nimplemented via implicit triggers, and ",(0,i.jsx)(t.code,{children:"ALTER TABLE \u2026 DISABLE TRIGGERS ALL"})," disables them too \u2013 loading data in this\nstate should be done with care."]}),"\n",(0,i.jsx)(t.h2,{id:"6-avoiding-wal-writes",children:"6) Avoiding WAL writes"}),"\n",(0,i.jsx)(t.p,{children:"If this is a new table, consider completely avoiding WAL writes during the data load. Two options (both have limitations\nand require understanding that data can be lost if a crash happens):"}),"\n",(0,i.jsxs)(t.ul,{children:["\n",(0,i.jsxs)(t.li,{children:["\n",(0,i.jsxs)(t.p,{children:["Use unlogged table: ",(0,i.jsx)(t.code,{children:"CREATE UNLOGGED TABLE \u2026"}),". Unlogged tables are not archived, not replicated, they are not persistent (though, they survive normal restarts). However, converting an unlogged table to a normal one takes time (likely, a lot \u2013\xa0worth testing), because the data needs to be written to WAL. More about unlogged tables in ",(0,i.jsx)(t.a,{href:"https://crunchydata.com/blog/postgresl-unlogged-tables",children:"this post"}),"; also, see ",(0,i.jsx)(t.a,{href:"https://dba.stackexchange.com/questions/195780/set-postgresql-table-to-logged-after-data-loading/195829#195829",children:"this StackOverflow discussion"}),"."]}),"\n"]}),"\n",(0,i.jsxs)(t.li,{children:["\n",(0,i.jsxs)(t.p,{children:["Use ",(0,i.jsx)(t.code,{children:"COPY"})," with ",(0,i.jsx)(t.code,{children:"wal_level ='minimal'"}),". ",(0,i.jsx)(t.code,{children:"COPY"})," has to be executed inside the transaction that created the table.\nIn this case, due to ",(0,i.jsx)(t.code,{children:"wal_level ='minimal'"}),", ",(0,i.jsx)(t.code,{children:"COPY"})," writes won't be written to WAL\n(as of PG16, this is so only if the table is unpartitioned).\nAdditionally, consider using ",(0,i.jsx)(t.code,{children:"COPY (FREEZE)"})," \u2013 this approach also provides a benefit: all tuples\nare frozen after the data load. Setting ",(0,i.jsx)(t.code,{children:"wal_level='minimal'"}),", unfortunately, requires a restart, and additional\nchanges (",(0,i.jsx)(t.code,{children:"archive_mode = 'off'"}),", ",(0,i.jsx)(t.code,{children:"max_wal_senders = 0"}),"). Of course, this method doesn't work well in most of the\nproduction cases, but can be good for single-server setups. Details for the ",(0,i.jsx)(t.code,{children:"wal_level='minimal'"})," + ",(0,i.jsx)(t.code,{children:"COPY (FREEZE)"}),"\nrecipe in ",(0,i.jsx)(t.a,{href:"https://cybertec-postgresql.com/en/loading-data-in-the-most-efficient-way/",children:"this post"}),"."]}),"\n"]}),"\n"]}),"\n",(0,i.jsx)(t.h2,{id:"7-parallelization",children:"7) Parallelization"}),"\n",(0,i.jsx)(t.p,{children:"Consider parallelization. This may or may not speed up the process, depending on the bottlenecks of the single-threaded\nprocess (e.g., if single-threaded load saturates disk IO, parallelization won't help). Two options:"}),"\n",(0,i.jsxs)(t.ul,{children:["\n",(0,i.jsxs)(t.li,{children:["\n",(0,i.jsxs)(t.p,{children:["Partitioned tables and loading into multiple partitions using multiple workers\n(",(0,i.jsx)(t.a,{href:"/docs/postgres-howtos/database-administration/backup-recovery/how-to-use-pg-restore",children:"Day 20: pg_restore tips"}),")."]}),"\n"]}),"\n",(0,i.jsxs)(t.li,{children:["\n",(0,i.jsxs)(t.p,{children:["Unpartitioned table and loading in big chunks. Such chunks require preparation \u2013 it can be CSV split into\npieces, or exported ranges of table data using multiple synchronized ",(0,i.jsx)(t.code,{children:"REPEATABLE READ"})," transactions (working with the\nsame snapshot via ",(0,i.jsx)(t.code,{children:"SET TRANSACTION SNAPSHOT"}),"; see ",(0,i.jsx)(t.a,{href:"/docs/postgres-howtos/database-administration/backup-recovery/how-to-speed-up-pg-dump",children:"Day 8: How to speed up pg_dump"}),")."]}),"\n"]}),"\n"]}),"\n",(0,i.jsxs)(t.p,{children:["If you use TimescaleDB, consider ",(0,i.jsx)(t.a,{href:"https://github.com/timescale/timescaledb-parallel-copy",children:"timescaledb-parallel-copy"}),"."]}),"\n",(0,i.jsxs)(t.p,{children:["Last but not least: after a massive data load, don't forget to run ",(0,i.jsx)(t.code,{children:"ANALYZE"}),"."]})]})}function h(e={}){const{wrapper:t}={...(0,a.R)(),...e.components};return t?(0,i.jsx)(t,{...e,children:(0,i.jsx)(c,{...e})}):c(e)}}}]);