2

On the one hand the documentation states clearly that

When a column is added with ADD COLUMN and a non-volatile DEFAULT is specified, the default is evaluated at the time of the statement and the result stored in the table's metadata. That value will be used for the column for all existing rows. If no DEFAULT is specified, NULL is used. In neither case is a rewrite of the table required.

https://www.postgresql.org/docs/12/sql-altertable.html

On the other hand I've heard that because of tuple headers structure in some cases a table might be rewritten.

The documentation states that there are three fields responsible for null values storage behavior

The null bitmap is only present if the HEAP_HASNULL bit is set in t_infomask. If it is present it begins just after the fixed header and occupies enough bytes to have one bit per data column (that is, the number of bits that equals the attribute count in t_infomask2). In this list of bits, a 1 bit indicates not-null, a 0 bit is a null. When the bitmap is not present, all columns are assumed not-null.

https://www.postgresql.org/docs/12/storage-page-layout.html

I used this information and pageinspect extension to see what is going on with tuple headers when nullable columns are added or deleted. In all cases when I add or delete nullable column I see that existed tuples never change their headers.

So, I cannot find any evidence to the statement that a table might be rewritten. Fields t_infomask, t_infomask2, t_bits never change their values.

Am I miss something? Can I say that adding nullable columns to high-volume tables is absolutely safe in terms of disk activity (excluding the fact of exclusive locking)?

Dmitrii Apanasevich
  • 325
  • 3
  • 13
  • It is hard to comment meaningfully on what unnamed sources might have told you. Do you know what version they were talking about? – jjanes Feb 18 '21 at 16:36
  • No, I have no idea about exact version. I agree, that it looks like rumors. That is why I decided to ask a community. – Dmitrii Apanasevich Feb 19 '21 at 13:15

1 Answers1

0

The behavior has changed in PostgreSQL v11. Before that, adding a column with a non-NULL default will rewrite the table.

The point is that from v11 on, the tuple and its header don't have to be modified, adding such a column is just a metadata change. You won't see that with pageinspect. NULL values are never stored in the tuple, there is just the NULL bitmap, which is present if one of the table columns is nullable.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I'm interested in nullable columns only. On the contrary, `pageinspect` alows to see tuple headers as well as its data. Look here https://pgpedia.info/p/pageinspect.html for example. – Dmitrii Apanasevich Feb 18 '21 at 12:40