Positional Update Handling in Column Stores Sandor Heman, Marcin - - PowerPoint PPT Presentation

▶
positional update handling
SMART_READER_LITE
LIVE PREVIEW

Positional Update Handling in Column Stores Sandor Heman, Marcin - - PowerPoint PPT Presentation

Nov 28, 2023 377 likes •951 views

Positional Update Handling in Column Stores Sandor Heman, Marcin Zukowski, Niels Nes, Lefteris Sidirourgos, Peter Boncz Presented by Guozhang Wang 2013.2.4 DB Lunch Row Store v.s. Column Store Row store Unnecessary read I/O

slide-1
SLIDE 1

Positional Update Handling in Column Stores

Sandor Heman, Marcin Zukowski, Niels Nes, Lefteris Sidirourgos, Peter Boncz Presented by Guozhang Wang 2013.2.4 DB Lunch

slide-2
SLIDE 2

Row Store v.s. Column Store

 Row store

  • Unnecessary read I/O
  • Not good for OLAP

 Column store

  • Save read I/O
  • Enable compression /

data ordering

slide-3
SLIDE 3

What about updates?

 Requires n random I/Os for

  • ne tuple if update-in-place

 Compression makes things

worse

 Replication and data ordering makes

thing even worse

slide-4
SLIDE 4

Differential Updates

 Collect updates in a write store (WS)

  • How to handle updates efficiently?

 Periodically propagate to read store (RS)

  • How to propagate quickly?

 Access both RS and WS for reads

  • How to handle reads efficiently?
slide-5
SLIDE 5

LSM-Tree for Indexing

 (2-3) tree or AVL tree for

WS in RAM

  • Maintain fast search

 Hierarchy of trees

  • Keep merging efficient

 B+ tree for lower-level trees for seq. I/O

slide-6
SLIDE 6

Array for Key-Value Store

 Sorted key/values in

memory table

 Merging is simplified as disk flush  Periodically compact tables on disk  Bloom filter used to improve searching

slide-7
SLIDE 7

VD for Column Stores

 Columns in RS sorted by

sort keys (SK)

 Value-based delta (VD) entries in WS

  • rdered by SKs of the RS columns

 Queries gets answered by scanning both

the RS and the delta entries in WS

slide-8
SLIDE 8

An Example of VD

store prod new qty London stool N 10 London table N 20 Paris rug N 1 Paris stool N 5

Read Store: inventory Sort-Key (SK): [store, prod]

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Write Store: INS

store prod Paris rug

Write Store: DEL SELECT * FROM inventory

slide-9
SLIDE 9

An Example of VD

store prod new qty London stool N 10 London table N 20 Paris rug N 1 Paris stool N 5

Read Store: inventory Sort-Key (SK): [store, prod]

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Write Store: INS

store prod Paris rug

Write Store: DEL SELECT * FROM ins UNION (SELECT * FROM inventory WHERE NOT EXISTS (SELECT * FROM del WHERE inventory.store = del.store AND inventory.prod = del.prod))

slide-10
SLIDE 10

An Example of VD

store prod new qty London stool N 10 London table N 20 Paris rug N 1 Paris stool N 5

Read Store: inventory Sort-Key (SK): [store, prod]

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Write Store: INS

store prod Paris rug

Write Store: DEL SELECT * FROM ins UNION (SELECT * FROM inventory WHERE NOT EXISTS (SELECT * FROM del WHERE inventory.store = del.store AND inventory.prod = del.prod))

slide-11
SLIDE 11

An Example of VD

store prod new qty London stool N 10 London table N 20 Paris rug N 1 Paris stool N 5

Read Store: inventory Sort-Key (SK): [store, prod]

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Write Store: INS

store prod Paris rug

Write Store: DEL SELECT * FROM ins UNION (SELECT * FROM inventory WHERE NOT EXISTS (SELECT * FROM del WHERE inventory.store = del.store AND inventory.prod = del.prod))

slide-12
SLIDE 12

An Example of VD

store prod new qty London stool N 10 London table N 20 Paris rug N 1 Paris stool N 5

Read Store: inventory Sort-Key (SK): [store, prod]

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Write Store: INS

store prod Paris rug

Write Store: DEL SELECT * FROM ins UNION (SELECT * FROM inventory WHERE NOT EXISTS (SELECT * FROM del WHERE inventory.store = del.store AND inventory.prod = del.prod))

