I’m not necessarily successful or famous for this. I’m not a maestro here trying to give you the truth about consulting. I am sharing what I do, as a practitioner, from these years as a consultant.

1) When I don’t have an opinion yet

You know the moment: “I suddenly feel unprepared, hearing about this piece of technology or service for the first time now” or “I have never encountered this type of problem before”. How do I buy myself time? I do not want to project fake confidence. I am not a “fake it until you make it” type. Side note: it feels stressful in that moment, but in hindsight, I know I don’t want to only solve problems I’ve already solved before in my career.

You may hear me saying things like “let me look that up” or even better “let’s figure it out together.” It changes the whole dynamic: I can gather more knowledge from the other side, and start reasoning together with them. Instead of admitting a gap and leaving it hanging, or hiding it, I turn it into shared work, a moment of learning in the open, and get a more detailed picture even if I do not know the implementation details yet. Later, I will still need time to research and come up with solutions, but this keeps the conversation going and being constructive, rather than becoming awkward.

Some other times there is a feeling that gets in the way, and I find myself doubling-down with stressful negative thoughts that consume energy, like: “I’m a consultant, they hired me because I’m supposed to be the expert”.

I’ve learned to go past that feeling. Not knowing something in the moment doesn’t cancel the expertise, it’s being honest. Being kind to your own brain about it matters more than performing certainty.

2) When I have an opinion, but it’s controversial

The real insight here is telling apart a value from a scar.

Example: “Capacity doesn’t change just because priorities did. If we’re adding something mid-sprint, something else of equal effort has to go”. That’s a principle, and I hold it firmly, even if it could be controversial, and it puts the client in front of trade-offs they may not like to hear. Capacity is finite. That doesn’t mean pushing back or reducing scope, it means addressing the request flexibly: moving it on the timeline, and being clear that “the team is at capacity” isn’t the same as “we shouldn’t do this”.

“In this project we are going to be working with service X” From my past experience, I remember that working with service X was a nightmare. This is one data point from one context, and treating it as universal truth is where bias sneaks in. The fix isn’t suppressing the experience, it’s presenting it as evidence to check rather than a verdict. Instead of saying a service “is terrible,” I try to hand it over as evidence: “this is what I’ve seen, worth checking if it still holds here” to keep the information useful without using my previous bad experience as an objective fact.

3) When my opinion contradicts theirs

I need to make a distinction here: If it is a “normal” disagreement I will try to stop for a second, breathe, try not to take it personally, make my recommendation backed by my reasoning (and I may not be right, you know?) and be constructive. Instead, I don’t promise something I know is wrong, or even just feels wrong, just to make the client happy. Maybe they will receive it, maybe not. But I did what I could. Building software shouldn’t be an intense life-or-death matter except when it touches my principles and ethics (examples: exploiting user privacy). At that point, I draw a line and feel free to be more direct, to avoid giving room for other interpretations.

None of this makes not-knowing comfortable, or disagreement painless. But naming these moments instead of pretending they do not happen has made them easier to sit with for me, as I still work on this skill every day.

XO Ruby Vancouver, August 15, Vancouver, Canada

XO Ruby Vancouver kicks things off. Fernando Perales will be speaking on “The Ruby Guide to Responsible LLM Integration,” a talk about the production challenges of wiring large language models into Ruby applications: malformed input, data leaks, prompt injection, outages, and rate limits, and the patterns that keep things stable once real users start hitting them.

EuRuKo 2026, September 16-18, Brno, Czech Republic

EuRuKo, the European Ruby Conference, returns for another year, this time in Brno. Sarah Lima and Rémy Hannequin are co-presenting It’s about Time, a hands-on workshop on why timekeeping in Ruby is trickier than it looks: time zones, daylight saving quirks, leap seconds, and the bugs they cause in persistence, background jobs, and tests. EuRuKo has one of the best community atmospheres on the Ruby calendar, and it’s always worth the trip.

Rails World 2026, September 23-24, Austin, Texas

