Postgresql (and even Mysql for that matter) lets you explicitly declare foreign key references when you create/alter a table and then the database will enforce integrity of those references for you. Which is great because when someone writes some kind of code outside of Rails to work with your db, and that code has bugs, and those bugs impact data integrity, there is a hard stop at the DB layer preventing dangling/invalid references that will blow something up later.
A side effect of creating these explicit foreign key relationships is that any needed indices are created on both sides of the key. [UPDATE: Nope, I'm wrong, see below.]
Here the author has a Rails setup that fails to declare foreign key constraints when it's making a table with relations. You can see this in the DB description of the `products` table which references the `categories` tables via a plain int called `category_id`. As a result of not having an explicit foreign key constraint this table also has no index on `category_id`.
So given a table with poor data integrity at the SQL/db level and lacking an index as a sympton of this problem, the author treats the symptom and advises the creation of an index on `category_id`, leaving the real problem woefully intact. And that real problem, to be clear, is the fact that the database is not being used properly but rather treated as a relatively dumb place to dump columnar data; things that Postgres gives you for free are set aside and pseudo-duplicated in Rails code.
Now I'm not blasting the author because I don't know if this is a tactical issue or an issue with Rails itself. Does Rails not allow foreign key constrains in the migrations?
Whether this is a flaw in Rails or how it's being used, this is a bad solution to the problem. The RDBMS is your friend, use it. (And if you're not going to use the integrity constraints of the DB why are you using Postgres instead of say BerkeleyDB or a loosely configured MySQL or Mongo or whatever?)
A side effect of creating these explicit foreign key relationships is that any needed indices are created on both sides of the key.
This is simply not true.
Foreign key relationships typically use the primary key of the referenced table, which is implicitly indexed, by virtue of its being a primary key, not its being a foreign key. The referring column is never implicitly indexed, though it often should be explicitly indexed.
rosser=> create table referenced (id int primary key, blah text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "referenced_pkey" for table "referenced"
CREATE TABLE
rosser=> create table referencing (id int primary key, referenced_id int references referenced(id), stuff text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "referencing_pkey" for table "referencing"
CREATE TABLE
rosser=> \d referenced
Table "public.referenced"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
blah | text |
Indexes:
"referenced_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "referencing" CONSTRAINT "referencing_referenced_id_fkey" FOREIGN KEY (referenced_id) REFERENCES referenced(id)
rosser=> \d referencing
Table "public.referencing"
Column | Type | Modifiers
---------------+---------+-----------
id | integer | not null
referenced_id | integer |
stuff | text |
Indexes:
"referencing_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"referencing_referenced_id_fkey" FOREIGN KEY (referenced_id) REFERENCES referenced(id)
I stand corrected! Fair point. While I wish the original author had not sidestepped the data integrity issue, I can see why he'd want to address the index issue independently.
I was searching for the same thing myself recently, and it seems that DHH is against having any sort of logic in the database - he believes that all validations and constraints should be in the application instead of being in both (which I believe is the correct way).
I probably phrased that incorrectly. I definitely think that you should have validation logic at the database level. I've had a couple of major issues in the past (while still learning web dev) where lack of indexes/constraints has pretty much ruined my database when I tried use them in production
You also want to implement foreign keys in the database since you do not want to have to remember to lock referenced rows when inserting new referring rows. The database helps you out with concurrency issues.
I was about to suggest the same thing. And if you go with foreigner, then you can / should also use the aptly-named immigrant[1] gem to automatically detect where foreign keys are missing and create the migrations for you.
Postgresql (and even Mysql for that matter) lets you explicitly declare foreign key references when you create/alter a table and then the database will enforce integrity of those references for you. Which is great because when someone writes some kind of code outside of Rails to work with your db, and that code has bugs, and those bugs impact data integrity, there is a hard stop at the DB layer preventing dangling/invalid references that will blow something up later.
A side effect of creating these explicit foreign key relationships is that any needed indices are created on both sides of the key. [UPDATE: Nope, I'm wrong, see below.]
Here the author has a Rails setup that fails to declare foreign key constraints when it's making a table with relations. You can see this in the DB description of the `products` table which references the `categories` tables via a plain int called `category_id`. As a result of not having an explicit foreign key constraint this table also has no index on `category_id`.
So given a table with poor data integrity at the SQL/db level and lacking an index as a sympton of this problem, the author treats the symptom and advises the creation of an index on `category_id`, leaving the real problem woefully intact. And that real problem, to be clear, is the fact that the database is not being used properly but rather treated as a relatively dumb place to dump columnar data; things that Postgres gives you for free are set aside and pseudo-duplicated in Rails code.
Now I'm not blasting the author because I don't know if this is a tactical issue or an issue with Rails itself. Does Rails not allow foreign key constrains in the migrations?
Whether this is a flaw in Rails or how it's being used, this is a bad solution to the problem. The RDBMS is your friend, use it. (And if you're not going to use the integrity constraints of the DB why are you using Postgres instead of say BerkeleyDB or a loosely configured MySQL or Mongo or whatever?)