slide-13
SLIDE 13

Problems of VD

 Expensive I/O scan of SK columns in RS

to merge with WS

  • No matter if the query needs SK columns

 Key-based union and diff operations are

computationally intensive

slide-14
SLIDE 14

The Idea:

  • Remember the position of an update in RS

rather than its sorted key values

  • Blindly apply updates according to positions

when merging RS and WS

slide-15
SLIDE 15

Tuple Position in RS

 RID: RowID

  • Unique, changeable according to updates

 SID: StableID

  • Non-unique, fixed at tuple creation time

 RID(t) = SID + ∆(t)

∆(t) = #inserts – #deletes before t

slide-16
SLIDE 16

An Example of RID/SID

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

SID store prod new qty RID London chair N 30 1 London stool N 10 1 2 London table N 20 2 3 Paris rug N 1 3 4 Paris stool N 5 4

slide-17
SLIDE 17

An Example of RID/SID

SID store prod new qty RID Berlin chair Y 5 Berlin cloth Y 20 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

slide-18
SLIDE 18

Positional Delta Tree for WS

 Keeps track of cumulative deltas (∆) in a

counting B-Tree

 Fast insertion of update entries and

updating deltas accordingly

 Fast merging updates with underlying RS

with the computed RID = SID + ∆

slide-19
SLIDE 19

An Example of PDT

SID store prod new qty RID London chair N 30 1 London stool N 10 1 2 London table N 20 2 3 Paris rug N 1 3 4 Paris stool N 5 4

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20 2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

∆ 0 1 2

slide-20
SLIDE 20

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID London chair N 30 1 London stool N 10 1 2 London table N 20 2 3 Paris rug N 1 3 4 Paris stool N 5 4 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-21
SLIDE 21

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID Berlin cloth Y 20 London chair N 30 1 1 London stool N 10 2 2 London table N 20 3 3 Paris rug N 1 4 4 Paris stool N 5 5 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-22
SLIDE 22

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID Berlin cloth Y 20 Berlin chair Y 5 1 London chair N 30 2 1 London stool N 10 3 2 London table N 20 4 3 Paris rug N 1 5 4 Paris stool N 5 6 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-23
SLIDE 23

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID Berlin cloth Y 20 Berlin chair Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-24
SLIDE 24

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID Berlin cloth Y 20 Berlin chair Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-25
SLIDE 25

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID Berlin cloth Y 20 Berlin chair Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-26
SLIDE 26

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID Berlin cloth Y 20 Berlin chair Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-27
SLIDE 27

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID Berlin cloth Y 20 Berlin chair Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-28
SLIDE 28

An Example of PDT

INSERT INTO inventory VALUES(‘Berlin’, ‘table’, Y, 10) INSERT INTO inventory VALUES(‘Berlin’, ‘cloth’, Y, 20) INSERT INTO inventory VALUES(‘Berlin’, ‘chair’, Y, 5)

2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

SID store prod new qty RID Berlin cloth Y 20 Berlin chair Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 ∆ 0 1 2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-29
SLIDE 29

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Insert Value Table

i0 i1 i2

Positional Delta Tree

store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-30
SLIDE 30

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Positional Delta Tree (rid: 2, type: del) Insert Value Table

i0 i1 i2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-31
SLIDE 31

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Positional Delta Tree (rid: 2, type: del)

RID 2 = 0 + 2

Insert Value Table

i0 i1 i2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-32
SLIDE 32

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 1 SID ∆ ins ins i2 i1 SID type value ins i0 SID type value

Positional Delta Tree (rid: 2, type: del)

RID 2 = 0 + 2

Insert Value Table

i0 i1 i2 store prod new qty Berlin table Y 10 Berlin cloth Y 5 Berlin chair Y 20

slide-33
SLIDE 33

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 1 SID ∆ ins ins i2 i1 SID type value SID type value

Positional Delta Tree (rid: 2, type: del)

RID 2 = 0 + 2

Insert Value Table

i1 i2 store prod new qty Berlin cloth Y 5 Berlin chair Y 20

slide-34
SLIDE 34

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 SID ∆ ins ins i2 i1 SID type value SID type value

Positional Delta Tree

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Insert Value Table

i1 i2

slide-35
SLIDE 35

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 SID ∆ ins ins i2 i1 SID type value SID type value

