Benedikt: 00:08

Welcome to Slow and Steady Video Podcast, where you get to follow along as you build products in public. Each week, we'll give you an honest peek into our lives As you share our struggles, our wins, and everything in between. I'm Benedict. Today is July 25th. This is episode number 185.

Benedikt: 00:25

And I'm feeling excited because I'm joined by Andrew Atkinson. Andrew is a software engineer at Fountain And very passionate, just like me, about PostgreSQL and Reyes. And he's even so passionate that he's currently writing a book called High Performance Postgres With Reyes. Welcome to the show, Andy.

Andrew: 00:44

Thank you. Glad to be here. Excited to be here.

Benedikt: 00:47

Yeah. We've been chatting a little bit before, and, like, We've been following each other for a while and, like, chiming in on various things on on the socials. And so it's nice to finally have a proper conversation with you even if it's just via video call.

Andrew: 01:02

Yeah. It is it's great. And I I think it's it's still really cool to, be able to, You know, communicate across the ocean like we're doing in in, you know, low latency. And, you know, it's hard to meet otherwise, so it's a it's a great opportunity.

Benedikt: 01:16

If it weren't for time zones, right? That's the one thing we were probably never going to fix.

Andrew: 01:22

Yeah. That is a tricky challenge for especially for today's modern distributed companies and that kind of thing.

Benedikt: 01:29

Yes. Exactly. Well, We initially got the idea of recording this episode because of a post I sent out on the socials last week. And basically, there was a situation where I was, working on, a critical part of our application, let's put it that way. And fiddling with stuff and changing stuff in our application code and then rolling it out to production.

Benedikt: 01:59

And, everything seemed fine. Like, tests were passing all of it. But then I got one error from an exception notification system, And that said something like, couldn't write entry to the database because there's already a record in there and stuff like that. And that was once again a time where I appreciated database constraints and appreciated the fact that they prevent me from screwing up. And, traditionally, at least, it feels that way.

Benedikt: 02:32

I feel I feel like as Rails developers or, like, the Rails community, at least in the early days, didn't really appreciate databases as much As it maybe should or maybe as it does today. So you suggested that we just record an episode about us, like, about database constraints and how they can save your bacon in production.

Andrew: 02:54

Yeah. I thought that that tweet was cool because it, you know, it spoke to a real world experience I think a lot of developers first have. And, as a developer that has learned more and more about the capabilities of the database, I think that what I am passionate about is trying to help others learn about and put to use some of these capabilities. And, I agree. Having done Ruby on Rails for a really, really long time, more than a decade across a lot of different companies and different types of apps, you know, I think that there are different ways, that well, you know, Ruby on Rails didn't actually directly support, database level constraints.

Andrew: 03:42

I I had looked this up, recently. I think it was in version 4.2 is where it was added, which was, I think, 2014. And Ruby on Rails, you know, as we know, came out in the mid 2000, The aughts. And, so there was quite a a lot of number of years there where it wasn't directly supported. And, you know, I think you could you could speculate why, like, might be you know, there was a lot of emphasis on database portability.

Andrew: 04:07

Don't use too much of the capabilities of 1 database, relational database, you might move to another one, you know, and that kind of thing.

Benedikt: 04:14

Yeah.

Andrew: 04:15

And, there could be other reasons. But, yeah, it's it, it's a great It's, you know, I'd love to, we could get into some of the, specifics, but, for example, the, You know, either you know, even just making sure, you know, your fields where you don't expect there to be null values or where you have a really good idea of what uniqueness looks like on a table. Being able to add those things to your table helps improve your your data model and helps reduce the chances for bugs.

Benedikt: 04:49

Yeah, absolutely. Yeah. It's funny, like, that, like, database agnostic, race set up. Have you ever encountered in your career, like, a case where someone actually migrated databases other than, like, in the 1st 2 weeks of development? Something like

Andrew: 05:06

Right. Exactly. Yeah. Maybe right away, but, I mean, I think that, no. I know that 1 company I joined, they had moved from MySQL to Postgres before I had joined.

Andrew: 05:18

But, again, it was kind of like a, I don't even know I don't think it was for technology reasons. I think it was, well, I guess it was before I joined, so I I don't know too many of the details. But in the time that I've ever worked at a company, you know, which is some number of years typically, Like, you know, the relational database, when I started is the same relational database, that was being used when I left. And, You know, the, you know, as as you know, I'm, very enthusiastic about Postgres specifically, and so I've kind of Made the choice to specialize more and learn more about it more deeply and and the ecosystem and, some of the history behind it and that kind of thing. And, the you know, it's Part of it is part of my reason for doing that is because it has become more and more popular with professional developers.

Andrew: 06:18

And, there is a Stack Overflow survey that, adds some weight to that as well. I think there were 50,000 respondents. And, I believe the in the summary of that was that Postgres has recently become The more popular choice, I think, used in I I I don't I'm not a 100% sure now if it was solely in at at work or also for kind of personal projects or both. But Postgres, using the same metric had overtaken MySQL unpopularity. And, So I think and the reason I'm saying that is because I think the regarding the moving databases, like, you know, they're Really, it's it's, it's part of the stack of software that a business needs to run their web application or their other types of applications.