Rails World lands in Austin this year, and two thoughtbotters are speaking. Tess Griffin is presenting Sharding Is Hard: Let’s Make It Easier!, on when vertical scaling stops being enough, how to pick a shard key, and how to keep transactions consistent across shards. Joël Quenneville is presenting Harness Engineering on Rails, on treating AI agents less like chatbots to prompt and more like reactive systems that improve code by iterating on standards, tests, and linters instead of one mistake at a time. Rails World has become the biggest event on the Rails calendar, and we’re glad to be part of it again.

Rocky Mountain Ruby, September 28-29, Boulder, Colorado

Right on the heels of Rails World, Rocky Mountain Ruby brings a single-track, two-day conference to Boulder. Fernando Perales is speaking on “Slowly We Rot: signs of a Rails app’s decay,” a talk about the patterns of decline he’s seen again and again after 13 years consulting on Rails apps: how a quiet TODO comment turns into code nobody wants to touch, why the rot compounds, and what to do about it before a rewrite starts to look like the only option.

Amsterdam Tech Leaders, October 6, Amsterdam, Netherlands

Next up is something a little different: a Tech Leaders meetup in Amsterdam on October 6, hosted by thoughtbot. It’s part of the same community series that’s been growing in London, Bristol, and Austin: casual, in-person conversations for engineering leaders. More details are coming soon.

London Chief Product Officer Conference 2026, October 29, London, UK

We’re also headed to the London Chief Product Officer Conference 2026, where [Bethan Ashley]bethan ashley is hosting and moderating a round table on product leadership. It’s a chance to dig into the questions CPOs are wrestling with alongside peers, in a smaller, conversation-driven format.

Tech Leaders meetups, ongoing

Alongside these conference stops, thoughtbot runs a recurring Tech Leaders meetup series: casual, in-person conversations for engineering leaders, hosted every few weeks in whichever city we can gather a room. Coming up: a Tech Leaders Meetup in London on August 18, Austin Tech Leaders on August 20, and Boston Tech Leaders x Webflow Conf ‘26 on September 2. Past editions have popped up in London, Bristol, Manchester, and Austin, including a Tech Leaders x SXSW 2026 stop back in March. Follow the calendar to see what’s next near you.

That’s six conference stops across two continents this fall, plus an ongoing run of Tech Leaders meetups. If you’re at any of these events, find us. We’d love to say hi.

There’s a better way: model each status change as its own row from the start. You get full history and the current state in one design, without sacrificing read performance. Here’s how.

Say we have a users table with name and status columns:

This design has an obvious limitation: if we change a user’s status, we can’t be sure of:

• What the previous status was;
• When the status changed.

Now imagine your stakeholders start asking questions like:

• Who was denied last Tuesday?
• How long did users stay in each status?
• Which users were denied and later re-approved?

These questions require history the current design has already thrown away. A single modeling change answers all of them efficiently, without sacrificing the one thing the current design does well: getting the current status.

The user statuses table

Instead of updating a status column on users, we create a separate table where each status change is a new row:

