Ten Zhi Yang

- @tzyinc on slides.com, codepen
- Software Developer @ Shopify (Not the other e-commerce)

- Writes nonsense in HTML, CSS and JS

 

https://slides.com/d/kEy393M/live

https://codepen.io/collection/dbeEYv

- Fundamentally:

  - Store Data

  - Retrieve Data

- OLTP: Online Transaction Processing

- OLAP: Online Analytical Processing 

Worlds Simplest database

- Writing has pretty good performance, appending to file is cheap

- Basic principle applies to logging subsystems in databases

- Reading requires you to find the start of the next key, hence some form of escape is necessary

- Linear search for records O(n)

- escape characters will mess up your storage if not handled properly

- Writing to multiple files and hence reading, requires some form of pointer to remember where you are and where to write

- Segmenting

- Compacting (on older files in background) 

- Merging segments (can be done with compacting

- Crash recovery (in prev example)

- Checksums for crash recovery

Hash indexes - in memory hash

- sequential writing is fast (esp for disk drives)

- concurrency & crash recovery (segments are immutable and/or append only)

- little disk fragmentation

- hash tables must fit into memory

- range queries are not efficient

SSTables + LSM trees (kind of)

- the LSM in LSM trees mean Log-Structured Merge, which happens periodically, in a rolling function

- possible to have 2 trees/structures (or more) C0 and C1 which can be in the form of SSTables

- C0 is stored in memory and is very fast, containing frequently ref-ed nodes

actual rolling merge

- bloom filter:  space-efficient probabilistic data structure 

  - may contain false +ves (but better than false -ves)

  - check if key exists before going through LSMtree

- 2 strategies in merging+compaction

  - size-tiered, small tables merge into bigger tables

  - level-tiered, oldest tables are merged

B+trees 

- B+Trees are actually more established than LSM trees

- B+Trees store the tree as pages, of two kinds

  - A series of pointers representing the index

  - Leaf pages which contain the data

- B+Trees modify in place

- B+Trees are built from "bottom up"; every time you split, you grow more parents

not sure what I was expecting

- Write Ahead Logs (WAL) - basically an undo list

- Alternatively, use copy on write scheme, also useful for snapshots

- add lightweight thread locks to maintain integrity during updates

- abbreviate keys, only need to know the boundaries between ranges

- Arrange page orders on disk such to be sequential

- sibling pointers of leafs, to reduce need to go back to root

B trees vs LSM trees

Faster Writes

Sequential Writes

Compact Data

Compaction blocks read/write

Faster Reads

Pages are fixed sizes

Each key only appear once

Light locking on write

- Some applications require searching for a similar key. This can be done by fuzzy indexes.
- For example, Lucene allows searching for words within edit distance 

- High availability, low latency

- Usually copy-only data

- Can be optimised for analytics access patterns

- key indexing not as important

each dimension is normalised

no data redundancy

less storage required

Data integrity

easy to maintain

one fact with dimensions

easier for analysts to use

faster query time (less joins)

Data could mismatch

easy to set up

- In column-oriented storage, data of all rows from each column are stored together. It relies on each file containing the rows in the same order.

- Access time: usually in OLAP we only need a columns, no need to waste time getting entire rows

- Compression: Adjacent similar data can be compressed, just need to track how many rows are the same data. eg. Gender has very few cardinality, Age is also small (none older than 150)  we can sort by the low cardinality columns to achieve better compression

- Optimisations: bitmap encoding for distinct values (eg. country) allows faster equality comparisons, we can also have multiple sort orders copies for regularly used queries

- Sorted columns optimizes for read-only queries, yet writes are more difficult.
- In-place updates would require rewriting the whole column on every write. Instead, we can use a LSM-tree like structure where a in-memory store buffers the new writes. When enough new writes are accumulated, they are then merged with the column files and written to new files in bulk.
- Queries, in this case, would require reading data from both disk and the in-memory store. This will be hidden within the engine and invisible to the user.

 

- Materialized view is defined like a standard view, but with results stored on disk, while virtual views are expanded and processed at query time.

- Updating materialized view when data changes is expensive

A common special case of materialized view is a data cube or OLAP cube, where data is pre-summarized over each dimension. This enables fast queries with precomputed queries, yet a data cube may not have the flexibility as raw data.

 

Can slice and dice (pictured above) and rollup and drill down