EnterpriseDB

Июль 26, 2022

Содержание

2. Overview PostgreSQL MVCC Motivation for Improvement HOT Basics HOT Internals Limitations Performance Numbers and Charts
3. What Does HOT Stand For ? Heap Organized Tuples Heap Optimized Tuples Heap Overflow Tuples Heap
4. Credits Its not entirely my work Several people contributed, some directly, many indirectly Simon Riggs –
5. Some Background - MVCC PostgreSQL uses MVCC (Multi Version Concurrency Control) for transaction semantics The good
6. MVCC - UPDATE V1 V2 V3 Index Heap Transaction T1 Updates V1 Transaction T1 Commits Transaction
7. MVCC - Visibility Index Heap Transaction T0 V3 V1 V2 T0 started before T1 committed T0
8. MVCC - Visibility Index Heap Transaction T2 Transaction T2 V3 V1 V2 T2 started after T1
9. MVCC - Visibility Index Heap Transaction T4 Transaction T4 Transaction T4 V3 V2 V1 T4 started
10. MVCC – Tuple States V2 Index Heap V1 and V2 are RECENTLY DEAD, V3 is the
11. Removing DEAD Tuples V2 Index Heap V1 V1 is DEAD. If it’s removed, we would have
12. MVCC - Index/Heap Bloat Updates Inserts Deletes Heap Index A Index B
13. MVCC - Index/Heap Bloat Heap Index A Index B VACUUM
14. Vacuum – Two Phase Process Heap Index A Index B
15. Vacuum Heap Index A Index B VACUUM can release free space only at the end of
16. Motivation Frequent Updates and Deletes bloat the heap and indexes resulting in performance degradation in long
17. Pgbench Results scale = 90, clients = 30, transactions/client = 1,000,000 two CPU, dual core, 2
18. Heap Bloat (# blocks) In 8.2, the heap bloat is too much for small and large
19. Postgres 8.3 – Multiple Autovacuum Multiple autovaccum processes helped small tables, but not large tables
20. Postgres 8.3 – HOT (Retail Vacuum)
21. Several Ideas Update In Place The first design. Replace old version with the new version and
22. HOT Update Necessary Condition A: UPDATE does not change any of the index keys Example: CREATE
23. HOT Update V1 V2 V3 Index Heap HOT Necessary Condition B: The new version should fit
24. HOT Update – Necessary Conditions Necessary Condition A: UPDATE does not change any of the index
25. Inside A Block Page Header tuple 1 tuple 2 tuple 4 tuple 3 tuple 5 tuple
26. HOT – Heap Scan V1 V2 V3 V4 Index Ref No change to Heap Scan Each
27. HOT – Index Scan V1 V2 V3 V4 Index Ref Index points to the Root Tuple
28. Pruning – Shortening the HOT Chain V3 V4 Index Ref V1 becomes DEAD V1 is removed,
29. Pruning – Shortening the HOT Chain V2 V3 V4 Index Ref Root LP is a redirected
30. Pruning – Shortening the HOT Chain V3 V4 Index Ref Root LP is a redirected LP
31. Pruning – Shortening the HOT Chain V4 Index Ref Root LP is a redirected LP V4
32. Pruning – Normal UPDATEs and DELETEs V1 Index Ref Normal UPDATEd and DELETEd tuples are removed
33. Pruning and Defragmentation Page Header tuple 1 tuple 2 tuple 4 tuple 3 tuple 5 tuple
34. Pruning – Recovering Dead Space Page Header tuple 1 tuple 2 tuple 4 tuple 3 tuple
35. Defragmentation – Collecting Dead Space Page Header tuple 5 tuple 6 tuple N Used Space Free
36. Billion $ Question – When to Prune/Defragment ? Pruning and defragmentation (PD) happens together – requires
37. Page Level Hints and Xid If UPDATE does not find enough free space in a page,
38. Lazy Vacuum / Vacuum Full Lazy Vacuum is almost unchanged. DEAD line pointers are collected and
39. Headline Numbers - Comparing TPS That’s a good 200% increase in TPS
40. Comparing Heap Bloat (# blocks) HOT significantly reduces heap bloat; for small and large tables
41. Comparing Index Bloat (# blocks) HOT significantly reduces index bloat too; for small and large tables
42. Comparing IO Stats
43. Comparing IO Stats
44. Comparing IO Stats Significant reduction in IO improves the headline numbers
45. What Should I Do ? Nothing! HOT is always enabled and there is no way to
46. Limitations Free space released by defragmentation can only be used for subsequent UPDATEs in the same
47. Create Index This was one of the most interesting challenges in HOT development. The goal was
48. Create Index - Challenges Handling broken HOT chains New Index must satisfy HOT properties All tuples
49. Create Index – Sane State 1, a, x 1, a, y 2, b, x 2, c,
50. Create Index – Broken HOT Chains 1, a, x 1, a, y 2, b, x 2,
51. Create Index – Building Index with Broken HOT Chains 2, b, x 2, c, y 3,
53. Скачать презентацию

Слайд 2

Overview
PostgreSQL MVCC
Motivation for Improvement
HOT Basics
HOT Internals
Limitations
Performance Numbers and Charts

Слайд 3

What Does HOT Stand For ?
Heap Organized Tuples
Heap Optimized Tuples
Heap Overflow

Tuples
Heap Only Tuples

Слайд 4

Credits
Its not entirely my work
Several people contributed, some directly, many indirectly
Simon

Riggs – for writing initial design doc and getting me involved
Heikki – for code review, idea generation/validation and participating in several long discussions.
Tom Lane – for patch review and code rework
Korry – for extensive code review within EnterpriseDB
Dharmendra, Siva, Merlin – for testing correctness/performance
Florian, Gregory – for floating ideas
Denis, Bruce – for constant encouragement and making me rework HOT thrice ☺
Faiz, Hope – for excellent project management within EnterpriseDB
Nikhil – for hearing to all my stupid ideas and helping with initial work
The list is so long that I must have missed few names – apologies and many thanks to them

Слайд 5

Some Background - MVCC
PostgreSQL uses MVCC (Multi Version Concurrency Control) for

transaction semantics
The good things:
Readers don’t wait for writers
Writer doesn’t wait for readers
Highly concurrent access and no locking overhead
The bad things:
Multiple versions of a row are created
The older, dead versions can not be easily removed because indexes don’t have visibility information
Maintenance overhead to reduce table/index bloat

Слайд 6

MVCC - UPDATE
V1
V2
V3
Index
Heap
Transaction T1 Updates V1
Transaction T1 Commits
Transaction T3 Updates V2
Transaction

T3 Commits

V1 is dead, but still visible to older transactions,
so we call it RECENTLY DEAD

V2 is dead, but still visible to older transactions,
It’s also RECENTLY DEAD

Live

Recently Dead

Retiring Transaction/xmax

Creating Transaction/xmin

Слайд 7

MVCC - Visibility
Index
Heap
Transaction T0
V3
V1
V2
T0 started before T1 committed
T0 can only see

time line

Слайд 8

MVCC - Visibility
Index
Heap
Transaction T2
Transaction T2
V3
V1
V2
T2 started after T1 committed, but before

T3 committed

T2 can only see V2

time line

Слайд 9

MVCC - Visibility
Index
Heap
Transaction T4
Transaction T4
Transaction T4
V3
V2
V1
T4 started after T3 committed
T4 can

only see V3

time line

Слайд 10

MVCC – Tuple States
V2
Index
Heap
V1 and V2 are RECENTLY DEAD, V3

is
the most current and LIVE version

T0 finishes, V1 becomes DEAD

T2 finishes, V2 becomes DEAD

Only V3 remains LIVE

Live

Recently Dead

Dead

V1 and V2 can not be removed, because
T0 and T2 can still see them

Слайд 11

Removing DEAD Tuples
V2
Index
Heap
V1
V1 is DEAD. If it’s removed, we would

have
a dangling pointer from the index.
V1 can not be removed unless
the index pointers pointing to it are also
removed
Note: Index entries do not have any visibility
Information
Near impossible to reliably find index pointers
of a given tuple.

Слайд 12

MVCC - Index/Heap Bloat
Updates
Inserts
Deletes
Heap
Index A
Index B

Слайд 13

MVCC - Index/Heap Bloat
Heap
Index A
Index B
VACUUM

Слайд 14

Vacuum – Two Phase Process
Heap
Index A
Index B

Слайд 15

Vacuum
Heap
Index A
Index B
VACUUM can release free space only at the

end of the heap. Tuples are not reorganized
to defragment the heap
Fragmented free space is recorded in the
Free Space Map (FSM)

Слайд 16

Motivation
Frequent Updates and Deletes bloat the heap and indexes resulting in

performance degradation in long term – spiral of death
Each version of a row has it’s own index entry, irrespective of whether index columns changed or not – index bloat
Retail VACUUM is near impossible (dangling index pointers)
Regular maintenance is required to keep heap/index bloat in check (VACUUM and VACUUM FULL)
Normal VACUUM may not shrink the heap, VACUUM FULL can but requires exclusive lock on the table
VACUUM requires two passes over the heap and one or more passes over each index.
VACUUM generates lots of IO activity and can impact the normal performance of the database.
Must be configured properly

Слайд 17

Pgbench Results
scale = 90, clients = 30, transactions/client = 1,000,000
two CPU,

dual core, 2 GB machine
separate disks for data (3 disks RAID0) and WAL (1 disk)
shared_buffers = 1536MB
autovacuum = on
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 500
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = -1

Слайд 18

Heap Bloat (# blocks)
In 8.2, the heap bloat is too much

for small and large tables

Слайд 19

Postgres 8.3 – Multiple Autovacuum
Multiple autovaccum processes helped small tables, but

not large tables

Слайд 20

Postgres 8.3 – HOT (Retail Vacuum)

Слайд 21

Several Ideas
Update In Place
The first design. Replace old version with the

new version and move old version somewhere else
It was just too complicated!
Heap Overflow Tuple
That’s what HOT used to stand for
A separate overflow relation to store the old versions.
Later changed so that the new version goes into the overflow relation and pulled into the main relation when old version becomes dead.
Managing overflow relation and moving tuples around was painful.
Heap Only Tuple
That’s what HOT stands for today
Tuples without index pointers

Слайд 22

HOT Update
Necessary Condition A: UPDATE does not change any of the

index keys
Example:
CREATE TABLE test (a int, b char(20));
CREATE UNIQUE INDEX textindx ON test(a);
INSERT INTO test VALUES (1, ‘foo’);
UPDATE test SET b = ‘bar’ WHERE a = 1;
UPDATE test SET a = a + 1 WHERE a = 1;
First UPDATE changes the non-index column – candidate for HOT update
Second UPDATE changes the index column – HOT update not possible

Слайд 23

HOT Update
V1
V2
V3
Index
Heap
HOT
Necessary Condition B: The new version should fit in the

same old block – HOT chains can not cross block boundary.

V1 is updated – no index key change
Single Index Entry Update Chain
V2 is updated – no free space in block

Слайд 24

HOT Update – Necessary Conditions
Necessary Condition A: UPDATE does not change

any of the index keys
Necessary Condition B: The new version should fit in the same old block – HOT chains can not cross block boundary.

Слайд 25

Inside A Block
Page Header
tuple 1
tuple 2
tuple 4
tuple 3
tuple 5
tuple 6
tuple N
Used

Space

Free Space

pd_upper

pd_lower

Root Tuples/LP

HOT Tuples/LP

Page Header followed by line pointers
Line pointers point to the actual tuples
Indexes always point to the line pointers
and not to the actual tuple
HOT chains originate at Root LP and
may have one or more HOT tuples
HOT tuples are not referenced by the
indexes directly.

Слайд 26

HOT – Heap Scan
V1
V2
V3
V4
Index Ref
No change to Heap Scan
Each

tuple is examined separately and
sequentially to check if it satisfies the
transaction snapshot

Слайд 27

HOT – Index Scan
V1
V2
V3
V4
Index Ref
Index points to the Root Tuple

If the Root tuple does not satisfy the
snapshot, the next tuple in the HOT chain
is checked.
Continue till end of the HOT chain
The Root tuple can not be removed even
if it becomes DEAD because index scan
needs it

Слайд 28

Pruning – Shortening the HOT Chain
V3
V4
Index Ref
V1 becomes DEAD
V1

is removed, but it’s line pointer (LP)
can not be removed – index points to it
Root LP is redirected to the LP of
next tuple in the chain

Слайд 29

Pruning – Shortening the HOT Chain
V2
V3
V4
Index Ref
Root LP is a

redirected LP
V2 becomes DEAD
V2 and it’s LP is removed – HOT tuple
Root LP now redirects to the next
tuple in the chain

Слайд 30

Pruning – Shortening the HOT Chain
V3
V4
Index Ref
Root LP is a

redirected LP
V3 becomes DEAD
V3 and it’s LP is removed – HOT tuple
Root LP now redirects to the next
tuple in the chain

Слайд 31

Pruning – Shortening the HOT Chain
V4
Index Ref
Root LP is a

redirected LP
V4 becomes DEAD
V4 and it’s LP is removed – HOT tuple
Root LP is now DEAD – still can’t
be removed

Слайд 32

Pruning – Normal UPDATEs and DELETEs
V1
Index Ref
Normal UPDATEd and DELETEd

tuples are removed and their LPs
are marked DEAD – LPs can’t be
removed
A very useful side-effect of HOT

Слайд 33

Pruning and Defragmentation
Page Header
tuple 1
tuple 2
tuple 4
tuple 3
tuple 5
tuple 6
tuple N
Used

Space

Free Space

pd_upper

pd_lower

Root Tuples/LP

HOT Tuples/LP

Слайд 34

Pruning – Recovering Dead Space
Page Header
tuple 1
tuple 2
tuple 4
tuple 3
tuple 5
tuple

tuple N

Used Space

Free Space

Слайд 35

Defragmentation – Collecting Dead Space
Page Header
tuple 5
tuple 6
tuple N
Used Space
Free Space
6
1
2
5
N

Слайд 36

Billion $ Question – When to Prune/Defragment ?
Pruning and defragmentation (PD)

happens together – requires cleanup lock on the buffer and shuffles tuples in a page.
Too frequent PD may conflict with other backends accessing the buffer.
Too infrequent PD may slow down reclaiming dead space and create long HOT chains.
Page level hint bits and transaction id is used to optimize PD operations.

Слайд 37

Page Level Hints and Xid
If UPDATE does not find enough free

space in a page, it does COLD UPDATE but sets PD_PAGE_FULL flag
The next access to page may trigger prune/defrag operation if cleanup lock is available.
PD never waits for cleanup lock
Page Xid is set to the oldest transaction id which deleted or updated a tuple in the page. PD is usable only if RecentGlobalXmin is less than the Page Xid.

Слайд 38

Lazy Vacuum / Vacuum Full
Lazy Vacuum is almost unchanged.
DEAD line pointers

are collected and reclaimed.
Vacuum Full clears the redirected line pointers by making them directly point to the first visible tuple in the chain.

Слайд 39

Headline Numbers - Comparing TPS
That’s a good 200% increase in TPS

Слайд 40

Comparing Heap Bloat (# blocks)
HOT significantly reduces heap bloat; for small

and large tables

Слайд 41

Comparing Index Bloat (# blocks)
HOT significantly reduces index bloat too; for

small and large tables

Слайд 42

Comparing IO Stats

Слайд 43

Comparing IO Stats

Слайд 44

Comparing IO Stats
Significant reduction in IO improves the headline numbers

Слайд 45

What Should I Do ?
Nothing! HOT is always enabled and there

is no way to disable it.
It works on user and system tables
A heap fill factor less than 100 may help
A significantly smaller heap fill factor (as low as 50) is useful for heavy updates where most of the updates are bulk updates
Non index key updates is a necessary condition for HOT – check if you don’t need one of the indexes.
Prune-defrag reclaims COLD UPDATEd and DELETEd DEAD tuples by converting their line pointers to DEAD
You still need VACUUM – may be less aggressive

Слайд 46

Limitations
Free space released by defragmentation can only be used for subsequent

UPDATEs in the same page – we don’t update FSM after prune-defragmentation
HOT chains can not cross block boundaries
Newly created index may remain unusable for concurrent transactions
Normal vacuum can not clean redirected line pointers

Слайд 47

Create Index
This was one of the most interesting challenges in HOT

development.
The goal was to support CREATE INDEX without much or no impact on the existing semantics.
Did we succeed ? Well, almost

Слайд 48

Create Index - Challenges
Handling broken HOT chains
New Index must satisfy HOT

properties
All tuples in a HOT chain must share the same index key
Index should not directly point to a HOT tuple.
Create Index should work with a ShareLock on the relation

Слайд 49

Create Index – Sane State
1, a, x
1, a, y
2, b, x
2,

c, y

3, d, x

4, e, x

4, f, y

indexA(col1)

Create Table test (col1 int, col2 char, col3 char);
Create Index indexA ON test(col1);

All HOT chains are in sane state
Every tuple in a chain shares the
same index key
Index points to the Root Line Pointer

Слайд 50

Create Index – Broken HOT Chains
1, a, x
1, a, y
2, b,

2, c, y

3, d, x

4, e, x

4, f, y

indexA(col1)

Create Index indexB ON test(col2);

indexB(col2)

Create a new Index on col2
Second and fourth HOT chains,
marked with , are broken
w. r. t. new Index
tuples are recently dead, but
may be visible to concurrent txns

Слайд 51

Create Index – Building Index with Broken HOT Chains
2, b, x
2,

c, y

3, d, x

4, e, x

4, f, y

1, a, x

1, a, y

indexA(col1)

Create Index indexB ON test(col2);

indexB(col2)

Recently Dead tuples are not indexed
Index remains unusable to the
transactions which can potentially
see these skipped tuples, including
the transaction which creates the
index
Any new transaction can use the index
xmin of pg_class row is used to check
index visibility for transactions

EnterpriseDB

Содержание

OverviewPostgreSQL MVCCMotivation for ImprovementHOT BasicsHOT InternalsLimitationsPerformance Numbers and Charts

What Does HOT Stand For ?Heap Organized TuplesHeap Optimized TuplesHeap Overflow

CreditsIts not entirely my workSeveral people contributed, some directly, many indirectlySimon

Some Background - MVCCPostgreSQL uses MVCC (Multi Version Concurrency Control) for

MVCC - UPDATEV1V2V3IndexHeapTransaction T1 Updates V1Transaction T1 CommitsTransaction T3 Updates V2Transaction

MVCC - VisibilityIndexHeapTransaction T0V3V1V2T0 started before T1 committedT0 can only see

MVCC - VisibilityIndexHeapTransaction T2Transaction T2V3V1V2T2 started after T1 committed, but before

MVCC - VisibilityIndexHeapTransaction T4Transaction T4Transaction T4V3V2V1T4 started after T3 committedT4 can

MVCC – Tuple StatesV2IndexHeap V1 and V2 are RECENTLY DEAD, V3

Removing DEAD TuplesV2IndexHeapV1 V1 is DEAD. If it’s removed, we would

MVCC - Index/Heap BloatUpdatesInsertsDeletesHeapIndex AIndex B

MVCC - Index/Heap BloatHeapIndex AIndex BVACUUM

Vacuum – Two Phase ProcessHeapIndex AIndex B

VacuumHeapIndex AIndex B VACUUM can release free space only at the

MotivationFrequent Updates and Deletes bloat the heap and indexes resulting in

Pgbench Resultsscale = 90, clients = 30, transactions/client = 1,000,000two CPU,

Heap Bloat (# blocks)In 8.2, the heap bloat is too much

Postgres 8.3 – Multiple AutovacuumMultiple autovaccum processes helped small tables, but

Postgres 8.3 – HOT (Retail Vacuum)

Several IdeasUpdate In PlaceThe first design. Replace old version with the

HOT UpdateNecessary Condition A: UPDATE does not change any of the

HOT UpdateV1V2V3IndexHeapHOTNecessary Condition B: The new version should fit in the

HOT Update – Necessary ConditionsNecessary Condition A: UPDATE does not change

Inside A BlockPage Headertuple 1tuple 2tuple 4tuple 3tuple 5tuple 6tuple NUsed

HOT – Heap ScanV1V2V3V4Index Ref No change to Heap Scan Each

HOT – Index ScanV1V2V3V4Index Ref Index points to the Root Tuple

Pruning – Shortening the HOT ChainV3V4Index Ref V1 becomes DEAD V1

Pruning – Shortening the HOT ChainV2V3V4Index Ref Root LP is a

Pruning – Shortening the HOT ChainV3V4Index Ref Root LP is a

Pruning – Shortening the HOT ChainV4Index Ref Root LP is a

Pruning – Normal UPDATEs and DELETEsV1Index Ref Normal UPDATEd and DELETEd

Pruning and DefragmentationPage Headertuple 1tuple 2tuple 4tuple 3tuple 5tuple 6tuple NUsed

Pruning – Recovering Dead SpacePage Headertuple 1tuple 2tuple 4tuple 3tuple 5tuple

Defragmentation – Collecting Dead SpacePage Headertuple 5tuple 6tuple NUsed SpaceFree Space6125N

Billion $ Question – When to Prune/Defragment ?Pruning and defragmentation (PD)

Page Level Hints and XidIf UPDATE does not find enough free

Lazy Vacuum / Vacuum FullLazy Vacuum is almost unchanged.DEAD line pointers

Headline Numbers - Comparing TPSThat’s a good 200% increase in TPS

Comparing Heap Bloat (# blocks)HOT significantly reduces heap bloat; for small

Comparing Index Bloat (# blocks)HOT significantly reduces index bloat too; for

Comparing IO Stats

Comparing IO Stats

Comparing IO StatsSignificant reduction in IO improves the headline numbers

What Should I Do ?Nothing! HOT is always enabled and there

LimitationsFree space released by defragmentation can only be used for subsequent

Create IndexThis was one of the most interesting challenges in HOT

Create Index - ChallengesHandling broken HOT chainsNew Index must satisfy HOT

Create Index – Sane State1, a, x1, a, y2, b, x2,

Create Index – Broken HOT Chains1, a, x1, a, y2, b,

Create Index – Building Index with Broken HOT Chains2, b, x2,

Похожие презентации

Overview
PostgreSQL MVCC
Motivation for Improvement
HOT Basics
HOT Internals
Limitations
Performance Numbers and Charts

What Does HOT Stand For ?
Heap Organized Tuples
Heap Optimized Tuples
Heap Overflow

Credits
Its not entirely my work
Several people contributed, some directly, many indirectly
Simon

Some Background - MVCC
PostgreSQL uses MVCC (Multi Version Concurrency Control) for

MVCC - UPDATE
V1
V2
V3
Index
Heap
Transaction T1 Updates V1
Transaction T1 Commits
Transaction T3 Updates V2
Transaction

MVCC - Visibility
Index
Heap
Transaction T0
V3
V1
V2
T0 started before T1 committed
T0 can only see

MVCC - Visibility
Index
Heap
Transaction T2
Transaction T2
V3
V1
V2
T2 started after T1 committed, but before

MVCC - Visibility
Index
Heap
Transaction T4
Transaction T4
Transaction T4
V3
V2
V1
T4 started after T3 committed
T4 can

MVCC – Tuple States
V2
Index
Heap
V1 and V2 are RECENTLY DEAD, V3

Removing DEAD Tuples
V2
Index
Heap
V1
V1 is DEAD. If it’s removed, we would

MVCC - Index/Heap Bloat
Updates
Inserts
Deletes
Heap
Index A
Index B

MVCC - Index/Heap Bloat
Heap
Index A
Index B
VACUUM

Vacuum – Two Phase Process
Heap
Index A
Index B

Vacuum
Heap
Index A
Index B
VACUUM can release free space only at the

Motivation
Frequent Updates and Deletes bloat the heap and indexes resulting in

Pgbench Results
scale = 90, clients = 30, transactions/client = 1,000,000
two CPU,

Heap Bloat (# blocks)
In 8.2, the heap bloat is too much

Postgres 8.3 – Multiple Autovacuum
Multiple autovaccum processes helped small tables, but

Several Ideas
Update In Place
The first design. Replace old version with the

HOT Update
Necessary Condition A: UPDATE does not change any of the

HOT Update
V1
V2
V3
Index
Heap
HOT
Necessary Condition B: The new version should fit in the

HOT Update – Necessary Conditions
Necessary Condition A: UPDATE does not change

Inside A Block
Page Header
tuple 1
tuple 2
tuple 4
tuple 3
tuple 5
tuple 6
tuple N
Used

HOT – Heap Scan
V1
V2
V3
V4
Index Ref
No change to Heap Scan
Each

HOT – Index Scan
V1
V2
V3
V4
Index Ref
Index points to the Root Tuple

Pruning – Shortening the HOT Chain
V3
V4
Index Ref
V1 becomes DEAD
V1

Pruning – Shortening the HOT Chain
V2
V3
V4
Index Ref
Root LP is a

Pruning – Shortening the HOT Chain
V3
V4
Index Ref
Root LP is a

Pruning – Shortening the HOT Chain
V4
Index Ref
Root LP is a

Pruning – Normal UPDATEs and DELETEs
V1
Index Ref
Normal UPDATEd and DELETEd

Pruning and Defragmentation
Page Header
tuple 1
tuple 2
tuple 4
tuple 3
tuple 5
tuple 6
tuple N
Used

Pruning – Recovering Dead Space
Page Header
tuple 1
tuple 2
tuple 4
tuple 3
tuple 5
tuple

Defragmentation – Collecting Dead Space
Page Header
tuple 5
tuple 6
tuple N
Used Space
Free Space
6
1
2
5
N

Billion $ Question – When to Prune/Defragment ?
Pruning and defragmentation (PD)

Page Level Hints and Xid
If UPDATE does not find enough free

Lazy Vacuum / Vacuum Full
Lazy Vacuum is almost unchanged.
DEAD line pointers

Headline Numbers - Comparing TPS
That’s a good 200% increase in TPS

Comparing Heap Bloat (# blocks)
HOT significantly reduces heap bloat; for small

Comparing Index Bloat (# blocks)
HOT significantly reduces index bloat too; for

Comparing IO Stats
Significant reduction in IO improves the headline numbers

What Should I Do ?
Nothing! HOT is always enabled and there

Limitations
Free space released by defragmentation can only be used for subsequent

Create Index
This was one of the most interesting challenges in HOT

Create Index - Challenges
Handling broken HOT chains
New Index must satisfy HOT

Create Index – Sane State
1, a, x
1, a, y
2, b, x
2,

Create Index – Broken HOT Chains
1, a, x
1, a, y
2, b,

Create Index – Building Index with Broken HOT Chains
2, b, x
2,