Andrew: 07:12

And, you know, if it's there's both a technical component and there's also a social component too. It needs to be a popular tool to help attract and retain, You know, team members to help, build and grow the platform. So if you know, both both for my own Personal interests and professional interests. Postgres was a a really dying relational database, and it it wasn't, You know, having all these great things that it does have, like annual releases, constantly improving performance, improving functionality, great documentation, that sort of thing, I wouldn't be investing so heavily in it. And I think businesses might be more likely to move too.

Andrew: 07:50

Right? Like, teams might be like, hey, this is not This is not something we wanna keep investing in, but in fact, the opposite is true. You know, I think if you are and and probably the same thing for you know, I think On the MySQL side, there was the MySQL MariaDB fork, a number of years ago, and I I'm not as involved in that world, but I I think that's probably fairly stabilized. So I think if your, you know, if if your team is building on one of those one of these Kind of bigger open source relational database management systems. Like, there isn't a lot of incentive to switch.

Benedikt: 08:30

Yeah. And I also feel like that, like, mentality of like Staying super far away from or, like, not making use of the database that you're using, like, fully use, by, like, not using all these features. I feel like you're doing yourself a disservice, because databases are a great tool. And, if you are afraid to fully use them and fully utilize them just because you might move to something else in the future. I think that's that's just not going anywhere, and you're missing out on a lot of things.

Benedikt: 09:04

So,

Andrew: 09:05

Right. And Yeah. It it should be you know, we haven't mentioned this yet either, but it's very important to note that, Postgres is very standards based, And there is the ANSI standard for SQL. So if you write and, of course, we're talking about we started talking about database constraints. Most of the common constraint types are gonna be there's gonna be equivalents in other relational database management systems.

Andrew: 09:29

So if you were to ever do a migration, for some reason, you know, you really would that would be part of your migration. You would say, okay, we have this not null constraint, this foreign key constraint. We need the same one created on the other side. And, so, yeah, I think it's I think that argument is I I don't know that anyone you know, I I don't really see people talking about that for many years now. Yeah.

Andrew: 09:53

But even if even if someone dug it up and said, let's argue about this again. You know, I mean, there's You really could make the case that it's, you know, any sort of big migration like that is going to have a lot of and migrating your database objects like your constraints is going to be just one of the pieces. So

Benedikt: 10:10

True. Yeah. Yeah. Yeah. Absolutely.

Benedikt: 10:14

Okay. With that out

Andrew: 10:14

of the way.

Benedikt: 10:15

With that out of the way, now that we've made our opinions clear and validated statement. Talking about database constraints. You mentioned the most obvious one is like not null or null enforcement. What's your 2nd popular, 2nd most popular database constraint that you use a lot?

Andrew: 10:41

I would say I mean, yeah, foreign key constraints and, you know, I mean, having a good expressing your data model, You know, so I think you also work with Ruby on Rails. Is that correct, then?

Benedikt: 10:52

Yes. Okay. Yes. Reyes, ActiveRecord, Postgres. We are fully aligned on the text stack.

Benedikt: 10:58

Okay.

Andrew: 10:58

Yeah. So we we mostly use the same or we use the same tech stack. And, but if you use another object relational mapper besides active record, you might Express your, you know, your relationships between your data models in the application code. And, there's value in that. And, of course, like, it helps with, you know, your your model or your application code layer, now getting relationships, and that it's and it's not it's not just value.

Andrew: 11:26

It's critical. But there, When you if you can put that information into the database by describing the parent child or the hierarchical relationships with, your tables, then, not only does that enforce that relationship so that, you know, a Primary key, foreign key relationship stays intact. You can't delete one side or the other. It also adds a form of documentation and, description to your data model that can be used by other clients as well. So if you have if you need to do something like copy your tables, which is something I've been working on a lot lately where I work.

Andrew: 12:09

We have these different environments, and we need different, role level slices of data or things like that. I can tell you more about if you're interested. But, You know, we can do that with as long as you know, it sort of depends on, but it it's, we invest in making sure that we have as many kinda foreign key constraints out of this possible so that we know the dependencies and, we we don't necessarily need the application code to know those things.

Benedikt: 12:40

That makes a lot of sense because then, like, I'll like, I mean, your application is usually just one of the tools that connects to the database and, I don't know, reporting, Business analytics or even maintenance tools might still look at the database, and the database constraints, or like the foreign keys, Help them figure out the data structure because otherwise it's just tables and just not immediately obvious host of the next. Yeah. Yeah.

Andrew: 13:04

Yep.

Benedikt: 13:05

That's a good point.

Andrew: 13:07

