Document atthasmissing default optimization avoids verification table scan

On 9/24/21, 7:30 AM, "James Coleman" <jtc331@gmail.com> wrote:

When PG11 added the ability for ALTER TABLE ADD COLUMN to set a constant
default value without rewriting the table the doc changes did not note
how the new feature interplayed with ADD COLUMN DEFAULT NOT NULL.
Previously such a new column required a verification table scan to
ensure no values were null. That scan happens under an exclusive lock on
the table, so it can have a meaningful impact on database "accessible
uptime".

I'm likely misunderstanding, but are you saying that adding a new
column with a default value and a NOT NULL constraint used to require
a verification scan?

+     Additionally adding a column with a constant default value avoids a
+     a table scan to verify no <literal>NULL</literal> values are present.

Should this clarify that it's referring to NOT NULL constraints?

Nathan

On Wed, Jan 19, 2022 at 5:08 PM Bossart, Nathan <bossartn@amazon.com> wrote:

On 9/24/21, 7:30 AM, "James Coleman" <jtc331@gmail.com> wrote:

When PG11 added the ability for ALTER TABLE ADD COLUMN to set a constant
default value without rewriting the table the doc changes did not note
how the new feature interplayed with ADD COLUMN DEFAULT NOT NULL.
Previously such a new column required a verification table scan to
ensure no values were null. That scan happens under an exclusive lock on
the table, so it can have a meaningful impact on database "accessible
uptime".

I'm likely misunderstanding, but are you saying that adding a new
column with a default value and a NOT NULL constraint used to require
a verification scan?

As a side-effect of rewriting every live record in the table and indexes to
brand new files, yes. I doubt an actual independent scan was performed
since the only way for the newly written tuples to not have the default
value inserted would be a severe server bug.

+     Additionally adding a column with a constant default value avoids a
+     a table scan to verify no <literal>NULL</literal> values are present.

Should this clarify that it's referring to NOT NULL constraints?

This doesn't seem like relevant material to comment on. It's an
implementation detail that is sufficiently covered by "making the ALTER
TABLE very fast even on large tables".

Also, the idea of performing that scan seems ludicrous. I just added the
column and told it to populate with default values - why do you need to
check that your server didn't miss any?

David J.

On Wed, Jan 19, 2022 at 7:51 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Wed, Jan 19, 2022 at 5:08 PM Bossart, Nathan <bossartn@amazon.com> wrote:

On 9/24/21, 7:30 AM, "James Coleman" <jtc331@gmail.com> wrote:

When PG11 added the ability for ALTER TABLE ADD COLUMN to set a constant
default value without rewriting the table the doc changes did not note
how the new feature interplayed with ADD COLUMN DEFAULT NOT NULL.
Previously such a new column required a verification table scan to
ensure no values were null. That scan happens under an exclusive lock on
the table, so it can have a meaningful impact on database "accessible
uptime".

I'm likely misunderstanding, but are you saying that adding a new
column with a default value and a NOT NULL constraint used to require
a verification scan?

As a side-effect of rewriting every live record in the table and indexes to brand new files, yes. I doubt an actual independent scan was performed since the only way for the newly written tuples to not have the default value inserted would be a severe server bug.

I've confirmed it wasn't a separate scan, but it does evaluate all
constraints (it doesn't have any optimizations for skipping ones
probably true by virtue of the new default).

+     Additionally adding a column with a constant default value avoids a
+     a table scan to verify no <literal>NULL</literal> values are present.

Should this clarify that it's referring to NOT NULL constraints?

This doesn't seem like relevant material to comment on. It's an implementation detail that is sufficiently covered by "making the ALTER TABLE very fast even on large tables".

Also, the idea of performing that scan seems ludicrous. I just added the column and told it to populate with default values - why do you need to check that your server didn't miss any?

