{"id":11510,"date":"2018-08-05T12:26:34","date_gmt":"2018-08-05T10:26:34","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/"},"modified":"2018-08-05T12:26:34","modified_gmt":"2018-08-05T10:26:34","slug":"when-does-postgresql-create-the-table-and-index-files-on-disk","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/","title":{"rendered":"When does PostgreSQL create the table and index files on disk?"},"content":{"rendered":"<p>A question that pops up from time to time is: When we create a table or an index in PostgreSQL are the files on disk created immediately or is this something that happens when the first row is inserted? The question mostly is coming from Oracle DBAs because in Oracle you can have <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/sqlrf\/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6\" target=\"_blank\" rel=\"noopener\">deferred segment creation<\/a>. In PostgreSQL there is no parameter for that so lets do a quick test.<\/p>\n<p><!--more--><\/p>\n<p>We start with a simple table:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t1 ( a int );\nCREATE TABLE\n<\/pre>\n<p>To get the real file name we can either use the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/functions-admin.html\" target=\"_blank\" rel=\"noopener\">pg_relation_filepath<\/a> function:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select pg_relation_filepath('t1');\n pg_relation_filepath \n----------------------\n base\/33845\/33933\n(1 row)\n<\/pre>\n<p>&#8230; or we can use the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/oid2name.html\" target=\"_blank\" rel=\"noopener\">oid2name<\/a> utility:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG10] oid2name -d postgres -t t1\nFrom database \"postgres\":\n  Filenode  Table Name\n----------------------\n     33933          t1\n<\/pre>\n<p>Now we can easily check if that file is already existing:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG10] ls -la $PGDATA\/base\/33845\/33933\n-rw-------. 1 postgres postgres 0 Jul 24 07:47 \/u02\/pgdata\/10\/PG103\/base\/33845\/33933\n<\/pre>\n<p>It is already there but empty. The files for the <a href=\"https:\/\/www.postgresql.org\/docs\/10\/static\/storage-vm.html\" target=\"_blank\" rel=\"noopener\">visibility map<\/a> and the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/storage-fsm.html\" target=\"_blank\" rel=\"noopener\">free space map<\/a> are not yet created:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG10] ls -la $PGDATA\/base\/33845\/33933*\n-rw-------. 1 postgres postgres 0 Jul 24 07:47 \/u02\/pgdata\/10\/PG103\/base\/33845\/33933\n<\/pre>\n<p>What happens when we create an index on that empty table?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create index i1 on t1 (a);\nCREATE INDEX\npostgres=# select pg_relation_filepath('i1');\n pg_relation_filepath \n----------------------\n base\/33845\/33937\n(1 row)\npostgres=# ! ls -la $PGDATA\/base\/33845\/33937\n-rw-------. 1 postgres postgres 8192 Jul 24 08:06 \/u02\/pgdata\/10\/PG103\/base\/33845\/33937\n<\/pre>\n<p>The file is created immediately as well but it is not empty. It is exactly one page (my blocksize is 8k). Using the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/pageinspect.html\" target=\"_blank\" rel=\"noopener\">pageinspect<\/a> extension we can confirm that this page is just for metadata information:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create extension pageinspect;\nCREATE EXTENSION\npostgres=# SELECT * FROM bt_metap('i1');\n magic  | version | root | level | fastroot | fastlevel \n--------+---------+------+-------+----------+-----------\n 340322 |       2 |    0 |     0 |        0 |         0\n(1 row)\npostgres=# SELECT * FROM bt_page_stats('i1', 0);\nERROR:  block 0 is a meta page\npostgres=# \n<\/pre>\n<p>The remaining questions is: When will the free space map and the visibility map be created? After or with the first insert?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# insert into t1 (a) values (1);\nINSERT 0 1\npostgres=# ! ls -la $PGDATA\/base\/33845\/33933*\n-rw-------. 1 postgres postgres 8192 Jul 24 08:19 \/u02\/pgdata\/10\/PG103\/base\/33845\/33933\n<\/pre>\n<p>Definitely not. The answer is: vacuum:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# vacuum t1;\nVACUUM\npostgres=# ! ls -la $PGDATA\/base\/33845\/33933*\n-rw-------. 1 postgres postgres  8192 Jul 24 08:19 \/u02\/pgdata\/10\/PG103\/base\/33845\/33933\n-rw-------. 1 postgres postgres 24576 Jul 24 08:22 \/u02\/pgdata\/10\/PG103\/base\/33845\/33933_fsm\n-rw-------. 1 postgres postgres  8192 Jul 24 08:22 \/u02\/pgdata\/10\/PG103\/base\/33845\/33933_vm\n<\/pre>\n<p>Hope that helps &#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A question that pops up from time to time is: When we create a table or an index in PostgreSQL are the files on disk created immediately or is this something that happens when the first row is inserted? The question mostly is coming from Oracle DBAs because in Oracle you can have deferred segment [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"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-11510","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>When does PostgreSQL create the table and index files on disk? - 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\/when-does-postgresql-create-the-table-and-index-files-on-disk\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"When does PostgreSQL create the table and index files on disk?\" \/>\n<meta property=\"og:description\" content=\"A question that pops up from time to time is: When we create a table or an index in PostgreSQL are the files on disk created immediately or is this something that happens when the first row is inserted? The question mostly is coming from Oracle DBAs because in Oracle you can have deferred segment [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-05T10:26:34+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 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\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"When does PostgreSQL create the table and index files on disk?\",\"datePublished\":\"2018-08-05T10:26:34+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/\"},\"wordCount\":220,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/\",\"name\":\"When does PostgreSQL create the table and index files on disk? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-08-05T10:26:34+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-does-postgresql-create-the-table-and-index-files-on-disk\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"When does PostgreSQL create the table and index files on disk?\"}]},{\"@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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"When does PostgreSQL create the table and index files on disk? - 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\/when-does-postgresql-create-the-table-and-index-files-on-disk\/","og_locale":"en_US","og_type":"article","og_title":"When does PostgreSQL create the table and index files on disk?","og_description":"A question that pops up from time to time is: When we create a table or an index in PostgreSQL are the files on disk created immediately or is this something that happens when the first row is inserted? The question mostly is coming from Oracle DBAs because in Oracle you can have deferred segment [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/","og_site_name":"dbi Blog","article_published_time":"2018-08-05T10:26:34+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"When does PostgreSQL create the table and index files on disk?","datePublished":"2018-08-05T10:26:34+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/"},"wordCount":220,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/","url":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/","name":"When does PostgreSQL create the table and index files on disk? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-08-05T10:26:34+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/when-does-postgresql-create-the-table-and-index-files-on-disk\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"When does PostgreSQL create the table and index files on disk?"}]},{"@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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11510","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\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11510"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11510\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11510"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11510"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11510"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11510"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}