Reducing Postgres Join Rows with LATERAL and a JSONB Fence for Real-Time Scoring Tables

The weekend bug: my “fast” SQL was exploding the row count

I ran into a frustrating situation while preparing features for a real-time scoring pipeline. The query looked reasonable, but it behaved like a row-multiplication machine: a small table of user events joined into a larger table of “latest profile facts,” and suddenly I was generating millions of intermediate rows.

That kind of blow-up doesn’t just slow things down—it also makes data observability harder because the “wrong” outputs can be hard to trace back to the exact join expansion.

I wanted a way to:

join only the single latest matching fact per user,
while still filtering by a JSONB attribute stored in that fact row,
and do it without scanning or joining more rows than necessary.

The niche trick I ended up with: using LATERAL (a correlated subquery that can reference columns from the outer query) plus a “JSONB fence” filter applied inside the lateral subquery.

Below is the working approach I used on PostgreSQL.

Minimal schema I used to reproduce the problem

I used two tables:

user_events: incoming events (many per user)
profile_facts: precomputed facts, with a JSONB payload and a timestamp

-- PostgreSQL schema
CREATE TABLE user_events (
  event_id        bigserial PRIMARY KEY,
  user_id         bigint NOT NULL,
  event_time      timestamptz NOT NULL,
  event_type      text NOT NULL
);

CREATE TABLE profile_facts (
  fact_id         bigserial PRIMARY KEY,
  user_id         bigint NOT NULL,
  fact_time       timestamptz NOT NULL,
  payload         jsonb NOT NULL
);

-- Useful indexes for the pattern below
CREATE INDEX ON user_events (user_id, event_time DESC);
CREATE INDEX ON profile_facts (user_id, fact_time DESC);

-- A GIN index for querying JSONB keys (optional but common)
CREATE INDEX profile_facts_payload_gin ON profile_facts USING GIN (payload);

Sample data

INSERT INTO user_events (user_id, event_time, event_type) VALUES
(1, now() - interval '2 hours', 'click'),
(1, now() - interval '1 hour', 'purchase'),
(2, now() - interval '3 hours', 'click');

INSERT INTO profile_facts (user_id, fact_time, payload) VALUES
(1, now() - interval '90 minutes',  '{"source":"app","tier":"free","region":"US"}'),
(1, now() - interval '30 minutes',  '{"source":"web","tier":"pro","region":"EU"}'),
(1, now() - interval '10 minutes',  '{"source":"web","tier":"pro","region":"US"}'),
(2, now() - interval '50 minutes',  '{"source":"app","tier":"free","region":"APAC"}');

The naive query that multiplies rows

My first attempt joined events to all facts, then tried to pick the latest using an outer filter. In practice, this creates a huge intermediate result.

Here’s a simplified version of what I started with:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  e.event_id,
  e.user_id,
  e.event_time,
  f.fact_id,
  f.fact_time,
  f.payload
FROM user_events e
JOIN profile_facts f
  ON f.user_id = e.user_id
WHERE f.payload->>'source' = 'web';

What happens when you run this

For each user_events row, Postgres finds all matching profile_facts rows with payload.source = 'web'.
For user 1, there are two web facts (30 minutes and 10 minutes ago).
So a user with multiple events produces multiple joined rows per event.

In a real pipeline where profile_facts and user_events are larger (and the filters are more complex), this becomes a row explosion.

The optimization: LATERAL “latest row” lookup with an internal JSONB filter

The key idea I used:

For each event e, run a subquery that:
filters facts by payload.source = 'web' inside the subquery (the “JSONB fence”),
sorts by fact_time desc,
takes LIMIT 1 (latest match),
returns exactly one fact row per event.

The working query

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  e.event_id,
  e.user_id,
  e.event_time,
  pf.fact_id,
  pf.fact_time,
  pf.payload
