[PATCH] updates to docs about HOT updates for BRIN
Hello,
I have a small documentation patch to the HOT updates page
<https://www.postgresql.org/docs/current/storage-hot.html>to add references
to summary (BRIN) indexes not blocking HOT updates
</messages/by-id/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg@mail.gmail.com>,
committed 19d8e2308b.
Thanks,
Elizabeth Christensen
Attachments:
0001-Adding-Summary-Index-Info-to-HOT-Update-Documentatio.patchapplication/octet-stream; name=0001-Adding-Summary-Index-Info-to-HOT-Update-Documentatio.patchDownload
From: elizabeth-christensen <elizabeth.christensen@crunchydata.com>
Date: Mon, 26 Feb 2024 12:09:22 -0600
Subject: [PATCH] Adding Summary Index Info to HOT Update Documentation page
Commit 19d8e2308b changed when the HOT update optimization is possible but neglected to update the Heap-Only Tuples (HOT) documentation. This patch updates that documentation accordingly.
Author: Elizabeth Christensen
Backpatch-through: 16
Discussion:
---
doc/src/sgml/storage.sgml | 12 +++++-------
1 file changed, 5 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index 3ea4e5526d..f12ed31483 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -1097,8 +1097,7 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- The update does not modify any columns referenced by the table's
- indexes, including expression and partial indexes.
+ The update only modifies columns which are un-indexed or are only
+ indexed with summarizing indexes (such as <acronym>BRIN</acronym>)
+ and does not update any columns referenced by the table's non-summary
+ indexes, including expression and partial non-summary indexes.
</para>
</listitem>
<listitem>
@@ -1114,7 +1113,7 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- New index entries are not needed to represent updated rows.
+ New index entries are not needed to represent updated rows,
+ however, summary indexes may still need to be updated.
</para>
</listitem>
<listitem>
@@ -1130,11 +1129,10 @@ data. Empty in ordinary tables.</entry>
</para>
<para>
- In summary, heap-only tuple updates can only be created
- if columns used by indexes are not updated. You can
- increase the likelihood of sufficient page space for
- <acronym>HOT</acronym> updates by decreasing a table's <link
- linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
+ In summary, heap-only tuple updates can only be created if columns used by
+ non-summary indexes are not updated. You can increase the likelihood of
+ sufficient page space for <acronym>HOT</acronym> updates by decreasing
+ a table's <link linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
If you don't, <acronym>HOT</acronym> updates will still happen because
new rows will naturally migrate to new pages and existing pages with
sufficient free space for new row versions. The system view <link
--
2.37.0
Greetings,
* Elizabeth Christensen (elizabeth.christensen@crunchydata.com) wrote:
I have a small documentation patch to the HOT updates page
<https://www.postgresql.org/docs/current/storage-hot.html>to add references
to summary (BRIN) indexes not blocking HOT updates
</messages/by-id/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg@mail.gmail.com>,
committed 19d8e2308b.
Sounds good to me, though the attached patch didn't want to apply, and
it isn't immediately clear to me why, but perhaps you could try saving
the patch from a different editor and re-sending it?
Thanks,
Stephen
On Feb 26, 2024, at 4:21 PM, Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
* Elizabeth Christensen (elizabeth.christensen@crunchydata.com) wrote:
I have a small documentation patch to the HOT updates page
<https://www.postgresql.org/docs/current/storage-hot.html>to add references
to summary (BRIN) indexes not blocking HOT updates
</messages/by-id/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg@mail.gmail.com>,
committed 19d8e2308b.Sounds good to me, though the attached patch didn't want to apply, and
it isn't immediately clear to me why, but perhaps you could try saving
the patch from a different editor and re-sending it?Thanks,
Stephen
Thanks Stephen, attempt #2 here.
Elizabeth
Attachments:
v2-0001-Adding-Summary-Index-Info-to-HOT-Update-Documentatio.patchapplication/octet-stream; name=v2-0001-Adding-Summary-Index-Info-to-HOT-Update-Documentatio.patch; x-unix-mode=0644Download
From: elizabeth-christensen <elizabethannegarrett@gmail.com>
Date: Mon, 26 Feb 2024 12:09:22 -0600
Subject: [PATCH] Adding Summary Index Info to HOT Update Documentation page
Commit 19d8e2308b changed when the HOT update optimization is possible but neglected to update the Heap-Only Tuples (HOT) documentation. This patch updates that documentation accordingly.
Author: Elizabeth Christensen
Backpatch-through: 16
Discussion:
---
doc/src/sgml/storage.sgml | 18 ++++++++++--------
1 file changed, 10 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index 3ea4e5526d..314469706e 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -1097,8 +1097,10 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- The update does not modify any columns referenced by the table's
- indexes, including expression and partial indexes.
+ The update only modifies columns which are un-indexed or are only indexed with
+ summarizing indexes (such as BRIN) and does not update any columns referenced
+ by the table's non-summary indexes, including expression and partial
+ non-summary indexes.
</para>
</listitem>
<listitem>
@@ -1114,7 +1116,8 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- New index entries are not needed to represent updated rows.
+ New index entries are not needed to represent updated rows, however,
+ summary indexes may still need to be updated.
</para>
</listitem>
<listitem>
@@ -1130,11 +1133,10 @@ data. Empty in ordinary tables.</entry>
</para>
<para>
- In summary, heap-only tuple updates can only be created
- if columns used by indexes are not updated. You can
- increase the likelihood of sufficient page space for
- <acronym>HOT</acronym> updates by decreasing a table's <link
- linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
+ In summary, heap-only tuple updates can only be created if columns used by
+ non-summary indexes are not updated. You can increase the likelihood of
+ sufficient page space for <acronym>HOT</acronym> updates by decreasing
+ a table's <link linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
If you don't, <acronym>HOT</acronym> updates will still happen because
new rows will naturally migrate to new pages and existing pages with
sufficient free space for new row versions. The system view <link
--
2.37.0
Greetings,
* Elizabeth Christensen (elizabeth.christensen@crunchydata.com) wrote:
On Feb 26, 2024, at 4:21 PM, Stephen Frost <sfrost@snowman.net> wrote:
* Elizabeth Christensen (elizabeth.christensen@crunchydata.com) wrote:I have a small documentation patch to the HOT updates page
<https://www.postgresql.org/docs/current/storage-hot.html>to add references
to summary (BRIN) indexes not blocking HOT updates
</messages/by-id/CAFp7QwpMRGcDAQumN7onN9HjrJ3u4X3ZRXdGFT0K5G2JWvnbWg@mail.gmail.com>,
committed 19d8e2308b.Sounds good to me, though the attached patch didn't want to apply, and
it isn't immediately clear to me why, but perhaps you could try saving
the patch from a different editor and re-sending it?Thanks Stephen, attempt #2 here.
Here's an updated patch which tries to improve on the wording a bit by
having it be a bit more consistent. Would certainly welcome feedback on
it though, of course. This is a tricky bit of language to write and
a complex optimization to explain.
Thanks!
Stephen
Attachments:
v3-0001-Adding-Summary-Index-Info-to-HOT-Update-Documentatio.patchtext/x-diff; charset=us-asciiDownload
From bb88237d1f807572a496ff2a267ab56bef61ac8e Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Mon, 26 Feb 2024 17:17:54 -0500
Subject: [PATCH] docs: Update HOT update docs for 19d8e2308b
Commit 19d8e2308b changed when the HOT update optimization is possible
but neglected to update the Heap-Only Tuples (HOT) documentation. This
patch updates that documentation accordingly.
Author: Elizabeth Christensen
Reviewed-By: Stephen Frost
Backpatch-through: 16
Discussion: https://postgr.es/m/CABoUFXRjisr58Ct_3VsFEdQx+fJeQTWTdJnM7XAp=8MUbtoa9A@mail.gmail.com
---
doc/src/sgml/storage.sgml | 19 +++++++++++--------
1 file changed, 11 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index 3ea4e5526d..d6c53a8d25 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -1097,8 +1097,11 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- The update does not modify any columns referenced by the table's
- indexes, including expression and partial indexes.
+ The update only modifies columns which are not referenced by the
+ table's indexes or are only referenced by summarizing indexes (such
+ as <acronym>BRIN</acronym>) and does not update any columns
+ referenced by the table's non-summarizing indexes, including
+ expression and partial non-summarizing indexes.
</para>
</listitem>
<listitem>
@@ -1114,7 +1117,8 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- New index entries are not needed to represent updated rows.
+ New index entries are not needed to represent updated rows, however,
+ summary indexes may still need to be updated.
</para>
</listitem>
<listitem>
@@ -1130,11 +1134,10 @@ data. Empty in ordinary tables.</entry>
</para>
<para>
- In summary, heap-only tuple updates can only be created
- if columns used by indexes are not updated. You can
- increase the likelihood of sufficient page space for
- <acronym>HOT</acronym> updates by decreasing a table's <link
- linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
+ In summary, heap-only tuple updates can only be created if columns used by
+ non-summarizing indexes are not updated. You can increase the likelihood of
+ sufficient page space for <acronym>HOT</acronym> updates by decreasing
+ a table's <link linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
If you don't, <acronym>HOT</acronym> updates will still happen because
new rows will naturally migrate to new pages and existing pages with
sufficient free space for new row versions. The system view <link
--
2.34.1
Hello,
On 2024-Feb-26, Stephen Frost wrote:
Here's an updated patch which tries to improve on the wording a bit by
having it be a bit more consistent. Would certainly welcome feedback on
it though, of course. This is a tricky bit of language to write and
a complex optimization to explain.
Should we try to explain what is a "summarizing" index is? Right now
the only way to know is to look at the source code or attach a debugger
and see if IndexAmRoutine->amsummarizing is true. Maybe we can just say
"currently the only in-core summarizing index is BRIN" somewhere in the
page. (The patch's proposal to say "... such as BRIN" strikes me as too
vague.)
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this." (Fotis)
/messages/by-id/200606261359.k5QDxE2p004593@auth-smtp.hol.gr
Greetings,
* Alvaro Herrera (alvherre@alvh.no-ip.org) wrote:
On 2024-Feb-26, Stephen Frost wrote:
Here's an updated patch which tries to improve on the wording a bit by
having it be a bit more consistent. Would certainly welcome feedback on
it though, of course. This is a tricky bit of language to write and
a complex optimization to explain.Should we try to explain what is a "summarizing" index is? Right now
the only way to know is to look at the source code or attach a debugger
and see if IndexAmRoutine->amsummarizing is true. Maybe we can just say
"currently the only in-core summarizing index is BRIN" somewhere in the
page. (The patch's proposal to say "... such as BRIN" strikes me as too
vague.)
Not sure about explaining what one is, but I'd be fine adding that
language. I was disappointed to see that there's no way to figure out
the value of amsummarizing for an access method other than looking at
the code. Not as part of this specific patch, but I'd generally support
having a way to that information at the SQL level (or perhaps everything
from IndexAmRoutine?).
Attached is an updated patch which drops the 'such as' and adds a
sentence mentioning that BRIN is the only in-core summarizing index.
Thanks!
Stephen
Attachments:
v4-0001-Adding-Summary-Index-Info-to-HOT-Update-Documentatio.patchtext/x-diff; charset=us-asciiDownload
From 131f83868b947b379e57ea3dad0acf5a4f95bca8 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Mon, 26 Feb 2024 17:17:54 -0500
Subject: [PATCH] docs: Update HOT update docs for 19d8e2308b
Commit 19d8e2308b changed when the HOT update optimization is possible
but neglected to update the Heap-Only Tuples (HOT) documentation. This
patch updates that documentation accordingly.
Author: Elizabeth Christensen
Reviewed-By: Stephen Frost, Alvaro Herrera
Backpatch-through: 16
Discussion: https://postgr.es/m/CABoUFXRjisr58Ct_3VsFEdQx+fJeQTWTdJnM7XAp=8MUbtoa9A@mail.gmail.com
---
doc/src/sgml/storage.sgml | 21 +++++++++++++--------
1 file changed, 13 insertions(+), 8 deletions(-)
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index 3ea4e5526d..f2bb0283ae 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -1097,8 +1097,13 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- The update does not modify any columns referenced by the table's
- indexes, including expression and partial indexes.
+ The update only modifies columns which are not referenced by the
+ table's indexes or are only referenced by summarizing indexes and
+ does not update any columns referenced by the table's
+ non-summarizing indexes, including expression and partial
+ non-summarizing indexes. The only summarizing index in the core
+ <productname>PostgreSQL</productname> distribution is
+ <acronym>BRIN</acronym>.
</para>
</listitem>
<listitem>
@@ -1114,7 +1119,8 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- New index entries are not needed to represent updated rows.
+ New index entries are not needed to represent updated rows, however,
+ summary indexes may still need to be updated.
</para>
</listitem>
<listitem>
@@ -1130,11 +1136,10 @@ data. Empty in ordinary tables.</entry>
</para>
<para>
- In summary, heap-only tuple updates can only be created
- if columns used by indexes are not updated. You can
- increase the likelihood of sufficient page space for
- <acronym>HOT</acronym> updates by decreasing a table's <link
- linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
+ In summary, heap-only tuple updates can only be created if columns used by
+ non-summarizing indexes are not updated. You can increase the likelihood of
+ sufficient page space for <acronym>HOT</acronym> updates by decreasing
+ a table's <link linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
If you don't, <acronym>HOT</acronym> updates will still happen because
new rows will naturally migrate to new pages and existing pages with
sufficient free space for new row versions. The system view <link
--
2.34.1
On Tue, 2024-02-27 at 09:48 -0500, Stephen Frost wrote:
Attached is an updated patch which drops the 'such as' and adds a
sentence mentioning that BRIN is the only in-core summarizing index.
The original patch reads more clearly to me. In v4, summarizing (the
exception) feels like it's dominating the description.
Also, is it standard practice to backport this kind of doc update? I
ordinarily wouldn't be inclined to do so, but v4 seems targeted at 16
as well.
Attached my own suggested wording that hopefully addresses Stephen and
Alvaro's concerns. I agree that it's tricky to write so I took a more
minimalist approach:
* I got rid of the "In summary" sentence because (a) it's confusing
now that we're talking about summarizing indexes; and (b) it's not
summarizing anything, it's just redundant.
* I removed the mention partial or expression indexes. It's a bit
redundant and doesn't seem especially helpful in this context.
If this is agreeable I can commit it.
Regards,
Jeff Davis
Attachments:
v5-0001-docs-Update-HOT-update-docs-for-19d8e2308b.patchtext/x-patch; charset=UTF-8; name=v5-0001-docs-Update-HOT-update-docs-for-19d8e2308b.patchDownload
From d17ecaf1af52ba5b055c19b465d77cc53f825747 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Mon, 26 Feb 2024 17:17:54 -0500
Subject: [PATCH v5] docs: Update HOT update docs for 19d8e2308b
Commit 19d8e2308b changed when the HOT update optimization is possible
but neglected to update the Heap-Only Tuples (HOT) documentation. This
patch updates that documentation accordingly.
Author: Elizabeth Christensen
Reviewed-By: Stephen Frost, Alvaro Herrera
Discussion: https://postgr.es/m/CABoUFXRjisr58Ct_3VsFEdQx+fJeQTWTdJnM7XAp=8MUbtoa9A@mail.gmail.com
---
doc/src/sgml/storage.sgml | 21 +++++++++++----------
1 file changed, 11 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index 652946db7d..2e1914b95b 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -1097,8 +1097,10 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- The update does not modify any columns referenced by the table's
- indexes, including expression and partial indexes.
+ The update does not modify any columns referenced by the table's indexes,
+ not including summarizing indexes. The only summarizing index method in
+ the core <productname>PostgreSQL</productname> distribution is <link
+ linkend="brin">BRIN</link>.
</para>
</listitem>
<listitem>
@@ -1114,7 +1116,8 @@ data. Empty in ordinary tables.</entry>
<itemizedlist>
<listitem>
<para>
- New index entries are not needed to represent updated rows.
+ New index entries are not needed to represent updated rows, however,
+ summary indexes may still need to be updated.
</para>
</listitem>
<listitem>
@@ -1130,14 +1133,12 @@ data. Empty in ordinary tables.</entry>
</para>
<para>
- In summary, heap-only tuple updates can only be created
- if columns used by indexes are not updated. You can
- increase the likelihood of sufficient page space for
+ You can increase the likelihood of sufficient page space for
<acronym>HOT</acronym> updates by decreasing a table's <link
- linkend="reloption-fillfactor"><literal>fillfactor</literal></link>.
- If you don't, <acronym>HOT</acronym> updates will still happen because
- new rows will naturally migrate to new pages and existing pages with
- sufficient free space for new row versions. The system view <link
+ linkend="reloption-fillfactor"><literal>fillfactor</literal></link>. If you
+ don't, <acronym>HOT</acronym> updates will still happen because new rows
+ will naturally migrate to new pages and existing pages with sufficient free
+ space for new row versions. The system view <link
linkend="monitoring-pg-stat-all-tables-view">pg_stat_all_tables</link>
allows monitoring of the occurrence of HOT and non-HOT updates.
</para>
--
2.34.1
On Mar 4, 2024, at 5:32 PM, Jeff Davis <pgsql@j-davis.com> wrote:
Attached my own suggested wording that hopefully addresses Stephen and
Alvaro's concerns. I agree that it's tricky to write so I took a more
minimalist approach:If this is agreeable I can commit it.
Regards,
Jeff Davis<v5-0001-docs-Update-HOT-update-docs-for-19d8e2308b.patch>
Looks good to me. Thanks!
Elizabeth
Greetings,
* Jeff Davis (pgsql@j-davis.com) wrote:
On Tue, 2024-02-27 at 09:48 -0500, Stephen Frost wrote:
Attached is an updated patch which drops the 'such as' and adds a
sentence mentioning that BRIN is the only in-core summarizing index.The original patch reads more clearly to me. In v4, summarizing (the
exception) feels like it's dominating the description.Also, is it standard practice to backport this kind of doc update? I
ordinarily wouldn't be inclined to do so, but v4 seems targeted at 16
as well.
I do think this change should be back-ported to when the change
happened, otherwise the documentation won't reflect what's in the
product for that version...
Attached my own suggested wording that hopefully addresses Stephen and
Alvaro's concerns. I agree that it's tricky to write so I took a more
minimalist approach:
* I got rid of the "In summary" sentence because (a) it's confusing
now that we're talking about summarizing indexes; and (b) it's not
summarizing anything, it's just redundant.
* I removed the mention partial or expression indexes. It's a bit
redundant and doesn't seem especially helpful in this context.
Just to point it out- the "In summary" did provide a bit of a summary,
before the 'partial or expression indexes' bit was removed. That said,
I tend to still agree with these changes as I feel that users will
generally be able to infer that this applies to partial and expression
indexes without it having to be spelled out to them.
If this is agreeable I can commit it.
Great, thanks!
Stephen
Import Notes
Reply to msg id not found: E437E2AE-93F7-4030-ACB2-9DAE6A73A7A0@crunchydata.com1abb9150e7e50eace9e6cd7520e22c7404856f3b.camel@j-davis.com | Resolved by subject fallback