CREATE TABLE user_statuses (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  status VARCHAR NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

When a user’s status changes, we insert a new record. We never update or delete existing ones.

It’s worth stepping back and asking: what is a user’s current status? With this model, the answer becomes a definition:

A user’s current status is their most recently recorded status.

Not a value we store and keep in sync, but a query we run against the timeline. Adding a status column on users to speed up reads would mean caching a fact already derivable from user_statuses, a violation of Third Normal Form that creates two sources of truth that can drift apart.

If an attribute has a lifecycle, discrete transitions like pending to approved, consider tracking its changes by default, as retrofitting history after the fact means backfilling data you never recorded. The rest of this article shows that it’s possible to query it efficiently.

Querying the current status

There is a tradeoff, of course. Normalized data like this is harder to query. “Give me each user’s current status” used to be a simple column read. Now it requires finding the most recent user_statuses row per user. But harder to query does not mean slow. With the right approach and proper indexing, it’s possible to keep the data normalized and still have good query performance.

There are several ways to do this in SQL, and they differ in clarity, composability, and performance.

All of the approaches below benefit from a composite index that lets Postgres locate a user’s most recent status without scanning the entire table:

CREATE INDEX idx_user_statuses_user_id_created_at
  ON user_statuses (user_id, created_at DESC, id DESC)
  INCLUDE (status);

The B-tree is organized by (user_id, created_at DESC, id DESC) for fast lookups. INCLUDE (status) stores status in the index leaf pages as a non-key column, so Postgres can answer queries without fetching the row from the heap. This turns an Index Scan into an Index Only Scan.

Correlated subquery

The most straightforward approach. A subquery in the SELECT returns a single value per user. Simple and portable across databases.

SELECT
  users.id,
  users.name,
  (
    SELECT status
    FROM user_statuses
    WHERE user_statuses.user_id = users.id
    ORDER BY created_at DESC, id DESC
    LIMIT 1
  ) AS current_status
FROM users;

If you only need the status, this works well. But each additional column from the latest status (like created_at) requires another correlated subquery, which doubles the index lookups.

Window function

Numbers every row per user with ROW_NUMBER(), then filters to the first. This solves the multiple-column problem: you can select anything from the latest row. With the right index (like ours), Postgres can stop early per partition and avoid scanning extraneous rows.

The main downside is ergonomic: the query requires wrapping in a subquery just to filter on the computed row number. This doesn’t usually play well with ORM pagination, as the ORM won’t know the query needs to be wrapped in yet another subquery for COUNT(*) or LIMIT/OFFSET to work correctly.

SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN (
  SELECT
    user_id,
    status,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC, id DESC
    ) AS row_number
  FROM user_statuses
) latest ON latest.user_id = users.id AND latest.row_number = 1;

DISTINCT ON

Keeps the first row per group based on the ORDER BY. Concise and Postgres-native, but it sorts all status rows for the involved users before deduplicating.

SELECT DISTINCT ON (user_statuses.user_id)
  users.id, users.name, user_statuses.status
FROM users
LEFT JOIN user_statuses ON user_statuses.user_id = users.id
ORDER BY user_statuses.user_id, user_statuses.created_at DESC, user_statuses.id DESC;

Lateral join

Runs a subquery per row in the outer query that can reference that row’s columns. With an index, each lookup reads exactly one row. Unlike DISTINCT ON, it reads exactly one status row per user.

SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN LATERAL (
  SELECT status
  FROM user_statuses
  WHERE user_statuses.user_id = users.id
  ORDER BY created_at DESC, id DESC
  LIMIT 1
) latest ON true;

For each user, Postgres dips into user_statuses, grabs the most recent row via the index, and moves on.

Unlike the window function approach, the outer query stays flat, so ORMs can add LIMIT/OFFSET or wrap it with COUNT(*) without issues.

Benchmarking the approaches

I ran EXPLAIN ANALYZE on all four approaches.

-- Postgres 17, 100,000 users, 5,000,000 status changes (roughly 50
-- per user), with a covering index on (user_id, created_at DESC,
-- id DESC) INCLUDE (status).

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR NOT NULL
);

