Re: PG 12 draft release notes
Peter Geoghegan <pg@bowt.ie>
Commits
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
doc: PG 12 relnotes: update wording on truncate/vacuum item
- 4f41a7227511 12.0 landed
-
docs: PG 12 relnotes, update btree items
- 4bfb79ff6b1f 12.0 landed
-
doc: PG 12 relnotes, list added snowball/FTS languages
- 968072837173 12.0 landed
-
doc: PG 12 relnotes, merge new SQL partition function items
- 6a631a454664 12.0 landed
-
docs: PG 12 release notes, support functions
- 728840fe13ac 12.0 landed
-
docs: PG 12 relnote adjustments based on feedback from Tom Lane
- 8e719d33fd25 12.0 landed
-
docs: adjust RECORD PG 12 relnote item
- 3468a04a3e49 12.0 landed
-
doc: adjust PG 12 relnotes item on float digit adjustment
- b84a801d6a67 12.0 landed
-
doc: adjustments for PG 12 release notes
- 32fe2e3194c7 12.0 landed
-
docs: fix duplicate wording in PG 12 release notes
- 34d40becfa7a 12.0 landed
-
doc: properly attibute PG 12 pgbench release note item
- 5d971565a799 12.0 landed
-
doc: PG 12 release notes: normalize attribution names
- f86b0c3c4653 12.0 landed
-
Refactor the fsync queue for wider use.
- 3eb77eba5a51 12.0 cited
-
Add "split after new tuple" nbtree optimization.
- f21668f328c8 12.0 cited
-
Add nbtree high key "continuescan" optimization.
- 29b64d1de7c7 12.0 cited
-
Allow amcheck to re-find tuples using new search.
- c1afd175b5b2 12.0 cited
-
Consider secondary factors during nbtree splits.
- fab250243387 12.0 cited
-
Partial implementation of SQL/JSON path language
- 72b6460336e8 12.0 cited
-
Allow extensions to generate lossy index conditions.
- 74dfe58a5927 12.0 cited
-
Make TupleTableSlots extensible, finish split of existing slot type.
- 4da597edf1ba 12.0 cited
-
Reduce path length for locking leaf B-tree pages during insertion
- d2086b08b023 12.0 cited
On Tue, May 21, 2019 at 1:51 PM Bruce Momjian <bruce@momjian.us> wrote:
> > My concern here (which I believe Alexander shares) is that it doesn't
> > make sense to group these two items together. They're two totally
> > unrelated pieces of work. Alexander's work does more or less help with
> > lock contention with writes, whereas the feature that that was merged
> > with is about preventing index bloat, which is mostly helpful for
> > reads (it helps writes to the extent that writes are also reads).
> >
> > The release notes go on to say that this item "gives better
> > performance for UPDATEs and DELETEs on indexes with many duplicates",
> > which is wrong. That is something that should have been listed below,
> > under the "duplicate index entries in heap-storage order" item.
>
> OK, I understand how the lock stuff improves things, but I have
> forgotten how indexes are made smaller. Is it because of better page
> split logic?
That is clearly the main reason, though suffix truncation (which
represents that trailing/suffix columns in index tuples from branch
pages have "negative infinity" sentinel values) also contributes to
making indexes smaller.
The page split stuff was mostly added by commit fab250243 ("Consider
secondary factors during nbtree splits"), but commit f21668f32 ("Add
"split after new tuple" nbtree optimization") added to that in a way
that really helped the TPC-C indexes. The TPC-C indexes are about 40%
smaller now.
> > > Author: Peter Geoghegan <pg@bowt.ie>
> > > 2019-03-20 [dd299df81] Make heap TID a tiebreaker nbtree index column.
> As I remember the benefit currently is that you can find update and
> deleted rows faster, right?
Yes, that's true when writing to the index. But more importantly, it
really helps VACUUM when there are lots of duplicates, which is fairly
common in the real world (imagine an index where 20% of the rows are
NULL, for example). In effect, there are no duplicates anymore,
because all index tuples are unique internally.
Indexes with lots of duplicates group older rows together, and new
rows together, because treating heap TID as a tiebreaker naturally has
that effect. VACUUM will generally dirty far fewer pages, because bulk
deletions tend to be correlated with heap TID. And, VACUUM has a much
better chance of deleting entire leaf pages, because dead tuples end
up getting grouped together.
--
Peter Geoghegan