Re: Hardware upgrade for a high-traffic database

Tom Lane wrote:
> The difference would be pretty marginal --- especially if you choose
to
> use bigints instead of ints. (A timestamp is just a float8 or bigint
> under the hood, and is no more expensive to compare than those
datatypes.
> Timestamps *are* expensive to convert for I/O, but comparison does not
> have to do that.) I wouldn't recommend kluging up your data schema
just
> for that.

Right (int4 use was assumed). I agree, but it's kind of a 'two birds
with one stone' kind of thing, because it's easier to work with reverse
ordering integers than time values. So I claim a measurable win (the
real gainer of course being able to select and sort on the same key,
which works on any type), based on the int4-int8 difference, which is a
33% reduction in key size.

One claim I don't have the data for is that read-forward is better than
read-back, but my gut tells me he'll get a better cache hit ratio that
way. This will be very difficult to measure.

As for kludging, using a decrementing sequence is not a bad idea if the
general tendency is to read the table backwards, even if just for
conceptual reasons. The main kludge is the int4 assumption, which (IMO)
isn't so bad. He would just have to rebuild the existing p-key in
reverse order (10$ says his keys are all already int4s), and hopefully
not mess with the application code too much.

At least, it's what I would try if I was in his shoes :)

YMMV
Merlin