Re: A space-efficient, user-friendly way to store categorical data

On 02/11/2018 10:06 PM, Thomas Munro wrote:
> On Mon, Feb 12, 2018 at 12:24 PM, Andrew Dunstan
> <andrew.dunstan@2ndquadrant.com> wrote:
>> On Mon, Feb 12, 2018 at 9:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Andrew Kane <andrew@chartkick.com> writes:
>>>> A better option could be a new "dynamic enum" type, which would have
>>>> similar storage requirements as an enum, but instead of labels being
>>>> declared ahead of time, they would be added as data is inserted.
>>>
>>> You realize, of course, that it's possible to add labels to an enum type
>>> today.  (Removing them is another story.)
>>>
>>> You haven't explained exactly what you have in mind that is going to be
>>> able to duplicate the advantages of the current enum implementation
>>> without its disadvantages, so it's hard to evaluate this proposal.
>>>
>>
>>
>> This sounds rather like the idea I have been tossing around in my head
>> for a while, and in sporadic discussions with a few people, for a
>> dictionary object. The idea is to have an append-only list of labels
>> which would not obey transactional semantics, and would thus help us
>> avoid the pitfalls of enums - there wouldn't be any rollback of an
>> addition.  The use case would be for a jsonb representation which
>> would replace object keys with the oid value of the corresponding
>> dictionary entry rather like enums now. We could have a per-table
>> dictionary which in most typical json use cases would be very small,
>> and we know from some experimental data that the compression in space
>> used from such a change would often be substantial.
>>
>> This would have to be modifiable dynamically rather than requiring
>> explicit additions to the dictionary, to be of practical use for the
>> jsonb case, I believe.
>>
>> I hadn't thought about this as a sort of super enum that was usable
>> directly by users, but it makes sense.
>>
>> I have no idea how hard or even possible it would be to implement.
>
> I have had thoughts over the years about something similar, but going
> the other way and hiding it from the end user.  If you could declare a
> column to have a special compressed property (independently of the
> type) then it could either automatically maintain a dictionary, or at
> least build a new dictionary for your when you next run some kind of
> COMPRESS operation.  There would be no user visible difference except
> footprint.  In ancient DB2 they had a column property along those
> lines called "VALUE COMPRESSION" (they also have a row-level version,
> and now they have much more advanced kinds of adaptive compression
> that I haven't kept up with).  In some ways it'd be a bit like toast
> with shared entries, but I haven't seriously looked into how such a
> thing might be implemented.

For what it is worth, there is a similar concept in R called "factors".
When categorical data is stored in a data.frame (R language equivalent
to relations) it is transparently and automatically converted. I believe
this is both for storage compression and to facilitate some of the
analytics. In R you can also explicitly specify to *not* convert strings
to factors as a performance optimization, because that conversion does
have a noticeable impact during ingestion and is not always needed.

I can also envision usefulness of this type of mechanism in other
security related scenarios.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development