Is there a performance or storage space advantage to lowering time/timestamp precision in PostgreSQL?

Question

PostgreSQL allows time/timestamp to specify a precision:

time, timestamp, and interval accept an optional precision value p which specifies the number of fractional digits retained in the seconds field. By default, there is no explicit bound on precision. The allowed range of p is from 0 to 6.

From PostgreSQL 13 Documentation

However it states that storage space is a constant 8-bytes for (timestamp and time without timezone) and 12-bytes for time with timezone regardless of p.

In the case that one doesn't need extra precision — say milliseconds(p = 3) or seconds(p=0) would suffice — is there an advantage to explicitly lowering the precision?

Answer 1

If you need rounded values, storing them rounded is going to be faster and less error-prone than dynamically rounding each time you access it.

And having less precision means more ties so more opportunity for index duplicate compression (on versions new enough to offer that). It would also offer better compression for the datafiles when they are offline (like in backups) and possibly when online if your FS offers built-in compression.

Answer 2

As is often the case with PostgreSQL, using different types is more about documenting your schema (both in terms of capability as well as intent) than performance optimization.

This is similiar to the CHAR vs VARCHAR debacle. In PostgreSQL both are internally stored in a variable-length array, so there's no performance difference, but you can still use different types to signal to the db user what he can expect from the stored data.

Please keep in mind however, that in the case of timestamp precision the rounding behaviour might be very much unexpected and unwanted.