PostgreSQL JSONB with Laravel: Tips & Tricks

Tip: GIN Index for Fast JSONB Queries

DB::statement('CREATE INDEX idx_posts_meta ON posts USING GIN (metadata)');

Without this index, JSONB queries do full table scans.

Gotcha: jsonb_set() for Partial Updates

UPDATE posts SET metadata = jsonb_set(metadata, '{views}', '100');

Updates a single key without replacing the whole JSON object.

Tip: Query Nested JSONB

Post::whereRaw("metadata->'author'->>'name' = ?", ['John'])->get();

The ->> operator returns text. -> returns JSONB.

Gotcha: JSONB Stores Numbers as Numbers

$post->metadata = ['count' => '5']; // Stored as string
$post->metadata = ['count' => 5];  // Stored as number

Be consistent with types.

Tip: JSONB Contains Query

Post::whereRaw("metadata @> ?", [json_encode(['status' => 'published'])])->get();

Finds posts where metadata contains the given key-value pair.

Gotcha: jsonb_pretty() for Debugging

SELECT jsonb_pretty(metadata) FROM posts WHERE id = 1;

Human-readable JSON output.

Tip: JSONB Array Operations

// Find posts with a specific tag
Post::whereRaw("metadata->'tags' @> ?", [json_encode(['laravel'])])->get();

Gotcha: JSONB vs JSON

Always use jsonb. It's indexed, faster, and removes duplicate keys. Plain json is stored as text.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

JSONB in PostgreSQL is powerful, but it's not a replacement for relational design. I've debugged slow queries where JSONB fields were queried without a GIN index, resulting in sequential scans of million-row tables. Always create USING GIN indexes on JSONB columns you query, and avoid JSONB for data that has a fixed schema — that's what regular columns are for. JSONB shines for flexible metadata, not primary data.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)