I'm open to the idea of wordsmithing here, of course, but I strongly
disagree that this is irrelevant data. There are plenty of
optimizations Postgres could theoretically implement but doesn't, so
measuring what should happen by what you think is obvious ("told it to
populate with default values - why do you need to check") is clearly
not valid.

This patch actually came out of our specifically needing to verify
that this is true before an op precisely because docs aren't actually
clear and because we can't risk a large table scan under an exclusive
lock. We're clearly not the only ones with that question; it came up
in a comment on this blog post announcing the newly committed feature
[1]: .

I realize that most users aren't as worried about this kind of
specific detail about DDL as we are (requiring absolutely zero slow
DDL while under an exclusive lock), but it is relevant to high uptime
systems.

Thanks,
James Coleman

1: https://www.depesz.com/2018/04/04/waiting-for-postgresql-11-fast-alter-table-add-column-with-a-non-null-default/

On Wed, Jan 19, 2022 at 6:14 PM James Coleman <jtc331@gmail.com> wrote:

I'm open to the idea of wordsmithing here, of course, but I strongly
disagree that this is irrelevant data.

Ok, but wording aside, only changing a tip in the DDL - Add Table section
doesn't seem like a complete fix. The notes in alter table, where I'd look
for such an official directive first, need to be touched as well.

For the alter table docs maybe change/add to the existing sentence below
(I'm in favor of not pointing out that scanning the table doesn't mean we
are rewriting it, but maybe I'm making another unwarranted assumption
regarding obviousness...).

"Adding a CHECK or NOT NULL constraint requires scanning the table [but not
rewriting it] to verify that existing rows meet the constraint. It is
skipped when done as part of ADD COLUMN unless a table rewrite is required
anyway."

On that note, does the check constraint interplay with the default rewrite
avoidance in the same way?

For the Tip I'd almost rather redo it to say:

"Before PostgreSQL 11, adding a new column to a table required rewriting
that table, making it a very slow operation. More recent versions can
sometimes optimize away this rewrite and related validation scans. See the
notes in ALTER TABLE for details."

Though I suppose I'd accept something like (leave existing text,
alternative patch text):

"[...]large tables.\nIf the added column also has a not null constraint the
usual verification scan is also skipped."

"constant" is used in the Tip, "non-volatile" is used in alter table -
hence a desire to have just one source of truth, with alter table being the
correct place. We should sync them up otherwise.

David J.

On Wed, Jan 19, 2022 at 9:34 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Wed, Jan 19, 2022 at 6:14 PM James Coleman <jtc331@gmail.com> wrote:

I'm open to the idea of wordsmithing here, of course, but I strongly
disagree that this is irrelevant data.

Ok, but wording aside, only changing a tip in the DDL - Add Table section doesn't seem like a complete fix. The notes in alter table, where I'd look for such an official directive first, need to be touched as well.

