Heikki Linnakangas wrote:
I'm sorry I missed the original discussions, but I have to ask: Why do
we want enums in core? The only potential advantage I can see over using
a look-up table and FK references is performance.
Well, there are a few things. Sometimes its tidiness, sometimes
integrity... I've seen more than one system with hundreds of these
things, and they've either gone down the table-per-enum solution, with
LOTS of extra tables whose values never change, or the EAV solution,
with one or two globally referenced tables to which everything in your
system has as a FK, and an integrity check in a trigger if you're very
lucky. Yuck on both accounts. Enums hit a sweet spot in the middle and
provide data integrity and performance for non-changing values.
1. What's the point of having comparison operators for enums? For most
use cases, there's no natural ordering of enum values.
Well, there are a number of cases where ordering IS important, and
indeed, enums provide a way to do it easily where many of the
alternative solutions do not. It's one of the key benefits.
2. The comparison routine compares oids, right? If the oids wrap around
when the enum values are created, the ordering isn't what the user expects.
As has been pointed out by others quicker on the draw than me, I do sort
the OIDs at enum creation time, for exactly this reason.
3. 4 bytes per value is wasteful if you're storing simple status codes
etc. Especially if you're doing this for performance, let's do no harm
by wasting space. One byte seems enough for the typical use cases. I'd
even argue that having a high upper limit on the number of enum values
encourages misuse of the feature.
I'd really love to have these fit into a 1 or 2 byte value on disk, but
AFAIK there's simply no way to do it currently in postgresql. If we ever
move to a solution where on-disk representation is noticeably different
from in-memory representation, then it might be doable. If that does
happen, we might benefit from other improvements such as being able to
order columns in a tuple on disk so as to minimize alignment padding,
not having to store a composite type's oid, etc. Until that happens,
though, if it ever does, this is probably the tightest on-disk
representation we're likely to get, unless we're happy to impose some
pretty severe restrictions, like 8 bits per enum, and only 256 enums in
total (for a 2 byte total). I was already shot down trying to make
similar restrictions when I first brought it up. :) The OID solution
seems to offend the least. :)
We did discuss this somewhat earlier, and I'm happy to take alternative
suggestions, but AFAIK this is about as good as it's going to get right now.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster