{"id":5143,"date":"2015-05-04T12:23:18","date_gmt":"2015-05-04T10:23:18","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/"},"modified":"2015-05-04T12:23:18","modified_gmt":"2015-05-04T10:23:18","slug":"variations-on-1m-rows-insert-1-bulk-insert-postgresql-2","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/","title":{"rendered":"Variations on 1M rows insert (1): bulk insert – PostgreSQL"},"content":{"rendered":"

When I read Franck’s post about Variations on 1M rows insert (1): bulk insert<\/a> I thought doing quite the same in PostgreSQL might be interesting. Lets start by using the same test tables, one using a primary key and the other one without a primary key:create table DEMO (“id” int , “text” varchar(15), “number” int);<\/p>\n

create table DEMO_PK (\"id\" int , \"text\" varchar(15), \"number\" int,\n constraint demo_pk_pk primary key (id) ) ;\npostgres=# d demo*\n Table \"public.demo\"\n Column | Type | Modifiers\n --------+-----------------------+-----------\n id | integer |\n text | character varying(15) |\n number | integer |\nTable \"public.demo_pk\"\n Column | Type | Modifiers\n --------+-----------------------+-----------\n id | integer | not null\n text | character varying(15) |\n number | integer |\n Indexes:\n \"demo_pk_pk\" PRIMARY KEY, btree (id)\nIndex \"public.demo_pk_pk\"\n Column | Type | Definition\n --------+---------+------------\n id | integer | id\n primary key, btree, for table \"public.demo_pk\"<\/pre>\n
 <\/p>\n
I am using a Virtual Box VM with Oracle Linux 7.1 (64bit) using 512mb of memory and one vCPU for the Tests:<\/p>\n
cat \/etc\/oracle-release \nOracle Linux Server release 7.1\ncat \/proc\/meminfo | head -1\nMemTotal:         502612 kB\ncat \/proc\/cpuinfo | grep proc\nprocessor\t: 0\n<\/pre>\n
Similar to Franck’s plsql block I’ll use a plpgsql for the first tests:<\/p>\n
timing on\nshow autocommit;\ntruncate table DEMO;\nDO $$DECLARE\n  l_people_array varchar(15)[12] := '{\"Marc\", \"Bill\", \"George\", \"Eliot\", \"Matt\", \"Trey\", \"Tracy\",\"Greg\", \"Steve\", \"Kristina\", \"Katie\", \"Jeff\"}';\n  n int;\nBEGIN\n  for i in 0..1e6 loop\n     n:=trunc(random()*1000+1);\n     insert into DEMO values( i , l_people_array[floor((random()*11))+1::int] , n );\n  end loop;\nEND$$;\n<\/pre>\n
(The code for the demo_pk table is exactly the same, except for the table name). It does not exactly the same as Frank’s plsql does but it does comparable things, I believe \ud83d\ude42
\nThe PostgreSQL version I’ll use is 9.4.1:<\/p>\n
postgres=# select version();\n                                                         version                                                          \n--------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit\n(1 row)\n<\/pre>\n
Lets start by executing the test without a primary key and thus no index:<\/p>\n
postgres=# i 1mio_rows_no_pk.sql\nTiming is on.\n autocommit \n------------\n on\n(1 row)\n\nTime: 0.183 ms\nTRUNCATE TABLE\nTime: 71.339 ms\nDO\nTime: 6861.547 ms\n<\/pre>\n
Not so bad. Doing the same with a primary key and thus an index to maintain:<\/p>\n
postgres-# i 1mio_rows_pk.sql\nTiming is on.\n autocommit \n------------\n on\n(1 row)\n\nTime: 0.494 ms\nTRUNCATE TABLE\nTime: 37.314 ms\nDO\nTime: 10900.631 ms\n<\/pre>\n
Around 3 seconds more, but again: Not so bad. As there is no “bulk collect” or “bulk insert” in PostgreSQL I can not do the same tests as Franck. But PostgreSQL knows a copy command, lets look at that. First I’ll unload the table to a flat file so I have something to load:<\/p>\n
postgres=# copy demo to '\/home\/postgres\/demo.txt';\nCOPY 1000001\nTime: 239.161 ms\n<\/pre>\n
Believe it or not: it took 240 ms to unload 1000001 rows from a table to a flat file. Now lets do the opposite and load the data back to the table using the copy command. The script used for doing this looks like this for both variations (with and without primary key):<\/p>\n
timing on\nshow autocommit;\ntruncate table demo;\ncopy DEMO from '\/home\/postgres\/demo.txt';\n<\/pre>\n
First one without the primary key:<\/p>\n
postgres=# i 1mio_rows_no_pk_copy.sql\nTiming is on.\n autocommit \n------------\n on\n(1 row)\n\nTime: 0.663 ms\nTRUNCATE TABLE\nTime: 32.026 ms\nCOPY 1000001\nTime: 1877.480 ms\n<\/pre>\n
The second one with the primary key:<\/p>\n
postgres=# i 1mio_rows_pk_copy.sql\nTiming is on.\nTRUNCATE TABLE\nTime: 33.370 ms\nCOPY 1000001\nTime: 6227.844 ms\n<\/pre>\n
This makes a big difference: 1,8 seconds compared to 6,2 seconds. But still amazingly fast. For those who do know PostgreSQL a little bit and might wonder about fsync:<\/p>\n
postgres=# show fsync;\n fsync \n-------\n on\n(1 row)\n<\/pre>\n
If I turn this off, without the primary key:<\/p>\n
postgres=# alter system set fsync='off';\nALTER SYSTEM\nTime: 62.066 ms\npostgres=# select pg_reload_conf();\nLOG:  received SIGHUP, reloading configuration files\nLOG:  parameter \"fsync\" changed to \"off\"\n pg_reload_conf \n----------------\n t\n(1 row)\n\nTime: 7.253 ms\npostgres=# show fsync;\n fsync \n-------\n off\n(1 row)\n\nTime: 0.601 ms\npostgres=# i 1mio_rows_no_pk_copy.sql\nTiming is on.\n autocommit \n------------\n on\n(1 row)\n\nTime: 0.277 ms\nTRUNCATE TABLE\nTime: 10.064 ms\nCOPY 1000001\nTime: 611.455 ms\npostgres=# i 1mio_rows_pk_copy.sql\nTiming is on.\nTRUNCATE TABLE\nTime: 11.768 ms\nCOPY 1000001\nTime: 4674.273 ms<\/pre>\n
Around 0,6 seconds without the primary key and around 4 seconds with the primary key. But remember that you might loose data if fsync<\/a> is off and the server crashed during the load.<\/p>\n
Lets wait what Frank will do in the next post of his series.<\/p>\n","protected":false},"excerpt":{"rendered":"
When I read Franck’s post about Variations on 1M rows insert (1): bulk insert I thought doing quite the same in PostgreSQL might be interesting. Lets start by using the same test tables, one using a primary key and the other one without a primary key:create table DEMO (“id” int , “text” varchar(15), “number” int); […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[368],"tags":[77],"type_dbi":[],"class_list":["post-5143","post","type-post","status-publish","format-standard","hentry","category-development-performance","tag-postgresql"],"acf":[],"yoast_head":"\nVariations on 1M rows insert (1): bulk insert - 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\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Variations on 1M rows insert (1): bulk insert - PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"When I read Franck’s post about Variations on 1M rows insert (1): bulk insert I thought doing quite the same in PostgreSQL might be interesting. Lets start by using the same test tables, one using a primary key and the other one without a primary key:create table DEMO (“id” int , “text” varchar(15), “number” int); […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-04T10:23:18+00:00\" \/>\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=\"4 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\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Variations on 1M rows insert (1): bulk insert – PostgreSQL\",\"datePublished\":\"2015-05-04T10:23:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/\"},\"wordCount\":370,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Development & Performance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/\",\"name\":\"Variations on 1M rows insert (1): bulk insert - PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-05-04T10:23:18+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Variations on 1M rows insert (1): bulk insert – 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":"Variations on 1M rows insert (1): bulk insert - 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\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/","og_locale":"en_US","og_type":"article","og_title":"Variations on 1M rows insert (1): bulk insert - PostgreSQL","og_description":"When I read Franck’s post about Variations on 1M rows insert (1): bulk insert I thought doing quite the same in PostgreSQL might be interesting. Lets start by using the same test tables, one using a primary key and the other one without a primary key:create table DEMO (“id” int , “text” varchar(15), “number” int); […]","og_url":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/","og_site_name":"dbi Blog","article_published_time":"2015-05-04T10:23:18+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Variations on 1M rows insert (1): bulk insert – PostgreSQL","datePublished":"2015-05-04T10:23:18+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/"},"wordCount":370,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Development & Performance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/","url":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/","name":"Variations on 1M rows insert (1): bulk insert - PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-05-04T10:23:18+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-1-bulk-insert-postgresql-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Variations on 1M rows insert (1): bulk insert – 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\/5143","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=5143"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5143\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5143"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}