Positional Delta Tree (rid: 5, type: del) Insert Value Table

i1 i2 store prod new qty Berlin chair Y 5 Berlin cloth Y 20

slide-36
SLIDE 36

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 SID ∆ ins ins i2 i1 SID type value SID type value

Positional Delta Tree (rid: 5, type: del) Insert Value Table

i1 i2 RID 5 > 0 + 2 store prod new qty Berlin chair Y 5 Berlin cloth Y 20

slide-37
SLIDE 37

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 SID ∆ ins ins i2 i1 SID type value SID type value

Positional Delta Tree (rid: 5, type: del) Insert Value Table

i1 i2 RID 5 > 0 + 2 store prod new qty Berlin chair Y 5 Berlin cloth Y 20

slide-38
SLIDE 38

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2 SID ∆ ins ins i2 i1 SID type value 3 del d0 SID type value

Positional Delta Tree (rid: 5, type: del)

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Insert Value Table

i1 i2 RID 5 > 0 + 2

Delete Value Table

d0 store prod Paris rug

SID(t) = RID - ∆(t)

slide-39
SLIDE 39

An Example of PDT

DELETE FROM inventory WHERE store = ‘Berlin’ AND prod = ‘table’ DELETE FROM inventory WHERE store = ‘Paris’ AND prod = ‘rug’

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 Berlin table Y 10 2 London chair N 30 3 1 London stool N 10 4 2 London table N 20 5 3 Paris rug N 1 6 4 Paris stool N 5 7 2

  • 1

SID ∆ ins ins i2 i1 SID type value 3 del d0 SID type value

Positional Delta Tree

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Insert Value Table

i1 i2

Delete Value Table

d0 store prod Paris rug

slide-40
SLIDE 40

An Example of PDT

INSERT INTO inventory VALUES(‘Paris’, ‘rack’, Y, 4)

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 London chair N 30 2 1 London stool N 10 3 2 London table N 20 4 4 Paris stool N 5 5 2

  • 1

SID ∆ ins ins i2 i1 SID type value 3 del d0 SID type value

Positional Delta Tree

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Insert Value Table

i1 i2

Delete Value Table

d0 store prod Paris rug

slide-41
SLIDE 41

An Example of PDT

INSERT INTO inventory VALUES(‘Paris’, ‘rack’, Y, 4)

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 London chair N 30 2 1 London stool N 10 3 2 London table N 20 4 4 Paris stool N 5 5 2

  • 1

SID ∆ ins ins i2 i1 SID type value 3 del d0 SID type value

Positional Delta Tree

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Insert Value Table

i1 i2

Delete Value Table

d0 store prod Paris rug Insert before RID = 5

slide-42
SLIDE 42

An Example of PDT

INSERT INTO inventory VALUES(‘Paris’, ‘rack’, Y, 4)

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 London chair N 30 2 1 London stool N 10 3 2 London table N 20 4 4 Paris stool N 5 5 2

  • 1

SID ∆ ins ins i2 i1 SID type value 3 del d0 SID type value

Positional Delta Tree

store prod new qty Berlin chair Y 5 Berlin cloth Y 20

Insert Value Table

i1 i2

Delete Value Table

d0 store prod Paris rug Insert before RID = 5, which is at SID = 3

slide-43
SLIDE 43

An Example of PDT

INSERT INTO inventory VALUES(‘Paris’, ‘rack’, Y, 4)

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 London chair N 30 2 1 London stool N 10 3 2 London table N 20 4 4 Paris stool N 5 5 2

  • 1

SID ∆ ins ins i2 i1 SID type value 3 3 ins del i0 d0 SID type value

Positional Delta Tree

store prod new qty Paris rack Y 4 Berlin chair Y 5 Berlin cloth Y 20

Insert Value Table

i1 i2

Delete Value Table

d0 store prod Paris rug Insert before RID = 5, which is at SID = 3 i0

slide-44
SLIDE 44

An Example of PDT

INSERT INTO inventory VALUES(‘Paris’, ‘rack’, Y, 4)

SID store prod new qty RID Berlin chair Y 20 Berlin cloth Y 5 1 London chair N 30 2 1 London stool N 10 3 2 London table N 20 4 4 Paris stool N 5 5 2 SID ∆ ins ins i2 i1 SID type value 3 3 ins del i0 d0 SID type value

