2

I have a PostgreSQL table which is quite large at the moment: 340 columns. This table is designed to hold the styling for a complex PDF.

Over time I have made changes: dropped columns, added some new ones etc ... and it may still evolve further. Actually my current need is to double up the number of columns.

As per https://www.postgresql.org/docs/current/limits.html the upper limit for a table is :

  • 1600 columns
  • heap-page of 8192-byte

I have simulated in my Rails app all possible cases to calculate the maximum heap page and it looks fine at the moment with 2.5KB. Most of my columns are of type Boolean (1 byte), Integer (4 bytes) and double precision (8 bytes). Also some character varying but in low amount.

Also Postgres documentation specifies : "Columns that have been dropped from the table also contribute to the maximum column limit. Moreover, although the dropped column values for newly created tuples are internally marked as null in the tuple's null bitmap, the null bitmap also occupies space."

As I don't want to use JSON columns, I will not double up the number of columns but rather create another table. Yet the above 340 may be pushed further than 340 columns.

So here my questions :

  • How to know the total number of columns, including the ones destroyed, from the psql client
  • What is the size of a tuple from a destroyed column ?
  • Is it possible to get the heap page size from a specific record, still from psql ?
  • How would the database fail in case one of the two limits is reached : is the database broken, the table or just the record (in case the limit reached is heap page size)

1 Answer 1

5

The number of columns for a table, including dropped ones, can be found with

SELECT count(*) FROM pg_attribute
WHERE attrelid = 'table_name'::regclass
  AND attnum > 0;

When you drop a column, the existing rows don't change. So the value of the dropped column is still there with the same size as before. New rows will have a NULL value there, which doesn't occupy any extra space, so for new rows there is no overhead except for a single bit in the null mask.

The size of a table row can be found with

SELECT pg_column_size(table_name) FROM table_name;

If a limit for the size of a row or the number of columns is reached, INSERTing the offending row will cause an error, and nothing else will be broken.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.