Modeling State Transitions in Postgres

On most projects I’ve consulted on, status starts as a column. It works, until someone asks “who was denied last Tuesday?” and the schema can’t answer. At that point, you can’t retrofit history you never recorded.

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:

id	name	status
1	Paul Winston	`pending`
2	Bob Marley	`ready_for_review`
3	Carlos Lagrande	`denied`

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.

id	user_id	status	created_at
1	1	`pending`	2026-07-10 09:00:00
2	1	`ready_for_review`	2026-07-12 14:30:00
3	1	`approved`	2026-07-15 11:00:00
4	2	`pending`	2026-07-11 10:00:00
5	2	`denied`	2026-07-13 16:45:00

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.

Benchmark setup

-- 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

Approach	Execution Time
Correlated subquery	0.015 ms
Window function	0.048 ms
`DISTINCT ON`	0.4 ms
Lateral join	0.012 ms

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

Queries and plans (single user)

---------- 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

Approach	Execution Time
Correlated subquery	0.2 ms
Window function	0.7 ms
`DISTINCT ON`	1,903 ms
Lateral join	0.07 ms

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.

Queries and plans (page of 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)

Approach	Execution Time
Correlated subquery	223 ms
Window function	459 ms
`DISTINCT ON`	2,823 ms
Lateral join	174 ms

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.

Queries and plans (100,000 users)

---------- 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:

Approach	With index	Without index
Window function	459 ms	1,946 ms
Lateral join	174 ms	~4 hours

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.

Queries and plans (without index)

---------- 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.

Sign up to receive a weekly recap from thoughtbot

About thoughtbot