Re: Why is time with timezone 12 bytes?

On Wed, Sep 22, 2010 at 7:20 PM, Josh Berkus <josh(at)agliodbs(dot)com> 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