At the far end, any programming logic you put in the DB will be impossible to scale if needed. You can only have one DB, but any number of Rails servers.
Database constraints are a matter of preference and skill set. They make some things much harder when you have to delete things is certain convoluted orders, and I can get the correctness through tests. Your decisions may differ. That's fine.
You should always put indexes on things you query by. Be vigilant. This is the one case where "only optimize when you have a performance problem" rule doesn't apply.
> any programming logic you put in the DB will be impossible to scale if needed.
> You can only have one DB, but any number of Rails servers.
The difference in processing time between a database with no rules and one with all of them is usually negligible.
> You should always put indexes on things you query by
I have put indexes on things, and PostgreSQL still decided it was more efficient to do a sequential scan. There are cases where they don't make a difference ("low cardinality" or something like that). Also, indexes slow down writes and eat disk space. Usually each is just a little. But it can add up sometimes.
The advice I see most is to run EXPLAIN and judge from that. Even after you've added the index and run ANALYZE, run EXPLAIN again to make sure that the database is using the index and that it makes a difference.
FWIW I've worked for multiple companies where tables couldn't reasonably be indexed.
Special cases though, usually write-only analytic tables that are queried once in a blue moon and have incredibly high traffic (~millions of rows per hour).
At the far end, any programming logic you put in the DB will be impossible to scale if needed. You can only have one DB, but any number of Rails servers.
Database constraints are a matter of preference and skill set. They make some things much harder when you have to delete things is certain convoluted orders, and I can get the correctness through tests. Your decisions may differ. That's fine.
You should always put indexes on things you query by. Be vigilant. This is the one case where "only optimize when you have a performance problem" rule doesn't apply.