Enum-likes in Postgres: Text or SmallInt ?

[bensheldon]

As I work towards #831, I'm finding a few places where I want to have enum-like behavior in the database (e.g. lock_strategy: advisory, row, hybrid).

I'm wondering if the decision for how to implement it really matters?

• Text: I generally reach form Text columns in Postgres, because I like having a human readable value when I'm writing SQL in my business intelligence tools (usually Metababase). Rails enums do work with strings which I frequently reach for in my projects, though it seems like that's an unsupported capability so maybe not something to build a public gem around 🤷🏻 Also the storage and indexing requirement is more.
• SmallInt: This seems to be what's recommended for working with Rails enums. It seems to have the lightest impact on the database (storage, indexibility). But I dislike it as a practical matter when querying it outside of Rails because I have to remap the values in my BI tool (usually with a messy a SQL CASE WHEN).

I'm opening this discussion mainly on the grounds that this is the kind of decision that takes up an inordinate amount of my headspace when working through items like #831 so I wanted to open it up for discussion just to hopefully have someone tell me either the decision doesn't matter, or convince me one way or the other. Thank you for you help 💖

btw, I'm completely rejecting Postgres native enums. Rails support isn't completely there yet and I don't like that they're global to the database which is a larger footprint than I want a gem to have.

[reczy]

One approach I started exploring recently (dev only still) is to use text backed by a reference table. This gives you the human readability of text (since the actual text is the id of the reference table - no artificial numbers/sequences/uuids) along with postgres' built in foreign key protection.

SmallInts would be ideal for performance, but I've never liked the mental overhead of having to translate numbers to real values while digging through data. Maybe it's worth it for this library though?

Might still need some polish, but see below:

module Models
  module ReferenceTableHelper
    extend ActiveSupport::Concern

    class_methods do
      def repopulate!
        upsert_all(self::ALL_IDS.map { |id| { id: id } })
      end
    end

  end
end

class AccountState < ApplicationRecord
  include Models::ReferenceTableHelper

  ACTIVE = 'active'
  BLOCKED = 'blocked'
  ETC = 'etc'

  ALL_IDS = [
    ACTIVE,
    BLOCKED,
    ETC,
  ].freeze
end

class Account < ApplicationRecord
  belongs_to :account_state, foreign_key: :state
end

# migration helper

  def fk_to_reference_table(t, column, ref_table)
    t.text column, null: false
    t.foreign_key ref_table, column: column
    t.index column
  end

# migration

  create_table :account_states, id: :text

  change_table :accounts do |t|
    fk_to_reference_table(t, :state, :account_states)
  end

  Account.repopulate!

[bensheldon]

Capturing some responses from Mastodon:

we always use text because I want the value to be clear from looking at the data/sql alone.

I might feel differently for something like GoodJob which isn’t an application table I expect to query but… nah, I still don’t think the space savings are worth the obfuscation

I always prefer text, sometimes I want to query using SQL and I hate to do something like: "where state in (2,4,5)".

+1 to "I'm completely rejecting Postgres native enums". My experience with PG enums isn't good. I had many problems with non-Rails related tasks, like restoring database and updating enums (removing values and updating values)

text for all the reasons you mentioned.

FWIW one thing that surprised me about Postgres enums (before going back to text enums was) is the fact you can't drop values from an enum.