Storing status as a single column loses history the moment it changes. A better approach is a separate `user_statuses` table where each status change is an immutable, timestamped row. This enables full audit history, duration analysis, and pattern queries while still delivering current-status reads efficiently. Four SQL approaches are benchmarked on Postgres 17 with 100k users and 5M status rows: lateral join is fastest overall (174 ms for all users), correlated subquery is close behind, window function is a solid second, and DISTINCT ON is catastrophically slow at scale (1,903 ms for just 15 users with LIMIT). A composite covering index on `(user_id, created_at DESC, id DESC) INCLUDE (status)` is essential — without it, lateral join degrades from 174 ms to roughly 4 hours for 100k users. Cursor pagination is recommended over OFFSET to avoid row-skipping costs.
Table of contents
The user statuses tableQuerying the current statusBenchmarking the approachesFiltering users by statusWhat this data model enablesWrap-upIf you enjoyed this post, you might also like:Questions this post answers
What is the fastest way to query the current status per user from a history table in PostgreSQL?
A LEFT JOIN LATERAL with LIMIT 1 and a composite covering index on (user_id, created_at DESC, id DESC) INCLUDE (status) is the fastest approach. In benchmarks on Postgres 17 with 100k users and 5M status rows, it ran in 174 ms for all users and 0.07 ms for a page of 15 — outperforming correlated subquery (223 ms / 0.2 ms), window function (459 ms / 0.7 ms), and DISTINCT ON (2,823 ms / 1,903 ms). Developers building audit-trail schemas track PostgreSQL query patterns like this on daily.dev.
Why is DISTINCT ON so slow when paginating a history table in PostgreSQL?
DISTINCT ON cannot push a LIMIT through the Unique node when joined to another table. Instead of stopping early, Postgres hash-joins the entire history table, sorts all rows on disk, and only then returns the requested page. With 5M status rows and LIMIT 15, this produced a 330 MB external merge sort taking 1,903 ms — versus 0.07 ms for a lateral join doing the same work. Engineers hitting unexpected slow queries on PostgreSQL pagination find the root cause faster when they follow database internals coverage on daily.dev.
How much does dropping the covering index hurt a lateral join on a large PostgreSQL table?
Removing the covering index turns a lateral join from the fastest approach into effectively unusable. With the index, querying current status for 100k users takes 174 ms via one index-only scan per user. Without it, each of the 100k lookups becomes a sequential scan of all 5M rows, pushing total execution time to approximately 4 hours. The window function degrades far less — from 459 ms to 1,946 ms — because it can still do a single sequential scan. Teams deciding between query strategies for append-only tables stay current on PostgreSQL indexing trade-offs through daily.dev.