jonnifyelixir
F4 / F4.08 / sql

F4.08 · part 2 of 3

SQLite & PostgreSQL

Because the high bits of the id are a timestamp, a window of time is a contiguous window of ids. To find every user created in a span, you do not need a created_at column or a second index: compute the Snowflake at the start of the window and the Snowflake at the end, and ask for id >= min AND id < max. The primary-key index already answers it.

Pick a window. The bounds are computed the same way the generator makes ids — (ms − epoch) << 22 — so the query is exact. Select a range.

A time window is an id range Advanced: one index, two queries

A time window is an id range

The lower bound is the smallest id that could be minted at or after the window opens; the upper bound is the smallest id at or after it closes. Every row in between was created in the window. Select a range to compute its bounds and the query.

Time window · select one

window 2026-05-01 .. 2026-06-01 min snowflake (window opens) 308392073625600000 max snowflake (window closes) 319626097459200000 SELECT * FROM users WHERE id >= 308392073625600000 AND id < 319626097459200000 one month of users, with no created_at column 

      

      
window: 2026-05

      
served by: the primary-key index

    

    
No timestamp column, no second index: the bounds are pure arithmetic on the dates, and the
      primary-key B-tree already stores rows in the order the query wants them.

  


  

    
Advanced: one index, two queries

    

      
The bound is (ms − epoch) << 22 — the timestamp shifted into place
        with the worker and sequence bits zeroed, which is the smallest id any worker could mint at that millisecond.
        So id >= min admits every id from the window’s first millisecond onward, and
        id < max stops at the next window’s first millisecond — a half-open
        range that misses nothing and double-counts nothing, across all 1024 workers. The same primary-key index that
        does point lookups serves this scan as a contiguous read, so a point fetch and a time range share one
        structure.

      
It is identical in SQLite and PostgreSQL: SQLite holds the id in its INTEGER class (up to 8 bytes),
        PostgreSQL in a bigint, and the comparison is numeric in both. The
        EchoData.Snowflake helpers min_for_time/1 and
        max_for_time/1 produce the two bounds, so the query reads as a date range while the
        database sees integers.

    

    
# a date range becomes an id range — the PK index serves it
min = EchoData.Snowflake.min_for_time("2026-05-01")   # => 308392073625600000
max = EchoData.Snowflake.max_for_time("2026-06-01")   # => 319626097459200000

from(u in User, where: u.id >= ^min and u.id < ^max)
# every user created in May 2026 — no created_at column, no second index

    

      

        
a date window

        
“Everything created in May” — a span of time.

      

      
      

        
a contiguous id range

        
id >= min AND id < max, answered by the primary-key index.

      

    

    
Next: Redis keys —
      namespaced string keys, and how edge validation sheds abusive traffic before the cache.

  


  

    
References

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

    

      
Sources

      
      
Related in this course