FROM user_events e
JOIN LATERAL (
  SELECT f.*
  FROM profile_facts f
  WHERE f.user_id = e.user_id
    -- JSONB fence: only consider facts that match the payload rule
    AND f.payload->>'source' = 'web'
  ORDER BY f.fact_time DESC
  LIMIT 1
) pf ON TRUE;

Step-by-step breakdown

JOIN LATERAL (...) pf ON TRUE
- LATERAL makes the subquery run per outer row (e).
- ON TRUE is a standard pattern meaning “the subquery always produces at most one row; don’t add another join condition.”
WHERE f.user_id = e.user_id
- correlates the subquery to the current event’s user.
AND f.payload->>'source' = 'web'
- this is the fence: it limits the candidate facts before sorting/limiting.
ORDER BY f.fact_time DESC LIMIT 1
- returns the most recent fact that satisfies the fence.

Why this is faster than the naive join

Instead of joining all matching facts and then trying to reduce afterward, it:

filters early,
sorts a smaller candidate set,
and emits exactly one pf row per e.

Making it production-real: selecting more complex “latest per event” logic

In scoring pipelines, the “latest” rule often depends on more than a timestamp. For example, I needed:

latest fact for the user and a region matching an event attribute (or some derived rule).

Here’s a realistic pattern: let’s pretend the event includes an implicit region rule based on event_type.

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  e.event_id,
  e.user_id,
  e.event_time,
  pf.fact_id,
  pf.fact_time,
  pf.payload->>'region' AS fact_region
FROM user_events e
JOIN LATERAL (
  SELECT f.*
  FROM profile_facts f
  WHERE f.user_id = e.user_id
    AND f.payload->>'source' = 'web'
    AND f.payload->>'region' = CASE
      WHEN e.event_type = 'purchase' THEN 'US'
      ELSE 'EU'
    END
  ORDER BY f.fact_time DESC
  LIMIT 1
) pf ON TRUE;

The structure stays the same, but the fence becomes event-aware.

Observability tie-in: how row-count sanity helped catch the earlier bug

When I compared query outputs, the difference was obvious:

naive join: multiple fact_id values per event_id
optimized query: exactly one fact_id per event_id (or none, if no matching fence)

To make this enforceable, I added a sanity check query during development that counts matches per event:

WITH matches AS (
  SELECT
    e.event_id,
    COUNT(pf.fact_id) AS match_count
  FROM user_events e
  LEFT JOIN LATERAL (
    SELECT f.fact_id
    FROM profile_facts f
    WHERE f.user_id = e.user_id
      AND f.payload->>'source' = 'web'
    ORDER BY f.fact_time DESC
    LIMIT 1
  ) pf ON TRUE
  GROUP BY e.event_id
)
SELECT *
FROM matches
ORDER BY match_count DESC, event_id;

With the LIMIT 1, match_count should be either 0 or 1. In my earlier naive query approach, it was frequently 2+ for users with multiple web facts.

This kind of constraint is gold for data observability: it turns “performance weirdness” into a measurable contract.

Practical indexing notes (so the plan doesn’t regress)

The query’s performance depends on quickly locating candidates per user and ordering by time.

I found these indexes were essential:

CREATE INDEX ON profile_facts (user_id, fact_time DESC);

And for JSONB filtering:

If the JSONB predicate is selective and stable, consider a functional index.
For example, if source is frequently queried:

CREATE INDEX ON profile_facts ((payload->>'source'));

For more complex JSONB keys, you can use GIN on the whole payload, but the functional index on the specific key used in the fence often helps Postgres choose a better plan.

Final takeaway

I optimized a real-time scoring join in Postgres by replacing a naive join (that multiplied rows) with a JOIN LATERAL subquery that applies a JSONB fence inside the correlated lookup and returns only the latest matching row via ORDER BY ... LIMIT 1. This pattern kept intermediate row counts small, made performance predictable, and gave me a clear observability signal: the query contract becomes “0 or 1 matching latest fact per event,” which is exactly the kind of constraint that prevents silent data quality regressions.