{"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":"

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 creation<\/a>. In PostgreSQL there is no parameter for that so lets do a quick test.<\/p>\n

<\/p>\n

We start with a simple table:<\/p>\n

\npostgres=# create table t1 ( a int );\nCREATE TABLE\n<\/pre>\n
To get the real file name we can either use the pg_relation_filepath<\/a> function:<\/p>\n
\npostgres=# select pg_relation_filepath('t1');\n pg_relation_filepath \n----------------------\n base\/33845\/33933\n(1 row)\n<\/pre>\n
… or we can use the oid2name<\/a> utility:<\/p>\n
\npostgres@pgbox:\/home\/postgres\/ [PG10] oid2name -d postgres -t t1\nFrom database \"postgres\":\n  Filenode  Table Name\n----------------------\n     33933          t1\n<\/pre>\n
Now we can easily check if that file is already existing:<\/p>\n
\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
It is already there but empty. The files for the visibility map<\/a> and the free space map<\/a> are not yet created:<\/p>\n
\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
What happens when we create an index on that empty table?<\/p>\n
\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
The file is created immediately as well but it is not empty. It is exactly one page (my blocksize is 8k). Using the pageinspect<\/a> extension we can confirm that this page is just for metadata information:<\/p>\n
\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
The remaining questions is: When will the free space map and the visibility map be created? After or with the first insert?<\/p>\n
\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
Definitely not. The answer is: vacuum:<\/p>\n
\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
Hope that helps …<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/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":[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":"\nWhen 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 […]\" \/>\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=\"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=\"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\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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 & 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\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\/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":"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 […]","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":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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 & 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\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/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\/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\/29"}],"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}]}}