Data Types

Understand the range of possible values. Choose the data types that allow the smallest and largest possible values.

• How to calculate the row size, disk usage, and Column Tetris
• bigint for primary key if you know the table is going to grow over int range. The int to bigint migration is time consuming and expensive to do so. Case Study and this article
• Use uuid instead of varchar(36) PostgreSQL: UUID
• Use timestamptz and store UTC
• PostgreSQL’s text, char(n), and varchar(n) has no performance difference Source
• Use Network address data type (inet, 7 bytes) for IP addresses instead of varchar(15) (in the case of IPv4)
• double precision 8 bytes, 15 decimal digit precision; real 4 bytes, 6 decimal digit precision.
• Consider BRIN index for Time series table
• Use JSONB because B stands for Better.