When creating the schema for your timeseries database it is beneficial to follow postgres best practices for schema design. You can find an extensive list in the PostgreSQL Wiki. Since this list is quite long I want to highlight the most important ones for timeseries databases.
-
Use timestamp with time zone (timestamptz) for any timestamps
The normal timestamp datatype will always store local time without any timezone information.
This will make conversions to other timezones problematic as you might not have the correct source timezone especially when also dealing with daylight saving time. Additionally there will be problems if you ever want to change the local timezone of your installation. timestamp with time zone on the other hand will be stored normalized as UTC making conversions to another timezone when presenting to different users very easy. Internally both datatypes are stored as 64-bit integer so you do not gain any storage advantage by choosing timestamp without timezone over timestamp with timezone. -
Use text datatype for any character data
Do not use varchar/char for any character data but use text instead. If you need a character datatype with length limitation it is better to use text with a check constraint instead.
Since untyped character literals will default to text not using text will lead to additional casts in your queries when you use character literals in query constraints. These additional casts might lead to hard to debug performance problems and prevent index usage or other optimizations.
Internally char/varchar/text are stored the same way so you do not use less storage by using them over text.