rails/rails — pull request #13885 by mauricio (2014-01-30 02:09:01+00:00)
Title: TableDefinition.timestamps should do the right thing by default and set the columns to not null

And why should it do that?

ActiveRecord will always set a value for these fields, so we don't have the case of them being empty or null anyway, but the real issue lies when you want to index one of those fields. For most databases (I'm going to link to MySQL and PostgreSQL specifically), an index for a field that allows `null` as a value is less efficient, because it has to handle the case of having a value or having `null`.

At [High Performance MySQL](http://shop.oreilly.com/product/0636920022343.do) the authors state:

> A lot of tables include nullable columns even when the application does not need to store NULL (the absence of a value), merely because it’s the default. It’s usually best to specify columns as NOT NULL unless you intend to store NULL in them.
> It’s harder for MySQL to optimize queries that refer to nullable columns, because they make indexes, index statistics, and value comparisons more complicated. A nullable column uses more storage space and requires special processing inside MySQL. When a nullable column is indexed, it requires an extra byte per entry and can even cause a fixed-size index (such as an index on a single integer column) to be converted to a variable-sized one in MyISAM.
> The performance improvement from changing NULL columns to NOT NULL is usually small, so don’t make it a priority to find and change them on an existing schema unless you know they are causing problems. However, if you’re planning to index columns, avoid making them nullable if possible.
> There are exceptions, of course. For example, it’s worth mentioning that InnoDB stores NULL with a single bit, so it can be pretty space-efficient for sparsely populated data. This doesn’t apply to MyISAM, though.

And as for PostgreSQL, only after 8.3 that indexing null fields is optimized (taken from [PostgreSQL 9: High Performance](http://www.packtpub.com/postgresql-90-high-performance/book):

> Indexing of NULL values is greatly improved, including the ability to control where they are placed relative to the rest of the table (beginning or end).

So it would be great if Rails also made the best decision possible here and decided these fields should be `NOT NULL` by default.