And they're, you know, they're always they're optional. And so I think, it's it's, again, kinds of you know, it kinda gets back at the, the opportunity that's there for developers to add, you know, and a database administrator that maybe thinks about data a lot or a data engineer, You know, they might they might learn about this in kind of a 1 on 1 course and just take it for granted. But what I've experienced is, I think there is an educational opportunity for a lot of first to say, like, you know, hey, these capabilities are here, but then actually putting them into use. And we can get into that as well, but it's not always super trivial depending on the you know, unfortunately, For large larger applications, either large database tables or high query volume or both, sometimes adding the foreign key constraints Or or not as straightforward as just adding them and you're done. It's, you have to there's some operational challenges like, table locks that we can talk about and stuff.

Andrew: 14:11

But They're all, there's there's great patterns, and and I'm happy to provide some more links to some tools and that sort of thing as well, that make it achievable. It just does require a little bit more investment to add. But, yeah, if you do if you had the mindset of of, like, hey, we wanna build out the data model and make sure that all of our table relationships are as expressive as as possible. I found it to be Both good as a, you know, for operational reasons to help avoid bugs like breaking integrity or but also because of these other clients.

Benedikt: 14:50

Yeah. What I also enjoy is cascading the leads, because it's so easy to forget about, Like the correct order of deleting stuff. And, with the foreign keys under cascading deletes, you just delete the parent row and all the children just get removed as well and stuff like that. That's super handy at times.

Andrew: 15:11

Yeah. Yeah, definitely. Yeah. Yeah. Usually, it's not you know, with a relational data model, of course, like, the more rich the application is, you're gonna have it's not gonna be data in in one table.

Andrew: 15:22

It's gonna be data in lots of tables that, form kind of a graph of data. So if you If you have a parent or a root, node kind of object, it's likely there's gonna be lots of, objects or table rows that refer to it. And, they're not going to make sense likely when you delete that parent or that root node, So you might wanna remove them or nullify them or something like that.

Benedikt: 15:51

Yeah, totally. So in terms of constraints, I feel like the next. The next, the next one under this is probably unique constraints. And I at least for me, those are the, like, Null constraints, foreign keys, and unique constraints are probably the ones I use the most. Yeah.

Benedikt: 16:10

And for everything else, it's more like Sometimes it's it makes sense to add an additional one, but, like,

Andrew: 16:18

yeah, there was 3

Benedikt: 16:19

other most important most important ones. So I

Andrew: 16:22

think we can

Benedikt: 16:23

talk about yeah. Go ahead.

Andrew: 16:24

Go. Well, I was gonna say, like, we, unique is, of course, a little more clear, what it does. But there are 2 more constraint types, check constraints and exclusion constraints that we could also talk about. Would you like to start with would you like to talk a bit about unique constraints?

Benedikt: 16:44

Let's talk a little bit about unique constraints and also maybe Let's touch on unique indexes versus unique constraints because that distinction isn't very clear and not necessarily obvious. Yeah. And it might not even be a thing in some databases.

Andrew: 17:03

Yeah. I only really know Postgres in this area, and I I don't even you know, I feel about 95% confident on on this, And I've written about it. So I like it. It's a little foggy, but because I guess in practice, the way I've always added unique constraints is from adding an index. That's a common pattern I've seen in most.

Andrew: 17:22

So in a Ruby on Rails or active record world, You create a a migration, which is, you know, incremental database modification or a DTL change for database people that adds an index, and you would add a, unique index that, Enforces the that supports the unique constraint.

Benedikt: 17:51

Mhmm.

Andrew: 17:52

But it is possible to add a unique constraint to a field or a combination of fields at the table level without an index.

Benedikt: 18:00

Yeah. So just remember Like, it rarely happens. Right?

Andrew: 18:04

Yeah. I've never done I've I've really never done that. Maybe if you knew at the outset for your table definition, as you created the table, that, a particular field was needed to be unique. You might well, my understanding is what Postgres does is when you create a unique constraint on a field or a combination of fields. I think the combination of fields, I believe you can only do when you do a create table statement, but I'm not a 100% sure.

Andrew: 18:30

You might be able to alter a table and add a unique constraint. I just haven't done that. I believe Postgres internally creates an index for that, for those lookups. Right? Because the constraint also, needs to be enforced at, row modification time.

Andrew: 18:51

So any insert, update, or delete, that constraint needs to be checked, and that needs to be fast. Right. And the way that that we do that is with an index. So I believe that what I have you know, I don't know how much of, of an optimization opportunity this really represents because, I, you know, I don't really see this done in practice, but I think, technically speaking, if you add a unique constraint to a field, adding an index that adds the unique constraint as a redundant index. Mhmm.

Andrew: 19:21

Yeah. Have you well, how have you handled that? Do you typically, You know, if you like, part of the reason I started to ask you a question, but let me say one more thing, and then I'll go back to the question. Part of the reason that The index approach is used in practice is because typically, You know, your table is not brand new or with low query volume or with small data in a at least, like, in a scale like, in a lot of Places I've worked where there there are maybe mid mid stage startups that are, you know, maybe like series a, series b. They've got product market fit, and they're in more of a scaling mode.

