FAQ
All,

I was just checking on our year-2027 compliance, and happened to notice
that time with time zone takes up 12 bytes. This seems peculiar, given
that timestamp with time zone is only 8 bytes, and at my count we only
need 5 for the time with microsecond precision. What's up with that?

Also, what is the real range of our 8-byte *integer* timestamp?

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

## Search Discussions

•  at Sep 22, 2010 at 9:55 pm ⇧

On 22 September 2010 22:01, Josh Berkus wrote:
All,

I was just checking on our year-2027 compliance, and happened to notice
that time with time zone takes up 12 bytes.  This seems peculiar, given
that timestamp with time zone is only 8 bytes, and at my count we only
need 5 for the time with microsecond precision.  What's up with that?

Also, what is the real range of our 8-byte *integer* timestamp?
The time is 8 bytes, (1,000,000 microseconds * 60 minutes, * 24 hours
= 1,440,000,000 microseconds = 31 bits = 8 bytes).

The timezone displacement takes up to 12 bits, meaning 3 bytes.
(1460+1459 = 2919 = 12 bits = 3 bytes). So that's 11 bytes. Not sure
where the extra 1 byte comes from.

--
Thom Brown
IRC (freenode): dark_ixion
Registered Linux user: #516935
•  at Sep 22, 2010 at 9:58 pm ⇧

On Wed, Sep 22, 2010 at 10:54:53PM +0100, Thom Brown wrote:
On 22 September 2010 22:01, Josh Berkus wrote:
All,

I was just checking on our year-2027 compliance, and happened to notice
that time with time zone takes up 12 bytes. ?This seems peculiar, given
that timestamp with time zone is only 8 bytes, and at my count we only
need 5 for the time with microsecond precision. ?What's up with that?

Also, what is the real range of our 8-byte *integer* timestamp?
The time is 8 bytes, (1,000,000 microseconds * 60 minutes, * 24 hours
= 1,440,000,000 microseconds = 31 bits = 8 bytes).
31 bits = approx. 4 bytes at 8 bits/byte, not 8 bytes.
The timezone displacement takes up to 12 bits, meaning 3 bytes.
(1460+1459 = 2919 = 12 bits = 3 bytes). So that's 11 bytes. Not sure
where the extra 1 byte comes from.
This would yield 7 bytes.

Ken
•  at Sep 22, 2010 at 10:02 pm ⇧

On 22 September 2010 22:58, Kenneth Marshall wrote:
On Wed, Sep 22, 2010 at 10:54:53PM +0100, Thom Brown wrote:
On 22 September 2010 22:01, Josh Berkus wrote:
All,

I was just checking on our year-2027 compliance, and happened to notice
that time with time zone takes up 12 bytes. ?This seems peculiar, given
that timestamp with time zone is only 8 bytes, and at my count we only
need 5 for the time with microsecond precision. ?What's up with that?

Also, what is the real range of our 8-byte *integer* timestamp?
The time is 8 bytes, (1,000,000 microseconds * 60 minutes, * 24 hours
= 1,440,000,000 microseconds = 31 bits = 8 bytes).
31 bits = approx. 4 bytes at 8 bits/byte, not 8 bytes.
The timezone displacement takes up to 12 bits, meaning 3 bytes.
(1460+1459 = 2919 = 12 bits = 3 bytes).  So that's 11 bytes.  Not sure
where the extra 1 byte comes from.
This would yield 7 bytes.
I think it's clear I should get some sleep. :S

--
Thom Brown
IRC (freenode): dark_ixion
Registered Linux user: #516935
•  at Sep 22, 2010 at 9:57 pm ⇧

Josh Berkus writes:
I was just checking on our year-2027 compliance, and happened to notice
that time with time zone takes up 12 bytes. This seems peculiar, given
that timestamp with time zone is only 8 bytes, and at my count we only
need 5 for the time with microsecond precision. What's up with that?
I think it's an 8-byte seconds count plus 4 bytes to indicate the
timezone. If this datatype had any actual real-world use then it might
be worth worrying about how big it is, but AFAICS its only excuse for
existence is to satisfy the SQL standard.
Also, what is the real range of our 8-byte *integer* timestamp?
See the fine manual. I believe the limits have more to do with
calendar arithmetic than with the nominal range of 2^64 microseconds.

regards, tom lane
•  at Sep 22, 2010 at 10:01 pm ⇧

Also, what is the real range of our 8-byte *integer* timestamp?
See the fine manual. I believe the limits have more to do with
calendar arithmetic than with the nominal range of 2^64 microseconds.
I'm asking based on that. The docs only give the limits for a *float*
timestamp. I'd like to fix the docs, but I can only do it if I have the
data ...

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
•  at Sep 22, 2010 at 10:51 pm ⇧

