{"id":29070,"date":"2023-11-05T14:08:17","date_gmt":"2023-11-05T13:08:17","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=29070"},"modified":"2023-11-05T14:08:19","modified_gmt":"2023-11-05T13:08:19","slug":"what-is-the-maximum-number-of-columns-for-a-table-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/","title":{"rendered":"What is the maximum number of columns for a table in PostgreSQL?"},"content":{"rendered":"\n

In our PostgreSQL DBA Essentials<\/a> workshop we’re also letting the attendees know about the limits of PostgreSQL. Those limits are documented<\/a> but one of those limits might not be very clear. The limit I am talking about is the maximum number of columns which can be in a table. In the documentation you’ll find this: “The maximum number of columns for a table is further reduced as the tuple being stored must fit in a single 8192-byte heap page. For example, excluding the tuple header, a tuple made up of 1600 int<\/code> columns would consume 6400 bytes and could be stored in a heap page, but a tuple of 1600 bigint<\/code> columns would consume 12800 bytes and would therefore not fit inside a heap page. Variable-length fields of types such as text<\/code>, varchar<\/code>, and char<\/code> can have their values stored out of line in the table’s TOAST table when the values are large enough to require it. Only an 18-byte pointer must remain inside the tuple in the table’s heap. For shorter length variable-length fields, either a 4-byte or 1-byte field header is used and the value is stored inside the heap tuple.”<\/p>\n\n\n\n

Depending on how you read this it might be clear or it might not be clear. This is how I read it: You cannot create a table which contains data types which in total would exceed 8160 bytes, which is the blocksize minus the tuple header. Lets see if this is true or I did understand something wrong.<\/p>\n\n\n\n

Here is a little bash script which generates a table consisting of n columns of a specific data type. There is not any error handling in the script, so the data type needs to exist:<\/p>\n\n\n

\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] cat gen_tab.sh \n#!\/bin\/bash\nAMOUNT_OF_COLS=$1\nDATA_TYPE=$2\nSTATEMENT="create table t ( "\nfor (( i=1 ; i<${AMOUNT_OF_COLS} ; i++ )); \ndo\n  STATEMENT+=" col${i} ${DATA_TYPE},"\ndone\nSTATEMENT+="col${AMOUNT_OF_COLS} ${DATA_TYPE} );"\necho ${STATEMENT}\n<\/pre><\/div>\n\n\n
When we create a table containing 1600 int columns, does that work?<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] .\/gen_tab.sh 1600 int > a.sql\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -f a.sql\nCREATE TABLE\n<\/pre><\/div>\n\n\n
This works just fine and is consistent with the documentation. What happens if we do the same but we use a bigint instead of an int column? The way I’ve read the documentation this should not work:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -c "drop table t"\nDROP TABLE\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] .\/gen_tab.sh 1600 bigint > a.sql\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -f a.sql\nCREATE TABLE\n<\/pre><\/div>\n\n\n
This works fine as well and I was a bit surprised by that. What the documentation is really saying is, that an row which consumes more than 8160 bytes which fail to insert or update. Here is another simple bash script to test this:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] cat .\/insert_data.sh \n#!\/bin\/bash\nAMOUNT_OF_COLS=$1\nDATA=$2\nSTATEMENT="insert into t values ( "\nfor (( i=1 ; i<${AMOUNT_OF_COLS} ; i++ )); \ndo\n  STATEMENT+=" ${DATA},"\ndone\nSTATEMENT+="${DATA} );"\necho ${STATEMENT}\n<\/pre><\/div>\n\n\n
Using this against the table with the 1600 bigint columns will fail:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] .\/insert_data.sh 1600 1 &gt; b.sql\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -f b.sql\npsql:b.sql:1: ERROR:  row is too big: size 12824, maximum size 8160\n<\/pre><\/div>\n\n\n
Doing the same against a table with 1600 int columns will succeed:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -c "drop table t"\nDROP TABLE\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] .\/gen_tab.sh 1600 int > a.sql\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -f a.sql\nCREATE TABLE\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] .\/insert_data.sh 1600 1 > b.sql\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -f b.sql \nINSERT 0 1\n<\/pre><\/div>\n\n\n
So it is not about the types of columns only when it comes to the maximum number of columns for a table. It is also about the content of the rows coming in.<\/p>\n\n\n\n
Btw: Deleted columns also count against the limit of 1600. Consider this:<\/p>\n\n\n
\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -c "drop table t"\nDROP TABLE\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] .\/gen_tab.sh 1600 int > a.sql\npostgres@debian12-pg:\/home\/postgres\/ [pgdev] psql -f a.sql\nCREATE TABLE\n<\/pre><\/div>\n\n\n
This created a table with 1600 columns. Let’s drop two and add another one:<\/p>\n\n\n
\npostgres=# alter table t drop column col1600;\nALTER TABLE\npostgres=# alter table t drop column col1599;\nALTER TABLE\npostgres=# alter table t add column xxx int;\nERROR:  tables can have at most 1600 columns\npostgres=# \n<\/pre><\/div>\n\n\n
Even if we dropped two column, we cannot add another one afterwards.<\/p>\n","protected":false},"excerpt":{"rendered":"
In our PostgreSQL DBA Essentials workshop we’re also letting the attendees know about the limits of PostgreSQL. Those limits are documented but one of those limits might not be very clear. The limit I am talking about is the maximum number of columns which can be in a table. In the documentation you’ll find this: […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[2749],"class_list":["post-29070","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql","type-postgresql"],"acf":[],"yoast_head":"\nWhat is the maximum number of columns for a table 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\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What is the maximum number of columns for a table in PostgreSQL?\" \/>\n<meta property=\"og:description\" content=\"In our PostgreSQL DBA Essentials workshop we’re also letting the attendees know about the limits of PostgreSQL. Those limits are documented but one of those limits might not be very clear. The limit I am talking about is the maximum number of columns which can be in a table. In the documentation you’ll find this: […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-05T13:08:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-05T13:08:19+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=\"3 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\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"What is the maximum number of columns for a table in PostgreSQL?\",\"datePublished\":\"2023-11-05T13:08:17+00:00\",\"dateModified\":\"2023-11-05T13:08:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/\"},\"wordCount\":476,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/\",\"name\":\"What is the maximum number of columns for a table in PostgreSQL? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2023-11-05T13:08:17+00:00\",\"dateModified\":\"2023-11-05T13:08:19+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What is the maximum number of columns for a table 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":"What is the maximum number of columns for a table 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\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"What is the maximum number of columns for a table in PostgreSQL?","og_description":"In our PostgreSQL DBA Essentials workshop we’re also letting the attendees know about the limits of PostgreSQL. Those limits are documented but one of those limits might not be very clear. The limit I am talking about is the maximum number of columns which can be in a table. In the documentation you’ll find this: […]","og_url":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2023-11-05T13:08:17+00:00","article_modified_time":"2023-11-05T13:08:19+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"What is the maximum number of columns for a table in PostgreSQL?","datePublished":"2023-11-05T13:08:17+00:00","dateModified":"2023-11-05T13:08:19+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/"},"wordCount":476,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/","name":"What is the maximum number of columns for a table in PostgreSQL? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-11-05T13:08:17+00:00","dateModified":"2023-11-05T13:08:19+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/what-is-the-maximum-number-of-columns-for-a-table-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"What is the maximum number of columns for a table 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\/29070","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=29070"}],"version-history":[{"count":8,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/29070\/revisions"}],"predecessor-version":[{"id":29251,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/29070\/revisions\/29251"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=29070"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=29070"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=29070"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=29070"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}