When designing a database table, you should take care to pick the appropriate data type. When the database goes to production, changing the data type of a column can become a very costly operation, especially for heavily-loaded tables. The cost often comes from locking the table, and in some cases, rewriting it. When picking a data type, consider a balance between the following factors:
- Extensibility: Can the maximum length of a type be increased or decreased without a full table rewrite and a full table scan?
- Data type size: Going for a safe option, such as choosing big integers instead of integers, will cause more storage consumption.
- Support: This factor is important for rich data types, such as XML, JSON, and hstore. If the drivers, such as JDBC drivers, don't support rich types, you need to write your own code to serialize and deserialize...