CREATE TABLE user_statuses (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  status VARCHAR NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO users (name)
SELECT 'User ' || n
FROM generate_series(1, 100000) AS n;

INSERT INTO user_statuses (user_id, status, created_at)
SELECT
  (random() * 99999)::int + 1,
  (ARRAY['pending', 'ready_for_review',
         'approved', 'denied', 'onboarding']
  )[floor(random() * 5 + 1)::int],
  now() - (random() * interval '365 days')
FROM generate_series(1, 5000000);

CREATE INDEX idx_user_statuses_user_id_created_at
  ON user_statuses (user_id, created_at DESC, id DESC)
  INCLUDE (status);

With a single user

For a single user, all approaches are sub-millisecond. The differences are negligible at this scale.

---------- CORRELATED SUBQUERY ----------

EXPLAIN ANALYZE
SELECT
  users.id,
  users.name,
  (
    SELECT status
    FROM user_statuses
    WHERE user_statuses.user_id = users.id
    ORDER BY created_at DESC, id DESC
    LIMIT 1
  ) AS current_status
FROM users
WHERE users.id = 42;

-- Plan: index-only scan with LIMIT 1

Index Scan using users_pkey on users  (rows=1)
  SubPlan 1
    ->  Limit  (rows=1 loops=1)
          ->  Index Only Scan using idx_user_statuses_user_id_created_at
                on user_statuses  (rows=1 loops=1)

---------- WINDOW FUNCTION ----------

EXPLAIN ANALYZE
SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN (
  SELECT
    user_id, status,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC, id DESC
    ) AS row_number
  FROM user_statuses
) latest ON latest.user_id = users.id AND latest.row_number = 1
WHERE users.id = 42;

-- Plan: index-only scan over this user's rows, stops at row 1

Nested Loop Left Join  (rows=1)
  ->  Index Scan using users_pkey on users  (rows=1)
  ->  Subquery Scan on latest  (rows=1)
        Filter: (latest.row_number = 1)
        ->  WindowAgg  (rows=1)
              Run Condition: (row_number() <= 1)
              ->  Index Only Scan using idx_user_statuses_user_id_created_at
                    on user_statuses  (rows=2)

---------- DISTINCT ON ----------

EXPLAIN ANALYZE
SELECT DISTINCT ON (user_statuses.user_id)
  users.id, users.name, user_statuses.status
FROM users
LEFT JOIN user_statuses ON user_statuses.user_id = users.id
WHERE users.id = 42
ORDER BY user_statuses.user_id,
         user_statuses.created_at DESC,
         user_statuses.id DESC;

-- Plan: sorts all ~40 status rows for this user, then deduplicates

Unique  (rows=1)
  ->  Sort  (rows=40)
        Sort Method: quicksort  Memory: 27kB
        ->  Nested Loop Left Join  (rows=40)
              ->  Index Scan using users_pkey on users  (rows=1)
              ->  Index Only Scan using idx_user_statuses_user_id_created_at
                    on user_statuses  (rows=40)

---------- LATERAL JOIN ----------

EXPLAIN ANALYZE
SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN LATERAL (
  SELECT status
  FROM user_statuses
  WHERE user_statuses.user_id = users.id
  ORDER BY created_at DESC, id DESC
  LIMIT 1
) latest ON true
WHERE users.id = 42;

-- Plan: index-only scan with LIMIT 1, single pass

Nested Loop Left Join  (rows=1)
  ->  Index Scan using users_pkey on users  (rows=1)
  ->  Limit  (rows=1 loops=1)
        ->  Index Only Scan using idx_user_statuses_user_id_created_at
              on user_statuses  (rows=1 loops=1)

With a page of 15 users

The correlated subquery, window function, and lateral join are all sub-millisecond. DISTINCT ON is catastrophically slower: Postgres can’t push the LIMIT through the Unique node, so it hash-joins all 5 million status rows, sorts them on disk, and only then returns 15.

---------- CORRELATED SUBQUERY ----------

EXPLAIN ANALYZE
SELECT
  users.id,
  users.name,
  (
    SELECT status
    FROM user_statuses
    WHERE user_statuses.user_id = users.id
    ORDER BY created_at DESC, id DESC
    LIMIT 1
  ) AS current_status
FROM users
ORDER BY id
LIMIT 15;

-- Plan: index-only scan with LIMIT 1, loops=15

Limit  (rows=15)
  ->  Index Scan using users_pkey on users  (rows=15)
        SubPlan 1
          ->  Limit  (rows=1 loops=15)
                ->  Index Only Scan using idx_user_statuses_user_id_created_at
                      on user_statuses  (rows=1 loops=15)

