When JSONB beats normalized tables on Aurora and when it bites back. GIN, jsonb_path_ops, expression indexes, and the migration patterns I actually trust in production.
A Thursday afternoon at the creator economy platform I spent the last few years at. We were six weeks into a feature where creators could attach arbitrary metadata to community posts, tags, hidden flags, A/B variants, the usual grab bag. Someone had picked metadata jsonb on the community_posts table because it was fast to ship. Eight months later that column was 90 GB on a multi-terabyte Aurora writer, and the p99 on /communities/:id/posts had quietly drifted from 120 ms to 480 ms over a quarter. Nobody noticed until a creator complained in a support ticket. We did.
I like JSONB. I reach for it more than most. But the column above is also a perfect example of how it gets you. So here’s what I actually do now, with the scars to back it up.
The honest version. JSONB is right when the shape varies per row in a way you can’t enumerate at write time, and you mostly read the whole document or filter on one or two keys. Webhook payloads. Provider-specific receipts. Event envelopes you don’t own. Feature flag overrides per tenant. That kind of thing.
It’s wrong when you find yourself writing metadata->>'status' in eight different queries and one of them sits in a hot read path. That’s a column. Promote it.
The rule I land on in code review is boring. If the same key appears in more than two WHERE clauses across the codebase, it’s not a property of a JSON document anymore. It’s a column with a wrong birth certificate.
-- yes
create table apple_iap_receipts (
id bigserial primary key,
transaction_id text not null unique,
payload jsonb not null,
received_at timestamptz not null default now()
);
-- no, this is hiding three columns
create table community_posts (
id bigserial primary key,
author_id bigint not null,
metadata jsonb not null default '{}'
-- and now every read does metadata->>'status', metadata->>'visibility'
);Default GIN on a JSONB column gives you the kitchen sink. Every key, every value, every nested path. Great for ad-hoc ?, ?&, ?|, @>, and key existence queries. Big index. Slow writes.
jsonb_path_ops is the one I default to when the access pattern is containment. Roughly half the size, faster to build, and the writes hurt less. The trade is it only supports @>. No key-existence operators. If you live on @> queries, take that deal.
-- containment-only, smaller, faster writes
create index concurrently idx_iap_receipts_payload_path
on apple_iap_receipts
using gin (payload jsonb_path_ops);
-- example query the index actually serves
select id, received_at
from apple_iap_receipts
where payload @> '{"environment": "Production", "type": "DID_RENEW"}'::jsonb
order by received_at desc
limit 50;The bit that catches people. payload->>'environment' = 'Production' does NOT use the GIN index above. The expression rewrite has to be containment. I’ve watched a senior engineer stare at EXPLAIN ANALYZE for fifteen minutes because she’d written it with ->> and a JSON cast and the planner had quietly fallen back to a seq scan on a 90 GB column. Containment or it didn’t happen.
Most of the time, you don’t want all of JSONB indexed. You want one or two keys, fast, and you’d rather have a small B-tree than a big GIN. This is where expression indexes pull their weight.
-- a b-tree on a single extracted key, with the cast that matches your queries
create index concurrently idx_posts_status
on community_posts ((metadata->>'status'))
where (metadata->>'status') is not null;
-- and a composite for the actual access pattern
create index concurrently idx_posts_community_visible_created
on community_posts (community_id, (metadata->>'visibility'), created_at desc)
where (metadata->>'visibility') = 'public';Partial indexes are underused. Half of “JSONB is slow” complaints I’ve seen are really “we indexed everything and indexed nothing.” If 95% of reads care about visibility = 'public', the index should say so.
A Tuesday morning the same year. I was tagged into a war room because the Community read path was sluggish, p99 had jumped from around 120 ms to 8 seconds. Datadog’s AuroraReplicaLagMaximum > 60s for 2m alert had fired around 10:14 PT and by the time I joined, replica lag was at 14 minutes and climbing.
The on-call’s first move was the obvious one. Bump reader instance class up two tiers, r6g.4xlarge to r6g.16xlarge, on the theory that the readers were CPU-bound. Lag didn’t move a millimeter. The readers weren’t CPU-bound. They were starved of WAL.
I pulled pg_stat_activity on the writer. A long-running ANALYZE on community_posts was holding write-side locks. Someone had scheduled a partition-stats refresh in a maintenance cron that didn’t respect peak hours. The reason community_posts was so painful to analyze was the same reason its writes were heavy. The metadata JSONB column had a default GIN index, and the table had grown to a size where analyzing it produced WAL bursts the readers couldn’t drain fast enough.
We killed the analyze. Replica lag drained in about six minutes. Same week we shipped a db_safe_maintenance.rb wrapper. Every maintenance command routes through it and refuses to run between 06:00 and 22:00 UTC. We also swapped the default GIN for jsonb_path_ops on that column because nobody was using key-existence queries on it. The index shrunk by roughly half. ANALYZE got cheaper. About 22 minutes of degraded Community reads. The runbook now leads with “before touching reader scaling, check pg_stat_activity on the writer.” I’m the reason that sentence is in there.
The other war story. Late evening, past midnight UTC but only around 6 p.m. Pacific. We were promoting a JSON key, metadata->>'visibility', into a proper visibility column on a table with hundreds of millions of rows. The migration looked fine in review. It used add_column ... null: false, default: 'private' with the gem’s “safer” helper. I’d ack’d it that morning.
That helper, on Aurora at our row count, took an ACCESS EXCLUSIVE lock while it backfilled the default. About 87 seconds of blocked writes. Login error rate hit 100% for 85 of those seconds. The first instinct was to roll the migration back. There’s no clean rollback for a half-applied add_column_with_default, and killing it mid-flight risked leaving the table in an inconsistent metadata state. We let it finish.
The fix afterwards is the pattern I now use for every JSONB-to-column promotion on a hot table. Three steps. Never two.
# step 1: add as nullable, no default. cheap metadata change.
class AddVisibilityToCommunityPosts < ActiveRecord::Migration[7.1]
def change
add_column :community_posts, :visibility, :string
end
end
# step 2: backfill in batches, off-peak, idempotent
class BackfillCommunityPostsVisibility < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
BATCH_SIZE = 5_000
def up
CommunityPost
.where(visibility: nil)
.in_batches(of: BATCH_SIZE) do |batch|
batch.update_all(
"visibility = COALESCE(metadata->>'visibility', 'private')"
)
sleep 0.2
end
end
end
# step 3: enforce not null only after 100% backfilled
class EnforceCommunityPostsVisibilityNotNull < ActiveRecord::Migration[7.1]
def change
change_column_null :community_posts, :visibility, false
end
endWe also added a CI rule blocking any add_column with a non-null default on tables over 10 million rows. The gem is safer than raw ActiveRecord, not safe.
WHERE clauses, it wants to be a column.jsonb_path_ops over default GIN when your access pattern is containment. Half the size, lighter writes, you give up key-existence operators.->> does not use a jsonb_path_ops GIN index. Query with @> or you get a seq scan.pg_stat_activity on the writer before scaling readers.Thanks for reading. If you’ve got thoughts, send them my way.