Modeling State Transitions in Postgres

2026-08-11T00:00:00+00:00

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

Humid 1.0: React server-side rendering in Rails can be easy!

2026-08-04T00:00:00+00:00

I want React server-side rendering to be easy. There aren’t a lot of React server-side rendering tools in the Rails world: React on Rails has the largest presence, but it has many bells and whistles that I’m not ready for. I could sidecar Node.js but I really don’t want another piece of infrastructure I have to manage.

I just want something simple to use and easy to get started with. So we built it. Today we’re announcing Humid 1.0!

Humid is just a few helpers for React server-side rendering in Rails with mini_racer, a minimal and modern embedded V8 for Ruby.

Install it

gem "humid"

Write your renderer and wrap it with the setHumidRenderer global.

//app/assets/javascript/server_rendering.jsx
import React from 'react'
import { renderToString } from 'react-dom/server'

const Greeting = ({ name }) => <h1>Hello {name}h1>

setHumidRenderer((json) => {
  const props = JSON.parse(json)
  return renderToString(<Greeting {...props} />)
})

And render it from Ruby:

require 'humid'
require 'mini_racer'

context = MiniRacer::Context.new
server_bundle = Rails.root.join("app/assets/builds/server_rendering.js")
props = JSON.generate({ name: "World" })

Humid.prepare(context, {application_path: server_bundle})
Humid.render(context, props)
# => Hello World

Best of all:

There’s no separate Node.js process.
It’s powered by the performance of mini_racer.
There’s nothing that Humid does that you can’t do by yourself: it’s less than 200 lines of code.
Instrumentation and Telemetry included.
You can set Humid.prepare‘s default options with an initializer.

Design

Humid is designed with two goals in mind:

It’s for the common case where all data is gathered before rendering. Your application fetches everything needed, passes it as props, and Humid returns the rendered HTML in a single synchronous call.
It’s a stepping stone for when you want to scale on the edge using Cloudflare V8 isolates. mini_racer is a bare V8 environment, if your JS bundle works with mini_racer, it’ll work on Cloudflare V8 isolates.

Caveats

Humid renders synchronously. It does not support streaming or async data fetching during render. Gather your data first, then pass it as props.

mini_racer is a bare V8 environment: no window, no document, no Node builtins. Your build will need polyfills or shims. Here’s a working one for React.

mini_racer is thread safe, but not fork-safe. See guidance here for your webserver.