{"id":10639,"date":"2017-11-22T18:10:23","date_gmt":"2017-11-22T17:10:23","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/"},"modified":"2017-11-22T18:10:23","modified_gmt":"2017-11-22T17:10:23","slug":"create-index-concurrently-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/","title":{"rendered":"Create index CONCURRENTLY in PostgreSQL"},"content":{"rendered":"

In PostgreSQL when you create an index on a table, sessions that want to write to the table must wait until the index build completed by default. There is a way around that, though, and in this post we’ll look at how you can avoid that. <\/p>\n

<\/p>\n

As usual we’ll start with a little table:<\/p>\n

\npostgres=# \\! cat a.sql\ndrop table if exists t1;\ncreate table t1 ( a int, b varchar(50));\ninsert into t1\nselect a.*, md5(a::varchar) from generate_series(1,5000000) a;\npostgres=# \\i a.sql\nDROP TABLE\nCREATE TABLE\nINSERT 0 5000000\n<\/pre>\n
When you now create an index on that table and try to write the table at the same time from a different session that session will wait until the index is there (the screenshot shows the first session creating the index on the left and the second session doing the update on the right, which is waiting for the left one):
\n\"Selection_007\"<\/a><\/p>\n
For production environments this not something you want to happen as this can block a lot of other sessions especially when the table in question is heavily used. You can avoid that by using “create index concurrently”<\/a>.<\/p>\n
\"Selection_008\"<\/a><\/p>\n
Using that syntax writes to the table from other sessions will succeed while the index is being build. But, as clearly written in the documentation: The downside is that the table needs to be scanned twice, so more work needs to be done which means more resource usage on your server. Other points need to be considered as well. When, for whatever reason, you index build fails (e.g. by canceling the create index statement):<\/p>\n
\npostgres=# create index concurrently i1 on t1(a);\n^CCancel request sent\nERROR:  canceling statement due to user request\n<\/pre>\n
… you maybe would expect the index not to be there at all but this is not the case. When you try to create the index right after the canceled statement again you’ll hit this:<\/p>\n
\npostgres=# create index concurrently i1 on t1(a);\nERROR:  relation \"i1\" already exists\n<\/pre>\n
This does not happen when you do not create the index concurrently:<\/p>\n
\npostgres=# create index i1 on t1(a);\n^CCancel request sent\nERROR:  canceling statement due to user request\npostgres=# create index i1 on t1(a);\nCREATE INDEX\npostgres=# \n<\/pre>\n
The questions is why this happens in the concurrent case but not in the “normal” case? The reason is simple: When you create an index the “normal” way the whole build is done in one transaction. Because of this the index does not exist when the transaction is aborted (the create index statement is canceled). When you build the index concurrently there are multiple transactions involved: “In a concurrent index build, the index is actually entered into the system catalogs in one transaction, then two table scans occur in two more transactions”. So in this case:<\/p>\n
\npostgres=# create index concurrently i1 on t1(a);\nERROR:  relation \"i1\" already exists\n<\/pre>\n
… the index is already stored in the catalog:<\/p>\n
\npostgres=# create index concurrently i1 on t1(a);\n^CCancel request sent\nERROR:  canceling statement due to user request\npostgres=# select relname,relkind,relfilenode from pg_class where relname = 'i1';\n relname | relkind | relfilenode \n---------+---------+-------------\n i1      | i       |       32926\n(1 row)\n<\/pre>\n
If you don’t take care of that you will have invalid indexes in your database:<\/p>\n
\npostgres=# \\d t1\n                        Table \"public.t1\"\n Column |         Type          | Collation | Nullable | Default \n--------+-----------------------+-----------+----------+---------\n a      | integer               |           |          | \n b      | character varying(50) |           |          | \nIndexes:\n    \"i1\" btree (a) INVALID\n<\/pre>\n
You might think that this does not harm, but then consider this case:<\/p>\n
\n-- in session one build a unique index\npostgres=# create unique index concurrently i1 on t1(a);\n-- then in session two violate the uniqueness after some seconds\npostgres=# update t1 set a = 5 where a = 4000000;\nUPDATE 1\n-- the create index statement will fail in the first session\npostgres=# create unique index concurrently i1 on t1(a);\nERROR:  duplicate key value violates unique constraint \"i1\"\nDETAIL:  Key (a)=(5) already exists.\n<\/pre>\n
This is even worse as the index now really consumes space on disk:<\/p>\n
\npostgres=# select relpages from pg_class where relname = 'i1';\n relpages \n----------\n    13713\n(1 row)\n<\/pre>\n
The index is invalid, of course and will not be used by the planner:<\/p>\n
\npostgres=# \\d t1\n                        Table \"public.t1\"\n Column |         Type          | Collation | Nullable | Default \n--------+-----------------------+-----------+----------+---------\n a      | integer               |           |          | \n b      | character varying(50) |           |          | \nIndexes:\n    \"i1\" UNIQUE, btree (a) INVALID\n\npostgres=# explain select * from t1 where a = 12345;\n                              QUERY PLAN                              \n----------------------------------------------------------------------\n Gather  (cost=1000.00..82251.41 rows=1 width=37)\n   Workers Planned: 2\n   ->  Parallel Seq Scan on t1  (cost=0.00..81251.31 rows=1 width=37)\n         Filter: (a = 12345)\n(4 rows)\n<\/pre>\n
But the index is still maintained:<\/p>\n
\npostgres=# select relpages from pg_class where relname = 'i1';\n relpages \n----------\n    13713\n(1 row)\npostgres=# insert into t1 select a.*, md5(a::varchar) from generate_series(5000001,6000000) a;\nINSERT 0 1000000\n\npostgres=# select relpages from pg_class where relname = 'i1';\n relpages \n----------\n    16454\n(1 row)\n<\/pre>\n
So now you have an index which can not be used to speed up queries (which is bad) but the index is still maintained when you write to the table (which is even worse because you consume resources for nothing). The only way out of this is to drop and re-create the index:<\/p>\n
\npostgres=# drop index i1;\nDROP INDEX\n-- potentially clean up any rows that violate the constraint and then\npostgres=# create unique index concurrently i1 on t1(a);\nCREATE INDEX\npostgres=# \\d t1\n                        Table \"public.t1\"\n Column |         Type          | Collation | Nullable | Default \n--------+-----------------------+-----------+----------+---------\n a      | integer               |           |          | \n b      | character varying(50) |           |          | \nIndexes:\n    \"i1\" UNIQUE, btree (a)\n\npostgres=# explain select * from t1 where a = 12345;\n                          QUERY PLAN                           \n---------------------------------------------------------------\n Index Scan using i1 on t1  (cost=0.43..8.45 rows=1 width=122)\n   Index Cond: (a = 12345)\n(2 rows)\n<\/pre>\n
Remember: When a create index operations fails in concurrent mode make sure that you drop the index immediately. <\/p>\n
One more thing to keep in mind: When you create an index concurrently and there is another session already modifying the data the create index command waits until that other operation completes:<\/p>\n
\n-- first session inserts data without completing the transaction\npostgres=# begin;\nBEGIN\nTime: 0.579 ms\npostgres=# insert into t1 select a.*, md5(a::varchar) from generate_series(6000001,7000000) a;\nINSERT 0 1000000\n-- second sessions tries to build the index\npostgres=# create unique index concurrently i1 on t1(a);\n<\/pre>\n
The create index operation will wait until that completes:<\/p>\n
\npostgres=# select query,state,wait_event,wait_event_type from pg_stat_activity where state ='active';\n                                query                                 | state  | wait_event | wait_event_t\n----------------------------------------------------------------------+--------+------------+-------------\n create unique index concurrently i1 on t1(a);                        | active | virtualxid | Lock\n select query,state,wait_event,wait_event_type from pg_stat_activity; | active |            | \n<\/pre>\n
… meaning when someone forgets to end the transaction the create index command will wait forever. There is the parameter idle_in_transaction_session_timeout<\/a> which gives you more control on that but still you need to be aware what is happening here. <\/p>\n
Happy index creation \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"
In PostgreSQL when you create an index on a table, sessions that want to write to the table must wait until the index build completed by default. There is a way around that, though, and in this post we’ll look at how you can avoid that.<\/p>\n","protected":false},"author":29,"featured_media":10640,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-10639","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nCreate index CONCURRENTLY in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create index CONCURRENTLY in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"In PostgreSQL when you create an index on a table, sessions that want to write to the table must wait until the index build completed by default. There is a way around that, though, and in this post we’ll look at how you can avoid that.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-11-22T17:10:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"992\" \/>\n\t<meta property=\"og:image:height\" content=\"56\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Create index CONCURRENTLY in PostgreSQL\",\"datePublished\":\"2017-11-22T17:10:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/\"},\"wordCount\":604,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png\",\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/\",\"name\":\"Create index CONCURRENTLY in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png\",\"datePublished\":\"2017-11-22T17:10:23+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png\",\"width\":992,\"height\":56},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Create index CONCURRENTLY in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Create index CONCURRENTLY in PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Create index CONCURRENTLY in PostgreSQL","og_description":"In PostgreSQL when you create an index on a table, sessions that want to write to the table must wait until the index build completed by default. There is a way around that, though, and in this post we’ll look at how you can avoid that.","og_url":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2017-11-22T17:10:23+00:00","og_image":[{"width":992,"height":56,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png","type":"image\/png"}],"author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Create index CONCURRENTLY in PostgreSQL","datePublished":"2017-11-22T17:10:23+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/"},"wordCount":604,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png","keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/","name":"Create index CONCURRENTLY in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png","datePublished":"2017-11-22T17:10:23+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Selection_007-1.png","width":992,"height":56},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/create-index-concurrently-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Create index CONCURRENTLY in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10639","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10639"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10639\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/10640"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10639"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10639"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10639"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10639"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}