---------- WINDOW FUNCTION ----------

EXPLAIN ANALYZE
SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN (
  SELECT
    user_id, status,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC, id DESC
    ) AS row_number
  FROM user_statuses
) latest ON latest.user_id = users.id AND latest.row_number = 1
ORDER BY users.id
LIMIT 15;

-- Plan: index-only scan, stops after first row per partition

Limit  (rows=15)
  ->  Merge Left Join  (rows=15)
        ->  Index Scan using users_pkey on users  (rows=15)
        ->  Materialize  (rows=15)
              ->  Subquery Scan on latest  (rows=15)
                    Filter: (latest.row_number = 1)
                    ->  WindowAgg  (rows=15)
                          Run Condition: (row_number() <= 1)
                          ->  Index Only Scan using idx_user_statuses_user_id_created_at
                                on user_statuses  (rows=681)

---------- DISTINCT ON ----------

EXPLAIN ANALYZE
SELECT DISTINCT ON (user_statuses.user_id)
  users.id, users.name, user_statuses.status
FROM users
LEFT JOIN user_statuses ON user_statuses.user_id = users.id
ORDER BY user_statuses.user_id,
         user_statuses.created_at DESC,
         user_statuses.id DESC
LIMIT 15;

-- Plan: hash joins all 5 million rows, sorts on disk, then returns 15

Limit  (rows=15)
  ->  Unique  (rows=15)
        ->  Sort  (rows=5000000)
              Sort Method: external merge  Disk: 330696kB
              ->  Hash Right Join  (rows=5000000)
                    ->  Seq Scan on user_statuses  (rows=5000000)
                    ->  Hash
                          ->  Seq Scan on users  (rows=100000)

---------- LATERAL JOIN ----------

EXPLAIN ANALYZE
SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN LATERAL (
  SELECT status
  FROM user_statuses
  WHERE user_statuses.user_id = users.id
  ORDER BY created_at DESC, id DESC
  LIMIT 1
) latest ON true
ORDER BY users.id
LIMIT 15;

-- Plan: index-only scan with LIMIT 1, loops=15

Limit  (rows=15)
  ->  Nested Loop Left Join  (rows=15)
        ->  Index Scan using users_pkey on users  (rows=15)
        ->  Limit  (rows=1 loops=15)
              ->  Index Only Scan using idx_user_statuses_user_id_created_at
                    on user_statuses  (rows=1 loops=15)

These numbers assume the first page. With a high OFFSET, all approaches degrade because Postgres processes every skipped row before returning results. At OFFSET 99000, even the lateral join takes around 157 ms to return 15 rows. Cursor pagination avoids this entirely by filtering with WHERE id > :last_seen_id instead of skipping rows.

With all users (100,000)

DISTINCT ON is the slowest by far: it hash-joins all 5 million rows, then sorts them on disk. The window function walks the index in order and stops early per partition. The correlated subquery and lateral join are neck and neck: both do an index-only scan with LIMIT 1, executed once per user: 100,000 random reads into the index. The correlated subquery would fall behind if it needed more columns, since each additional column requires another subplan.

---------- CORRELATED SUBQUERY ----------

EXPLAIN ANALYZE
SELECT
  users.id,
  users.name,
  (
    SELECT status
    FROM user_statuses
    WHERE user_statuses.user_id = users.id
    ORDER BY created_at DESC, id DESC
    LIMIT 1
  ) AS current_status
FROM users;

-- Plan: index-only scan with LIMIT 1, executed once per user (loops=100000)

Seq Scan on users  (rows=100000)
  SubPlan 1
    ->  Limit  (rows=1 loops=100000)
          ->  Index Only Scan using idx_user_statuses_user_id_created_at
                on user_statuses  (rows=1 loops=100000)

---------- WINDOW FUNCTION ----------

EXPLAIN ANALYZE
SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN (
  SELECT
    user_id, status,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC, id DESC
    ) AS row_number
  FROM user_statuses
) latest ON latest.user_id = users.id AND latest.row_number = 1;

