Schema Evolution - The Hard Parts

Schema evolution, also known as “schema migrations,” is a core part of application development and is considered one of the riskiest - with the potential to bring down entire production systems with one innocent mistake.

Schema evolution becomes especially tricky for SaaS, where the same application serves many tenants. Whether the tenants share the same Postgres databases and schema or are more isolated, schema changes must be coordinated across tenants, application servers, and Postgres schemas.

In this talk, we’ll discuss the challenges of schema evolution in each of the common patterns developers use to implement multi-tenant applications. We’ll explore existing solutions and share how we are solving this problem at Nile, a Serverless Postgres built for SaaS.

Transcript

1. Gwen Shapira, co-founder of Nile Date April 2024
2. Nice to meet you! I’m Gwen Shapira - Co-founder of Nile: Serverless Postgres for Modern SaaS - Previously: Cloud Native Kafka lead @ Confluent - Previously: Data architect (Hadoop, MySQL, Oracle) - Apache Kafka PMC - Wrote books, tweet a lot @gwenshap 02
3. • Schema migrations today • Process problems • Lock problems • Multi-tenancy problems • and how we solved them • Logical replication problems
4. Schema Evolution Today
5. How do you modify the schema for production applications?
7. More Right answers: - Run migration scripts in CI/CD pipeline directly - Init container
8. Where do migration scripts come from?
10. This looks easy.
11. Process Problems
12. Problem 1: Failed migration leftovers: ai_in_eu=> create table cats ( id serial primary key, name text not null, age int not null, weight float not null ); CREATE TABLE ai_in_eu=> create index on cats (nickname); ERROR: column "nickname" does not exist You now have a table with no index…
13. Solved by Postgres IMO, one of the top Postgres feature. Make sure you use it*.
14. Problems 2 + 3: Schema Mismatches & Incompatible changes mydb=> CREATE INDEX ON cats (nickname); ERROR: column "nickname" does not exist mydb=> ALTER TABLE cats RENAME name to nickname; But it existed in pre-prod? Will the app handle this?
15. Solved by modern developer practices And by enforcing common schema compatibility rules
16. Problem 3: Concurrent attempts to migrate
17. Solved by locking This works only in databases where DDL doesn’t cause transactions to commit. Like Postgres.
18. Lock problems
20. Conflicts with SELECT INSERT / UPDATE / DELETE CREATE INDEX CONCUR CREATE INDEX ALTER / DROP / TRUNCATE TABLE SELECT ❌ INSERT / UPDATE / DELETE ❌ ❌ CREATE INDEX CONCUR ❌ ❌ ❌ CREATE INDEX ❌ ❌ ❌ ALTER / DROP / TRUNCATE TABLE ❌ ❌ ❌ ❌ ❌
21. ALTER TABLE blocking SELECT ain’t great. But at least it is very short. Right?
23. It can get worse BEGIN ALTER TABLE super_busy ADD COLUMN ok boolean; ALTER TABLE … ADD CONSTRAINT FOREIGN KEY REFERENCES … CREATE UNIQUE INDEX ON very_large_table … CREATE TABLE … ALTER TABLE huge_table … ADD CONSTRAINT CHECK … UPDATE TABLE also_busy set X = f(x,y,z); -- wait, getting coffee COMMIT
24. Ouch. So what do we do?
25. Don’t get coffee. 02
26. Don’t do: Do this instead: BEGIN ALTER TABLE … ADD CONSTRAINT FOREIGN KEY REFERENCES ALTER TABLE huge_table … ADD CONSTRAINT CHECK … -- more stuff COMMIT BEGIN ALTER TABLE … ADD CONSTRAINT FOREIGN KEY REFERENCES … NOT VALID ALTER TABLE huge_table … ADD CONSTRAINT CHECK … NOT VALID -- more stuff COMMIT BEGIN ALTER TABLE … VALIDATE CONSTRAINT COMMIT
27. Don’t do: Do this instead: BEGIN ALTER TABLE … UPDATE huge_table SET … COMMIT BEGIN ALTER TABLE … COMMIT BEGIN UPDATE huge_table SET … COMMIT
28. Don’t do: Do this instead: BEGIN ALTER TABLE t1 … RENAME ALTER TABLE t2 … RENAME COMMIT -- Retry on failure: BEGIN SET LOCAL lock_timeout=100s; LOCK TABLE … IN ACCESS EXCLUSIVE MODE LOCK TABLE … IN ACCESS EXCLUSIVE MODE ALTER TABLE … ALTER TABLE … COMMIT
29. Multi-tenancy problems
30. Multi-tenancy SaaS products usual have the same application stack across all customers. The data for the customers can be in a schema / DB for each Tenants. Or the data can be in shared schema, sometimes sharded for scale. 02
31. Migrating lots of Tenants schemas Starts easy… Migration container Tenant 1 Tenant 2 schemas=(Tenants1 Tenants2) for schema in "${schemas[@]}" do migrate_schema ${schema} done
32. Tenants Tenants get_db_list() | xargs -P 5 -n 1 migrate_schema Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants
33. Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants Tenants ✅ You apps, reports, tools… all have to support all scenarios. ❌ ✅ ✅ ✅ ✅ ✅ ❌ 🕠 🕠 ✅ ✅ ❌ 🕠
34. Coordinating DDLs. The Nile way. It may look like an overkill. But Nile exists to solve hard database problems for multi-tenant applications. The goal is to give an experience identical to a single database with same guarantees. While providing isolation when needed. These familiar guarantees make the system simple to reason about and creates a great developer experience.
35. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator BEGIN… 🪝 🪝 🪝 🪝 🪝 🪝
36. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator BEGIN DISTRIBUTED TRANSACTION
37. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator BEGIN
38. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator ACK / ERROR
39. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator CREATE … ALTER … DROP…
40. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator ACQUIRE LOCKS ([locks]) CREATE TABLE ACQUIRE LOCKS ([locks]) ALTER TABLE
41. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator SELECT acquire_locks([lock info]) CREATE TABLE SELECT acquire_locks([lock info]) ALTER TABLE
42. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator COMMIT
43. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator PRE-COMMIT
44. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator PREPARE TRANSACTION
45. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator POST-COMMIT
46. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator COMMIT PREPARED
47. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator OK / ERR
48. Tenants Tenants Tenants Tenants Tenants Tenants Migration script Migration transaction coordinator COMMITTED!
49. Looks familiar? This is the famous 2-phase commit. (with extra distributed locks)
50. There is also the opposite problem Where multiple tenants share the schema… tenant_id employee_id email salary 1 2 gwen@nile.dev <private> 1 1 ram@nile.dev <private> 2 5456312 vahid@ibm.com <private> 3 4354622 brian@google.com <private>
51. - What if one tenant has constraint conflict? - What if one tenant is not ok with maintenance window? 02
52. Logical Replication Problem
53. There is just one problem: Logical replication doesn’t replicate DDLs
54. The real solution: Postgres logical replication should replicate DDLs
55. Vendor solutions: AWS RDS
56. OSS solutions: Capture DDLs DDL Audit Table DDL Subscription Logical replication Apply DDLs*
57. OSS solutions: • https://github.com/enova/pgl_ddl_deploy • https://github.com/samedyildirim/logical_ddl • https://github.com/2ndQuadrant/pglogical
58. SaaS Migrating multi-tenant databases require both coordination and isolation Logical Replication It doesn’t do DDLs. There are solutions, but one day someone will solve it right. Do it right DDL is full of problems that you don’t see when they are small and then blow up. To sum it up Schema Migrations are deceptive They look simple and get tricky fast Today Most companies integrate migrations in CI/CD and use migration tools. Some problems are solved with simple tools - transactions, and CI/CD Locks Process Locks are surprisingly tricky and nuanced. Break migrations into steps and test on “realistic” env.
59. Follow us on: X (Twitter) twitter.com/niledatabase Discord discord.gg/8UuBB84tTy Linkedin https://www.linkedin.com/company/niledatabase/ twitter.com/gwenshap