Andrew: 20:00

At at those points in time, adding a unique constraint that's gonna lock the table, while it's being propagated is gonna be a, a challenging operation to do online, and it's not gonna be something that folks are gonna be excited about taking the database down for. So typically, we do it with an index that we add using the concurrently keyword. And By adding the index concurrently the unique, index concurrently, it doesn't lock the table with the exclusive lock, and we can do that without taking the database down, and we can can do that, like, whether it's a high or low activity period. And, And so now you have this, now you have the index and the unique constraint applied. Is that typically what you've done or?

Benedikt: 20:53

Yeah. Like, that's usually how it works for 1, because it's the way race does it, like, supports it out of the box. Right? I feel like, you just do add index unique true, and that's it. Right?

Benedikt: 21:07

That's that's one thing. And then at least in our use cases, When we enforce, uniqueness, it's almost always Almost like a primary key, like a composed primary key. Like, sure, we still have the ID column, which is the real primary key, but in most cases, We just have this one thing that's identified by this combination of other columns. Right? Most of the time, it's a combination even.

Benedikt: 21:35

Right. So that's yeah. Usually and it and and because of that, we usually also wanna look up because of that. So the the the index makes sense anyways for, like, performance reasons. I think there's only 1 occasion where we actually Did it the other way around, and used a unique constraint without the index.

Benedikt: 21:58

And if I remember correctly, that was because, We had, the access list gem to to manage, order. And, the problem with the unique index was that when you switch stuff around and, like, change the order, for a short period, you have, like, 2 rows with the same with the same position. And, that wouldn't work with the with the index. So we had to use this feature, called Deferred Constraints, where you can basically set up the unique constraint and tell it to only check at the end of the transaction. So you were able to do a couple of inserts or updates in whatever order, Potentially creating conflicts in that process, but it would only be checked at the commit where you hopefully resolved all those con conflicts with with additional updates.

Benedikt: 22:56

So I feel like that's the one reason why you might not want to use a unique index instead of, And and use a unique constraint instead.

Andrew: 23:07

Yep. You get that control over some of those properties of the constraint. And, I guess at the beginning of the book you mentioned or sorry. At the beginning of the podcast, you mentioned that I'm writing a book. This the example you just listed is actually, it's I think it's a common example to help illustrate how to do this, but there's a exercise, in the book, the book, as you said, high performance post grads for rails.

Andrew: 23:36

In that I believe it's in the chapter on, where the chapter mostly emphasizes constraints, but it covers a little bit more broadly, Integrity and consistency and kind of what those mean and how to put them into practice. The The, example though with deferring the constraints to the transaction commit time, the constraint enforcement, The it's it's funny you mentioned the axe as list because I believe that is the exact example that I wrote about. It's it's pretty clear, and it's open source code. And you can look at it, and there's Some good blog posts about it as well. So there's a lot of educational material around it.

Andrew: 24:19

And, it's a it is a useful technique that I've used and seen at in use companies as well. So it's it's not just a hypothetical.

Benedikt: 24:27

Yeah. I feel like this is the the the one The the the one thing everyone will run into eventually if they start like, if you don't have any database constraints, I guess You never know. But once you realize, hey. They're useful, you start using them. And then at some point, you I mean, accessless feels like, the OG of, of Reyes plug ins.

Benedikt: 24:52

Like

Andrew: 24:53

Yeah. It is. It's anything x as. Yeah. Yeah.

Andrew: 24:57

Anything that starts with acts as or, yeah, some other some other marks over the years.

Benedikt: 25:03

Yeah. That's a that's a It's been around for, like, forever. So at some point, you just use it because you want something to be ordered in a list. And then suddenly you realize, oh, now the the new fancy constraint and the old, plug in don't really play nice, again. So everyone at some point runs into this particular one.

Andrew: 25:22

Yeah. Yep. And warranty constraints can be, deferred as well. And I believe check constraints as well, which we haven't really talked about yet. But check constraints are, another really interesting constraint type that actually are getting more direct support an active record in new versions.

Andrew: 25:42

I believe in 7.0 or 7.1, which is not yet released. There's It's it's real in the weeds nitty gritty, but there's some additional support for check constraint options using active record methods. So I think that those things are possible and have always you know, prior to having those things, the way you would do that is you would, You know, if you're doing an active record migration as you could do execute and you could run a SQL statement inside as a a string, and That's fine. But, I think it I think by having active record support, it helps grow the usage of those capabilities. And then also, it It means that that capability is a standards based capability that's gonna exist in the databases that are supported, which, you know, are MySQL, MariaDB, and I think SQLite and and Postgres.

Andrew: 26:39

And, so I think those it it sends Send some kind of message like, you know, hey, this is a supported capability. And, yeah. So would you like to talk about check constraints for a bit? Or

Benedikt: 26:51

Absolutely. Because that's one of those that I haven't really used. I don't think we have a check constraint in user list at this point. So, would love to hear some use cases and maybe some examples of what you use them for if you have used

