Thread: does timestamp precision affect storage size?
i'm cleaning up some queries for performance, and noticed that we never use precision beyond the second (ie, `timestamp(0)`)in our business logic. would there be any savings in storage or performance improvements from losing the resolution on fractional seconds, or are`timestamp(precision)` effectively the same for storage as `timestamp`? (based on docs, I assume the latter but wantedto check)
On 21/06/16 22:39, Jonathan Vanasco wrote: > i'm cleaning up some queries for performance, and noticed that we never use precision beyond the second (ie, `timestamp(0)`)in our business logic. > > would there be any savings in storage or performance improvements from losing the resolution on fractional seconds, orare `timestamp(precision)` effectively the same for storage as `timestamp`? (based on docs, I assume the latter but wantedto check) No, there are no space savings here. =# select pg_column_size('now'::timestamptz(0)), pg_column_size('now'::timestamptz); pg_column_size | pg_column_size ----------------+---------------- 8 | 8 (1 row) -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Jonathan Vanasco <postgres@2xlp.com> writes: > would there be any savings in storage or performance improvements from losing the resolution on fractional seconds, Storage-wise, no. If you have a resolution spec on your columns now, I think dropping the resolution spec would save you a few nanoseconds per row insertion due to not having to apply the roundoff function. Adding one would certainly not improve speed. regards, tom lane
On Jun 21, 2016, at 4:50 PM, Tom Lane wrote: > Storage-wise, no. If you have a resolution spec on your columns now, > I think dropping the resolution spec would save you a few nanoseconds per > row insertion due to not having to apply the roundoff function. Adding > one would certainly not improve speed. On Jun 21, 2016, at 4:47 PM, Vik Fearing wrote: > No, there are no space savings here. > > =# select pg_column_size('now'::timestamptz(0)), > pg_column_size('now'::timestamptz); Thanks. I thought that was happening, but wanted to make sure. the allure of shaving a byte or two off some rows couldn'tbe ignored ;)