> If ever you decide you want to migrate away from Postgres[…]
it's one of the things to keep in mind. Depending on your application, migrating away from postgres will be more or less painful. If you're already deeply invested in postgres features, this is just one more problem to solve.
> If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else
yes. This has the all the usual issues of putting (some) business logic into the database. On the other hand, by using this, you're basically just creating a data integrity constraint similar to a foreign key, just one not as widely supported.
Still. If you ever plan to move to a database that doesn't support foreign key constraints, you will have to implement the business logic somewhere else.
For me, data integrity is paramount. If ever I can put a data integrity check directly into the database, I will do it because bugs in the application logic can exist and when the database itself enforces integrity constraint, I'm protected from those.
I don't want to have to deal with, to stay in the framework of the article, a shipped, but unpaid order. Was this a bug in the application? Did it actually ship? Did the payment fail?
If the database blows up on any attempts to store invalid data, I'm protected from having to ask these questions.
> If the database blows up on any attempts to store invalid data, I'm protected from having to ask these questions.
I'm always surprised when people fight using constraints. During dev, I see them as a godsend for spotting problems - I don't know how many bugs having self-enforcing data structures has caught.
I will say that I have, under protest, turned them off in production once for performance. A particular heavily-used flow was annoying due to a large number of FK checks on an intermediate step. But never during development, and given the number of FK-violation errors I've seen in production code, preferably not even then.
Fixing code is almost always so much easier than fixing data.
it's one of the things to keep in mind. Depending on your application, migrating away from postgres will be more or less painful. If you're already deeply invested in postgres features, this is just one more problem to solve.
> If ever you want to scale this, such that you want to calculate states in batches, you'd want to have the business logic somewhere else
yes. This has the all the usual issues of putting (some) business logic into the database. On the other hand, by using this, you're basically just creating a data integrity constraint similar to a foreign key, just one not as widely supported.
Still. If you ever plan to move to a database that doesn't support foreign key constraints, you will have to implement the business logic somewhere else.
For me, data integrity is paramount. If ever I can put a data integrity check directly into the database, I will do it because bugs in the application logic can exist and when the database itself enforces integrity constraint, I'm protected from those.
I don't want to have to deal with, to stay in the framework of the article, a shipped, but unpaid order. Was this a bug in the application? Did it actually ship? Did the payment fail?
If the database blows up on any attempts to store invalid data, I'm protected from having to ask these questions.