On Dec 15, 2009, at 6:29 PM, Jeff Davis wrote:
> On Tue, 2009-12-15 at 18:06 -0600, decibel wrote:
>> Now that varlena's don't have an enormous fixed overhead, perhaps it's
>> worth looking at using them. Obviously some operations would be
>> slower, but for your stated examples of auditing and history, I
>> suspect that you're not going to notice the overhead that much.
>
> For most varvarlena types, you only get stuck with the full alignment
> burden if you get unlucky. In this case, we're moving from 16 bytes to
> 17, which really means 24 bytes with alignment. Try creating two tables:
My thought was that many timestamps don't actually need 16 bytes. Jan 1, 2000 certainly doesn't. So if your dates are
closeto the PG epoch, you can get away with far fewer than 8 bytes, which means varlena would be a win.
*does some math* Actually, we're kinda screwed with microsecond time. Neglecting leap years and what-not, I come up
with8 years as the most you can represent in 6 bytes. The good news is that 7 bytes gets you all the way to 2284 (with
uSprecision), so we're not actually hurting ourselves on storage until 4284 or so. Not everyone needs uS precision, so
itmight be worth looking at a varlena-based timestamp.
I was actually thinking about storing something like an absolute time and then an interval. That might have been able
tocompact a lot more if you used some kind of modified varlena (you'd want to store how long both the absolute time and
theinterval were). But again, we're rather screwed if you use uS precision. 1 byte header + 7 bytes for absolute gets
us+/- 2284 years from epoch, but 4 bytes for interval only gives us 4294 seconds at uS precision. Maybe still worth it
forthose hour-long meetings.
But if you switch to second precision, things get a lot more interesting: 1 byte overhead + 3 bytes interval gives you
194days. 4 bytes of 1 second absolute time gets you epoch +/- 136 years. That means you could represent an entire
periodin 8 bytes.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net