Andrew: 27:06

Yeah. There's There's, they're pretty simple conceptually. It's really just any Boolean expression that you wanna write with SQL, you can do as a check constraint. So some of the most simple examples, and these examples are in the book as well. And there's blog posts about these too if if you don't pick up the book.

Andrew: 27:25

But I love it if you picked up the book.

Benedikt: 27:28

Of course.

Andrew: 27:31

Dear listeners and, and Benedicte. But the, the simple examples would be, like, if you have or one that comes to mind is 2 timestamp fields where it doesn't it wouldn't make any sense for 1 field to be at a time, earlier than another field. So you might express that as a check constraint. It's kinda like almost like adding a like an Assertion to your code or other programming languages, like, I think Java what did Java call it? It was like, and

Benedikt: 28:05

assert. Yeah.

Andrew: 28:10

In Ruby, it might be something that would be like A guard clause or raising an exception or something like that. You know, it's a way you can add additional Kind of bug prevention, but it also, again, helps to describe your data model. Like, and the book has The book, is based on a or the book uses, a Ruby on Rails app connected to Postgres that's on GitHub. It's a public app, it's a kind of a learning app. It's called Rideshare, and it's kinda like Uber or Lyft, part of its data model.

Andrew: 28:44

And, you can actually go and check it out right now if you want, if you go to my GitHub profile, but it's used the same app is used throughout the book. And, You know, for example, there's one of the tables and models is called, vehicle reservations. And There's a little bit of a twist on the, data model, kinda like to mangle it into for examples and exercises purposes. But The idea is you could also, you might have like a private, company that rents out vehicles, so you might make them available for reservations. Kind of like maybe I mean, Uber or Lyft, they don't do this.

Andrew: 29:25

You can't really rent their vehicles. But other, rental services like, Is Taro one of them or something like that? Like a startup? Anyway

Benedikt: 29:37

Let's assume there is one that does this.

Andrew: 29:40

Okay. Yeah. There's there's some I've I've never used it myself, but there's some other company, at least in the United States, that's popular that you can. Individuals can rent out their cars to other other people. Mhmm.

Andrew: 29:51

Kinda like Airbnb, but for your car. And, So let's say you made a reservation. Well, it wouldn't make any sense to have, you know, your end time of your reservation be before your start time. Or, You might want to say that, a a duration of a reservation is a certain amount of time, like it's at least this much time. Those are things you could do with check constraints.

Andrew: 30:17

There's another purpose for check constraints, which is how I learned about them first. Didn't really learn about them for data validation or kind of documentation as much. But the other purpose I learned about from strong migrations originally, which is a a Ruby gem and a tool that can help you write safer migrations. And It encodes a bunch of patterns that help you with at least higher scale, databases where you wanna change them while other transactions are happening. Other queries are happening at the same tables.

Andrew: 30:51

And, one of the things that recommends for is, is using check constraints because of their their additional control, fine grained control over, like, when you, can enforce them, to use them as a stand in for I believe it was for when you want it. Like, let's say you wanted to later add a particular field you wanted to add a not null constraint to. I believe this is the example from strong migrations. What you can do is you can first add a check constraint, but you can, not validate it for all rows. And what that means is that it will only be an effect for new row modifications.

Andrew: 31:37

But you can, you can basically fake out a not null constraint. And you can say this column value should be not all, but in the check constraint body. And then You don't incur that cost of applying it to all rows. If you have a small sized database, like with, you know, I don't know, 100,000 rows or less or something, it it's probably not a big deal. You might take a if you might be able to add the not null constraint without this overhead of the check constraint as the a sister kind of.

Andrew: 32:10

But if you have a a big table with millions of rows and lots of queries, Then, if you add the not null constraint, if my memory is correct about that, because Postgres is is to improve these things in version to version. So I believe that this is still an active, challenge. Of rows. And in order to do that, it needs to check all those rows, and it's gonna lock the table while that's happening. And the length of that lock can cause downtime because queries will time out, that sort of thing.

Andrew: 32:40

They'll be blocked on that change being applied. So check constraint can be used to help out in that process by first applying the check constraint in a not valid form. And then what you can do is you checked all the rows. You can actually then add the, Not in all constraint and and, like, overlap it. And, almost like check constraint can be used almost like scaffolding and rails back in the day.

Andrew: 33:06

I don't know if scaffolding is still a thing. Is scaffolding still a thing?

Benedikt: 33:10

I don't I don't know. I I like it's I haven't seen it around, for a long, long time.

Andrew: 33:16

So Maybe? Yeah. I haven't either. It so so for anyone that hasn't used Ruby on Rails in a long time, yeah, scaffolding would help. You you could generate a model or a resource, I guess.

Andrew: 33:28

And, you know, you that's been always part of the beauty of Ruby on Rails is you get all this stuff with relatively, straightforward generators. You get like a model, and you get a controller, and you get a test, and you get a route, and you get And used to get like scaffolding stuff. And that would be like some basic views, ERB views, or that kind of thing. So you could start doing CRUD operations with a resource, like, relatively quickly. But And that helps you, yeah, it helps you with, you know, speeding up your development process.

