Benedikt Deicke (co-founder of UserList) recently hosted me on the Slow & Steady podcast (co-hosted by Benedicte Raae). Our main topic was Database Constraints, why they’re useful and how to use them.
Benedikt recently tweeted about how a database constraint prevented an error in a critical part of their system. Since we’d planned to discuss something related to PostgreSQL, that story provided the spark for the episode topic.
Slow & Steady Podcast
The Slow & Steady Podcast is co-hosted by technical founders building in public and sharing their struggles, wins, and everything in between.
They’ve been running it for 4 years!
Check out their catalog of episodes. https://www.slowandsteadypodcast.com
Eventually we got into the main topic for the episode, database constraints in PostgreSQL.
We covered most of them.
- Not Null
- Primary Key and Foreign Key
Before we got into the main topic, we took a detail to discuss the historical lack of support for database level constraints in Ruby on Rails.
In the earlier days of Ruby on Rails in the 2000s there was more emphasis on database “portability,” in order words using less RDBMS features to make it easier to move your application from one to another.
In my career experience though most companies don’t switch their RDBMS.
Other popular relational databases like MySQL/MariaDB support most of the same constraints as well, including Default, Not Null, Primary Key, Foreign Key, and Check constraints.
What are the benefits of constraints?
- Constraints describe characteristics and relationships about columns and tables, expressed as SQL, and visible as part of your schema/structure
- Constraints are database objects that can be added, removed, dumped, and restored
- Constraints enforce the characteristics and relationships you’ve defined as rows are inserted, updated, and deleted
The main challenge in working with developers in my experience, is even knowing the constraints exist in general, where to use them, and then the actual process of adding them!
That was part of my motivation to cover this topic in detail for a while chapter in High Performance PostgreSQL for Rails.
Constraints are validated immediately by default. Some constraints can have their validation deferred, meaning validation only runs for new row changes but not on existing rows.
Not all constraints support this deferred validation though so it can be tricky to add some types to a running system.
When adding constraints on big tables, this can cause the table to be locked for a long time and blocks queries trying to access it. Fortunately these are known scenarios with workarounds.
Strong Migrations (linked below) helps guide Rails developers towards adding constraints safely.
Recommendations and Resources
- Strong Migrations - Helps you add constraints and avoid riskier Migrations using safer alternatives.
- Active Record Doctor - helps you identify missing Foreign Key and Unique constraints based on your Active Record model configuration
- Database Consistency - helps you identify missing constraints
- Postgres Constraints for Newbies - Nice introduction
- Check Constraints support in Active Record (Rails 6.1+)
Episode and Wrap Up
Listen to the episode here: 👉 Database constraints with Andrew Atkinson
Thanks for taking a look and we’d love to hear your feedback. If you liked the episode please share it on social media.
Thanks for reading! 👋