Positional Delta Tree

store prod new qty Paris rack Y 4 Berlin chair Y 5 Berlin cloth Y 20

Insert Value Table

i1 i2

Delete Value Table

d0 store prod Paris rug Insert before RID = 5, which is at SID = 3 i0

slide-45
SLIDE 45

Stacking PDTs

 Layers of PDTs: “deltas on deltas on ..”

  • PDT contains all differences in time [lo,hi]

RS Table

PDT

PDT

t1 t2

PDT

t0 t1

PDT PDT

PDT

PDT

PDT

PDT

t2 t3 lo h i

slide-46
SLIDE 46

Stacking for Isolation

 Immutable read-PDT  BIG: main

memory

 Updateable write-PDT  SMALL: L2 cache

  • Periodically propagate changes in write-PDT to

read-PDT

Stable Table Read-PDT Write-PDT TABLEx Propagate() Read-PDT

slide-47
SLIDE 47

Stacking for Isolation

 Transaction creates a snap-

shot copy of the write-PDT at startup

 Updates go into trans-PDT  On commit, propagate

trans-PDT into write-PDT

  • May not succeed if write-PDT

has already been updated

Stable Table Read-PDT Write-PDT TABLEx Write-PDT Trans PDT Copy Write-PDT Transaction State Propagate()

slide-48
SLIDE 48

Optimistic Concurrency Control

Stable Table Read-PDT Write-PDT Trans PDT TABLEx Copy Write-PDT Trans A Trans PDT Copy Write-PDT Trans B

 T

wo concurrent transactions

t2 t1 t3 t1 t1 t0

slide-49
SLIDE 49

Optimistic Concurrency Control

Stable Table Read-PDT Trans PDT TABLEx Copy Write-PDT Trans A Trans PDT Copy Write-PDT Trans B Write-PDT

 T

wo concurrent transactions

 A commits before B

t2 t0 t2 t1 t3 t1

slide-50
SLIDE 50

Optimistic Concurrency Control

 T

wo concurrent transactions

 A commits before B  B wants to commit, tries

to propagate its trans- PDT

Stable Table Read-PDT Write-PDT Trans PDT TABLEx Trans A Trans PDT Copy Write-PDT Trans B

t2 t0 t3 t1 t2 t1

slide-51
SLIDE 51

Optimistic Concurrency Control

 If conflict detected when

enumerating RIDs

  • Cannot commit B since

write-set overlaps, abort

Stable Table Read-PDT Write-PDT Trans PDT TABLEx Trans A Trans PDT Copy Write-PDT Trans B

t3 t1 t2 t0 t2 t1

slide-52
SLIDE 52

Optimistic Concurrency Control

 If conflict detected when

enumerating RIDs

  • Cannot commit B since

write-set overlaps, abort

 Otherwise B can

commit

  • Generate a new PDT

aligned with A’s PDT

Stable Table Read-PDT Write-PDT Trans PDT TABLEx Trans A Trans PDT Copy Write-PDT Trans B Serialize()

t2 t0 t3 t1 t2 t1

slide-53
SLIDE 53

Optimistic Concurrency Control

 If conflict detected when

enumerating RIDs

  • Cannot commit B since

write-set overlaps, abort

 Otherwise B can

commit

  • Generate a new PDT

aligned with A’s PDT

Stable Table Read-PDT Write-PDT Trans PDT TABLEx Trans A Copy Write-PDT Trans B Trans PDT Trans PDT

t2 t1 t2 t0 t3 t2 t3 t2

slide-54
SLIDE 54

Optimistic Concurrency Control

 If conflict detected when

enumerating RIDs

  • Cannot commit B since

write-set overlaps, abort

 Otherwise B can

commit

  • Generate a new PDT

aligned with A’s PDT

  • Propagate the new PDT

Stable Table Read-PDT Write-PDT Trans PDT TABLEx Trans A Copy Write-PDT Trans B Trans PDT

t2 t1 t3 t2 t3 t0

slide-55
SLIDE 55

Evaluation

 48GB RAM, 3GBs I/O performance  PTC-H Benchmark

slide-56
SLIDE 56

Conclusion

 PDTs speed-up differential update

merging

  • Reduced I/O volume
  • Reduced CPU merge overhead

 PDTs are stackable

  • Suitable for Snapshot Isolation

Thank you