Andrew: 34:04

You might not stick with the scaffolding, but and so check constraints are you know, it's it's a little bit of a rough analogy, but There there's some similarities where you can use a check constraint, and that's another way to use check constraints. So if you don't use them for kind of your data verification or consistency checking. It might be worth just keeping them in mind for kind of, the helper use case.

Benedikt: 34:30

That makes a lot of sense. Yeah. Interesting. One thought came up, while we were talking about constraints in general. And it feels like, basically all constraints are, like, row level.

Benedikt: 34:45

Right? They're just, like, look at, Like in and also the check constraint, I guess. Just can look at, like, the row that gets modified, except for the unique constraint that kinda looks at everything else. Right? Is it true?

Benedikt: 34:59

Like, is that really the only constraint that we can use to, I don't know, validate the current row against Are there rows in the database? Or can maybe the check constraint also check, like, there's not, There's no entry with the same date or, well, that would be a constraint. But, all the other like, The new entry is newer than every every other entry in the database or something like that. Is this is this possible, or is it out of the

Andrew: 35:28

question for constraints? Well, you either intentionally or unintentionally set up a perfect segue into exclusion constraints, which, Which you It was

Benedikt: 35:40

unintentional, but, good.

Andrew: 35:44

So, yeah, the, another constraint type. And this, I think, covers, I think, all of the available constraint types we, I believe, have mentioned now. But this is the most or the least commonly used one in in my experience, but it's actually one of the it's it's really powerful. The syntax is a little wild, but, the exclude you'll see if you add an exclusion It'll say and you describe a table, like in Postgres backslash d table name if you're in PSQL, for example. Describe a table and then you scroll away at the bottom, you'll see the constraints that are defined at the table level.

Andrew: 36:21

And, With exclusion constraints, you can you can use them to validate data between multiple rows.

Benedikt: 36:30

Mhmm.

Andrew: 36:31

And the example that's in the book, and it's a common example. And, You know, if I'm being fully transparent, I don't know if I know many other examples. Although we did actually use one at where I work recently. And I think the general takeaway, or the high level takeaway is if you ever are in a situation where, Yeah. You wanna you wanna express a scenario that you don't want to happen that goes beyond 1 row.

Andrew: 37:03

Like, if you can try to remember that exclusion constraints might be, might be the thing that would help you out, then There's a little bit of a process of, in my opinion, of learning the syntax. It's a little bit terse. Like, it's not very self documenting. But what you can do is they there's these different operators, and you can say The example in the book is an easy one to describe. But, again, it has the vehicle reservations concept.

Andrew: 37:33

And the idea would be you don't want overlapping reservations. You know, like, if you and I book the same reservation, that would be bad because, like, you know, do you get the car? Do I get the car? Like, do we neither of us get the car? And you might build that as, you know, most Rails developers would likely build that as, like, an active record, model validation, right, specs.

Andrew: 37:56

Probably still wanna do all that stuff. But if it's a very core functionality that's unlikely to change, you might wanna consider also augmenting it with a exclusion constraint. And what you can do is you can say, Essentially, don't allow at insert time, I guess, or any any DML operation, insert, update, or delete. You can say you can essentially have a way to to look at compare that row to any other rows. And you can say for these sets of values.

Andrew: 38:27

Like, this should not be allowed if this were to happen. And it's in that way, it's similar to, like, a unique constraint where, You'd say, but it's just 1 column value or it could be a couple of columns. But it's like you can do the whole row and and, check that it there isn't gonna be and so sorry. And then I I I wasn't fully explaining it. So with the with the, exclusion constraint for non overlapping reservations, you can use the time range capabilities, and you can look at the time ranges between 2 rows, like, if you have a start and end time.

Andrew: 39:07

And you can make sure that there's no overlap within those ranges.

Benedikt: 39:13

Interesting. Yeah. I think I should look at those and, like, at least get a little bit familiar with them because Sounds like it might be useful.

Andrew: 39:22

Yeah. It's it's something that probably wouldn't be You know, it's probably like a occasional capability. And I I would say if if it's, It would it probably would make sense as a higher value, use case in your application. But if you do anything with time ranges, you know, and you don't want overlaps. You know?

Andrew: 39:48

It's something to consider. And I I believe they're Yeah. Again, I don't know for sure whether there's similar constraint capabilities in other relational databases. But for sure in Postgres, it's an exclusion constraint. And it's it's to introduce it, it's kinda like the other constraint types.

Andrew: 40:04

You know, you'll add it to a table. It'll show up. It'll become part of the definition of your database.

Benedikt: 40:12

Yeah. Makes sense. You just described, you briefly talked about, Rails validations. And how Developers usually just start with Rails validations to make sure the bad stuff doesn't happen, then maybe eventually add database constraints. One thing for me as someone who likes database constraints, one thing that always was a little bit annoying, or still is a little bit annoying, is That to get the user experience right, you still have to write all those validations.

