Re: Range types

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 close to 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 with 8 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 uS precision), so we're not actually hurting ourselves on storage until 4284 or so. Not everyone needs uS precision, so it might 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 to compact a lot more if you used some kind of modified varlena (you'd want to store how long both the absolute time and the interval 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 for those hour-long meetings.

But if you switch to second precision, things get a lot more interesting: 1 byte overhead + 3 bytes interval gives you 194 days. 4 bytes of 1 second absolute time gets you epoch +/- 136 years. That means you could represent an entire period in 8 bytes.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net