abbreviated keys for BYTEA?

Posted Aug 6, 2015 19:23 UTC (Thu) by jberkus (guest, #55561)
In reply to: abbreviated keys for BYTEA? by zack
Parent article: "Big data" features coming in PostgreSQL 9.5

I guess I don't understand why you'd care that much about abbreviated key sorting on checksums? It's not like you'd care about soriting them. I guess just because of index build time?

If so, well, "patches welcome". Probably what you'd want to do with BYTEA is compare the first 8 bytes, sort, then compare the full values to break ties.

On the other hand, you could just store the checksums as NUMERICs, if you are fine with converting to hex and back.

abbreviated keys for BYTEA?

Posted Aug 7, 2015 18:58 UTC (Fri) by zack (subscriber, #7062) [Link] (1 responses)

> I guess I don't understand why you'd care that much about abbreviated key sorting on checksums? It's not like you'd care about soriting them. I guess just because of index build time?

Index (and specifically b-tree) build time and maintenance are my main concerns, yes.

But I was also under the impression that abbreviated keys are relevant also for (b-tree) index lookups and uniqueness constraint verifications, due to the comparisons needed to get down to the actual indexes values, no matter how shallow the index is. Maybe that's a wrong impression of mine?

abbreviated keys for BYTEA?

Posted Aug 7, 2015 21:17 UTC (Fri) by jberkus (guest, #55561) [Link]

Yes, they are related. Although if you're just trying to get an exact match on a checksum, you might consider using GIN indexes instead. For a simple scalar, GIN indexes in Postgres behave a lot like hash indexes.

Anyway, I encourage you to bring up the idea of BYTEA on a PostgreSQL mailing list. Nobody's opposed to extending abbreviated keys further, we just ran out of time for 9.5.