So, today I was discussing in the Elixir Brasil telegram group and some questions about types in migrations and ecto schemas. I've decided just to put everything together so it's easier in the future to direct people to this post and help them properly on this subject. It was part of the idea when I started this blog to put small bits of advice that I think can be useful for other people and myself in the future.

  • Schema Types are defined in ecto documentation, and is basically used to translate the data that comes from the database to elixir types.

  • Ecto.Type is a behaviour defined so you can properly load data from the database or load data from the database to a defined elixir struct, people usually call it ecto custom types.

  • Migrations types that you use when adding/changing a column should be defined in the database driver(like postgrex). Most of them are converted from atom to the expression you use on create~/~alter statements.

Now being specific to PostgreSQL, there are some tips when using/setting types to the column.

  • :string or :varchar always gonna set the default size to 255. I recommend you to always set it as :text or at least :"varchar[]".

  • :map will always set to :jsonb in the database and they're equivalent. Avoid using :json except if you're using a PostgreSQL version that don't have support to :jsonb.

About always using :text, I'm not sure when this was available but at least since postgres 8.0 there is no performance difference for the character types.

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.