Benedikt: 40:46

And for some cases, even like custom validators and stuff like that. Do you have any strategies around that? For example, I do you know of a way to, I don't know, Magically get the validations based on the database constraints? Or do you have a good way of dealing with Constraint exceptions, like, where something bad happens and then, to use the database or the the database client usually throws an exception. Right.

Benedikt: 41:15

And eventually, it just, like, bubbles up and is a, 500 server error. And he doesn't know what happened. Do you have any strategies or tips on how you deal with that?

Andrew: 41:27

Well, I did I did also cover I tried to since the book is it's mostly a Postgres book, But it's aimed at Ruby on Rails developers because I'm most familiar with that and and wanted to have those examples that were concrete and, you know, based on my real world experience. I do also I have used and have created custom validators like you've mentioned and used All the built in active record validations. And I I don't I try to avoid taking like a you weren't really asking about an opinion, but This it's kind of a hot topic that I'd say amongst, you know, like, where where to put your kind of validation logic and and whether you should put 2 places or 1 or that kind of thing. And I tried to avoid, taking an opinion on that and covering a breadth of application side and database side ways to validate, correctness and consistency. But it does it is like it's more work.

Andrew: 42:27

And, the the naive way you know, or the the most laborious way is to, you're you're gonna do some duplication and some duplicate efforts. I have I feel like I have seen some generation tools where you could Again, if you can if you but I I don't have any to recommend off the top of my head. I do have a couple tools to recommend, though, in this area. But just briefly, the Going back to what we had discussed earlier, if you describe your data model and your relationships, you know, at your table and fields level, It it allows another tool to come along and and generate code. So it could be that a command line tool could look and inspect your your tables and system catalog tables and postgres.

Andrew: 43:15

And, maybe not, You know, and maybe like interleave the validations in the right spot or like, you know, at least but at least list like, hey, you could add these validations. And And there there certainly are command line tools that do that kind of thing.

Benedikt: 43:30

Mhmm.

Andrew: 43:31

As far as connecting it all the way to the front end, One opinion I read regarding user experience. And and now that I said, I'm not gonna lay down a lot of opinions. Let me lay down an opinion. Like, I did I did see this kind of hot take from somebody that was like, active record validations are, not really worth it. And what they advocated for instead was the combination of database constraints.

Andrew: 43:56

And then also the idea that your application would ever run without JavaScript enabled. Forget about that. Like, that's that was kind of an old school idea too, is like, you know, your application should but it's like what app what modern web application runs without JavaScript. Yeah. So if you and, you know, to have the best client side the best user experience, you want client side validations.

Andrew: 44:20

So they kinda were advocating for investing in client side validations and database constraints kind of on the 2 opposite ends of the spectrum and that middle layer of model validations to worry less about. It might be similar to Well, anyway, that's kind of one school of thought, because I do think if you add, you know, active record model That's still gonna possibly be, limited in in how much of a user experience improvement you can you can Or, you know, it might be limited in in your user experience depending on how your front end is built. Yeah. Maybe with I guess my experience in the last few years has been kind of like these separate, you know, SPA front ends or React based front ends. But maybe in maybe with Rails and, some of the newer front end technologies now.

Andrew: 45:13

Because I'll admit, I mostly have drifted more towards back end and even infrastructure. And I'm not as up to speed on, Hotwire and Stimulus and some of those things lately. But if, theoretically, if if they could derive some you know, if you could do something like add this constraint, but also, like, propagate an active record model validation and possibly also make that accessible to the front end, that would be great, you know? Because you could have, You'd have more bang for your investment of, more spots where those validations would show up. A couple tools in the book that I mentioned as well that, I'm happy to share here.

Andrew: 45:58

By the way, There's over 40 gems and Postgres extensions that are mentioned in the book besides the core, Postgres and Ruby on Rails. And I have been tweeting recently as a hook to try to get people to sign up. If you go to pgrailsbook.com, For the next 3 weeks, I'm gonna keep sending out 10 at a time from the book, like, just basically brief summaries. And, you know, kind of as like a a way to entice people to, You know, stay interested in the book, which hopefully is out in the next month or so. But also because, you know, they're all open source tools, and I love to help Raise awareness about them so that they can, you know, get more use and the the creators and maintainers can get some more attention and and help and recognition for their hard work and that kind of thing.

Andrew: 46:52

So 2 2 of those tools are, active record doc Active Record Doctor. I don't know if you've seen that one. It's a command line tool, and, it can do some of the things we've been talking about like help you identify missing constraints. And it looks at your active record model classes and your database constraints and helps you identify places where you can make those more consistent. So you might it might be Adding, either an active record model validation that is consistent with a database concern you have or the other way around.

Andrew: 47:27

And then another one that's similar is called database consistency. The gem is database underscore consistency. And that's another command line tool, that you can use. And, again, it will analyze your, your both your code and your database and Print out some suggestions and recommendations. And these tools can be done by used by developers individually, and they can also be integrated into, like, a linting stage or that kind of thing in development process.