-- Plan: walks all 5 million rows in index order, stops at first per partition

Merge Right Join  (rows=100000)
  ->  Subquery Scan on latest  (rows=100000)
        Filter: (latest.row_number = 1)
        ->  WindowAgg  (rows=100000)
              Run Condition: (row_number() <= 1)
              ->  Index Only Scan using idx_user_statuses_user_id_created_at
                    on user_statuses  (rows=5000000)
  ->  Index Scan using users_pkey on users  (rows=100000)

---------- DISTINCT ON ----------

EXPLAIN ANALYZE
SELECT DISTINCT ON (user_statuses.user_id)
  users.id, users.name, user_statuses.status
FROM users
LEFT JOIN user_statuses ON user_statuses.user_id = users.id
ORDER BY user_statuses.user_id,
         user_statuses.created_at DESC,
         user_statuses.id DESC;

-- Plan: hash joins all 5 million rows, then sorts on disk

Unique  (rows=100000)
  ->  Sort  (rows=5000000)
        Sort Method: external merge  Disk: 330696kB
        ->  Hash Right Join  (rows=5000000)
              ->  Seq Scan on user_statuses  (rows=5000000)
              ->  Hash
                    ->  Seq Scan on users  (rows=100000)

---------- LATERAL JOIN ----------

EXPLAIN ANALYZE
SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN LATERAL (
  SELECT status
  FROM user_statuses
  WHERE user_statuses.user_id = users.id
  ORDER BY created_at DESC, id DESC
  LIMIT 1
) latest ON true;

-- Plan: one index-only scan with LIMIT 1 per user, single pass

Nested Loop Left Join  (rows=100000)
  ->  Seq Scan on users  (rows=100000)
  ->  Limit  (rows=1 loops=100000)
        ->  Index Only Scan using idx_user_statuses_user_id_created_at
              on user_statuses  (rows=1 loops=100000)

Without the index

The window function and lateral join are the two strongest approaches with our covering index. How much of that performance comes from the index itself? I dropped it and re-ran both on all 100,000 users:

The window function is about 4x faster with the index. Because the index includes status as a non-key column, Postgres can do an index-only scan over all 5 million rows without touching the heap at all. Without the index, it falls back to a sequential scan plus an external merge sort on disk.

The lateral join went from the fastest to the slowest. With the index, it reads one row per user: 100,000 fast lookups. Without it, each lookup becomes a sequential scan of all 5 million rows, repeated 100,000 times.

---------- WINDOW FUNCTION ----------

EXPLAIN ANALYZE
SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN (
  SELECT
    user_id, status,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY created_at DESC, id DESC
    ) AS row_number
  FROM user_statuses
) latest ON latest.user_id = users.id AND latest.row_number = 1;

-- Plan: sequential scan + external merge sort on disk

Hash Right Join  (rows=100000)
  ->  Subquery Scan on latest  (rows=100000)
        Filter: (latest.row_number = 1)
        ->  WindowAgg  (rows=100000)
              Run Condition: (row_number() <= 1)
              ->  Sort  (rows=5000000)
                    Sort Method: external merge
                    ->  Seq Scan on user_statuses  (rows=5000000)
  ->  Hash  (rows=100000)
        ->  Seq Scan on users  (rows=100000)

---------- LATERAL JOIN ----------

EXPLAIN ANALYZE
SELECT users.id, users.name, latest.status
FROM users
LEFT JOIN LATERAL (
  SELECT status
  FROM user_statuses
  WHERE user_statuses.user_id = users.id
  ORDER BY created_at DESC, id DESC
  LIMIT 1
) latest ON true;

-- Plan: sequential scan of all 5 million rows per user (loops=100000)

Nested Loop Left Join  (rows=100000)
  ->  Seq Scan on users  (rows=100000)
  ->  Limit  (rows=1 loops=100000)
        ->  Sort  (rows=1 loops=100000)
              Sort Method: top-N heapsort  Memory: 25kB
              ->  Seq Scan on user_statuses  (rows=50 loops=100000)
                    Filter: (user_id = users.id)
                    Rows Removed by Filter: 4999950

