Postgres JSONB vs JSON: When to Use Which

TL;DR: JSON keeps the original text. JSONB parses to a binary format. That difference governs every other trade-off.

The short version

JSON keeps the original text. JSONB parses to a binary format. That difference governs every other trade-off.

This guide covers the mental model, the patterns that pay off, and the trade-offs that decide whether a technique fits your code.

Working example

Here's a minimal example you can run as-is. Drop it in a fresh file, run it, and trace through it once before reading the rest.

-- Top 5 customers by revenue, last 30 days
SELECT c.id, c.email, SUM(o.total_cents) / 100.0 AS revenue_usd
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
  AND o.status = 'paid'
GROUP BY c.id, c.email
ORDER BY revenue_usd DESC
LIMIT 5;

Why this pattern

The shape above shows up in real Postgres codebases because it satisfies three constraints at once: it stays type-safe, it composes with the rest of the language's idioms, and it leaves a clear trail for the next developer (which, in six months, is you).

When you write the same pattern three times in a project, extract it. When you write it three times across projects, extract it into a shared library.

A common variant

The same idea adapted for a different shape. Notice how the structure stays the same — only the specifics change.

SELECT
  order_date,
  daily_total,
  SUM(daily_total) OVER (ORDER BY order_date) AS running_total
FROM (
  SELECT DATE(created_at) AS order_date, SUM(total_cents) AS daily_total
  FROM orders WHERE status = 'paid' GROUP BY 1
) d
ORDER BY order_date;

Trade-offs to watch

Every pattern has a failure mode. The most common one here is over-application: developers who learn a technique apply it everywhere, including places where simpler code would have been clearer.

Rule of thumb: if the abstraction takes more lines to describe than it saves, the abstraction is wrong.

A more involved example

Once the basic pattern is clear, here's how it composes with surrounding code. Read this one slowly.

WITH ranked AS (
  SELECT
    user_id,
    title,
    views,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY views DESC) AS rn
  FROM posts
)
SELECT user_id, title, views
FROM ranked
WHERE rn <= 3;

When to skip it

If the surrounding code is already simple, don't reach for Postgres-specific cleverness. Boring code is a feature. Save the patterns for places where they actually pay off — usually at module boundaries, in shared libraries, or where the alternative would be 50 lines of repetition.

FAQ

Related reading

• Postgres Indexing: The 80/20 That Solves Most Slow Queries
• Reading EXPLAIN ANALYZE Without Going Insane
• Postgres Row-Level Security: Patterns That Work
• Async Rust with Tokio: The Fundamentals
• Type Narrowing: Six Techniques That Cover 95% of Real Code