does timestamp precision affect storage size?

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, or are `timestamp(precision)` effectively the same for storage as `timestamp`? (based on docs, I assume the latter but wanted to 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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't be ignored ;)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general