Josh Berkus writes:
Also, what is the real range of our 8-byte *integer* timestamp?
See the fine manual. I believe the limits have more to do with
calendar arithmetic than with the nominal range of 2^64 microseconds.
I'm asking based on that. The docs only give the limits for a *float*
timestamp. I'd like to fix the docs, but I can only do it if I have the
data ...
It's the same, because the limits are calendar based (particularly,
the Julian-date functions) and not dependent on the representation.

regards, tom lane
•  at Sep 22, 2010 at 10:59 pm ⇧

It's the same, because the limits are calendar based (particularly,
the Julian-date functions) and not dependent on the representation.
Hmmm? Just storing dates for the range described (until the year
294,000) takes 8bytes by my calculations. And that's without the 3
bytes for the time zone. Is my math off?

And, of course, this doesn't answer at all why time with time zone is so
huge.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
•  at Sep 22, 2010 at 11:19 pm ⇧

Josh Berkus writes:
It's the same, because the limits are calendar based (particularly,
the Julian-date functions) and not dependent on the representation.
Hmmm? Just storing dates for the range described (until the year
294,000) takes 8bytes by my calculations. And that's without the 3
bytes for the time zone. Is my math off?
timestamptz stores GMT; it doesn't store timezone separately.
(If it did, we'd need more than 8 bytes...)
And, of course, this doesn't answer at all why time with time zone is so
huge.
Because we haven't lifted a finger to optimize it.

regards, tom lane
•  at Sep 22, 2010 at 11:21 pm ⇧

timestamptz stores GMT; it doesn't store timezone separately.
(If it did, we'd need more than 8 bytes...)
Oh, yeah. Duh.
Because we haven't lifted a finger to optimize it.
Well, that's a direct answer. Ok, will put it in the list of "TODO next
time we change the on-disk format".

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
•  at Sep 23, 2010 at 12:48 am ⇧

On Wed, Sep 22, 2010 at 7:20 PM, Josh Berkus wrote:
timestamptz stores GMT; it doesn't store timezone separately.
(If it did, we'd need more than 8 bytes...)
Oh, yeah.  Duh.
Because we haven't lifted a finger to optimize it.
Well, that's a direct answer.  Ok, will put it in the list of "TODO next
time we change the on-disk format".
Technically, there's no reason why we can't do this for 9.1. What we
can do is change the name of the "time with timezone" type to
something like "oldtimetz", keeping the current OID. And then we can
add a new type called "time with timezone". Then, with some suitable
hacking of pg_dump --binary-upgrade, I think it should be possible to
make timetz columns from pre-9.1 databases turn into oldtimetz columns
when pg_upgrade is used. New applications will of course get the new
data type. Then, in a future release (let's call it 10.0), we could
remove the oldtimetz types. pg_upgrade would then forbid upgrades to
the release that used the old type, by complaining about (1) upgrades
from 8.4/9.0 with timetz columns, or (2) upgrades from 9.1+ with
oldtimetz columns. Users would instead be instructed to upgrade to a
9.1+ release, use ALTER TABLE ... TYPE timetz, and then upgrade again.
Obviously this could still be inconvenient for some users, but it's a
lot better than breaking everything all at once: you have 5 or 10
years to find time to rewrite the table.

If someone decides to attack this, it would also be good to see about
reducing the typalign to something less than "d". I am concerned that
all of that alignment is wasting a great deal of space on disk, which
is becoming ever more of a problem as people start to use PostgreSQL
with larger and larger databases. It seems like the performance
benefit is likely pretty limited, too. Copying a 64-bit value that is
only 4-byte aligned rather than 8-byte aligned should only be very
slightly slower if you do it as two 4-byte fetches rather than a
single 8-byte fetch, I would think, and it seems like a small price to
pay to avoid inserting as many as 7 padding bytes.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
•  at Sep 23, 2010 at 1:00 am ⇧

Robert Haas writes:
Technically, there's no reason why we can't do this for 9.1. What we
can do is change the name of the "time with timezone" type to
something like "oldtimetz", keeping the current OID. And then we can
add a new type called "time with timezone". [ with large amounts of
consequent work ]
I think you missed the point of my response, which is that there are
easily 10^6 more-pressing things to work on than the size of timetz.
Do you know of any actual use cases for it?

regards, tom lane
•  at Sep 23, 2010 at 1:06 am ⇧

On Wed, Sep 22, 2010 at 9:00 PM, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
Technically, there's no reason why we can't do this for 9.1.  What we
can do is change the name of the "time with timezone" type to
something like "oldtimetz", keeping the current OID.  And then we can
add a new type called "time with timezone".  [ with large amounts of
consequent work ]
I think you missed the point of my response, which is that there are
easily 10^6 more-pressing things to work on than the size of timetz.
Do you know of any actual use cases for it?
Well, I wasn't responding to you - I was responding to Josh.
Regardless of the merits of redesigning this particular data type, I
think it's important for us to cultivate a mindset of figuring out how
we can make gradual improvements to the on-disk format without
earth-shattering consequences for pg_upgrade. Mind you, I don't
currently have the time to hack on this for, uh, more or less the
reason you state. But if my boss told me he double my pay if I got it
done, I wouldn't tell him it's technically impossible.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
•  at Sep 23, 2010 at 5:26 pm ⇧

On 9/22/10 6:00 PM, Tom Lane wrote:
I think you missed the point of my response, which is that there are
easily 106 more-pressing things to work on than the size of timetz.
Do you know of any actual use cases for it?
It would be a good project to add to the list of "easy TODOs to get
started with."

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
•  at Sep 23, 2010 at 5:29 pm ⇧

Josh Berkus wrote:
On 9/22/10 6:00 PM, Tom Lane wrote:
I think you missed the point of my response, which is that there are
easily 106 more-pressing things to work on than the size of timetz.
Do you know of any actual use cases for it?
It would be a good project to add to the list of "easy TODOs to get
started with."
Except for the pg_upgrade issue.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +
•  at Sep 23, 2010 at 5:35 pm ⇧

On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian wrote:
Josh Berkus wrote:
On 9/22/10 6:00 PM, Tom Lane wrote:
I think you missed the point of my response, which is that there are
easily 106 more-pressing things to work on than the size of timetz.
Do you know of any actual use cases for it?
It would be a good project to add to the list of "easy TODOs to get
started with."
Except for the pg_upgrade issue.
Which is a big "except".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
•  at Sep 23, 2010 at 5:47 pm ⇧

Robert Haas writes:
On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian wrote:
Josh Berkus wrote:
It would be a good project to add to the list of "easy TODOs to get
started with."
Except for the pg_upgrade issue.
Which is a big "except".
Yeah. That constraint is what leads me to think that the return on
effort is just not worth it. Maybe we should file this in the category
of "things to look at next time we break on-disk compatibility".

regards, tom lane
•  at Sep 23, 2010 at 5:54 pm ⇧

Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian wrote:
Josh Berkus wrote:
It would be a good project to add to the list of "easy TODOs to get
started with."
Except for the pg_upgrade issue.
Which is a big "except".
Yeah. That constraint is what leads me to think that the return on
effort is just not worth it. Maybe we should file this in the category
of "things to look at next time we break on-disk compatibility".
Yes, I would like to see such a category on the TODO list. Should I do
it?

FYI, I am please at the lack of serious problems with pg_upgrade.
People are obviously using it because the are filing bug reports, but
none of them are serious, and relate to pilot error or odd
configurations.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +
•  at Sep 23, 2010 at 6:33 pm ⇧

On Thu, Sep 23, 2010 at 1:46 PM, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian wrote:
Josh Berkus wrote:
It would be a good project to add to the list of "easy TODOs to get
started with."
Except for the pg_upgrade issue.
Which is a big "except".
Yeah.  That constraint is what leads me to think that the return on
effort is just not worth it.  Maybe we should file this in the category
of "things to look at next time we break on-disk compatibility".
I'm worried about how we're going to manage that. First, as
pg_upgrade becomes more mature, the penalty for breaking on-disk
compatibility gets a LOT bigger. I'd like to think that "the next
time we break on-disk compatibility" means approximately "never", or
at least "not for a very long time". Second, if we do decide to break
it, how and when will we make that decision? Are we just going to
decide to break it when we run into a feature that we really want that
can't be had any other way? If we want to make breaking on-disk
compatibility something that only happens every 5 years or so, we had
better give people - I don't know, a year's notice - so that we can
really knock out everything people have any interest in fixing in one
release.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
•  at Sep 23, 2010 at 7:20 pm ⇧

Excerpts from Robert Haas's message of jue sep 23 14:33:06 -0400 2010:

I'm worried about how we're going to manage that. First, as
pg_upgrade becomes more mature, the penalty for breaking on-disk
compatibility gets a LOT bigger. I'd like to think that "the next
time we break on-disk compatibility" means approximately "never", or
at least "not for a very long time". Second, if we do decide to break
it, how and when will we make that decision?
I liked your earlier suggestion: if somebody wants to pg_upgrade, he
needs to go to the latest minor release of their branch, run some
command to upgrade the on-disk format (say ALTER TABLE / SET TYPE), and

Now if it was workable to handle floating-point datetimes to integer
datetimes this way, it would be excellent.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
•  at Sep 23, 2010 at 7:20 pm ⇧

Robert Haas wrote:
On Thu, Sep 23, 2010 at 1:46 PM, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Thu, Sep 23, 2010 at 1:29 PM, Bruce Momjian wrote:
Josh Berkus wrote:
It would be a good project to add to the list of "easy TODOs to get
started with."
Except for the pg_upgrade issue.
Which is a big "except".
Yeah. ?That constraint is what leads me to think that the return on
effort is just not worth it. ?Maybe we should file this in the category
of "things to look at next time we break on-disk compatibility".
I'm worried about how we're going to manage that. First, as
pg_upgrade becomes more mature, the penalty for breaking on-disk
compatibility gets a LOT bigger. I'd like to think that "the next
time we break on-disk compatibility" means approximately "never", or
at least "not for a very long time". Second, if we do decide to break
it, how and when will we make that decision? Are we just going to
decide to break it when we run into a feature that we really want that
can't be had any other way? If we want to make breaking on-disk
compatibility something that only happens every 5 years or so, we had
better give people - I don't know, a year's notice - so that we can
really knock out everything people have any interest in fixing in one
release.
Let me come clean and explain that I am worried pg_upgrade has limited
our ability to make data format changes.

pg_upgrade is much more accepted now than I think anyone expected a year
ago. Our users are now going to complain if pg_upgrade upgrades are not
supported in future releases, which eventually is going to cause us
problems.

I think having binary upgrades for 9.0 was a big features, and got
mentioned in the press release, but let's not kid ourselves that we
aren't going down a road that might be paved with pain.

We have explored all sorts of ideas to mitigate the pain, like new data
type oids and reading (writing?) old data format pages, but that is all
untested territory.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +
•  at Sep 23, 2010 at 7:44 pm ⇧

On Thu, 2010-09-23 at 15:20 -0400, Bruce Momjian wrote:

decide to break it when we run into a feature that we really want that
can't be had any other way? If we want to make breaking on-disk
compatibility something that only happens every 5 years or so, we had
better give people - I don't know, a year's notice - so that we can
really knock out everything people have any interest in fixing in one
release.
Let me come clean and explain that I am worried pg_upgrade has limited
our ability to make data format changes.
It is nice to see hackers finally realizing that this is true (and
required).
pg_upgrade is much more accepted now than I think anyone expected a year
ago. Our users are now going to complain if pg_upgrade upgrades are not
supported in future releases, which eventually is going to cause us
problems.
"us" being -hackers yes, but it will only help the community.

Sincerely,

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
•  at Sep 23, 2010 at 7:48 pm ⇧

Joshua D. Drake wrote:
On Thu, 2010-09-23 at 15:20 -0400, Bruce Momjian wrote:

decide to break it when we run into a feature that we really want that
can't be had any other way? If we want to make breaking on-disk
compatibility something that only happens every 5 years or so, we had
better give people - I don't know, a year's notice - so that we can
really knock out everything people have any interest in fixing in one
release.
Let me come clean and explain that I am worried pg_upgrade has limited
our ability to make data format changes.
It is nice to see hackers finally realizing that this is true (and
required).
It is like credit card companies offering customer perks to encourage
vendors to accept credit cards. It is not something vendors set out to
do, but it becomes a customer disappointment if they don't comply.
pg_upgrade is much more accepted now than I think anyone expected a year
ago. Our users are now going to complain if pg_upgrade upgrades are not
supported in future releases, which eventually is going to cause us
problems.
"us" being -hackers yes, but it will only help the community.
Right.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +
•  at Sep 25, 2010 at 10:21 am ⇧

On Thu, Sep 23, 2010 at 02:33:06PM -0400, Robert Haas wrote:
I'm worried about how we're going to manage that. First, as
pg_upgrade becomes more mature, the penalty for breaking on-disk
compatibility gets a LOT bigger. I'd like to think that "the next
time we break on-disk compatibility" means approximately "never", or
at least "not for a very long time". Second, if we do decide to break
it, how and when will we make that decision? Are we just going to
decide to break it when we run into a feature that we really want that
can't be had any other way?
I don't think we should be discouraging people from trying to solve the
problems though. Given that you know that eventually there will come a
time that you need to change the format, it seems it would be better to
have one release that changes a datatype, one that changes the page
layout, etc, rather than doing one release that breaks everything at
once.

From a pure testing point of view, if someone coded up a multi-OID
approach to data type upgrades and it was actually tested in a release
that would give a lot more confidence than changing everything at once.

So, regular small changes rather than one big change that nobody wants
to try.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Patriotism is when love of your own people comes first; nationalism,
when hate for people other than your own comes first.
- Charles de Gaulle

## Related Discussions

 view thread | post
Discussion Overview
 group pgsql-hackers categories postgresql posted Sep 22, '10 at 9:01p active Sep 25, '10 at 10:21a posts 24 users 9 website postgresql.org... irc #postgresql

### 9 users in discussion

Content

People

Support

Translate

site design / logo © 2022 Grokbase