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?

  • 4
    Short answer: No. Long answer: No, unless you benchmark it and find out I'm wrong :). But to my knowledge, no, there's nothing to be gained by doing that.
    – AdamKG
    Commented Sep 2, 2021 at 20:49
  • 4
    As a side note don't use timetz
    – user1822
    Commented Sep 3, 2021 at 5:26
  • 2
    @a_horse_with_no_name: Interestingly, the page also contains a recommendation never to use precision specification with either timestamp or timestamptz. In that light, the OP's question about either storage or performance gains using reduced precision becomes somewhat moot.
    – Andriy M
    Commented Sep 3, 2021 at 11:18

2 Answers 2


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.

  • 2
    Do you think that the performance difference is noticeable? Commented Sep 3, 2021 at 7:35
  • 1
    The performance difference is measurable if you measure carefully. I doubt they are important very often though.
    – jjanes
    Commented Sep 3, 2021 at 22:42

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.

  • 2
    char is padded to the defined length, so there might be substantial difference in storage requirement between a varchar and a char of the same length. Don't use char
    – user1822
    Commented Sep 3, 2021 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.