When designing a database table, one should take care in picking the appropriate data type. When the database goes to production, changing the data type of a column might 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.
PostgreSQL provides a very extensive set of data types. Some of the native data type categories are as follows:
- Numeric type
- Character type
- Date and time types
These data types are common for most relational databases. Moreover, they are often sufficient for modeling traditional applications.