jonnifyelixir
F4 / F4.08 / keys

F4.08 · part 1 of 3

Branded ids as keys

The wire shows USR0NbWMtkosp8, but the database does not store that string. It stores the 64-bit integer underneath it as a bigint primary key — eight bytes, not fourteen characters — and the application brands it back into a string only at the edge, on the way out. The integer key is compact, it sorts by time, and it carries enough to validate itself.

One record, three views of its key: the integer the database keeps, the branded string the client sees, and the index that turns either into a row. Select one.

Store the integer, brand at the edge Advanced: eight bytes that sort themselves

Store the integer, brand at the edge

The integer and the string are the same value in two encodings: the column holds the number, the view layer brands it. Storing the number keeps the key small and numerically ordered; an index on it answers a point lookup in O(log n). Select a view.

View · select one

bigint PK 319545566822428714 8 bytes · numerically ordered wire USR0NbWMtkosp8 14 chars · branded on the way out B-tree index on id (bigint) WHERE id = 319545566822428714 -> O(log n), one row the database stores the 64-bit integer, not the string 

      

      
role: the stored primary key

      
size: 8 bytes

    

    
Store the number, show the string. The column stays eight bytes and sorts by time on its own,
      and the branded form is a pure function of it — computed at the edge, never persisted twice.

  


  

    
Advanced: eight bytes that sort themselves

    

      
A bigint is fixed at eight bytes and compares in one instruction; the fourteen-character
        string is larger, sorts lexically rather than numerically, and would need care to keep its width. Keeping the
        integer as the key also makes the primary-key index do double duty: because the high bits are a timestamp, rows
        inserted in time order land in id order, so the index is naturally clustered by time — new rows append at
        the end rather than scattering. That is what the next dive’s range query rides on.

      
The brand is a boundary concern, not a storage one. The schema’s id is the
        integer; a view or serializer prepends the namespace and base62-encodes on the way out, and a plug strips and
        decodes on the way in. Storing the string instead would double the key’s width, lose the cheap numeric
        order, and tempt code to parse it everywhere rather than at one edge.

    

    
# the schema keys on the integer; the namespace lives at the edge
schema "users" do
  field :id, :integer, primary_key: true   # bigint Snowflake, 8 bytes
  field :email, :string
end

Portal.Id.brand("USR", user.id)   # => "USR0NbWMtkosp8"  (only at the edge)

    

      

        
F4.07 · the id

        
A 64-bit integer with a branded, base62 wire form.

      

      
      

        
F4.08.1 · the stored key

        
Persist the integer as a bigint PK; brand it only when it leaves the system.

      

    

    
Next: SQLite & PostgreSQL
      — how the same integer key range-queries by time without a created_at column.

  


  

    
References

    
Primary sources for this lesson, and where it connects in the course.

    

      
Sources

      
      
Related in this course