What is the maximum number of columns for a table in PostgreSQL?

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: “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 columns would consume 6400 bytes and could be stored in a heap page, but a tuple of 1600 bigint columns would consume 12800 bytes and would therefore not fit inside a heap page. Variable-length fields of types such as text, varchar, and char 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.”

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.

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:

postgres@debian12-pg:/home/postgres/ [pgdev] cat gen_tab.sh 
#!/bin/bash
AMOUNT_OF_COLS=$1
DATA_TYPE=$2
STATEMENT="create table t ( "
for (( i=1 ; i<${AMOUNT_OF_COLS} ; i++ )); 
do
  STATEMENT+=" col${i} ${DATA_TYPE},"
done
STATEMENT+="col${AMOUNT_OF_COLS} ${DATA_TYPE} );"
echo ${STATEMENT}

When we create a table containing 1600 int columns, does that work?

postgres@debian12-pg:/home/postgres/ [pgdev] ./gen_tab.sh 1600 int > a.sql
postgres@debian12-pg:/home/postgres/ [pgdev] psql -f a.sql
CREATE TABLE

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:

postgres@debian12-pg:/home/postgres/ [pgdev] psql -c "drop table t"
DROP TABLE
postgres@debian12-pg:/home/postgres/ [pgdev] ./gen_tab.sh 1600 bigint > a.sql
postgres@debian12-pg:/home/postgres/ [pgdev] psql -f a.sql
CREATE TABLE

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:

postgres@debian12-pg:/home/postgres/ [pgdev] cat ./insert_data.sh 
#!/bin/bash
AMOUNT_OF_COLS=$1
DATA=$2
STATEMENT="insert into t values ( "
for (( i=1 ; i<${AMOUNT_OF_COLS} ; i++ )); 
do
  STATEMENT+=" ${DATA},"
done
STATEMENT+="${DATA} );"
echo ${STATEMENT}

Using this against the table with the 1600 bigint columns will fail:

postgres@debian12-pg:/home/postgres/ [pgdev] ./insert_data.sh 1600 1 > b.sql
postgres@debian12-pg:/home/postgres/ [pgdev] psql -f b.sql
psql:b.sql:1: ERROR:  row is too big: size 12824, maximum size 8160

Doing the same against a table with 1600 int columns will succeed:

postgres@debian12-pg:/home/postgres/ [pgdev] psql -c "drop table t"
DROP TABLE
postgres@debian12-pg:/home/postgres/ [pgdev] ./gen_tab.sh 1600 int > a.sql
postgres@debian12-pg:/home/postgres/ [pgdev] psql -f a.sql
CREATE TABLE
postgres@debian12-pg:/home/postgres/ [pgdev] ./insert_data.sh 1600 1 > b.sql
postgres@debian12-pg:/home/postgres/ [pgdev] psql -f b.sql 
INSERT 0 1

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.

Btw: Deleted columns also count against the limit of 1600. Consider this:

postgres@debian12-pg:/home/postgres/ [pgdev] psql -c "drop table t"
DROP TABLE
postgres@debian12-pg:/home/postgres/ [pgdev] ./gen_tab.sh 1600 int > a.sql
postgres@debian12-pg:/home/postgres/ [pgdev] psql -f a.sql
CREATE TABLE

This created a table with 1600 columns. Let’s drop two and add another one:

postgres=# alter table t drop column col1600;
ALTER TABLE
postgres=# alter table t drop column col1599;
ALTER TABLE
postgres=# alter table t add column xxx int;
ERROR:  tables can have at most 1600 columns
postgres=# 

Even if we dropped two column, we cannot add another one afterwards.