Filtering users by status

A common application query is “give me all users whose current status is X.” This needs to be fast.

SELECT users.id, users.name
FROM users
LEFT JOIN LATERAL (
  SELECT status
  FROM user_statuses
  WHERE user_statuses.user_id = users.id
  ORDER BY created_at DESC, id DESC
  LIMIT 1
) latest ON true
WHERE latest.status = 'ready_for_review';

The lateral join finds each user’s current status, then the outer WHERE filters to the ones we care about. No index can express “users whose latest status is X,” so Postgres checks every user’s latest status.

For all 100,000 users, that means one index-only scan per user and a post-filter to discard the non-matches. For a page of 15 using cursor pagination it stays fast, since Postgres picks up from the last seen ID and stops as soon as it fills the page:

SELECT users.id, users.name
FROM users
LEFT JOIN LATERAL (
  SELECT status
  FROM user_statuses
  WHERE user_statuses.user_id = users.id
  ORDER BY created_at DESC, id DESC
  LIMIT 1
) latest ON true
WHERE latest.status = 'ready_for_review'
  AND users.id > :last_seen_id
ORDER BY users.id
LIMIT 15;

How many users Postgres scans per page depends on how common the status is. If 20% of users are currently ready_for_review, it checks roughly 75 users to fill a page of 15. If the status is rare, it scans more, but each check is a single index-only probe. The query above runs in about 0.35 ms.

What this data model enables

Now that we have the full timeline of status changes, we can answer questions that a single status column never could.

Who was denied last Tuesday?

SELECT DISTINCT users.id, users.name
FROM users
JOIN user_statuses ON user_statuses.user_id = users.id
WHERE user_statuses.status = 'denied'
  AND user_statuses.created_at >= '2026-07-14'
  AND user_statuses.created_at < '2026-07-15';

No lateral join needed here. We are querying the history directly, not deriving the current state.

How long did users stay in each status?

This is where window functions actually shine. LEAD() lets us peek at the next row in the sequence to calculate the duration of each status:

SELECT
  user_id,
  status,
  created_at AS started_at,
  LEAD(created_at) OVER (
    PARTITION BY user_id
    ORDER BY created_at, id
  ) AS ended_at,
  LEAD(created_at) OVER (
    PARTITION BY user_id
    ORDER BY created_at, id
  ) - created_at AS duration
FROM user_statuses
ORDER BY user_id, created_at, id;

The last status for each user will have a NULL duration, which makes sense: it’s still the current one.

Window functions are a natural fit here. Unlike our earlier benchmark where we only needed the latest row per user, this query genuinely needs every row because it computes across the full timeline.

Which users were denied and later re-approved?

SELECT DISTINCT users.id, users.name
FROM users
JOIN user_statuses denied
  ON denied.user_id = users.id
  AND denied.status = 'denied'
JOIN user_statuses approved
  ON approved.user_id = users.id
  AND approved.status = 'approved'
  AND approved.created_at > denied.created_at;

This joins the history table against itself: one join finds the denied row, the other finds an approved row that came after it. No derived state, just facts in the timeline.

Wrap-up

When an attribute changes over time and those changes matter to your domain, model it as a separate table of timestamped rows. Don’t update a column in place and lose what was there before.

To query the current value, use a LATERAL JOIN with a composite index. It reads one row per lookup, returns as many columns as you need, and composes well with the rest of your query. Window functions are a close second with the right index, thanks to a Run Condition optimization that stops early per partition. Correlated subqueries work for simple cases but don’t scale to multiple columns. DISTINCT ON scans the entire history table and gets slower as it grows.

The payoff is that the same table that gives you the current status also gives you the full timeline, duration analysis, pattern matching, and operational metrics. One modeling decision, many questions answered.