JSFoo 2019

An experience report on how DB constraints helped our team perform profound structural changes to our application data through 4 large refactorings that happened over 30 months.

Relational databases are robust, reliable, and battle-tested. However, they truly shine when it comes to maintaining data integrity. This talk demonstrates the powerful nature of constraints in the context of large scale data refactorings, with real-life examples and code samples, all to make a central point: They cannot be an afterthought. You have to make them a part of your development process, and you get rewarded for doing so.

Outline

Since the context is around four refactorings, the talk is also made up of as many sections.

1. Migrating from Neo4j to PostgreSQL. (8 minutes)
2. Changing primary keys from UUID to Integer while maintaining referential integrity. (7 minutes)
3. Moving from a “shared and federated data” model to multi-tenant like “shared nothing” model. (8 minutes)
4. Getting rid of a join-table in the most central relationship in the app, and using triggers in conjunction with constraints to being reliability into the process. (7 minutes)

Apart from talking about constraints, I will also touch upon “adjacent” principles that were a part of this process, such as idempotency, iterative development, and testability.

Intro and conclusion together: ~5 minutes.
Buffer time for adjustments: ~5 minutes.

Requirements

This talk is most benefical to two kinds of people:

• Those who have prior experience building applications using a relational database
• Those who have built applications, but never with relational databases

Some experience or understanding of the process of writing web-based applications is required.

Speaker bio

Swanand is a Principal Engineer at First.io, a Durham based startup. He is a self-proclaimed “gnosiophile” and a fan of continuous improvement. He loves writing software, building communities, and mentoring people. He’s currently helping people get better at PostgreSQL through https://postgres-workshop.com/

Links

• https://speakerdeck.com/swanandp/the-well-rounded-engineer
• https://speakerdeck.com/swanandp/the-deep-work-mentality
• https://speakerdeck.com/swanandp/combinator-parsing