1"use strict";(self.webpackChunkdatabase_lab_docs=self.webpackChunkdatabase_lab_docs||[]).push([[21994],{7177(e,t,s){s.d(t,{K:()=>w});var n=s(96540),a=s(99312),i=s(74848);const o=e=>(0,i.jsx)("svg",Object.assign({width:"18",height:"18",viewBox:"0 0 18 18",fill:"none",xmlns:"http://www.w3.org/2000/svg"},e,{children:(0,i.jsx)("path",{d:"M9 0C4.0302 0 0 4.0302 0 9C0 13.9698 4.0302 18 9 18C13.9698 18 18 13.9698 18 9C18 4.0302 13.9698 0 9 0ZM13.1093 7.01724C13.1133 7.10582 13.1152 7.19481 13.1152 7.28421C13.1152 10.0139 11.0374 13.1616 7.23766 13.1618C6.07104 13.1618 4.98546 12.8198 4.07126 12.2338C4.23289 12.2529 4.39742 12.2624 4.564 12.2624C5.53189 12.2624 6.42261 11.9323 7.12971 11.3781C6.2254 11.3614 5.46295 10.7641 5.19983 9.94331C5.32576 9.96748 5.45526 9.98067 5.58806 9.98067C5.77661 9.98067 5.95926 9.95526 6.13284 9.90788C5.18761 9.71864 4.47556 8.88327 4.47556 7.88297C4.47556 7.87363 4.47556 7.86525 4.47583 7.8566C4.7542 8.01137 5.07253 8.10448 5.41145 8.11491C4.85678 7.74481 4.49217 7.11214 4.49217 6.39542C4.49217 6.01694 4.59448 5.66235 4.77191 5.35707C5.79062 6.60704 7.31305 7.42909 9.02994 7.51547C8.99451 7.36414 8.97624 7.20648 8.97624 7.04443C8.97624 5.90405 9.90143 4.97887 11.0422 4.97887C11.6364 4.97887 12.1731 5.23004 12.5501 5.63159C13.0207 5.53876 13.4626 5.36682 13.8619 5.1302C13.7074 5.61237 13.38 6.01694 12.9534 6.27278C13.3713 6.22279 13.7696 6.11197 14.1397 5.94745C13.8632 6.36177 13.5126 6.72569 13.1093 7.01724Z",fill:"currentColor"})})),l=()=>(0,i.jsxs)("svg",{width:"18",height:"18",viewBox:"0 0 18 18",fill:"none",xmlns:"http://www.w3.org/2000/svg",children:[(0,i.jsx)("path",{d:"M8.81657 14.2023C8.81752 14.2032 8.81847 14.204 8.81945 14.2048C8.82084 14.2059 8.8223 14.207 8.82377 14.208L8.82605 14.2096L8.82472 14.2085C8.81676 14.2024 8.80915 14.1959 8.80191 14.189L8.80074 14.188L8.79986 14.1872C8.80324 14.1906 8.80678 14.1938 8.81038 14.1969C8.81221 14.1986 8.81408 14.2002 8.81597 14.2018L8.81657 14.2023Z",fill:"currentColor"}),(0,i.jsx)("path",{d:"M9.14091 14.1983L9.14247 14.1969C9.14601 14.1938 9.14949 14.1907 9.15283 14.1874L9.1517 14.1884L9.15078 14.1891C9.14361 14.1961 9.13584 14.2027 9.12788 14.2087L9.12677 14.2097C9.12787 14.2088 9.12899 14.2081 9.13009 14.2073C9.13119 14.2065 9.13227 14.2057 9.13331 14.2048C9.13426 14.204 9.13518 14.2032 9.13616 14.2024L9.13682 14.2018C9.13821 14.2006 9.13955 14.1995 9.14091 14.1983Z",fill:"currentColor"}),(0,i.jsx)("path",{"fill-rule":"evenodd","clip-rule":"evenodd",d:"M9 18C13.9706 18 18 13.9706 18 9C18 4.02944 13.9706 0 9 0C4.02944 0 0 4.02944 0 9C0 13.9706 4.02944 18 9 18ZM13.7284 8.12794L14.2985 9.88984C14.3845 10.1559 14.2911 10.4449 14.0661 10.6092L9.12475 14.2115C9.12402 14.212 9.12317 14.2123 9.12244 14.2129L9.12194 14.2132L9.12001 14.2144C9.1124 14.2197 9.1045 14.2245 9.09635 14.229C9.09518 14.2296 9.09411 14.2305 9.09294 14.2311L9.09083 14.2323L9.08918 14.233L9.08798 14.2335C9.08703 14.2339 9.08599 14.2342 9.08501 14.2347C9.0829 14.2357 9.08074 14.2366 9.07857 14.2375C9.07664 14.2383 9.0747 14.239 9.07279 14.2398C9.0712 14.2405 9.06964 14.2412 9.06808 14.2419C9.06531 14.2431 9.06255 14.2443 9.05968 14.2453L9.05914 14.2455L9.05712 14.2464L9.05619 14.2467C9.05561 14.247 9.05503 14.2472 9.05444 14.2474L9.05336 14.2476C9.05274 14.2478 9.05211 14.2479 9.05149 14.2481C9.05093 14.2482 9.05037 14.2483 9.04982 14.2485L9.04859 14.2488C9.04269 14.2505 9.03662 14.2518 9.03059 14.2532C9.02935 14.2534 9.02814 14.2538 9.02692 14.2541C9.02572 14.2544 9.02452 14.2547 9.02329 14.255L9.01997 14.2558C9.01927 14.2559 9.01859 14.2561 9.01791 14.2562C9.0173 14.2564 9.01668 14.2565 9.01605 14.2566C9.01501 14.2568 9.01391 14.2567 9.01287 14.2569L9.01217 14.2569C9.00643 14.2577 9.00055 14.2584 8.99464 14.2589C8.98851 14.2594 8.98233 14.2597 8.97619 14.2597C8.96416 14.2597 8.95218 14.2586 8.94053 14.2569L8.93999 14.2569C8.93923 14.2567 8.93845 14.2567 8.93768 14.2567C8.9374 14.2567 8.93711 14.2566 8.93683 14.2566C8.9361 14.2565 8.93538 14.2563 8.93467 14.2562C8.9341 14.256 8.93352 14.2559 8.93294 14.2558C8.93212 14.2557 8.93061 14.2552 8.93061 14.2552L8.92957 14.255C8.92842 14.2547 8.9273 14.2545 8.92619 14.2542C8.92494 14.2538 8.92369 14.2535 8.92242 14.2532C8.91969 14.2526 8.91694
114.252 8.91421 14.2514C8.91083 14.2506 8.90748 14.2497 8.9042 14.2487L8.90287 14.2484C8.90236 14.2483 8.90182 14.2482 8.90129 14.248C8.90067 14.2479 8.90005 14.2478 8.89946 14.2476L8.89842 14.2473C8.89784 14.2472 8.89728 14.2469 8.89671 14.2467L8.8958 14.2464L8.89374 14.2455L8.89321 14.2453C8.88897 14.2438 8.88496 14.2418 8.88082 14.2401L8.87737 14.2387C8.87407 14.2374 8.87077 14.2361 8.86759 14.2346C8.86667 14.2341 8.86566 14.2338 8.86474 14.2334L8.86358 14.2329L8.86196 14.2322L8.85988 14.2311C8.85928 14.2307 8.85872 14.2304 8.85815 14.23C8.85764 14.2297 8.85713 14.2293 8.8566 14.229C8.84838 14.2246 8.84033 14.2196 8.83268 14.2143L8.83183 14.2137L8.83082 14.2131L8.83041 14.2129C8.82968 14.2123 8.82886 14.212 8.82814 14.2115L3.88752 10.6093C3.66236 10.445 3.56895 10.1559 3.65503 9.88991L4.22732 8.12753L4.22748 8.12709L5.35612 4.63381C5.4187 4.44755 5.58937 4.32469 5.78222 4.32469H5.78459C5.98044 4.3257 6.14612 4.44692 6.20664 4.63346L7.2829 7.95242H10.6725L11.7468 4.63365C11.8074 4.44692 11.9731 4.3257 12.1689 4.32469C12.3
1642 4.32313 12.5346 4.44676 12.5965 4.63096L13.7282 8.12744L13.7284 8.12794ZM8.83082 14.2131C8.83166 14.2137 8.83249 14.2143 8.83333 14.215C8.83445 14.2158 8.83557 14.2166 8.83672 14.2174C8.8352 14.2164 8.83372 14.2153 8.83223 14.2142L8.83082 14.2131Z",fill:"currentColor"})]}),r=()=>(0,i.jsx)("svg",{width:"18",height:"18",viewBox:"0 0 18 18",fill:"none",xmlns:"http://www.w3.org/2000/svg",children:(0,i.jsx)("path",{d:"M9 0C4.0275 0 0 4.13211 0 9.22838C0 13.3065 2.5785 16.7648 6.15375 17.9841C6.60375 18.0709 6.76875 17.7853 6.76875 17.5403C6.76875 17.3212 6.76125 16.7405 6.7575 15.9712C4.254 16.5277 3.726 14.7332 3.726 14.7332C3.3165 13.6681 2.72475 13.3832 2.72475 13.3832C1.9095 12.8111 2.78775 12.8229 2.78775 12.8229C3.6915 12.887 4.16625 13.7737 4.16625 13.7737C4.96875 15.1847 6.273 14.777 6.7875 14.5414C6.8685 13.9443 7.10025 13.5381 7.3575 13.3073C5.35875 13.0764 3.258 12.2829 3.258 8.74709C3.258 7.73988 3.60675 6.91659 4.18425 6.27095C4.083 6.03774 3.77925 5.0994 4.263 3.82846C4.263 3.82846 5.01675 3.58116 6.738 4.77462C7.458 4.56958 8.223 4.46785 8.988 4.46315C9.753 4.46785 10.518 4.56958 11.238 4.77462C12.948 3.58116 13.7017 3.82846 13.7017 3.82846C14.1855 5.0994 13.8817 6.03774 13.7917 6.27095C14.3655 6.91659 14.7142 7.73988 14.7142 8.74709C14.7142 12.2923 12.6105 13.0725 10.608 13.2995C10.923 13.5765 11.2155 14.1423 11.2155 15.0071C11.2155 16.242 11.2043 17.2344 11.2043 17.5341C11.2043 17.7759 11.3617 18.0647 11.823 17.9723C15.4238 16.7609 18 13.3002 18 9.22838C18 4.13211 13.9702 0 9 0V0Z",fill:"currentColor"})}),d=e=>(0,i.jsx)("svg",Object.assign({width:"18",height:"18",viewBox:"0 0 18 18",fill:"none",xmlns:"http://www.w3.org/2000/svg"},e,{children:(0,i.jsx)("path",{d:"M9 0C4.0302 0 0 4.0302 0 9C0 13.9698 4.0302 18 9 18C13.9698 18 18 13.9698 18 9C18 4.0302 13.9698 0 9 0ZM6.3847 13.6055H4.19279V7.01106H6.3847V13.6055ZM5.28882 6.1106H5.27454C4.539 6.1106 4.06329 5.60426 4.06329 4.97145C4.06329 4.32436 4.55356 3.83203 5.30338 3.83203C6.05319 3.83203 6.51462 4.32436 6.5289 4.97145C6.5289 5.60426 6.05319 6.1106 5.28882 6.1106ZM14.2883 13.6055H12.0966V10.0776C12.0966 9.19102 11.7793 8.58636 10.9862 8.58636C10.3807 8.58636 10.0201 8.99423 9.8616 9.38795C9.80365 9.52885 9.78951 9.72578 9.78951 9.92285V13.6055H7.59773C7.59773 13.6055 7.62643 7.62973 7.59773 7.01106H9.78951V7.94476C10.0808 7.49542 10.6019 6.85629 11.7648 6.85629C13.2069 6.85629 14.2883 7.79878 14.2883 9.82425V13.6055Z",fill:"currentColor"})})),c="root_ggeG",h="links_Ryzh",m="note_mfpI",p="avatar__gf0",g="content_Pi_3",u="name_U0ju",x="role_kzoF",b="link_uoqF",C=e=>{const{avatarUrl:t,name:s,role:C,twitterUrl:f,gitlabUrl:w,githubUrl:E,linkedinUrl:j,note:T,anchorId:I="author"}=e,y="a[href=\\#"+I+"]",v=Boolean(f||w||E||j),L=(0,n.useRef)(null),N=()=>{L.current&&requestAnimationFrame(()=>{const{scrollTop:e,scrollHeight:t,clientHeight:s}=document.documentElement,n=e;n!==t-s&&window.scrollTo({top:n-60-24})})};return(0,n.useEffect)(()=>{const e=window.document.querySelector(y);if(e)return e.addEventListener("click",N),()=>e.removeEventListener("click",N)},[]),(0,i.jsxs)("div",{className:c,id:I,ref:L,children:[(0,i.jsx)("img",{className:p,src:t,alt:s}),(0,i.jsxs)("div",{className:g,children:[(0,i.jsx)("h6",{className:u,children:s}),C&&(0,i.jsxs)("p",{className:x,children:[C," ",(0,i.jsx)("a",{href:"/",children:"PostgresAI"})]}),v&&(0,i.jsxs)("nav",{className:h,children:[f&&(0,i.jsx)(a.p,{href:f,className:b,label:"Twitter",children:(0,i.jsx)(o,{})}),w&&(0,i.jsx)(a.p,{href:w,className:b,label:"Gitlab",children:(0,i.jsx)(l,{})}),E&&(0,i.jsx)(a.p,{href:E,className:b,label:"Github",children:(0,i.jsx)(r,{})}),j&&(0,i.jsx)(a.p,{href:j,className:b,label:"Linkedin",children:(0,i.jsx)(d,{})})]}),T&&(0,i.jsx)("p",{className:m,children:T})]})]})};var f=s(95343);const w=e=>{const{author:t,authors:s}=e,n=s||(t?[t]:[]);return(0,i.jsxs)(i.Fragment,{children:[n.map((e,t)=>(0,i.jsx)(C,Object.assign({},e,{anchorId:n.length>1?"author-"+(t+1):"author"}),e.name+"-"+t)),(0,i.jsx)(f.a,{})]})}},28453(e,t,s){s.d(t,{R:()=>o,x:()=>l});var n=s(96540);const a={},i=n.createContext(a);function o(e){const t=n.useContext(i);return n.useMemo(function(){return"function"==typeof e?e(t):{...t,...e}},[t,e])}function l(e){let t;return t=e.disableParentContext?"function"==typeof e.components?e.components(a):e.components||a:o(e.components),n.createElement(i.Provider,{value:t},e.children)}},34395(e,t,s){s.d(t,{L_:()=>i,aF:()=>l,ic:()=>r,nQ:()=>a,p6:()=>n,x4:()=>o});const n={avatarUrl:"/assets/author/nik.jpg",name:"Nikolay Samokhvalov",role:"CEO & Founder of",twitterUrl:"https://twitter.com/samokhvalov",gitlabUrl:"https://gitlab.com/NikolayS",githubUrl:"https://github.com/NikolayS",linkedinUrl:"https://www.linkedin.com/in/samokhvalov",note:"Working on tools to balance Dev with Ops in DevOps"},a={avatarUrl:"/assets/author/dante.jpg",name:"Dante Cassanego",twitterUrl:"https://twitter.com/dantec",gitlabUrl:"https://gitlab.com/dante.cassanego",githubUrl:"https://github.com/dcassanego",linkedinUrl:"https://www.linkedin.com/in/dantecassanego"},i={avatarUrl:"/assets/author/anatoly.jpg",name:"Anatoly Stansler",gitlabUrl:"https://gitlab.com/anatolystansler",githubUrl:"https://github.com/astansler",linkedinUrl:"https://www.linkedin.com/in/anatoly-stansler-37265514a"},o={avatarUrl:"/assets/author/artyom.jpg",name:"Artyom Kartasov",role:"Software Engineer at",twitterUrl:"https://twitter.com/arkartasov",gitlabUrl:"https://github.com/agneum",githubUrl:"https://gitlab.com/akartasov"},l={avatarUrl:"/assets/author/bogdan.jpg",name:"Bogdan Tsechoev",role:"Software Engineer at",gitlabUrl:"https://gitlab.com/bogdan.tsechoev",githubUrl:"https://github.com/bogdan-ts"},r={avatarUrl:"/assets/author/denis.png",name:"Denis Morozov",role:"Staff Engineer at",gitlabUrl:"https://gitlab.com/Sarumyan9999"}},75187(e,t,s){s.r(t),s.d(t,{assets:()=>h,contentTitle:()=>c,default:()=>g,frontMatter:()=>d,metadata:()=>n,toc:()=>m});var n=s(99119),a=s(74848),i=s(28453),o=s(95343),l=s(7177),r=s(34395);const d={author:"Nikolay Samokhvalov",authorimg:"/assets/images/nik.jpg",date:new Date("2021-11-03T23:45:00.000Z"),publishDate:new Date("2021-11-03T23:45:00.000Z"),linktitle:"Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL",title:"Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL",weight:0,image:"/assets/thumbnails/20211101_postgresql_ddl_if_exists_shakespeare_meme.png",description:"Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL",tags:["Postgres insights","DDL"]},c=void 0,h={authorsImageUrls:[void 0]},m=[{value:"What is this about?",id:"what-is-this-about",level:2},{value:"Example 1: CREATE TABLE IF NOT EXISTS",id:"example-1-create-table-if-not-exists",level:2},{value:"Example 2: DROP TABLE IF EXISTS",id:"example-2-drop-table-if-exists",level:2},{value:"Example 3: CREATE INDEX CONCURRENTLY IF NOT EXISTS",id:"example-3-create-index-concurrently-if-not-exists",level:2},{value:"When to use IF NOT EXISTS / IF EXISTS",id:"when-to-use-if-not-exists--if-exists",level:2}];function p(e){const t={a:"a",code:"code",h2:"h2",p:"p",pre:"pre",...(0,i.R)(),...e.components};return(0,a.jsxs)(a.Fragment,{children:[(0,a.jsx)(t.h2,{id:"what-is-this-about",children:"What is this about?"}),"\n",(0,a.jsxs)(t.p,{children:["Many DDL statements in PostgreSQL support modifiers ",(0,a.jsx)(t.code,{children:"IF EXISTS"})," / ",(0,a.jsx)(t.code,{children:"IF NOT EXISTS"}),". For example:"]}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:"test=# create table if not exists mytable();\nCREATE TABLE\ntest=# drop table if exists mytable;\nDROP TABLE\n"})}),"\n",(0,a.jsxs)(t.p,{children:["I recommend using ",(0,a.jsx)(t.code,{children:"IF EXISTS"})," / ",(0,a.jsx)(t.code,{children:"IF NOT EXISTS"})," in DDL only when necessary. Here are three examples that demonstrate how the overuse of these words may lead to negative consequences."]}),"\n",(0,a.jsx)(t.h2,{id:"example-1-create-table-if-not-exists",children:"Example 1: CREATE TABLE IF NOT EXISTS"}),"\n",(0,a.jsx)(t.p,{children:"Let's assume we have the following to be deployed using some DB schema versioning tool (such as Flyway, Sqitch, or something embedded to ORM/framework like Ruby on Rails Active Record migrations):"}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-sql",children:"create table if not exists mytable(\n id int8 primary key,\
1n title text not null,\n created_at timestamptz not null default now()\n);\n"})}),"\n",(0,a.jsx)(t.p,{children:"The problem here is that strictly speaking, we cannot guarantee the result \u2013 there might be an existing table with the same name and a different structure. It might be created by a previous version of the same DB schema change attempt deployed by the same tool or something that was executed by another tool or manually:"}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:'test=# -- something from the past\ntest=# create table if not exists mytable(\n\xa0 id int8 primary key\n);\nCREATE TABLE\ntest=#\ntest=# -- our attempt to create a table\ntest=# create table if not exists mytable(\n\xa0 id int8 primary key,\n\xa0 title text not null,\n\xa0 created_at timestamptz not null default now()\n);\nNOTICE: \xa0relation "mytable" already exists, skipping\nCREATE TABLE\ntest=#\ntest=# -- what do we have?\ntest=# \\d mytable\n\xa0 \xa0 \xa0 \xa0 \xa0 \xa0 \xa0 Table "public.mytable"\n\xa0Column | \xa0Type \xa0| Collation | Nullable | Default\n--------+--------+-----------+----------+---------\n\xa0id \xa0 \xa0 | bigint | \xa0 \xa0 \xa0 \xa0 \xa0 | not null |\nIndexes:\n\xa0 \xa0 "mytable_pkey" PRIMARY KEY, btree (id)\n'})}),"\n",(0,a.jsxs)(t.p,{children:["Why do engineers add ",(0,a.jsx)(t.code,{children:"IF NOT EXISTS"})," in such cases? Because they are uncertain if their change was already deployed to lower environments: dev, QA, staging, and so on. ",(0,a.jsx)(t.code,{children:"IF NOT EXISTS"}),' suppresses errors and allows to "deploy" the change many times. However, this approach just hides the imperfection of the management of those environments \u2013 instead of improving the workflow, we now have increased risks of having various schema change anomalies and even production incidents. Further, we will discuss two more cases similar in nature.']}),"\n",(0,a.jsx)(o.a,{}),"\n",(0,a.jsx)(t.h2,{id:"example-2-drop-table-if-exists",children:"Example 2: DROP TABLE IF EXISTS"}),"\n",(0,a.jsx)(t.p,{children:'Most of the modern schema version control tools allow having both "do" and "undo" steps (they can be called somehow else \u2013 e.g., "deploy" and "revert"). There are opinions that the "undo" steps are useless \u2013 they may seem more or less valuable depending on circumstances, deployment workflow, and DevOps culture. However, if they are used, in some cases, we can find the following:'}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-sql",children:"drop table if exists mytable;\n"})}),"\n",(0,a.jsx)(t.p,{children:'What\'s bad with this approach? It can hide logical issues in the schema change scripts. For instance, let\'s assume we develop some software to be deployed in thousands of places, and our schema change scripts \u2013 both "do" and "undo" parts of them \u2013 should be reliably executed everywhere. We rely on the "undo" steps for the cases when our clients need to revert the upgrade; it may happen because of various issues not necessarily related to the database.'}),"\n",(0,a.jsx)(t.p,{children:'We have well-developed CI/CD pipelines testing our changes in advanced form to ensure that logic is correct and detect anomalies earlier \u2013 they always get tested using the chain "do -> undo -> do" in CI/CD pipelines.'}),"\n",(0,a.jsx)(t.p,{children:"Now let's consider this:"}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-sql",children:'-- "do"\ncreate table table2(\n id int8 primary key\n);\n'})}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-sql",children:'-- "undo"\ndrop table if exists table2;\ndrop table if exists mytable; -- someone left it here just by mistake\n'})}),"\n",(0,a.jsxs)(t.p,{children:['What\'s wrong here? The CI/CD pipelines will not see any problems \u2013 the chain "do -> undo -> do" will work pretty well. The attempt to drop ',(0,a.jsx)(t.code,{children:"mytable"})," will not fail; we'll just get a notice:"]}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:'test=# drop table if exists mytable;\nNOTICE: table "mytable" does not exist, skipping\nDROP TABLE\n'})}),"\n",(0,a.jsxs)(t.p,{children:["But what if ",(0,a.jsx)(t.code,{children:"mytable"}),' was created by another DB schema migration long ago? If the "undo" step needs to be executed somewhere, we are going to drop that table. This is not what we expected! For such time of mistakes, we do need to have an error in CI tests - bug IF EXISTS is going to "mask" the problem. As a result, automated testing is not going to catch the problem, and this wrong change has risks to be released.']}),"\n",(0,a.jsx)(t.h2,{id:"example-3-create-index-concurrently-if-not-exists",children:"Example 3: CREATE INDEX CONCURRENTLY IF NOT EXISTS"}),"\n",(0,a.jsxs)(t.p,{children:["For large tables under load, it is recommended to use ",(0,a.jsx)(t.code,{children:"CREATE INDEX CONCURRENTLY"})," \u2013 it is going to work longer that ",(0,a.jsx)(t.code,{children:"CREATE INEDEX"}
1)," but it won't cause downtime."]}),"\n",(0,a.jsxs)(t.p,{children:['It is not uncommon to see how DBAs try various index ideas right on the production database, trying to find a band-aid for some suboptimal query, and then, once found, they suggest developers include the index to migrations \u2013 just to "register" it. In such cases, it is tempting to use ',(0,a.jsx)(t.code,{children:"CREATE INDEX CONCURRENTLY IF NOT EXISTS"}),"."]}),"\n",(0,a.jsxs)(t.p,{children:["However, the thing is that ",(0,a.jsx)(t.code,{children:"CREATE INDEX CONCURRENTLY"})," is not atomic: if an attempt to deploy it fails, then the index is going to remain to be defined \u2013 in the INVALID state. Here is a simple example:"]}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:'test=# create table mytable(\n id int8 PRIMARY KEY,\n title text NOT NULL,\n created_at timestamptz NOT NULL DEFAULT now()\n);\nCREATE TABLE\n\ntest=# insert into mytable(title)\n select random()::text\n from generate_series(1, 10000000) i;\nINSERT 0 10000000\n\ntest=# set statement_timeout to \'1ms\';\nSET\ntest=# create index concurrently if not exists mytable_title_idx\n on mytable\n using btree (title);\nERROR: canceling statement due to statement timeout\ntest=# reset statement_timeout;\nRESET\n\ntest=# \\d mytable\n Table "public.mytable"\n Column | Type | Collation | Nullable | Default\n------------+--------------------------+-----------+----------+---------\n id | bigint | | not null |\n title | text | | not null |\n created_at | timestamp with time zone | | not null | now()\nIndexes:\n "mytable_pkey" PRIMARY KEY, btree (id)\n "mytable_title_idx" btree (title) INVALID\n'})}),"\n",(0,a.jsxs)(t.p,{children:["In this case, another attempt to deploy ",(0,a.jsx)(t.code,{children:"CREATE INDEX CONCURRENTLY IF NOT EXISTS"}),' is going to do nothing and report "success":']}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:'test=# create index concurrently if not exists mytable_title_idx\n on mytable\n using btree (title);\nNOTICE: relation "mytable_title_idx" already exists, skipping\nCREATE INDEX\n'})}),"\n",(0,a.jsxs)(t.p,{children:["Interestingly, in this case \u2013 actually, we talk about the creation of any index on a large table under significant load in general \u2013 the ",(0,a.jsx)(t.code,{children:"IF EXISTS"}),' can be helpful. We can use it in the "do" step to support cleanup \u2013 ',(0,a.jsx)(t.code,{children:"DROP INDEX CONCURRENTLY IF EXISTS"}),", leaving ",(0,a.jsx)(t.code,{children:"CREATE INDEX CONCURRENTLY"}),' "clean":']}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:"test=# drop index concurrently if exists mytable_title_idx;\nDROP INDEX\ntest=#\ntest=# create index concurrently if not exists mytable_title_idx\n on mytable\n using btree (title);\nCREATE INDEX\n"})}),"\n",(0,a.jsx)(t.p,{children:"This may be enough \u2013 only if we forget about DBA's actions that left behind a VALID index! (Of course, it is not a good idea to test index ideas on production - for that, thin clones provided by Database Lan Engine are better in terms of risks for production and freedom for testing.)"}),"\n",(0,a.jsxs)(t.p,{children:["How to drop the index but only if it's marked as INVALID? In this case, we could implement some logic to run ",(0,a.jsx)(t.code,{children:"DROP INDEX CONCURRENTLY"})," only if ",(0,a.jsx)(t.code,{children:"pg_index.indisvalid"})," for the index is ",(0,a.jsx)(t.code,{children:"false"}),":"]}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{children:"test=# select indisvalid\nfrom pg_index\nwhere indexrelid = 'mytable_title_idx'::regclass::oid;\n indisvalid\n------------\n f\n(1 row)\n"})}),"\n",(0,a.jsxs)(t.p,{children:["Unfortunately, this logic cannot be scripted in the form of anonymous DO block of PL/pgSQL code because ",(0,a.jsx)(t.code,{children:"DROP INDEX CONCURRENTLY"})," cannot be executed inside explicitly defined transactions, functions, or anonymous DO blocks \u2013 so the logic needs to be coded externally. Running ",(0,a.jsx)(t.code,{children:"DROP INDEX"})," in such a block would be possible, but this is not suitable for zero-downtime changes."]}),"\n",(0,a.jsx)(t.h2,{id:"when-to-use-if-not-exists--if-exists",children:"When to use IF NOT EXISTS / IF EXISTS"}),"\n",(0,a.jsxs)(t.p,{children:["If we aim to have predictable results, it is better to avoid ",(0,a.jsx)(t.code,{children:"IF NOT EXISTS"})," / ",(0,a.jsx)(t.code,{children:"IF EXISTS"})," in DB schema migrations. They need to be very rare \u2013 one of the examples was demonstrated above (",(0,a.jsx)(t.code,{children:"DROP INDEX CONCURRENTLY IF EXISTS"}),")."]}),"\n",(0,a.jsx)(t.p,{children:"In some other cases, such as scripts used for benchmarks, they can be really useful. For example, we can write bootstrap scripts that create DB schema used for benchmarking in an idempotent manner and use it in multiple benchmark iterations \u2013 so it does not matter if it is the first iteration of the 100th, the result will always be predictable:"}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-sql",children:"drop table if exists test_table;\ncreate table test_table(\n ...\n);\n"})}),"\n",(0,a.jsx)(t.p,{children:'Or even dropping the whole "benchmarking" schema before re-creating it:'}),"\n",(0,a.jsx)(t.pre,{children:(0,a.jsx)(t.code,{className:"language-sql",children:"drop schema if exists benchmark cascade;\ncreate schema ...\ncreate ...\n"})}),"\n",(0,a.jsxs)(t.p,{children:["In other cases, especially dealing with production, multiple systems, multiple people \u2013 it is better to reduce the use of ",(0,a.jsx)(t.code,{children:"IF NOT EXISTS"})," / ",(0,a.jsx)(t.code,{children:"IF EXISTS"})," to a minimum."]}),"\n",(0,a.jsxs)(t.p,{children:["And what do you think? Find me on Twitter (",(0,a.jsx)(t.a,{href:"https://twitter.com/samokhvalov",children:"@samokhvalov"}),") and let me know!"]}),"\n",(0,a.jsx)(l.K,{author:r.p6})]})}function g(e={}){const{wrapper:t}={...(0,i.R)(),...e.components};return t?(0,a.jsx)(t,{...e,children:(0,a.jsx)(p,{...e})}):p(e)}},95343(e,t,s){s.d(t,{a:()=>g});s(96540);var n=s(74848);const a=e=>(0,n.jsx)("svg",{className:e.className,viewBox:"0 0 15 14",fill:"none",xmlns:"http://www.w3.org/2000/svg",children:(0,n.jsx)("path",{d:"M13.6571 6.16873L7.83291 0.344521C7.37355 -0.11484 6.62854 -0.11484 6.16917 0.344521C5.70981 0.803883 5.70981 1.5489 6.16917 2.00826L11.161 7.00001L6.16917 11.9917C5.70981 12.4511 5.70981 13.1961 6.16917 13.6555C6.62854 14.1148 7.37355 14.1148 7.83291 13.6555L13.6571 7.8313C14.1165 7.37308 14.1165 6.62809 13.6571 6.16873ZM6.65418 5.81604L2.03028 0.942636C1.56565 0.453556 0.813091 0.453556 0.348467 0.942636C-0.116156 1.43172 -0.116156 2.22511 0.348467 2.71479L4.13178 6.70212L0.348467 10.6895C-0.116156 11.1786 -0.116156 11.9725 0.348467 12.4616C0.813091 12.9507 1.56565 12.9507 2.03028 12.4616L6.65418 7.5882C7.1188 7.09912 7.1188 6.30572 6.65418 5.81604Z",fill:"currentColor"})}),i="icon_V8r0",o=e=>(0,n.jsxs)("a",{href:e.href,className:e.className,children:[e.children,"\xa0",(0,n.jsx)(a,{className:i})]}),l="root_GDEH",r="img_yZ2V",d="content_Ja04",c="title_Eg0U",h="desc_OsDW",m="links_rDgJ",p="link_hKLb",g=()=>(0,n.jsxs)("div",{className:l,children:[(0,n.jsx)("img",{src:"/assets/db-lab-logo.svg",alt:"Database Lab",className:r,width:"224px",height:"170px"}),(0,n.jsxs)("div",{className:d,children:[(0,n.jsx)("h6",{className:c,children:"DBLab Engine"}),(0,n.jsx)("p",{className:h,children:"Instant database branching with O(1) economics."}),(0,n.jsxs)("nav",{className:m,children:[(0,n.jsx)(o,{href:"/blog/20250721-dblab-engine-4-0-released",className:p,children:"Read detailed blog post"}),(0,n.jsx)(o,{href:"/docs",className:p,children:"Read documentation"})]})]})]})},99119(e){e.exports=JSON.parse('{"permalink":"/blog/20211103-three-cases-against-if-not-exists-and-if-exists-in-postgresql-ddl","editUrl":"https://gitlab.com/postgres-ai/docs/-/edit/master/blog/20211103-three-cases-against-if-not-exists-and-if-exists-in-postgresql-ddl.md","
1source":"@site/blog/20211103-three-cases-against-if-not-exists-and-if-exists-in-postgresql-ddl.md","title":"Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL","description":"Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL","date":"2021-11-03T23:45:00.000Z","tags":[{"inline":true,"label":"Postgres insights","permalink":"/blog/tags/postgres-insights"},{"inline":true,"label":"DDL","permalink":"/blog/tags/ddl"}],"readingTime":6.97,"hasTruncateMarker":true,"authors":[{"name":"Nikolay Samokhvalov","key":null,"page":null}],"frontMatter":{"author":"Nikolay Samokhvalov","authorimg":"/assets/images/nik.jpg","date":"2021-11-03T23:45:00.000Z","publishDate":"2021-11-03T23:45:00.000Z","linktitle":"Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL","title":"Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL","weight":0,"image":"/assets/thumbnails/20211101_postgresql_ddl_if_exists_shakespeare_meme.png","description":"Three cases against IF NOT EXISTS / IF EXISTS in Postgres DDL","tags":["Postgres insights","DDL"]},"unlisted":false,"prevItem":{"title":"DLE 3.0: UI, persistent clones, PostgreSQL 14, more","permalink":"/blog/20211221-dle-3-0-0-brings-ui-and-persistent-clones"},"nextItem":{"title":"How partial, covering, and multicolumn indexes may slow down UPDATEs in PostgreSQL","permalink":"/blog/20211029-how-partial-and-covering-indexes-affect-update-performance-in-postgresql"}}')},99312(e,t,s){s.d(t,{p:()=>a});s(96540);var n=s(74848);const a=e=>(0,n.jsx)("a",{href:e.href,className:e.className,"aria-label":e.label,target:"_blank",rel:"noopener noreferrer",children:e.children})}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.