The relational model in the modern development age

Slides from FOSDEM 2022 presentation: https://fosdem.org/2022/schedule/event/relational_model_dev/

Relational databases have invested in the performance of the relational model, but not as much in developer flows, creating an operational barrier driving developers away. We present an improved paradigm that brings back ownership into developers hands, illustrated by recent developments in Vitess.

The relational model is one of the oldest surviving models in computer science. But while relational databases have evolved to meet modern load, throughput and scalability needs, they have not evolved as much to meet developers' needs.

The schema, at the heart of the relational model, remain a major operational blocker in modern development flows. Developing and deploying schema changes is unlike any other development and deployment flow in practice today. Operational complexity and constraints, lack of conflict resolution, difficulty or inability to undeploy, and the need to understand database internals, all make relational schema development deter developers, who look for other solutions elsewhere.

In this session we will review these impediments and how they came to be, and offer a modern take, that gives developers back their ownership of their data and flows. Recent developments in Vitess, an open source CNCF project, introduce new capabilities that change the relational development paradigm. We will discuss:

- Development flow: hiding the operational complexity
- Deployments and scheduled schema migrations
- Undeploy: revertible changes
- Align schema and code: declarative schemas, idempotent deployments, and version control

Transcript

1. The relational model in the modern development age Shlomi Noach PlanetScale FOSDEM 2022 Towards an improved operational paradigm, illustrated by Vitess
2. Agenda - Relational databases as production systems - Modern expectations - Schema deployment friction - Deployment sheduling - Deployment conflicts - Undeploying - Redeploying, idempotency & version control
3. About me Engineer at PlanetScale Author of open source projects orchestrator, gh-ost, freno and others Maintainer for Vitess Blog at http://openark.org github.com/shlomi-noach @ShlomiNoach
4. Founded Feb. 2018 by co-creators of Vitess, ~85 employees, HQ San Francisco, remote team MySQL-compatible serverless database platform, built for developers Built on top of Vitess 
5. Vitess A database clustering system for horizontal scaling of MySQL • CNCF graduated project • Open source, Apache 2.0 licence • Contributors from around the community
6. The Relational Model Data changes vs. schema changes, and where relational databases fell short
7. Deploying: size and time Deployments can be measured in minutes, hours or days
8. Deploying: submission & runtime Scheduling migrations
9. Scheduling: offered paradigm Asynchronous migrations Database scheduler
10. Vitess: migration scheduler Migrations run asynchronous, decoupled from caller Flexible scheduling: Single pending migration at a time Single running migration at a time Concurrent migrations
11. Deployment conflicts Choose between a required deployment or a required operation • Server restart/reboot • Promotion/failover
12. Deployment conflicts Interrupt due to conflicting operations
13. Deployment conflicts Withhold due to conflicting operations
14. Deployment conflicts Pursue and withhold operation
15. Deployment conflicts Deployment is coupled with the running database server process
16. Deployment conflicts: offered paradigm Process, server & failure agnostic deployments
17. Vitess: failure agnostic migrations mysql> SET @@ddl_strategy=’online’; mysql> ALTER TABLE my_table ADD INDEX name_idx(name(24)), DROP COLUMN c; Auto-resumes after restart Auto resumes on promoted replica
18. Undeploying Rolling back a deployment
19. Undeploying Blast radius
20. Undeploying Feasibility
21. Undeploying Feasibility: constructing the reverse ALTER statement mysql> ALTER TABLE my_table DROP INDEX state_idx; mysql> ALTER TABLE my_table ADD INDEX state_idx(state, token(32));
22. Undeploying Feasibility: dropped data mysql> ALTER TABLE my_table DROP COLUMN my_col; mysql> DROP TABLE my_table;
23. Undeploying Time
24. Undeploying: offered paradigm Revert as first class citizen
25. Vitess: REVERT mysql> SET @@ddl_strategy=’online’; mysql> REVERT VITESS_MIGRATION ‘a1dac193_4b86_11ec_a827_0a43f95f28a3’; Restore dropped tables, populated with data Restore dropped columns, populated with data Quick operation, proportional to time since migration completion, not to table size.
26. Vitess: REVERT Near instant REVERT made possible by: mysql> SET @@ddl_strategy=’online -allow-concurrent -postpone-completion’; mysql> REVERT VITESS_MIGRATION ‘A1dac193_4b86_11ec_a827_0a43f95f28a3’; +--------------------------------------+ | uuid | +--------------------------------------+ | bf4598ab_8d55_11eb_815f_f875a4d24e90 | +--------------------------------------+ Runs, but does not complete, a reverting migration
27. Vitess: REVERT Either choose to actually revert in case of trouble, or abort the revert if all goes well: mysql> ALTER VITESS_MIGRATION 'bf4598ab_8d55_11eb_815f_f875a4d24e90' COMPLETE; mysql> ALTER VITESS_MIGRATION 'bf4598ab_8d55_11eb_815f_f875a4d24e90' CANCEL;
28. Redeploying Painfully untrivial
29. Redeploying Relational databases and imperative changes
30. Redeploying Analyzing errors mysql> ALTER TABLE my_table ADD COLUMN my_column INT NOT NULL DEFAULT 0;
31. Redeploying Lack of errors, undesired operation mysql> ALTER TABLE my_table ADD INDEX (status); mysql> ALTER TABLE my_table PARTITION BY HASH(id) PARTITIONS 4;
32. Redeploying What is your current schema?
33. Redeploying What is your current schema? db_changes/0001_initial db_changes/0002_add_products db_changes/0003_key_on_user db_changes/0004_refactor_address_to_another_table
34. Redeploying So what is your current schema?
35. Redeploying Effective source of truth?
36. Redeploying Would you agree to read code by series of patches?
37. Redeploying Captive in decades old flow
38. Redeploying: offered paradigm Declarative schema changes
39. Vitess: declarative changes mysql> SET @@ddl_strategy='online -declarative'; -- The following migration creates a new table, as the table does not exist: mysql> CREATE TABLE decl_table( id INT PRIMARY KEY ); +--------------------------------------+ | uuid | +--------------------------------------+ | b06475e5_8a74_11eb_badd_f875a4d24e90 | +--------------------------------------+
40. Vitess: declarative changes mysql> SET @@ddl_strategy='online -declarative'; -- The next migration will implicitly ALTER the table decl_table into desired state: mysql> CREATE TABLE decl_table( id INT PRIMARY KEY, ts TIMESTAMP NOT NULL ); +--------------------------------------+ | uuid | +--------------------------------------+ | b7d6e6fb_8a74_11eb_badd_f875a4d24e90 | +--------------------------------------+
41. Vitess: declarative changes mysql> SET @@ddl_strategy='online -declarative'; -- Next migration does not change table structure, hence is a noop and implicitly successful: mysql> CREATE TABLE decl_table( id INT PRIMARY KEY, ts TIMESTAMP NOT NULL ); +--------------------------------------+ | uuid | +--------------------------------------+ | 110574b1_8a75_11eb_badd_f875a4d24e90 | +--------------------------------------+
42. Vitess: declarative changes A hybrid approach: per-table declarative Exploration continues
43. Fear of deployment “Risky” schema deployments Human-handled large deployments Avoid dealing with the database
44. Existing paradigm: conclusion We are caught in a paradigm that has evolved an elaborate ecosystem, yet one that does not meet modern expectations Outdated paradigms go beyond schema deployments
45. The relational developer paradigm Asynchronous migrations Scheduled migrations Migrations decoupled from server, survive reboot and failover Revertible migrations as first class citizens Retriable migrations Support declarative, idempotent migrations
46. Links Vitess project home page and docs: https://vitess.io/ Vitess repo: https://github.com/vitessio/vitess Vitess Slack workspace: https://vitess.io/slack PlanetScale docs: https://docs.planetscale.com
47. Thank you! Questions? github.com/shlomi-noach @ShlomiNoach