Tom MacWright

Thoughts on storing stuff in databases

  • User preferences should be columns in the users table. Don’t get clever with a json column or hstore. When you introduce new preferences, the power of types and default values is worth the headache of managing columns.
  • Emails should probably be citext, case-insensitive text. But don’t count on that to prevent people from signing up multiple times - there are many ways to do that.
  • Most text columns should be TEXT. The char-limited versions like varchar aren’t any faster or better on Postgres.
  • Just try not to use json or jsonp, ever. Having a schema is so useful. I have regretted every time that I used these things.
  • Make as many things NOT NULL as possible. Basically the same as “don’t use json” - if you don’t enforce null checks at the database level, null values will probably sneak in eventually.
  • Most of the time choose an enum instead of a boolean. There is usually a third value beyond true & false that you’ll realize you need.
  • Generally store times and dates without timezones. There are very, very few cases where you want to store the original timezone rather than store everything in UTC and format it to the user’s TZ at display time.
  • Most tables should have a createdAt column that defaults to NOW(). Chances are, you’ll need it eventually.