Benedikt: 47:59

Nice. Yeah. I should check. I've I've heard about both of them, but I've Never used them, but it sounds like a smart idea to just once in a while, run them and see what comes up.

Andrew: 48:10

Yeah. It's almost like an kind of another helper or an assistant, you know, if, you know, you might be on top of things, but it you know, I know that One of the ones I put into practice recently was, if you have, like, primary key, foreign key, columns that are related to each other. The tool recommend that they have the same type. It's easy it's easy for those types to drift apart because in newer versions of Rails, for example, I actually just did this yesterday, but I was creating a, What was I creating? I think I was creating a foreign key constraint, and the default, That doesn't really make sense.

Andrew: 48:56

Maybe it was index. In any case, the default Integer type. I think it was just creating a new table with a new column that was an integer type with active record, and it shows the Postgres big int type or the, But the table being referred to, its primary key was a int, like the int four type, The 4 bytes integer type. And, the you know, there's one direction that's that's fine. You know, you can always put a smaller thing in a bigger container, but you can't go the other way around.

Benedikt: 49:29

Yeah. Yeah.

Andrew: 49:30

So and I think being intentional about those types is helpful too. And, you know, it's more of a long term thing, but, You know, some of the high growth organizations, they do have to convert their if if they didn't use big integers all over, they have to do this into big conversion, which is painful. But, yeah. Anyway, that that's just one small example, and it will Mhmm. It does that by, like, looking at your types.

Andrew: 49:55

And like I said, you could also hook it into your development process. So maybe you could get those suggestions as you're doing migrations and that kind of thing.

Benedikt: 50:02

Right. Yeah. Makes sense. Cool. I think we covered all the constraints.

Benedikt: 50:08

We covered how to How to use some inactive record, or not use some inactive record? I think we both agree that there's proof of improvement there, But, it's still great that,

Andrew: 50:22

And I I think, like, yeah. Sorry to interrupt you. I was gonna say that I, you know, I might have made a couple small mistakes in describing these, and it's because it's they're If you don't, you know, they're not the kinds of things you use every day. So I think it's totally normal to, I think what's best for developers is to just kinda At least have familiarity with what they do and how they could use them and keep them in mind. But even myself, like, I'm always using the Postgres docs.

Andrew: 50:49

Lately, I'm also using chat GPT for generating examples, and, that's really helpful for especially for things that I kinda know about, but I can go and verify myself. I'm a big fan of anytime you wanna experiment with these capabilities, use your local Postgres database, You know, modify your schema, add constraints, try to outrun your tests, like, find out all of the issues. And And, you know, using tools like Chat GPT to help you generate examples is is another tool to, like, help with these infrequently, but, valuable, capabilities that the database offers.

Benedikt: 51:31

Yeah. That's a good idea. Like, Just for example because for advice, sometimes it's good stuff.

Andrew: 51:38

Yeah. Advice is is sketchier because if you're not familiar with the area, you're probably not in a good position to verify its accuracy. But, if you want to generate examples, then you can go and verify like yourself, you know, if it's especially if it's, you know, and of course, all the regular disclaimers, I'll put your company's code in there and that kind of thing. But, that's that's why I think, you know, the having these kind of, like, toy applications, toy databases, kind of sandbox things that you can experiment on is is very valuable.

Benedikt: 52:15

Yeah. Agreed. Cool. Where can people learn more about the book And about you?

Andrew: 52:22

Yes. Thank you. Yeah. They can learn more about the book for now at pgrailsbook.com. And that's a landing page Where, if you give me your email address, I will send you just a couple emails, not many, about updates about the book until it's available, which, is we're currently the publisher and myself are currently working towards end of August as the beta release date.

Andrew: 52:47

And at that point, I I maybe I'll redirect the URL to the book landing page or something else. But, Otherwise, I also have run a a blog for a really long time, more than 15 years, at andy atkinson.com. And lately, I'm doing more blogging mostly just exclusively about Postgres or Ruby on Rails. And I'm also on Twitter And occasionally on Mastodon and very recently on Blue Sky. Or you can, you can hit me up on LinkedIn or whatever.

Benedikt: 53:19

What's your handle on those platforms?

Andrew: 53:22

I'm using, and from Andy or Andrew. So a n d and then a t k I, my last name, Atkinson. So it's a little bit, it's not like a word, but and at key. And I think, yeah, I'm using that handle on each of those social media platforms.

Benedikt: 53:39

Okay. Cool. It should be easy enough to find you then. Awesome. Well, Thanks a lot for making the time, getting up super early, and, talking with me about database constraints.

Benedikt: 53:50

I certainly learned a few things here and there. So, Much appreciated, and, good luck with the book launch.

Andrew: 53:57

Thank you very much for having me and, for the opportunity to, I share with you some, some of the stuff and about the book, and, hope you have a a great rest of your week. Thank you.