Tweet
Tweet

Slide 1

Slide 1 text

It’s time for relational databases to focus on developers Shlomi Noach Reversim Summit 2022

Slide 2

Slide 2 text

The relational model

Slide 3

Slide 3 text

ALTER TABLE node_health ADD COLUMN extra_info varchar(128) NOT NULL, DROP KEY instance_tag_idx DROP TABLE instance_tags Schema changes (DDLs)

Slide 4

Slide 4 text

About me Engineer at PlanetScale; previously at GitHub, Booking.com, Outbrain Author of open source projects orchestrator, gh-ost, freno and others Maintainer for Vitess Blog at http://openark.org github.com/shlomi-noach @ShlomiNoach

Slide 5

Slide 5 text

Vitess A database clustering system for horizontal scaling of MySQL ● CNCF graduated project ● Open source, Apache 2.0 licence ● Contributors from around the community ● Originally developed at YouTube to mass scale their load

Slide 6

Slide 6 text

MySQL-compatible serverless database platform, built for developers Main sponsors for Vitess

Slide 7

Slide 7 text

ALTER TABLE node_health ADD COLUMN extra_info varchar(128) NOT NULL, DROP KEY instance_tag_idx DROP TABLE instance_tags Avoiding schema changes

Slide 8

Slide 8 text

Why?

Slide 9

Slide 9 text

Interacting with the relational database

Slide 10

Slide 10 text

Schema change as an operation The relational database does not have a concept of developers. It only has the concept of operations people.

Slide 11

Slide 11 text

Schema change as an operation

Slide 12

Slide 12 text

Schema change as a deployment We should apply our deployment terminology and practices to schema changes

Slide 13

Slide 13 text

Operational time ALTER TABLE node_health ADD COLUMN extra_info varchar(128) NOT NULL; [Three days later…] Query OK, 0 rows affected (70h 12m 56s)

Slide 14

Slide 14 text

Operational overhead

Slide 15

Slide 15 text

External online schema change tools ● pt-online-schema-change ● gh-ost ● pg-osc

Slide 16

Slide 16 text

app

Slide 17

Slide 17 text

app

Slide 18

Slide 18 text

app Row copy

Slide 19

Slide 19 text

app Row copy

Slide 20

Slide 20 text

app Row copy Apply changes

Slide 21

Slide 21 text

app

Slide 22

Slide 22 text

app

Slide 23

Slide 23 text

app

Slide 24

Slide 24 text

Operational conflicts

Slide 25

Slide 25 text

No content

Slide 26

Slide 26 text

Blast radius

Slide 27

Slide 27 text

user

Slide 28

Slide 28 text

user 10% deployment

Slide 29

Slide 29 text

user 20% deployment

Slide 30

Slide 30 text

user 50% deployment

Slide 31

Slide 31 text

user 100% deployment

Slide 32

Slide 32 text

app

Slide 33

Slide 33 text

app

Slide 34

Slide 34 text

Blue-green deployments

Slide 35

Slide 35 text

user

Slide 36

Slide 36 text

user

Slide 37

Slide 37 text

user

Slide 38

Slide 38 text

user

Slide 39

Slide 39 text

user ? ? ? ?

Slide 40

Slide 40 text

Undeployment (revert)

Slide 41

Slide 41 text

app

Slide 42

Slide 42 text

app

Slide 43

Slide 43 text

app

Slide 44

Slide 44 text

Apply changes app

Slide 45

Slide 45 text

Apply changes app sync

Slide 46

Slide 46 text

Apply changes sync app

Slide 47

Slide 47 text

Apply changes app

Slide 48

Slide 48 text

Apply changes app

Slide 49

Slide 49 text

Undeploy via blue-green deployments

Slide 50

Slide 50 text

Undeploy as first class citizen

Slide 51

Slide 51 text

Redeployment

Slide 52

Slide 52 text

CREATE TABLE IF NOT EXISTS node_health ( hostname varchar(128) CHARACTER SET ascii NOT NULL, token varchar(128) NOT NULL, last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (hostname, token) ) ENGINE=InnoDB DEFAULT CHARSET=ascii “Base schema”

Slide 53

Slide 53 text

ALTER TABLE node_health DROP PRIMARY KEY, ADD PRIMARY KEY (hostname, token) ALTER TABLE node_health ADD COLUMN extra_info varchar(128) CHARACTER SET utf8 NOT NULL ALTER TABLE node_health MODIFY last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP “patches”

Slide 54

Slide 54 text

if flags2&BinlogThroughGTID != 0 { dataSize, pos, ok := readUint32(data, pos) if !ok { return logFile, logPos, position, readPacketErr } if gtid := string(data[pos : pos+int(dataSize)]); gtid != "" { position, err = DecodePosition(gtid) if err != nil { return logFile, logPos, position, err } } } return logFile, logPos, position, nil Source code

Slide 55

Slide 55 text

No content

Slide 56

Slide 56 text

ALTER TABLE node_health DROP PRIMARY KEY, ADD PRIMARY KEY (hostname, token) ALTER TABLE node_health ADD COLUMN extra_info varchar(128) CHARACTER SET utf8 NOT NULL ALTER TABLE node_health MODIFY last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP Human calculated diff

Slide 57

Slide 57 text

CREATE TABLE IF NOT EXISTS node_health ( hostname varchar(128) CHARACTER SET ascii NOT NULL, token varchar(128) NOT NULL, last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (hostname, token) ) ENGINE=InnoDB DEFAULT CHARSET=ascii Declarative schema

Slide 58

Slide 58 text

CREATE TABLE IF NOT EXISTS node_health ( hostname varchar(128) CHARACTER SET ascii NOT NULL, token varchar(128) NOT NULL, last_seen_active timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, extra_info varchar(128) CHARACTER SET utf8 NOT NULL PRIMARY KEY (hostname, token) ) ENGINE=InnoDB DEFAULT CHARSET=ascii Declarative schema

Slide 59

Slide 59 text

ALTER TABLE node_health ADD COLUMN extra_info varchar(128) CHARACTER SET utf8 NOT NULL Imperative deployments via DDL statements

Slide 60

Slide 60 text

Idempotent deployments

Slide 61

Slide 61 text

More to discuss Asynchronous and scheduled deployments, job management, load shedding and throttling, discoverability, multi change deployments, multi target deployments, artifacts and garbage collection

Slide 62

Slide 62 text

How did we let this happen?

Slide 63

Slide 63 text

What’s next?

Slide 64

Slide 64 text

Thank you! @ShlomiNoach github.com/shlomi-noach Images source: https://www.pexels.com/