For the alter table docs maybe change/add to the existing sentence below (I'm in favor of not pointing out that scanning the table doesn't mean we are rewriting it, but maybe I'm making another unwarranted assumption regarding obviousness...).

"Adding a CHECK or NOT NULL constraint requires scanning the table [but not rewriting it] to verify that existing rows meet the constraint. It is skipped when done as part of ADD COLUMN unless a table rewrite is required anyway."

I'm looking over the docs again to see how it might be better
structured; point is well taken that we should have it clearly in the
primary place.

On that note, does the check constraint interplay with the default rewrite avoidance in the same way?

I hadn't checked until you asked, but interestingly, no it doesn't (I
assume you mean scan not rewrite in this context):

test=# select seq_scan from pg_stat_all_tables where relname = 't2';
seq_scan
----------
2
test=# alter table t2 add column i3 int not null default 5;
ALTER TABLE
test=# select seq_scan from pg_stat_all_tables where relname = 't2';
seq_scan
----------
2
test=# alter table t2 add column i4 int default 5 check (i4 < 50);
ALTER TABLE
test=# select seq_scan from pg_stat_all_tables where relname = 't2';
seq_scan
----------
3

That seems like an opportunity for improvement here, though it's
obviously a separate patch. I might poke around at that though
later...

For the Tip I'd almost rather redo it to say:

"Before PostgreSQL 11, adding a new column to a table required rewriting that table, making it a very slow operation. More recent versions can sometimes optimize away this rewrite and related validation scans. See the notes in ALTER TABLE for details."

Though I suppose I'd accept something like (leave existing text, alternative patch text):

"[...]large tables.\nIf the added column also has a not null constraint the usual verification scan is also skipped."

"constant" is used in the Tip, "non-volatile" is used in alter table - hence a desire to have just one source of truth, with alter table being the correct place. We should sync them up otherwise.

As noted I'll look over how restructuring might improve and reply with
an updated proposed patch.

Thanks,
James Coleman

On 1/19/22, 5:15 PM, "James Coleman" <jtc331@gmail.com> wrote:

I'm open to the idea of wordsmithing here, of course, but I strongly
disagree that this is irrelevant data. There are plenty of
optimizations Postgres could theoretically implement but doesn't, so
measuring what should happen by what you think is obvious ("told it to
populate with default values - why do you need to check") is clearly
not valid.

This patch actually came out of our specifically needing to verify
that this is true before an op precisely because docs aren't actually
clear and because we can't risk a large table scan under an exclusive
lock. We're clearly not the only ones with that question; it came up
in a comment on this blog post announcing the newly committed feature
[1].

My initial reaction was similar to David's. It seems silly to
document that we don't do something that seems obviously unnecessary.
However, I think you make a convincing argument for including it. I
agree with David's feedback on where this information should go.

Nathan

On 1/20/22 12:25, Bossart, Nathan wrote:

On 1/19/22, 5:15 PM, "James Coleman" <jtc331@gmail.com> wrote:

I'm open to the idea of wordsmithing here, of course, but I strongly
disagree that this is irrelevant data. There are plenty of
optimizations Postgres could theoretically implement but doesn't, so
measuring what should happen by what you think is obvious ("told it to
populate with default values - why do you need to check") is clearly
not valid.

This patch actually came out of our specifically needing to verify
that this is true before an op precisely because docs aren't actually
clear and because we can't risk a large table scan under an exclusive
lock. We're clearly not the only ones with that question; it came up
in a comment on this blog post announcing the newly committed feature
[1].

My initial reaction was similar to David's. It seems silly to
document that we don't do something that seems obviously unnecessary.
However, I think you make a convincing argument for including it. I
agree with David's feedback on where this information should go.

I still don't understand the confusion. When you add a new column with a
non-null non-volatile default, none of the existing rows has any storage
for the new column, so there is nothing to scan and nothing to verify on
such rows. Only the catalog is changed. This is true whether or not the
new column is constrained by NOT NULL. I don't understand what people
think might have had to be verified by scanning the table.

If what's happening is not clear from the docs then by all means let's
make it clear. But in general I don't think we should talk about what we
used to do.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Thu, Jan 20, 2022 at 3:31 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 1/20/22 12:25, Bossart, Nathan wrote:

On 1/19/22, 5:15 PM, "James Coleman" <jtc331@gmail.com> wrote:

I'm open to the idea of wordsmithing here, of course, but I strongly
disagree that this is irrelevant data. There are plenty of
optimizations Postgres could theoretically implement but doesn't, so
measuring what should happen by what you think is obvious ("told it to
populate with default values - why do you need to check") is clearly
not valid.

This patch actually came out of our specifically needing to verify
that this is true before an op precisely because docs aren't actually
clear and because we can't risk a large table scan under an exclusive
lock. We're clearly not the only ones with that question; it came up
in a comment on this blog post announcing the newly committed feature
[1].

My initial reaction was similar to David's. It seems silly to
document that we don't do something that seems obviously unnecessary.
However, I think you make a convincing argument for including it. I
agree with David's feedback on where this information should go.

I still don't understand the confusion. When you add a new column with a
non-null non-volatile default, none of the existing rows has any storage
for the new column, so there is nothing to scan and nothing to verify on
such rows. Only the catalog is changed. This is true whether or not the
new column is constrained by NOT NULL. I don't understand what people
think might have had to be verified by scanning the table.

If what's happening is not clear from the docs then by all means let's
make it clear. But in general I don't think we should talk about what we
used to do.

This path isn't about talking about what we used to do (though that's
already in the docs); it is about trying to make it clear.

But actually "When you add a new column with a non-null non-volatile
default...there is nothing to scan" doesn't always hold as I showed
with the check constraint above. Other than that I think that phrasing
is actually almost close to the kind of clarity I'd like to see in the
docs.

As noted earlier I expect to be posting an updated patch soon.

Thanks,
James Coleman

On Thu, Jan 20, 2022 at 3:43 PM James Coleman <jtc331@gmail.com> wrote:

As noted earlier I expect to be posting an updated patch soon.

Here's the updated series. In 0001 I've moved the documentation tweak
into the ALTER TABLE notes section. In 0002 I've taken David J's
suggestion of shortening the "Tip" on the DDL page and mostly using it
to point people to the Notes section on the ALTER TABLE page.

Thanks,
James Coleman

On Fri, Jan 21, 2022 at 11:55 AM James Coleman <jtc331@gmail.com> wrote:

On Thu, Jan 20, 2022 at 3:43 PM James Coleman <jtc331@gmail.com> wrote:

As noted earlier I expect to be posting an updated patch soon.

Here's the updated series. In 0001 I've moved the documentation tweak
into the ALTER TABLE notes section. In 0002 I've taken David J's
suggestion of shortening the "Tip" on the DDL page and mostly using it
to point people to the Notes section on the ALTER TABLE page.

WFM

David J.

On 1/21/22 13:55, James Coleman wrote:

On Thu, Jan 20, 2022 at 3:43 PM James Coleman <jtc331@gmail.com> wrote:

As noted earlier I expect to be posting an updated patch soon.

Here's the updated series. In 0001 I've moved the documentation tweak
into the ALTER TABLE notes section. In 0002 I've taken David J's
suggestion of shortening the "Tip" on the DDL page and mostly using it
to point people to the Notes section on the ALTER TABLE page.

I don't really like the first part of patch 1, but as it gets removed by
patch 2 we can move past that.

+     Before <productname>PostgreSQL</productname> 11, adding a new
column to a
+     table required rewriting that table, making it a very slow operation.
+     More recent versions can sometimes optimize away this rewrite and
related
+     validation scans.  See the notes in <command>ALTER TABLE</command>
for details.

I know what it's replacing refers to release 11, but let's stop doing
that. How about something like this?

Adding a new column can sometimes require rewriting the table,
making it a very slow operation. However in many cases this rewrite
and related verification scans can be optimized away by using an
appropriate default value. See the notes in <command>ALTER
TABLE</command> for details.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Fri, Jan 21, 2022 at 2:08 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 1/21/22 13:55, James Coleman wrote:

+     Before <productname>PostgreSQL</productname> 11, adding a new
column to a
+     table required rewriting that table, making it a very slow operation.
+     More recent versions can sometimes optimize away this rewrite and
related
+     validation scans.  See the notes in <command>ALTER TABLE</command>
for details.

I know what it's replacing refers to release 11, but let's stop doing
that. How about something like this?

Adding a new column can sometimes require rewriting the table,
making it a very slow operation. However in many cases this rewrite
and related verification scans can be optimized away by using an
appropriate default value. See the notes in <command>ALTER
TABLE</command> for details.

I think it is a virtue, and am supported in that feeling by the existing
wording, to be explicit about the release before which these optimizations
can not happen. The docs generally use this to good effect without
overdoing it. This is a prime example.

The combined effect of "sometimes", "in many", "can be", and "an
appropriate" make this version harder to read than it probably needs to
be. I like the patch as-is over this; but I would want to give an
alternative wording more thought if it is insisted upon that mention of
PostgreSQL 11 goes away.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, Jan 21, 2022 at 2:08 PM Andrew Dunstan <andrew@dunslane.net> wrote:

I know what it's replacing refers to release 11, but let's stop doing
that. How about something like this?

Adding a new column can sometimes require rewriting the table,
making it a very slow operation. However in many cases this rewrite
and related verification scans can be optimized away by using an
appropriate default value. See the notes in <command>ALTER
TABLE</command> for details.

I think it is a virtue, and am supported in that feeling by the existing
wording, to be explicit about the release before which these optimizations
can not happen. The docs generally use this to good effect without
overdoing it. This is a prime example.

The fact of the matter is that optimizations of this sort have existed
for years. (For example, I think we've optimized away the rewrite
when the new column is DEFAULT NULL since the very beginning.) So it
does not help to write the text as if there were no such optimizations
before version N and they were all there in N.

I agree that Andrew's text could stand a pass of "omit needless words".
But I also think that we could be a bit more explicit about what "slow"
means. Maybe like

Adding a new column can require rewriting the whole table,
making it slow for large tables. However the rewrite can be optimized
away in some cases, depending on what default value is given to the
column. See <command>ALTER TABLE</command> for details.

(the ALTER TABLE reference should be a link, too)

regards, tom lane

On Fri, Jan 21, 2022 at 2:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Fri, Jan 21, 2022 at 2:08 PM Andrew Dunstan <andrew@dunslane.net>

wrote:

I know what it's replacing refers to release 11, but let's stop doing
that. How about something like this?

Adding a new column can sometimes require rewriting the table,
making it a very slow operation. However in many cases this rewrite
and related verification scans can be optimized away by using an
appropriate default value. See the notes in <command>ALTER
TABLE</command> for details.

I think it is a virtue, and am supported in that feeling by the existing
wording, to be explicit about the release before which these

optimizations

can not happen. The docs generally use this to good effect without
overdoing it. This is a prime example.

The fact of the matter is that optimizations of this sort have existed
for years. (For example, I think we've optimized away the rewrite
when the new column is DEFAULT NULL since the very beginning.) So it
does not help to write the text as if there were no such optimizations
before version N and they were all there in N.

Fair point, and indeed the v10 docs do mention the NULL (or no default)
optimization.

I agree that Andrew's text could stand a pass of "omit needless words".
But I also think that we could be a bit more explicit about what "slow"
means. Maybe like

Adding a new column can require rewriting the whole table,
making it slow for large tables. However the rewrite can be optimized
away in some cases, depending on what default value is given to the
column. See <command>ALTER TABLE</command> for details.

Comma needed after however.
You've removed the "constraint verification scan" portion of this. Maybe:
"""
...
column. The same applies for the NOT NULL constraint verification scan.
See <command>ALTER TABLE</command> for details.
"""

Re-reading this, the recommendation:

- However, if the default value is volatile (e.g.,
- <function>clock_timestamp()</function>)
- each row will need to be updated with the value calculated at the time
- <command>ALTER TABLE</command> is executed. To avoid a potentially
- lengthy update operation, particularly if you intend to fill the
column
- with mostly nondefault values anyway, it may be preferable to add the
- column with no default, insert the correct values using
- <command>UPDATE</command>, and then add any desired default as
described
- below.

has now been completely removed from the documentation. I suggest having
this remain as the Tip and turning the optimization stuff into a Note.

(the ALTER TABLE reference should be a link, too)

Yeah, the page does have a link already (fairly close by...) but with these
changes putting one here seems to make sense.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

You've removed the "constraint verification scan" portion of this.

Indeed, because that's got nothing to do with adding a new column
(per se; adding a constraint along with the column is a different
can of worms).

Re-reading this, the recommendation:

- However, if the default value is volatile (e.g.,
- <function>clock_timestamp()</function>)
- each row will need to be updated with the value calculated at the time
- <command>ALTER TABLE</command> is executed. To avoid a potentially
- lengthy update operation, particularly if you intend to fill the
column
- with mostly nondefault values anyway, it may be preferable to add the
- column with no default, insert the correct values using
- <command>UPDATE</command>, and then add any desired default as
described
- below.

has now been completely removed from the documentation.

Really? That's horrid, because that's directly useful advice.

regards, tom lane

On Fri, Jan 21, 2022 at 4:08 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On 1/21/22 13:55, James Coleman wrote:

On Thu, Jan 20, 2022 at 3:43 PM James Coleman <jtc331@gmail.com> wrote:

As noted earlier I expect to be posting an updated patch soon.

Here's the updated series. In 0001 I've moved the documentation tweak
into the ALTER TABLE notes section. In 0002 I've taken David J's
suggestion of shortening the "Tip" on the DDL page and mostly using it
to point people to the Notes section on the ALTER TABLE page.

I don't really like the first part of patch 1, but as it gets removed by
patch 2 we can move past that.

At first I was very confused by this feedback, but after looking at
the patch files I sent, that's my fault: I meant to remove the
modification of the "Tip" section but somehow missed that in what I
sent. I'll correct that in the next patch series.

James Coleman

On Fri, Jan 21, 2022 at 5:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

"David G. Johnston" <david.g.johnston@gmail.com> writes:

You've removed the "constraint verification scan" portion of this.

Indeed, because that's got nothing to do with adding a new column
(per se; adding a constraint along with the column is a different
can of worms).

Yeah. Initially I'd thought I'd wanted it there, but by explicitly
linking people to the ALTER TABLE docs for more details (I've made
that a link now too) I'm now inclined to agree that tightly focusing
the tip is better form.

Re-reading this, the recommendation:

- However, if the default value is volatile (e.g.,
- <function>clock_timestamp()</function>)
- each row will need to be updated with the value calculated at the time
- <command>ALTER TABLE</command> is executed. To avoid a potentially
- lengthy update operation, particularly if you intend to fill the
column
- with mostly nondefault values anyway, it may be preferable to add the
- column with no default, insert the correct values using
- <command>UPDATE</command>, and then add any desired default as
described
- below.

has now been completely removed from the documentation.

Really? That's horrid, because that's directly useful advice.

Remedied, but rewritten a bit to better fit with the new style/goal of
that tip).

Version 3 is attached.

James Coleman

On Fri, Jan 21, 2022 at 5:14 PM James Coleman <jtc331@gmail.com> wrote:

Really? That's horrid, because that's directly useful advice.

Remedied, but rewritten a bit to better fit with the new style/goal of
that tip).

Version 3 is attached.

Coming back to this after a respite I think the tip needs to be moved just
like everything else. For much the same reason (though this may only be a
personal bias), I know what SQL Commands do the various things that DDL
encompasses (especially the basics like adding a column) and so the DDL
section is really just a tutorial-like chapter that I will generally forget
about because I will go straight to the official source which is the SQL
Command Reference. My future self would want the tip to show up there. If
we put the tip after the existing paragraph that starts: "Adding a column
with a volatile DEFAULT or changing the type of an existing column..." the
need to specify an example function in the tip goes away - though maybe it
should be moved to the notes paragraph instead: "with a volatile DEFAULT
(e.g., clock_timestamp()) or changing the type of an existing column..."

David J.

On Sat, Jan 22, 2022 at 12:35 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Fri, Jan 21, 2022 at 5:14 PM James Coleman <jtc331@gmail.com> wrote:

Really? That's horrid, because that's directly useful advice.

Remedied, but rewritten a bit to better fit with the new style/goal of
that tip).

Version 3 is attached.

Coming back to this after a respite I think the tip needs to be moved just like everything else. For much the same reason (though this may only be a personal bias), I know what SQL Commands do the various things that DDL encompasses (especially the basics like adding a column) and so the DDL section is really just a tutorial-like chapter that I will generally forget about because I will go straight to the official source which is the SQL Command Reference. My future self would want the tip to show up there. If we put the tip after the existing paragraph that starts: "Adding a column with a volatile DEFAULT or changing the type of an existing column..." the need to specify an example function in the tip goes away - though maybe it should be moved to the notes paragraph instead: "with a volatile DEFAULT (e.g., clock_timestamp()) or changing the type of an existing column..."

In my mind that actually might be a reason to keep it that way. I
expect someone who's somewhat experienced to know there are things
(like table rewrites and scans) you need to consider and therefore go
to the ALTER TABLE page and read the details. But for someone newer
the tutorial page needs to introduce them to the idea that those
gotchas exist.

Thoughts?
James Coleman